Financial modelling skills assessment
Try our on-line assessment
Home Register Contact About Us

  Visual Basic for Finance workshop


Links

This is an intensive course in applying Visual Basic to Financial Applications.

About this 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:

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

Outline

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.

Content

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

Databases

This section demonstrates how Visual Basic can work with a range of database servers.

  • ActiveX Data Objects (ADO)
  • Using a spreadsheet database
  • Using a Microsoft SQL server database
  • Creating a Database on an SQL-compliant server
  • Using an Access database
  • Using an open source database (MySql)
  • Creating an Access Database

XML

XML is one of a family of languages designed to transport and store data. This section shows how Visual Basic can work with XML.

  • Microsoft's COM XML parser
  • MSXML
  • Parsing an XML file
  • Exporting as XML

Links


Sitemap | Terms of use | Privacy | Contact us
- 18.206.194.21 -