Excel 2010 VBA "Invalid Procedure Call or Argument" error in regex function

Question

I'm working with the following RegEx function in Excel 2010 and am getting the "Invalid Procedure Call or Argument" error on the last line of the function. I substituted the ActiveCell.Value for the constant (commented out). The constant did work properly, although the cell value does not.
What is causing this error to occur?
I appreciate any help in this. Thanks.

Sub SUB1()
Dim c As Variant
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
'MsgBox (c)
    If RE6(c.Value) Then
         c.Interior.ColorIndex = 7
         Else
         c.Interior.ColorIndex = 6
    End If
Next
End Sub


Sub Test()
'Const strTest As String = "qwerty123456uiops"
Dim strTest As String
strTest = ActiveCell.Value
MsgBox RE6(strTest)
End Sub


Function RE6(strData As String) As String
Dim RE As Object
Dim REMatches As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .MultiLine = False
    .Global = False
    .IgnoreCase = True
    .Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9]"
End With

Set REMatches = RE.Execute(strData)
MsgBox ("REMatches.Count" & REMatches.Count)
'If Not REMatches Is Nothing Then
    If REMatches.Count <= 0 Then
    RE6 = ""
    Else
    RE6 = REMatches(0)
    End If
'Else

'End If


End Function

When you run this with ActiveCell.Value, does the active cell contain the text qwerty123456uiops? — phoog
– phoog, Commented Mar 28, 2012 at 16:18
If you intend to remove caracters from alphanumeric values, this can be done using an Excel Formula (office.microsoft.com/en-us/excel-help/…). — Francis P
– Francis P, Commented Mar 28, 2012 at 17:42
Thanks - I changed the pattern to [A-Z], and that came back with a result. My initial pattern was different, and that resulted in the error. I wouldn't have thought that a change in pattern could result in an error. Maybe the data type? — buck1112
– buck1112, Commented Mar 28, 2012 at 18:59
The error is due to accessing index 0 of REMatches when REMatches is empty. You should verify is REMatches is not empty before accessing the index, to prevent eventual errors. — Francis P
– Francis P, Commented Mar 28, 2012 at 19:34

Tim Williams · Accepted Answer · 2012-03-28 22:32:22Z

4

Most likely there is no match: if you test the .Count property of REMatches is it zero?

Your function should test for that and return a suitable value (empty string maybe) instead.

EDIT: if you only want to check for the presence or absence of a pattern, then using .Test() is easier than using .Execute(). I changed your function to return a Boolean, which is more natural in this type of case.

Sub CheckCellValues()
    Dim c As Range
    For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
        If RE6(c.Value) Then
            c.Interior.ColorIndex = 7
        Else
            c.Interior.ColorIndex = 6
        End If
    Next
End Sub

Function RE6(strData As String) As Boolean
    Dim RE As Object
    Dim REMatches As Object
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9]"
    End With
    RE6 = RE.Test(strData) 'much simpler...
    'or...
    'REMatches = RE.Execute(strData)
    'RE6 = (REMatches.Count > 0)
End Function

edited Mar 28, 2012 at 22:32

answered Mar 28, 2012 at 17:33

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

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

6 Comments

buck1112 Over a year ago

Thanks - I did do the test, and yes, it came back as zero.

Tim Williams Over a year ago

No, I believe it's a Collection

buck1112 Over a year ago

Is the index zero-based? In effect, if the count came back as zero, would the index be -1 or just non-existant?

Tim Williams Over a year ago

It's zero-based, but if the collection is empty then there's no "zero'th" item. It's not an array, so it doesn't have bounds - maybe that's what you're referring to?

buck1112 Over a year ago

Yes, I think I was thinking that 'zero' was the first item, but that the count would equal 'one'.

|

brettdj · Accepted Answer · 2012-03-28 22:35:33Z

1

Your code appears to be aimed at testing whether a consecutive 6 digit number occurs in each cell in Sheet1 A1:D10, ie you are looking for a Boolean True/False so

Use a simpler pattern Re.Pattern = "[0-9]{6}"
Use the Regexp Test method - you don't need a collection of matches, just to know if one (as Re.Global = False) exists

Return a Boolean result from your function

Function RE6(strData As String) As Boolean
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .MultiLine = False
    .Global = False
    .IgnoreCase = True
    .Pattern = "[0-9]{6}"
    RE6 = .Test(strData)
End With
End Function

answered Mar 28, 2012 at 22:35

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

2 Comments

buck1112 Over a year ago

Would the 'RE.Execute' way be only if I did want that collection of matches? ..and '.Test' only if I want one?

brettdj Over a year ago

@buck1112 'Test' indicates if the regexp pattern is matched in the string of interest. If you want to either work with the first/collection of matches, or replace the first/colllection of matches insitu then you need 'Execute' or 'Replace' respectively. In your case you have set 'RE.Global = False' so your code will only retrun the first match - not a collection. You will need 'Re.Global=True' to work with a collection of matches

Collectives™ on Stack Overflow

Excel 2010 VBA "Invalid Procedure Call or Argument" error in regex function

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related