User-function description from code? 1

[N1GHTEYES]

If I pull up a user-function in the object browser, I can enter or edit a description of the function. Can somebody please tell me how I could access that description in code?

If you're interested, the reason I ask is as follows.

I have an .xla I tend to use to keep most of my useful macros and functions. It gets upgraded fairly often and a few of my colleagues use it too. It seems like it would be easiest to include a sheet in the xla which had a listing of: all of the functions, when they were added, why, their inputs, outputs, external effects & so on. It could also have a column for the descriptions. If I could then get the xla code for "Thisworkbook" to copy the descriptions to the userfunction descriptions on startup, it would mean that I'd only need to enter them in one place and I'd always be sure they were available to the users at the latest version.

 

[WalkerEvans]

N1GHTEYES,

Try this:

Private Sub Workbook_Open()
Set ExcelSheet = GetObject("DescriptionSheet")[i]'With full path names[/i]
ExcelSheet.Application.Visible = True
ExcelSheet.Parent.Windows(1).Visible = True
Copy Before:=Worksheets("Name")[i]'The worksheet following the Description sheet[/i]
ExcelSheet.Application.Quit
End Sub

This should do the job, depending on how your .xla is put together. This presumes that the Description sheet is the first one in your .xla; if it is not, the Copy line will need revision.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[N1GHTEYES]

Thanks for the reply, but that is not what I was after. I obviously did not make myself clear. I'll try again.

Lets assume I have a sheet with the descriptions in, and that that sheet is already part of my xla. I do not want to copy that sheet anywhere. What I want to do is to take the description of each userfunction as written in the sheet, and set those to be the descriptions for those userfunctions as reported by Excel.

For example, here is how I would do it manually:

Lets say I write a userfunction called "Hello".
In object browser, I select the "Hello" function, right-click and choose properties. This gives me a dialogue box in which I can write a function decription, such as "This function writes the word 'Hello'".

If I now select the function in a cell in the spreadsheet, this description appears in the function dialogue box.

Great, that's fine if I want to go through the above process, but I don't. What I want to do is access that "description" field of the userfunction programatically, so I can write some code to take all the descriptions as listed in a sheet in my xla, and assign them to the appropriate functions.

 

[combo]

Try Application.MacroOptions method

combo

 

[N1GHTEYES]

Ta Combo.

That was exactly what I was after.

I knew I'd recognise it when I saw it. I think my remaining braincell devoted to memory is on the fritz again.