CODEWITHSUNDEEP
regexarraysvbaexcel
franklin
franklin

Reputation: 1819

send regexp matches to an array of strings

I'm trying to get the code below to send the results of the regexp search to an array of strings. How can I do that?

When I change name to an array of strings i.e. Dim name() as String VBA throws a type-mismatch exception. Any idea what I can do to fix that?

Many thanks.

 Do While Not EOF(1)
    Line Input #1, sText
    If sText <> "" Then


       Dim Regex As Object, myMatches As Object

       ' instantiates regexp object
       Set Regex = CreateObject("VBScript.RegExp")
       With Regex
            .MultiLine = False
            .Global = True
            .IgnoreCase = False
            .Pattern = "^Personal\sname\s*[:]\s*"
       End With

       ' get name, seperated from Personal Name
       If Regex.test(sText) Then

            Set myMatches = Regex.Execute(sText)
            Dim temp As String
            temp = Regex.Replace(sText, vbNullString)
            Regex.Pattern = "^[^*]*[*]+"
            Set myMatches = Regex.Execute(temp)
            Dim temp2 As String
            temp2 = myMatches.Item(0)
            name = Trim(Left(temp2, Len(temp2) - 3))

        End If
    End If
Loop

Upvotes: 0

Views: 2514

Answers (2)

Rich Tier
Rich Tier

Reputation: 9441

change

'call this "A"
Dim temp2 As String
temp2 = myMatches.Item(0)

to

'stick this at the top
 redim temp2(0 to 0)

'replace "A" with this
 new_top = ubound(temp2)+1
 redim preserve temp2 (0 to new_top)
 temp2(new_top) = myMatches.Item(0)

Upvotes: 0

Reafidy
Reafidy

Reputation: 8471

You should not use "name" as a variable name as it conflicts with an excel property. Try sName or sNames instead, where s is for string.

With a array you need to give it a size before you can assign a value to each element.

Dim sNames(4) As String  '// Or Dim sNames(1 To 4) As String

sName(1) = "John"
...
sName(4) = "Sam"

or if you don't know the total number of elements (names) to begin with then:

Dim sNames() As String
Dim iTotalNames As Integer

iTotalNames = '// Some code here to determine how many names you will have

ReDim sNames(iTotalNames) '// You can also use ReDim Preserve if you have existing elements

sName(1) = "John"
...
sName(4) = "Sam"

So I suspect you will need something like:

  Dim sNames() As String
  Dim iTotalNames As Integer

  '// Your code ....

  iTotalNames = iTotalNames + 1
  ReDim Preserve sNames(iTotalNames)
  sNames(iTotalNames) = Trim(Left(temp2, Len(temp2) - 3))

  '// Rest of your code ...

Also in VBA all dimensioning of variables should be at the top of the module.

Upvotes: 3

Related Questions