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
– Jules, Commented Jan 18, 2016 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
– Oscar Anthony, Commented Jan 18, 2016 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
– Mathieu Guindon, Commented Jan 18, 2016 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
– Oscar Anthony, Commented Jan 18, 2016 at 23:13
No worries - if your code works as intended now, feel free to post it (all!) on Code Review for tips to make it cleaner and easier to maintain. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 18, 2016 at 23:21

Community · Accepted Answer · 2017-05-23 12:23:30Z

3

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.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Jan 18, 2016 at 23:00

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

2 Comments

Oscar Anthony Over a year ago

Thank you, I will modify my code accordingly. I did not know about Code Review Stack Exchange. I will start using it more often!

Iron Man Over a year ago

@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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related