Class Object Not Recognized

Question

I'm having an issue with a class object not being recognized (error 91, object reference not set). Here's the code:

---Main (Standard Module)
Option Explicit
Public wbCode As Workbook

Public Sub MainSub()
Dim str As String
Dim tables As New CTables
Call SetExcelObjects
str = tables.shExclusions.Cells(20, 1) ---this produces error 91
End Sub

Public Sub SetExcelObjects()
Dim tables As New CTables
Dim str As String
Set wbCode = ThisWorkbook
Set tables.shExclusions = wbCode.Worksheets("Exclusions")
str = tables.shExclusions.Cells(20, 1) ---this line executes okay
End Sub


---CTables (Class Module)
Option Explicit
Public shExclusions As Worksheet

Lynn Crumbling · Accepted Answer · 2012-10-07 02:47:17Z

1

Try this:

---Main (Standard Module) 
Option Explicit 
Public wbCode As Workbook 

Public Sub MainSub() 
Dim str As String 
Dim tables As New CTables 
Call SetExcelObjects(tables)
str = tables.shExclusions.Cells(20, 1) ---this produces error 91 
End Sub 

Public Sub SetExcelObjects(tables as CTables) 
Dim str As String 
Set wbCode = ThisWorkbook 
Set tables.shExclusions = wbCode.Worksheets("Exclusions") 
str = tables.shExclusions.Cells(20, 1) ---this line executes okay 
End Sub 


---CTables (Class Module) 
Option Explicit 
Public shExclusions As Worksheet

answered Oct 7, 2012 at 2:47

Lynn Crumbling

13.4k8 gold badges61 silver badges98 bronze badges

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

1 Comment

Daniel Over a year ago

+1, Though because it was difficult for me to tell what change you suggested, I am explaining for future readers. The issue is that the object tables was not being set within the scope of Main. By passing the object reference to SetExcelObjects, SetExcelObjects is able to define tables to make it useable. An alternate solution would have been to make tables a private variable for the whole module.

Collectives™ on Stack Overflow

Class Object Not Recognized

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