Hi Hank,

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've been burnt passing arrays of non-simple types between Excel and
VB6 dlls too and always assumed it's COM's marshalling getting in the
way. My suggestion would be to make it easy on the users of the DLL by
defining the object As Variant and allowing them to pass a single
sheet, array of sheets, collection of sheets or an Excel Sheets object:

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

Dim vItem As Variant
Dim oSht As Excel.Worksheet

TestMethodDLL = False
On Error GoTo ErrorHandler

'Just one sheet?
If TypeName(TargetWorksheets) = "Worksheet" Then
Set oSht = TargetWorksheets
MsgBox oSht.Name

'An array, Collection or Sheets object?
ElseIf IsArray(TargetWorksheets) Or _
TypeName(TargetWorksheets) = "Collection" Or _
TypeName(TargetWorksheets) = "Sheets" Then

'Loop through them, checking for worksheets
For Each vItem In TargetWorksheets
If TypeName(vItem) = "Worksheet" Then
Set oSht = vItem
MsgBox oSht.Name
End If
Next
End If

TestMethodDLL = True

ExitPoint:

Exit Function

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

End Select

Resume ExitPoint

End Function


Tested using the following Excel code:

Sub TestDLLVersion()

Dim obj As New class1
Dim col As Collection
Dim obja_wks() As Excel.Worksheet

'A single sheet
obj.TestMethodDLL Application, Sheet1, Sheet2

'An array of sheets
ReDim obja_wks(1)
Set obja_wks(0) = Sheet2
Set obja_wks(1) = Sheet3
obj.TestMethodDLL Application, Sheet1, obja_wks

'A collection of sheets
Set col = New Collection
col.Add Sheet2
col.Add Sheet3
obj.TestMethodDLL Application, Sheet1, obja_wks

'An Excel Sheets collection
obj.TestMethodDLL Application, Sheet1, ActiveWindow.SelectedSheets

Set obj = Nothing

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie