Constructor in VBA - Runtime error 91 "Object variable not set"

Question

I am trying to write some code in excel VBA using the Object Oriented Concept. Therefore I wanted to initialize my objects with constructors, like we usually do in Java. However I discovered that the default Class_Initialize() Sub that is offered in VBA does not take arguments. After searching a bit, I found that the answer for this Question proposed a pretty good alternative.

Here is a sample of my Factory Module (I Named it Creator):

Public Function CreateTool(ToolID As Integer) As cTool
    Set CreateTool = New cTool
    CreateTool.InitiateProperties (ToolID)     '<= runtime error 91 here
End Function

The class cTool:

Private pToolID As Integer
Private pAttributes As ADODB.Recordset 
Private pCnn As ADODB.Connection

Public Sub InitiateProperties(ToolID As Integer)
    Dim sSQL As String
    Set pCnn = connectToDB()     'A function that returns a connection to the main DB
    pToolID = ToolID
    sSQL = "SELECT Tool_ID, Status, Type, Tool_Number " _
                 & "FROM Tool  WHERE Tool_ID = " & pToolID
    pAttributes.Open sSQL, pCnn, adOpenKeyset, adLockOptimistic, adCmdText
End Sub

This is how I call the constructor:

Dim tool As cTool
Set tool = Creator.CreateTool(id)

My issue is that when I run the code, I get the following error:

Run-Time error '91' : Object Variable or With Block Variable not Set

The debug highlights the CreateTool.InitiateProperties (ToolID) line of my CreateTool Function.

I know that this usually happens when someone is setting a value to an object without using the keyword Set but it does not seem to be my case.

Any help, advice to resolve this issue would be greatly appreciated!

Thanks.

You haven't create pAttributes object in your cTool.InitateProperties. — Jules, Jan 18 '16 at 22:50
Thank you! I never tought about checking my *cTool* class as the debugger was pointing somewhere else. Adding the line `Set pAttributes = New ADODB.Recordset` solved the issue. Thanks again for the fast reply. I will edit my post to include you answer — Oscar Anthony, Jan 18 '16 at 23:02
Don't do that. This is a Q&A site, not a forum. The Question should contain the question, and Answers should contain the answer. — Mathieu Guindon, Jan 18 '16 at 23:08
Well noted @Mat'sMug . I did not notice that you already made an answer. My bad, i am still new here. I will not edit the question. — Oscar Anthony, Jan 18 '16 at 23:13
No worries - if your code works as intended now, feel free to post it (all!) on [codereview.se] for tips to make it cleaner and easier to maintain. — Mathieu Guindon, Jan 18 '16 at 23:21

score 3 · Accepted Answer · edited May 23 '17 at 12:23

Might not be the cause of your error, but this:

Public Function CreateTool(ToolID As Integer) As cTool
    Set CreateTool = New cTool
    CreateTool.InitiateProperties (ToolID)     '<= runtime error 91 here
End Function

Is problematic for a number of reasons. Consider:

Public Function CreateTool(ByVal ToolID As Integer) As cTool
    Dim result As cTool
    Set result = New cTool
    result.InitiateProperties ToolID
    Set CreateTool = result
End Function

Now, looking at the rest of your code, you're doing the VBA equivalent of doing work in the constructor, i.e. accessing database and other side-effects to constructing your object.

As @Jules correctly identified, you're accessing the unitialized object pAttributes inside InitiateProperties - that's very likely the cause of your problem.

I'd strongly recommend another approach - if you come from Java you know doing work inside a constructor is bad design... the same applies to VBA.

Get your code working, and post it all up on Code Review Stack Exchange for a full peer review.

Thank you, I will modify my code accordingly. I did not know about Code Review Stack Exchange. I will start using it more often! — Oscar Anthony, Jan 18 '16 at 23:20
@OscarAnthony Keep in mind that Code Review is a place where **working** code is reviewed and not a place to ask _how to make it work_, but rather _how to make it work_ **better**. — Iron Man, Jan 18 '16 at 23:32

Constructor in VBA - Runtime error 91 "Object variable not set"

1 Answers1