Visual Basic for Finance workshop
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:
- 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
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
Do a Visual Basic self-assessment
See an example from the workshop of Visual Basic used in spreadsheets