Please make xl2bb check for infinite ranges

[JenniferMurphy]

I am trying to use xl2bb as much as I can. It clearly makes submitting and understanding questions much easier for everyone. But it has also caused me a lot of extra work and frustration. This is because it hangs if there are any "infinite" named ranges ($3:$3 or $B:$B) defined anywhere in the workbook. When this happens, I then have to abort Excel, which causes it to reopen with different copies of every open workbook. I then have to check if the autosaved copy is really different from the original and keep whichever one is the most current. If I forget to save before running xl2bb, I can lose some work.

Part of this is my fault. Before I learned about tables, I used a lot of these infinite ranges to avoid having to define a fixed range that gave me problems if it expanded or a complicated range by naming the 2 "border" cells just outside the range and then a third one using offset to get the cells inside the two border cells. Now that I am using tables, I rarely need to do that, but I have a lot of existing workbooks with these infinite ranges. If I want to post a question using xl2bb, I have to either check that there are no infinite ranges defined anywhere in that workbook, something that is tedious and error-prone, or move the sheet to a new workbook.

Apparently, based on previous threads, fixing xl2bb to not hang is non-trivial and not on the To Do list. Ok, but then I would like to request that xl2bb be updated to check for any infinite ranges and issue a warning message. If the message can identify the sheet and the range, that would be great, but I would be happy with just the warning.

Thank you ?

 

[smozgur]

@JenniferMurphy,

Is it possible to upload a sample workbook causing this issue somewhere that I can download? This would be very helpful if you could as it will be better to check a real case instead of trying to reproduce it.

Thanks.

 

[JenniferMurphy]

@JenniferMurphy,

Is it possible to upload a sample workbook causing this issue somewhere that I can download? This would be very helpful if you could as it will be better to check a real case instead of trying to reproduce it.

Thanks.

Sure. This folder has 2 workbooks.

1. In one workbook, xl2bb hangs trying to make a minisheet of the table in sheet1. Sheet2 has 2 infinite ranges.
2. In the other workbook, the 2 infinite ranges are deleted (causing errors in that table), but now xl2bb is able to make a minisheet of the table in sheet1

OneDrive link to example workbooks

Thanks for looking into this.

 

[smozgur]

Thanks for uploading the sample file.
I'll post here about the finding and if a resolution is possible.

 

[smozgur]

@JenniferMurphy,

The problem is not actually the name with the infinite range. In fact, XL2BB is able to handle that named range.

The actual problem is the merged cells that is intersecting with the named range - Values and Ascending headers in your workbook. Even Excel itself gets confused with that merged cells intersecting with the named range. You can test it by simply selecting RankAscAvg (or RankAscEq) in the name dropdown and clicking on the Trace Dependents button on the ribbon in the Formulas->Formula Auditing section. You will notice that even Excel can't select the named range cells (that is supposed to be the column H) but select columns E:I instead and also can't find the dependent cells that end up with not drawing any tracing arrow. That's because of the merged cells. The following shows what is selected when the RankAscAvg named range is selected - also take a look in the name dropdown to see how Excel switches the range due to the existence of the merged area instead of selecting the column H.

Although it is a structural error because a named range is not supposed to be the entire column or row in the first place, combining it with a merged cell in the same range makes things much more complicated, and it cannot be completely solved in XL2BB side as it doesn't have a solution in Excel side in the first place other than a silence error handler as we can see in the sample. However, if you unmerge the merged cells or as the better solution set the named range to not include the merged cell (perhaps using a dynamic range or directly set it as H6:H15) then both Excel and XL2BB will be happy working with that named range and your workbook will be properly fixed as well.

Thanks again for taking my attention to this rare situation, so even I can't make XL2BB nicely work with a named range like this, I am hoping to exclude this kind of problematic named range during the XL2BB mini-sheet creation to avoid unresponsive application - kind of a similar solution that Excel doing. Not sure, but if possible, I will also try to make XL2BB warn the user about this issue considering it might generate an inconsistent mini-sheet after excluding the named range.

I can't say an exact time about the updated release date but hopefully, this situation will be included in it.

Hope this answers your question.

 

[JenniferMurphy]

@JenniferMurphy,

The problem is not actually the name with the infinite range. In fact, XL2BB is able to handle that named range.

The actual problem is the merged cells that is intersecting with the named range - Values and Ascending headers in your workbook. Even Excel itself gets confused with that merged cells intersecting with the named range. You can test it by simply selecting RankAscAvg (or RankAscEq) in the name dropdown and clicking on the Trace Dependents button on the ribbon in the Formulas->Formula Auditing section. You will notice that even Excel can't select the named range cells (that is supposed to be the column H) but select columns E:I instead and also can't find the dependent cells that end up with not drawing any tracing arrow. That's because of the merged cells. The following shows what is selected when the RankAscAvg named range is selected - also take a look in the name dropdown to see how Excel switches the range due to the existence of the merged area instead of selecting the column H.

View attachment 55808

Interesting.

Although it is a structural error because a named range is not supposed to be the entire column or row in the first place,

Says who? Where does M$FT say that I cannot define a range to be an entire column? And if this is true, why does it allow me to do so?

combining it with a merged cell in the same range makes things much more complicated, and it cannot be completely solved in XL2BB side as it doesn't have a solution in Excel side in the first place other than a silence error handler as we can see in the sample.

Not sure what you mean by silent error handler. And where do we see it in what sample?

However, if you unmerge the merged cells or as the better solution set the named range to not include the merged cell (perhaps using a dynamic range or directly set it as H6:H15) then both Excel and XL2BB will be happy working with that named range and your workbook will be properly fixed as well.

Yes, any of those workarounds will avoid the problem. But I object to your characterizing my workbook as "fixed". It was not broken. It is Excel that is broken.

Thanks again for taking my attention to this rare situation,

I not sure how rare it is. I would guess that a small percentage of Excel users frequent this board (or any board). A smaller proportion still submit problems using xl2bb. When I was working in software development, we estimated that less than 1% of users who encounter a problem (bug) take the time to report it. So if we did get a bug report, we estimated that the number of users who had experienced that problem was 2 orders of magnitude larger.

so even I can't make XL2BB nicely work with a named range like this,

As you have so clearly pointed out, the problem is not the named range. It is the intersection of the named range and the merged cells.

BTW: I tried setting up an intersection of an infinite column and an infinite row. That did not cause any problems.

I am hoping to exclude this kind of problematic named range during the XL2BB mini-sheet creation to avoid unresponsive application - kind of a similar solution that Excel doing. Not sure, but if possible, I will also try to make XL2BB warn the user about this issue considering it might generate an inconsistent mini-sheet after excluding the named range.

Great

I can't say an exact time about the updated release date but hopefully, this situation will be included in it.

OK

Hope this answers your question.

Yes, it does thanks.

I have simplified the example workbook in the OneDrive folder. I believe it shows what is happening much more clearly and simply now.

 

[JenniferMurphy]

Here's one more detail. xl2bb does not include infinite ranges in its list of named ranges. This sheet has these named ranges:

...but the minisheet only lists Test:

Replace.xlsx
	B	C	D
3	DP→	0	2
4	Units→	oz	oz
5	3oz	3.0000 oz	3.0000 oz
6	3 oZ	3.0000 oz	3.0000 oz
7	3.5oz	3.5000 oz	3.5000 oz
8	3.00 oz	3.0000 oz	3.0000 oz
9	1234.56OZ	1,234.5600 oz	1,234.5600 oz
10	100ml	3.0000 oz	3.3800 oz
11	100 mL	3.0000 oz	3.3800 oz
12	1000mL	34.0000 oz	33.8100 oz
13	1,000ml	34.0000 oz	33.8100 oz
14	123.456ML	4.0000 oz	4.1700 oz
FmtOzMl

Cell Formulas
Range	Formula
C5:D14	C5	=FmtOzMl(@Input,,@DP)

Named Ranges
Name	Refers To	Cells
Test	=FmtOzMl!$B$5:$B$14	C5:D5

 

[JenniferMurphy]

How are you coming with a new version that will check for these infinite loop problems?

I just ran into it again. I tried to get a minisheet and all of Excel hung. I had 5-6 workbooks open at the time. All of them. All of them were hung. The last time this happened, it took me half an hour to recover the workbooks. This time, I let it run for over 2 hours in the hope that it might free things up. No such luck.

I don't think I had any infinite ranged or merged cells, but I can't verify that. It was in a temporary workbook and it was lost in the recovery process.

I hope a new version is out soon. If checking for all possible hang situations is too difficult, at least add a timer that will offer me a way out if it takes more than a couple of minutes.