return value from select case

Question

How can I get the value from the expression in my Select Case statement?

When we write select case statements, we are told that good practice is to have a case else at the end. I understand the purpose of this to be if we don't think of all possible cases, there is a way to inform us instead of just moving on. Usually I just use debug.assert in that case, which works great for personal debugging and is sometimes sufficient for some end users after delivery.

That doesn't work well when I am writing to the VBE, as breaking is not supported anytime after the VBE has been called. I realize that I could probably unload the VBE object and then debug.assert, but that kind of defeats the purpose of pausing my code, if the case has to do with what I am reading/writing to the VBE.

To my mind, the easiest solution would be to msgbox TheUnexpectedResultFromMyExpression, but I have no idea how to call that. The second easiest solution appears to be to have full and complete prescience of what my users may or may not do, as well as when and how they may do it. I have been working on that too, so if you don't know how to return the value, then maybe you have some tips on omniscience.

I know that I, in the vase majority of cases, could simply copy the expression itself to a msgbox ... inside case else, but I happen to be working with a case of a decision tree based upon the return to setting an object, and I am not interested in doing that twice. Another option might be to just myVariable=<expression> and select case myVariable instead of select case <expression>, and then always debug.print myVariable before every select case, but my log is already so busy, doing that in a larger project would mean I have to buy another monitor, and I am struggling with groceries right now.

Asking here seems easier. Thanks.

EDIT:

For those that seemed to have a hard time understanding what I am trying to ask, I boiled the code down as simply as I could. Obviously the below isn't super useful, but you get the idea.

Select Case Forms("Form1").Module.CreateEventProc("Click", Forms("Form1").Controls("label0").Name)
Case 1
    Debug.Print "line1"
Case 2
    Debug.Print "line2"
Case Else
    Debug.Print Forms("Form1").Module.CreateEventProc("Click", Forms("Form1").Controls("label0").Name)
End Select

When my Case Else statement runs, it may print the value of the expression (the workaround I mentioned) in the immediate window, and I know I could do the same with a msgbox or variant or whatever, but it runs the code again. And yes, as I mentioned above already, I could (in this case) just assign the value to a long and then run the Select Case on the long, but that option doesn't solve the problem in my application. And lest this become a conversation where we debate the merits of using VBE objects, or tries to get me to ask a different question of why I get unexpected values (I am not, I am trying to plan for clean debugging during runtime), or someone asking me why I can't just make the code simpler and easier to use instead of tens of thousands of lines of code that write another thousand+ lines of code, it is because I have a client. And they pay for what they want.

So, back to original question, all I want is to know how to return the value from the select case expression. If you are a superhero, and can get code to pause after the VBE is called, then by all means answer that question instead or in addition to.

".. we are told that good practice is to have a case else at the end." That certainly depends. — Gustav, Apr 13 '16 at 09:36
*"I could (in this case) just assign the value to a long and then run the Select Case on the long, but that option doesn't solve the problem in my application."* - why not? This is exactly the way to go to run the statement only once. If this doesn't work for you, maybe you should explain your actual problem? — Andre, Apr 15 '16 at 06:38
"(in this case)" above refers to the example. Not my actual project. My actual problem is that I need to return the value for the expression of the `select case` statement. That value is held in memory, obviously, I want access to it. If the answer is "that is impossible in VBA", then that is the answer I want, with a source: not "why would anyone want that, prove that you actually want what you want". — CWilson, Apr 15 '16 at 13:30
Since you ask, yes, IMHO there was too much distracting information (and what we call here "fluff") in your question. Just the first line and a short code example would have been best. The rest of the confusion comes from the fact that (probably) very few people have ever asked themselves or others this question - because (see my answer). — Andre, Apr 15 '16 at 17:35

score 1 · Accepted Answer · answered Apr 15 '16 at 17:28

The answer is: you can't.

Sorry, no source, except multiple descriptions of Select Case, e.g. MSDN, which would mention it, if this functionality existed.

It is a rather unusual question, that's one reason for the confusion in the comments. You are looking for a "meta" variable or method, like @@IDENTITY in T-SQL. But this doesn't exist for the VBA Select Case.

And the reason why it isn't needed: you have complete control over the testexpression in the Select part. The usual way, and I actually consider this good programming practice, is to always assign any sort of complex expression or method call (like CreateEventProc) to a variable, and then use this variable for Select Case.

So

LineNr = Forms("Form1").Module.CreateEventProc("Click", Forms("Form1").Controls("label0").Name)

Select Case LineNr 
    Case <expected values>
        ' do something useful
    Case Else
        Debug.Print "Whoa, unexpected LineNr: " & LineNr
End Select

is really the and the only solution.

I begrudgingly accept this answer. :) Truthfully, we were writing and interacting with a select case in the VBE through a select case in the code. I really didn't want to make it more complicated, but since the question was spurred by a desire to have clean code and correct debugging procedure, I thought some more and decided we could add more lines (to the code, the VBE code we were writing, and the log) to make it easier for me to understand in a year. — CWilson, Apr 23 '16 at 06:16

Doug Coats · Answer 2 · 2016-04-14T12:54:11.723

0

I just wrote this maybe itll help?

Private Sub mystuff()
Dim stuff As String


Select Case stuff
    Case Is = "Mine"
        stuff = "yours "
    Case Is = "Not yours"
        stuff = "his "
    Case Else
        stuff =  "Hers"
    End Select

    debug.print "stuff"; stuff    

End Sub

edited Apr 14 '16 at 12:54

answered Apr 12 '16 at 19:17

Doug Coats

6,255
9
27
49

As I put in my original question, making my log even more busy isn't the ideal solution... but perhaps it is the only possible solution? And by the way, if you are using `debug.print`, why didn't you just `debug.print stuff`? – CWilson Apr 14 '16 at 12:40
As you can imagine, if I am getting unexpected values in a select case statement after calling the VBE, there are multiple moving parts, and I would need lots of lines of `debug.print` statements to show everything that might be affecting my expression value. – CWilson Apr 14 '16 at 12:46
Good question.... I tend to replace MsgBox with debug.print alot of the time. Probably wiser that way lol Either way I typed up my original response fairly quickly (and you didnt really leave me a lot to go on either). Either way, you will end up with something kind of ugly, see my edited response. – Doug Coats Apr 14 '16 at 12:52
sometimes adding tons of debug.print lines can get ugly and super tedious till you pinpoint the issue, but its sometimes a necessary evil – Doug Coats Apr 14 '16 at 12:55
Obviously I was unclear. I don't have a problem in the `Select Case` statement right now, I am trying to use good debugging practice in case I ever do. So, I am not trying to add lines to figure something out (of course, things get awfully ugly when that happens), I mean the log that runs every time the program is run, including after distribution. – CWilson Apr 14 '16 at 21:21
Anyway, what you have up now isn't VBA. And by the way `stuff` changes after the `Select` statement, so this really wouldn't work. – CWilson Apr 14 '16 at 21:23
I just modelled it after the multiple case selects I have used numerous times. I even ran it in visual studio and it did work( returned "hers.") So IDK where youre thinking this isnt VBA......you are asking about VBA right? LOL But either way you may have to add multiple debug.print lines. – Doug Coats Apr 15 '16 at 12:21
My bad. I looked at it and recognized BASIC. I didn't realize that VBA had the compatibility optionally. And I hope I am not being rude: writing an answer is like putting yourself out there, and I appreciate it. On the other hand, this (nor your original edit) doesn't really answer the question at all, which is how to return the value of the expression. – CWilson Apr 15 '16 at 14:31

return value from select case

2 Answers2