View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
NickHK NickHK is offline
external usenet poster
 
Posts: 4,391
Default 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