excel vba named range properties code example

Example 1: excel vba create named range

' Creates a named range
Sub CreateNamedRange(ByRef pWorkBook As Workbook, ByVal pWorkSheetName As String, _
    ByVal pRangeName As String, ByVal pAddress As String)
    Dim rRange As Range
    Set rRange = pWorkBook.Sheets(pWorkSheetName).Range(Chr(39) & pWorkSheetName _
        & Chr(39) & "!" & pAddress)
    If NamedRangeExist(pWorkBook, pRangeName) Then
        pWorkBook.Names(pRangeName).Delete
    End If
    pWorkBook.Names.Add Name:=pRangeName, RefersTo:=rRange
End Sub

' Checks if a range with same name exists
Function NamedRangeExist(ByRef pWorkBook As Workbook, pName As String) As Boolean
    Dim nName As Name
    For Each nName In pWorkBook.Names
        If nName.Name = pName Then
            NamedRangeExist = True
            Exit Function
        End If
    Next nName
End Function

Sub TestMe()
    CreateNamedRange ThisWorkbook, "Sheet1", "MyNewRange", "A1:D10"
    MsgBox Range("MyNewRange").Address
End Sub

Example 2: excel vba List of all named ranges

' List of all named ranges ("my_range" - > [Sheet1!$A$3:$B$5])
' Add some named range to test
Sub AllNamedRanges()
    Dim nRangeName As Name
    Dim rNamedRange As Range
    Dim sNameDetails As String
    Dim iIndiceWS As Long
    Dim sNamedList As String
    
    With ThisWorkbook
        For iIndiceWS = 1 To .Worksheets.Count
            If .Names.Count > 0 Then
                For Each nRangeName In .Names
                    Set rNamedRange = nRangeName.RefersToRange
                    If Not rNamedRange Is Nothing Then
                        If .Worksheets(iIndiceWS).Name _
                            = rNamedRange.Worksheet.Name Then
                            sNameDetails = nRangeName.Name _
                                & " [" & rNamedRange.Worksheet.Name _
                                & "!" & rNamedRange.Address & "]"
                            ' sNameDetails can be stored in a Collection too
                            sNamedList = sNamedList & sNameDetails & vbCrLf
                        End If
                    End If
                Next nRangeName
            End If
        Next iIndiceWS
    End With
    
    Debug.Print sTextTemp
End Sub