area in memory for calc

[alrodopial]

It must be very simple but ...

How do I store a range with values in memory so I can make calculations in memory and not writing/reading from from excel?
eg. MyAREA = RANGE("A1:C5")

so later I can do eg myVAR = MyAREA.Cels(1,1).Value + MyAREA.Cells(1,1).Value

[alrodopial]

If I

Code: Select all

set MyAREA = RANGE("A1:C5")

and then
MyAREA.Cels(1,1) = 0

it also changes the value of the first cell in the sheet .
I want the changes to be made only inside the code

[NorthView]

I'm not sure this is possible, alrodopial. I guess MS would expect people to use VBA to hold values in memory instead.

[osknows]

Hi alrodopial,

You can use an array instead of a range object

Code: Select all

Option Explicit

Sub test()
Dim MyAREA() As Variant
Dim myVAR As Long

    With Sheet1

    MyAREA = .Range("A1:C5").Value
   
    End With
   
    myVAR = MyAREA(1, 1) + MyAREA(1, 1)

End Sub

[alrodopial]

Thanks os, it works fine,
Thanks north