Array function ouput

[PBezucha]

Hi people,

If a function operation should be done on one array to get another one of
the same dimension, there is probably a single way, how to pass values for
output – as Array function (correct?). When attempting to do this in the
simplest case, as it follows, I was very surprised (xl2000).

Function Operation(A As Variant) As Variant
Operation = Array(A(1), A(2))
End Function

If the selection for the array-function Operation was row range, then the
result values were correct. If, however, the range was column, only the first
of argument values ( A(1) ) appeared in both two cells. I understand this has
something to do with two-dimensional feature of A. Where I can learn more
about the topic? Or, directly, if the assumption of the only way via Array
function is valid, could the function distinguish between row and column
output selection in one-dimensional case?
What would be the best way to fill in Array with all N arguments where N is
the dimension of arbitrary input array A?

With thanks and regards

 

[Bob Phillips]

Function Operation(A As Variant) As Variant
If A.Rows.Count > 1 Then
If A.Columns.Count > 1 Then
Operation = Array(A(1, 1), A(1, 2))
Else
Operation = Array(A(1, 1), A(2, 1))
End If
Else
Operation = Array(A(1, 1), A(1, 2))
End If

End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[PBezucha]

Hello Bob,
Many thanks, anyway. The gist I have expressed wrongly was, however, the
effect of localization of the function output. The resulting array has
different outcomes even in the utmost simple case

Function Operation()As Variant
Operation = Array(1,2)
End Function

according to whether two selected cells are arranged in column or row.
Obviously this must be treated quite differently.

 

[PBezucha]

Luckily I came across CPearson. After him it was elementary:

Option Explicit
Function Operation(A As Variant) As Variant
Dim B() As Double, I As Long, N As Long
N = A.Count
ReDim B(1 To N)
For I = 1 To N
B(I) = A(I).Value ^ 2 'operation example
Next I
If Application.Caller.Rows.Count > 1 Then
Operation = Application.WorksheetFunction.Transpose(B)
Else: Operation = B
End If
End Function