## 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

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.