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

[Mackeral]

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:

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.

 

[Jaafar Tribak]

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.

 

[Yongle]

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

I do not experience that behaviour - example below

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

 

[Fluff]

You can also do calculations in the Case

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

 

[Mackeral]

One other question I have: How could I see the documentation you found?
Thanks,
Mac

 

[Mackeral]

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

 

[Mackeral]

You present an unusual way to use Select with True/False. But this was what I was trying:

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

 

[Mackeral]

I should not have posted 2 problems together.

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

Again this 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:

Case Function_Returing_TRUE_or_FALSE(Arg)   <-- Statement B

So what is the difference between Statement A and Statement B?

 

[mikerickson]

I've added some parenthesis to your 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,

xStr = "cat"
Select Case True
    Case (xStr Like "?ats"): MsgBox "a"
    Case (xStr Like "c*"): MsgBox "b": Rem this is what happens
End Select

 

[Jaafar Tribak]

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 :

    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

    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