VBA Passing Objects to Class Module Methods

I can't seem to pass objects to class module methods. I get an "object does not support this property or method error" when the TheReport.ExtractReportProperties method is called.

Here is what I have:

1. An Excel file with one sheet.
2. The sheet contains a button which calls LoadButton_Click sub when clicked.
3. The attached VBA code for the sheet.
4. The modules, Configuration and Globals
5. The class module called ReportHeader

Points to anyone who can explain this or provide a helpful link.

I can pass strings, integers, and other primitive datatypes, but not object. Thanks!

-MikeA
---------- HERE IS CODE FOR THE SHEET ----------
Option Explicit

Sub LoadButton_Click()

  Set TheCSVWorkbook = Workbooks.Open( _
    Filename:=(Cells(2, 5).Value), _
    UpdateLinks:=0, _
    ReadOnly:=False, _
    Format:=2, _
    IgnoreReadOnlyRecommended:=True, _
    Notify:=True, _
    AddToMru:=False _
    )
 
  Set TheCSVWorksheet = TheCSVWorkbook.Worksheets(1)
 
  Set TheReport = New ReportHeader
  TheReport.ExtractReportProperties (TheCSVWorksheet)

End Sub
---------- HERE IS CODE FOR CONFIGURATION ----------
Option Explicit

'Configuration File

Public Const ReportTypeCell As String = "A1"
Public Const ReportMonthCell As String = "B1"
Public Const ReportYearCell As String = "C1"
Public Const ReportStartDateCell As String = "D1"
Public Const ReportEndDateCell As String = "E1"
Public Const AgentCodeCell As String = "F1"
Public Const AgentNameCell As String = "G1"
Public Const UnitCodeCell As String = "H1"
Public Const UnitNameCell As String = "I1"
Public Const BranchCodeCell As String = "J1"
Public Const BranchNameCell As String = "K1"
Public Const ContractDateCell As String = "L1"
Public Const ServiceCodeCell As String = "M1"
Public Const AgentStatusCell As String = "N1"
---------- HERE IS CODE FOR GLOBALS ----------
Option Explicit

'Global Variables

Public TheCSVWorkbook As Workbook
Public TheCSVWorksheet As Worksheet
Public TheReport As ReportHeader
---------- HERE IS CLASS MODULE REPORTHEADER ----------
Option Explicit

Public ReportType As Variant
Public ReportMonth As String
Public ReportYear As Integer
Public ReportStartDate As Date
Public ReportEndDate As Date
Public AgentCode As String
Public AgentName As String
Public UnitCode As String
Public UnitName As String
Public BranchCode As String
Public BranchName As String
Public ContractDate As Date
Public ServiceCode As String
Public AgentStatus As String

Public Sub ExtractReportProperties(W As Worksheet)

  ReportType = W.Range(ReportTypeCell).Value
  ReportMonth = W.Range(ReportMonthCell).Value
  ReportYear = W.Range(ReportYearCell).Value
  ReportStartDate = W.Range(ReportStartDateCell).Value
  ReportEndDate = W.Range(ReportEndDateCell).Value
  AgentCode = W.Range(AgentCodeCell).Value
  AgentName = W.Range(AgentNameCell).Value
  UnitCode = W.Range(UnitCodeCell).Value
  UnitName = W.Range(UnitNameCell).Value
  BranchCode = W.Range(BranchCodeCell).Value
  BranchName = W.Range(BranchNameCell).Value
  ContractDate = W.Range(ContractDateCell).Value
  ServiceCode = W.Range(ServiceCodeCell).Value
  AgentStatus = W.Range(AgentStatusCell).Value
 
End Sub
---------- END OF CODE LISTINGS ----------
LVL 2
magalabastroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TwistD_PissRCommented:
I was able to reproduce your problem and here is a solution:

----------------------
ReportHeader Class Module
----------------------
Option Explicit
Public ReportType As Variant
Public ReportMonth As String
Public ReportYear As Integer
Public ReportStartDate As Date
Public ReportEndDate As Date
Public AgentCode As String
Public AgentName As String
Public UnitCode As String
Public UnitName As String
Public BranchCode As String
Public BranchName As String
Public ContractDate As Date
Public ServiceCode As String
Public AgentStatus As String
Public Sub ExtractReportProperties(W As Worksheet)
    On Error GoTo ErrHandler
    colTemp(0) = W.Range(ReportTypeCell).Value
    colTemp(1) = W.Range(ReportMonthCell).Value
    colTemp(2) = W.Range(ReportYearCell).Value
    colTemp(3) = W.Range(ReportStartDateCell).Value
    colTemp(4) = W.Range(ReportEndDateCell).Value
    colTemp(5) = W.Range(AgentCodeCell).Value
    colTemp(6) = W.Range(AgentNameCell).Value
    colTemp(7) = W.Range(UnitCodeCell).Value
    colTemp(8) = W.Range(UnitNameCell).Value
    colTemp(9) = W.Range(BranchCodeCell).Value
    colTemp(10) = W.Range(BranchNameCell).Value
    colTemp(11) = W.Range(ContractDateCell).Value
    colTemp(12) = W.Range(ServiceCodeCell).Value
    colTemp(13) = W.Range(AgentStatusCell).Value
    Exit Sub
ErrHandler:
    Debug.Print Err.Description
    Debug.Print Err.Number
End Sub

--------------------
Module - Globals
--------------------
Option Explicit
Public Const ReportTypeCell As String = "A1"
Public Const ReportMonthCell As String = "B1"
Public Const ReportYearCell As String = "C1"
Public Const ReportStartDateCell As String = "D1"
Public Const ReportEndDateCell As String = "E1"
Public Const AgentCodeCell As String = "F1"
Public Const AgentNameCell As String = "G1"
Public Const UnitCodeCell As String = "H1"
Public Const UnitNameCell As String = "I1"
Public Const BranchCodeCell As String = "J1"
Public Const BranchNameCell As String = "K1"
Public Const ContractDateCell As String = "L1"
Public Const ServiceCodeCell As String = "M1"
Public Const AgentStatusCell As String = "N1"
Public colTemp(14)

-----------------
WorkSheet - With the button
-----------------
Option Explicit
Public TheCSVWorkbook As Workbook
Public TheCSVWorksheet As Worksheet
Public TheReport As ReportHeader
Sub LoadButton_Click()
    On Error GoTo ErrHandler
    Dim colReturn As Collection
    Set TheCSVWorkbook = Workbooks.Open( _
        Filename:=(Cells(2, 5).Value), _
        UpdateLinks:=0, _
        ReadOnly:=False, _
        Format:=2, _
        IgnoreReadOnlyRecommended:=True, _
        Notify:=True, _
        AddToMru:=False _
        )
    Set TheCSVWorksheet = TheCSVWorkbook.Worksheets(1)
    Set TheReport = New ReportHeader
    TheReport.ExtractReportProperties TheCSVWorksheet
    'The following is just to verify that you received the array value
    ' you can loop though it and do with it what you like!
    Debug.Print colTemp(0)
    Debug.Print colTemp(1)
    Debug.Print colTemp(2)
    Debug.Print colTemp(3)
    Debug.Print colTemp(4)
    Debug.Print colTemp(5)
    Debug.Print colTemp(6)
    'etc...
    Exit Sub
ErrHandler:
    Debug.Print Err.Description
    Debug.Print Err.Number
End Sub

Now just complie and test!
In your VBE Immediate window you should see:
1
2
3
4
5
6
7

HTH's!
TwistD
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
magalabastroAuthor Commented:
TwistD,

  I reviewed your code and didn't see much change except the following:

(1) Error handlers. This helps in debugging. Thanks.
(2) Using a global array instead of the class attributes. I don't think this is what I need because I need my class to be completely encapsulated with access to data via attributes or via function parameters.

  However, your code does work. Upon comparing your code to my code, I saw one fundamental flaw in mine. The way I called the class method ExtractReportProperties was wrong. I used parentheses! Your code didn't and that is what removed the error.

My code:
     TheReport.ExtractReportProperties (TheCSVWorksheet)
Your code:
     TheReport.ExtractReportProperties TheCSVWorksheet

I have experienced this before in VBScript classes in ASP programming but didn't remember it in time to debug my code.

Although your code didn't really point out the real error, it helped me find it. Thanks a lot! I appreciate the effort to set-up the project in your environment. Thanks once again. I hope you find a B grade fair.

Thanks!

-MikeA
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.