Slowdown from Named Ranges in xlsm format?

[dendres]

Hello,

I have a client who as an Excel project that spans 10 workbooks. The reason for this is that it was created in the early days of Excel and it was deemed better to split the project up so file sizes would be more manageable. (sizes are 165KB to 3.5MB) These 10 workbooks are linked to each other and all 10 are always open at the same time when working on their Excel project.

Fast forward to this year and my task was to convert these 10 files to *.xlsm format for Excel 2007 or later. The conversion took a few dozen hours, but was successful. All of these workbooks use Named Ranges. The client will often import large CSV files via VBA and it is easiest for the VBA code to directly find where each value goes by assigning it to a Named Range.

One of the 10 files had 31,000+ Named Ranges in it. As far as I can tell, they all refer to individual cells or ranges of cells. There are no dynamic formulas like OFFSET or anything fancy going on that are telltale causes of slowdown. But when keying data in that workbook, the program will pause about 15 seconds before letting me go on to the next cell. I did notice that about 8,000+ Named Ranges resulted in a #REF ! error, so I removed them. Everything still worked fine overall, but the slowdown persists. It even occurs when I turn Calculation to Manual.

I realize still having 23,000+ Named Ranges is a lot. The slowdown does not occur when using these 10 files together in Excel 2003 (*.xls). Also, I have recreated the workbook by copy and pasting each worksheet into the worksheet of a new workbook, moved over the VBA code and user forms, as well as used VBA code to bring in all of the 23,000+ Named Ranges.

I'm certain the Named Ranges are the culprit of my slowdown, since they were the last thing I did to duplicate this workbook when recreating it as described in the last paragraph. Before the last step of adding in the Named Ranges, I tested the data entry and it went extremely fast, as it should.

Does anyone have suggestions or information on what I can do to remove the slowdown? I don't see a way out of using the named ranges due to the need to import CSV files. Will consolidating the 10 workbooks help in anyway?

Thanks for your time in reading all of this and your help too.

 

[Fluff]

If the named ranges are not dynamic, why not scrap them & just hard code the ranges into the VBA code?

 

[dendres]

If the named ranges are not dynamic, why not scrap them & just hard code the ranges into the VBA code?

I guess that's an option. But I'm going to be asked to extend the features of the workbooks down the road and I can't predict if my client will want to move around any of the cells as they currently are. If that happens, well, then my VBA code will obviously need a lot of rework. So I'm hoping there's at least one more option out there that allows me to retain the named ranges and remove the slowdown.

Thank you though.

 

[Fluff]

I can't think of any way of doing it, but maybe one of the other members can come up with an alternative solution.

 

[dendres]

I can't think of any way of doing it, but maybe one of the other members can come up with an alternative solution.

I appreciate your time and help.

I should have also noted that the Named Ranges are used as part of formulas across the workbooks as well. Perhaps that matters. In my troubleshooting, I've slowly introduced a few thousand Named Ranges back in at a time, and the slowdown increases accordingly.

 

[dendres]

I have also tried saving the files as XLSB instead of XLSM, but this did not help either.