 # Array function help in Calc macros

The following macro works when I pass it two 1x3 cell ranges:

``````Function test(a,b)
dim c
c = CrossProduct(a,CrossProduct(b,a))
test = AngleBetweenVectors(a,c)
End Function
``````

But when I change it so that I can pass it a single 1x6 cell range (see below), something goes wrong. I get an error that the AngleBetweenVectors function is trying to divide by zero, suggesting that the arrays a or c are all zeros.

``````Function test(d)
dim a
dim b
a = Array(d(1,1),d(1,2),d(1,3))
b = Array(d(1,4),d(1,5),d(1,6))
dim c
c = CrossProduct(a,CrossProduct(b,a))
test = AngleBetweenVectors(a,c)
End Function
``````

If it helps, here are the two functions called. I know these work when called alone, and they work when called in the first version of my test function above.

``````Function AngleBetweenVectors(a as Variant,b as Variant)
dim MAGa
MAGa = SQR(a(1,1)*a(1,1) + a(1,2)*a(1,2) + a(1,3)*a(1,3))
dim MAGb
MAGb = SQR(b(1,1)*b(1,1) + b(1,2)*b(1,2) + b(1,3)*b(1,3))
dim DOTPRODUCTab
DOTPRODUCTab = a(1,1)*b(1,1) + a(1,2)*b(1,2) + a(1,3)*b(1,3)
dim COSPHI
COSPHI = DOTPRODUCTab / (MAGa * MAGb)
dim PHI
PHI = ArcCos(COSPHI) * (180/3.1415926535)
AngleBetweenVectors = PHI
End Function

Function CrossProduct(a,b) as Variant
dim CPx
CPx = (a(1,2) * b(1,3)) - (a(1,3) * b(1,2))
dim CPy
CPy = (a(1,3) * b(1,1)) - (a(1,1) * b(1,3))
dim CPz
CPz = (a(1,1) * b(1,2)) - (a(1,2) * b(1,1))
CrossProduct = Array(CPx,CPy,CPz)
End Function
``````

Can anyone help me with what’s going wrong? Thanks!

Why don’t you use the spreadsheet at hand?

I’m not sure what you’re suggesting or asking, Villeroy. I’ve just boiled a much more complicated macro down to a minimal working example that reproduces my error. To fix my real problem, part of a whole bunch of code, I need to understand why this isn’t working.

Get and use XRay tool if you don’t already: Bernard Marcelly Web site. Villeroy helped make MRI, which is over-all a little more powerful of a tool, but XRay is just the thing for finicky array stuff in BASIC.

What is your defining function for d? Are you sure you have it as a 1x6 not 6x1?

And…for others looking at this for future reference, remember that a 1x6 range is a 2-dimensional array, not just an array of six elements.

Tell me, @aphilosopherTO , does your module have an Option Base line? The fact is that when converting two parts of array D into parameters for CrossProduct using Array (), you can get arrays starting from zero. Therefore, addressing b(1,3) can lead to errors.(this is a one-dimensional array, access to its elements uses one index)

I have Option Base 1 selected, yes. Calls like b(1,3) aren’t leading to errors elsewhere in the macro.

There is no defining function for d. To get the error, I would just call test(A1:A6) in the Calc sheet itself. If instead I call test(A1:A3:A4:A6) in the working version of the macro, it works. The macros I provided should be functioning as a MWE, if you include Option Base 1 and the following macro for acos:

Public Function ArcCos (x As Variant) As Variant

``````Select Case x
Case -1
ArcCos = 4 * Atn(1)

Case 0:
ArcCos = 2 * Atn(1)

Case 1:
ArcCos = 0

Case Else:
ArcCos = Atn(-x / Sqr(-x * x + 1)) + 2 * Atn(1)
End Select
``````

End Function

I’ve never used XRay before (I’ve just started with macros in Calc). Since all there is to see in this code is what I’ve shown above, I was hoping there was some obvious syntax error that I’m missing.

Honestly, I don’t understand how you managed to achieve this with the code you provided in the question:

Thanks for trying the code, John. I understand what you’re saying. I don’t know why it works, but it does. (I’m just learning Basic and how Open/Libre office handles it.) I’m running these on LibreOffice, not OpenOffice, if that matters.

How would you write this code, if you expected it to work? Is there a way to specify in the CrossProduct function that it’s receiving 1x3 arrays?

I might share a little of my preferred style for working with arrays in user functions. I’d start with a testing Sub before even going to a sheet entry. BASIC is old and sort of treats you right when you tip your hat and say sir. Explicit indexing is just that.

``````Sub MyTestPlatform()
Dim Source(1 To 1, 1 To 6)
Dim Result
Dim Index As Integer

For Index = LBound(Source, 2) To UBound(Source, 2) '2 = 2nd dimension
Source(1, Index) = 10*(Index+1) + 1 'Simulating source parameters
Next Index
Result = MyUserFunction(Source)
MsgBox Result

End Sub 'MyTestPlatform

Function MyUserFunction(Source As Variant)
'Requires data in 1-by-6 2-d array (in a row range)
'	To use with a column, use TRANSPOSE as inner function
Dim SubFunctionArraySize As Integer
Dim SubFunctionArray As Variant
Dim Dummy As Integer
Dim Index As Integer
Dim Stepper As Integer
MyUserFunction = 1 'It's going to be multiplicative
SubFunctionArraySize = 3

'Test for dimensions
Dummy = -1
On Error Resume Next
Dummy = LBound(Source, 2)
On Error Goto 0
If Dummy = -1 Then
MyUserFunction = CVErr(502) 'Source is not a 2-d array
Exit Function
End If
If (UBound(Source, 2) - LBound(Source, 2) + 1) Mod SubFunctionArraySize <> 0 Then
MyUserFunction = CVErr(502) 'Source cannot be n subfunction's worth of elements
Exit Function
End If

'Dimension the array to use as parameter
ReDim SubFunctionArray(1 To SubFunctionArraySize)

'Do something
Stepper = 0
For Index = LBound(Source, 2) To UBound(Source, 2)
Stepper = (Stepper Mod SubFunctionArraySize) + 1 '1,2,3,1,2,3,...
SubFunctionArray(Stepper) = Source(1, Index)
If Stepper = SubFunctionArraySize Then
MyUserFunction = MyUserFunction * MySubFunction(SubFunctionArray) 'Do something with values
End If
Next Index

End Function 'MyUserFunction

Private Function MySubFunction(Source As Variant)
Dim Index As Integer

For Index = LBound(Source) To UBound(Source)
MySubFunction = MySubFunction + Source(Index) 'Do something with sub-values
Next Index

End Function 'MySubFunction``````
1 Like

I will definitely talk about this later, with some code samples. Now I want your explanation - what should your `Test()` functions return and what do they return? As far as I can see, the functions calculate the angle between vector A and vector product A*B. By definition, this is always a right angle, that is, you will always get 90 degrees, and this can be returned from the `Test()` function in one line, without additional calculations. What exactly are you calculating?

1 Like

How would I write this function? First of all, I would try to forget about arrays and make the algorithm forget about them too. Indeed, what does a function need to calculate the angle between vectors? Only six numbers are the coordinates of the ends of two vectors. So let’s pass them to the function as six parameters!

`````` Function AngleBetweenVectors3D(aX#, aY#, aZ#, bX#, bY#, bZ#) As Variant
``````

At first glance, this is cumbersome, almost clumsy. But as a result, inside the function, we do not need to access the elements by indices!

``````Function AngleBetweenVectors3D(aX#, aY#, aZ#, bX#, bY#, bZ#) As Variant
Dim denominator As Double
denominator = SQR(aX*aX + aY*aY + aZ*aZ)*SQR(bX*bX + bY*bY + bZ*bZ)
If denominator = 0 Then
AngleBetweenVectors3D = "Wrong params: #DIV/0!"  ' or CVErr(532)
Else
AngleBetweenVectors3D = ArcCos((aX * bX + aY * bY + aZ * bZ) / denominator) * (180/Pi)
EndIf
End Function
``````

Write in the cell `=ANGLEBETWEENVECTORS3D(A1;A2;A3;A4;A5;A6)` and get the result.

Yes, it is difficult to specify six parameters, one range would be much more convenient. This is not a problem, create a wrapper function that will know how you passed the data and how the `AngleBetweenVectors3D()` function perceives it:

``````Function AngleBetweenVectorsV(a As Variant) As Variant
AngleBetweenVectorsV = AngleBetweenVectors3D(a(1,1),a(2,1),a(3,1),a(4,1),a(5,1),a(6,1))
End Function
``````

and call it like as `=ANGLEBETWEENVECTORSV(A1:A6)`

If the coordinates of the vector are located not in a column, but in a row, then the wrapper function will call the calculation a little differently:

``````Function AngleBetweenVectorsH(a As Variant) As Variant
AngleBetweenVectorsH = AngleBetweenVectors3D(a(1,1),a(1,2),a(1,3),a(1,4),a(1,5),a(1,6))
End Function
``````

and call will be like`=ANGLEBETWEENVECTORSH(A1:F1)`

That is, the main idea is to divide the data representation (array) and the computational part (with coordinates) into two independent pieces.

Developing the idea, let’s implement the `CrossProduct` function.

This function should return the coordinates of the result vector. It is tempting to return this as an array of three elements. However, what should this array be? Do you need a result as row? In this case it is enough to concatenate the three numbers using the `ARRAY()` function, as you did in your code. What if you need a column? The code gets very complicated.

Let’s replace the function with a procedure - six input parameters (the same as for `AngleBetweenVectors3D`) and three more output parameters. The computational part becomes simple and intuitive:

``````Sub CrossProduct3D(aX#, aY#, aZ#, bX#, bY#, bZ#, ByRef cX#, ByRef cY#, ByRef cZ#)
cX = aY * bZ - aZ * bY
cY = aZ * bX - aX * bZ
cZ = aX * bY - aY * bX
End Sub
``````

The wrapper functions won’t be very complicated either:

``````Function CrossProductH(a As Variant) As Variant
Dim c(1 To 1, 1 To 3) As Double
CrossProduct3D(a(1,1),a(1,2),a(1,3),a(1,4),a(1,5),a(1,6), c(1,1), c(1,2), c(1,3))
CrossProductH = c()
End Function

Function CrossProductV(a As Variant) As Variant
Dim c(1 To 3, 1 To 1) As Double
CrossProduct3D(a(1,1),a(2,1),a(3,1),a(4,1),a(5,1),a(6,1), c(1,1), c(2,1), c(3,1))
CrossProductV = c()
End Function
``````

Call it like as `{=CROSSPRODUCTH(A1:F1)}` and `{=CROSSPRODUCTV(A1:A6)}` (I hope you understand that these are array functions, so you should finish entering the formula with Ctrl+Shift+Enter)

I understand that I did not answer the question from the title of this topic, I took the conversation in the other direction. What to do, the example you gave is not very suitable for explaining arrays.In his comment, @joshua4 (Thank you!) provided code snippets that demonstrate some techniques for working with arrays. It is a pity, but without from a specific task, this is not very clear. If you come up with another example, in which you cannot do without arrays, then ask a question with this sample and we will try to figure out what and how to do.

3 Likes

Thanks @JohnSUN and @joshua4. That was all very helpful. (And, John, you were right about the math itself being problematic.)