Paramters or Arugment Auto-Label for User-Defined Function

[Andrew.D.Robertson]

Hi,

I am trying to create a auto-label to display the valid parameters
that would appear when you enter a user defined function into a
worksheet, much the same way that a label appears when you type:

"=sum("

and then the following label appears below the active cell:

"Sum(number1, [number2],...)"

I would like a lable like this to appear, with text I define, when I
input my user-defined function into a worksheet.

Can anyone help?

Thanks!

 

[timebird]

i think it is a description for a function.
you can add a description with Application.MacroOptions

a example that i'm using...

With Application

'.MacroOptions Macro:="", Description:="", Category:=1

'//Risk-Adjusted Performance Measures
.MacroOptions Macro:="mSHARPE", Description:="It is calculated by
subtracting the risk free rate from the rate of return for a portfolio and
dividing the result by the standard deviation of the portfolio returns.",
Category:=1
.MacroOptions Macro:="mTREYNOR", Description:="the Treynor Ratio
(sometimes called Reward-to-Variability-Ratio) also relates excess return to
risk; but systematic risk instead of total risk is used. The higher the
Treynor Ratio, the better the performance under analysis.", Category:=1
.MacroOptions Macro:="mIR", Description:="In investing terminology,
the ratio of expected return to risk, as measured by standard deviation.
Usually, this statistical technique is used to measure a manager's
performance against a benchmark.", Category:=1
.MacroOptions Macro:="mALPHA", Description:="ALPHA is the
risk-adjusted returns that a portfolio manager generates in excess of the
risk-adjusted returns expected by the Capital Asset Pricing Model (CAPM).",
Category:=1
.MacroOptions Macro:="mBETA", Description:="BETA determines the
relationship of the undiversifiable risks in relation to the expected return
of the stock", Category:=1
.MacroOptions Macro:="JENSEN", Description:="Jensen's Alpha measures
the value added by selection activities. Alpha is defined as the difference
between the average realized return of a portfolio manager with private
information and the expected return of the passive strategy based upon public
information only with equal systematic risk.", Category:=1
.MacroOptions Macro:="mSORTINO", Description:="the Sortion Ratio
indicates the excess return per unit of 'risk' associated with the 'excess
return'. While 'risk' in the context of the Sharpe ratio means volatility,
the Sortino Ratio uses downside-volatility.", Category:=1
.MacroOptions Macro:="mRESIDUAL_VARIANCE", Description:="Also called
unexplained variance. In general, the variance of any residual", Category:=1

End With

*Category Number
'The category numbers are as shown below:
'0 No category appears only in All
'1 Financial
'2 Date & Time
'3 Math & Trig
'4 Statistical
'5 Lookup & Reference
'6 Database
'7 Text
'8 Logical
'9 Information
'10 Commands normally hidden
'11 Customizing normally hidden
'12 Macro Control normally hidden
'13 DDE/External normally hidden
'14 User Defined default
'15 Engineering only available if the Analysis Toolpak add-in is installed

--
msn (e-mail address removed)
---------------------------------------------
the best time to plant a tree was twenty years ago.
the second best time, is today - Chinese proverb

Hi,

I am trying to create a auto-label to display the valid parameters
that would appear when you enter a user defined function into a
worksheet, much the same way that a label appears when you type:

"=sum("

and then the following label appears below the active cell:

"Sum(number1, [number2],...)"

I would like a lable like this to appear, with text I define, when I
input my user-defined function into a worksheet.

Can anyone help?

Thanks!

 

[Andrew.D.Robertson]

Thanks, but that didnt quite do what I was looking for. That provided
a decription of the macro when you select it on the "Formulas" tab in
the "Function Library" group (I am using Excel 2007). What I am
looking for, though, is the automatic text that appears as you type in
the first bracket "(" in the spreadsheet. It's a little blue label
that appears underneath the active cell.

If application.macrooptions does this, I guess I dont understand
specifically which field to use then - I tried the "Description" field
and the "StatusBar" field, but neither of those produced the right
result.

Thank you for your help with this though, I was not aware of the
MarcoOptions method before this

 

[T. Valko]

What you're wanting to do is add what is called a function tool tip. Some
folks call them balloon tips. Unfortunately, it can't be done. You *might*
be able to add something to the "insert function wizzard" but since you're
using Excel 2007 even that'll be very difficult. Excel 2007 is not easily
modified. I suggest you post in the programming forum.

 

[Andrew.D.Robertson]

Thanks Biff, I will do as you suggested and post in the programming
forum. I am dissappointed that Microsoft did not integrate such a
simple feature into the 2007 app.

 

[Gord Dibben]

You can show argument help but not ToolTips.

To customize your User Defined Functions....

See Laurent Longre's website for FUNCUSTOMIZE add-in.

ZIP file also includes a demo file and a how-to-use file.

http://longre.free.fr/english/index.html

Ballon(Tool) Tips are not available with Laurent's method.


Gord Dibben Excel MVP