Type mismatch when calling sub with array of worksheets argument

[Benjamin]

Hi,

I have a Sub with the following signature:

Sub SendSheet( _
ByRef awksSheets() As Worksheet, _
ByVal strReceiver As String, _
ByVal strSubject As String, _
ByVal strBody As String)

When I call it as follows

SendSheet Array(Workheets(1),Worksheets(2)), _
"(e-mail address removed)", "Subject", "Text body"

I get the error "Compile error: Type mismatch: array or user-defined
type expected". Why is that? How do I have to call this Sub?

Any pointers are greatly appreciated.

Cheers, Benjamin

 

[NickHK]

If you check the help for the Array function, you see "Returns a Variant
containing an array.". So
?TypeName (Array(1, 2, 3))
Variant()

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

NickHK

 

[Benjamin]

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

 

[NickHK]

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