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.
