switch or case statement for excel spreadsheet 1

[electricpete]

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.

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

=====================================
(2B)+(2B)' ?

 

[IDS]

Thanks Pete.

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/

 

[pmover]

IDS,

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

 

[IDS]

a simple task to copy the text and paste into Module.

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.

although it has been awhile (years) since writing some VBA, i seem to recall a select case or case is statement

Yes, there is a Select Case statement in VBA.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Doug - the cut/paste works ok for me and even preserves the indentation. But anyway I have put it into a spreadsheet and uploaded it here if that helps.

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)' ?

 

[IDS]

But anyway I have put it into a spreadsheet and uploaded it here if that helps.

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/