Advice for VBA functions using arrays

[Don Taylor]

I've tried to do my homework, reading Walkenbach and searching the net for
answers before posting this. I'd appreciate any advice on improving this.

I'm trying to help someone do a little project handling arrays.

First:

I found an example implementing the Cross Product function in
the Microsoft techbase.

Option Base 1
'based on http://support.microsoft.com/default.aspx?scid=kb;en-us;121820
'Usage: select C1:C3, type =C(A1:A3,B1:B3) and then <ctrl><shift><enter>
Function Cross(Vec1 As Object, Vec2 As Object) As Variant
Dim TempArray(3, 1)
TempArray(1, 1) = Vec1.Cells(2, 1).Value * Vec2.Cells(3, 1).Value - Vec1.Cells(3, 1).Value * Vec2.Cells(2, 1).Value
TempArray(2, 1) = Vec1.Cells(3, 1).Value * Vec2.Cells(1, 1).Value - Vec1.Cells(1, 1).Value * Vec2.Cells(3, 1).Value
TempArray(3, 1) = Vec1.Cells(1, 1).Value * Vec2.Cells(2, 1).Value - Vec1.Cells(2, 1).Value * Vec2.Cells(1, 1).Value
Cross = TempArray
End Function

The Microsoft example uses .Value everywhere but no other examples
I've seen use this

'Well, that seems to work, so try a dot or inner product of two vectors
Function Dot(Vec1 As Object, Vec2 As Object) As Double
' Dot = Vec1.Cells(1, 1).Value * Vec2.Cells(1, 1).Value + Vec1.Cells(2, 1).Value * Vec2.Cells(2, 1).Value + Vec1.Cells(3, 1).Value * Vec2.Cells(3, 1).Value
Dot = Vec1.Cells(1, 1) * Vec2.Cells(1, 1) + Vec1.Cells(2, 1) * Vec2.Cells(2, 1) + Vec1.Cells(3, 1) * Vec2.Cells(3, 1)
End Function

'And try to find the Norm or length of a vector
Function Norm(Vec1 As Object) As Double
' Norm = Sqr(Vec1.Cells(1, 1).Value * Vec1.Cells(1, 1).Value + Vec1.Cells(2, 1).Value * Vec1.Cells(2, 1).Value + Vec1.Cells(3, 1).Value * Vec1.Cells(3, 1).Value)
Norm = Sqr(Vec1.Cells(1, 1) * Vec1.Cells(1, 1) + Vec1.Cells(2, 1) * Vec1.Cells(2, 1) + Vec1.Cells(3, 1) * Vec1.Cells(3, 1))
End Function

I've tested it with and without .Value and they seem to give the same
result. Is there some good explanation about when this is needed and
when it isn't? And are there any other simplifications I could make
to these definitions?

Next:

What I really need to do is use these functions within each other at
times and I can't figure out why that isn't working.

For example, if I
select C1:C3 and then type =Cross(A1:A3,B1,B3)<ctrl><shift><enter>
it works. But if I
select C1:C3 and then type =Cross(Cross(A1:A3,B1:B3),B1:B3))<ctrl><shift><enter>
it doesn't give the cross product of the cross and B1:B3.

I thought Cross was returning an array that could then be used to
fill a range of cells OR be used just like an array of cells to
be given to a function, like cross, that accepts arrays of cells.

Thanks

 

[Tim Williams]

value is the default property for a range, so it *usually* doesn't matter if
you leave it out. However, it is good practise to include it.

Can't help with the second part.

Tim

I've tried to do my homework, reading Walkenbach and searching the net for
answers before posting this. I'd appreciate any advice on improving this.

I'm trying to help someone do a little project handling arrays.

First:

I found an example implementing the Cross Product function in
the Microsoft techbase.

Option Base 1
'based on http://support.microsoft.com/default.aspx?scid=kb;en-us;121820
'Usage: select C1:C3, type =C(A1:A3,B1:B3) and then <ctrl><shift><enter>
Function Cross(Vec1 As Object, Vec2 As Object) As Variant
Dim TempArray(3, 1)
TempArray(1, 1) = Vec1.Cells(2, 1).Value * Vec2.Cells(3, 1).Value -

Vec1.Cells(3, 1).Value * Vec2.Cells(2, 1).Value

TempArray(2, 1) = Vec1.Cells(3, 1).Value * Vec2.Cells(1, 1).Value -

Vec1.Cells(1, 1).Value * Vec2.Cells(3, 1).Value

TempArray(3, 1) = Vec1.Cells(1, 1).Value * Vec2.Cells(2, 1).Value -

Vec1.Cells(2, 1).Value * Vec2.Cells(1, 1).Value

Cross = TempArray
End Function

The Microsoft example uses .Value everywhere but no other examples
I've seen use this

'Well, that seems to work, so try a dot or inner product of two vectors
Function Dot(Vec1 As Object, Vec2 As Object) As Double
' Dot = Vec1.Cells(1, 1).Value * Vec2.Cells(1, 1).Value + Vec1.Cells(2,

1).Value * Vec2.Cells(2, 1).Value + Vec1.Cells(3, 1).Value * Vec2.Cells(3,
1).Value

Dot = Vec1.Cells(1, 1) * Vec2.Cells(1, 1) + Vec1.Cells(2, 1) *

Vec2.Cells(2, 1) + Vec1.Cells(3, 1) * Vec2.Cells(3, 1)

End Function

'And try to find the Norm or length of a vector
Function Norm(Vec1 As Object) As Double
' Norm = Sqr(Vec1.Cells(1, 1).Value * Vec1.Cells(1, 1).Value +

Vec1.Cells(2, 1).Value * Vec1.Cells(2, 1).Value + Vec1.Cells(3, 1).Value *
Vec1.Cells(3, 1).Value)

Norm = Sqr(Vec1.Cells(1, 1) * Vec1.Cells(1, 1) + Vec1.Cells(2, 1) *

Vec1.Cells(2, 1) + Vec1.Cells(3, 1) * Vec1.Cells(3, 1))

 

[Stephen Bullen]

Hi Don,

What I really need to do is use these functions within each other at
times and I can't figure out why that isn't working.

For example, if I
select C1:C3 and then type =Cross(A1:A3,B1,B3)<ctrl><shift><enter>
it works. But if I
select C1:C3 and then type =Cross(Cross(A1:A3,B1:B3),B1:B3))<ctrl><shift><enter>
it doesn't give the cross product of the cross and B1:B3.

I thought Cross was returning an array that could then be used to
fill a range of cells OR be used just like an array of cells to
be given to a function, like cross, that accepts arrays of cells.

The problem is that the code sample you're working from is only designed to handle
ranges as arguments, not general arrays, and only 3x1 arrays as well. To write the
formula properly, you need code to do the following:

1. Check the type of the first input variable
2. If it's an array, make sure it's a 2D array, converting it if not.
3. If it's a range, check it's only one area and more than one cell
(and maybe that it's a specific size).
4. If the range is an OK size and shape, read the values into an array (using
vArr1 = TheRange.Value)
5. Repeat 1-4 for the second parameter
6. You now have two 2D arrays, so you have to check they're the same size and
contain numbers (not text).
7. Finally loop through the arrays using LBound() and UBound() to get their
dimensions, to calculate the result.


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Don Taylor]

Hi Don,

The problem is that the code sample you're working from is only designed
to handle ranges as arguments, not general arrays, and only 3x1 arrays
as well. To write the formula properly, you need code to do the following:

For the problem I'm working on I'll always use only 3x1 arrays.
Cross product actually makes no sense for any other arguments.

1. Check the type of the first input variable
2. If it's an array, make sure it's a 2D array, converting it if not.
3. If it's a range, check it's only one area and more than one cell
(and maybe that it's a specific size).
4. If the range is an OK size and shape, read the values into an array (using
vArr1 = TheRange.Value)
5. Repeat 1-4 for the second parameter
6. You now have two 2D arrays, so you have to check they're the same size and
contain numbers (not text).
7. Finally loop through the arrays using LBound() and UBound() to get their
dimensions, to calculate the result.

Thank you for the guidelines.
For the problems I'm working on the result will always be
either a range of 3 cells in a spreadsheet or the result
of having previously applied the function to 3 cells, or
repeating this process. But I'm guessing that I just
don't understand something about the variable types.

Is there a resource somewhere that explains dealing with
things like this? The handful of books that I've found
on the subject of Excel and VBA don't seem to provide any
detail for dealing with this subject. (Old style procedural
programming isn't a problem for me, I just need some
documentation that gives the low level details and I can
take it from there)

Thanks again

 

[Stephen Bullen]

Hi Don,

For the problems I'm working on the result will always be
either a range of 3 cells in a spreadsheet or the result
of having previously applied the function to 3 cells, or
repeating this process. But I'm guessing that I just
don't understand something about the variable types.

OK. Whenever you write a UDF, there are numerous things that can be passed in to any parameters you have:

Public Function MyFunc(vParam As Variant) As String
MyFunc = TypeName(vParam)
End Function


A single cell: =MyFunc(A1)
Multiple cells in one area: =MyFunc(A1:A3)
A range of multiple areas: =MyFunc((A1:A3,B1:B3))
A single number: =MyFunc(10)
A string: =MyFunc("Hello")
A 1D (horizontal) array: =MyFunc({10,20,"OK"})
A 2D array: =MyFunc({1,2;3,4;5,6})
An array resulting from a UDF: =MyFunc(MyFunc(<whatever>))

So the first thing most UDFs need to do is to check whether they've been given something they can use, which we do by checking either the TypeName
or using IsArray().

A UDF will always return either a single value (number/string/error) or an array of values (but very rarely a range). So if you want to nest your
functions, you need to design it to accept either ranges or arrays.

In your case, you can use either a 3x1 Range or a 3x1 array. To make the processing easier for us, we can read all the values from a Range into an
array by just reading its Value:

mvMyArray = rngMyRange.Value

So if we ignore error handling and input validation, a Cross() function to handle either ranges or arrays might look something like this:

Public Function Cross(vIn1 As Variant, vIn2 As Variant) As Variant

Dim vaArr1 As Variant
Dim vaArr2 As Variant
Dim vaResult As Variant

'Convert parameters to arrays

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

'Same for second param
If IsArray(vIn2) Then
vaArr2 = vIn2

ElseIf TypeName(vIn2) = "Range" Then
'Read the range's values into an array
vaArr2 = vIn2.Value
End If

'If array assignments failed, bail out
If IsEmpty(vaArr1) Or IsEmpty(vaArr2) Then
Cross = CVErr(xlErrValue)
Exit Function
End If

'If we got this far, we were given arrays
'or multi-cell ranges, so we should check
'they're the correct size.

ReDim vaResult(1 To 3, 1 To 1)

'Calculate the result using the arrays
vaResult(1, 1) = vaArr1(2, 1) * vaArr2(3, 1) - vaArr1(3, 1) * vaArr2(2, 1)
vaResult(2, 1) = vaArr1(3, 1) * vaArr2(1, 1) - vaArr1(1, 1) * vaArr2(3, 1)
vaResult(3, 1) = vaArr1(1, 1) * vaArr2(2, 1) - vaArr1(2, 1) * vaArr2(1, 1)

'Return the result
Cross = vaResult

End Function

Is there a resource somewhere that explains dealing with
things like this? The handful of books that I've found
on the subject of Excel and VBA don't seem to provide any
detail for dealing with this subject. (Old style procedural
programming isn't a problem for me, I just need some
documentation that gives the low level details and I can
take it from there)

Not that I know of - other than asking questions here!

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Alan Beban]

Hi Don,
. . .
So if we ignore error handling and input validation, a Cross() function to handle either ranges or arrays might look something like this:

Public Function Cross(vIn1 As Variant, vIn2 As Variant) As Variant

Dim vaArr1 As Variant
Dim vaArr2 As Variant
Dim vaResult As Variant

'Convert parameters to arrays

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

Since IsArray returns True for both arrays and multi-cell ranges (at
least in xl2000 and earlier), why not simply

If IsArray(vIn1) Then vaArr1 = vIn1

Alan Beban

 

[Stephen Bullen]

Hi Alan,

Since IsArray returns True for both arrays and multi-cell ranges (at
least in xl2000 and earlier), why not simply

If IsArray(vIn1) Then vaArr1 = vIn1

Good catch - and IsArray() fails with an error for a multi-area range,
so we should do the 'Is it a single-area, multi-cell Range' checking
first.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Alan Beban]

. . . and IsArray() fails with an error for a multi-area range,
so we should do the 'Is it a single-area, multi-cell Range' checking
first.

Whoops! Not in Excel 2000; IsArray(rng) simply ignores the areas other
than the first, but doesn't throw an error; it returns True if rng is a
multi-area range.

Regards,
Alan Beban

 

[Stephen Bullen]

Hi Alan,

Whoops! Not in Excel 2000; IsArray(rng) simply ignores the areas other
than the first, but doesn't throw an error; it returns True if rng is a
multi-area range.

D'Oh! I tested it using:

Function IsItArray(vIn As Variant) As Boolean
IsItArray = IsArray(vIn)
End Function

and

=isitarray(C2:C8,E2:E8)

which gave #VALUE! and I assumed that came from the IsArray test, rather
than that I was trying to pass two parameters to a single-param UDF!

rather than

=isitarray((C2:C8,E2:E8))

which, as you say, returns True

So as .Value is the default property for a Range, and .Value returns the
array for the first area of a multi-area range, then I guess your
alternative of

If IsArray(vIn1) Then vaArr1 = vIn1

could indeed be used. I don't think I'd ever use that, though, as it
certainly isn't obvious from reading it what's going on. If I did use it,
I'd probably write five lines of comments or so to explain what it's
doing. And I'd probably treat a multi-area range as an error condition,
rather than only using the first area. But I realise we all have our own
preferences <g>.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Don Taylor]

D'Oh! I tested it using:

Function IsItArray(vIn As Variant) As Boolean
IsItArray = IsArray(vIn)
End Function


which gave #VALUE! and I assumed that came from the IsArray test, rather
than that I was trying to pass two parameters to a single-param UDF!

rather than

which, as you say, returns True

So as .Value is the default property for a Range, and .Value returns the
array for the first area of a multi-area range, then I guess your
alternative of

If IsArray(vIn1) Then vaArr1 = vIn1

could indeed be used. I don't think I'd ever use that, though, as it
certainly isn't obvious from reading it what's going on. If I did use it,
I'd probably write five lines of comments or so to explain what it's
doing. And I'd probably treat a multi-area range as an error condition,
rather than only using the first area. But I realise we all have our own
preferences <g>.

Many thanks for all the insights provided in response to my question.
I believe I'm getting close to seeing the light here.

A few things I've done, here is the example code provided again:

Function Cross(VIn1 As Variant, VIn2 As Variant) As Variant
Dim vaArr1 As Variant
Dim vaArr2 As Variant
Dim vaResult As Variant

'Convert parameters to arrays if not already arrays
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
If IsArray(VIn2) Then 'We got an array, so just use it
vaArr2 = VIn2
ElseIf TypeName(VIn2) = "Range" Then 'Read the range's values into an array
vaArr2 = VIn2.Value
End If

ReDim vaResult(1 To 1, 1 To 3)

'Calculate the result using the arrays
vaResult(1, 1) = vaArr1(2, 1) * vaArr2(3, 1) - vaArr1(3, 1) * vaArr2(2, 1)
vaResult(2, 1) = vaArr1(3, 1) * vaArr2(1, 1) - vaArr1(1, 1) * vaArr2(3, 1)
vaResult(3, 1) = vaArr1(1, 1) * vaArr2(2, 1) - vaArr1(2, 1) * vaArr2(1, 1)

Cross = vaResult 'Return the result
End Function

And I've named ranges of 3 contiguous cells with more meaningful names,
like Black, White, Y, B, x (the actual names representing the colors
being used in the project). Interestingly, all these names are fine
but trying to hame a set of cells R fails every time, even though I
have not been able to find anything in the Excel help system that would
seem to conflict with this name, so I'm using Red as an alternative.

Now things like
=Cross(Cross(Red,White),Black)
works!
But now I find
=Cross(Black-White,Red-White)
fails with #VALUE! errors, even though Black-White and Red-White
happily work as array formulas.

Is there yet another tweak I need to make these work too?

Then I'll add tweaks so that it recognizes that some folks use rows
to hold vector coefficients and some use columns. Then maybe we can
get someone to provoke Microsoft to add this to their web page on
the Cross function, and save the next poor guy trying to do this a
LOT of work and confusion.

Thanks for all your help

 

[Alan Beban]

Interestingly, all these names are fine
but trying to hame a set of cells R fails every time, even though I
have not been able to find anything in the Excel help system that would
seem to conflict with this name, so I'm using Red as an alternative.

Nor can you use C as a name; I think it has to do with R and C being
reserved in connection with R1C1 referencing.

Alan Beban