CODEWITHSUNDEEP
excelvbaexception
miguelmpn
miguelmpn

Reputation: 2027

VBA Object variable or With block variable not set error

I'm having a hard time with VBA errors on Excel, can someone help me understanding what this error means?

"VBA Object variable or With block variable not set error"

My function is supposed to check if a sheet exists, if not create the new sheet, rename it and return to the main function. The code works, but the error is always thrown..

Function GetWorksheetFromName(Name As String) As Worksheet
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
        If StrComp(WS.Name, Name, vbTextCompare) = 0 Then
            Set GetWorksheetFromName = WS
            Exit Function
        End If
    Next WS

    With ThisWorkbook
        Set WS = Worksheets.Add(After:=Sheets(.Sheets.Count))
        WS.Name = Name
    End With

    Set GetWorksheetFromName = WS

End Function

P.S.: this might help, but I still haven't fixed my code

Upvotes: 1

Views: 1822

Answers (2)

miguelmpn
miguelmpn

Reputation: 2027

The error was actually listed on the MSDN link

You attempted to use an object variable that has been set to Nothing.

Set MyObject = Nothing             ' Release the object.

MyCount = MyObject.Count       ' Make a reference to a released object.

Respecify a reference for the object variable. For example, use a new Set statement to set a new reference to the object.

Dim WS As Worksheet

Sheets("Config").Select
WS = GetWorksheetFromName(Range("B8").Value)

My error was on the last line, I was missing the SET

Set WS = GetWorksheetFromName(Range("B8").Value)

Thank you Tim Williams and Scott Craner for your quick help

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166391

Set WS = .Worksheets.Add(After:=.Sheets(.Sheets.Count))

note the added period before Sheets and Worksheets - your current code addresses the Sheets collection of the Active workbook, not ThisWorkbook.

Upvotes: 1

Related Questions