1. General Topics Home
2. Introduction
   1. Getting Started
   2. Using The Help File
   3. Top Dozen Functions
   4. Golden Rules
   5. Excel 2007
3. Menu Options
   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
4. Business Functions Date Arithmetic
   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
5. Techniques
   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
6. Worked Examples
   1. Introduction To the Worked Examples
   2. Daycount
   3. General Projections
   4. Business Plans
   5. Cashbasis And Periods
   6. Using Timebases
   7. Using Dates
7. How to ...
   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
8. Utilities - Information
   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
9. Utilities - Formatting
   1. Number Formats
   2. Apply BF"s Color Palette
   3. Bullets
   4. Color Cells
   5. Conditional Formats
10. Utilities - Validating
    1. Validation DropDowms
    2. Validation Standards
11. Utilities - Operations
    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
12. Customising Business Functions
    1. The ".ini" file
13. Resources On The Web
    1. Forum
    2. Online Help
    3. Templates
14. Appendix 1
    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
15. Appendix 2 Key Formulae
    1. NPV of Annual To Periodic CashFlows - CorrectionM
    2. Interest - Simple, Annual, Continous and Discount Factors
16. Appendix 3 New and Retired Functions
    1. New Functions
    2. Obsolete Functions
    3. Discontinued Functions
    4. Deprecated Functions

Using Business Functions in VBA (Visual Basic for Applications)

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

• 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.

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