+ Reply to Thread
Results 1 to 9 of 9

UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOptions

  1. #1
    Registered User
    Join Date
    01-05-2021
    Location
    US
    MS-Off Ver
    16
    Posts
    6

    UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOptions

    I am trying to add function and argument descriptions to some custom functions using Application.MacroOptions. I put an example below. The macro runs fine, but after saving and closing Excel, the descriptions are gone. How do you make the descriptions remain after closing the application?

    Please Login or Register  to view this content.
    Last edited by footballfan22; 01-05-2021 at 03:43 PM.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    If the udf is in a given workbook, and you close that workbook, the udf ceases to be available, and the descriptions you set are effectively purged with the udf.

    I should add that you could call your setup macro from the workbook's Workbook_Open event handler. That should restore everything whenever the workbook is open and in use.
    Last edited by hrlngrv; 01-05-2021 at 04:06 PM. Reason: addendum

  3. #3
    Registered User
    Join Date
    01-05-2021
    Location
    US
    MS-Off Ver
    16
    Posts
    6

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    All of these UDFs are stored in a separate .xla file. I'm not very familiar with those, but all the functions are automatically available in any new workbook.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    XLA files also run Workbook_Open and Application-level event handlers. Run your setup macro from the XLA's Workbook_Open event handler.

  5. #5
    Registered User
    Join Date
    01-05-2021
    Location
    US
    MS-Off Ver
    16
    Posts
    6

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    That worked great. Just to clarify, will I need to include a function description sub for each of my custom functions in the Workbook_Open event handler? Or is there a way to run the sub once and have the descriptions permanently saved?

  6. #6
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    There are may ways to handle this. One HUGE separate macro with setup for every UDF. A huge Workbook_Open with all this setup. Separate macros for each UDF.

    My own preference would be table-driven design. XLA files also need to have at least one worksheet, but it's never seen. Nevertheless, it's there. You could use it to store udf name in column 1, udf description in column 2, and individual argument descriptions in the subsequent columns. Use one macro (separate from but called by Workbook_Open) to iterate through the table by row, gathering the parameters for Application.MacroOptions, then calling it with those parameters.

  7. #7
    Registered User
    Join Date
    01-05-2021
    Location
    US
    MS-Off Ver
    16
    Posts
    6

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    That's a great idea. I'm trying to open a worksheet in the .xla file but can't. How do you view the .xla file worksheet?

  8. #8
    Registered User
    Join Date
    01-05-2021
    Location
    US
    MS-Off Ver
    16
    Posts
    6

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    Now I'm getting Run-time error '1004': Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command.

    For the .xla file in the Properties window of ThisWorkbook, I change IsAddin to False. This makes the worksheet visible. I made a table as you suggested above. Then I changed IsAddin back to True before saving and closing. Here is my code for Workbook_Open:

    Please Login or Register  to view this content.
    The AddFunctionDocumentation Sub is in a module, code below:

    Please Login or Register  to view this content.
    Please let me know if you have any suggestions.

  9. #9
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: UDF Description and UDF Arguments Descriptions Not Saving with Application.MacroOption

    This is why I hate this sort of thing. Too many glitches. I had to recreate an extremely oversimplified add-in to test.

    Looks like you need to toggle the add-in back and forth from add-in, to not add-in, to add-in. See the Workbook_Open code in the workbook attached below. Actually, I had to put the .XLAM into a .ZIP file for this site to accept it. You'd need to extract the .XLAM from the .ZIP file.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Application.MacroOptions Help File Question
    By phapronulu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2016, 05:22 PM
  2. Application.MacroOptions: StatusBar property not working
    By greglittle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2012, 12:56 AM
  3. Copy Name with description in a cell as many times descriptions come in a range
    By brajpalshishodia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-12-2012, 12:31 PM
  4. Replies: 0
    Last Post: 08-16-2012, 02:01 PM
  5. Providing "descriptions" for VBA User Function Arguments
    By Mark@Work in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2006, 11:14 AM
  6. application.dialogs(xlDialogPrint) - arguments
    By David in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2005, 03:05 AM
  7. [SOLVED] passing arguments by postion through application.run
    By ben in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2005, 09:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1