![]() In future if there are any changes to the formula, you doon y have to search whole workbook on where this formula is used. This will simplify the maintenance of Formula. Then use only that defined name in any cell. Similar to this, you can define any complex formula & associate it to a defined name. Else this will not workĮnd Sub Manage Multiple Complex Formula using Defined Names here is a sample code on how to use the defined name created in previous example. Pass the Defined name as parameter for this function & Excel will calculate the formula to give the final result. This can be achieved by using the Application.Evaluate or simply Evaluate function. Excel will sum the values in D & E, giving the result. This will sum the values in cells D1 & E1.Īfter executing this macro, you can enter =SumDE in any cell & press Enter key. In this Code, we are creating 2 defined names. ThisWorkbook.Sheets(1).Range("F1") = "=sumDE" 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()ĪctiveWorkbook.Worksheets("Sheet1").Names.Add Name:="chking", RefersTo _ RefersTo – Actual formula with reference or another defined name.There are four parameters to define a Name These names can be defined in Excel from Menu -> Formula -> Name Manager -> New ![]() ![]() Creating Defined Name with Formula in Excelĭefined names or Custom Formula are used to simply the long formula into a short & meaningful name.
0 Comments
Leave a Reply. |