PDA

View Full Version : Application.MacroOptions: StatusBar property not working



greglittle
10-18-2012, 02:41 PM
Please help me figure out how to make the StatusBar property work for registering my UDFs. The descriptions are working correctly and they are appearing in the correct categories, but for some reason the StatusBar text is not appearing. (Is there a character limit?) Thanks!

Here is my sub to register them contained in the same module as my UDFs:

Sub RegisterUDFs()
Dim cF As String
Dim C As String
Dim PV As String
Dim G As String

cF = "Calculates the Country Risk Premium based on the Country Risk Curve." & vbLf _
& "CurveFormula(<Country Risk Multiplier>)"
C = "Calculates the CAGR of a range of values." & vbLf _
& "CAGR(range)"
PV = "Calculates the total of the Present Values of a range of cash flows." & vbLf _
& "PVsum(range,[periods/yr])"
G = "Calculates Avg. Growth of a range of values following break-even." & vbLf _
& "AvgGrowth(range)"

Application.MacroOptions Macro:="CurveFormula", Description:=cF, Category:=5, StatusBar:="CurveFormula(range) / Calculates the Country Risk Premium based on the Country Risk Curve."
Application.MacroOptions Macro:="CAGR", Description:=C, Category:=1, StatusBar:="CAGR(range)"
Application.MacroOptions Macro:="PVsum", Description:=PV, Category:=1, StatusBar:="PVsum(range,[integer (periods/yr)]) / Calculates the total of the Present Values of a range of cash flows."
Application.MacroOptions Macro:="AvgGrowth", Description:=G, Category:=1, StatusBar:="AvgGrowth(range) / Calculates Avg. Growth of a range of values following break-even."
End Sub

Here is what I have placed in my 'ThisWorkbook' object:

Private Sub Workbook_Open()
RegisterUDFs
End Sub

P.S. Am I correct in assuming that every time the workbook is re-opened the UDFs need to be registered again? (I also assume that I should unregister them on Workbook_Close, correct?)

Kenneth Hobs
10-18-2012, 02:49 PM
Yes, there is a character limit to macrooptions. More importantly, you can not have multiple lines in the statusbar.

greglittle
10-18-2012, 02:54 PM
I am not trying to put multiple lines in the statusbar. Is the '/' being interpreted by VBA as a NL?

Application.MacroOptions Macro:="AvgGrowth", Description:=G, Category:=1, StatusBar:="AvgGrowth(range) / Calculates Avg. Growth of a range of values following break-even."

Paul_Hossler
10-18-2012, 06:07 PM
According to ...


http://my.safaribooksonline.com/book/office-and-productivity-applications/0596007663/application-members/progexcel-chp-7-109

'StatusBar' is ignored

I tried some built in Functions, and they didn't display a SB either

Paul

greglittle
10-21-2012, 09:54 PM
So is there any other way to have a tooltip appear for a UDF?

Kenneth Hobs
10-22-2012, 06:50 AM
No.

Press Shift+F3 after typing the function name and the "(" to get to the description dialog.

If you need a longer description, that can be done but it is much more involved than macrooptions.

greglittle
10-22-2012, 12:21 PM
Kenneth, thanks, but I am not having any problems with the description dialog box. It works just fine and I do not need a longer description. What I am having problems with is getting the tooltip to pop-up when the function is written. I thought that was done through the statusbar property, but apparently not b/c even if I put in a very short string the tooltips don't work. The images below show what I am trying to make work for my UDFs.

Any ideas how? Thanks.

http://s9.postimage.org/j6m2g82bh/Tool_Tip.jpg

http://s15.postimage.org/or64m7xrd/tooltip2.jpg

Kenneth Hobs
10-22-2012, 01:02 PM
So is there any other way to have a tooltip appear for a UDF?


No.