VBA Switch statement

[John4bank]

How do I set a default value in a switch statement?

I have the following code (this is an abbreviated version of it):
SwitchNum = Switch(x = "P1003On", 1, x = "P1002On", 2, x = "P2022",
3)

The above code works perfectly except for giving me a default value.

I want if x doesn't equal any of these values it to default to equaling 999,
but I am not sure how to set a default value in VBA switch statements. I am a
C++ developer, and am trying to teach myself the syntax of VBA.

Thanks,
John

 

[JulieS]

Hello John,

I don't believe the Switch function contains an "else" statement. Try
an IIf function instead. Syntax:

IIf( /expr/, /truepart/, /falsepart/ )

You can nest IIf statements within one another. So in your example:
IIf(x="P1003On", 1, IIf(x="P1002On", 2, IIf(x="P2022", 3,999)))

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[JulieS]

Hi again John,

I'm not sure of why you are doing the process, but if you simply want to
set the value of a custom field based upon the value of another custom
field, consider using a formula in the field instead of running code.
The formula I wrote in the last post works well in a text field and
constantly calculates and updates if the value in the referred field is
changed.

Julie
Project MVP

 

[Assaf]

Hi,
You can add a final condition of true and its value:

Something like that:
j = Switch( _
expr1 , 2, _
expr2, 5, _
expr3, 8, _
True, 9)

 

[John4bank]

Assaf, Thanks so much, the true statement was just what I was looking to have.

Julie, I had orginally built it using if statements, but I wanted to cleanup
the code and do it correctly that is why I was asking about the switch
statement.

 

[John4bank]

Here is what the finished product looks like:

SwitchNum = Switch(x = "P1003On", P1003On, x = "P1002On", P1002On, x
= "P1001On", P1001On, x = "P1000On", P1000On, x = "P1004On", P1004On, x =
"P1005On", P1005On, x = "P2001", P2001, x = "P1006On", P1006On, x =
"P1003Off", P1003Off, x = "P1002Off", P1002Off, x = "P1001Off", P1001Off, x =
"P1000Off", P1000Off, x = "P1004Off", P1004Off, x = "P1005Off", P1005Off, x =
"P1006Off", P1006Off, x = "P2000", P2000, x = "P2022", P2022, True, 999)
If SwitchNum = "999" Then
SelectRow p, rowrelative:=False
FontEx Color:=15 ' 15 = a font color of grey
Else
Rsr.CostRateTables("A").PayRates.Add iDate, SwitchNum,
SwitchNum, "0"


Just incase anyone needs to do something similar in the future.

 

[JulieS]

Great John. I'm glad that Assaf's solution worked. Thanks for sharing
your code as well.

Julie
Project MVP

 

[jack dahlgren]

With that many conditions I'd use a case statement instead for readability
and maintainability.

-Jack

 

[JulieS]

Jack,
Glad you expressed that opinion. When I saw John's finished switch
statement I thought a case statement would be a better call too, but
hesitated to express it. I'm glad one of the VBA "gurus" weighed in.
Julie

 

[John4bank]

Alright, thanks. I will replace it with a case statement.