1

I have an add-in which defines a User Defined Formula (checks regular expression matching) in Excel in which it will be used to in cells custom validation. Assume user A has the add-in installed and user B doesn't have the add-in. Thus, if user A created a worksheet and validated a specific cell using the UDF then when user B tries to open this worksheet and edit the same cell, he will keep getting an error because the UDF is not defined and no matching data is allowed (I would call it an undefined behavior because user B doesn't have the UDF defined).

Is there a way to resolve this issue that doesn't user B to install the add-in? Or to clear the data validation for any problematic cell?

I should mention that using a UDF in custom validation is done by defining a named range that wraps the UDF with the pattern needed to match because UDFs can't be used directly as a formula in data validation.

Thanks in advance.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
FAF
  • 113
  • 4
  • A macro to paste.special values once the UDF has completed for those particular cells run by User A before sending to User B. – Solar Mike Feb 21 '23 at 05:54
  • I don't understand what does the macro do? and how does this solve the problem above? @SolarMike – FAF Feb 21 '23 at 05:58
  • The Macro pastes special values, then User B just gets a worksheet with values with no udf removing the problem you described. – Solar Mike Feb 21 '23 at 05:59

1 Answers1

1

You can check if the result of the UDF is an error (i.e. the user doesn't have access to it, resulting in a #NAME? error):

=IF(ISERROR(UDF()),<alternative validation>,UDF())

This way, if the add-on containing the UDF is installed, that will be used for validation. If not, you can provide an alternative validation (probably simpler) or just allow/disallow everything.

Gravitate
  • 2,885
  • 2
  • 21
  • 37