2

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
Tyler bolton
  • 23
  • 2

1 Answers1

2

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
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 5
    Take a look at [What's the difference between Dim As New vs Dim / Set](https://stackoverflow.com/questions/42656468/whats-the-difference-between-dim-as-new-vs-dim-set) for some thoughts regarding those two options. – Ron Rosenfeld Oct 04 '19 at 14:53