VBA Object variable or With block variable not set error

Question

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

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
– user4039065, Commented Mar 15, 2016 at 17:29
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
– miguelmpn, Commented Mar 15, 2016 at 18:06

Tim Williams · Accepted Answer · 2016-03-15 17:33:40Z

1

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.

edited Mar 15, 2016 at 17:33

answered Mar 15, 2016 at 17:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

miguelmpn Over a year ago

Still the same... even if I comment the bottom section I still get the error

miguelmpn Over a year ago

I have found the issue, thank you for your time I was actually missing one Set statement when receiving the the result of the function... I'm still learning how to process this basic errors with no line numbers :|

miguelmpn · Accepted Answer · 2016-03-15 18:04:45Z

0

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

answered Mar 15, 2016 at 18:04

miguelmpn

2,0761 gold badge20 silver badges28 bronze badges

Collectives™ on Stack Overflow

VBA Object variable or With block variable not set error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related