Class Modules - Need Help (1 Viewer)

[reclusivemonkey]

I am trying (unsuccessfully) to get Class Modules clear in my feeble brain. I've got plenty text books, I've read up on support.microsoft.com, googled and read all the forums I can find. The most useful thing I've found so far is the post here;

http://www.access-programmers.co.uk/forums/showthread.php?t=93726

Following this example, I've actually got *something* working, rather than the errors I have been getting for the past few weeks. My intention here is to set up a function which calculates, from the current date, the reporting month (i.e. the month of the date yesterday), current financial year, etc. so that I can pull data automatically from my Access database to Excel. This works fine when I reference variables from forms/etc. But ideally I want it to be "smarter"; I have got this far;

Option Compare Database
Private dteToday As Date
Private intMonth As Integer
Private strFYear As String

Private Sub SetDate()
    dteToday = Date
End Sub

Public Function Reporting() As Date
    Reporting = dteToday
End Function

This resides in CReporting, a class module in the Access Database. When I call a sub from a regular module in the db like this;

Sub Test()
    Dim TestDate As CReporting
    Set TestDate = New CReporting
    MsgBox TestDate.Reporting
End Sub

All I get is 00:00:00, when I am expecting today's date. I'm not too bad with VBA in general, its vastly decreased my working time in many areas, but the whole class modules are just one layer of abstraction too far for my feeble brain. If I can get one value returned, I'll be off, but I am totally stuck here. I've started reading up on Property Get/Let/Set, but that only confused me more...

 

[MarkK]

Given the code you have you must call SetDate before your Function Reporting() will return a value. See how Sub SetDate() assigns the date to dteToday?
And your Function Reporting() then exposes dteToday.

Amend Sub Test as follows...

Sub Test()
[COLOR=Green]  'you can dim and create the class in one step
[/COLOR]  Dim TestDate As New CReporting

[COLOR=Green]  'enclosed in a with block, since we'll refer to it a couple of times[/COLOR]
  With TestDate
[COLOR=Green]    'call Sub SetDate() first, so dteToday is assigned a value
[/COLOR]    .SetDate
[COLOR=Green]    'now Function Reporting() should return someting cooler than 00:00
[/COLOR]    MsgBox .Reporting
  End With

[COLOR=Green]  'and it's good practice to explicitly dispose of the class when finished[/COLOR]
  Set TestDate = Nothing

End Sub

Or, if you modify your class a little...

Private dteToday As Date

Private Class_Initialize()
[COLOR=Green]  'make this assignment when class is created[/COLOR]
  dteToday = Date
End Sub

Public Function Reporting() As Date
    Reporting = dteToday
End Function

...your original Sub Test() will work, since the class now sets the dteToday variable in the initialize method.

But, the lifetime of your class may exceed the duration of the date. Say you create the class at 11:59pm, and this is where exposing the date as a property of the class makes more sense, since it'll always be current. Here's another possibility for your class module...

[COLOR=Green]'no private variables[/COLOR]

Public Property Get DateNow as Date
[COLOR=Green]  'this will always yield a current date[/COLOR]
  DateNow = Date
End Sub

Public Function Reporting() As Date
[COLOR=Green]  'and this simply re-exposes the DateNow Property
[/COLOR]  Reporting = Me.DateNow
End Function

Obviously none of these are particulary useful objects as they exist here, but maybe this gives you a few more ideas as you dive into programming with class modules.

Cheers,
Mark

 

[reclusivemonkey]

Hi Mark,

Thanks very much for the reply, this is very informative. The comments are particularly useful; I think I will now be able to make some progress here!

Luke