Get the address parts from a selected range

Tested in Excel 2016 (16.0.9330.2073) 64-bit

You can use an input box to allow selecting a range and then get the workbook, sheet name and range.

To select a range, create a button on a sheet or form and add the following sub:

 
 
  1. Option Explicit
  3. Sub SelectRange()
  4.     Dim a As String
  5.    
  6.     a = Application.InputBox("Please select...", , , , , , , 0)
  7.    
  8.     Debug.Print GetAddressPart(a, "w")
  9.     Debug.Print GetAddressPart(a, "s")
  10.     Debug.Print GetAddressPart(a, "r")
  11.    
  12. End Sub

Where GetAddressPart function is:

 
 
  1. Public Function GetAddressPart(AddressString As String, Optional AddressPart As String = "R", Optional ConvertToStyle = xlA1) As String
  2.     ' AddressPart could be W = Workbook; S = Sheet; R = Range
  3.    ' TypeConvert could be xlA1 or xlR1C1
  4.    Dim myWorkbook As String, mySheet As String, myRange As String
  5.     Dim FromStyle, myRef
  6.    
  7.      ' find the workbook
  8.    If InStr(1, AddressString, "]") > 0 Then
  9.         myWorkbook = Mid(AddressString, InStr(1, AddressString, "[") + 1, InStr(1, AddressString, "]") - InStr(1, AddressString, "[") - 1)
  10.     Else
  11.         myWorkbook = ActiveWorkbook.Name
  12.     End If
  13.    
  14.     ' find the sheet
  15.    If InStr(1, AddressString, "]") > 0 Then
  16.         mySheet = Mid(AddressString, InStr(1, AddressString, "]") + 1, InStr(1, AddressString, "!") - InStr(1, AddressString, "]") - 1)
  17.         mySheet = Replace(mySheet, "'", "")
  18.     Else
  19.         If InStr(1, AddressString, "!") > 0 Then
  20.             mySheet = Mid(AddressString, 2, InStr(1, AddressString, "!") - 2)
  21.             mySheet = Replace(mySheet, "'", "")
  22.         Else
  23.             mySheet = ActiveSheet.Name
  24.         End If
  25.     End If
  26.    
  27.     ' find the range
  28.    If ConvertToStyle = xlA1 Then
  29.         FromStyle = xlR1C1
  30.     Else
  31.         FromStyle = xlA1
  32.     End If
  33.        
  34.     myRef = Application.ConvertFormula(Mid(AddressString, 2, Len(AddressString)), FromStyle, ConvertToStyle)
  35.     myRange = Mid(myRef, InStr(1, myRef, "$"), Len(myRef))
  36.    
  37.     ' return result
  38.    Select Case UCase(AddressPart)
  39.         Case "W"
  40.             GetAddressPart = myWorkbook
  41.         Case "S"
  42.             GetAddressPart = mySheet
  43.         Case "R"
  44.             GetAddressPart = myRange
  45.     End Select
  46.    
  47. End Function

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.