Sets of Characters in Excel VBA

[aussie_craig]

Hi,

I want to compare a single character to see if it is one of severa
characters. eg 1 to 9. I thought VBA supported sets ?

ie Assuming MyString is 1 charcter in length, Instead of doing this:
If MyString = "1" or MyString = "2" or MyString = "3" etc..... The
...

Can I do:
If MyString = {1..9} Then ....
Excel VBA doesn't like the curly bracket, it spits any error.

Does anyone know how to compare of value to a set or list like this ?

Thanks in Advance for any help.
Craig

 

[Rob van Gelder]

Here is an approach you could alter for your needs:

Sub test()
Dim MyString As String, bln As Boolean

MyString = "4"

Select Case MyString
Case "1", "3", "5", "7", "9": bln = True
Case Else: bln = False
End Select

If bln Then
MsgBox "1 to 9"
End If
End Sub


You could also change to:
Case 1 To 9: bln = True

 

[Charles Williams]

Hi Craig,

You can also use INSTR
strList="123456789"
if instr(strList,MyString)>0 then

or LIKE
if MyString like "[1-9]" then


--
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Rob van Gelder]

Hey cool!
That's like regular expressions. When did VBA get that?!

--
Rob van Gelder - http://www.vangelder.co.nz/excel

Hi Craig,

You can also use INSTR
strList="123456789"
if instr(strList,MyString)>0 then

or LIKE
if MyString like "[1-9]" then


--
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Hi,

I want to compare a single character to see if it is one of several
characters. eg 1 to 9. I thought VBA supported sets ?

ie Assuming MyString is 1 charcter in length, Instead of doing this:
If MyString = "1" or MyString = "2" or MyString = "3" etc..... Then
..

Can I do:
If MyString = {1..9} Then ....
Excel VBA doesn't like the curly bracket, it spits any error.

Does anyone know how to compare of value to a set or list like this ?

Thanks in Advance for any help.
Craig.


--
aussie_craig
------------------------------------------------------------------------
aussie_craig's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=15491
View this thread:
http://www.excelforum.com/showthread.php?threadid=270714

 

[Myrna Larson]

Several versions ago <bg>.

 

[Charles Williams]

It was there in Excel Version 5 (I just checked)