Array Parameters as Variants Only

Am I correct in my assumption that I can only pass arrays in the form of
variants to a procedure as a parameter.

For example, the following function always works for arrays
Function DoNothing(ByRef ArrIn as variant)
DoNothing = arrIn
End Function

While the following function never seems to work for arrays even if they are
dimensioned as integer arrays.
Function DoNothing(ByRef ArrIn as integer)
DoNothing = arrIn
End Function

Regards,
Wayne C.

Sub Main()
Dim arr() As Integer
Dim v As Variant
ReDim arr(1 To 3)
For i = 1 To 3
arr(i) = i
Next
v = DoNothing(arr)
For i = LBound(v) To UBound(v)
Debug.Print i, v(i)
Next
End Sub


Function DoNothing(ByRef ArrIn() As Integer)
DoNothing = ArrIn
End Function


works for me.

--
Regards,
Tom Ogilvy



"TheVisionThing" <WCressman@etelligence.com> wrote in message
news:tEX0e.3469$c76.1431@newssvr11.news.prodigy.com...
> Am I correct in my assumption that I can only pass arrays in the form of
> variants to a procedure as a parameter.
>
> For example, the following function always works for arrays
> Function DoNothing(ByRef ArrIn as variant)
> DoNothing = arrIn
> End Function
>
> While the following function never seems to work for arrays even if they
are
> dimensioned as integer arrays.
> Function DoNothing(ByRef ArrIn as integer)
> DoNothing = arrIn
> End Function
>
> Regards,
> Wayne C.
>
>

TheVisionThing wrote:
> Am I correct in my assumption that I can only pass arrays in the form of
> variants to a procedure as a parameter.

No. Perhaps you are thinking about the parameters of a ParamArray array.

Alan Beban
>
> For example, the following function always works for arrays
> Function DoNothing(ByRef ArrIn as variant)
> DoNothing = arrIn
> End Function
>
> While the following function never seems to work for arrays even if they are
> dimensioned as integer arrays.
> Function DoNothing(ByRef ArrIn as integer)
> DoNothing = arrIn
> End Function
>
> Regards,
> Wayne C.
>
>

Your code snippet works when the array is called within a Sub. I have seen
and tried code snippets from other posts using the same approach you
outlined,but have never been able to get it to work as a worksheet function
call. If the function is used in a cell on an Excel worksheet and the array
values are called from a range on a worksheet, the result is the #VALUE error
message

For example, the values 1,2,3 are in Rows C3:C5 and named ArrIn. When
=DoNothing(ArrIn) or DoNothing(ArrIn()) is entered in Cell D7 , the result is
always #VALUE! .

Am I missing something?

thebaje

"Tom Ogilvy" wrote:

> Sub Main()
> Dim arr() As Integer
> Dim v As Variant
> ReDim arr(1 To 3)
> For i = 1 To 3
> arr(i) = i
> Next
> v = DoNothing(arr)
> For i = LBound(v) To UBound(v)
> Debug.Print i, v(i)
> Next
> End Sub
>
>
> Function DoNothing(ByRef ArrIn() As Integer)
> DoNothing = ArrIn
> End Function
>
>
> works for me.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "TheVisionThing" <WCressman@etelligence.com> wrote in message
> news:tEX0e.3469$c76.1431@newssvr11.news.prodigy.com...
> > Am I correct in my assumption that I can only pass arrays in the form of
> > variants to a procedure as a parameter.
> >
> > For example, the following function always works for arrays
> > Function DoNothing(ByRef ArrIn as variant)
> > DoNothing = arrIn
> > End Function
> >
> > While the following function never seems to work for arrays even if they
> are
> > dimensioned as integer arrays.
> > Function DoNothing(ByRef ArrIn as integer)
> > DoNothing = arrIn
> > End Function
> >
> > Regards,
> > Wayne C.
> >
> >
>
>
>

ArrIn isn't an array. It is a range.

In that situation you could do

Public Function Test(arr As Range) As Variant
Dim vArr as Variant
vArr = arr.Value
Test = vArr
End Function

Assuming you wanted to do something to the values of the cells in the range
before you passed back the answer.

--
Regards,
Tom Ogilvy


"thebaje" <thebaje@discussions.microsoft.com> wrote in message
news:4B355C2C-C7A8-47E7-8D40-9CA9E20ED18B@microsoft.com...
> Your code snippet works when the array is called within a Sub. I have seen
> and tried code snippets from other posts using the same approach you
> outlined,but have never been able to get it to work as a worksheet
function
> call. If the function is used in a cell on an Excel worksheet and the
array
> values are called from a range on a worksheet, the result is the #VALUE
error
> message
>
> For example, the values 1,2,3 are in Rows C3:C5 and named ArrIn. When
> =DoNothing(ArrIn) or DoNothing(ArrIn()) is entered in Cell D7 , the result
is
> always #VALUE! .
>
> Am I missing something?
>
> thebaje
>
> "Tom Ogilvy" wrote:
>
> > Sub Main()
> > Dim arr() As Integer
> > Dim v As Variant
> > ReDim arr(1 To 3)
> > For i = 1 To 3
> > arr(i) = i
> > Next
> > v = DoNothing(arr)
> > For i = LBound(v) To UBound(v)
> > Debug.Print i, v(i)
> > Next
> > End Sub
> >
> >
> > Function DoNothing(ByRef ArrIn() As Integer)
> > DoNothing = ArrIn
> > End Function
> >
> >
> > works for me.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "TheVisionThing" <WCressman@etelligence.com> wrote in message
> > news:tEX0e.3469$c76.1431@newssvr11.news.prodigy.com...
> > > Am I correct in my assumption that I can only pass arrays in the form
of
> > > variants to a procedure as a parameter.
> > >
> > > For example, the following function always works for arrays
> > > Function DoNothing(ByRef ArrIn as variant)
> > > DoNothing = arrIn
> > > End Function
> > >
> > > While the following function never seems to work for arrays even if
they
> > are
> > > dimensioned as integer arrays.
> > > Function DoNothing(ByRef ArrIn as integer)
> > > DoNothing = arrIn
> > > End Function
> > >
> > > Regards,
> > > Wayne C.
> > >
> > >
> >
> >
> >

thebaje wrote:
> Your code snippet works when the array is called within a Sub. I have seen
> and tried code snippets from other posts using the same approach you
> outlined,but have never been able to get it to work as a worksheet function
> call. If the function is used in a cell on an Excel worksheet and the array
> values are called from a range on a worksheet, the result is the #VALUE error
> message
>
> For example, the values 1,2,3 are in Rows C3:C5 and named ArrIn. When
> =DoNothing(ArrIn) or DoNothing(ArrIn()) is entered in Cell D7 , the result is
> always #VALUE! .

The above is simply wrong. Perhaps there is something in your code for
DoNothing that causes it. With 1,2,3 in C3:C5, which is named "ArrIn", with
Function DoNothing(whatever) As Integer
DoNothing = Application.Sum(whatever)
End Function

then on the worksheet =DoNothing(ArrIn) returns 6

Alan Beban
>
> Am I missing something?
>
> thebaje