Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arrays through procedures
All,
I'm getting a compile error: Type mismatch: array or user-defined type expected on the "Call Sort(arrDate)" line. What should I be looking for to troubleshoot this? Below are the lines of code that deal with arrDate. Sub Button17_Click() ReDim arrDate(0) Call GetMonths(arrDate,arrSite) Call SiteSelected(arrDate,arrSite) End Sub Sub GetMonths(arrDate, arrSite as Variant) 'Here is where I build arrDate and send to Sort to get them in order Call Sort(arrDate) End Sub Sub Sort(arr() as Variant) 'Sort array in order End Sub Sub SiteSelected(arrDate,arrSite as Variant) 'Here arrDate is actually used End Sub I know my arrDate array gets built properly because it appears correctly in the SiteSelected Procedure when I skip the Sort Procedure. Clearly I don't know how to pass arrays through procedures effectively so any help with that would be appreciated also. -- Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arrays through procedures
Hi Brian, Sort is a reserved word in VBA code for the Sort function. When
you use it as a Macro name it conflicts with the internal libraries and throws an error. You need to modify the macro name. Something lik Srt or sSort or mSort...as long as the compiler can tell it is not the function Sort. "Brian" wrote in message ... All, I'm getting a compile error: Type mismatch: array or user-defined type expected on the "Call Sort(arrDate)" line. What should I be looking for to troubleshoot this? Below are the lines of code that deal with arrDate. Sub Button17_Click() ReDim arrDate(0) Call GetMonths(arrDate,arrSite) Call SiteSelected(arrDate,arrSite) End Sub Sub GetMonths(arrDate, arrSite as Variant) 'Here is where I build arrDate and send to Sort to get them in order Call Sort(arrDate) End Sub Sub Sort(arr() as Variant) 'Sort array in order End Sub Sub SiteSelected(arrDate,arrSite as Variant) 'Here arrDate is actually used End Sub I know my arrDate array gets built properly because it appears correctly in the SiteSelected Procedure when I skip the Sort Procedure. Clearly I don't know how to pass arrays through procedures effectively so any help with that would be appreciated also. -- Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arrays through procedures
The problem arises because your Sort function declares its input
parameter as an array of Variants: Sub Sort(arr() as Variant) This means that you must pass to it something that the compiler knows is an array. However, in your GetMonths procedure, you get arrDate as a single Variant, not an array of Variants. While a Variant can contain an array, it does not necessarily contain an array as far as the compiler is concerned. Thus, the compiler complains that it needs an array. You can change the declaration of your Sort procedure so that it expects a single Variant (which contains an array) rather than an array of Variants. Change Sub Sort(arr() as Variant) To Sub Sort(arr as Variant) Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 18 Jan 2010 11:14:01 -0800, Brian wrote: All, I'm getting a compile error: Type mismatch: array or user-defined type expected on the "Call Sort(arrDate)" line. What should I be looking for to troubleshoot this? Below are the lines of code that deal with arrDate. Sub Button17_Click() ReDim arrDate(0) Call GetMonths(arrDate,arrSite) Call SiteSelected(arrDate,arrSite) End Sub Sub GetMonths(arrDate, arrSite as Variant) 'Here is where I build arrDate and send to Sort to get them in order Call Sort(arrDate) End Sub Sub Sort(arr() as Variant) 'Sort array in order End Sub Sub SiteSelected(arrDate,arrSite as Variant) 'Here arrDate is actually used End Sub I know my arrDate array gets built properly because it appears correctly in the SiteSelected Procedure when I skip the Sort Procedure. Clearly I don't know how to pass arrays through procedures effectively so any help with that would be appreciated also. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arrays through procedures
Thanks, Chips solution worked, but this is still good to know.
-- Brian "JLGWhiz" wrote: Hi Brian, Sort is a reserved word in VBA code for the Sort function. When you use it as a Macro name it conflicts with the internal libraries and throws an error. You need to modify the macro name. Something lik Srt or sSort or mSort...as long as the compiler can tell it is not the function Sort. "Brian" wrote in message ... All, I'm getting a compile error: Type mismatch: array or user-defined type expected on the "Call Sort(arrDate)" line. What should I be looking for to troubleshoot this? Below are the lines of code that deal with arrDate. Sub Button17_Click() ReDim arrDate(0) Call GetMonths(arrDate,arrSite) Call SiteSelected(arrDate,arrSite) End Sub Sub GetMonths(arrDate, arrSite as Variant) 'Here is where I build arrDate and send to Sort to get them in order Call Sort(arrDate) End Sub Sub Sort(arr() as Variant) 'Sort array in order End Sub Sub SiteSelected(arrDate,arrSite as Variant) 'Here arrDate is actually used End Sub I know my arrDate array gets built properly because it appears correctly in the SiteSelected Procedure when I skip the Sort Procedure. Clearly I don't know how to pass arrays through procedures effectively so any help with that would be appreciated also. -- Brian . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arrays through procedures
Thanks Chip, it works like a charm.
-- Brian "Chip Pearson" wrote: The problem arises because your Sort function declares its input parameter as an array of Variants: Sub Sort(arr() as Variant) This means that you must pass to it something that the compiler knows is an array. However, in your GetMonths procedure, you get arrDate as a single Variant, not an array of Variants. While a Variant can contain an array, it does not necessarily contain an array as far as the compiler is concerned. Thus, the compiler complains that it needs an array. You can change the declaration of your Sort procedure so that it expects a single Variant (which contains an array) rather than an array of Variants. Change Sub Sort(arr() as Variant) To Sub Sort(arr as Variant) Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 18 Jan 2010 11:14:01 -0800, Brian wrote: All, I'm getting a compile error: Type mismatch: array or user-defined type expected on the "Call Sort(arrDate)" line. What should I be looking for to troubleshoot this? Below are the lines of code that deal with arrDate. Sub Button17_Click() ReDim arrDate(0) Call GetMonths(arrDate,arrSite) Call SiteSelected(arrDate,arrSite) End Sub Sub GetMonths(arrDate, arrSite as Variant) 'Here is where I build arrDate and send to Sort to get them in order Call Sort(arrDate) End Sub Sub Sort(arr() as Variant) 'Sort array in order End Sub Sub SiteSelected(arrDate,arrSite as Variant) 'Here arrDate is actually used End Sub I know my arrDate array gets built properly because it appears correctly in the SiteSelected Procedure when I skip the Sort Procedure. Clearly I don't know how to pass arrays through procedures effectively so any help with that would be appreciated also. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing arrays through procedures
I have had the same kind of problem recently on Excel 2007 and 2010.
Sub main() Dim aslocal() As String ReDim aslocal(2) aslocal(0) = "cats" aslocal(1) = "dogs" ' sub0 (aslocal()) 'ERROR Type mismatch.... ' sub0 (aslocal) 'ERROR Type mismatch.... ' fun0 (alocal()) 'ERROR Type mismatch.... ' fun0 (aslocal) 'ERROR Type mismatch.... ' fun1 (aslocal) 'ERROR Type mismatch.... Debug.Print aslocal(1) i = fun0(aslocal) Debug.Print aslocal(1) i = fun1(aslocal) Debug.Print aslocal(1) End Sub Sub sub0(ByRef as0() As String) as0(1) = "weasels" End Sub Function fun0(ByRef as0() As String) as0(1) = "wombats" End Function Function fun1(ByRef as0() As String) As Long as0(1) = "ferrets" fun1 = 0 End Function dogs wombats ferrets On Monday, January 18, 2010 2:14 PM Brian wrote: All, I am getting a compile error: Type mismatch: array or user-defined type expected on the "Call Sort(arrDate)" line. What should I be looking for to troubleshoot this? Below are the lines of code that deal with arrDate. Sub Button17_Click() ReDim arrDate(0) Call GetMonths(arrDate,arrSite) Call SiteSelected(arrDate,arrSite) End Sub Sub GetMonths(arrDate, arrSite as Variant) 'Here is where I build arrDate and send to Sort to get them in order Call Sort(arrDate) End Sub Sub Sort(arr() as Variant) 'Sort array in order End Sub Sub SiteSelected(arrDate,arrSite as Variant) 'Here arrDate is actually used End Sub I know my arrDate array gets built properly because it appears correctly in the SiteSelected Procedure when I skip the Sort Procedure. Clearly I do not know how to pass arrays through procedures effectively so any help with that would be appreciated also. -- Brian On Monday, January 18, 2010 2:31 PM JLGWhiz wrote: Hi Brian, Sort is a reserved word in VBA code for the Sort function. When you use it as a Macro name it conflicts with the internal libraries and throws an error. You need to modify the macro name. Something lik Srt or sSort or mSort...as long as the compiler can tell it is not the function Sort. On Monday, January 18, 2010 2:48 PM Chip Pearson wrote: The problem arises because your Sort function declares its input parameter as an array of Variants: Sub Sort(arr() as Variant) This means that you must pass to it something that the compiler knows is an array. However, in your GetMonths procedure, you get arrDate as a single Variant, not an array of Variants. While a Variant can contain an array, it does not necessarily contain an array as far as the compiler is concerned. Thus, the compiler complains that it needs an array. You can change the declaration of your Sort procedure so that it expects a single Variant (which contains an array) rather than an array of Variants. Change Sub Sort(arr() as Variant) To Sub Sort(arr as Variant) Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tuesday, January 19, 2010 2:20 PM Brian wrote: Thanks, Chips solution worked, but this is still good to know. -- Brian "JLGWhiz" wrote: On Tuesday, January 19, 2010 2:21 PM Brian wrote: Thanks Chip, it works like a charm. -- Brian "Chip Pearson" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Arrays to a Sub | Excel Programming | |||
Passing Variables Between Procedures | Excel Programming | |||
Calling Procedures from another excel sheet by passing objects | Excel Programming | |||
passing arrays between functions in VBA | Excel Programming | |||
Passing ARGUMENTS between event procedures of a USERFORM | Excel Programming |