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)
- RefersTo – Actual formula with reference or another defined name
You can also create a defined name using VBA code also as given below:
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
Manage Multiple Complex Formula using Defined Names
Similar to this, you can define any complex formula & associate it to a defined name. Then use only that defined name in any cell. This will simplify the maintenance of Formula.
In future if there are any changes to the formula, you doon;y have to search whole workbook on where this formula is used. You can just go to Menu -> Formula -> Names Manager. Choose the specific name, edit the formula in it & same it.
The whole workbook will be refreshed for the changed formula. Not many people are using this feature. But this is a very sophisticated way to manage any number of formula in a Workbook.