?
Solved

VBA Passing Objects to Class Module Methods

Posted on 2003-10-24
2
Medium Priority
?
1,075 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 1050 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month11 days, 15 hours left to enroll

752 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