Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    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.

    For example:

    Sub BFVBA()
    Dim FromDates As Range, CashFlows(0 To 11), p%, Base#
    Dim AnnualRates(1 To 3), StartDate As Long, CurrentDate As Long, Finish As Long
    Set FromDates = Range("FromDates")
    "Note: FromDates is a named range in the active workbook with 3 dates in it
    AnnualRates(1) = 10
    AnnualRates(2) = 20
    AnnualRates(3) = 30
    Base = 3
    Finish = 2007
    StartDate = DateSerial(2002, 1, 1)
    For p = 0 To 11
    CurrentDate = Application.Run("DpM", StartDate, p * Base)
    CashFlows(p) = Application.Run("Fstep", CurrentDate, Base, Finish, FromDates, AnnualRates)
    Next p
    Range("CashFlows") = CashFlows
    "Note: "CashFlows" is a named range of 12 cells in a ROW in the current workbook and it gets updated by this function
    End Sub

    Note the following:
    • 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.

    Array Functions

    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:

    Sub BFVBA()
    Dim FromDates As Range, CashFlows, p%, Base#
    Dim AnnualRates(1 To 3), StartDate As Long, MyDates(0 To 11) As Long, Finish As Long
    Set FromDates = Range("FromDates")
    AnnualRates(1) = 10
    AnnualRates(2) = 20
    AnnualRates(3) = 30
    Base = 3
    Finish = 2007
    StartDate = DateSerial(2002, 1, 1)
    For p = 0 To 11
    MyDates(p) = Application.Run("DpM", StartDate, p * Base)
    Next p
    CashFlows = Application.Run("Fstep", MyDates, Base, Finish, FromDates, AnnualRates)
    Range("CashFlows") = CashFlows
    End Sub

    The same restrictions as before apply to assigning return values to a range - Excel prefers horizontal one-dimensional arrays.

    Trace Facility

    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.

    For example:

    Application.Run "CPPBF_TraceOn"
    CurrentDate = Application.Run("DpM", 2002,12)
    Application.Run "CPPBF_TraceShow"

    Error messages

    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:
    ConstantError numberCell error value

    Visual Basic is a trademark of Microsoft Corporation

    Business Functions Ltd, London, UK Website Design: Webpure