hatman
Well-known Member
- Joined
- Apr 8, 2005
- Messages
- 2,664
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):
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?
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):
Code:
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?