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

Hi again

After re-reading my own reply to Bruce's question, I just noticed that the 
code (which I pasted from the original) contained statements like 

   tempArray(i, 2) > "0"
 tempArray(i, 3) = "1"

I don't suppose you posted your code for it to be critically analysed, 
Bruce, but I can't help wondering why the quotes are round the zero and 
the one. If tempArray is declared as an array of Variants (that is, if 
your declaration looked something like Dim tempArray(50, 10)), then giving 
it quoted numerical values might lead to some strange results - for 
example,in VBA the statement "20" > "3" evaluates to False, because the 
quotes make VBA interpret the comparison as an alphabetical comparison of 
strings. (On the other hand, 20 > 3 evaluates to True, as you'd expect.) 
I'd advise you to declare tempArray as an array of numerical type (e.g. 
Dim tempArray(50, 10) As Single) and then you can be sure the values are 
all treated as numbers. (If you did that, the assignment tempArray(i, 3) = 
"1" would cause a compile error, so you'd be forced to replace "1" with 1. 
But I think that's what you really want anyway.)

Just an observation that I thought might be worth mentioning in case you 
do get strange results!

Howard





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: