VBA custom functions - show input description

[Excelsemi]

Hi all

I am doing a finance course, with quite a few formulas constructed in VBA (fx. The Black-Scholes formula etc.)
However, when there are +40 functions, it can be hard to remember what inputs are supposed to be place first, last etc. in the formula in the Excel Cell.

So I have been searching for a way to show the name of the input between the ( Semicolons which splits up the different inputs in the formula.

so when I type the function I have written in Excel:

=BSCallPrice(Input1;Input2;input3;input4;input5) it is blank, but is there a a way to name the inputs like when using Formula NPV which shows : NPV(Rate;value1;value2;...)
So I know that the first input is the rate.

In excel it should show : =BSCallPrice(S;X;T;i;sigma) so I know in which sequence my inputs should be typed.

Function BSCallPrice(S, x, T, i, sigma)
' Calculates Black-Scholes call price
'S: Stock price
'X: Exercise price
'T: Time to maturity
'i: Interest rate
'sigma: standard deviation
Dim d1, d2, Nd1, Nd2
d1 = (Log(S / x) + (i + 0.5 * sigma ^ 2) * T) / (sigma * Sqr(T))
d2 = d1 - sigma * Sqr(T)
Nd1 = Application.NormSDist(d1)
Nd2 = Application.NormSDist(d2)
BSCallPrice = S * Nd1 - x * Exp(-i * T) * Nd2
End Function

Hope it makes sense Any help would be very appreciated.

 

[njimack]

Providing you're using Excel 2010 or later, this link allows you to add descriptions to each argument, as well as adding it to one of the categories of in-built functions: Spreadsheet Page Excel Tips: User-Defined Function Argument Descriptions In Excel 2010

 

[Excelsemi]

Providing you're using Excel 2010 or later, this link allows you to add descriptions to each argument, as well as adding it to one of the categories of in-built functions: Spreadsheet Page Excel Tips: User-Defined Function Argument Descriptions In Excel 2010

Thank you !

So if I understand i correctly:

To take the example of my formula, I have to edit the DescribeFunction() code for all my built in VBA functions, and add the descriptions for the single cells or how does it work? But there is no way to have it shown in Excel as I write the formula in a cell, I have to open the description (Function Arguments) tab?

Sub DescribeFunction()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 5) As String

   FuncName = "BSCallPrice"
   FuncDesc = "Returns the nth element of a string that uses a separator character"
   Category = 7 [COLOR=#0000ff]'Text category[/COLOR]
   ArgDesc(1) = "S"
   ArgDesc(2) = "X"
   ArgDesc(3) = "T"
   ArgDesc(4) = "I"
  ArgDesc(5) = "SIGMA"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub