Hank Scorpio wrote ...

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.

How about using your own custom class collection class that wraps the
collection object and only allows Excel.Worksheet objects in? e.g.

' --- Revised code in DLLTestClass
Option Explicit

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

TestMethodDLL = False
On Error GoTo ErrorHandler

MsgBox TargetWorksheets.Count

TestMethodDLL = True

ExitPoint:

Exit Function

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

End Select

Resume ExitPoint

End Function


' --- Code in new CWorksheets class (in VB6 project)
Option Explicit

Private m_colWorksheets As Collection

Public Property Get Item(ByVal Index As Variant) As Excel.Worksheet
Set Item = m_colWorksheets.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
Set NewEnum = m_colWorksheets.[_NewEnum]
End Property

Public Sub Add(ByVal ExcelWorksheet As Excel.Worksheet)
m_colWorksheets.Add ExcelWorksheet, ExcelWorksheet.Name
End Sub

Public Property Get Count() As Long
Count = m_colWorksheets.Count
End Property

Public Function Remove(ByVal Index As Variant) As Boolean
On Error Resume Next
m_colWorksheets.Remove Index
Remove = CBool(Err.Number = 0)
End Function

Private Sub Class_Initialize()
Set m_colWorksheets = New Collection
End Sub


' --- Code in Excel standard module
Option Explicit

Sub TestDLLVersion()

Dim obj As New DLLTestClass

Dim obja_wks As CWorksheets
Set obja_wks = New CWorksheets

obja_wks.Add Sheet3
obja_wks.Add Sheet2

obj.TestMethodDLL Application, Sheet1, obja_wks

Set obj = Nothing

End Sub

Jamie.

--