Link to home
Start Free TrialLog in
Avatar of stmoritz
stmoritz

asked on

Excel VBA question regarding using a string or variable as comparison operator in a IF condition

The user should define a certain number and define the way excel should compare it to a count. How is the correct syntax to code this?

So the user defines for example

Dim UserCountNumber as Double
Dim UserCountComparisonOperator as String

UserCountNumber = 9
UserCountComparisonOperator = ">="

If [xxx.object whatever].Count >= 9 Then...


So instead of using hard coded >= and 9, using the variables...??

Thanks for your help.
Avatar of dlmille
dlmille
Flag of United States of America image

I would do it something like this:

Sub test()

Dim UserCountNumber As Double
Dim UserCountComparisonOperator As String

    UserCountNumber = 9
    UserCountComparisonOperator = ">="

    Select Case UserCountComparisonOperator
        Case Is = "="
            If [xxx.object whatever].Count = 9 then ...
        Case Is = "<"
            If [xxx.object whatever].Count < 9 then ...
        Case Is = ">"
            If [xxx.object whatever].Count > 9 then ...
        Case Is = "<="
            If [xxx.object whatever].Count <= 9 then ...
        Case Is = ">="
            If [xxx.object whatever].Count >= 9 then ...
        Case Is = "<>"
            If [xxx.object whatever].Count <> 9 then ...
    End Select
    

End Sub

Open in new window


Dave
Avatar of stmoritz
stmoritz

ASKER

Thanks Dave. I assume you meant...

   Case Is = "="
            If [xxx.object whatever].Count = UserCountNumber  then ...

Open in new window


right?

Will check and get back asap.
Ahh.  Of course.  Correct.  

Sub test()

Dim UserCountNumber As Double
Dim UserCountComparisonOperator As String

    UserCountNumber = 9
    UserCountComparisonOperator = ">="

    Select Case UserCountComparisonOperator
        Case Is = "="
            If [xxx.object whatever].Count = UserCountNumber then ...
        Case Is = "<"
            If [xxx.object whatever].Count < UserCountNumber then ...
        Case Is = ">"
            If [xxx.object whatever].Count > UserCountNumber then ...
        Case Is = "<="
            If [xxx.object whatever].Count <= UserCountNumber then ...
        Case Is = ">="
            If [xxx.object whatever].Count >= UserCountNumber then ...
        Case Is = "<>"
            If [xxx.object whatever].Count <> UserCountNumber then ...
    End Select
    

End Sub

Open in new window

Hi Dave. I increased to 200, as I have a follow up question regarding the "Then" in this code

If the condition is met, the code should continue after the "Select" if not exit sub.

How to code this?
Then End Select Else Exit Sub?
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ONLY ONE of the Case statement "paragraphs" or blocks will be run.  The one where the condition is met OR if there is a Case Else clause, then that block will be run.  The normal sequence would then continue after the End Select statement.

Cheers,

Dave
Dave. Now, honestly: Do I give to low points? Honestly, tell me. You know, I don't want to inflate this infinitely available currency of ee points. But I also don't want to be a greedy old a.hole. You think I should generally give more? Please tell me. It's important to me as this platform is one of the greatest things on earth, at least on the internet! no joke!  :o)
No worries.  Most folks set the questions around 500 points (just look at the list).  I generally don't even look at the points that are being proposed to be awarded, rather the question and go forth to conquer.

Your choice of points should generally be associated with level of anticipated complexity or urgency needed.  You get more Experts looking (perhaps) with more points.

At the end of the day its your call.

I'm ok answering followup questions without the teaser of adding more points.  Your comment on increasing points made me look for the first time at the points proposed and I just smiled - and shared my smile with you.

No worries, mate!

Dave
So here as example
Select Case UserCountComparisonOperator
        Case Is = "="
            If [xxx.object whatever].Count = UserCountNumber Then
                'do whatever
            End If
        Case Is = "<"
            If [xxx.object whatever].Count < UserCountNumber Then
                'do whatever
            End If

Open in new window


If the first "=" case is true how do I tell him to proceed with "End Select" or "GoTo" and then naming the following code section with that respective name? If none of the conditions/cases is met (which is also possible), the  Sub must be exited.

So again might it work like this:

 If [xxx.object whatever].Count < UserCountNumber Then End Select Else Exit Sub

Open in new window

Only ONE clause will be executed.

Say the first case "=" is the condition that is actually met.

The code will run to either the next Case, or Case Else, or End Select.  The code will NOT process those other Case Is statements.

No need for a preempting End Select or Go To.  Once all the code in the BLOCK (between the first Case Is and the next Case Is, Case Else, or End Select - whichever comes first) is run, the code proceeds to the End Select and moves forward from there.

In your VBA Editor, click your left mouse on the word Case in the code.  Then, hit F1 for the help.

Dave
Here's how it actually works:


Select Case SOMETHING
     Case X
       [  code ...
          ....
          ....]
      Case Y
       [ code ...
          ....
          ....]
       Case Else
       [ code ...
          .....
         ......]
End Select


The block (identified by the [ and ] brackets I put there for VISUALIZATION PURPOSES ONLY) that gets executed is the FIRST one where the conditions are met (Case Else runs if none of the conditions are met).

Dave
Understood.

it gets much more complicated than expected so I need to raise poins again ;-)

So if any BLOCK (i.e. the FIRST) meets condition, code should proceed, if no condition block met, code should exit sub (that's my intention). So sorry for driving you mad, but how do I do this exactly then?
IF no condition block is met, and you have a Case Else statement, then THAT gets executed.  Thus the Exit Sub goes there if you want to exit when no conditions are met.

See line 35 of my post with the test sub() that has the complete Case Statement with the Case Else.

Dave
thanks Dave for not giving up on me!  ;-)
Avatar of byundt
Just to be a little contrary, you could greatly simplify the code by using Application.Evaluate:

If Application.Evaluate(Worksheets.Count & UserCountComparisonOperator & UserCountNumber) Then

In the statement above, you could substitute whatever you like for the Worksheets.Count.

Brad
Brad reminds us there's more than one way to skin a cat, lol!

Aside from learning about the Case statement, an introduction to Application.Evaluate is an excellent alternative, even from a contrarian!  Good on ya!

If you use that, your code could be written as:

 If Application.Evaluate([xxx.object whatever].Count  & " " & UserCountComparisonOperator & " " & UserCountNumber) then
     'do something
 Else
     Exit Sub
 End If

Open in new window


Dave
Thanks Brad.

Would this also work in Outlook to check/count # of attachments for example?

If Application.Evaluate(Outlook.MailItem.Attachments.Count & UserCountComparisonOperator & UserCountNumber)
Then

Open in new window

Yes, it should.

Dave
Outlook doesn't have an Evaluate method. You would need to borrow it from Excel.
Now, I thought it did, as I did Help on it in Outlook.  Turns out the help redirected to Excel examples, so I slipped up on that, lol.

Brad - do you have to instantiate an instance of Excel if not running to do this?  Seems a bit of overhead just to save a few lines of code.

Dave
Wouldn't early binding do the trick?
I don't thinks so.  I tried:

Dim oEx As Excel.Application

    Debug.Print oEx.Evaluate("1+2")

and it failed.  It wants oEx to be bound to an instance of Excel.

Now, if Excel is already running, then that's probably OK, and my tests work fine.  Otherwise, hmmmm - you've got the overhead of loading a new instance, etc., but here would be the code:

Sub test()
Dim oEx As Object

    On Error Resume Next
    Set oEx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set oEx = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    
    If oEx.Evaluate(Outlook.MailItem.Attachments.Count & UserCountComparisonOperator & UserCountNumber) Then
        'Do Something
    Else
        Exit Sub
    End If
    
    Set oEx = Nothing
End Sub

Open in new window


Dave
Dave,
I did the following on a Windows XP box with Outlook running and Excel not running:

Sub Test()
Dim xl As Object
Set xl = Excel.Application
Debug.Print xl.Evaluate("1+2")
End Sub

Before checking the box for Excel 14 Object Library in Tools...References, I got an error on the Set statement. Afterwards, I got a 3 in the Immediate pane.

Brad
Me too.  Office 2007, Vista.  Excel 12 Object Library

My code fails, however.

Sub test()
Dim oEx As Excel.Application

    
 Debug.Print oEx.Evaluate("1+2")
 
End Sub

Open in new window

I see - your code works, but it IS instantiating an instance of Excel.

Set xl = Excel.Application 'instantiates an new instance - even if an instance is open

Also, your code as written doesn't drop that instance, so it stays in memory after.

Dave
I tried to do it like Dick Kusleika shows in http://www.dailydoseofexcel.com/archives/2005/02/28/browser-links/

Sub Test()
Dim xl As Excel.Application
Set xl = New Excel.Application
Debug.Print xl.Evaluate("1+2")
End Sub

Once again, this works when Excel 14 Object Library is checked, and fails (on the Dim statement) if it is not.
Agreed, it works.  However, it instantiates an instance of Excel, re: not sure this is worth it for the benefit of saving a few lines of code.

However, another way to skin a cat, lol - and not dissimilar to the late binding approach I posted which checks and uses an existing instance if it can, before instantiating, whilst the New Excel.Application actually creates a new instance, regardless.

Dave
Further tests showed the same results when I declared xl as Object.
I think we agree it works.
If I put a Stop on the End Sub statement, I can see Excel.exe taking 14 MB of memory in the Windows Task Manager. Excel goes away automatically when I execute the End Sub statement.
Agreed - your last post using NEW Excel.Application cleans itself up properly.

Based on this experience, I'm not a fan of co-opting another application unless it really adds significant benefit.  But that's my opinion, lol.

Back to the original question, it was my initial assumption that the OP was going to do DIFFERENT stuff based on the result of the comparison.  If this is truly the case, then the CASE statement is the best way to go, IMHO.

However, a direct comparison just to get to a truth/false, the Application.Evaluate is excellent for Excel and, knowing what one is getting into with respect to instantiating an Excel instance to co-opt the method, looks pretty good for Outlook as well, though I would hesitate unless I got a lot of benefit from it.  With a few lines of code, I get what I want and don't have to instantiate anything (I can't seem to get off that line, but that's where I am.)

Good discussion.  Thanks, Brad

Dave