This is an esoteric one which may or may not get any responses, but
even random thoughts would be useful. (I hope that Don will forgive me
for lapsing into a cross post here, but I think that both groups are
relevant. I haven't been able to find anything on this after Googling
both the VB and Excel groups, nor anything in the KnowledgeBase.)

I'm writing a VB6 (SP6) DLL which is intended to be called from /
automate all versions of Office from 97 onwards. Objects are early
bound. In view of KB articles 244167 and 245115 (regarding the
automation of multiple versions of Office), the DLL contains a
reference to the earliest supported version of Excel; that is,
Excel8.olb. However I ran into a compile error in Excel (argument type
mismatch, described below) when I tried calling one of the library's
methods.

I created a second dummy .dll with the same Excel library reference
just to ensure that it wasn't a problem specific to the first project.
(It wasn't.) The dummy contains one class called DLLTestClass, which
has one (very simplified) method:

Function TestMethodDLL( _
xlApp As Excel.Application, _
SourceWorksheet As Excel.Worksheet, _
ByRef TargetWorksheets() As Excel.Worksheet _
) As Boolean

TestMethodDLL = False
On Error GoTo ErrorHandler

MsgBox UBound(TargetWorksheets)

TestMethodDLL = True

ExitPoint:

Exit Function

ErrorHandler:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description

End Select

Resume ExitPoint

End Function

I compiled the dummy dll without any problem, then opened an Excel
2002 workbook, set a reference to the dll, and included the following
in a standard module:

Sub TestDLLVersion()

Dim obj As New DLLTestClass

Dim obja_wks() As Excel.Worksheet

ReDim obja_wks(1)

Set obja_wks(0) = Sheet2
Set obja_wks(1) = Sheet3

obj.TestMethodDLL Application, Sheet1, obja_wks

Set obj = Nothing

End Sub

When I run it, I get a compile error relating to the obja_wks variable
in the method call line; specifically, "ByRef Argument Type Mismatch".

If I put an identical class into the Excel workbook itself, and create
a second procedure which sets the obj variable to THAT class instead
of the dll's one, no such error occurs. This set me t'thinkin. I
therefore saved the workbook, and re-opened it in Excel 97. This time
there was no compile error in the procedure which calls the dll, and
the procedure ran perfectly.

Just to prove the point (and yes, I do have one), I changed the
reference in the dummy DLL to the Excel 10 library, recompiled it,
opened the workbook in Excel 2002, re-set the reference to the dummy
dll and... no compile error.

Conclusion: Using a reference to the oldest Office library will
generally allow you to automate that and subsequent versions... UNLESS
you're passing an array of objects from that library (or maybe just
SOME objects?), in which case you're toast unless you've specified the
library for the EXACT Office version that you're using. I've already
written a stack of methods which use all manner of individual objects;
the application object, a workbook, a worksheet, a range, a Word
document... and despite calling them from 97, 2000 AND 2002, this is
the first time I've had a problem.

Which means that I'll presumably have to kludge some unsatisfactory
workaround. (I haven't decided what yet, but I'm not crazy about the
idea of using collections, variants or generic late bound objects
since in all cases I'd need to somehow check that the passed argument
is indeed a worksheet. Which is what I was hoping to avoid, but
probably won't be able to...)

Anyone else come across this?


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *