MacroOptions Not persisting as expected

[hatman]

I am so confused.

Once upon a time, I used Laurent Longre's utility to get UDF's fully documented in teh Function Wizard. With the addition of the ArgumentDescriptions argument for the Application.MacroOptions method in 2010, I have been converting my Addins to use this method instead. I wrote a routine in the Addin_Install event to parse the shFunctions worksheet I already had in place from Laurent's utility, and everything worked perfectly. Until I hit (2) snags ysterday. First snag occurred when I tried installing my addin on a 2007 machine, and it wouldn't even compile because of the unrecognized argument. So I adjusted my code for a conditional compile (note it tooke me a couple of tries to get it right, and the wrong case fired on amy machine the first time... this is an important point, I think):

Private Sub Workbook_AddinInstall()
    Dim l_Row As Long
    Dim cnt As Long
    Dim cnt2 As Long
    Dim l_Col As Long
    Dim Args() As Variant
   
   l_Row = shFunctions.Range("A65536").End(xlUp).Row
   
   For cnt = 2 To l_Row
   
        l_Col = shFunctions.Range("AA" & cnt).End(xlToLeft).Column
        
        If l_Col > 6 Then
        
            ReDim Args(1 To l_Col - 6)
            
            For cnt2 = 1 To UBound(Args, 1)
            
                Args(cnt2) = shFunctions.Range("F" & cnt).Offset(0, cnt2).Value
            
            Next cnt2
            
            Debug.Print Version
            
            #If VBA7 Then
            
                Application.MacroOptions macro:=shFunctions.Range("A" & cnt).Value, Description:=shFunctions.Range("F" & cnt).Value, Category:=shFunctions.Range("D" & cnt).Value, Argumentdescriptions:=Args
                
            #Else
            
                Application.MacroOptions macro:=shFunctions.Range("A" & cnt).Value, Description:=shFunctions.Range("F" & cnt).Value, Category:=shFunctions.Range("D" & cnt).Value
                
            #End If
            
        Else
        
            Application.MacroOptions macro:=shFunctions.Range("A" & cnt).Value, Description:=shFunctions.Range("F" & cnt).Value, Category:=shFunctions.Range("D" & cnt).Value
            
        End If
   
   Next cnt
   
End Sub

At the same time, I ALSO added a new UDF with a single argument to the Addin. I uninstalled the addin on my machine, and re-installed, to fire the code and get the new function to show up in my custom category with all o fthe bells and whistles. But it did not have all of the bells and whistles. The description for the argumnet of the new UDF was missing, while teh descriptions for all of teh otehrs were still there. But it got more bizarre than that. For some reason I closed Excel, and re-opened it, then went to my custom function category. All of the ORIGINAL UDF's were in place, but the new one was not. Now, I had just added (3) new UDF's on Monday, with teh conditional compile, and they worked FINE, still show up fine, all of their argument descriptions are FINE. Bu the one I added yesterday (Tuesday) is all funky. I uninstalled and re-installed the addin, tried renaming the file as well as teh Addin, tried shutting down Excel in between and re-starting it before installing my addin... nothing changes this behaviour. I finally added a msgbox in the code to determine which component of the conditional compile is firing, and got an error message telling me that I cannot modify a function in a hidden workbook, a malady I have seen documented when the code is placed in the Open event instead of the addin_install event. After that, even when I replaced teh msgbox with a debug.print, and later remove dteh line altogether, I would get the hidden workbook error whenever I installed the addin. So I COMMENTED OUT ALL OF THE ABOVE CODE. Got rid of it... kaput. Uninstalled the addin and closed Excel. Renamed the file on my hard-drive, went into the properties and renamed the addin title. Re-opened Excel, and then installed the addin. Voila, my custom function category suddenly appeared, and all of teh documentation for the UDFS in that file were present in all of their glory.

My conclusion is that Excel saves the MaroOptions information into a file somewhere and then associates it with a particular addin. Makes sense, as the Addin_INstall event does not fire when you open the application and the addin is already installed, so the info must come from SOMEWHERE. I see now that the data in that file is erratically being over-written. It is not intended to accomodate the sheer number of changes I am implementing with tehse rapid-fire install/re-install activities as I edit, troubleshoot and basically monkey with this thing as a developer who is doing testing and refining. Anyone know how to fix my issue? I plowed through all of the AppData locations that made sense, looking for a likely file... no dice. Registry perhaps?

 

[hatman]

Some Progress

I am making some progress. I have simplified my scope to playing with a single UDF. Code looks like this:

Function GMT_2_Double(Date_String) As Double

Dim strYear As String
Dim Rem_String As String
Dim arr
Dim arr2
Dim datDate As Date
Dim tme As Date
Dim dblTme As Double
Dim strng As String

strng = Date_String

strYear = Left(strng, 4)

Rem_String = Mid(strng, 6)

arr = Split(Rem_String, ":")

arr2 = Split(arr(3), ".")

datDate = DateValue("12/31/" & CLng(strYear) - 1)

datDate = datDate + CLng(arr(0))

tme = TimeValue(arr(1) & ":" & arr(2) & ":" & arr2(0))

datDate = datDate + tme
dblTme = CDbl(datDate) + 1 / 24 / 3600 * CDbl("0." & arr2(1))

GMT_2_Date = dblTme
End Function


Sub Push_Macro_Options()

Dim Args(1 To 1)

Args(1) = "JMEWS Date in the format YYYY_DDD:HH:MM:SS.000"

Application.MacroOptions macro:="Cnvrt.GMT_2_Double", Description:="Convert a GMT String Date " & _
"to a double precision decimal that can be displayed as an Excel Date (Custom Date Format " & _
"should be M/D/YY HH:MM:SS.000 or equivalent.)", Category:="JMEWS Functions", Argumentdescriptions:=Args

End Sub

In the addin I really want this to reside in, the Argument Description won;t show up at all... but if I start a NEW workbook, paste this code into a new module in teh new workbook, and execute the macro option push, everything shows up fine. So I started doing some wacky stuff with that new file. I exported the code module, and opened it with Wordpad... found something astonishing:

 Attribute VB_Name = "Module1"
Option Explicit
　
　
Function GMT_2_Double(Date_String) As Double
Attribute GMT_2_Double.VB_Description = "Convert a GMT String Date to a double precision decimal that can be displayed as an Excel Date (Custom Date Format should be M/D/YY HH:MM:SS.000 or equivalent.) yoo ho, new stuff here"
Attribute GMT_2_Double.VB_ProcData.VB_Invoke_Func = " \n18"
　
Dim strYear As String
Dim Rem_String As String
..... (remaining code unchanged)

So that tells me that what is hapenning is that the MacroOptions method is actually making a change to the workbook file. I added some additional text to the description, saved the .bas file, then imported it into the workbook. Saved and closed teh workbook, then re-opened, and the new added text showed up in the function wozard.

This behaviour makes me question the validity of putting the MacoOptions command in the AddinInstall event at all. By rights, it should be a one-time execution by the developer, which changes the file, now distribute it all of your users, and the proper header info is already embedded in the file. This ALSO explains why some of my changes seemed to be getting discarded. I invoked the MacroOptions method in a file, but then DID NOT SAVE IT, because the .Saved property was not changed to FALSE by the Method execution, and I did not know that the file had been altered... changes to the header info was NOT SAVED, so when I opened the workbook the next time, none of those changes to the meta-data had been saved, it reverted to the previously saved data.

So now I needed to find where the argument descriptions were saved. Saved the workbook as a .zip file, and opened it... in the \xl\workbook.xml file, I found this snippet in the middle:

- <x14:definedName name="GMT_2_Double">
- <x14:argumentDescriptions count="1">
  <x14:argumentDescription index="0">JMEWS Date in the format YYYY_DDD:HH:MM:SS.000</x14:argumentDescription> 

  </x14:argumentDescriptions>


  </x14:definedName>

So now I have some of the pieces. There is also an entry in this file for the function category, but it's not germain here. Now to figure out how this all plays together. And why the argument description won't show up in the "REAL" workbook. Some syntax issue, perhaps? Not sure.

 

[hatman]

Resolved

I am going to document this, so it's available for others. Somewhere along the lines, as I was including the same UDF both in my "All Encompassing" addin, as well as a specific addin for a particular user, I misinterpretted some of the wizard behaviour, and thought that Excel was getting "confused" about which descriptions went with which UDF in which file. An article I found somewhere suggested (stringly, I might add) that to reduce confusion, the VBAProject name should preceed the function name to properly scope it. This is NOT a proper syntax. The Method will obligingly include teh entire text string, but then the XL Engine will not be able to "match them up". FURTHERMORE, I discovere dthat if you decide to change the name of your UDF after you have pushed the MacroOptions method, a new entry is made in teh XML, but the old one is not purged. Just residue, but it also counts as file bloat. I have not dug in to see if the old residue can muck up the works... I am manually going into the addins where I have used this method, clearing out ALL of the pertinent XML code, then pushing the data back in using a one-time procedure, and saving. I have not tested this, but my belief is that XL 2007 will ignore this XML entry. In this way, I can completely avoid the need for the conditional compile. I will comment out the entire macro_option_push procedure before distributing.

 

[jkpieterse]

Re: Resolved

Interesting stuff indeed. I guess you read my article about implementing the Laurent Longre trick:

Register UDFs

Never knew MacroOptions adds hidden attributes to the modules

 

[hatman]

Re: Resolved

Yes, I have read your article in recent years, which while still a little beyond my ability to understand simply from reading, it went a long way toward explaining why and how Laurent's trick worked. And I thank you greatly for the time you put into that article (and the rest of your site: a true wonderful resource for peons like me who hack their way through this stuff). I had been using Laurent's trick for at least 10 years, and while effective, I have always regretted the need to distribute the additional dll. The additional dll also proved problematic when my company started blocking E-Mailing of dll's... it's getting tough around here. When we finally upgraded to 2010, and I saw the additional MacroOptions argument, I was all over it as a replacement for Laurent's trick, something more easily distributed, though admittedly, not backwards compatible the way the dll solution is. Anywho, you saw my challenges... and no documentation out there for how the MacroOptions method works. Even though I figured this much out through brute force, there are still a couple of loose ends. In the absence of any documentation that is more complete, what I have here will have to suffice. Thanx for the feedback.

 

[jkpieterse]

Re: Resolved

Perhaps a moot point since you upgraded to 2010 anyway, but the method in my article does not require you to distribute dll's.

 

[hatman]

Re: Resolved

Apparently I didn;t read it closely as I thought, nor understood it as well as I had hoped. I need to go back and take a closer look, now I am intrigued, especially since I need to support 2007 for a while yet (as a mega-corporation, it takes about 2 years for everyone to get upgraded after they have completed their evaluation period and started the actual upgrade process... I am at the front of the curve). Thank you again.