CODEWITHSUNDEEP
excelvba
Tyler bolton
Tyler bolton

Reputation: 23

How do I pass an Object to VBA Class Get Property Method without receiving an 'Object not Defined' Error?

When passing a Worksheet to a Class Object Method, I receive:

Error 91: Object Variable or With Block variable not set.

The problem is, I do not know where it is I have not set an object. 

Dim WS As Worksheet
Set WS = oExport.ExportSheet(oExport.WB)

Dim testint As Integer
Dim oAsset As clsAsset
testint = oAsset.AssetIDCol(WS)

I have checked, and the worksheet object is correctly set in Line 2 of the code. The error occurs when assigning a value to the 'testint' variable. Here is the .AssetIDCol() property from my clsAsset property:

Option Explicit

Private priv_asset_id_col As Integer

Public Static Property Get AssetIDCol(WS As Worksheet) As Integer

   If Not priv_asset_id_col = Null Then
        AssetIDCol = priv_asset_id_col
        Exit Property
   End If

   Dim rngX As Range
   Dim Val As Variant
   Dim i As Integer

   Set rngX = WS.UsedRange '.Rows(1) '.Find(SearchVal, LookAt:=xlWhole)

   For i = 1 To rngX.Columns.Count

   If InStr(priv_asset_id_col_name, rngX.Cells(1, i).Value) > 0 Then
       AssetIDCol = i
       priv_asset_id_col = i
       Exit Property
   End If

   Next i

   AssetIDCol = 0

End Property

How do I fix this error? The Get property returns an integer, so I am not sure where I am failing to use Set in creating an instance.

Upvotes: 2

Views: 321

Answers (1)

Vityata
Vityata

Reputation: 43593

The error appears, because oAsset is declared, but not initialized.

The easiest way to fix it is to write Dim oAsset As New clsAsset instead of Dim oAsset As clsAsset. This way you are referring to a new object upon declaration.

Another way is to set the new object explicitly with a new line, after declaring it:

Dim oAsset As clsAsset
Set oAsset = New clsAsset

Upvotes: 2

Related Questions