File very slow to open with named ranges

[Excel4444]

is anyone familiar with why a file that may contain 30,000+ named ranges may take a long time to open? additionally any known reservations for having so many named ranged.

thanks in advance!

[Richard Buttrey]

Wow!

Never in 42 years (well 30 with Excel) have I ever encountered a spreadsheet/workbook with so many named ranges.

Can you explain what the ranges are for and why so many. Are the names being processes by a VBA loop since evaluating the name would take time each pass through the loop.

[Excel4444]

it's a very complex workbook and the goal is leverage many datasets and thus many individual data points and that number could even double to 60k.

can you clarify this question:
Are the names being processes by a VBA loop since evaluating the name would take time each pass through the loop.

[Richard Buttrey]

it's a very complex workbook and the goal is leverage many datasets and thus many individual data points and that number could even double to 60k.

can you clarify this question:
Are the names being processes by a VBA loop since evaluating the name would take time each pass through the loop.

I was seeking to know whether your workbook uses macros?

You've not explained what the range names are, how/where they are used and how complex they are if functions are involved. But instead of us all guessing why not upload the workbook?

[janmorris]

im confused how it instantly get 60,000 named ranges... who or what creates them?

maybe this would explain why it runs slow.

[Excel4444]

all great questions and definitely don't want to leave you guys guessing, but unfortunately I cannot upload the workbook and I do concede that makes troubleshooting more challenging, however here is what I can share:

1. it's a .xlsm file
2. there are macros that create the named ranges
3. the named ranges are not currently being using in functions, but formulas
4. there's roughly 30k now, but that can get higher

I'm curious if excel is going through some quasi indexing or loading of named ranged when opening a workbook that contain 1 or 1,000 (or 30k) named ranges. Additionally, are there any adverse affects that that many named ranges can have on a file? once the file opens it seems to run fine.

[Excel4444]

Hey ya'll just wanted to pop back and see if there's any thoughts. thanks!

[torachan]

I do not think you are going to get an answer if you are not prepared to upload your workbook.
You are the person with the nightmare of a problem, do you not think it would be beneficial to your sanity to help us to help you.

[Excel4444]

indeed it would benefit me and my sanity
I will work on uploading a sample workbook.

[Excel4444]

after I created a sample file with said 30k named ranges I found the file to opened rather quick, however the named ranges where isolated to one sheet and the workbook had nothing else in it. Where as the workbook I referring to has a lot more going on, of which I cannot share. In conclusion, my attempt to re-create the issue with an abbreviated workbook failed, so my issue, may be caused the those 30k named ranges being used in 100's of formulas and spread throughout the entire workbook.

should that conclude this thread? thoughts?

best in advance

[torachan]

concluding a thread without a resolution must be disappointing.
however all that we can conclude - you are obviously using a very, very inefficient workbook produced most likely by a novice 'glueing' processes together.
we shall never know.

[Excel4444]

I would respectively disagree. It's a very efficient workbook, the time it takes to open I don't think is a proxy for efficiency, but more so it's complexity or excel being excel, which is always a wild card. Especially when a 30k named workbook is spun up, and a it yields a different result.
the workbook is not constructed of glued together processes as if it where then the workbook itself (once open) would not work well and that's not the case. we're only discussing the opening of or start up of the workbook. There are no volatile functions or formulas, many historic forum artifacts (this and others) have been considered when deploying some of the features of said workbook and no rogue or old VLOOKUP using full columns, etc. and any google search for how to create an efficient fast workbook have been considered and subsequently "brought up to code".

Everything is very targeted. but something is obstructing the workbook for opening quickly. agree, concluding a thread without a resolution is disappointing.

[josephteh]

Try saving the file in binary format.. maybe it can help.

[Excel4444]

thanks for the suggestion, but after two attempts is didn't open faster while also causing a recovery issue...

[Richard Buttrey]

all great questions and definitely don't want to leave you guys guessing, but unfortunately I cannot upload the workbook and I do concede that makes troubleshooting more challenging, however here is what I can share:

1. it's a .xlsm file
2. there are macros that create the named ranges
3. the named ranges are not currently being using in functions, but formulas
4. there's roughly 30k now, but that can get higher

I'm curious if excel is going through some quasi indexing or loading of named ranged when opening a workbook that contain 1 or 1,000 (or 30k) named ranges. Additionally, are there any adverse affects that that many named ranges can have on a file? once the file opens it seems to run fine.

Prior to uploading the workbook why not upload the macro so that we have some sort of idea what it's doing

If a macro is generating all those names, does that mean the ranges are always different each time the wb is opened?

It's more normal to predefine range names, and make them dynamic range names if the range varies so it's not immediately obvious why you would want to generate all those names at run time.

Formulae are merely one or more Excel functions combined so I don't understand your reference to functions/formulas

[Excel4444]

I was referencing functions/formulas as just a means of highlighting that many measures have been taken to implement best practices for workbook performance. The macro doesn't re-name them every time upon open, only when new data points are introduced to the file. So the named ranges do often stay the same. the macro would just be equivalent to "Create from Selection" from the Formulas tab.

is there a tool or material/artifacts that outline what excel does on start up? For example, index, catalog "something", create a calc dependency tree, similar to someone asking "what takes you so long to get to the front door"? and you're like well first I fold laundry, eat breakfast, etc. then I can get a sense of what I need to adjust to get to the door faster (or open the file quicker).

[Excel4444]

Just wanted to check in.

I am familiar with the Inspect Workbook feature in excel, of which I've tried. There also an Inquire add-on that I seem to not have access to (https://docs.microsoft.com/en-us/off...excel-workbook), but I was hoping there was a tool that could assist in diagnosing workbook performance with specific respect to the start up or opening of the workbook.

[Excel4444]

I found a during the rebuild on my file that it wasn't until I got to one (of two) of the last migrated sheets that the file finally hit an "open wall". I've read quite a bit about the Excel Dependency Tree (https://docs.microsoft.com/en-us/off...-recalculation) (one of many artifacts), but nonetheless informative. Additionally reading somewhere that once a certain numbers of calculations, decencies, etc. it hit that the Tree is forcibly rebuild/calc'd. I already have Manual Calculations on this workbook, BUT could the workbook upon opening be re-rebuilding, building or calculating something regardless of the manual setting?

If so, is there a way to turn that off via VBA or something else?

thanks in advance