atw: Re: VBA Select Case Statement question. [SEC=UNCLASSIFIED]

Hi Howard/Michael,

That was the conclusion I came to as well (particularly after trawling the 
Web for an hour or so) but I was just hoping.

I typically use case statements as single expression validations but I'm 
doing code cleanup and was trying to compact a few dozen if-else 
statements into equivalent case statements so there is no point in putting 
if-else under a case statement (although I started to do that as well 
before I realised what I was doing and slapped myself on the side of the 
head  :)).

I then started entertaining the notion of nested case statements but came 
to the same conclusion.

Oh well, it was worth the question.

Thanks for the confirmation guys. I won't waste any more time on the 
windmill.

Cheers,

Bruce Ashley
----------------------------------
MYOB Australia
bruce.ashley@xxxxxxxx
Ext 7160
----------------------------------



Howard.Silcock@xxxxxxxxxxx 
Sent by: austechwriter-bounce@xxxxxxxxxxxxx
23/08/2007 01:06 PM
Please respond to
austechwriter@xxxxxxxxxxxxx


To
austechwriter@xxxxxxxxxxxxx
cc

Subject
atw: Re: VBA Select Case Statement question. [SEC=UNCLASSIFIED]







Hi Bruce 

When you want to start a Select... with the a statement 

    Select Case priority 

VBA expects each of the following Case statements to specify a possible 
value (or a range of values) for priority.  So you can have Case 
statements like 

    Case "Priority 1" 

introducing actions to take place when priority = "Priority 1", 
or (if priority were a numerical variable) 

    Case 1 To 4 
  
introducing actions to take place when 1 <= priority <= 4, or 

    Case Is <= 10  (the Is operator is to express open-ended ranges) 

introducing actions to take place when priority <= 10. 

So somehow you'll have to sum up the cases you want to distinguish in 
terms of a single variable, or else just use something like 

    Case "Priority 1" 
     If (tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.166664")  then 
      tempArray(i, 3) = "1" 
     End if 

  Case "Priority 2" 
      If (tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.333328")  Then 

         tempArray(i, 3) .. 
      End If 
      
   etc 

Hope that helps 

Howard 


Howard Silcock
Technical Writer
Zare Pty Ltd
Ph  02 6261 2073
Fax 02 6112 2073 


"   


bruce.ashley@xxxxxxxx 
Sent by: austechwriter-bounce@xxxxxxxxxxxxx 
23/08/2007 12:31 PM 

Please respond to
austechwriter@xxxxxxxxxxxxx


To
austechwriter@xxxxxxxxxxxxx 
cc

Subject
atw: VBA Select Case Statement question. [SEC=UNCLASSIFIED]








Hi everyone. 

Using VBA (via Excel 2003), I'm having some issues with a Select Case 
statement I'm trying to put together. 

In the past I have never had to test for multiple expressions of the type 
shown below and have come across what appears to be an annoying limitation 
with the validation of expressions (but I'm hoping someone out there will 
advise otherwise). 

Only the first expression is validated so the second and third ones are 
ignored. 

As an 'if else' statement, these multiple expressions work fine but I 
cannot find out how to validate the Case statement equivalents as follows: 


        Select Case priority 

           Case Priority = "Priority 1" And _ 
               (tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.166664") 

               tempArray(i, 3) = "1" 
                
           Case Priority = "Priority 2" And _ 
               (tempArray(i, 2) > "0") And (tempArray(i, 2) <= "0.333328") 

               tempArray(i, 3) ... etc 

I've tried many variations such as: Case "Priority 1", (tempArray(i, 2) > 
"0") , (tempArray(i, 2) <= "0.166664") etc but to no avail. 

It appears that  you cannot add variables to the Select statement ie 
Select Case (priority, tempArray). 

Anyone know what the rules are here (I have no reference material other 
than the Web and that has been of little help today)? 

TIA. 

Cheers, 

Bruce Ashley 
---------------------------------- 
MYOB Australia 
bruce.ashley@xxxxxxxx 
Ext 7160 
----------------------------------

Other related posts: