Hi dlow,
What we are dealing with is a feature of Excel 'Case Statements'. Branches of 'Case Statements' should be constants and not evaluations, since Excel VBA apparently compiles the 'Case' branches as constants.
The 'Select Case' construction should probably be replaced with a 'Compound If' construction.
See the following example macros that are included in the attached file.
Option Explicit
Sub SimplifiedDemonstrationOfFaultyCaseConstruction()
'This fails because the 'Instr() Case' is doing an evaluation
'It looks like it should work, but apparently Excel VBA compiles the construction to a constant
'
'Make 'SheetX' the Active Sheet
Dim uiValue As String
uiValue = "SheetX"
Sheets("SheetX").Activate
Select Case ActiveSheet.Name
Case "Sheet1", "Sheet2", "Sheet3"
'Do Nothing
MsgBox "Sheet1, Sheet2, Sheet3"
Case InStr("," & uiValue & ",", "," & ActiveSheet.Name & ",")
MsgBox "SheetX success: uivalue=" & uiValue
Case Else
MsgBox "Case Failure - 'SheetX' branch should have been selected"
End Select
End Sub
Sub ReplaceCaseStatementWithCompoundIf()
Dim uiValue As String
uiValue = "SheetX"
Sheets("SheetX").Activate
If ActiveSheet.Name = "Sheet1" Or ActiveSheet.Name = "Sheet2" Or ActiveSheet.Name = "Sheet3" Then
'Do Nothing
MsgBox "Sheet1, Sheet2, Sheet3"
ElseIf InStr("," & uiValue & ",", "," & ActiveSheet.Name & ",") Then
MsgBox "SheetX success: uivalue=" & uiValue
Else
MsgBox "if Failure - 'SheetX' branch should have been selected"
End If
End Sub
It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
This option requires all variables to be declared and will give a compiler error for undeclared variables.
Lewis
Bookmarks