VBA VBA Run-Time Errors Run-time error '91': Object variable or With block variable not set
Example
Incorrect code
Sub DoSomething()
Dim foo As Collection
With foo
.Add "ABC"
.Add "XYZ"
End With
End Sub
Why doesn't this work?
Object variables hold a reference, and references need to be set using the Set keyword. This error occurs whenever a member call is made on an object whose reference is Nothing. In this case foo is a Collection reference, but it's not initialized, so the reference contains Nothing - and we can't call .Add on Nothing.
Correct code
Sub DoSomething()
Dim foo As Collection
Set foo = New Collection
With foo
.Add "ABC"
.Add "XYZ"
End With
End Sub
Why does this work?
By assigning the object variable a valid reference using the Set keyword, the .Add calls succeed.
Other notes
Often, a function or property can return an object reference - a common example is Excel's Range.Find method, which returns a Range object:
Dim resultRow As Long
resultRow = SomeSheet.Cells.Find("Something").Row
However the function can very well return Nothing (if the search term isn't found), so it's likely that the chained .Row member call fails.
Before calling object members, verify that the reference is set with a If Not xxxx Is Nothing condition:
Dim result As Range
Set result = SomeSheet.Cells.Find("Something")
Dim resultRow As Long
If Not result Is Nothing Then resultRow = result.Row
