string matching

[Alex Lee]

Dear all:
I have newbie question on how to match strings with excel macro.
What I want is to match a word against different elements of an array
and do something with it if matches.

Lets say I have an array like the one below and now I want to output
(msgbox) every time the string "apple" appears. How would I do this?
In perl it would be trivial with something like =~ /.apple./

----------------------------------------------
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"

For x = 1 To 3
If arr(x) = "*apple*" Then
msgbox arr(x)
end if

Next
----------------------------------------------


Unfortunenly the code above does not work. Any help?

thanks. alex

 

[Norman Jones]

Hi Alex,

Try something like:

'=============>>
Public Sub Tester()
Dim x As Long
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"

For x = 1 To 3
If InStr(1, arr(x), "apple", vbTextCompare) Then
MsgBox arr(x)
End If

Next x
End Sub
'<<=============

 

[Norman Jones]

Hi Alex,

I should, however, have added that, unless you have
declared Option Base as 1 at the head of your module,
your instruction:

ReDim arr(3)

is equivalent to:

ReDim arr(0 to 3)

 

[Rick Rothstein \(MVP - VB\)]

I have newbie question on how to match strings with excel macro.

What I want is to match a word against different elements of an array
and do something with it if matches.

Lets say I have an array like the one below and now I want to output
(msgbox) every time the string "apple" appears. How would I do this?
In perl it would be trivial with something like =~ /.apple./

----------------------------------------------
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"

For x = 1 To 3
If arr(x) = "*apple*" Then
msgbox arr(x)
end if

Next
----------------------------------------------

You could use the InStr method Norman posted, or one that looks almost like
a regular expression...

For x = 1 To 3
If arr(x) Like "*[aA]pple*" Then
MsgBox arr(x)
End If
Next x

where the [aA] allows "apple" to start with either a lower or upper case
"A".

Another possibility is to use the Filter function to create an array of just
elements from the original array that contain the search word (apple, in
your case) and also note the alternate way of assigning values to your
array...

Public Sub Tester()
Dim X As Long
Dim Arr() As String
Dim SubArr
Arr = Split("apple tree|I like apples|pears are good for you", "|")
SubArr = Filter(Arr, "apple", , vbTextCompare)
For X = 0 To UBound(SubArr)
MsgBox Arr(X)
Next X
End Sub


Rick

 

[Rick Rothstein \(MVP - VB\)]

Typed too fast... see inline correction.

Another possibility is to use the Filter function to create an array of
just elements from the original array that contain the search word (apple,
in your case) and also note the alternate way of assigning values to your
array...

Public Sub Tester()
Dim X As Long
Dim Arr() As String
Dim SubArr

The above should be... Dim SubArr() As String

Arr = Split("apple tree|I like apples|pears are good for you", "|")
SubArr = Filter(Arr, "apple", , vbTextCompare)
For X = 0 To UBound(SubArr)
MsgBox Arr(X)
Next X
End Sub

Rick

 

[Alex Lee]

Hi Norman:
that totally worked! thanks. I've searched google for an hr but could
not find a solution.

alex

 

[Alex Lee]

Rick,
thanks for your help. You are right about this method being more
regular expression like. Actually I prefer this method because of the
similiartiy to regex.

alex

I have newbie question on how to match strings with excel macro.
What I want is to match a word against different elements of an array
and do something with it if matches.

Lets say I have an array like the one below and now I want to output
(msgbox) every time the string "apple" appears. How would I do this?
In perl it would be trivial with something like =~ /.apple./

----------------------------------------------
Dim arr() As Variant
ReDim arr(3)
arr(1) = "apple tree"
arr(2) = "I like apples"
arr(3) = "pears are good for you"

For x = 1 To 3
If arr(x) = "*apple*" Then
msgbox arr(x)
end if

Next
----------------------------------------------

You could use the InStr method Norman posted, or one that looks almost like
a regular expression...

For x = 1 To 3
If arr(x) Like "*[aA]pple*" Then
MsgBox arr(x)
End If
Next x

where the [aA] allows "apple" to start with either a lower or upper case
"A".

Another possibility is to use the Filter function to create an array of just
elements from the original array that contain the search word (apple, in
your case) and also note the alternate way of assigning values to your
array...

Public Sub Tester()
Dim X As Long
Dim Arr() As String
Dim SubArr
Arr = Split("apple tree|I like apples|pears are good for you", "|")
SubArr = Filter(Arr, "apple", , vbTextCompare)
For X = 0 To UBound(SubArr)
MsgBox Arr(X)
Next X
End Sub

Rick- Hide quoted text -

- Show quoted text -

 

[Rick Rothstein \(MVP - VB\)]

thanks for your help. You are right about this method being more

regular expression like. Actually I prefer this method because of the
similiartiy to regex.

Just so you know, though, the InStr test is "faster" than the Like operator
comparison; but you wouldn't see the difference unless you were running a
huge loop. You might want to look again at the Filter function as that is
remarkably flexible in finding sub-strings within String arrays.

Rick

 

[Rick Rothstein \(MVP - VB\)]

You might want to look again at the Filter function as that is remarkably

flexible in finding sub-strings within String arrays.

And the reason why is that you don't have to examine each individual array
element in a loop looking for matching substrings... true, Filter must do
that behind the scenes, but it is so much quicker in doing so than a VBA
loop can ever be.

Rick