Maybe post an example to see what kind of functions we are talking about
here.

RBS


"RSunday" wrote in message
...
No - I do not refer to Access objects etc. I have written a function in
Access VBA. This function is the official "business logic" and I want to
use
the same function in Excel - but I don't want to re-write the function in
Excel-VBA. If I had the function in a DLL - then I could just make a
reference to it in Excel-VBA. In Access I can just refer to other .mdb
files
and share their functions.

So my question is: How do I use the VBA-functions in my .mdb files in
Excel?

"Carlos" wrote:

You must refer to Access' library, not to Access' files. It is the
library
the one that contains the object model. Proceed the same as you did, but
instead of adding *.mdb files add Microsoft Access 11.0 Object Library,
Microsoft DAO 3.6 Object Library and so on.

You should know what objects are you using in Access in order to get the
right library. I would personally avoid this method, since it often leads
to
inconsistencies that require you to rewrite the code anyway and would use
OLE
automation instead.

Example of inconsistency 1. As you may already know, recordsets are
defined
for both the DAO and the ADODB object models. How would Excel know which
one
you are talking about? Are you willing to rely on VBA's judgement? You
would
have to make explicit statements like:

Dim obj01 As DAO.Recordset
Dim obj02 As ADODB.Recordset

***

Example of inconsistency 2. Both Word and Excel have an object called
range.
If you make a reference in Excel to Word's library, then it is not clear
what
object do you want to use and would lead you to the same problem as
befo

Dim wdRng As Word.Range
Dim xlRng As Excel.Range

***

As I said before, I would use OLE automation instead. The only thing you
would need to change is the way your variables and arguments are
declared. I
currently do not have to much examples with Access, but I hope you find
my
examples from Excel and Word useful.

Example of declaration 1. This is a macro declared in Word.
Public Sub WordMacro(doc As Document)
[code]
End Sub

If you are calling Word from Excel then the same macro would be:
Public Sub WordMacroFromExcel(doc As Object)
[code]
End Sub

***

You have to create instances of the objects you are using though.

Example of OLE automation 1. Suppose you are in Excel and want to create
a
Word document.

Option Explicit
Public Sub CreateWordDocument
Dim wdApp As Object
Dim doc As Object

'Create a Word instance and make it visible
'Note: always make visible other applications whenever your code is prone
'to errors, like when you are still learning or experimenting, because if
something
'goes wrong (an error) the instance will still be active, you won't see
it
and
'will only be able to shut it down using the task manager, which is
cumbersome.
Set wdApp = CreateObject("Word.Application")

'Add a Word document
Set doc = wdApp.Documents.Add

End Sub

--
Carlos Mallen


"RSunday" wrote:

I have a database in Access with some programmed calculation routines
in VBA.
Now I would like to use these routines in Excel - but I dont want to
write
them again.

I thought I would just reference the .mdb file from Excel VBA
(tools-references- browse for .mdb files) - but I just get the message
that I
cant add a reference to the specified file.

Is there a way to use routines from .mdb in Excel-VBA?