Answer for:

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

Message 4 of 3

View entire thread
0 Votes

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.

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

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.