Visual Basic for Finance



This is an intensive two day course in applying Visual Basic to Financial Applications. During the course you will learn how to:

  • Automate routine work and save the time and trouble of doing the same thing over and over
  • Increase work efficiency by making shortcuts for common tasks
  • Customize Microsoft Office products to your needs by extending or changing those products' functionality
  • Integrate workflows across Microsoft Office applications
  • Increase the "wow-factor" of your developed applications
  • About
  • Facilitator
  • Outline
  • Content
  • Assessment
  • Register

About the course

Microsoft Office products like Microsoft Excel, Word, Outlook and PowerPoint contain a powerful but often underutilized resource - Visual Basic.

Visual Basic is part of most Microsoft Office products and it gives you the power to:

  • Automate routine work and save the time and trouble of doing the same thing over and over
  • Increase work efficiency by making shortcuts for common tasks
  • Customize Microsoft Office products to your needs by extending or changing those products' functionality
  • Integrate workflows across Microsoft Office applications
  • Increase the "wow-factor" of your developed applications

This course shows how to achieve these benefits. You will receive:

  • Intensive two day training in Visual Basic
  • Examples showing how Visual Basic is applied to common finance needs
  • Reference guides that you can take away and refer to
  • Full solutions to all of the exercises

About the facilitator

This course's facilitator is Alex Palfi. Alex had ten years of experience with Macquarie Group in presenting finance courses internationally. He has presented courses in financial modelling, spreadsheet skills, visual basic, valuation, and option pricing and risk management in London, New York, Los Angeles, Tokyo, Hong Kong, Taipei, Singapore, Thailand, South Africa, Brazil, Australia & New Zealand.

Alex has worked in Investment banking, commercial banking and software development industries. Alex's university qualifications are in engineering. He obtained Masters and Bachelors degrees in Electrical Engineering from the University of Canterbury in New Zealand.

Audience

This course's intended audience is Microsoft Excel / Microsoft Office "Power Users" who wish to exploit the power and potential of Visual Basic in finance.

Level

This is an intermediate through to advanced level course.

Duration

The course covers two days.

Prerequisites

You will need Excel skills at an intermediate level at least. You should be able to use formulae, create charts and understand how cells are referenced (e.g. $A1:$B5).

Format

The course has a "hands-on" format. Each participant works with a laptop for the major part of the course on practical Visual Basic topics.

Following is an overview of this course's content.

Macros and Add-Ins

Macros are a central part of Visual Basic. A macro is a set of actions within an Office Application. The action can be relatively simple: To print a page; or it can be complex: For example to extract information from an Excel workbook and put a summary into a Word document. Macros can be "recorded" and "played back". Or they can be written. Or a combination of the two. This section introduces Macros: How they are created, where they can be put and how they can be shared. Macro security is also covered.

  • The Personal.xls macro workbook - its purpose and function
  • Recording keyboard shortcuts
  • Running macros from keyboard shortcuts
  • Modifying keyboard shortcuts
  • Changing a macro's name
  • Moving your Personal.xls from one PC to another
  • Running macros from the main Excel menu
  • Running macros from custom menu items
  • Sharing macro workbooks - menu items
  • The auto_open macro
  • Creating an Add-In
  • Making an Add-In load automatically
  • Updating an Add-In
  • Password protecting an Add-In
  • Disabling an Add-In
  • Macro and Add-In security
Using the Visual Basic Development Environment (IDE)

The Integrated Development Environment (IDE) is the "Workbench" that lets you develop, debug and run Visual Basic applications.

  • Introduction to the IDE
  • Developing code
  • Setting breakpoints

  • Single stepping
  • Setting a watch
Programming

Whilst macro recording is a very useful way of developing some Visual Basic applications other applications need to be written or programmed. This section shows how to program in Visual Basic.

  • Overview of variables
  • Variable names
  • Variable types
  • Variable default values
  • Overview of statements
  • Several statements on one line
  • One statement on several lines
  • If statement
  • Iif statement
  • Select Case statement
  • For statement
  • For Each statement
  • While statement
  • Do While statement
  • Do Until statement
  • Do Loop statement
  • Calling functions and subroutines
  • Arguments - passing by reference
  • Arguments - passing by value
  • Exit Sub and Exit Function statement
  • On Error statement
  • On Error Resume Next statement
  • On Error GoTo statement
  • Arrays
  • Collections
  • Joining strings
  • Splitting strings
  • Left function
  • Right function
  • Mid function
  • Len function
  • Worksheet functions
The Object model

The object model is a way of referring to individual parts of an Office application. At the "top" of the model is the application itself (e.g. Excel, Word, Outlook, PowerPoint). The application will be composed of Menus and Toolbars, Documents (in Word), Workbooks (in Excel), Slides (in PowerPoint), user-defined settings (e.g. header, footer) and so on. These parts will in turn be composed of sub-parts. The object model is a way of representing and working with the hierarchy of objects that make up a complete application.

  • Overview of the Object model
  • Object model syntax
  • Object model - intellisense walkthrough
  • Properties and methods
  • Recording macros to learn about the Object model
  • Range object
  • Rows property
  • Columns property
  • Cells property
  • End property
  • Offset property
  • Selection property
Controls

Controls are items like checkboxes and listboxes that can be added to worksheets to make them user-friendlier.

  • Overview of controls
  • Control events
  • Reading control state
  • Setting control state

  • Button control
  • Checkbox control
  • Dropdown control
Forms

Forms allow you to extend the range of interfaces and interactions the user experiences when they use your applications.

  • Overview of Forms
  • Message boxes
  • GetOpenFile dialog
  • SaveFileAs dialog
  • FileDialog dialog
  • User forms
  • Addressing controls in User forms
Functions

Along with macros functions are a very important type of Visual Basic code. Functions provide features that macros don't. Functions, for example, are useable in Excel formulae whereas macros aren't. This section describes what functions are and how they can be created and used.

  • Function arguments
  • Function evaluation
  • Function return values
  • Defining a function
  • Range arguments
  • Optional arguments
  • Returning arrays
  • Limitations of functions
Events

When something happens in an Office Application that is an event. Events cause the application to take an action. Visual Basic allows you to "intercept" events and change the behaviour that the application would otherwise take. This lets you extend and customise behaviours to suit your purposes.

  • Overview of events
  • Workbook events
  • SheetSelectionChange event
  • BeforeSave event
  • Worksheet events
  • FollowHyperlink event
  • Applications of events
Classes

In Visual Basic "class" has a similar meaning to the term "type". Members of the same class all have things in common. Members of an "employee" class, for example, may have a name, an address and an employee id. Members of an "asset" class might have an asset id and an asset description.

  • Relationship between Classes, types and objects
  • Class interfaces
  • Encapsulating behaviours
  • Public and private access
Design Principles

This section reviews principles of good design.

  • Minimise unnecessary dependencies
  • Use a naming convention
  • Keep procedures short
  • Keep scope small
  • Make private where possible
  • One exit point per procedure
  • Use Option Explicit
  • Test your work
Office Integration

This section illustrates how Office applications can communicate and integrate by using Visual Basic.

  • Type and object libraries
  • References to object libraries
  • Object libraries and portability
  • Late binding
  • Early binding
  • Excel / Word integration
  • PowerPoint / Excel integration
  • Excel / Internet Explore integration
  • Visual Basic / Outlook integration
Assessment

You can do a free on-line assessment of your Visual Basic skills before registering on the course. The assessment comprises a series of questions on topics covered in the course.

The assessment will give you a good insight as to the range and depth of topics covered.

If you subsequently attend the course and re-do the assessment your score should rise to near 100%.

Click here to do the assessment. Your results will be emailed to you within approximately one minute after you complete the assessment.

Register

You can register online for any of our courses by selecting the "Register" menu item at the top of this page.

Website by Astronaut Designs