Named Ranges and Memory Usage

How memory-intensive is the use of named ranges? I have workbook that has to be reconfigured day-to-day based on it usage. I have macros to do the reorganizing for me, and they work great most of the time. The problem arises when something in my workflow environment changes and I find it necessary to move cells around manually to make room for a new piece of data in the default template.

When I move, insert, delete, cut/paste, etc. in the main workbook all of the references in other cells automatically update to reflect the change. My macros, however, remain static (of course), and I have to manually change every affected reference in the VBA code every time I change the layout. It just struck me (genius, I know ) that I can use named references in the VBA code to solve my problem implicity.

Before I do that I need to know, however: How are named ranges stored inside Excel? There are quite a few ranges that will need names, and if RAM will be being used every time a named range is changed or accessed I won't be able to do it. The workbook is used by my coworkers and I on machines that are already overtaxed by visually-intensive data processing (they display and record engineering information for our customers in real-time) and all extraneous memory usage must be kept to a minimum.

Thanks for your help,
~Rab

Hello Rab,

If your systems are running at or near the maximum capacity, you efforts in reducing resources is a waste of time. System resources are fixed. Expanding your RAM, increasing virtual memory, etc. help, but do not expand your system resources. You gain more benefit from better workbook design and coding. Formulae are very system intensive, mainly due to constant updating and recalculating of every formula on the worksheet.

You don't mention how many ranges you have, and there is no workbook attached for review to give you a meaningful estimate. Named ranges are part of Range Object. Just as every other Object has a Name property, so does a Range. The cell locations "A1", and "D5" are actually range names. Since you are using a method that is built into the object, the additional overhead is minimal in both storage, and execution.

Sincerely,
Leith Ross

Since you are using a method that is built into the object, the additional overhead is minimal in both storage, and execution.

That is precisely what I wanted to hear. Thank you so very much!