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

1. Name for definition
2. Scope (workbook or specific worksheet)
4. 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

'Scope: For Specific Sheet
:="=Sheet1!\$A\$2"

'Scope: For whole Workbook
"=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.