Excel VBA – Create Defined Name Formula & How to Use

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)
3. Comments
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

    '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.