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

stmoritz
stmoritz used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Author

Commented:
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?

Author

Commented:
Then End Select Else Exit Sub?
Most Valuable Expert 2012
Top Expert 2012
Commented:
Wow - 200 ;)  (Just having a bit of fun with you.  You know you have infinite points - they never run out.)

Just create a Case Else clause for your exit sub.  That will handle if the conditions are not met.  Also, if they are, code will continue as presumed, after the End Select statement.

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
                'do whatever
            End If
        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
        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
        Case Is = "<>"
            If [xxx.object whatever].Count <> UserCountNumber Then
                'do whatever
            End If
        Case Else
            Exit Sub
    End Select
    

End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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)
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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

Most Valuable Expert 2012
Top Expert 2012

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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?
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
thanks Dave for not giving up on me!  ;-)
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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

Most Valuable Expert 2012
Top Expert 2012

Commented:
Yes, it should.

Dave
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Outlook doesn't have an Evaluate method. You would need to borrow it from Excel.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Wouldn't early binding do the trick?
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Most Valuable Expert 2012
Top Expert 2012

Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Further tests showed the same results when I declared xl as Object.
Most Valuable Expert 2012
Top Expert 2012

Commented:
I think we agree it works.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial