Passing arrays through procedures

[Brian]

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.

 

[JLGWhiz]

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.

 

[Chip Pearson]

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]

 

[Brian]

Thanks, Chips solution worked, but this is still good to know.

 

[Brian]

Thanks Chip, it works like a charm.
--
Brian

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]

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.

.

 

[Malcolm Windham]

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

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]