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.
Microsoft Excel

Avatar of undefined
Last Comment
dlmille

8/22/2022 - Mon
dlmille

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
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.
dlmille

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
stmoritz

ASKER
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?
stmoritz

ASKER
Then End Select Else Exit Sub?
ASKER CERTIFIED SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dlmille

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
stmoritz

ASKER
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)
dlmille

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
stmoritz

ASKER
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dlmille

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
dlmille

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
stmoritz

ASKER
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

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
stmoritz

ASKER
thanks Dave for not giving up on me!  ;-)
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
dlmille

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
stmoritz

ASKER
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

dlmille

Yes, it should.

Dave
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
byundt

Outlook doesn't have an Evaluate method. You would need to borrow it from Excel.
dlmille

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
byundt

Wouldn't early binding do the trick?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
dlmille

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
byundt

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
dlmille

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

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
byundt

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.
dlmille

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
byundt

Further tests showed the same results when I declared xl as Object.
dlmille

I think we agree it works.
byundt

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

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