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: