Type mismatch when calling sub with array of worksheets argument
 

Hi,

I have a Sub with the following signatu

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)), _
", "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

Type mismatch when calling sub with array of worksheets argument
 

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" wrote in message
ps.com...
Hi,

I have a Sub with the following signatu

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)), _
", "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

Type mismatch when calling sub with array of worksheets argument
 

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

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