HOW TO: VBA with Named Ranges, Formulas & Worksheet Functions

Real simple. VBA to Define a named range, use that in a formula or worksheetfunction.

[boredonatuesday]

Sub Name_sum_cell()

Dim Temp_V As Variant: Dim wks As Worksheet: Dim rng As Range

Set wks = ThisWorkbook.Sheets(2)

'one way to name a range
wks.Names.Add Name:="Pants",

'yes, another way to name a range
Set rng = wks.Range("D3:D7")
With rng
.Name = "Pants"
End With


Set rng = Range("Pants")

'Using a worksheet application self contained in a variable
Temp_V =
'test variable
Debug.Print "Sum: " & Temp_V
' add results to workbook
wks.Cells(9, 4).Value = Temp_V: wks.Cells(9, 3).Value = "Sum"
' add formula to workbook. result is the same
wks.Cells(9, 5).FormulaR1C1 = "=SUM(Pants)"


more
Temp_V =
Debug.Print "Average: " & Temp_V
wks.Cells(10, 4).Value = Temp_V: wks.Cells(10, 3).Value = "Average"
wks.Cells(10, 5).FormulaR1C1 = "=Average(Pants)"


Temp_V =
Debug.Print "Max: " & Temp_V
wks.Cells(11, 4).Value = Temp_V: wks.Cells(11, 3).Value = "Max"
wks.Cells(11, 5).FormulaR1C1 = "=Max(Pants)"

End Sub