Option Explicit causes names defined in a worksheet to give object needed error in VBA

[lmoseley7]

Here is my issue:
Adding Option Explicit to my code module is causing an 'object needed' error when referencing named ranges within workbooks. Removing Option Explicit allows the code to run as expected. The line that errors out is Set Basewkst = [Criteria] where Criteria is the VBA name of a worksheet within the workbook.

Sub Update_SCD_Data()'Creates SCD Data sheet if it doesn't exist, then copies the data from the Data worksheet.
    Dim ControlFile As String
    Dim Dimen_Data_FileName As String
    Dim DimenWbkName As String
    Dim BaseWkst As Worksheet
    Dim inputrng As Range
    Dim destrng As Range
    Dim SCDdata As Workbook
    ControlFile = ActiveWorkbook.Name
    If WorksheetExists("SCD Data") Then GoTo Clear_SCD_Table
    '*****
    Sheets("SUMMARY").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "SCD Data"
    '*****
Clear_SCD_Table:
    Sheets("SCD Data").Select
    If Last(1, Range("A:A")) < 4 Then GoTo RemoveSubtotals
    Range("3:3").Select
        With Range(Selection, Selection.End(xlDown)) _
            .Offset(1, 0)
            .Delete Shift:=xlUp
        End With
RemoveSubtotals:
    If InStr(ControlFile, "Asset") Then
            Remove_SSB_Asset_Subtotals
    ElseIf InStr(ControlFile, "Income") Then
            Remove_SSB_Inc_Subtotals
    End If
    Sheets("SCD Data").Select
    Set BaseWkst = [Criteria]
    DimenWbkName = BaseWkst.[SCDWkbkName].Value
    ChDir _
        "\\accounting\Reconciliations\SCD - SSB Recons"
    Workbooks.Open Filename:= _
        "\\accounting\Reconciliations\SCD - SSB Recons\" & DimenWbkName _
        , Notify:=False
    Dimen_Data_FileName = ActiveWorkbook.Name
    Application.Goto Reference:="SCDdata"


    Set inputrng = Selection
    Windows(ControlFile).Activate
    Set destrng = Sheets("SCD Data").Range("A2:C2").Resize(inputrng.Rows.Count, inputrng.Columns.Count)
    MsgBox ("destrng Address: " & destrng.Address)
    destrng = inputrng.Value
    Windows(Dimen_Data_FileName).Close False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[shg]

It's defined as a Worksheet variable, and you're trying to set it assign it as a Range variable.

 

[lmoseley7]

I realize that the '[]' indicates a range variable, but I've also seen, and have used quite often, the [] to indicate a sheet name that has been changed in VBA. For instance, this sheet was formally named Sheet21, but I renamed it 'Criteria'. I don't mean the name you see when you look at the workbook, but the name that VBA uses. I'm assuming that's why you said I was trying to assign it as a Range Variable, but if I'm missing some other reference, please point it out because at this point I'm blurry-eyed looking at this. Could you offer a solution to correct, since I'm not seeing the issue even with your clue? Hopefully it's not too time intensive.

It's defined as a Worksheet variable, and you're trying to set it assign it as a Range variable.

 

[shg]

I realize that the '[]' indicates a range variable,

No -- the brackets are a shortcut to the Evaluate function.

Change

Dim BaseWkst As Worksheet

to

Dim BaseWkst As Range

And while your at it, change the variable name to reflect its use.

 

[Norie]

If the codename of the sheet is 'Criteria' use this,

Set Basewkst = Criteria

if it's the 'tab' name of the worksheet use this.

Set Basewkst = Sheets("Criteria")

 

[lmoseley7]

Thanks for the [] clarification.

No -- the brackets are a shortcut to the Evaluate function.

Change

Dim BaseWkst As Worksheet

to

Dim BaseWkst As Range

And while your at it, change the variable name to reflect its use.

 

[lmoseley7]

So the brackets were unnecessary in this instance I guess? Funny thing is I've run this macro several times since then and I just noticed that where I thought I had commented it out, it is not in this version (I have several workbooks using the same macro code). I wonder if the issue was that in debugging the code I might have activated another sheet through a mouse-click or something and so the reference wasn't valid? Not sure, but thanks for the help. Examples like this are very informative for me.

If the codename of the sheet is 'Criteria' use this,

Set Basewkst = Criteria

if it's the 'tab' name of the worksheet use this.

Set Basewkst = Sheets("Criteria")