Excel VBA Object Variable Not Set Error 91

Question

I've been having trouble with a specific line of VBA code today. I keep getting error messages about "Object variable not set..." which is VBA error 91.

The following code is where the error occurs and is located in the modules folder as mFactory

Public Function CreateInterface(InterfaceWB As Workbook, SourceFilepath As String, SourceBookPass As String) As CInterface
     Dim NewInterface As CInterface
     Set NewInterface = New CInterface

     NewInterface.InitiateProperties InterfaceWB:=InterfaceWB, SourceFilepath:=SourceFilepath, SourceBookPass:=SourceBookPass <------Error Here
     Set CreateInterface = NewInterface
End Function

This method is called on workbook open from ThisWorkbook:

Public Interface As CInterface

Private Sub Workbook_Open()
     Dim InterfaceWB As Workbook
     Dim SourceFilepath As String
     Dim SourceBookPass As String

     Set InterfaceWB = ThisWorkbook
     'Change this variable if the location of the source workbook is changed
     SourceFilepath = "C:\file.xlsx"
     'Change this variable if the workbook password is changed
     SourceBookPass = "password"

     Set Interface = mFactory.CreateInterface(InterfaceWB, SourceFilepath, SourceBookPass)
End Sub

And the InitiateProperties method called in the mFactory module is implemented in the class module CInterface:

Sub InitiateProperties(InterfaceWB As Workbook, SourceFilepath As String, SourceBookPass As String)
     pInterfaceWB = InterfaceWB
     pSourceFilepath = SourceFilepath
     pSourceBookPass = SourceBookPass
End Sub

My VBAProject structure is as follows:

VBAProject
     Microsoft Excel Objects
          Sheet1
          ThisWorkbook
     Modules
          mFactory
     Class Modules
          CInterface

One thing I did is change the CInterface instancing to PublicNotCreatable because I was getting an error about passing private arguments to public functions. I'm trying to use a "constructor" to create one instance of the CInterface class to use globally in the VBA project. Why is the object not set at the line I'm getting the error?

mFactory is not an Object. Have your tried Set Interface = CreateInterface(InterfaceWB, SourceFilepath, SourceBookPass)? — PatricK
– PatricK, Commented Jun 22, 2016 at 1:54
mFactory is a module in the same VBAProject. Here I'm making a method call to the module. Sorry if my jargon is incorrect for VBA, I'm not very familiar with this language. — Frank Gallagher
– Frank Gallagher, Commented Jun 23, 2016 at 1:07

TZubiri · Accepted Answer · 2016-06-22 01:55:15Z

1

Ths error occurs when you try to use or assign an object variable without setting it, in this case the pInterfaceWB.

Sub InitiateProperties(InterfaceWB As Workbook, SourceFilepath As String, SourceBookPass As String)
     pInterfaceWB = InterfaceWB

Should be

Set pInterfaceWB = InterfaceWB

answered Jun 22, 2016 at 1:55

TZubiri

98314 silver badges31 bronze badges

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

1 Comment

Frank Gallagher Over a year ago

Ah yes, that would be it! I'm fairly new to VBA and Set statements are something I always forget. Thank you.

Collectives™ on Stack Overflow

Excel VBA Object Variable Not Set Error 91

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related