- Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- Range Inputs can either be arrays or ranges on your worksheet, In the example we"ve done both, with FromDates being a range, and AnnualRates being a VBA array.
- Dates are integer numbers and our preferred data type is long. You can"t declare them as Dates because a date is not a fundamental data type in the Excel kernel. You can of course convert longs to dates within VBA and vice versa. Remember too that BF will accept a year like 2007.5 as a valid date (July 2007), which is handy.
- Optional Arguments remain optional. We didn"t use any in the above example where we defaulted DayCount and Periods in the function FStep simply by omitting them.
- If you have a one dimensional range, the range assignment such as Range("DestName")=SourceRange ( or the cashflow example above) results in a range which is in row form on the worksheet, i.e. horizontal. The same goes for array functions, below.
- If you need to generate a date that is a long for use with BF, consider using VBA"s DateSerial function.
Using Business Functions in VBA (Visual Basic for Applications)
|How to call Business Functions from VBA|
|You can call Business Functions from VBA using the Application.Run command (Excel 97 and above).|
The first argument is the name of the function, and subsequent arguments are as per the documentation for the function.
Note the following:
Array functions, and projections functions that you want to run in array mode, can also be invoked from VBA. The code is slightly changed, because this time an array of dates is being input to the function, and a variant containing the output array is being returned:
The same restrictions as before apply to assigning return values to a range - Excel prefers horizontal one-dimensional arrays.
BF"s Trace facility is available within VBA and is useful for debugging purposes - principally finding out why a function is returning an error. To use it you have to turn it on just before calling the function, and show the Trace window immediately after calling the function. You do this by first call CPPBF_TraceOn and then CPPBF_TraceShow.
When you are working in VBA, if a function does not work you will receive an error code, which may be only visible in the debugger. These are the Excel error codes: