Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
ByRef argument type mismatch error? | Excel Programming |