switch or case statement for excel spreadsheet
switch or case statement for excel spreadsheet
(OP)
I don't know about you, but I have always been annoyed that excel doesn't provide a switch or case statement (at least not that I know of). So I wrote one in vba that can be called from excel. Tends to help avoid complicated nested if logic.
CODE
Function sw(ParamArray invar())
' implement logic similar to switch
' example call
' =switch(boolean1, value1, boolean2, value2, boolean3, value3....)
' returns the value corresponding to the first true boolean
' at least one of the boolean expressions must be true
' requires an even number of arguments
' the syntax is pretty much identical to vba switch, except that there is no explicit allowance for else value
' if you want an else clause, enter true for the next to last argument, followed by the associated value
' Note that indexing of invar starts at 0, regardless of Option Base statement
' Check to confirm even number of arguments (as required)
If UBound(invar) Mod 2 <> 1 Then MsgBox "Error: Need even number of arguments for sw"
Dim ctr As Long ' loop counter
Dim tempswitch As Variant ' variable which will hold the output value
ctr = 0 ' initialize counter
Do While True ' loop until broken by exit command
' Check for boolean input
If VarType(invar(ctr)) <> vbBoolean Then MsgBox "Error 1st 3rd 5th etc arguments of sw must be boolean"
If invar(ctr) Then ' in this case have found a true value, assign function and exit
tempswitch = invar(ctr + 1)
sw = tempswitch
Exit Do
Else ' Else have not found true yet, update counter and continue loop
ctr = ctr + 2
End If
' Check for reaching end of invar without having found true
If ctr + 1 > UBound(invar) Then MsgBox "Error: sw needs at least one true boolean argument"
Loop
End Function
' implement logic similar to switch
' example call
' =switch(boolean1, value1, boolean2, value2, boolean3, value3....)
' returns the value corresponding to the first true boolean
' at least one of the boolean expressions must be true
' requires an even number of arguments
' the syntax is pretty much identical to vba switch, except that there is no explicit allowance for else value
' if you want an else clause, enter true for the next to last argument, followed by the associated value
' Note that indexing of invar starts at 0, regardless of Option Base statement
' Check to confirm even number of arguments (as required)
If UBound(invar) Mod 2 <> 1 Then MsgBox "Error: Need even number of arguments for sw"
Dim ctr As Long ' loop counter
Dim tempswitch As Variant ' variable which will hold the output value
ctr = 0 ' initialize counter
Do While True ' loop until broken by exit command
' Check for boolean input
If VarType(invar(ctr)) <> vbBoolean Then MsgBox "Error 1st 3rd 5th etc arguments of sw must be boolean"
If invar(ctr) Then ' in this case have found a true value, assign function and exit
tempswitch = invar(ctr + 1)
sw = tempswitch
Exit Do
Else ' Else have not found true yet, update counter and continue loop
ctr = ctr + 2
End If
' Check for reaching end of invar without having found true
If ctr + 1 > UBound(invar) Then MsgBox "Error: sw needs at least one true boolean argument"
Loop
End Function
=====================================
(2B)+(2B)' ?
RE: switch or case statement for excel spreadsheet
I didn't even know about the VBA Switch function, so I haven't been missing it, but I'm sure I'll find a use for it.
Do you know if there is a way to copy and paste from here into the VBE without losing all the line breaks?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: switch or case statement for excel spreadsheet
a simple task to copy the text and paste into Module.
although it has been awhile (years) since writing some VBA, i seem to recall a select case or case is statement. i could be wrong as i've CRS (can't remember stuff) lately.
good luck!
-pmover
RE: switch or case statement for excel spreadsheet
When I copy the text and paste it into a module I lose all the line breaks; i.e. the text all goes onto one line.
Yes, there is a Select Case statement in VBA.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: switch or case statement for excel spreadsheet
pmover - the purpose of the function is not to add a new capability to vba... it is to add a new capability to spreadsheets.
=====================================
(2B)+(2B)' ?
RE: switch or case statement for excel spreadsheet
Thanks Pete.
Losing the line breaks is probably an exciting new feature in Excel 2010, or Windows 7, or something.
Serves me right for being a compulsive "up"grader.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/