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

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
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.

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,719
Office Version
2010
Platform
Windows
It's defined as a Worksheet variable, and you're trying to set it assign it as a Range variable.
 

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,719
Office Version
2010
Platform
Windows
I realize that the '[]' indicates a range variable,
No -- the brackets are a shortcut to the Evaluate function.

Change

Code:
Dim BaseWkst As Worksheet
to

Code:
Dim BaseWkst As Range
And while your at it, change the variable name to reflect its use.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
If the codename of the sheet is 'Criteria' use this,
Code:
Set Basewkst = Criteria
if it's the 'tab' name of the worksheet use this.
Code:
Set Basewkst = Sheets("Criteria")
 

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
Thanks for the [] clarification.

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

Change

Code:
Dim BaseWkst As Worksheet
to

Code:
Dim BaseWkst As Range
And while your at it, change the variable name to reflect its use.
 

lmoseley7

Board Regular
Joined
Jul 9, 2013
Messages
151
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,
Code:
Set Basewkst = Criteria
if it's the 'tab' name of the worksheet use this.
Code:
Set Basewkst = Sheets("Criteria")
 

Forum statistics

Threads
1,082,441
Messages
5,365,539
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top