XL2BB v2.0 bug: Named ranges not listed in Capture Range

[Sulprobil]

Hello,

Named ranges were missing in my XL2BB capture range, Mini Sheet -
XL2BB v2.0 bug: Named ranges not listed in Capture Range, Mini Sheet of German Excel Version of Microsoft Office LTSC Professional Plus 2021, Version 2108 (Build 14332.20204).

See thread Confusion Of Join Method, please.
And: just retested here:

Test TEXTJOIN

Mini Sheet...

www.mrexcel.com

I am happy to send you the file, too.

Regards,
Bernd

 

[smozgur]

Hello Bernd,

Thanks for reporting the issue.

Is it only that specific Excel file or named ranges never listed in your mini-sheets?
Is it possible that you don't have the Named Ranges option checked?

That would be helpful to identify the problem if you could upload the sample file to the cloud. If you can't then please let me know, so I can start a conversation with you.
(There is no way to test on the German version for me but it shouldn't be a language-specific problem anyway.)

Note: Edited the thread title to keep it shorter since the issue is already explained in the post in detail.

 

[Sulprobil]

Hello,

I did tick that box, and I tested this several times.
The file:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Regards,
Bernd

 

[smozgur]

Thank you.

I tested the workbook in Excel 365 and XL2BB works as it is supposed to be.

TextJoin_Test.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1|1	1|2	1|3	1|4	1|5	1|6	1|7	1|8	1|9	1|10
2	2|1	2|2	2|3	2|4	2|5	2|6	2|7	2|8	2|9	2|10
3	3|1	3|2	3|3	3|4	3|5	3|6	3|7	3|8	3|9	3|10
4	4|1	4|2	4|3	4|4	4|5	4|6	4|7	4|8	4|9	4|10
5	5|1	5|2	5|3	5|4	5|5	5|6	5|7	5|8	5|9	5|10
6	6|1	6|2	6|3	6|4	6|5	6|6	6|7	6|8	6|9	6|10
7	7|1	7|2	7|3	7|4	7|5	7|6	7|7	7|8	7|9	7|10
8	8|1	8|2	8|3	8|4	8|5	8|6	8|7	8|8	8|9	8|10
9	9|1	9|2	9|3	9|4	9|5	9|6	9|7	9|8	9|9	9|10
10	10|1	10|2	10|3	10|4	10|5	10|6	10|7	10|8	10|9	10|10
11
12
13
14	TEXTJOIN2019	1|1,1|2,2|1,2|2,3|3,4|4,5|5,5|6,6|5,6|6,7|7,7|8,8|7,8|8,9|9
15	TEXTJOIN	#VALUE!
Sheet1

Cell Formulas
Range	Formula
A1:J10	A1	=ROW() & "|" & COLUMN()
B14	B14	=TEXTJOIN2019(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)
B15	B15	=TEXTJOIN(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)

Named Ranges
Name	Refers To	Cells
ThreeByThreeByThree	=Sheet1!$A$1:$C$3,Sheet1!$D$4:$F$6,Sheet1!$G$7:$I$9	B14:B15
TwoByTwoByFive	=Sheet1!$A$1:$B$2,Sheet1!$C$3:$D$4,Sheet1!$E$5:$F$6,Sheet1!$G$7:$H$8,Sheet1!$I$9:$J$10	B14:B15

It is unlikely, but maybe the option box is stuck for some reason and the associated Office registry value is always False. Could you please try the following?

• Uncheck the Named Ranges option
• Quit Excel
• Launch Excel and verify that the option is still unchecked (let me know if it is not)
• Check the option and retry the mini-sheet to see if it will work

* If you'd like to use the Immediate Window in VBE, then you can use the following one-liner to get the current value of the setting to avoid open/close Excel:

print GetSetting("xl2bb", "options", "extra_nr")

Thanks.

 

[smozgur]

It took some time but I think I identified the problem. It looks to be a bug in VBA when you have the semicolon as the list separator (language-specific).

I changed my regional format to use the semicolon instead of comma as the list separator, then I have the same problem.

It can be reproduced by using the following line in debug window when your workbook is open.

print Activeworkbook.Names("ThreeByThreeByThree").RefersToRange.Address

This should return the name's range address. However, if you have a regional format that has the semicolon as the list separator, and the named range contains multiple areas, then the RefersToRange property returns an error although it is still a perfectly valid range.

If you change the regional format in Windows settings to another format that uses comma as the list separator and reopen the Excel then it will work without problems.
(I am not suggesting changing your settings to use XL2BB but just to explain the problem).

I don't know if this is a known bug but obviously, there is something wrong with a property of the name object in different regional format settings. I might consider reporting this to Microsoft. I don't think I will hear about it for a long time if it is a real bug but at least I can hear if it is not a bug and something that I might be missing.

XL2BB depends on the RefersToRange property to generate the named ranges table and it won't be able to access the named ranges where this function fails due to this possible bug.

Thanks again for reporting the issue and also your help.

 

[Sulprobil]

Hello again,
When I switched my Windows 11 region format to English (Great Britain) it worked fine:

TextJoin_Test.xlsm
	A	B	C	D	E	F	G	H	I	J
1	1|1	1|2	1|3	1|4	1|5	1|6	1|7	1|8	1|9	1|10
2	2|1	2|2	2|3	2|4	2|5	2|6	2|7	2|8	2|9	2|10
3	3|1	3|2	3|3	3|4	3|5	3|6	3|7	3|8	3|9	3|10
4	4|1	4|2	4|3	4|4	4|5	4|6	4|7	4|8	4|9	4|10
5	5|1	5|2	5|3	5|4	5|5	5|6	5|7	5|8	5|9	5|10
6	6|1	6|2	6|3	6|4	6|5	6|6	6|7	6|8	6|9	6|10
7	7|1	7|2	7|3	7|4	7|5	7|6	7|7	7|8	7|9	7|10
8	8|1	8|2	8|3	8|4	8|5	8|6	8|7	8|8	8|9	8|10
9	9|1	9|2	9|3	9|4	9|5	9|6	9|7	9|8	9|9	9|10
10	10|1	10|2	10|3	10|4	10|5	10|6	10|7	10|8	10|9	10|10
11
12
13
14	TEXTJOIN2019	1|1,1|2,2|1,2|2,3|3,4|4,5|5,5|6,6|5,6|6,7|7,7|8,8|7,8|8,9|9
15	TEXTJOIN	#WERT!
Sheet1

Cell Formulas
Range	Formula
A1:J10	A1	=ROW() & "|" & COLUMN()
B14	B14	=TEXTJOIN2019(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)
B15	B15	=TEXTJOIN(",",FALSE,ThreeByThreeByThree TwoByTwoByFive)

Named Ranges
Name	Refers To	Cells
ThreeByThreeByThree	=Sheet1!$A$1:$C$3,Sheet1!$D$4:$F$6,Sheet1!$G$7:$I$9	B14:B15
TwoByTwoByFive	=Sheet1!$A$1:$B$2,Sheet1!$C$3:$D$4,Sheet1!$E$5:$F$6,Sheet1!$G$7:$H$8,Sheet1!$I$9:$J$10	B14:B15

But: AFAIK there is no VBA involved.

Thanks and regards,
Bernd

 

[smozgur]

But: AFAIK there is no VBA involved.

Yes, it is entirely VBA-related as I tried to explain. RefersToRange method fails on multi-area ranges if the list separator is semicolon - ";" - as used in =SUM(1;2) instead of =SUM(1,2). XL2BB add-in is using the RefersToRange method to analyze the names in the workbook. So, if you have a regional format that the list separator is semicolon, and the named range consists of multiple areas, then XL2BB fails because the RefersToRange VBA method fails. Hope it makes sense.

Thanks for testing and confirming.

 

[Sulprobil]

Hi,
Thanks, you are right.