+ Reply to Thread
Results 1 to 10 of 10

Better way for writing IF statement ?

  1. #1
    monir
    Guest

    Better way for writing IF statement ?

    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.

  2. #2
    macropod
    Guest

    Re: Better way for writing IF statement ?

    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

    --
    macropod
    [MVP - Microsoft Word]


    "monir" <[email protected]> wrote in message
    news:[email protected]...
    > 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.




  3. #3
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187
    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

  4. #4
    monir
    Guest

    Re: Better way for writing IF statement ?

    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.

    "tony h" wrote:

    >
    > 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
    >
    >
    > --
    > tony h
    > ------------------------------------------------------------------------
    > tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
    > View this thread: http://www.excelforum.com/showthread...hreadid=548020
    >
    >


  5. #5
    macropod
    Guest

    Re: Better way for writing IF statement ?

    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]


    "monir" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >
    > "tony h" wrote:
    >
    > >
    > > 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
    > >
    > >
    > > --
    > > tony h
    > > ------------------------------------------------------------------------
    > > tony h's Profile:

    http://www.excelforum.com/member.php...o&userid=21074
    > > View this thread:

    http://www.excelforum.com/showthread...hreadid=548020
    > >
    > >




  6. #6
    Don Guillett
    Guest

    Re: Better way for writing IF statement ?

    Not limited in formulas either. Several workarounds such as using +
    However, I still don't understand the problem due to a lack of enough info.
    Are you trying to do a loop for many cells where the cell is the myvar. Give
    us the entire picture.

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "macropod" <[email protected]> wrote in message
    news:[email protected]...
    > 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]
    >
    >
    > "monir" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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.
    >>
    >> "tony h" wrote:
    >>
    >> >
    >> > 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
    >> >
    >> >
    >> > --
    >> > tony h
    >> > ------------------------------------------------------------------------
    >> > tony h's Profile:

    > http://www.excelforum.com/member.php...o&userid=21074
    >> > View this thread:

    > http://www.excelforum.com/showthread...hreadid=548020
    >> >
    >> >

    >
    >




  7. #7
    monir
    Guest

    Re: Better way for writing IF statement ?

    macropod;

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


    "macropod" wrote:

    > 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]
    >
    >
    > "monir" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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.
    > >
    > > "tony h" wrote:
    > >
    > > >
    > > > 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
    > > >
    > > >
    > > > --
    > > > tony h
    > > > ------------------------------------------------------------------------
    > > > tony h's Profile:

    > http://www.excelforum.com/member.php...o&userid=21074
    > > > View this thread:

    > http://www.excelforum.com/showthread...hreadid=548020
    > > >
    > > >

    >
    >
    >


  8. #8
    monir
    Guest

    Re: Better way for writing IF statement ?

    Hi Don;

    There's no problem! My initial inquery dealt with whether someone knows a
    better or a more intelligent way (in VBA) of writing the sample:
    IF myVar1 >= 1 And myVar1 < 100 And myVar1 <> 25 And myVar1 <= Index And
    myVar1 .... Then GoTo myLable
    instead of repeating the same variable name over and over and over again in
    the statement.

    Regards.



    "Don Guillett" wrote:

    > Not limited in formulas either. Several workarounds such as using +
    > However, I still don't understand the problem due to a lack of enough info.
    > Are you trying to do a loop for many cells where the cell is the myvar. Give
    > us the entire picture.
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "macropod" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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]
    > >
    > >
    > > "monir" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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.
    > >>
    > >> "tony h" wrote:
    > >>
    > >> >
    > >> > 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
    > >> >
    > >> >
    > >> > --
    > >> > tony h
    > >> > ------------------------------------------------------------------------
    > >> > tony h's Profile:

    > > http://www.excelforum.com/member.php...o&userid=21074
    > >> > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=548020
    > >> >
    > >> >

    > >
    > >

    >
    >
    >


  9. #9
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187
    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

  10. #10
    Don Guillett
    Guest

    Re: Better way for writing IF statement ?

    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
    [email protected]
    "monir" <[email protected]> wrote in message
    news:[email protected]...
    > macropod;
    >
    > Sorry, I misspoke! The limit of max 9 IFs is for the nested w/s IF
    > function.
    > My apologies!
    >
    >
    > "macropod" wrote:
    >
    >> 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]
    >>
    >>
    >> "monir" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > 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.
    >> >
    >> > "tony h" wrote:
    >> >
    >> > >
    >> > > 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
    >> > >
    >> > >
    >> > > --
    >> > > tony h
    >> > > ------------------------------------------------------------------------
    >> > > tony h's Profile:

    >> http://www.excelforum.com/member.php...o&userid=21074
    >> > > View this thread:

    >> http://www.excelforum.com/showthread...hreadid=548020
    >> > >
    >> > >

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1