1

I'm working on automating some excel tasks at work using VBA and having trouble with the following:

I'm trying to create a Sub with multiple Cases which I call with the Case as an argument but it's not working. It always selects the first case. Can anybody point out what I'm doing wrong?

Example:

Main Sub:

Public Sub Test()

Call CaseTest(Case2)

End Sub

"Called Sub":

Public Sub CaseTest(CaseSelect)

Select Case CaseSelect

Case Case1

MsgBox "Case1"

Case Case2

MsgBox "Case2"

End Select
End Sub
  • 2
    Your Case1 and Case2 are treated as variables. I'd recommend you to add Option Explicit statement as a first line of each your module - this will detect such errors printing "Undefined variable" error message. – Akina Jul 12 '19 at 9:05
  • 2
    I'm voting to close this question as off-topic because it describes a beginner coding error without general interest for our readers. – harrymc Jul 12 '19 at 9:09
  • @Akina I have read that so many times and still haven't learned, maybe now I will as I wasted quite a bit of time on this issue. – FreeSoftwareServers Jul 12 '19 at 11:55
  • 1
3

When testing Case against strings that Case statement needs Quotes as well as the argument.

Eg:

Main Sub:

Public Sub Test()

Call CaseTest("Case2") ' Note the ""

End Sub

"Called Sub":

Public Sub CaseTest(CaseSelect)

Select Case CaseSelect

Case "Case1"

MsgBox "Case1"

Case "Case2" ' Note the ""

MsgBox "Case2"

End Select
End Sub

Not the answer you're looking for? Browse other questions tagged or ask your own question.