Solved

VBA Passing Objects to Class Module Methods

Posted on 2003-10-24
2
1,056 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
ID: 9615566
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
ID: 9624502
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

828 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