Pass workbook name to ActiveX DLL

[meldrum_scotland]

I have a VBA routine which I pass some variables to an ActiveX DLL
(VB6). I can do it with integers and longs but when I try to do it
with anything more sophisticated it crashes.

I'd like to be able to pass a the active workbook name (so the DLL
can
upon it).


Any help much appreciated.


Best


Meldrum

 

[Peter T]

Ensure there's an appropriate argument in the procedure in the entry class
of your dll.

Public Function foo(objXL as Object, objWB as workbook, ByRef arg1, ByVal
arg2) as long

Now you're into the dll, call whatever you internal procedures want, passing
the object ref's of course. Remember to explicitly qualify any Excel objects
which in VBA are fine implicit, eg

objXL.ActiveWorkbook.ActiveCell

Above "As Object" assumes late binding. It'll be easier if you set a
reference in Project / References to your Excel, then declare as you would
in Excel but prefix with Excel.

Public Function foo(xlApp as Excel.Application, wb as Excel,Workbook, etc
Dim rng as Excel.Range

If you are not sure any user might have an earlier version than the one you
set a reference to, convert back to late binding before releasing.


In Excel, you could call the procedure with something like this

Dim cEntry as myAx.ClassName
Set cEntry = New myAx.ClassName

result = cEntry(application, activeworkbook, 123, "abc")


Regards,
Peter T

 

[Chip Pearson]

First of all, ensure that the ActiveX DLL has a reference to the Excel
object library (and, for good measure, the Office library). Then, you can
use those data types in your DLL. E.g.,

[in DLL named MyDLL. class named MyClass]
Public Function GetSum(RR As Excel.Range) As Double
Dim R As Excel.Range
Dim D As Double
For Each R In RR.Cells
D = D+R.Value
Next R
GetSum = D
End Function

You would then reference MyDLL in the References dialog in VBA, and then
call the function from VBA with code like

Dim D As Double
D = MyDLL.GetSum(Range("A1:A10")

When I write ActiveX DLLs, I always prefix the non-native VB objects with
the library in which they are defined. That is, I use "As Excel.Range"
rather than "As Range". While this is not always necessary, it provides some
level of self-documentation. Moreover, it prevents potential name
collisions. For example, suppose you have a DLL that references both Excel
and Word. In both Excel and Word, there is an object named "Range", but
these are entirely different objects. By including the "Excel" library
prefix, you ensure that the compiler uses the Excel definition of a Range,
not the Word definition.

with anything more sophisticated it crashes.

In what sense does it "crash". The generic term "crash" is used to mean any
number of undesirable outcomes.

Can you provide an example procedure that you think should work but actually
"crashes"?

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)