#value error with excel user defined function

Question

I have created user defined function in excel VBA:

Public Function RegExpReplace(Text As String, Pattern As String, replaceVar As String, Optional Glob As Boolean = False, Optional IgnoreCase As Boolean = False, Optional Multiline As Boolean = False) As Variant
On Error GoTo ErrHandl
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = Pattern
regex.Global = Glob
regex.IgnoreCase = IgnoreCase
regex.Multiline = Multiline
RegExpReplace = CVar(regex.Replace(Text, replaceVar))
MsgBox RegExpReplace
ErrHandl:
RegExpReplace = CVErr(xlErrValue)
End Function

But when I am trying to call it from sheet i got #VALUE! error:

error screenshot (localized excel)

while MsgBox shows correct result:

MsgBox screenshot

The code you posted is working for me. But as you did not post the whole code it's difficult to tell what your issue is. Especially there is no error handling code posted. I guess you also run through this code although the function was completed successfully. — Storax
– Storax, Commented Dec 8, 2019 at 16:50
Call your function from a VBA Sub and you can debug more easily. — Tim Williams
– Tim Williams, Commented Dec 8, 2019 at 17:09
@Storax This is hole code. The purpose was to add this function to excel Add-in (. xlam file) and use it on worksheets. — svarogman
– svarogman, Commented Dec 8, 2019 at 19:34
If this is the whole code you get a compile error and you will not get a MsgBox giving you the result you showed. Every function needs to end with a End Function and you alse need to define the label ErrHandl. Please check your code again more carefully. — Storax
– Storax, Commented Dec 8, 2019 at 19:43
@Storax Thank you, i just forget to post last 3 lines of my code — svarogman
– svarogman, Commented Dec 8, 2019 at 19:53

Storax · Accepted Answer · 2019-12-08 19:55:07Z

2

Change your code like that

Public Function RegExpReplace(Text As String, Pattern As String, replaceVar As String, Optional Glob As Boolean = False, Optional IgnoreCase As Boolean = False, Optional Multiline As Boolean = False) As Variant
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = Pattern
    regex.Global = Glob
    regex.IgnoreCase = IgnoreCase
    regex.Multiline = Multiline
    RegExpReplace = CVar(regex.Replace(Text, replaceVar))
    MsgBox RegExpReplace
    Exit Function
ErrHandl:
    RegExpReplace = CVErr(xlErrValue)
End Function

You forgot to exit the function in case there is no error and because of that the line RegExpReplace = CVErr(xlErrValue) was also executed giving you #Value even if there is no error.

answered Dec 8, 2019 at 19:55

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

#value error with excel user defined function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related