excel vba create change named range code example
Example 1: excel vba create 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
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: vba set named range
ThisWorkbook.Names.Add Name:="myRangeName", RefersTo:=Range("A1")