CODEWITHSUNDEEP
excelvba
AhlawSahla
AhlawSahla

Reputation: 17

VBA function using multiple range inputs and multiple outputs

I am trying to write a function in excel VBA that calculates the coefficients of the equation of a plane using 9 inputs (coordinates of 3 points) from a range in a worksheet. Here is what I have so far but that doesn't seem to work:

Function Plane_Eq_3Pts(ByVal X1 As Range, ByVal Y1 As Range, ByVal Z1 As Range, ByVal X2 As Range, ByVal Y2 As Range, ByVal Z2 As Range, ByVal X3 As Range, ByVal Y3 As Range, ByVal Z3 As Range) As Variant()

Dim Eq(0 To 3) As Variant

Eq(0) = (Y2 - Y1) * (Z3 - Z1) - (Y3 - Y1) * (Z2 - Z1)
Eq(1) = (Z2 - Z1) * (X3 - X1) - (Z3 - Z1) * (X2 - X1)
Eq(2) = (X2 - X1) * (Y3 - Y1) - (X3 - X1) * (Y2 - Y1)
Eq(3) = -(Eq(0) * X1 + Eq(1) * Y1 + Eq(2) * Z1)

Plane_Eq_3Pts = Eq

End Function

I have tried using Control+Shift+Enter when using the function above and selecting 4 cells however, I keep getting a #VALUE on all 4 of the selected cells. Any help is greatly appreciated!

Upvotes: 0

Views: 578

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Your function (exactly as coded) is using a one dimensional array . This is appropriate for a row output and in Excel 365 it will spill into a dynamic row:

enter image description here

If you want it to spill into a column in Excel 365 then TRANSPOSE() could be used:

=TRANSPOSE(Plane_Eq_3Pts(A1,A2,A3,A4,A5,A6,A7,A8,A9))

enter image description here

If you are not using Excel 365 then the function must be array-entered.

EDIT#1:

Lets take the row case. First hi-light cells D3 through G3. Second click in the Formula Bar. Third type the formula with array-entry.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

Upvotes: 1

Related Questions