×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

switch or case statement for excel spreadsheet

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

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

RE: switch or case statement for excel spreadsheet

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/
 

RE: switch or case statement for excel spreadsheet

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

RE: switch or case statement for excel spreadsheet

Quote:

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.

Quote:

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/
 

RE: switch or case statement for excel spreadsheet

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

RE: switch or case statement for excel spreadsheet

Quote:

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
The Great Project Profitability Debate
A/E firms have a great opportunity to lead the world into the future, but the industry’s greatest asset—real-time data—is sitting wasted in clunky, archaic ERP platforms. Learn how real-time, fully interactive dashboards in a modern ERP allow you to unlock data that will shape the future of the world. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close