excel define range vba code example

Example 1: excel vba can a vba function return a range?

'VBA functions can return an Excel range, but since a range
'is an object, the 'Set' keyword is required:

Function Test() As Range
    Set Test = [A1:Z99]
End Function

Example 2: 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