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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top