Solved

VBA Passing Objects to Class Module Methods

Posted on 2003-10-24
2
1,051 Views
Last Modified: 2013-11-23
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 ----------
0
Comment
Question by:magalabastro
2 Comments
 
LVL 1

Accepted Solution

by:
TwistD_PissR earned 350 total points
Comment Utility
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
 
LVL 2

Author Comment

by:magalabastro
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now