ERRORS in VBA FUnctions "InputBox" & "Select Case"

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
225
Office Version
  1. 365
Platform
  1. Windows
These 2 VBA expressions do not function as it the are documented to, and I found that these had been recently implimented and broke code I've been running for years.

INPUTBOX

The docmentation says that if you hit the "Cancel" key that the routine returns a FALSE.
It now returns an Empty String.

The workaround is to use the SetStr function to test for the Escape Key having been used:
Code:
Test = SetStr(Arg) = 0
"Arg" being the string returned from the InputBox call.

SELECT CASE
The documentation refers to using "Is" or "Like" prior to haing a calculation as part of a "Case" statement. Not only that these don't work; now you can't have any calculations in the "Case" statement. It does not respond to a TRUE or FALSE argument, only what has been presented in the "Select Case" statement.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
What is SetStr ?

The documentation doesn't say that pressing Cancel returns a False... The doc says it returns a zero-length string.

Use StrPtr to check if the user pressed Cancel which should return 0.

Regarding Select Case , can you give us an example as it is not clear to me from your description.
 
Upvote 0
These 2 VBA expressions do not function as it the are documented to, and I found that these had been recently implimented and broke code I've been running for years.
INPUTBOX

The docmentation says that if you hit the "Cancel" key that the routine returns a FALSE.
It now returns an Empty String.

InputBox returns a string
Application.InputBox returns boolean with value = False

SELECT CASE
The documentation refers to using "Is" or "Like" prior to haing a calculation as part of a "Case" statement. Not only that these don't work; now you can't have any calculations in the "Case" statement. It does not respond to a TRUE or FALSE argument, only what has been presented in the "Select Case" statement.

It is difficult to work out what is going wrong without seeing your code - please post the problem code

It does not respond to a TRUE or FALSE -argument
:confused:
I do not experience that behaviour - example below
Code:
Sub SelectCase()
    Const A = 100
    Const B = 50
    Dim msg: msg = "A = " & A & vbCr & "B = " & B & vbCr & vbCr & "Case is "
    
    
    Select Case (A = 2 * B) = True
        Case True: MsgBox msg & "true", , "A = 2 X B"
        Case Else: MsgBox msg & "false", , "A = 2 X B"
    End Select
    
    Select Case (A = B) = True
        Case True: MsgBox msg & "true", , "A = B"
        Case Else: MsgBox msg & "false", , "A = B"
    End Select
 
Last edited:
Upvote 0
You can also do calculations in the Case
Code:
Sub chk()
   Const x As Long = 100
   Const y As Long = 10
   
   Select Case True
      Case x - y = 10
         MsgBox x - y
      Case x / y = 10
         MsgBox x / y
   End Select
End Sub
 
Upvote 0
One other question I have: How could I see the documentation you found?
Thanks,
Mac
 
Upvote 0
I see how you are able to True/False in Select Case.

But what I was referring to is that you cannot do

Select Case TS
 
Upvote 0
You present an unusual way to use Select with True/False. But this was what I was trying:
Code:
<br>
    TS = "A"<br>
    Select Case TS<br>
        Case TS = "A": Stop<br>
        Case TS <> "A": Stop<br>
        Case Else: Stop    '  <-- This is where this code ends up stopping.<br>
    End Select
<br>
<br>
Looking at the Documentation, I read "comparison Operators" as meaning the kind of code displayed above. However, on a closer reading of the text, it really means just a series of things that might be the same as what is in TS.<br>
<br>
So my conclusion that there is an error in the SELECT CASE documentation is WRONG, and the documentation does work exactly the way it's described.<br>
<br>
 
Upvote 0
I should not have posted 2 problems together.

This response is a further example to the "Select Case" issue.

Again this code
Code:
        TS = "A"     Select Case TS
        Case TS = "A": Stop   <-- Statement A
        Case TS <> "A": Stop
        Case Else: Stop    '  <-- This is where this code ends up stopping.
    End Select

But I have another Case statement in my code which does work:
Code:
Case Function_Returing_TRUE_or_FALSE(Arg)   <-- Statement B

So what is the difference between Statement A and Statement B?
 
Upvote 0
I've added some parenthesis to your code
Code:
TS = "A"     
Select Case TS
    Case (TS = "A"): Stop   <-- Statement A
    Case (TS <> "A"): Stop
    Case Else: Stop    '  <-- This is where this code ends up stopping.
End Select
Since (TS="A") is "True" (when evaluated against a string) and TS="A", the first case fails
Since (TS<>"A") is "False" and TS = "A", the second case fails.
Thus the Case Else prevails.

Also, to demonstrate the use of LIKE,
Code:
xStr = "cat"
Select Case True
    Case (xStr Like "?ats"): MsgBox "a"
    Case (xStr Like "c*"): MsgBox "b": Rem this is what happens
End Select
 
Last edited:
Upvote 0
Like mikerickson explained, the prepositions in your Case statements will always evaluate to a boolean value which will never be a string.

The correct way is :
Code:
    TS = "A"
    Select [COLOR=#0000ff]Case TS[/COLOR]
        [COLOR=#0000ff]Case "A"[/COLOR]: Stop
        [COLOR=#0000ff]Case Is <> "A"[/COLOR]: Stop
        Case Else: Stop   
    End Select

Or

Code:
    TS = "A"
    Select [COLOR=#ff0000]Case True[/COLOR]
       [COLOR=#ff0000] Case TS = "A"[/COLOR]: Stop
        [COLOR=#ff0000]Case TS <> "A"[/COLOR]: Stop
        Case Else: Stop    
    End Select
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top