Update Macro to auto capture cell range and table name

One way to use LET is to make long formulas easier to read. Positive for both the builder in the future and for others using the model. Sep 2021 though, only available in 365.

Using =Table1 is like using =Table1[#Data] and when still easy to read, I prefer short versions.

Juliano-Petrukio's approach with finding special characters holds more possibilities 🙂 and is among others a way to get the name of linked files.

Two cells linking to different files
='[Open Workbook.xlsx]Sheet1'!$A$1

='C:\Users\bosinander\[Closed Workbook.xlsx]Sheet1'!$A$1

=LET(

string;FORMULATEXT(A1);
first;FIND("'";string);
last;FIND("]";string);
output;MID(string;first+1;last-first-1);
SUBSTITUTE(output;"[";"")
)

For version not having the LET function, it is the same as

=SUBSTITUTE(MID(FORMULATEXT(A1);FIND("'";FORMULATEXT(A1))+1;FIND("]";FORMULATEXT(A1))-FIND("'";FORMULATEXT(A1))-1);"[";"")

Output;

Open Workbook.xlsx
C:\Users\bosinander\Closed Workbook.xlsx

bosinander 

In case you perfer an UDF and get all possible desired outputs.

Function GetTableInfo(AnyCell As Range, InfoType As Integer) As Variant
    Dim TableOutput As String

    TableOutput = vbNullString    
    On Error Resume Next
    
    Select Case InfoType    
    Case 1 ' Table Name
        TableOutput = AnyCell.ListObject.Name
    Case 2 ' Table Range
        TableOutput = AnyCell.ListObject.DataBodyRange.Address
    Case 3 ' Number of Rows
        TableOutput = AnyCell.ListObject.DataBodyRange.Rows.Count
    Case 4 ' Number of Columns
        TableOutput = AnyCell.ListObject.DataBodyRange.Columns.Count
    Case Else
        TableOutput = "Undefined"
    End Select    
    GetTableInfo = TableOutput
End Function