I have built a DLL using VB6 to be used by Excel/VBA. The DLL contains
a number of functions and classes.

The classes are visible, but not the functions, according to the
object browser. Any functionality involving the classes is readily
usable. However, I cannot make use of the functions.

How can I make the functions visible/callable outside the DLL?

Thanks

JAC brought next idea :
I have built a DLL using VB6 to be used by Excel/VBA. The DLL contains
a number of functions and classes.

The classes are visible, but not the functions, according to the
object browser. Any functionality involving the classes is readily
usable. However, I cannot make use of the functions.

How can I make the functions visible/callable outside the DLL?

Thanks

The functions you want to access inside your DLL must be 'public'
methods within the main class, or any other class within the DLL, -and-
an object reference (via Tools, References) must be 'Set' in your code
to the specific class that contains the function you're trying to
access.

Example: <air code
Dim oMyFunctions As New MyFunctionsDLL
Dim oFunctionSet1 As New oMyFunctions.FunctionSet1
Dim oFunctionSet2 As New oMyFunctions.FunctionSet2

Also:
- the DLL must be properly registered on the user machine so it is
recognized by Excel.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 16 July, 18:09, GS wrote:
JAC brought next idea :

I have built a DLL using VB6 to be used by Excel/VBA. The DLL contains
a number of functions and classes.

The classes are visible, but not the functions, according to the
object browser. Any functionality involving the classes is readily
usable. However, I cannot make use of the functions.

How can I make the functions visible/callable outside the DLL?

Thanks

The functions you want to access inside your DLL must be 'public'
methods within the main class, or any other class within the DLL, -and-
an object reference (via Tools, References) must be 'Set' in your code
to the specific class that contains the function you're trying to
access.

Example: <air code
* Dim oMyFunctions As New MyFunctionsDLL
* Dim oFunctionSet1 As New oMyFunctions.FunctionSet1
* Dim oFunctionSet2 As New oMyFunctions.FunctionSet2

Also:
- the DLL must be properly registered on the user machine so it is
recognized by Excel.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks for your advice. However, I'm not entirely sure what you mean.

I tried the following:

1. Adding a Public Declare Function XXX Lib "MyDLL" with arguments,
etc to the VBA underlying the Excel spreadsheet.

2. Adding a class module to the DLL containing my test function and
setting the instancing property to 6 GlobalMultiUse. When I did that,
the function became visible in the Globals part of the Object browser.
However, despite all that, I got #VALUE when I inserted my user-
defined function in a cell on the spreadsheet.

In the end, I decided to cheat and define my UDF simply in the VBA
behind the spreadsheet, which called up routines in the DLL to do the
real substantial work. That was a successful workaround.

Ideally, I should like to call the UDF directly from the Excel
worksheet. So, if anyone has a solution, I should be pleased to hear
it.

I suspect that I'm not far from solving my problem, but lack one or
two essential steps.

JAC brought next idea :
On 16 July, 18:09, GS wrote:
JAC brought next idea :

I have built a DLL using VB6 to be used by Excel/VBA. The DLL contains
a number of functions and classes.

The classes are visible, but not the functions, according to the
object browser. Any functionality involving the classes is readily
usable. However, I cannot make use of the functions.
How can I make the functions visible/callable outside the DLL?
Thanks

The functions you want to access inside your DLL must be 'public'
methods within the main class, or any other class within the DLL, -and-
an object reference (via Tools, References) must be 'Set' in your code
to the specific class that contains the function you're trying to
access.

Example: <air code
* Dim oMyFunctions As New MyFunctionsDLL
* Dim oFunctionSet1 As New oMyFunctions.FunctionSet1
* Dim oFunctionSet2 As New oMyFunctions.FunctionSet2

Also:
- the DLL must be properly registered on the user machine so it is
recognized by Excel.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks for your advice. However, I'm not entirely sure what you mean.

I tried the following:

1. Adding a Public Declare Function XXX Lib "MyDLL" with arguments,
etc to the VBA underlying the Excel spreadsheet.

2. Adding a class module to the DLL containing my test function and
setting the instancing property to 6 GlobalMultiUse. When I did that,
the function became visible in the Globals part of the Object browser.
However, despite all that, I got #VALUE when I inserted my user-
defined function in a cell on the spreadsheet.

In the end, I decided to cheat and define my UDF simply in the VBA
behind the spreadsheet, which called up routines in the DLL to do the
real substantial work. That was a successful workaround.

Ideally, I should like to call the UDF directly from the Excel
worksheet. So, if anyone has a solution, I should be pleased to hear
it.

I suspect that I'm not far from solving my problem, but lack one or
two essential steps.

Ok, I see now what you're trying to do. This may require that your DLL
is a COMAddin so it runs 'in-process' to Excel rather than your VBA
project. I'm not even sure a UDF in a COMAddin will work, but I do know
that they work from an XLA.

Adding a declare to the function doesn't work from a cell. It will work
from code used by a function used in a cell (as you discovered).

The normal way UDFs are used is in VBA projects. Calling procs in a DLL
is allowed, but <AFAIKthe VBA project requires a ref to the DLL be set
in the 'Tools, References' dialog.

Here's a couple of really good books that explain how to implement
UDFs:
http://www.appspro.com/Books/Books.htm

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 17 July, 03:42, GS wrote:
JAC brought next idea :





On 16 July, 18:09, GS wrote:
JAC brought next idea :

I have built a DLL using VB6 to be used by Excel/VBA. The DLL contains
a number of functions and classes.

The classes are visible, but not the functions, according to the
object browser. Any functionality involving the classes is readily
usable. However, I cannot make use of the functions.
How can I make the functions visible/callable outside the DLL?
Thanks

The functions you want to access inside your DLL must be 'public'
methods within the main class, or any other class within the DLL, -and-
an object reference (via Tools, References) must be 'Set' in your code
to the specific class that contains the function you're trying to
access.

Example: <air code
* Dim oMyFunctions As New MyFunctionsDLL
* Dim oFunctionSet1 As New oMyFunctions.FunctionSet1
* Dim oFunctionSet2 As New oMyFunctions.FunctionSet2

Also:
- the DLL must be properly registered on the user machine so it is
recognized by Excel.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks for your advice. However, I'm not entirely sure what you mean.

I tried the following:

1. Adding a Public Declare Function XXX Lib "MyDLL" with arguments,
etc to the VBA underlying the Excel spreadsheet.

2. Adding a class module to the DLL containing my test function and
setting the instancing property to 6 GlobalMultiUse. When I did that,
the function became visible in the Globals part of the Object browser.
However, despite all that, I got #VALUE when I inserted my user-
defined function in a cell on the spreadsheet.

In the end, I decided to cheat and define my UDF simply in the VBA
behind the spreadsheet, which called up routines in the DLL to do the
real substantial work. That was a *successful workaround.

Ideally, I should like to call the UDF directly from the Excel
worksheet. So, if anyone has a solution, I should be pleased to hear
it.

I suspect that I'm not far from solving my problem, but lack one or
two essential steps.

Ok, I see now what you're trying to do. This may require that your DLL
is a COMAddin so it runs 'in-process' to Excel rather than your VBA
project. I'm not even sure a UDF in a COMAddin will work, but I do know
that they work from an XLA.

Adding a declare to the function doesn't work from a cell. It will work
from code used by a function used in a cell (as you discovered).

The normal way UDFs are used is in VBA projects. Calling procs in a DLL
is allowed, but <AFAIKthe VBA project requires a ref to the DLL be set
in the 'Tools, References' dialog.

Here's a couple of really good books that explain how to implement
UDFs:
*http://www.appspro.com/Books/Books.htm

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Thanks for your advice and thoughts, Garry. Much appreciated.

I will explore your ideas/thoughts.

However, in many ways I am quite happy with my workaround, pushing
most of the code into the DLL, but defining minimal UDFs in the
spreadsheet that call the code in the DLL to do the real work. It
seems quite a workable solution "without going over the top".

I hope that these collective comments help anyone else trying to do a
similar thing. It's good to share one's ideas, which underpins the
internet, I guess.

John