Creating Defined Name with Formula in Excel
Defined names or Custom Formula are used to simply the long formula into a short & meaningful name. These names can be defined in Excel from Menu -> Formula -> Name Manager -> New There are four parameters to define a Name- Name for definition
- Scope (workbook or specific worksheet)
- Comments
- RefersTo - Actual formula with reference or another defined name
VBA Code to Create Defined name or Custom Formula
Sub Add_Defined_Name()
Dim dName As Name, iRow As Double
'Add a Defined Name
'Scope: For Specific Sheet
ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="chking", RefersTo _
:="=Sheet1!$A$2"
'Add a Defined Name
'Scope: For whole Workbook
ThisWorkbook.Names.Add Name:="sumDE", RefersTo:= _
"=Sheet1!$D$1+Sheet1!$E$1"
'Use a Defined name
ThisWorkbook.Sheets(1).Range("D1") = 4
ThisWorkbook.Sheets(1).Range("E1") = 2
ThisWorkbook.Sheets(1).Range("F1") = "=sumDE"
End Sub
In this Code, we are creating 2 defined names. One is SUMDE. This will sum the values in cells D1 & E1.
After executing this macro, you can enter =SumDE in any cell & press Enter key. Excel will sum the values in D & E, giving the result.
How To Use Defined Name in Excel VBA
This can be achieved by using the Application.Evaluate or simply Evaluate function. Pass the Defined name as parameter for this function & Excel will calculate the formula to give the final result. here is a sample code on how to use the defined name created in previous example.Sub Use_Defined_Name_In_VBA()
'Note:Use Defined name within quotes. Else this will not work
MsgBox Evaluate("sumDE")
'Sum the values in Cells D1 & E1
End Sub