VBA Object variable or With block variable not set error

1
votes

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

2
excelvbaexception
What isn't working? Works for me as With GetWorksheetFromName("ABC") : .Range("A1") = 123 : End With . First pass it creates the new worksheet, second pass it uses the same worksheet. - user4039065
The code works, but throws-me this error - miguelmpn
How are you calling it? - Scott Craner
Which line throws the error ? - Tim Williams
I just answered the question, I don't really know if I should leave the question or not, but at least the MSDN link can be usefull :/ - miguelmpn

2 Answers

1
votes
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.

0
votes

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