I am extending a DLL that we have written for Excel VBA to call.

One of the new functions has 21 arguments. However, Excel
refuses to compile the VBA code and says that it cannot find
the function. Is there a limit on the number of arguments for
DLL functions in Excel VBA ?

Thanks,
Lynn McGuire

Lynn,

The compile issue sounds like it might be something other than a parameter
issue. Are references set correctly and pointing to the right DLL. Do you
have binary compatability set when compiling the DLL? If not, your code
might be refering to an older version of the dll file.

Otherwise, I ran into this a while ago with a straight function call in VBA
between add-ins using Application.Run, which has a limit of 30 parameters.
The work around I put in place was to pass a collection object as one
parameter. The calling routine sets up the collection items one by one and
the called routine reads each collection item as a parameter. Long winded,
but it works.

HTH,

--
Robin Hammond
www.enhanceddatasystems.com


"Lynn McGuire" wrote in message
...
I am extending a DLL that we have written for Excel VBA to call.

One of the new functions has 21 arguments. However, Excel
refuses to compile the VBA code and says that it cannot find
the function. Is there a limit on the number of arguments for
DLL functions in Excel VBA ?

Thanks,
Lynn McGuire

The compile issue sounds like it might be something other than a parameter issue. Are references set correctly and pointing to the
right DLL. Do you have binary compatability set when compiling the DLL? If not, your code might be refering to an older version of
the dll file.

I am building the DLL and then trying to use it. I have imported
a new BAS file for it to Excel VBA. Here is the def:

Declare Sub CalculateIsentropicCompressor Lib "DII2VBAS.DLL" _
Alias "_CalculateIsentropicCompressor@84" _
(ByVal NumberOfStreamsIn As Long, ByVal NumberOfStreamsOut As Long, _
ByVal NumberOfComponents As Long, ByVal padtodouble As Long, _
InletStreamTemperatures As Double, _
InletStreamPressures As Double, _
InletStreamMolarFlowrates As Double, _
InletStreamMolarFlowratesByComponent As Double, _
OutletStreamVaporFractions As Double, _
OutletStreamTemperatures As Double, _
OutletStreamPressures As Double, _
OutletStreamEnthalpies As Double, _
OutletStreamEntropies As Double, _
OutletStreamMolarFlowrates As Double, _
OutletStreamMolarFlowratesByComponent As Double, _
WorkAvailable As Double, _
IsentropicEfficiency As Double, _
PressureOutSpec As Double , _
CalculatedElectricalKwUsed As Double, _
CalculatedWorkUsed As Double, _
CalculatedIsentropicHead As Double)

Otherwise, I ran into this a while ago with a straight function call in VBA between add-ins using Application.Run, which has a
limit of 30 parameters. The work around I put in place was to pass a collection object as one parameter. The calling routine sets
up the collection items one by one and the called routine reads each collection item as a parameter. Long winded, but it works.

Here is the VBA call to my DLL. Fairly straight forward.

' compress to two outlet streams, vapor and liquid (if any)
CalculateIsentropicCompresssor Nin, Nout2, Ncomp, padtodouble, _
InletStreamTemperature(0), InletStreamPressure(0), _
InletStreamMolarFlowrate(0), _
InletStreamMolarFlowrateByComponent(0), _
OutletStreamVaporFraction(0), _
OutletStreamTemperature(0), OutletStreamPressure(0), _
OutletStreamEnthalpy(0), OutletStreamEntropy(0), _
OutletStreamMolarFlowrate(0), _
OutletStreamMolarFlowrateByComponent(0), _
WorkAvailable, IsentropicEfficiency, PressureOutSpec, _
CalculatedElectricalKwUsed, CalculatedWorkUsed, _
CalculatedIsentropicHead

Lynn

Here is the VBA call to my DLL. Fairly straight forward.

' compress to two outlet streams, vapor and liquid (if any)
CalculateIsentropicCompresssor Nin, Nout2, Ncomp, padtodouble, _
InletStreamTemperature(0), InletStreamPressure(0), _
InletStreamMolarFlowrate(0), _
InletStreamMolarFlowrateByComponent(0), _
OutletStreamVaporFraction(0), _
OutletStreamTemperature(0), OutletStreamPressure(0), _
OutletStreamEnthalpy(0), OutletStreamEntropy(0), _
OutletStreamMolarFlowrate(0), _
OutletStreamMolarFlowrateByComponent(0), _
WorkAvailable, IsentropicEfficiency, PressureOutSpec, _
CalculatedElectricalKwUsed, CalculatedWorkUsed, _
CalculatedIsentropicHead

I retyped this entire DLL call from VBA and it started working. Weird.

Lynn

As mentioned above, there is a limit to the number of parameters.
Perhaps you are calling the C/C++ function from your VBA code as a
means of creating a worksheet function out of a C/C++ DLL. The fastest
way to do this is through an XLL add-in. These use the Excel C API to
register user defined functions.

RapidXLL_NET automatically builds XLL add-ins ( as well as .NET
libraries ) from native C / C++ headers. Download a free trial at
http://www.RapidXLL.net The tool also allows a bit of customization.
You can default parameters and hide the them from worksheet users so
that the 21 parameter functions comes down to just 5 parameters. You
can also re-name the function in the process.

---
The RapidXLL Team
http://www.RapidXLL.net




T
Lynn McGuire wrote:
I am extending a DLL that we have written for Excel VBA to call.

One of the new functions has 21 arguments. However, Excel
refuses to compile the VBA code and says that it cannot find
the function. Is there a limit on the number of arguments for
DLL functions in Excel VBA ?

Thanks,
Lynn McGuire