CODEWITHSUNDEEP
excelvba
svarogman
svarogman

Reputation: 3

#value error with excel user defined function

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

Upvotes: 0

Views: 332

Answers (1)

Storax
Storax

Reputation: 12167

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.

Upvotes: 2

Related Questions