Making progress with array functions, another two questions

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" <[email protected]> wrote in message
news:[email protected]...
>
> =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" <[email protected]> writes:
>"Don Taylor" <[email protected]> wrote in message
>news:[email protected]...
>> =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

Ah... that is enlightening. Thank you for the help. I really
wish I could find a manual covering things like this. But that
just shows my age.

>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 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

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.

Maybe the problem lies in subtracting named ranges, check that.
But a similar function =Cross(Red-Black,Y-Black)<ctrl><shift><enter>
that maps vectors to vectors is working correctly, using the same
method I had shown for dealing originally for handling parameters
being passed. And =Cross(Red,White) fails with Subscript out of Range,
with or without <ctrl><shift><enter>.

>> 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 cell, otherwise you could have messages flashing up
>all over.

I understand. At the moment I'm writing page after page of lines like

{=(White-Black)*Dot(Black-x,Cross(Red-Black,Y-Black))/Norm(White-Black,Cross(Red-Black,Y-Black))}

(with Cross, Dot and Norm vector functions from 3-d geometry/calculus)
and each of those lines then named ranges to be used in subsequent
lines, each line having some physical significance, you will be able
to get out your calipers and measure each of those when the result
comes back from the machine shop.

I'm the first to admit this isn't the style of programming that I'm
used to. With my current groping around trying to connect old
programming skills to a Excel VBA mindset I'm much happier to see
a little message telling me that I still have a problem deep inside
of Cross or Dot or Norm than I would be trying to fill every cell
on the sheet with code to help me diagnose why it just says #VALUE!

>> 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.

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." As soon as I recognize how I have
misunderstood your help above it appears that I can correctly deal
with arguments in either vertical or horizontal form. That's good.
Then I'll try to make it friendly enough to cope with the users.

Thanks again

"Don Taylor" <[email protected]> wrote in message
news:[email protected]...
> 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

=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.

"Bob Phillips" <[email protected]> writes:
>"Don Taylor" <[email protected]> wrote in message
>news:[email protected]...
>> 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)