Error when running VBA macro on different computers

Question

I have written a macro in VBA that is working perfectly for my file. The problem is that when I am running this macro (of the same file) on a different computer, I get the following error:

Invalid procedure call or argument (Error 5)

... when executing .Item(0).Value in the following code:

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([a-zA-Zá-ùÁ-Ù])(\d+)"
    With .Execute(strText)
        strRes = .Item(0).Value

I suspect that maybe something is dependent on the specific Workbook or file path (although I cannot find the dependency), but I cannot resolve this weird behavior.

What application (Word, Excel, ...), and what versions, on each machine? — cxw, Jul 14 '16 at 18:59
Based on [error description](https://msdn.microsoft.com/en-us/library/aa445484(v=vs.60).aspx) the most common reason of above error is that: index exceeds the range... — Maciej Los, Jul 14 '16 at 19:00
"of the same file" - what's the value of `strRes` when it blows up? — Mathieu Guindon, Jul 14 '16 at 19:13

score 4 · Answer 1 · answered Jul 14 '16 at 19:31

It practically means that the string strText has no match with the regular expression. As mentioned in the docs:

Execute returns an empty Matches Collection if no match is found.

So this means .Item(0) does not exist and is an invalid reference:

strRes = .Item(0).Value

To solve this, test that there is at least one element in the collection using the Count property:

With .Execute(strText)
    strRes = "" ' Default value
    If .Count > 0 Then strRes = .Item(0).Value

Depending on what your code does with strRes, you might want take a different action when Count is 0. The above code tries to continue with the empty string, but maybe you want to exit a function, or display a message to the user, or still something else...

Error when running VBA macro on different computers

1 Answers1