Better way for writing IF statement ?

[monir]

Hello;

Is there a better (more intelligent) way of writing the following sample
single-line IF statement in Excel VBA procedure (macro):

IF myVar1 >= 1 And myVar1 < 100 And myVar1 <> 25 And myVar1 <= Index Then
GoTo myLable

rather than repeating the same variable name over and over and over again ??

Does With structure allow comparison operators ??

Thank you kindly.

 

[macropod]

Hi Monir,

You have to test each condition individually, but testing them all on one
line like this makes for slow execution, since every condition has to be
tested before the code can move on. Quicker would be:
If myVar1 >= 1 Then
If myVar1 < 100 Then
If myVar1 <> 25 Then
If myVar1 <= Index Then GoTo myLable
End If
End If
End If

If you re-order the above so that the conditions least likely to be
satisfied are tested first, that will further reduce execution time.

Cheers

 

[tony h]

Macropod is quite right but I wondered, Monir, whether there is more
behind the question. EG are you having to repeat this lots of times? If
so post some more description of the issue

Cheers

 

[monir]

Hi;

Since it's the same variable myVar1 that I'm testing, I thought there might
be a way (documented or improvised) that could be applied in this situation.
Something like inserting: "." or " ," or ":=" or "/" or whatever!

I originally had a nested IF similar to yours, but realized then that one
is limited to a max of 9 tested conditions within the nested IF
(ocassionally. there're more conditions!). So, I switched to a single-line
IF, where applicable, regardless of the number of conditions.

In some situations, the nested IF is the preferable (if not the only) way to
do the tests, where for each satisfied condition(s), there're different
computational procedures to follow.

Back to my sample:
IF myVar1 >= 1 And myVar1 < 100 And myVar1 <> 25 And myVar1 <= Index Then
GoTo myLable

How about:

Select Case myVar1
Case >= 1, < 100, <> 25, <= Index
GoTo myLable
End Select

with the above commas (which represent OR) are replaced by AND (or something
equivelant) ?? I've done a dry run. It didn't work.

Regards.

 

[macropod]

Hi monir,

Why do you think you're limited to 9 nested IFs? There's a worksheet limit
of 7, but that doesn't apply to vba. I just ran a test with 12 nested vba
IFs in both Word and Excel and got the expected result with no complaint
from vba about any limits being exceeded.

Cheers

 

[tony h]

If the object is to simplify coding (ie make it less prone to typing
errors) there are several ways of doing this but you have not provided
any wider cntext for the issue so it is difficult to advise.

Options include:
- using select statements
- writing a functions that returns a true/false and does the test
internally
- use a class module in a similar way

I have used all these methods in various circumstances.

regards

 

[Don Guillett]

See my post. The WORKSHEET limit for nested if's is 7 but there are several
workarounds. However, it is best then to use a lookup table or another
method.

If you would fully explain your problem perhaps someone could be able to
help. No mind reading here

--
Don Guillett
SalesAid Software
(e-mail address removed)

macropod;

Sorry, I misspoke! The limit of max 9 IFs is for the nested w/s IF
function.
My apologies!

Hi monir,

Why do you think you're limited to 9 nested IFs? There's a worksheet
limit
of 7, but that doesn't apply to vba. I just ran a test with 12 nested vba
IFs in both Word and Excel and got the expected result with no complaint
from vba about any limits being exceeded.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi;

Since it's the same variable myVar1 that I'm testing, I thought there might
be a way (documented or improvised) that could be applied in this situation.
Something like inserting: "." or " ," or ":=" or "/" or whatever!

I originally had a nested IF similar to yours, but realized then that
one
is limited to a max of 9 tested conditions within the nested IF
(ocassionally. there're more conditions!). So, I switched to a single-line
IF, where applicable, regardless of the number of conditions.

In some situations, the nested IF is the preferable (if not the only)
way to
do the tests, where for each satisfied condition(s), there're different
computational procedures to follow.

Back to my sample:
IF myVar1 >= 1 And myVar1 < 100 And myVar1 <> 25 And myVar1 <= Index
Then
GoTo myLable

How about:

Select Case myVar1
Case >= 1, < 100, <> 25, <= Index
GoTo myLable
End Select

with the above commas (which represent OR) are replaced by AND (or something
equivelant) ?? I've done a dry run. It didn't work.

Regards.

:


Macropod is quite right but I wondered, Monir, whether there is more
behind the question. EG are you having to repeat this lots of times?
If
so post some more description of the issue

Cheers