vbaargumentsbyref

VBA compile error due to "ByRef argument type mismatch"

I have this VBA code:

codes = regexSearch("Response Codes: (.+)[\W$]", email.Body)
MsgBox ("Codes is >>>" & codes & "<<<")
MsgBox ("Codes type is " & VarType(codes))
codes = Left(codes, Len(codes) - 1)
MsgBox ("Codes is >>>" & codes & "<<<")
msgCount = regexSearch(".+:\W+(\d+)", codes)

and my 'regexSearch' subroutine, (which works just fine in 30 or 40 other places) is defined as:

Function regexSearch(pattern As String, argument As String)

The body of my email contains:

Moneta IBS via VPMG Response Codes: 911: 202 Severity: 1

When I run the VBA againast the email, I get:

Compile error

If I remove the offending line and let the MsgBox diagnostics run, I see that:

'Codes' value

with the ending CR/LF.

The subsequent MsgBoxes state that 'Codes type is 8' (String), then 'Codes is >>>911:202<<<'

substituting "911: 202" for codes in msgCount = regexSearch(".+:\W+(\d+)", codes) and it works.

What is causing the argument type mismatch?

Solution

  • codes is probably declared as Variant while regexSearch expects an String. While a Variant can hold a String, it can also contain any other data type and therefore is not a valid argument if a String is expected.

    You can solve this by either declaring codes as String (if this is feasable in your case) or convert the content to a String when you call regexSearch

    msgCount = regexSearch(".+:\W+(\d+)", CStr(codes))