Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
I have a XLL mathematical library that I call though Application.Run
in Excel. This is a MAJOR performance bottleneck, and I am trying to see if I can call it directly though a wrapper instead. I found a discussion of how to call a XLL as if it were a DLL using xlcall32.dll on the net, and have compiled this up and put the resulting dll into the same directory as the XLL. I then tried to wrap the XLL's function using Private Declare Function inside Excel. However, this returns an error 53, File Not Found. I tried adding the complete path to the XLL, but this had no effect. This leads me to believe the error is misleading, and that it may mean "dll not registered"? Question1: Should I be able to use a complete path to find any dll no matter if they are reged or not? So then I tried registering the XLL, but this returns "LoadLibrary failed. GetLastError returns 0x000036b1. According to the very few hits on the 'net, this too is a completely generic message of dubious value. Question 2: Can an XLL be registered? As I understand it, XLL's are a strict superset of DLLs. Maury |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
Literally only seconds after posting I realized that I had typed in
the path with ".dll" instead of ".xll". Changing the extension made THAT part work at least. Now I'm getting this: Error 49, Bad DLL calling convention. I sort of expected I would get something like this, but just to be sure, does this mean I have written down the Private Declare Function parameter list incorrectly? Assuming this is the case... The XLL in question is passed a series of inputs that are represented as ranges on a sheet. We have adapted this to use 2D arrays of Double instead, which seems to pass into Application.Run just fine. But here's where I'm lost: should I declare the inputs like... ...., dates() as Double, conventions() as Double, ... or ...., dates as Any, conventions as Any, ... or even ...., dates as Variant, conventions as Variant, ... or maybe... ...., dates() as Variant, conventions() as Variant, ... I *sort of* understand the differences between these (well, not sure about the last two) but I can't say I *really* understand the nuances. Is something: Dim dates() as Double passed into calls as a Variant? Or Variant()? Or is there a difference in calling conventions between these? Thanks! Maury |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
I'm not sure you can declare xll functions as if a dll. In a quick test I
also get the same Error 49 message. Depending on the function, the error may occur after the function has worked perfectly (on error resume next prevents the msg). I suspect Application Run may be the only way to call xll functions in VBA. If you have the project files maybe you can rebuild as a dll and use Declare Function etc. Regards, Peter T "Maury Markowitz" wrote in message ... Literally only seconds after posting I realized that I had typed in the path with ".dll" instead of ".xll". Changing the extension made THAT part work at least. Now I'm getting this: Error 49, Bad DLL calling convention. I sort of expected I would get something like this, but just to be sure, does this mean I have written down the Private Declare Function parameter list incorrectly? Assuming this is the case... The XLL in question is passed a series of inputs that are represented as ranges on a sheet. We have adapted this to use 2D arrays of Double instead, which seems to pass into Application.Run just fine. But here's where I'm lost: should I declare the inputs like... ..., dates() as Double, conventions() as Double, ... or ..., dates as Any, conventions as Any, ... or even ..., dates as Variant, conventions as Variant, ... or maybe... ..., dates() as Variant, conventions() as Variant, ... I *sort of* understand the differences between these (well, not sure about the last two) but I can't say I *really* understand the nuances. Is something: Dim dates() as Double passed into calls as a Variant? Or Variant()? Or is there a difference in calling conventions between these? Thanks! Maury |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
On Apr 8, 12:31*pm, "Peter T" <peter_t@discussions wrote:
the msg). I suspect Application Run may be the only way to call xll functions in VBA. If this is the case, is there some "cost effective" way to do this in VB or VBA/Access? Maury |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
"Maury Markowitz" wrote
On Apr 8, 12:31 pm, "Peter T" <peter_t@discussions wrote: the msg). I suspect Application Run may be the only way to call xll functions in VBA. If this is the case, Don't take my report as definitive, only that it seems like that to me ! is there some "cost effective" way to do this in VB or VBA/Access? Presumably the xll functions were designed with use as cell formulas in mind. Maybe you can take advantage of that if you have a lot to do, eg Dim i as long Dim arr, arrResult as Variant qty = 10 ReDim arr(1 To qty, 1 To 1) For i = 1 To qty arr(i, 1) = i * 10 'populate arr Next Set ws = ActiveSheet ws.Range("A1").Resize(qty).Value = arr ws.Range("B1").Resize(qty).Formula = "=myXLL_foo(A1)" ws.Calculate ' if calc not already automatic arrResult = ws.Range("B1").Resize(UBound(arr)).Value Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
Actually seems it is possible to declare an xll just like a dll after all
Sadly William Hooper's excellent site seems to have been terminated for quite a while, but for anyone that has his demo functions (Anewxll) - I did this: Option Explicit Declare Function Junk1 Lib "whooperX.xll" ( _ ByVal d1 As Double, _ ByVal d1 As Double) As Double ' // First worksheet function example : Junk1 ' // Adds two numbers passed as doubles and returns a double ' double __stdcall Junk1( double d1, double d2) ' { ' return d1+d2; ' } Sub TestJunk() Dim d As Double ' to avoid hardcoding the xll's path can ' chdir to path of the xll, ChDir ThisWorkbook.Path d = Junk1(1.2, 2.5) Debug.Print d ' 3.7 End Sub What I had failed to do last time was include As Double for the function's return type. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... "Maury Markowitz" wrote On Apr 8, 12:31 pm, "Peter T" <peter_t@discussions wrote: the msg). I suspect Application Run may be the only way to call xll functions in VBA. If this is the case, Don't take my report as definitive, only that it seems like that to me ! is there some "cost effective" way to do this in VB or VBA/Access? Presumably the xll functions were designed with use as cell formulas in mind. Maybe you can take advantage of that if you have a lot to do, eg Dim i as long Dim arr, arrResult as Variant qty = 10 ReDim arr(1 To qty, 1 To 1) For i = 1 To qty arr(i, 1) = i * 10 'populate arr Next Set ws = ActiveSheet ws.Range("A1").Resize(qty).Value = arr ws.Range("B1").Resize(qty).Formula = "=myXLL_foo(A1)" ws.Calculate ' if calc not already automatic arrResult = ws.Range("B1").Resize(UBound(arr)).Value Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
On Apr 8, 3:55*pm, "Peter T" <peter_t@discussions wrote:
Actually seems it is possible to declare an xll just like a dll after all Sadly William Hooper's excellent site seems to have been terminated for quite a while, but for anyone that has his demo functions (Anewxll) - I did this: This is excellent news Peter, I can't thank you enough! I was trying to test this myself, but after downloading the XL API from MS I found that the version will build XLLs that will not run in XL2003, at least not with modification. Very frustrating, as you might imagine. I have a request, if I may be so bold. Do you have Access? If so, could you try the exact same code in Access to see what happens? You WILL need to use the xlcall32.dll and place it in the same directory as the xll. You can find this on the 'net very easily. You will also need to type in the full path to the XLL... Declare Function Junk1 Lib "c:\myxlldir\whooperX.xll"... You realize that if this works you've just disproven MS's own documentation? :-) Maury |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping a XLL as if it were a DLL
but after downloading the XL API from MS I found
that the version will build XLLs that will not run in XL2003, I didn't even know about the "XL API", maybe you can get hold of VisualC++ I have a request, if I may be so bold. Do you have Access? It's bust! But I tried in both Word and VB6, can't get it to work in either of these. This is what I did, let me know if I missed something - I already have a number of copies of xlcall32.dll (32kb v5.0) and placed one in the same folder as the xll. - I included the full path to the xll in the declaration, to double check I ran in Excel with the CurDir NOT that of the xll, and as before it worked fine. FWIW the xll was not 'loaded' in Excel like an addin. As before worked fine. Ran exact same code in both Word & VB6; in both I get Error 48 File not found C:\path\whooperX.xll Yes I'm sure the path is correct and ChDir to that of the xll makes no difference. Maybe you have an explanation ? You realize that if this works you've just disproven MS's own documentation? :-) It wouldn't be the first time but I'm not sure. I couldn't get all the functions in the xll to work. That may well be due to getting some parts of the declaration wrong or due to the function itself. I couldn't for example get this one to work - ////////////////////////////////////////////////////////////// // Function by William Hooper www.whooper.co.uk // 2d Lookup function ///////////////////////////////////////////////////////////// LPXLOPER __stdcall Lookup2d( LPXLOPER xRange, LPXLOPER xRow, LPXLOPER xCol ) Also, William Whooper in his own demo file instructs to use Application.Run in VBA If the dll type declaration can be made to work I can well see the attraction. Calling the simple xll function directly in a long loop vs Application.Run was a fraction of a second vs a coffee break! Regards, Peter T "Maury Markowitz" wrote in message ... On Apr 8, 3:55 pm, "Peter T" <peter_t@discussions wrote: Actually seems it is possible to declare an xll just like a dll after all Sadly William Hooper's excellent site seems to have been terminated for quite a while, but for anyone that has his demo functions (Anewxll) - I did this: This is excellent news Peter, I can't thank you enough! I was trying to test this myself, but after downloading the XL API from MS I found that the version will build XLLs that will not run in XL2003, at least not with modification. Very frustrating, as you might imagine. I have a request, if I may be so bold. Do you have Access? If so, could you try the exact same code in Access to see what happens? You WILL need to use the xlcall32.dll and place it in the same directory as the xll. You can find this on the 'net very easily. You will also need to type in the full path to the XLL... Declare Function Junk1 Lib "c:\myxlldir\whooperX.xll"... You realize that if this works you've just disproven MS's own documentation? :-) Maury |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Wrapping | Excel Discussion (Misc queries) | |||
column wrapping | Excel Discussion (Misc queries) | |||
wrapping text | Excel Worksheet Functions | |||
Text Wrapping | Excel Worksheet Functions | |||
wrapping | Excel Programming |