Sets of Characters in Excel VBA
 

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

--
aussie_crai
-----------------------------------------------------------------------
aussie_craig's Profile: http://www.excelforum.com/member.php...fo&userid=1549
View this thread: http://www.excelforum.com/showthread.php?threadid=27071

Sets of Characters in Excel VBA
 

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


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


"aussie_craig" wrote in message
...

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...o&userid=15491
View this thread: http://www.excelforum.com/showthread...hreadid=270714

Sets of Characters in Excel VBA
 

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
"aussie_craig" wrote in message
...

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...o&userid=15491
View this thread: http://www.excelforum.com/showthread...hreadid=270714

Sets of Characters in Excel VBA
 

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

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


"Charles Williams" wrote in message
...
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
"aussie_craig" wrote in
message ...

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...o&userid=15491
View this thread:
http://www.excelforum.com/showthread...hreadid=270714

Sets of Characters in Excel VBA
 

Several versions ago <bg.

On Wed, 20 Oct 2004 20:37:49 +1300, "Rob van Gelder"
wrote:

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

Sets of Characters in Excel VBA
 

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


"Myrna Larson" wrote in message
...
Several versions ago <bg.

On Wed, 20 Oct 2004 20:37:49 +1300, "Rob van Gelder"
wrote:

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