excel-vba Named Range Arrays

Example

Example sheet

enter image description here


Code

Sub Example()
    Dim wks As Worksheet
    Set wks = ThisWorkbook.Worksheets("Sheet1")
    
    Dim units As Range
    Set units = ThisWorkbook.Names("Units").RefersToRange
    
    Worksheets("Sheet1").Range("Year_Max").Value = WorksheetFunction.Max(units)
    Worksheets("Sheet1").Range("Year_Min").Value = WorksheetFunction.Min(units)
End Sub

Result

enter image description here

Using Named Ranges in VBA