Reversing Order of Values in Range Variable

[vthokienj]

I have a function that receives values from a worksheet as a Range variable:

Public Function Interpolate(x As Double, Xi As Range, Yi As Range)

Lets say that Xi has two values, which I can access like:

x1 = Xi(1)
x2 = Xi(2)

I want to reverse the two values, and I don't seem to be able to write directly to the variable, so
this can't be done:

Xi(1) = x2
Xi(2) = x1

How can you reverse the order of these values? They must stay in a Range object.

Thanks

 

[JoeMo]

If Xi is a range object you need to initialize it before you can do anything. This swaps the value in A1 with that in A2.

Sub test()
Dim R As Range
Set R = Range("A1:A2")
x1 = R(1)
x2 = R(2)
R(1) = x2
R(2) = x1
End Sub

 

[vthokienj]

Thanks for the reply. I am not able to write a value to the range variable. So your R(1) = x2 line crashes for me, as does this code:

Private Function ReverseValuesInRange(rangeOriginal As Range, rangeLength As Integer) As Range

    Dim R As Range
    Set R = Range("A1:A5")
    x1 = rangeOriginal(1) ' gets the value correctly
    R(1) = x1 ' but crashes here

End Function

 

[JoeMo]

Thanks for the reply. I am not able to write a value to the range variable. So your R(1) = x2 line crashes for me

Are you using the exact code I posted? What do you mean by "not able"? What happens when that line executes?

 

[vthokienj]

Yes, I am using the exact code. If I breakpoint on that R(1) = x1 line, the next step jumps to the on error code of the Interpolate function. And x1 has a value in it so that is not the issue.

My end goal is to have an interpolate function where the x values can descend, as I see almost all the code out there has it where they must ascend. So I am looking to detect the direction of the
data and switch the order if needed.

 

[JoeMo]

Yes, I am using the exact code. If I breakpoint on that R(1) = x1 line, the next step jumps to the on error code of the Interpolate function. And x1 has a value in it so that is not the issue.

My end goal is to have an interpolate function where the x values can descend, as I see almost all the code out there has it where they must ascend. So I am looking to detect the direction of the
data and switch the order if needed.

What happens if you just run the code w/o any breakpoints?

 

[vthokienj]

The same thing happens.

Perhaps there is something about using worksheet functions that I do not understand. From the worksheet, I call my function like so:

=interpolate(B28,B$9:B$13,C$9:C$13)

The function crashes where indicated when just trying to write a value to the worksheet, whether it be on the current one or another.

Public Function interpolate(x As Double, Xi As Range, Yi As Range)

    Application.enableEvents = False
    Application.screenUpdating = False
    Application.Calculation = False
    Sheets("Sheets2").Cells(1, 1) = "1" ' crashes here

     ' would also crash below
     ' myvalue = Xi(2)
     ' Xi(1) = myvalue

End Function

It seems like this should be something that executes unless there are different rules when functions are called from a worksheet.

 

[JoeMo]

The question I asked was with respect to the code I posted, not your function. If you copy my code exactly and run it w/o adding breakpoints, does it swap the contents of A1 and A2?

 

[vthokienj]

Yes, your code crashes as well

Sub test()
Dim R As Range
Set R = Range("A1:A2")
x1 = R(1)
x2 = R(2)
R(1) = x2 ' crashes here
R(2) = x1
End Sub

 

[JoeMo]

Yes, your code crashes as well

Sub test()
Dim R As Range
Set R = Range("A1:A2")
x1 = R(1)
x2 = R(2)
R(1) = x2 ' crashes here
R(2) = x1
End Sub

Maybe your workbook is corrupted. Close Excel, then restart Excel and open a new workbook, put the code above in it and try again after you place some values in A1:A2 of the active sheet.