Search code examples
stringexcelvbarangeexternal

convert external cell address into range in vba

I have a string in cell A1, which is the cell address of an external cell (i.e. another workbook), 

'[data transfer utility.xlsb]Sheet1'!$B$5

In vba I would like to create a range object, myRange, that would correspond to the address stored in cell A1.

I've tried various solutions, but none have worked.

Solution

  • VBA:

    Function Reference(strAddress As String) As Range

    Dim intPos As Integer, intPos2 As Integer
    Dim strWB As String, strWS As String, strCell As String

    intPos = InStr(strAddress, "]")
    strWB = Mid(strAddress, 2, intPos - 2)

    intPos2 = InStr(strAddress, "!")
    strWS = Mid(strAddress, intPos + 1, intPos2 - intPos - 2)

    strCell = Mid(strAddress, intPos2 + 1)

    Reference = Workbooks(strWB).Worksheets(strWS).Range(strCell)

End Function