How to Pass this Error Msg "Variable not defined'

Question

Hi

I would like to ask that in the below code I won't understand why it gives the Compile Error that Variable not defined

since Sheet32 does not exists which is true but why?

To my instinct if the First Case is true then the second case must be ignore.

Even this I have used the If statement, doesn't work.

since I have tried it in many ways to ignore this error but it always gives the compile error.

Sub Test

Dim ws As Worksheet

On Error Resume Next '[Either this line is exist or not, both way it show the compile error]

With Application  
    .Calculation = xlCalculationManual  
    .ScreenUpdating = False  
    .EnableEvents = False  
End With  
For Each ws In Worksheets  
    Select Case ws.CodeName  
    Case "Sheet6"  
        Sheet6.Select  

' Exit For '[Either this line is exist or not, both way it show the compile error]
' GoTo ExitHandler '[Either this line is exist or not, both way it show the compile error]
Exit Sub '[Either this line is exist or not, both way it show the compile error]
Case "Sheet32"
Sheet32.Select 'it gives here the Compile Error that Variable not defined
End Select
Next ws

On Error GoTo 0 '[Either this line is exist or not, both way it show the compile error]  
  

ExitHandler:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Thanks & Regards

Muneeb

Answer 1

HansV wrote:

Before you run a macro, the Visual Basic Editor checks the macro's code for syntax error. [....] Just remove that part from the macro.

Only if Option Explicit is set (see below). So the practical choices are:

1. Remove or comment out the Option Explicit statement. Usually not really a good idea.
2. Change Sheet32.Select to Sheets("Sheets32").Select, if that is what you really mean.

Sheet32 is an object name. Sheets("Sheet32") is the worksheet name that you see in the tabs at the bottom of the Excel window.

The worksheet named "Sheet32" might not be the worksheet object Sheet32.

I use object names when I want the flexibility of changing the worksheet name.

So, think about what ws.CodeName truly represents.

If it truly represents an object name and you want the option to delete or omit that worksheet, I think that #1 is the only practical option -- unless you want to use #If ... #End If to "comment" out the offending code manually, but optionally.

---

The following works without error, assuming there is no worksheet object Sheet123, because Option Explicit is commented out.

' Option Explicit (note the initial apostrophe on the left)

Sub testit()
Dim x As Variant
x = 1
Select Case x
Case 1:
MsgBox Range("a1").Address(external:=True)

Case Else:
MsgBox Sheets123.Range("a1").Address(external:=True)
End Select
End Sub

There should be no VBA error. But if you uncomment (reactivate) Option Explicit, VBA detects a compile-time (not runtime) error.

Answer 2

Before you run a macro, the Visual Basic Editor checks the macro's code for syntax error. If Sheet32 does not exist, the line Sheet32.Select is not valid syntactically, even if it will never be executed. So it is not a runtime error but a syntax error.

Just remove that part from the macro.