Q:

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

A:

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 

A:

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

