Type mismatch when calling sub with array of worksheets argument
Benjamin,
These work for me:
Private Sub CommandButton2_Click()
Dim WS(1 To 2) As Worksheet
Set WS(1) = Worksheets(1)
Set WS(2) = Worksheets(2)
MsgBox arrCountSheets(WS())
MsgBox varCountSheets(Array(Worksheets(1), Worksheets(2)))
'Compiler catches mismatch on this; Variant cannot go into an array of
objects
'MsgBox arrCountSheets(Array(Worksheets(1), Worksheets(2)))
'This is OK, as an array of objects can go into a Variant
MsgBox varCountSheets(WS())
End Sub
Private Function varCountSheets(argSheets As Variant) As Long
varCountSheets = UBound(argSheets) - LBound(argSheets) + 1
End Function
Private Function arrCountSheets(argSheets() As Worksheet) As Long
arrCountSheets = UBound(argSheets) - LBound(argSheets) + 1
End Function
NickHK
"Benjamin"
egroups.com...
NickHK wrote:
So you would either need to create a true array of Worksheets
Dim WS(1 To 2) As Worksheet
Set WS(1)=Workheets(1)
Set WS(2)=Workheets(2)
or change the sub signature to accept a Variant instead of an array of
WS's
Hi Nick,
thank you for the answer. Oddly enough, changing the signature to
accepting a Variant still produces the same error, but creating a true
array works. And is anyway the more proper solution, I feel.
Benjamin
|