Reference Class Module in Access from Excel

[reclusive.monkey]

I have created a custom function in Access. I used the Class Module
method as I wanted it to be accessible to Excel. I have this in a class
module in access;


Option Compare Database

Public Property Get strFinancialYear() As String
myDate = Date - 1
myYear = Year(myDate) - myYearModifier ' Get the year for
yesterday, with our modifier
If (MONTH(myDate)) >= 4 Then ' If we are after april, this year is
first part of FN year
strFinancialYear = myYear & "/" & Right(myYear + 1, 2)
Else
strFinancialYear = myYear - 1 & "/" & Right(myYear, 2)
End If
End Property

Public Function FinancialYear() As String
FinancialYear = Me.strFinancialYear
End Function


This is then called from a module in access;
Code:

Function ReportingYear() As String
Dim myReportingYear As New CFinancialYear
ReportingYear = myReportingYear.strFinancialYear
End Function


I then have
Code:

ReportingYear()


In a query field to give "2005/06". This works fine. However, when I
try to access this same query from Excel, I get the following error;
Code:

[Microsoft][ODBC Microsoft Access Driver] Undefined function
'ReportingYear' in expression


In my references dialog in Excel, I have ticked;
Code:

Visual Basic for Applications
Microsoft Excel 10.0 Object Library
Microsoft Forms 10.0 Object Library
Microsoft Access 10.0 Object Library


I cannot find a reference for ODBC anywhere and I have tried ticking as
many references which might seem to be relevant, but I am getting
nowhere. Can anyone see what I am doing wrong as I don't seem to be
able to get anywhere on this I have been trying to get this working for
months now and its becoming very frustrating.

 

[John Nurick]

Basically, custom VBA functions aren't available in queries executed from
outside Access. You'll probably need to have your Excel VBA code construct
the SQL statement for the query, passing in the including the appropriate
value for ReportingYear.

 

[reclusive monkey]

Hi John,

Thanks very much for your reply. I was starting to think today that
maybe I had travelled too far down this road without actually getting
anywhere. I've already got a VBA created SQL query passing to the
database and returning the results. I will create my function and
insert in there and see how I get on. You may just have saved what
precious little sanity I have left! Thanks again.

Luke

 

[Tim Ferguson]

(e-mail address removed) wrote in @g44g2000cwa.googlegroups.com:

I have created a custom function in Access. I used the Class Module
method as I wanted it to be accessible to Excel. I have this in a class
module in access;

Just copy the module to the Excel project.

That is the point of writing Classes; being able to reuse the code.

Hope that helps


Tim F

 

[reclusive monkey]

Hi Tim,

From what I understood of Class Modules, the whole point was that you

*don't* have to copy and paste. I could of copied and pasted the
Subs/Functions that worked perfectly well without having to explore
Class Modules. This is what I read in "VB & VBA In a Nutshell";

"When you create a class module, you are creating a COM interface.
Therefore, class modules allow you to describe your application to the
outside world via a programmable interface that consists of properties,
methods, and events in a way that allows you to retain control over the
application."

It then goes on to say you could copy and paste, but class modules help
you avoid all this... not quite it would seem.

 

[Tim Ferguson]

"When you create a class module, you are creating a COM interface.
Therefore, class modules allow you to describe your application to the
outside world via a programmable interface that consists of properties,
methods, and events in a way that allows you to retain control over the
application."

This goes somewhat beyond the context of Access and its friends. In VB
you can compile to a .exe or .dll and expose the interfaces as much as
you like. For all I know it's possible in VBA but it would presumably
involve launching whole applications under OLE automation and then
hacking into the programming environment of each.

It then goes on to say you could copy and paste, but class modules help
you avoid all this... not quite it would seem.

I would do it using Save and Import actually -- write once, use
everywhere.


B Wishes


Tim F

 

[reclusive monkey]

Hi Tim,

I see what you mean here. I guess if you are developing with VBA,
pretty soon you'll want to get VB so you have this flexibility. I am
working in a very limited environment, so thats not an option for me.
Also, I am not a programmer, so I think I would be getting in over my
head here. I've already started on making the changes I need to carry
out my filters from within Excel, and its all working fine. Thanks for
clearing that up. I'll have a go with the save and import; thats a good
idea.

 

[Tim Ferguson]

I guess if you are developing with VBA,
pretty soon you'll want to get VB so you have this flexibility

I must say, I went the other way. As a long time VB user from version 2 (I
think) up to ver 5, I now do nearly everything using a host like Access or
Excel and sometimes Word (I have a great analog clock in Word that makes
everybody laugh!).

I like the way that I don't have to re-invent the GUI every time from
scratch, or handle connecting text boxes to recordset fields, or talk to
printers and so on. Just lazy I guess...


All the best

Tim F