Can I still register a function in a DLL using ExecuteExcel4Macro REGISTER?

D

dwidavidwilkinson

Hi,

I have created by own simple DLL using C in VS 2010, which contains just one function.

Looking at some of the articles etc. on the web, it is suggested I can register this function in VBA using some code like this:

Sub VBARegisterFunction()

Dim sP As String, sDQ As String

sDQ = Chr(34) 'Double quotes

sP = ""
sP = sP & sDQ & "C:\Users\Owner\docs\vs2010\Projects\Test\Test.dll" & sDQ & ","
sP = sP & sDQ & "cube" & sDQ & ","
sP = sP & sDQ & "BB" & sDQ & ","
sP = sP & sDQ & "cube" & sDQ & ",,1"

Application.ExecuteExcel4Macro ("REGISTER(" & sP & ")")

End Sub

without going to the trouble of creating a full blown XLL, which is attractive just to get one simple function running.

When I run this code, I see no visible error, and I am aware that the function if successfully registered, is hidden. But I get #Value! errors when I try to reference the function in a cell.

Does anyone know, what I am doing wrong, or if this feature still works in Excel 2007/2010?

Thanks
 
W

witek

Hi,

I have created by own simple DLL using C in VS 2010, which contains just one function.

Looking at some of the articles etc. on the web, it is suggested I can register this function in VBA using some code like this:

Sub VBARegisterFunction()

Dim sP As String, sDQ As String

sDQ = Chr(34) 'Double quotes

sP = ""
sP = sP & sDQ & "C:\Users\Owner\docs\vs2010\Projects\Test\Test.dll" & sDQ & ","
sP = sP & sDQ & "cube" & sDQ & ","
sP = sP & sDQ & "BB" & sDQ & ","
sP = sP & sDQ & "cube" & sDQ & ",,1"

Application.ExecuteExcel4Macro ("REGISTER(" & sP & ")")

End Sub

without going to the trouble of creating a full blown XLL, which is attractive just to get one simple function running.

When I run this code, I see no visible error, and I am aware that the function if successfully registered, is hidden. But I get #Value! errors when I try to reference the function in a cell.

Does anyone know, what I am doing wrong, or if this feature still works in Excel 2007/2010?

Thanks
#value error means that registration work.
Error must be somewhere else.
I will need you C source. At least header of cube function at the beginning.
 
D

dwidavidwilkinson

Hi witek:

cube.c:

double _stdcall square (double *x)
{
return *x * *x;
}

I have no header..

cube.def:

LIBRARY "square"
EXPORTS
square

Thanks
 
W

witek

Hi witek:

cube.c:

double _stdcall square (double *x)
{
return *x * *x;
}

I have no header..

cube.def:

LIBRARY "square"
EXPORTS
square

Thanks


something does not match

1.
is your function named square or cube?
You export square and try to register cube

2.
"B" as I remember is a double type. not a pointer. I am not sure but if
you get funny output or it crashes try double square (double x).
 
D

dwidavidwilkinson

Hi,

Apologies it is square.c

I changed square.c to:

double _stdcall square (double x)
{
return x * x;
}

and it worked.. I appreciate your help. There are so few working examples available to learn from.

Thank you
 
D

dwidavidwilkinson

With the grateful help of witek I got this to work. This is what I did:

(1) Using Visual Studio Express 2010, I created a project called squareDLL consisting of:

square.c:

double _stdcall square (double x)
{
return x * x;
}

square.def:

LIBRARY "square"
EXPORTS
square

and stored it at this location:

C:\Users\Owner\Documents\Visual Studio 2010\Projects\squareDLL\Debug\squareDLL.dll

(2) In my Excel I setup:

Private Sub Workbook_Open()

Dim sParm As String, sDQ As String

sDQ = Chr(34) 'Double quotes

sParm = ""
sParm = sParm & sDQ & "C:\Users\Owner\Documents\Visual Studio 2010\Projects\squareDLL\Debug\squareDLL.dll" & sDQ & ","
sParm = sParm & sDQ & "square" & sDQ & ","
sParm = sParm & sDQ & "BB" & sDQ & ","
sParm = sParm & sDQ & "square" & sDQ & ",,1"

Application.ExecuteExcel4Macro ("REGISTER(" & sParm & ")")

End Sub

(3) I was then be able to use the square function, in a cell in my worksheet, like this:

=square(C2)
=square(45)
 
D

dwidavidwilkinson

With the grateful help of witek I got this to work. This is what I did:

(1) Using Visual Studio Express 2010, I created a project called squareDLL consisting of:

square.c:

double _stdcall square (double x)
{
return x * x;
}

square.def:

LIBRARY "square"
EXPORTS
square

and stored it at this location:

C:\Users\Owner\Documents\Visual Studio 2010\Projects\squareDLL\Debug\squareDLL.dll

(2) In Excel I setup:

Private Sub Workbook_Open()

Dim sParm As String, sDQ As String

sDQ = Chr(34) 'Double quotes

sParm = ""
sParm = sParm & sDQ & "C:\Users\Owner\Documents\Visual Studio 2010\Projects\squareDLL\Debug\squareDLL.dll" & sDQ & ","
sParm = sParm & sDQ & "square" & sDQ & ","
sParm = sParm & sDQ & "BB" & sDQ & ","
sParm = sParm & sDQ & "square" & sDQ & ",,1"

Application.ExecuteExcel4Macro ("REGISTER(" & sParm & ")")

End Sub

(3) I was then able to use the square function, in a cell in my worksheet, like this:

=square(C2)
=square(45)
 

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

Top