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))
	DOTPRODUCTab = a(1,1)*b(1,1) + a(1,2)*b(1,2) + a(1,3)*b(1,3)
	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)
		AngleBetweenVectors3D = ArcCos((aX * bX + aY * bY + aZ * bZ) / denominator) * (180/Pi)
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.


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