[RESOLVED] Passing parameters from VBA to ASM DLL
I'm planning to write a DLL in assembly language that is to be called from VBA code. I have googled before posting and that answered some of my questions but not all. (Google was so clever to also find "Assembly" when I said "Assembler" what gave me lots of .NET stuff I was definitely not looking for... :D)
The main thing I know so far is that VBA always uses the __stdcall calling convention for stuff like that.
What I didn't find is the way that the VBA data types are presented to the called function. I have to pass a string ByVal and a one-dimensional array of Longs ByRef to the single exposed function in the DLL. Will the string arrive as a pointer to a C-style string? And will it be ANSI, MBCS or even something else? Will the array be passed as a pointer to the bare data or is some data structure involved?
I posted this one here instead of the Assembly section because I hope I would find more VBA people here that already have called assembly routines from VBA than assembly people writing stuff to be called from VBA over there. I suppose what I'm trying to do is not really common...
TIA
Re: Passing parameters from VBA to ASM DLL
>...what gave me lots of .NET stuff I was definitely not looking for
Use -.net in next query to remove a lot of .bloat stuff...
As for what vb sends and c expects, it would be a lot easier if you had Visual Studio 6.0 because there are actual dll source files installed with VC++ and with it and the VB API viewer, it is easy to cross reference...
Good Luck
Re: Passing parameters from VBA to ASM DLL
Quote:
Originally Posted by
vb5prgrmr
Use -.net in next query to remove a lot of .bloat stuff...
Interesting. :) Will try that next time I need it. I actually tried Google's advanced search page and didn't see this option there, although it actually was there, almost right at the top... :o
Quote:
As for what vb sends and c expects, it would be a lot easier if you had Visual Studio 6.0 because there are actual dll source files installed with VC++ and with it and the VB API viewer, it is easy to cross reference...
C++ would be a good point to start as I already know that language. But, unfortunately, VS 6.0 is one of the things I do not have (unless it is identical to the '97 version, what I don't think it is). What does that VB API viewer do?
In the meantime, two more questions about that topic came to my mind, that I didn't think about in the first place:
How would I debug stuff like that? I'm pretty sure I can't stuff the entire Excel into the debugger.
Where is the best place to store a DLL like that? I know about the DLL lookup sequence, but of course there is no single word about Excel sheets in these docs. This DLL is meant to be used by only a single Excel workbook file, so %windir% or %windir%\System32 is most certainly not the preferred place to go.
TIA to all who might know that again... :)
Re: Passing parameters from VBA to ASM DLL
> But, unfortunately, VS 6.0 is one of the things I do not have (unless it is identical to the '97 version, what I don't think it is). What does that VB API viewer do?
Actually, 97 is 5.0 and it also has those files I am talking about (if I remember correctly). The VB API Viewer is what VB'ers use when they want to add some kind of system functionality to their program... API's from Sleep, QueryPerformanceCounter, GetDesktopWindow, SetWindowPos, SendMessage, and on and on. So what it does is you load the API file in the the program, type in the API you want to use, select public or private (for its declaration), click on add, and the correct API function/sub is ready to be copied into the vb program.
Well for the Dll part, as you would normally do...
Use your friends to search for creating an excel add in (yahoo, google, ask, answers, bing)
Good Luck
Re: Passing parameters from VBA to ASM DLL
Quote:
Originally Posted by
vb5prgrmr
Actually, 97 is 5.0 and it also has those files I am talking about (if I remember correctly).
Fine! :) So I think I could use it for that. I hope it won't collide with my XP pro SP3 though. I was always reluctant to install old developer packages like this one on newer Win versions since I am afraid the debug DLLs that come with them might replace some recent stuff with older one.
Quote:
The VB API Viewer is what VB'ers use when they want to add some kind of system functionality to their program... API's from Sleep, QueryPerformanceCounter, GetDesktopWindow, SetWindowPos, SendMessage, and on and on. So what it does is you load the API file in the the program, type in the API you want to use, select public or private (for its declaration), click on add, and the correct API function/sub is ready to be copied into the vb program.
Nice tool! :) I somehow always thought something like that should exist but I didn't know it. IMO typing in all these API declarations is one of the things most tideous about VB/VBA.
Quote:
Well for the Dll part, as you would normally do...
Well, calling a DLL from Excel doesn't look like one of these normal situations. Right now I think I would prefer storing the DLL in the same folder as the xls file, which I suppose to be the current working directory and so would be a place to look for the DLL. But the VBA code to call the DLL uses a file open dialog (commdlg) to acquire a file name prior to the DLL call. Couldn't that change the CWD?
Quote:
Use your friends to search for creating an excel add in (yahoo, google, ask, answers, bing)
I always thought add-ins were a different thing from DLLs, in particular they were (at least mostly) Excel-based, although of course they could call DLLs.
Re: Passing parameters from VBA to ASM DLL
Re: Passing parameters from VBA to ASM DLL
Quote:
Originally Posted by
dglienna
Nice resource too! :) I think this one is particularly useful for people who want to use the API functions from VBA and have no access to the VB API Viewer.
For a quick check-out I had a look at CreateFile() that I will need in my DLL (non-VBA though). And on the fly I learned that strings are apparently passed as ANSI, what I could deduce from the fact that this API is aliased to CreateFileA(). (You may have seen that this was one of my questions in an earlier post in this thread.)
Re: Passing parameters from VBA to ASM DLL
To get to a detailed answer about the original question:
You pass a string always ByVal to produce a pointer like C is expecting it.
To pass an array of longs by reference you declare the parameter of type long and pass it the first element of the array:
Code:
Dim myStr as String, myArr(128) As Long
Dim Res as Long
Declare Function MyDLLFun Lib "MyDll.dll" (ByVal aString as String, ByRef anArray as Long) As Long
...
Res = MyDLLFun(myStr, myArr(0))
Only, your DLL would have to know how big the array is. It cannot determine the arraysize, except if you give it another parameter being the number of elements in the array.
Re: Passing parameters from VBA to ASM DLL
Thank you for that concise answer, WoF! That was exactly what I needed. :)
Quote:
Originally Posted by
WoF
Only, your DLL would have to know how big the array is. It cannot determine the arraysize, except if you give it another parameter being the number of elements in the array.
The size of the array is no problem, it is always constant. (This is by nature of what I'm doing inside the DLL.)
Re: Passing parameters from VBA to ASM DLL
Then you won't have any problem. I did it that way many times.
Passing the first element of an array ByRef is the same as passing a pointer to the whole array, since all elements are consecutive in memory.
A string has to be always passed ByVal, however. Even if the dll function is to modify the string.
(Which is possible) Passing a string ByRef passes a pointer to the BSTR structure which is NOT a char pointer in C.
Re: Passing parameters from VBA to ASM DLL
Quote:
Originally Posted by
WoF
A string has to be always passed ByVal, however. Even if the dll function is to modify the string.
(Which is possible) Passing a string ByRef passes a pointer to the BSTR structure which is NOT a char pointer in C.
Thanks for the clarification. I would most certainly have messed that up when trying it the first time without your tip. ;)
I have looked up the BSTR structure in the meantime. It's not that scary at all, unlike, for example, the VARIANT structure. And I suppose I would need the BSTR if I wanted to change the length of the string in the callee.
BTW: The BSTR pretty much reminds me of the way Basic interpreters of the pre-IBM-PC era stored their strings. So it's not that unfamiliar at all... :)
Re: Passing parameters from VBA to ASM DLL
No, it's not unfamiliar at all, also not scary. I remember MBasic on CP/M did it similar. Well, no wonder, for isn't MBasic (and later Commodore Basic) Bill Gates first real product? ;-)
Only by convention the ByVal delivers a real char pointer usable for C.
But you should refrain from changing the length of a string, (except maybe for making it shorter).
But I wouldn't do even that, because it could cause leaks in the garbage collection.
Strings are always modified by VB in that a new string is created which contains the modifications and the old string is 'deallocated' or put for the garbage collector.
Re: Passing parameters from VBA to ASM DLL
Quote:
Originally Posted by
WoF
Well, no wonder, for isn't MBasic (and later Commodore Basic) Bill Gates first real product? ;-)
I think you're right, and the TRS-80 Level II Basic was another one of them. But it doesn't look like he was particularly proud of them, as neither his own name nor the term MS showed up in their initial messages. (Or did it in MBasic? I'm not really sure about that.)
Quote:
Only by convention the ByVal delivers a real char pointer usable for C.
I think it would be really hard to use Windows' API functions if it wouldn't be handled that way... ;)
Quote:
But you should refrain from changing the length of a string, (except maybe for making it shorter).
You mean I shouldn't even use SysReAllocString() and SysReAllocStringLen()? I supposed them to have been made for exactly that purpose.
Re: Passing parameters from VBA to ASM DLL
Well, if you are so sophisticated to use the reallocation functions I wouldn't mind you doing with a string as you like. ;)
I only warned from randomly changing the length value.
I think, Mr. Gates SOLD the initial Basic interpreters to Commodore or Tandy, so as they were theirs.
Whereas, guess what the M in MBasic would stand for.
Re: Passing parameters from VBA to ASM DLL
WoF, now I'm back to let you know that I got it working! :)
I had to do some learning the hard way, though: Initially I didn't remember your instuctions correctly, declared the array parameter to the DLL routine as ByRef lplCounts() As Long and passed the array as Counts(). That gave me a nice crash of the entire Excel. God knows where the DLL routine was writing to, but it pretty sure was not my array. :rolleyes: Then I looked up your post again, instantly corrected the faulty code and it ran.
And then there was the question about where to put the DLL, that remained unanswered in this thread. As the DLL is exclusively used by a single .xls file, I put it in the same directory as that file. This works fine until the user (the testing me in that case) changes the directory while picking a file to process. In that case the DLL will not be found (no surprise though). To fix this, I inserted the following line between picking the file and calling the DLL routine:
Code:
ChDir ActiveWorkbook.Path ' Make sure the DLL will be found
This doesn't only fix the problem, interestingly it does not affect the directory that the file picker dialog will start up with next time.
Thanks again to all contributors. All posts were quite valuable, but in particular WoF gave me the core competence I needed for that project. :)
:wave:
