Questions

How would one code a case statement in Excel VBA to do the following?

+
0 Votes
Locked

How would one code a case statement in Excel VBA to do the following?

wagara1
I am attempting to export excel spreadsheets to text files using different file layouts based on the information in the spreadsheet being exported. I have a total of five spreadsheets. My feeble attempts at using a case statement for this, have led to little more than a spike in my blood pressure. Any insight/examples of this would be appreciated.
  • +
    0 Votes
    Slayer_

    Is your trouble figuring out what information to case select on? Or the actual syntax of a select case. Or are you trying to write a complicated one.

    A common confusion is trying to use a case statement where an elseif would be better. But I find case statements easier to read. So to make a case statement behave like an elseif. Do this.

    [pre]
    Select Case True 'This will make VBA stop on the first true case statement
    case cell(1,2)="Test sheet 1"
    case cell(1,2)="Test sheet 2" and cell (3,4) = "Important test"
    case msgbox("Wasn't first case, should we keep trying?",[params]) = vbno 'If user picks no, case stops cause it evaluates to true
    case FunctionName 'this is a function that returns true or false, a return of true ends the case statement
    End Select
    [/pre]


    The use of this method over an elseif becomes clear when you have 50 or more possibilities.

    That is just some of the neat... abusive ways to use a case statement in VBA.

  • +
    0 Votes
    Slayer_

    Is your trouble figuring out what information to case select on? Or the actual syntax of a select case. Or are you trying to write a complicated one.

    A common confusion is trying to use a case statement where an elseif would be better. But I find case statements easier to read. So to make a case statement behave like an elseif. Do this.

    [pre]
    Select Case True 'This will make VBA stop on the first true case statement
    case cell(1,2)="Test sheet 1"
    case cell(1,2)="Test sheet 2" and cell (3,4) = "Important test"
    case msgbox("Wasn't first case, should we keep trying?",[params]) = vbno 'If user picks no, case stops cause it evaluates to true
    case FunctionName 'this is a function that returns true or false, a return of true ends the case statement
    End Select
    [/pre]


    The use of this method over an elseif becomes clear when you have 50 or more possibilities.

    That is just some of the neat... abusive ways to use a case statement in VBA.