VBA custom functions - show input description

Excelsemi

New Member
Joined
Mar 6, 2014
Messages
41
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.

Code:
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Excelsemi

New Member
Joined
Mar 6, 2014
Messages
41
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?

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,168
Members
414,215
Latest member
SRGDZMCS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top