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 UserCountComparisonOperato r as String
UserCountNumber = 9
UserCountComparisonOperato r = ">="
If [xxx.object whatever].Count >= 9 Then...
So instead of using hard coded >= and 9, using the variables...??
Thanks for your help.
So the user defines for example
Dim UserCountNumber as Double
Dim UserCountComparisonOperato
UserCountNumber = 9
UserCountComparisonOperato
If [xxx.object whatever].Count >= 9 Then...
So instead of using hard coded >= and 9, using the variables...??
Thanks for your help.
ASKER
Thanks Dave. I assume you meant...
right?
Will check and get back asap.
Case Is = "="
If [xxx.object whatever].Count = UserCountNumber then ...
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
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?
If the condition is met, the code should continue after the "Select" if not exit sub.
How to code this?
ASKER
Then End Select Else Exit Sub?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Cheers,
Dave
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)
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
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
ASKER
So here as example
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:
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
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
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
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
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
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?
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
See line 35 of my post with the test sub() that has the complete Case Statement with the Case Else.
Dave
ASKER
thanks Dave for not giving up on me! ;-)
Just to be a little contrary, you could greatly simplify the code by using Application.Evaluate:
If Application.Evaluate(Works heets.Coun t & UserCountComparisonOperato r & UserCountNumber) Then
In the statement above, you could substitute whatever you like for the Worksheets.Count.
Brad
If Application.Evaluate(Works
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:
Dave
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
Dave
ASKER
Thanks Brad.
Would this also work in Outlook to check/count # of attachments for example?
Would this also work in Outlook to check/count # of attachments for example?
If Application.Evaluate(Outlook.MailItem.Attachments.Count & UserCountComparisonOperator & UserCountNumber)
Then
Yes, it should.
Dave
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
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:
Dave
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
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
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.
My code fails, however.
Sub test()
Dim oEx As Excel.Application
Debug.Print oEx.Evaluate("1+2")
End Sub
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
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.
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
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
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
Open in new window
Dave