I have a worksheet function that takes two range variables as parameters, each with 5 cells. I want to switch the values such that Xi(1) = Xi(5)...Xi(5) = Xi(1) and do the same for the Yi values. I can not seem to set the variable correctly and it crashes as indicated below. Below is the image of the Xi variable in the watch window which is why I think the Xi.Value2.Value2(1).Value2(1, 1) should work.
The function is called from the worksheet like:
=ChangeValues(D$9:D$13,E$9:E$13)
Thanks for any help.
-- removed inline image ---
Watch : - : Xi : : Range/Range : calculations.ValuePL
- : Value2 : : Variant/Variant(1 to 5, 1 to 1) : calculations.ValuePL
- : Value2(1) : : Variant(1 to 1) : calculations.ValuePL
: Value2(1,1) : 5 : Variant/Double : calculations.ValuePL
- : Value2(2) : : Variant(1 to 1) : calculations.ValuePL
: Value2(2,1) : 4 : Variant/Double : calculations.ValuePL
- : Value2(3) : : Variant(1 to 1) : calculations.ValuePL
: Value2(3,1) : 3 : Variant/Double : calculations.ValuePL
- : Value2(4) : : Variant(1 to 1) : calculations.ValuePL
: Value2(4,1) : 2 : Variant/Double : calculations.ValuePL
- : Value2(5) : : Variant(1 to 1) : calculations.ValuePL
: Value2(5,1) : 1 : Variant/Double : calculations.ValuePL
The function is called from the worksheet like:
=ChangeValues(D$9:D$13,E$9:E$13)
Thanks for any help.
Code:
function ChangeValues(Xi as Range, Yi as Range)
' capture the original values - this part gets the correct values
Dim x1 As Double: x1 = Xi(1)
Dim x2 As Double: x2 = Xi(2)
Dim x3 As Double: x3 = Xi(3)
Dim x4 As Double: x4 = Xi(4)
Dim x5 As Double: x5 = Xi(5)
Dim y1 As Double: y1 = Yi(1)
Dim y2 As Double: y2 = Yi(2)
Dim y3 As Double: y3 = Yi(3)
Dim y4 As Double: y4 = Yi(4)
Dim y5 As Double: y5 = Yi(5)
' crashes when I try any of these four lines
Xi(1) = x5
Xi.Value2(1) = x5
Xi.Value2.Value2(1) = x5
Xi.Value2.Value2(1).Value2(1, 1) = x5
End Function
-- removed inline image ---
Watch : - : Xi : : Range/Range : calculations.ValuePL
- : Value2 : : Variant/Variant(1 to 5, 1 to 1) : calculations.ValuePL
- : Value2(1) : : Variant(1 to 1) : calculations.ValuePL
: Value2(1,1) : 5 : Variant/Double : calculations.ValuePL
- : Value2(2) : : Variant(1 to 1) : calculations.ValuePL
: Value2(2,1) : 4 : Variant/Double : calculations.ValuePL
- : Value2(3) : : Variant(1 to 1) : calculations.ValuePL
: Value2(3,1) : 3 : Variant/Double : calculations.ValuePL
- : Value2(4) : : Variant(1 to 1) : calculations.ValuePL
: Value2(4,1) : 2 : Variant/Double : calculations.ValuePL
- : Value2(5) : : Variant(1 to 1) : calculations.ValuePL
: Value2(5,1) : 1 : Variant/Double : calculations.ValuePL
Last edited: