Solved

VBA Passing Objects to Class Module Methods

Posted on 2003-10-24
2
1,065 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

732 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