This is an intensive course in applying Visual Basic to Financial Applications.
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:
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.
This is an intermediate through to advanced level course.
The course covers two days.
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).
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 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 Integrated Development Environment (IDE) is the “Workbench” that lets you develop, debug and run Visual Basic applications.
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.
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.
Controls are items like checkboxes and listboxes that can be added to worksheets to make them user-friendlier.
Forms allow you to extend the range of interfaces and interactions the user experiences when they use your applications.
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.
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.
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.
This section reviews principles of good design.
This section illustrates how Office applications can communicate and integrate by using Visual Basic.
This section demonstrates how Visual Basic can work with a range of database servers.
XML is one of a family of languages designed to transport and store data. This section shows how Visual Basic can work with XML.