Compatibility with Excel Labs Add-In and Minor Bug Fixes

[tchoha]

First, thank you for creating such a fantastic library! It's been quite useful for me. However, I encountered some issues:

1. The dots in function names cause problems with Excel Labs.
2. The "A" prefix for function names can be inconvenient in certain scenarios.
3. I found small typo bugs in two of the functions.

I’ve created a script that resolves the naming issues and fixes the bugs. After running the script, you can import the functions in Excel Labs in a module named according to your needs or preferences.
I’ve attached the script for your reference and would appreciate any feedback or updates. Let me know if I can assist further.

AutoXL - fix.zip

[chengtie]

Hello @tchoha, thank you very much for opening the first issue in the repository and for your well-considered remedy code!

There were indeed some typos, which I have just fixed in a commit: 5c10a2a.

Could you provide more details about the issues with dots in function names when using Excel Labs? Additionally, in which scenarios might the 'A' prefix for function names be inconvenient?

[tchoha]

Excel Labs says "Invalid name" for the dotted names. See attached screenshot.

... and also somehow considres the name "hidden" and the functions do not appear in Excel Name Manager.
About the "A" prefix - if you create something named A in any context it will consider it an ambiguity with the module name. However if you don't cross reference inside your module with names including the A. prefix, the module can be named(spaced) to the conveninence of the particular project where it is used.

[chengtie]

I just tried using Excel Labs, and it appears they don't allow function names with dots. I don't think this was the case when I developed the library using AFE, otherwise, I would have been alerted about it.

Additionally, I realized that we can define names with dots using the traditional Name Manager. In the following screenshot, I was able to define C.TEST.OUTSIDELABS in the traditional Name Manager, and it works. So, maybe it's a bug in Excel Labs that prevents function names from having dots? This is something we could clarify with the Excel Labs team. However, for now, we need to change the function names (e.g., via @tchoha 's code) if we load the text file in Excel Labs.

[chengtie]

About the "A" prefix - if you create something named A in any context it will consider it an ambiguity with the module name. However if you don't cross reference inside your module with names including the A. prefix, the module can be named(spaced) to the conveninence of the particular project where it is used.

I'm still not sure if I fully understand the scenarios. Could you provide an example?

[tchoha]

About the "A" prefix - if you create something named A in any context it will consider it an ambiguity with the module name. However if you don't cross reference inside your module with names including the A. prefix, the module can be named(spaced) to the conveninence of the particular project where it is used.

I'm still not sure if I fully understand the scenarios. Could you provide an example?

A simple example is "LET(A, 12345, A.SomeAutoXlFunction() + A)". Sure that "A" can be avoided as a name for anything else, but I believe it is nicer to be able to name(space) your module with whatever prefix one wants. This is possible after the modifications my script does as it removes the hardcoded "A." prefix in internal calls between your functions.

Maybe it will be best that we make a Zoom session if you are willing to discuss these matters in more detail.

[chengtie]

Maybe it will be best that we make a Zoom session if you are willing to discuss these matters in more detail.

Sure, please let me know your email, or you can write to me at tie.cheng@matrixlead.com to arrange a meeting.

I ran some tests. I defined A as a named range (=Sheet1!$F$16), and I also defined A as a module with A.TEST, A.TESTB, and A.TESTC. Here are some observations:

• It's fine to have a named range A and a module A at the same time.
• However, when there is a formula that defines a variable A inside, the variable A will shadow (or overwrite) the named range A and the module A. For instance,
  • =LET(A, 12345, A + A) returns 24690 (instead of 12445)
  • =LET(A, 12345, A.TEST + A) returns #FIELD!
  • =LET(A, 12345, A.TESTC() + A) is not acceptable. Here is a screenshot:
    
• I think the above semantics on variable scope make sense. If people choose to name a variable as A in a formula, it will conflict with the A library inside the formula. In this case, changing either the variable name or the prefix of the library via your code will avoid the conflict.