XL2BB hangs Excel

[Peter_SSs]

Here's a very simple workbook.

Thanks. I can reproduce the problem.
A quick test seems to me the problem is the whole column named range 'RankAscEq' as removing that removed the hang for me.
Still, I'll leave it to @smozgur to confirm or find something else and decide if a fix can be applied.

 

[JenniferMurphy]

Thanks. I can reproduce the problem.
A quick test seems to me the problem is the whole column named range 'RankAscEq' as removing that removed the hang for me.
Still, I'll leave it to @smozgur to confirm or find something else and decide if a fix can be applied.

I just added version 15 of that book to that OneDrive folder. I changed that range to be more specific (less than the entire column). That one doesn't hang. It looks like named ranges for an entire column ($D$D) may be the problem.

xl2bb hangs Excel

 

[smozgur]

Thanks. I can reproduce the problem.
A quick test seems to me the problem is the whole column named range 'RankAscEq' as removing that removed the hang for me.
Still, I'll leave it to @smozgur to confirm or find something else and decide if a fix can be applied.

Good finding, @Peter_SSs. In this particular workbook, XL2BB is trying to analyze 3M+ cells due to the named column range, which is not something usual but in the "it happens" category. I don't know what's the possibility of regenerating this in another workbook, but I took a note to reject the range that in such cases for the future XL2BB update.

 

[JenniferMurphy]

Good finding, @Peter_SSs. In this particular workbook, XL2BB is trying to analyze 3M+ cells due to the named column range, which is not something usual but in the "it happens" category. I don't know what's the possibility of regenerating this in another workbook, but I took a note to reject the range that in such cases for the future XL2BB update.

Hmmm... Naming an entire column is "not something usual"? Really? I've seen it a few times on tutorial sites, maybe even here. It looks like a bug in xl2bb to me.

And your solution is to "reject" the range? It's valid Excel syntax. What will you do, just say "no go" and exit? Or will you issue some opaque m$ft-style error message like #VALUE?

 

[smozgur]

It is not called a bug, because it will eventually work after analyzing all three million cells unless a memory leak occurs and the application goes unstable, but instead an unhandled case for such a rare situation that is kind of overusing named range feature in this sample.

Although this is not related to the question asked in this thread and this thread has been already finalized, I personally prefer using dynamic named ranges by using the OFFSET/COUNTA functions.

 

[smozgur]

And your solution is to "reject" the range? It's valid Excel syntax. What will you do, just say "no go" and exit? Or will you issue some opaque m$ft-style error message like #VALUE?

I realized that I didn't answer this question. I will most likely make XL2BB ignore that named range containing more than a certain cell count and inform the user about it, and generate the mini-sheet by ignoring that named range.

Thanks for reporting this issue, so we'll be able to handle it in the application.

 

[JenniferMurphy]

I just did a couple more tests. As it turns out, large named ranges are not always a problem. Version 16 of that book, saved in the same OneDrive folder, has that same $D:$D named range, but xl2bb is able to create the mini-sheet without a problem.

So rejecting all large ranges will eliminate some cases that would work.

 

[Peter_SSs]

Naming an entire column is "not something usual"?

It is unusual when you are generating a mini-sheet (limit of 3,000 cells), which is what the Add-In is designed for. If it was common, there would be lots of questions/comments about it in this forum.
The Add-In is not meant to be the equivalent of a full-blown version of Excel.

XL2BB already limits the number of cells that can be processed, as noted above, and even then it can produce code that is too big to post in the forum, & provides appropriate messages if either too many cells are selected or code is too big. This may just need to handled in a similar way with an appropriate message as suggested by @smozgur.

If it is critical for your question to have a named range for over 1 million cells then you may just have to provide a link to a shared copy of an actual file, like many members here already do.

 

[JenniferMurphy]

It is unusual when you are generating a mini-sheet (limit of 3,000 cells), which is what the Add-In is designed for.

Then I suggest that something to that effect ought to be included in the documentation. Perhaps the ZIP file should also include a "Read This" file, as many applications do..

If it was common, there would be lots of questions/comments about it in this forum.

It is my experience that 9 out of 10, perhaps 99 out of 100, people never bother to report problems. They just stop using that tool.

XL2BB already limits the number of cells that can be processed, as noted above, and even then it can produce code that is too big to post in the forum, & provides appropriate messages if either too many cells are selected or code is too big. This may just need to handled in a similar way with an appropriate message as suggested by @smozgur.

That would be my suggestion.

If it is critical for your question to have a named range for over 1 million cells then you may just have to provide a link to a shared copy of an actual file, like many members here already do.

I have done that many times. When I do, I get comments like, "No one is going to download a workbook from the Internet" or "If you would use xl2bb, we could all see what you are doing". So I was trying to be helpful.

I have spent well over 2 hours trying to help isolate the problem. Apparently, that is minimally appreciated. I think I have detected another factor in the problem, but it sounds like posting it would just be even more annoying.

Sorry to have bothered you.

 

[JenniferMurphy]

I just tried to use xl2bb again. The sheet with the table I wanted to capture and post, did not have any long ranges, but other sheets in that workbook did. Yet, when I clicked on Mini Sheet, Excel hung. Unfortunately, I had several workbooks open and all had to be restarted, which meant I had to figure out which ones I had saved before the hang.

This is a royal pain in the *ss. As a short-term or interim measure until you can figure out how to prevent the hangs, can you at least post a warning message if there is a chance that it might hang so I can take precautionary measures?

Thanks