power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Using range names in Hybrid Excel Monte Carlo models


As illustrated in the previous example, passing a single variable to and from a worksheet is easily achieved.  To recap, the following worksheet contains the range name "MyRange1" for cell B3:

The following two functions are equivalent - in other words, the cell can be referenced by its range name OR using A1 notation:

We strongly recommend using named ranges wherever values are being passed to and from spreadsheets.  There are two primary benefits of this practice:

  • Often in the building of a model, rows and columns are inserted or deleted, or cell ranges are moved to new locations.  While Excel automatically updates references to that range within the workbook, VBA references to the range are not updated.  This can have unintended - and sometimes catastrophic - consequences.  For example, your VBA code may overwrite cells containing formulas or other important information if the A1 range reference is incorrect.

  • Also, using logical names for worksheet ranges makes it easy to read the VBA code.  "Iterations = Range("Iterations")" is much easier to make sense of than "Iterations = Range("$B$23")".  This readability also helps in debugging your code.

Now we'll look at some advanced uses of range names.


Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.