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