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;
Code:

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;

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

I then have

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;

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

In my references dialog in Excel, I have ticked;

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.

 

[K Dales]

No need to add a reference for ODBC; you are already using ODBC (when you
query Access). In fact, the error you are getting is being sent from the
ODBC driver. I know the reason but no easy solution. I assume you are using
either MSQuery or ADO to get your Access data; both of these convert the
query to SQL. Now, while your Access project understands your functions the
SQL interpreter doesn't, thus the error. The easiest thing I can think of is
to omit that field from your query and duplicate it in an Excel function.
But that can lead to frustration down the road if you change it 5 years from
now and forget that it also needs to be changed in Excel. A potentially
better method would be to add a reference to MSAccess to your Excel project
and actually open the database and manipulate it directly via automation.

There maybe a way of using custom functions in an external database query
that I don't know of so you may need to research this further.

 

[reclusive monkey]

Thanks for the reply K, thats helped my understanding of the problem. I
think the best route to go down would be to replicate the function in
Excel. The exporting to excel is a seperate function from anything else
used in the database, so it should work out better than the method I am
currently using. Again, thanks for the reply.