Decompose Excel External Address into Workbook Name, Worksheet Name, Cell Address

[Boswell]

I have a need to use the External Address references as unique identifiers for cells in a worksheet. Using Range.Address(External:=True) returns a string of the form [Book1]Sheet1!$A$1...

I need a reliable way to extract workbook name "Book1", worksheet name "Sheet1", and cell reference "$A$1" from the above reference. The problem is easy in the form stated above; however because excel allows exclamation points, apostrophes, and spaces in workbook names and sheet names things get complicated.

Anybody have any prebaked code that can decompose an external address reference into the workbook name, worksheet name, and address?

Essentially I want the same result as using Range.Parent.Parent.Name (for workbook), Range.Parent.Name (for worksheet) and Range.Address(True,True) (for address) using a string manipulation on Range.Address(External:=True)

 

[mikerickson]

Dim FullAddressString as Strng
FullAddressString = "[Book1]Sheet1!$A$1"

MsgBox "is in Book " & Range(FullAddressString).Parent.Parent.Name
MsgBox "is in sheet " & Range(FullAddressString).Parent.Name
MsgBox "is in range" & Range(FullAddressString).Address

 

[Boswell]

Thanks for you response. I'm currently using .Parent.Parent.Name, .Parent.Name, and .Address; however these calls are a speed bottle neck in my routine so I am trying to replace them. Cursory implementation has proven that one call to .Address(External:=True) and string manipulation in place of the three calls is much quicker. Below is my first crack at the problem ...

Option Explicit
 
Sub decomposeAddress()
    '==========================================================================
    'Characters Not Allowed in WB Name
    '   < > ? [ ] : | *
 
        'Characters Not Allowed in WS Name
    '   \ / ? * [ ]
    '==========================================================================
 
    'Start with External Address
    '-------------------------------------------------------------------------
    Dim externalAddress As String
    externalAddress = Selection.Address(External:=True)
 
 
    'Get Range's Workbook Name
    '--------------------------------------------------------------------------
 
    '1. Find last and first Bracket Position and set wbName to characters in between
    Dim openBracketPos As Integer
    Dim closeBracketPos As Integer
 
    openBracketPos = InStr(externalAddress, "[")
    closeBracketPos = InStr(externalAddress, "]")
 
    Dim proposedWBName As String
    proposedWBName = Mid(externalAddress, openBracketPos + 1, closeBracketPos - (openBracketPos + 1))
 
    '2. Return Message with actual name, proposed name, and whether or not they are equal
    Dim actualWBName As String
    actualWBName = Selection.Parent.Parent.Name
 
    MsgBox ("Actual: " & actualWBName & vbNewLine & _
            "Proposed: " & proposedWBName & vbNewLine & _
            (StrComp(proposedWBName, actualWBName, vbBinaryCompare) = 0))
 
 
    'Get Range's WorkSheet Name
    '--------------------------------------------------------------------------
 
    '1. Find last exclam position and first close bracket position (already know close bracket from above)
    Dim lastExclamPos As Integer
    lastExclamPos = InStrRev(externalAddress, "!")
 
    Dim proposedWSName As String
    proposedWSName = Mid(externalAddress, closeBracketPos + 1, lastExclamPos - 1 - closeBracketPos)
 
    '2. Return Message with actual name, proposed name, and whether or not they are equal
    Dim actualWSName As String
    actualWSName = Selection.Parent.Name
 
    MsgBox ("Actual: " & actualWSName & vbNewLine & _
        "Proposed: " & proposedWSName & vbNewLine & _
        (StrComp(proposedWSName, actualWSName, vbBinaryCompare) = 0))
 
 
    'Get Range's Address
    '--------------------------------------------------------------------------
 
    '1. Use last exclam position to determine range address
    Dim proposedAddress As String
    proposedAddress = Mid(externalAddress, lastExclamPos + 1, Len(externalAddress) - lastExclamPos)
 
    '2. Return Message with actual name, proposed name, and whether or not they are equal
    Dim actualAddress As String
    actualAddress = Selection.Address(True, True)
 
    MsgBox ("Actual: " & actualAddress & vbNewLine & _
        "Proposed: " & proposedAddress & vbNewLine & _
        (StrComp(proposedAddress, actualAddress, vbBinaryCompare) = 0))
 
 
End Sub

The above routine will fail in the case that a space or apostrophe is in the WS name (and probably some other cases I have not considered). I am continually refining...

 

[Boswell]

Below is a condensed version of the previously posted code that takes into account spaces and apostrophes in worksheet names.. I am almost certain there is something i am not considering though

Option Explicit
 
Sub decomposeAddress()
    '==========================================================================
    'Characters Not Allowed in WB Name
    '   < > ? [ ] : | *
 
        'Characters Not Allowed in WS Name
    '   \ / ? * [ ]
    '==========================================================================
 
    'Start with External Address
    '-------------------------------------------------------------------------
    Dim externalAddress As String
    externalAddress = Selection.Address(External:=True)
 
 
    'Get Range's Workbook Name
    '--------------------------------------------------------------------------
    Dim openBracketPos As Integer
    Dim closeBracketPos As Integer
    openBracketPos = InStr(externalAddress, "[")
    closeBracketPos = InStr(externalAddress, "]")
 
    Dim proposedWBName As String
    proposedWBName = Mid(externalAddress, openBracketPos + 1, closeBracketPos - (openBracketPos + 1))
 
 
    'Get Range's WorkSheet Name
    '--------------------------------------------------------------------------
    Dim lastExclamPos As Integer
    lastExclamPos = InStrRev(externalAddress, "!")
 
    Dim proposedWSName As String
    proposedWSName = Mid(externalAddress, closeBracketPos + 1, lastExclamPos - 1 - closeBracketPos)
 
    'Deal with apostrophes and spaces
    If InStr(proposedWSName, "'") Then
        proposedWSName = Left(proposedWSName, Len(proposedWSName) - 1)
        proposedWSName = Replace(proposedWSName, "''", "'")
    ElseIf InStr(proposedWSName, " ") Then
        proposedWSName = Left(proposedWSName, Len(proposedWSName) - 1)
    End If
 
    'Get Range's Address
    '--------------------------------------------------------------------------
    Dim proposedAddress As String
    proposedAddress = Mid(externalAddress, lastExclamPos + 1, Len(externalAddress) - lastExclamPos)
 
    'Report whether or not things worked out
    '--------------------------------------------------------------------------
    Dim actualWBName As String
    Dim actualWSName As String
    Dim actualAddress As String
    actualWBName = Selection.Parent.Parent.Name
    actualWSName = Selection.Parent.Name
    actualAddress = Selection.Address(True, True)
 
    If (StrComp(proposedWBName, actualWBName, vbBinaryCompare) = 0) And _
        (StrComp(proposedWSName, actualWSName, vbBinaryCompare) = 0) And _
        (StrComp(proposedAddress, actualAddress, vbBinaryCompare) = 0) Then
        MsgBox ("Success!")
    Else
        MsgBox "Fail" & vbNewLine & _
                "Workbook: " & (StrComp(proposedWBName, actualWBName, vbBinaryCompare) = 0) & _
                "Worksheet: " & (StrComp(proposedWSName, actualWSName, vbBinaryCompare) = 0) & _
                "Address: " & (StrComp(proposedAddress, actualAddress, vbBinaryCompare) = 0)
    End If
 
End Sub

 

[mikerickson]

If you want to parse it with string functions, this keys on ] ! and $

Dim fullName As String
fullName = Range("A2:V5").Address(, , , True)

MsgBox "Book: " & Mid(fullName, 2, InStr(1, fullName, "]") - 2)
MsgBox "Sheet: " & Mid(fullName, InStr(1, fullName, "]") + 1, InStr(1, fullName, "!") - InStr(1, fullName, "]") - 1)
MsgBox "Cell(s): " & Mid(fullName, InStr(1, fullName, "$"))