Thanks to the generous help here I've made some progress in writing
array functions, lots more is working than when I started working
on this.

Now I'm trying to incorporate error checking and validation into
the functions. For example, a user might give a horizontal group
of cells or a vertical group, I'm not up to handling disconnected
cells yet. So, say, given a sheet with:

A B C
1 2 5 7
2 3
3 6

=Norm(A1:A3) works fine, Norm(A1:C1) gives Subscript out of range,
not surprising, but trying to fix this with =Gorm(A1:C1) gives
#VALUE! (And it is perfectly acceptable to dimension something
(3,1) but fails if you try (1,3) instead. However both of these
do seem to work if I pass an array to them (vertical to Norm and
horizontal to Gorm, so handling nested functions is working for me
now). Thus it seems that I still don't quite have the hang of
accepting Ranges.

I have tried various different ways of subscripting inside Gorn,
guessing that might be my problem, and that doesn't seem to help.
The problem appears to be limited to the case where I'm passed a
Range, when the TypeName of Vin1 is Variant both work correctly.

Option Base 1
Function Norm(VIn1 As Variant) As Double
Dim vaArr1 As Variant

'Convert parameter to array if not already array
If IsArray(VIn1) Then 'We got an array, so just use it
vaArr1 = VIn1
ElseIf TypeName(VIn1) = "Range" Then 'Read the range's values into an array
vaArr1 = VIn1.Value
End If

'Calculate the result using the array
Norm = Sqr(vaArr1(1, 1) ^ 2 + vaArr1(2, 1) ^ 2 + vaArr1(3, 1) ^ 2)

End Function

Function Gorm(VIn1 As Variant) As Double
Dim vaArr1 As Variant

'Convert parameter to array if not already array
If IsArray(VIn1) Then 'We got an array, so just use it
vaArr1 = VIn1
ElseIf TypeName(VIn1) = "Range" Then 'Read the range's values into an array
vaArr1 = VIn1.Value
End If

'Calculate the result using the array
Gorm = Sqr(vaArr1(1) ^ 2 + vaArr1(2) ^ 2 + vaArr1(3) ^ 2)
End Function

Would anyone be kind enough to give me a hint about what I'm missing here?

Next, a little style question. To use an On Error inside a function
I need to step around my error code. I'm assuming there is a way
of writing this that wouldn't make people who read this cry. Any
suggestion better than this?

Function Norm...
On Error GoTo Bad
....<<<ordinary code in function
Norm = ...
If 1 < 0 Then
Bad: MsgBox "Norm " & Err.Description
End If
End Function

And I suppose the next hurtle I will need to get over is when a
function is returning an array result where I need to know whether
the array is supposed to fill a horizontal or a vertical group of
cells. Am I correct that I need to have an array of a shape that
matches the destination of the function result? And how do I tell?

Thanks for all your help

"Don Taylor" wrote in message
...

=Norm(A1:A3) works fine, Norm(A1:C1) gives Subscript out of range,
not surprising, but trying to fix this with =Gorm(A1:C1) gives
#VALUE! (And it is perfectly acceptable to dimension something
(3,1) but fails if you try (1,3) instead. However both of these
do seem to work if I pass an array to them (vertical to Norm and
horizontal to Gorm, so handling nested functions is working for me
now). Thus it seems that I still don't quite have the hang of
accepting Ranges.

I have tried various different ways of subscripting inside Gorn,
guessing that might be my problem, and that doesn't seem to help.
The problem appears to be limited to the case where I'm passed a
Range, when the TypeName of Vin1 is Variant both work correctly.

The range is 2 dimensional so you need to handle both

Function Norm(VIn1 As Variant) As Double
Dim vaArr1 As Variant
Dim i As Long, j As Long
Dim tmp As Variant

'Convert parameter to array if not already array
If IsArray(VIn1) Then 'We got an array, so just use it
vaArr1 = VIn1
ElseIf TypeName(VIn1) = "Range" Then 'Read the range's values into an
array
vaArr1 = VIn1.Value
End If

'Calculate the result using the array
For i = LBound(vaArr1, 1) To UBound(vaArr1, 1)
For j = LBound(vaArr1, 2) To UBound(vaArr1, 2)
tmp = tmp + vaArr1(i, j) ^ 2
Next j
Next i
Norm = Sqr(tmp)

End Function


Next, a little style question. To use an On Error inside a function
I need to step around my error code. I'm assuming there is a way
of writing this that wouldn't make people who read this cry. Any
suggestion better than this?

Function Norm...
On Error GoTo Bad
...<<<ordinary code in function
Norm = ...
If 1 < 0 Then
Bad: MsgBox "Norm " & Err.Description
End If
End Function


Not really sure what you want hear but I strongly advise against a Msgbox in
a function. If there is an error in a function call, best to return the
error to the calling celol, otherwise you could have messages flashing up
all over.


And I suppose the next hurtle I will need to get over is when a
function is returning an array result where I need to know whether
the array is supposed to fill a horizontal or a vertical group of
cells. Am I correct that I need to have an array of a shape that
matches the destination of the function result? And how do I tell?

Surely, it is the other way around. The destination array should be
dependent on the result of the function? So the user could then copy the
formula to the correct number of cells, or add tests to handle no valid
result.