Making progress with array functions, another two questions

[Don Taylor]

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

 

[Bob Phillips]

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

 

[Don Taylor]

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 B22 named White, =Norm(White) works, great!

But now with B44 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,

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!

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

 

[Bob Phillips]

With this change, and B22 named White, =Norm(White) works, great!

But now with B44 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.

 

[Don Taylor]

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)

 

[Bob Phillips]

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)