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 FormulaVBA Create Defined name in ExcelVisual Basic123456789101112131415161718Sub Add_Defined_Name()Dim dName As Name, iRow As Double'Add a Defined Name'Scope: For Specific SheetActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="chking", RefersTo _:="=Sheet1!$A$2"'Add a Defined Name'Scope: For whole WorkbookThisWorkbook.Names.Add Name:="sumDE", RefersTo:= _"=Sheet1!$D$1+Sheet1!$E$1"'Use a Defined nameThisWorkbook.Sheets(1).Range("D1") = 4ThisWorkbook.Sheets(1).Range("E1") = 2ThisWorkbook.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.Use Defined Name with in VBA CodeVisual Basic1234567Sub Use_Defined_Name_In_VBA()'Note:Use Defined name within quotes. Else this will not workMsgBox Evaluate("sumDE")'Sum the values in Cells D1 & E1End 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.
Excel VBA – Create Defined Name Formula & How to Use
- Block Apps in FaceBook print screen Excel Countif Record Macro Timer in Excel Task Manager VCF HTML Table Import Data Thumbnails Text to Speech TTS Candy Crush C++ Programing Machine learning AI bulk email Excel VBA vba color index vba color codes Android social media Twitter Google+ Google Adsense Tips bitcoin Calendar in Excel outlook vba mass email Data Mining Excel Macro Facebook WhatsApp Wordpress python