' 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

' Scope.Names.Add Name:=RangeName, RefersTo:=NamedRange
'Example:
ThisWorkbook.Names.Add Name:="myRangeName", RefersTo:=Range("A1")