Passing a string to be used within a CASE statement

Question

this is my first time posting a question here. I am writing an MS Excel VBA project where I am passing a string between subroutines. In the second subroutine I am using the string to determine CASE conditions. EG 4000 to 9999; 4050 to 5000, 5050; 5051 to 6000, 6051 to 7000 being three examples. When these strings are typed after the CASE, it works , but passing the values as a string doesn't. My project involves the running of the sub many times with the case statement being required to change every time. Any Suggestions?

Many thanks NC here is a sample of my code. I have left out all the dims and calculations - I am positive that these would not help, thanks

Sub Calling_Sub ()
'Operating Income
Title = "Operating Income"
GL_Range = "5000 to 9499"
add_me_up Title, GL_Range

'Other Income
Title = "Other Income"
GL_Range = "4050 to 5000, 5050"
add_me_up Title, GL_Range

'Debts
Title = "Debts"
GL_Range = "5051 to 6000, 6051 to 7000"
add_me_up Title, GL_Range

'Other
Title = "Other"
GL_Range = "8051, 8055, 8070, 8075"
add_me_up Title, GL_Range

'and so on and so forth for all the other combinations
End Sub


Sub add_me_up (Title as string, GL_Range as string)

...

For Each lrowC In lrngC.Cells       'pivot table data rows

   Select Case lrowC.Cells.Offset(0, 1)

        Case ****GL_Range****
            If lrowC.Cells(1, 1) = Fin_Year Then      'current month & YTD
                Select Case Period
                    Case 1
...

end sub

Sub Calling_Sub () 'Operating Income Title = "Operating Income" GL_Range = "5000 to 9499" add_me_up Title, GL_Range End Sub — NanetteC, Aug 16 '15 at 10:16

score 0 · Answer 1 · answered Aug 16 '15 at 10:47

0

Not sure whether that is just wrong due to your code-reduction, but you have to call the add_me_up-routine with Call and brackets. E.g. like this:

Call add_me_up(Title, GL_Range)

Check it out on MSDN.

answered Aug 16 '15 at 10:47

Klaster

673
1
7
17

Thanks Klaster, it has no trouble calling the second sub, only that when it gets into the select case, case GL_Range sees the string as a literal alpha, and not as the values I want it to. EG. I want it to read :- Case 5000 to 9499 but it gets :- Case "5000 to 9499" – NanetteC Aug 16 '15 at 10:57
Okay, now I get it. Handing the numbers not as Strings makes perfectly sense, obviously. If you don't want to change `calling_sub`, you could also parse the numbers from the string. There are [solutions](https://stackoverflow.com/questions/15369485/how-to-extract-groups-of-numbers-from-a-string-in-vba) on Stackoverflow already. – Klaster Aug 16 '15 at 14:02
Thanks again Klaster, sadly the obvious took me longer, actually when I responded to you initially was when the solution hit me. Parsing was also a very valid option, and one I will probably use next time. I thank you sincerely for your trouble. – NanetteC Aug 16 '15 at 21:29

score 0 · Answer 2 · answered Aug 16 '15 at 11:31

Thanks for the suggestions, but I have a workable solution. I am now passing the numbers separate to the 'joiners', and by this, the case statement is able to read them as numbers, as opposed to the alpha strings previously. I appreciate any thoughts given to my problem, thanks.

Passing a string to be used within a CASE statement

2 Answers2