VBA

Monitor page
for changes

What is VBA?

Visual Basic for Applications (VBA) is a high level computer programming language that Microsoft developed to control (initially) their Office Suite of Applications - Word, Excel, Powerpoint, Access. It first appeared (I believe) in Excel 5.0. Soon, it spread to Microsoft Project and even non Microsoft programs like Visio, AutoCAD, WordPerfect and so on - Microsoft was/is quite willing to sell this to anyone as an automation language.

Most of the time, we don't use VBA alone - we use it to control an Automation Server (i.e. Word, Excel etc..). You need to store your source code somewhere, so VBA has to be hosted by something (e.g. in Access Modules) and it is particularly intimate and sensitive to it's host. VBA can control another Automation Server (e.g. Word) quite easily.

Non programmers often misquote VBA, they call it VB (Visual Basic). Actually, VB is a superset of VBA which Microsoft sells as a standalone package. It can be used alone or it can be used to control the usual Automation Servers.

VBA has it's roots in VB that has it's roots in Word Basic that has it's roots in VB/DOS that has it's roots in Microsoft Basic that came with the early IBM PCs that has it's roots in Microsoft Basic Compiler that was sold as a language for the Add-On CPM/80 Softcard for the Apple ][ that had it's roots in Dartmouth BASIC.

What are the language features of VBA?

VBA is a casually typed language - this is from it's ancestry. In the current environment, Microsoft exhorts you to enforce data types and declarations with Option Explicit. You declare variables with the Dim statement.

Aside from variable, constant and compiler declarations, the bulk of your code would be in chunks called procedures - either Subs or Functions. Advanced and long term programmers may develop Class modules - but most people get by with just Standalone or Form/Report modules - see MSDN: Microsoft Access 2000: Class, Form and Report Modules

Within these Procedures you would:

carry out variable assignment and calculations using + - / * & operators.

conditional branching with if...then...else and select case

looping with do while and for...next

It makes sense to use VBA's built-in functions rather than re-invent the wheel and create your own. There are math ones like abs, string handling ones like mid, date handling ones like datepart and even financial ones like ddb. Not to mention display formatting ones like format$ and data type conversion ones.

In professional work, it is very important to have enough Error Trapping.

Interacting with Automation Servers

For Access, you would often use the Application, DoCmd, RunCommand, Form, Reports and Me objects.

For DAO, you would be very interested in the Recordset object.

Although VBA and DAO will allow you to take bits of data from tables and querys and, you be very limited in manipulating data if you do not have knowledge of SQL - DAO by itself will open a table or query for you, but moving through records using MoveNext and checking them against using conditional operators ( =, <> etc..) would be very slow.

It is always more effective and efficient to use SQL Expressions and retrieve only the records that you want. One thing that ties up novices in knots is how to embed SQL expressions into VBA strings and how to ensure that Contorls (which may hold criteria) are evaluated and passed as part of the SQL string.

For Automating Word or Excel, you have a choice of Early Binding or Late Binding. Early Binding requires that you add a reference to the relevant object library in VBA Editor, Tools, References. Late Binding requires you to use GetObject - be careful of the syntax and what is optional and what is not. Also save your file before testing (don't you do that always?)

What is VBA?

What are the language features of VBA?

Interacting with Automation Servers

Interesting Bits