VBA error 91 Object variable or With block variable not set: when is the Set keyword required?

VBA has two kinds of assignment. Plain =, historically Let, copies a value: a number, string, date or array. Set makes an object variable refer to an object. A worksheet is an object, so you need Set ws = ThisWorkbook.Worksheets("Data").

Without Set, VBA treats the line as a value assignment and tries to store the value into the default property of whatever ws currently refers to. It refers to nothing yet, and that is error 91: an object variable was used before it was set.

x = ws.Range("A1") works because of default properties. A Range has Value as its default member, so in a value assignment VBA reads ws.Range("A1").Value for you. Which keyword you use therefore decides what you get: x = ws.Range("A1:B2") into a Variant gives a 2 by 2 array of cell values, indexed from 1, while Set x = ws.Range("A1:B2") makes x refer to the range itself.

Writing .Value explicitly whenever you mean the value removes the ambiguity and makes a missing Set much easier to spot.

The second error 91 has a different cause with the same number. Find returns Nothing when there is no match, the Set succeeds in assigning Nothing, and the failure comes one line later when you ask Nothing for its Row. It happens only on sheets without the text. Test the result before using it: If Not hit Is Nothing Then r = hit.Row, and handle the missing case in an Else branch.

The same check applies to anything that can legitimately return no object, and to object variables that are only assigned inside an If or a loop that may not run.

A related pitfall: Dim c As New Collection declares an auto-instancing variable. VBA silently creates the object the first time the variable is touched, and creates a fresh one if you touch it again after setting it to Nothing, so c Is Nothing can never be True, and a new empty object can appear where you expected an error. Declare and create separately: Dim c As Collection, then Set c = New Collection where you need it.

Set c = Nothing only releases this variable's reference; the object is destroyed when its last reference goes. Local object variables are released automatically when the procedure ends.

To find which variable is unset when a line has several dots, break on the error and inspect each part, or print TypeName(ws) in the Immediate window; an unset object variable reports Nothing. Chains such as Workbooks("a.xlsx").Worksheets("Data").Range("A1") are worth splitting into separate Set statements while debugging, so the failing step is obvious.

VB.NET removed both features behind this confusion: there is no Set for assignment and there are no parameterless default properties, so ws = ... there always assigns the reference.