LET() creates (multiple) invisible names

User avatar
diegol
StarLounger
Posts: 94
Joined: 28 Jan 2010, 04:15
Location: Buenos Aires, Argentina

LET() creates (multiple) invisible names

Post by diegol »

Hi all,

I found a strange behaviour with the LET function.

Open a new workbook, and in cell A1input: = LET(Architecture, 10, Architecture)

Using Jan Karel Pieterse's Name Manager add-in, you can see Excel has created a few invisible names.
_xlfn.LET: guess it's Excel's way to embed LET into the file so it will show the result when opened in a previous ver. not supporting the function.
_xlpm.Architecture: with another "prefix" (_xlpm), Excel seems to denote and maybe handle as a name the variable declared in a LET.

So far curious, but so good.

Now assume your LET formula is going to grow so you judge appropriate to shorten "Architecture" to just "A". One possible go at it would be hit F2, position the cursor after the last "e" in the first instance of "Architecture", then hit backspace some 11 times. Do the same with the second instance of "Architecture". The formula now reads =LET(A, 10, A)

Now when I have a look at the Name Manager:
_xlfn.LET
_xlpm.A
_xlpm.Ar
_xlpm.Arc
_xlpm.Arch
_xlpm.Archi
_xlpm.Archit
_xlpm.Archite
_xlpm.Architec
_xlpm.Architect
_xlpm.Architectu
_xlpm.Architectur
_xlpm.Architecture

Excel seems to have created one name after each partial edition, triggered by each backspace stroke. Now that's unexpected.

Not sure how many users peruse into invisible names, but it has proved useful for me to do away with unnecessary data and keep the file clean and slim. Sadly, after several edits of several LETs, the name list turns awfully unwieldly.

Now delete cell A1's content. You can even delete the cell / row / column. The name list remains intact! These have come to stay.
What's more, you cannot delete these _xlpm. prefixed names (nor the _xlfn.LET name, but that's not what really worries me) using JKP's add-in, not even after deleting the formula.

The described behaviour is really annoying, and also occurs when you decide to lengthen a variable's name within a LET using one-keystroke edits.

Some questions that come up:
* Is this a behaviour encountered by others here or is it just me?
* Risk of file bloat/corruption as a result of this?
* Any documentation that I can read to learn more?

Many thanks in advance.

:argentina: Diegol

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LET() creates (multiple) invisible names

Post by HansV »

I have Excel 2021 and I cannot reproduce this behavior.

S1654.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LET() creates (multiple) invisible names

Post by HansV »

Could it be caused by an add-in?
Best wishes,
Hans

User avatar
diegol
StarLounger
Posts: 94
Joined: 28 Jan 2010, 04:15
Location: Buenos Aires, Argentina

Re: LET() creates (multiple) invisible names

Post by diegol »

Hi Hans!

Now that you mention it, I should have specified my Excel version: Microsoft 365 Excel, version 2207.

I have a few addins (Enquire, PowerPivot, some third-party ones). I disabled all other than the Name Manager. The issue persists.
Then I disabled the Name Manager addin. Did the edit thing, then enabled the Name Manager so I can view invisible names. The issue persists.

In each case, after disabling the addins, and before attempting to recreate the behaviour, I closed the Excel application and restarted it.

:argentina: Diegol

User avatar
diegol
StarLounger
Posts: 94
Joined: 28 Jan 2010, 04:15
Location: Buenos Aires, Argentina

Re: LET() creates (multiple) invisible names

Post by diegol »

I asked a collegue who doesn't have the Name Manager installed to follow the steps, then send the Excel file over to me.
Had a look at it with the Name Manager add-in - the invisible names are there (all of them)!

:argentina: Diegol

User avatar
diegol
StarLounger
Posts: 94
Joined: 28 Jan 2010, 04:15
Location: Buenos Aires, Argentina

Re: LET() creates (multiple) invisible names

Post by diegol »

Also for clarity, I'm using Name Manager version 4.5, build 688.

:argentina: Diegol

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LET() creates (multiple) invisible names

Post by HansV »

Thanks for performing such a thorough investigation. Let's see if someone with Microsoft 365 can reproduce the problem.
I have Name Manager 4.5 build 692. The most recent one is build 697.
Best wishes,
Hans

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: LET() creates (multiple) invisible names

Post by rory »

I see the same behaviour with M365 V2209 (Insider Beta). Not using Name Manager.
Regards,
Rory

User avatar
diegol
StarLounger
Posts: 94
Joined: 28 Jan 2010, 04:15
Location: Buenos Aires, Argentina

Re: LET() creates (multiple) invisible names

Post by diegol »

Also I should mention we use Office in Spanish.
Nowadays changing language isn't as cumbersome as it used to be some years ago where you needed to uninstall Office in, say, Dutch, then reinstall in English. Not sure if the registry needed some cleanup previous to the reinstall.
Now it is done much easier via the Options | Language menu.

I mention this because I recall reading someone having difficulty with their field separator setup in Excel (it happened to be "/" or "\", I can't remember) and LAMBDAS. So maybe, at some point the language settings, or the field separator (I use ","), could interfere with proper functioning of formulas rolled out in the last 3 years whose logic, or internal programming (the innards), seems very different to more traditional Excel formulas.

:argentina: Diegol

User avatar
diegol
StarLounger
Posts: 94
Joined: 28 Jan 2010, 04:15
Location: Buenos Aires, Argentina

Re: LET() creates (multiple) invisible names

Post by diegol »

rory wrote:
11 Aug 2022, 15:45
I see the same behaviour with M365 V2209 (Insider Beta). Not using Name Manager.
Thanks rory!
Out of curiosity, how do you get to see the invisible names without the Name Manager addin? Excel's built in name manager feature doesn't display these as far as I know.
I guess you can use VBA for that purpose but I'm not so VBA literate.

:argentina: Diegol

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: LET() creates (multiple) invisible names

Post by rory »

diegol wrote:
11 Aug 2022, 16:06
I guess you can use VBA for that purpose
Yup. :)
Regards,
Rory

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LET() creates (multiple) invisible names

Post by HansV »

I have posted about this on a private Microsoft forum; I hope it gets picked up.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LET() creates (multiple) invisible names

Post by HansV »

@diegol: one question asked there: do you have AutoSave turned on?
Best wishes,
Hans

User avatar
diegol
StarLounger
Posts: 94
Joined: 28 Jan 2010, 04:15
Location: Buenos Aires, Argentina

Re: LET() creates (multiple) invisible names

Post by diegol »

Hans, thanks for posting the issue.

No, I don't have Autosave turned on. I tend to save on a frequent basis so when Excel crashed I normally discarded those recovered files.
Also at some point the Autosave feature seems to have started saving the file to OneDrive /Sharepoint, which I dislike and setting the defaults back to the local drive wasn't so intuitive for me.
My guess is before putting more effort into it, at some point I turned Autosave off.

But just in case, I turned it on and the behaviour persists.

:argentina: Diegol

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LET() creates (multiple) invisible names

Post by HansV »

Thanks. One of the users on that forum that having AutoSave turned on might cause the problem or contribute to it. That has been disproved too now.
Best wishes,
Hans