Forum Discussion
Advanced Formula Environment - Hidden function scope issues
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.
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:
- Temporarily unhide the hidden functions:
- Modify the names of the hidden functions to remove the underscore prefix or change them to a different naming convention that doesn't hide them.
- Save the workbook to ensure the changes are applied.
- Edit or delete the definitions of the functions:
- Open the Name Manager (Formulas tab > Defined Names group > Name Manager).
- You should now be able to see and select the previously hidden functions in the Name Manager.
- Edit or delete the definitions of the functions as needed.
- Save the workbook.
- Re-hide the functions:
- If you still prefer to keep the functions hidden, you can modify their names again to include the underscore prefix or use the original naming convention.
- Save the workbook to apply the changes.
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.
