CODEWITHSUNDEEP
excelvba
James
James

Reputation: 1852

VBA storing a range in a cell

I have a problem in VBA where I store a cell address as a range contained in a global variable. Problem is, any error and it stops working as it loses the variable. I'm trying to catch as many errors as I can, but I'd prefer to store the range in a cell if possible.

Does anyone have any suggestions? Thanks James

Upvotes: 0

Views: 2130

Answers (2)

Charles Williams
Charles Williams

Reputation: 23540

I would store the range in a Defined Name: Assuming the name is called CellAddr and it already exists

Names("CellAddr").RefersTo = "=" & Worksheets("Sheet2").Range("A3:B8").Address(True, True, xlA1, True)

Then you can retrieve the range values or range address using the defined name. You can also hide the name if you want, and it will persist in a saved workbook

Upvotes: 4

T I
T I

Reputation: 9943

I think something like this is what you are looking for, the Address property of Range returns the string of the Range

Edit: have ammended to show the name of the Worksheet

Public oRng As Range

Sub storeRange()
    Dim oWst1, oWst2 As Worksheet
    Dim oCell As Range

    Set oWst1 = Application.Worksheets("Sheet1")
    Set oWst2 = Application.Worksheets("Sheet2")
    Set oCell = oWst1.Range("A1")
    Set oRng = oWst2.Range("B2")

    oCell.Value = oWst2.Name & "!" & oRng.Address

End Sub

Upvotes: 1

Related Questions