Jul 11 2023 01:06 PM
Jul 11 2023 01:06 PM
I am working with formulas in excel labs / advanced formula environment. I created some recursive helper functions that would never be called directly. I chose to prefix these functions with an underscore. Conveniently this appears to make the functions hidden to the workbook as well although you can still use them (no tooltips). They are also hidden in the name manager.
When a worksheet is copied it creates a new formula / name definition in the name manager with a lower scope. Normally this is annoying, but I can go into the name manager and delete everything with scope below the workbook. The hidden formulas do not appear in the manager so there is no convenient way to delete / reset the definition. This means that you cannot edit the definition of the hidden function. While everything appears to load in correctly, the results of any of the functions will not change. If I simply rename the helper functions to something not hidden, everything works as advertised.
Jul 12 2023 12:54 AM
In Excel, hidden functions, such as those prefixed with an underscore, can indeed create challenges when it comes to managing and editing them. The issue you described regarding the inability to delete or reset the definitions of hidden functions in the Name Manager is a known limitation in Excel. Since hidden functions do not appear in the Name Manager, it becomes difficult to directly manage their definitions.
To work around this issue, you can try the following approach:
By temporarily unhiding the hidden functions, you can access and modify their definitions in the Name Manager. After making the necessary changes, you can re-hide the functions by renaming them accordingly.
It's worth noting that this workaround may be a bit tedious if you have many hidden functions to manage. Additionally, when copying worksheets, you may need to repeat this process to ensure the proper management of the hidden functions.
Consider using this workaround as a temporary solution, and if managing hidden functions becomes too cumbersome, you might consider alternative approaches such as using non-hidden function names or organizing the functions in a separate module or workbook to better manage their definitions.Since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together.
My answers are voluntary and without guarantee!
Hope this will help you.