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.

"Bob Phillips" writes:
"Don Taylor" wrote in message
...
With this change, and B2:D2 named White, =Norm(White) works, great!

But now with B4:D4 named Red, =Norm(White-Red)<ctrl<shift<Enter
fails with Subscript out of Range. How can it be out of range when
your code explicitly tests for the bounds? I'm confused.

Bets way IMO to deal with this is to allow a variable number of arguments.
This code handloes that, so you call with

What had been originally suggested for checking arguments was:

If IsArray(VIn1) Then 'We got an array, so just use it
vaArr1 = VIn1
ElseIf TypeName(VIn1) = "Range" Then 'Read range values into array
vaArr1 = VIn1.Value
End If

and then using vaArr1 for calculations.

What I figured out this morning was that Range and Array were
both falling into the first alternative. Now I'm having more
success with

If TypeName(VIn1) = "Variant()" Then 'We got an array, so just use it
vaArr1 = VIn1
For i = LBound(vaArr1, 1) To UBound(vaArr1, 1)
tmp = tmp + vaArr1(i) ^ 2
Next i
ElseIf TypeName(VIn1) = "Range" Then 'Read range values into array
vaArr1 = VIn1.Value
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
Else
MsgBox "Norm unexpected TypeName:" & TypeName(VIn1)
End If

which works for a horizontal range or a difference of horizontal
ranges or a function returning an array. The only case that doesn't
now work is where I give it {A1;A2;A3}

=Norm(A1:A3,A1:C1) or
=Norm(White,Red) or
=Norm(A1:A3) or
=Norm({2,3,6}) or
=Norm({2,3,6},{1,2,3})

Note that this is not an array formula, so you don't need the
Ctrl-Shift-Enter.

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

For k = LBound(VIn1) To UBound(VIn1)
'Convert parameter to array if not already array
If TypeName(VIn1(k)) = "Range" Then 'Read the range's values into an
array
vaArr1 = VIn1(k).Value
'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
ElseIf IsArray(VIn1(k)) Then 'We got an array, so just use it
vaArr1 = VIn1(k)
'Calculate the result using the array
For i = LBound(vaArr1) To UBound(vaArr1)
tmp = tmp + vaArr1(i) ^ 2
Next i
End If

Next k

Norm = Sqr(tmp)

End Function

The size of the resulting vector, or scalar, certainly depends on the
function. But some folks have always written their vectors in columns
and others have always written their vectors in rows, and some switch
back and forth in the same sheets. My naive hope was that I could
tell whether they had selected a group of cells in a row or column
when they were entering the vector function and thus I could return
the result in "the correct shape."

You can test whether the vector is 1 column wide or 1 row wide, but what if
it is nxn? Also, with the multiple arguments, some could be row vectors,
some could be column vectors.

I am confused now. I had spotted that problem with arrays and ranges and
reversed the tests.

What about the problem with multiple ranges/arrays, did my suggestion help?

--

HTH

RP
(remove nothere from the email address if mailing direct)