Use Access VBA functions from Excel

G

Guest

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?
 
G

Guest

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 before:

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
 
G

Guest

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 said:
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 before:

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


[QUOTE="RSunday"]
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?[/QUOTE][/QUOTE]
Click to expand...
 
R

RB Smissaert

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

RBS


RSunday said:
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 said:
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
before:

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


[QUOTE="RSunday"]
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?[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
G

Guest

It can be any kind of function. The point is that I use it in Access2003 and
now I want to use the exact same function in Excel - but would like only to
maintain it in one place.

I guess a key info here is: The function does not access any tables - but is
strictly a calculation routine.

So it is a function like:

Public function BusinessLogic1(x as double, y as double, z as double) as
double
code
BusinessLogic1 = ...... the result of the code in the function
End Function

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

RBS


RSunday said:
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 said:
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
before:

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


:

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?[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
R

RB Smissaert

I think you will have to use Application.Run, something like this:

Sub test()

Dim oAccess As Access.Application
Dim strFile As String
Dim lResult As Long

Set oAccess = New Access.Application

strFile = "C:\db1.mdb"

oAccess.OpenCurrentDatabase strFile

lResult = oAccess.Run("TimesTwo", 10)

MsgBox lResult

End Sub


RBS


RSunday said:
It can be any kind of function. The point is that I use it in Access2003
and
now I want to use the exact same function in Excel - but would like only
to
maintain it in one place.

I guess a key info here is: The function does not access any tables - but
is
strictly a calculation routine.

So it is a function like:

Public function BusinessLogic1(x as double, y as double, z as double) as
double
code
BusinessLogic1 = ...... the result of the code in the function
End Function

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

RBS


RSunday said:
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?

:

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
before:

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


:

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?[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA and VB6 functions return different answers? 3
excel spreadsheet functions written in vba 1
Excel VBA in Citrix. 0
Automation: Invoking Excel Financial Functions Without InstantiatingExcel? 4
Excel Barcoding using a central database repositories 0
Running MS Access From Excel VBA: Communication Between Processes? 2
excel and reqular expressions; what????? 1
Check user credentials 6

Top