The Name Manager in Excel is a powerful tool for creating references to certain cells and ranges. Using the Name Manager to create different macros in VBA, i.e., to break circular references, is best practice.
Where can I find the Name Manager?
To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.
After opening the Name Manager within a new file, you will likely not see any named ranges. Below is an example of what it will look like after defining multiple named ranges within a financial model for renewable energy investments.
Instead of referring directly to a cell or range, the use of named ranges can be a substitute for directly referring to a cell or range. This practice is recommended for certain technical inputs, such as constant numbers, i.e., hours per day, hours in a year to calculate full load hours, or large numbers like a million.
Using named ranges when creating macros is essential!
When creating macros, it is crucial to only use named ranges! It is surprising how many professional financial modelers still refer to certain cells and ranges directly within the VBA interface. One slight change to the structure of the financial model will likely crash the VBA code or result in incorrect outcomes.
Deleting or inserting a row above a named range will cause a reference within VBA to be incorrect after the structural change of the model has been done. Unlike an automatic reference update within formulas that refer to certain cells or ranges, VBA cannot change such references automatically. The only way to ensure references do not change is by using named ranges. Breaking circular references caused by debt sculpting according to a target DSCR is a situation where the Name Manager comes in handy.
How do I name certain cells and ranges?
Naming cells and ranges is a rather simple exercise once you know how it's done!
- Select a cell or range you would like to name.
- In the Name box, type in a name for the cell or reference. You can find the name box to the left of the Formula Bar.
- Press Enter. Your cell or range has been given a name that will now appear in the Name Manager.
How to refer to a named cell or range within the Excel spreadsheet?
First, we will name a cell with a constant number of hours per year. This is an important number for renewable energy investments and is used to calculate the full load hours of an energy plant.
In the above example, the constant number 8,760 hours per year in cell E7 has been named Hours_year.
After naming the cell, you can start typing a formula with an equal sign (=) and start writing Hours_year - the reference will show up and can be selected by pressing Enter.
How to build an advanced financial model for renewable energy investments?
Do you want to learn how to use the Name Manager properly and implement handy VBA macros within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.
Article sources
Use the Name Manager in Excel - Excel for Microsoft 365
Define and use names in formulas - Excel for Microsoft 365
