Mass Naming & Hyperlink Tools for Excel VBA

Overview

Excel VBA Named Ranges

Named ranges improve formula readability, but creating them one by one is slow. These three macros streamline named range workflows: Mass_Naming creates numbered named ranges in bulk, Cell_Reference_to_Name converts cell references in formulas to their named range equivalents, and Add_Hyperlinks turns formula references into clickable hyperlinks.

How They Work

Mass Naming: Select a range, enter a prefix and starting index, and each cell gets a named range like Prefix001, Prefix002, etc. Perfect for setting up input cells in financial models
Cell Reference to Name: Scans formula cells in the selection and replaces raw cell references (e.g., =Sheet1!B5) with their named range equivalents (e.g., =Revenue), making formulas self-documenting
Add Hyperlinks: Converts formula cell references into clickable hyperlinks while preserving the original cell styling and font color, adding a custom screentip for documentation

The VBA Code

Sub Mass_Naming()
    Dim PrefixInput As String
    Dim StartIndex As Long
    Dim cell As Range, i As Integer
    
    PrefixInput = InputBox("Please Enter the Name Prefix:")
    StartIndex = InputBox("Please Enter the Starting Index:")
    
    On Error GoTo Skip
    If StartIndex < 0 Then GoTo Skip
    
    i = StartIndex
    For Each cell In Selection
        cell.Name = PrefixInput & Format(i, "000")
        i = i + 1
    Next cell
    Exit Sub
Skip:
    MsgBox "Invalid Inputs!!!", vbCritical
End Sub

Sub Cell_Reference_to_Name()
    Dim Cel As Range, Sct_Rng As Range, Rng As Range
    
    If Selection.Count = 1 Then
        Set Sct_Rng = Selection
    Else
        Set Sct_Rng = Selection.SpecialCells(xlCellTypeFormulas)
    End If
    
    For Each Cel In Sct_Rng
        Dim Str As String: Str = Mid(Cel.Formula, 2, Len(Cel.Formula))
        Set Rng = Range(Str)
        On Error Resume Next
        If Rng.Name Is Nothing Then
            Cel.ClearContents
        Else
            Cel.Formula = "=" & Rng.Name.Name
        End If
        On Error GoTo 0
    Next Cel
End Sub

Sub Add_Hyperlinks()
    Dim Cel As Range, Rng As Range
    Dim Scr_Tip As String
    Scr_Tip = InputBox("Please enter the Screentip for all hyperlinks")
    
    If Selection.Count = 1 Then
        Set Rng = Selection
    Else
        Set Rng = Selection.SpecialCells(xlCellTypeFormulas)
    End If

    For Each Cel In Rng
        ' Add hyperlink while preserving cell style
        Dim Cur_Sty As String: Cur_Sty = Cel.Style
        Dim Cur_Clr As Long: Cur_Clr = Cel.Font.Color
        ActiveSheet.Hyperlinks.Add Cel, "", Cel.Formula, Scr_Tip
        Cel.Style = Cur_Sty
        Cel.Font.Color = Cur_Clr
    Next Cel
End Sub

Final Thoughts

Named ranges are a cornerstone of well-structured Excel models. These tools make it practical to adopt named ranges even in large workbooks where manual creation would be prohibitively time-consuming.