Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
Hi!
I just took the first few steps in writing a little DLL that should be called from Excel/VBA, and I stumbled upon some wierd behaviour. The function in my DLL is declared as int __stdcall foo( const char *t ); and is used in VBA via Declare Function foo Lib "C:\foo\foo.dll" (ByVal t As String) As Long I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). However, if I wrap foo with Function call_foo(t As String) As Long call_foo = foo(t) End Function and use the formula =call_foo("xyz"), everything works ok. Any ideas? Regards, Matthias PS. Any other hints to "VBA calls DLL" documentation are appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
hi,
you can't directly call the dll function on the worksheet. if you like to do, you have to make XLL library that is a kind of dll. XLL is a special programming model for EXCEL. please refer this http://support.microsoft.com/default...NoWebContent=1 " wrote: Hi! I just took the first few steps in writing a little DLL that should be called from Excel/VBA, and I stumbled upon some wierd behaviour. The function in my DLL is declared as int __stdcall foo( const char *t ); and is used in VBA via Declare Function foo Lib "C:\foo\foo.dll" (ByVal t As String) As Long I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). However, if I wrap foo with Function call_foo(t As String) As Long call_foo = foo(t) End Function and use the formula =call_foo("xyz"), everything works ok. Any ideas? Regards, Matthias PS. Any other hints to "VBA calls DLL" documentation are appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
Hi!
sjoo wrote: " wrote: I just took the first few steps in writing a little DLL that should be called from Excel/VBA, and I stumbled upon some wierd behaviour. [...] I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). you can't directly call the dll function on the worksheet. In fact, I can, but lacking the correct result. Any guesses for the technical reason? if you like to do, you have to make XLL library that is a kind of dll. XLL is a special programming model for EXCEL. please refer this http://support.microsoft.com/default...NoWebContent=1 Sounds interesting, but I don't have the mentioned "Microsoft Excel 97 Developer's Kit" available. Plus, Excel 97 is pretty old. What do I have to do when developing for more recent versions? Regards, Matthias |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
does this help ?
http://support.microsoft.com/kb/106553/EN-US/ NickHK wrote in message s.com... Hi! sjoo wrote: " wrote: I just took the first few steps in writing a little DLL that should be called from Excel/VBA, and I stumbled upon some wierd behaviour. [...] I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). you can't directly call the dll function on the worksheet. In fact, I can, but lacking the correct result. Any guesses for the technical reason? if you like to do, you have to make XLL library that is a kind of dll. XLL is a special programming model for EXCEL. please refer this http://support.microsoft.com/default...NoWebContent=1 Sounds interesting, but I don't have the mentioned "Microsoft Excel 97 Developer's Kit" available. Plus, Excel 97 is pretty old. What do I have to do when developing for more recent versions? Regards, Matthias |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
Hi!
NickHK schrieb: wrote in message s.com... sjoo wrote: " wrote: [...] I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). you can't directly call the dll function on the worksheet. In fact, I can, but lacking the correct result. Any guesses for the technical reason? if you like to do, you have to make XLL library that is a kind of dll. XLL is a special programming model for EXCEL. please refer this http://support.microsoft.com/default...NoWebContent=1 Sounds interesting, but I don't have the mentioned "Microsoft Excel 97 Developer's Kit" available. Plus, Excel 97 is pretty old. What do I have to do when developing for more recent versions? does this help ? http://support.microsoft.com/kb/106553/EN-US/ No, sorry. The example DLL and VB code looks like mine, and I have no problems when calling the DLL from VBA functions or macros. The problem arises when calling it from wihtin Excel. Regards, Matthias |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
I'm no C expert at all, but..
I would assume it has to do with how VBA sends the string to your DLL. In VBA, internally, it is held as Unicode in a BSTR, which works fine when passed ByVal to your function. IIRC, you are actually passing a pointer to a pointer of the string. However, going directly from the worksheet, your are not actually passing the same thing (possibly not even in a BSTR) and hence only the first character is resolved instaed of the whole string. As a trivial example, using this Windows calls Public Declare Function lstrlenA Lib "kernel32" (ByVal lpString As String) As Long in a VBA function gives lstrlenA("qwerty") equal to 6. Correct. From the worksheet, lstrlenA("qwerty")=1. Wrong So I would guess you have to give VBA chance to create a variable so it can pass the string correctly. i.e. create a dummy VBA function to call each of your DLL functions that invlove strings. NickHK groups.com... Hi! NickHK schrieb: wrote in message s.com... sjoo wrote: " wrote: [...] I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). you can't directly call the dll function on the worksheet. In fact, I can, but lacking the correct result. Any guesses for the technical reason? if you like to do, you have to make XLL library that is a kind of dll. XLL is a special programming model for EXCEL. please refer this http://support.microsoft.com/default...NoWebContent=1 Sounds interesting, but I don't have the mentioned "Microsoft Excel 97 Developer's Kit" available. Plus, Excel 97 is pretty old. What do I have to do when developing for more recent versions? does this help ? http://support.microsoft.com/kb/106553/EN-US/ No, sorry. The example DLL and VB code looks like mine, and I have no problems when calling the DLL from VBA functions or macros. The problem arises when calling it from wihtin Excel. Regards, Matthias |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
Hi!
NickHK wrote: groups.com... NickHK schrieb: wrote in message s.com... sjoo wrote: " wrote: [...] I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). you can't directly call the dll function on the worksheet. In fact, I can, but lacking the correct result. Any guesses for the technical reason? I'm no C expert at all, but.. I would assume it has to do with how VBA sends the string to your DLL. In VBA, internally, it is held as Unicode in a BSTR, which works fine when passed ByVal to your function. IIRC, you are actually passing a pointer to a pointer of the string. The C function accepts a pointer to type char; you can interpret this as a pointer to the string in memory, if you like to. [...] As a trivial example, using this Windows calls Public Declare Function lstrlenA Lib "kernel32" (ByVal lpString As String) As Long in a VBA function gives lstrlenA("qwerty") equal to 6. Correct. From the worksheet, lstrlenA("qwerty")=1. Wrong Very fine example. I will reuse it for further examination regarding this problem. So I would guess you have to give VBA chance to create a variable so it can pass the string correctly. i.e. create a dummy VBA function to call each of your DLL functions that invlove strings. For now, this will be the way I'll do it. Regards, Matthias |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
Hi!
NickHK schrieb: egroups.com... schrieb: I just took the first few steps in writing a little DLL that should be called from Excel/VBA, and I stumbled upon some wierd behaviour. The function in my DLL is declared as int __stdcall foo( const char *t ); and is used in VBA via Declare Function foo Lib "C:\foo\foo.dll" (ByVal t As String) As Long I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). Let me answer my own post: Excel passes the string as some kind of wide-character. Using const wchar_t *t in my function works perfectly. Now you mention that, changing the declare of lstrlenA to the Wide version; Public Declare Function lstrlenW Lib "kernel32" (ByVal lpString As String) As Long now works fine the worksheet also. IIRC normally when calling window function from VB there is Unicode ANSI conversion. Hence the "A" versions of these function are used. If I wanted to use the W version it would be Public Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long and call it with lstrlenW(strPtr("MyString")) From the worksheet, seems the conversion does not occur. Indeed. I just found the following document: http://msdn.microsoft.com/library/de...ce03082001.asp It says: "Although VBA uses Unicode internally, it converts all strings to ANSI strings before calling a function in a DLL". Thus, both Excel and VBA use Unicode internally, but only VBA insists on conversion. Sad situation -- I still have no better option than wrapping functions in VBA, otherwise my DLL would have to provide two different functions for each operation. Regards, Matthias |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arguments from VBA to DLL
wrote in message ups.com... Hi! NickHK schrieb: egroups.com... schrieb: I just took the first few steps in writing a little DLL that should be called from Excel/VBA, and I stumbled upon some wierd behaviour. The function in my DLL is declared as int __stdcall foo( const char *t ); and is used in VBA via Declare Function foo Lib "C:\foo\foo.dll" (ByVal t As String) As Long I noticed that foo didn't work internally as I expected and added writing *t to a file on each call of foo as a debugging measure. According to this output, only the first character of String t seems to be passed to foo when called directly from Excel (putting =foo("xyz") in a cell). Let me answer my own post: Excel passes the string as some kind of wide-character. Using const wchar_t *t in my function works perfectly. Now you mention that, changing the declare of lstrlenA to the Wide version; Public Declare Function lstrlenW Lib "kernel32" (ByVal lpString As String) As Long now works fine the worksheet also. IIRC normally when calling window function from VB there is Unicode ANSI conversion. Hence the "A" versions of these function are used. If I wanted to use the W version it would be Public Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long and call it with lstrlenW(strPtr("MyString")) From the worksheet, seems the conversion does not occur. Indeed. I just found the following document: http://msdn.microsoft.com/library/de...ce03082001.asp It says: "Although VBA uses Unicode internally, it converts all strings to ANSI strings before calling a function in a DLL". Thus, both Excel and VBA use Unicode internally, but only VBA insists on conversion. Sad situation -- I still have no better option than wrapping functions in VBA, otherwise my DLL would have to provide two different functions for each operation. Regards, Matthias Yes, given that one converts and other does not, it would be better to expose your function as wrapper to the private Declares. Otherwise, you will have to rely on the user knowing to call the W version from the worksheet and the A version from VBA. NickHK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Arguments in Excell | Excel Worksheet Functions | |||
Passing arguments to userforms | Excel Programming | |||
Passing arguments to VB Function | Excel Programming | |||
Passing Arguments | Excel Programming | |||
Passing arguments to a sub routine... | Excel Programming |