Pseudo Object Oriented Programming with Office
All of the Microsoft Office family of applications have a built in scripting tool using Visual Basic. Though the name Visual Basic is applied, it’s really a subset of the more generalized tools available to a developer with Visual Studio and thus is referred to as VBA (Visual Basic for Applications). This allows some pretty powerful development and automation options across any of the Office suite programs. In this article, we’ll focus on the use of VBA in the realm of Microsoft Access.
Object Oriented Programming
There are of course whole books and courses of study on this concept, but I’ll sum up here. Object Oriented simply means that your programming is divided up into “Objects”. Each of these objects has its own scope, properties, events, and actions they can perform. The task for the programmer is to understand what object they’re working with, and what they can do with it. Here’s an overview of some of the key terms:
- Scope – This refers to where a variable, function, or subroutine is available. Typically they’re only available within the object they exist, and often only within the function or sub.
- Properties – This is a set of specifications for the object and contains things like dimensions, colors, data sources, values, etc. Each type of object will have its own specific list of properties that can be set and left alone, or manipulated with your program.
- Events – These are actions that are taken on an object, like it being opened , clicked on, updated, etc. Each type of object has a specific list of events, and every event can be used as a trigger to run a set of programming as a subroutine.
- Actions – or Methods are essentially built in tasks you can make an object perform. For example you can make a list re-query, or a drop down combo box drop down.
Why Pseudo?
The main thing separating a fully object oriented programming language from what you have in Office is the power to define your own objects, and the fact that the resulting program relies on the run time compiling performed by the Office program in order to work at all. You can’t build a stand-alone executable application with Office, your end users will have to have a licensed version of Office on their computer to use your program.
So while you are essentially object oriented while programming in Office, you are within the sandbox of Office do not have complete control of the objects you’re manipulating.
The Visual Basic Editor
The Microsoft Visual Basic editor inherent to all the Office applications is where you will spend your time. It’s actually quite good, and helps you along with locating your objects and figuring out what you can do with them.
On the left you have an explorer style list of your database project modules, which includes all your forms and reports that have been set to include a module. Below that by default there’s a property window (which I typically close, since the properties are visible from a floating window in the regular Access screen too). Then in the center right, there’s the main section which shows the actual code windows.
Below that code section , you can show a screen part called the Immediate Window. If it’s not there, hit ctrl+g or select View – Immediate Window from the menu. This is a really handy window and can be used to type in code and have Visual Basic immediately execute it. If your running something that returns a value, like asking for a property value or doing a calculation, just precede your code with a “?” to have it spit out the result on the next line. If you’re code has a problem and has stopped in debug mode or intentionally with a break point, you can use the immediate window to ask what the value of a variable is or even change them on the fly.
Object Browser
In the likely event that you’re not sure what you’re looking for, the Object Browser will let you search for it and display some key information, like require arguments etc. This will start you off on a path to figuring it out, and with an accompanying Google search and some time, you’ll be able to program anything.
Manipulating Properties
One of the simplest things to do with VBA is just changing the properties of an object. Typically this is done on forms, where you may want to show or hide something, change the source of something, or alter values. On a form, each text box, combo box, and anything else placed is called a control. The first thing you’ll have to do is figure out how to get to the control to manipulate.
When coding through an event on the form, your scope is within that form itself. So say we’ve got a command button, and have set the On Click event to [Event Procedure] in the properties. VBA will start you off a subroutine like this:
Private Sub Button1_Click()
End Sub
In between the sub and end sub statements, you can put whatever you like. So let’s say we have a text box on the same form that we want to manipulate. We can find that text box by typing in:
Me.textboxname
While you do this, you’ll notice that the editor will start listing available items every time you hit the “.” key. Also, note this line starts with “Me”, that’s because you are currently within the scope of that form, so starting a statement with “Me” automatically starts you along the list of items within the form you’re working on. After you get the name of the text box in there, hit another “.” and VB will display the properties and methods for that particular text box. By default for text, combo and list boxes, you’ll be updating or requesting the value. So these statements are interpreted the same by VB:
Me.TextBoxName.Value = ”Change it”
Me.TextBoxName = ”Change it”
Some of the other handy properties include these:
- Visible – Set to True or False in order to show or hide a control
- Enabled – Set to False and the control will grey out and not be accessible, though it’s still visible
- Locked – Set to True and the user will not be able to directly edit a value, even though you’ll still be able to with VB.
Calling Methods
Many controls have methods you can call to make stuff happen. The primary examples are data driven controls like a combo box or list box. These only get populated when the form is loaded so you’ll often need to update or refresh them with code on the fly. To refresh a combo or list box, simply call the requery method like this:
Me.ComboBox1.ReQuery
You can also create new methods that can be called by defining a subroutine in the form as “Public” rather than “Private”. This only works at the form level, but is effective. Once you make a sub Public, it will appear in the list when you hit “Me.” and scroll around.
Objects on other Forms
So far, we’ve worked within a single form. These same techniques apply to other forms as well by replacing “Me” with the form name in this format: “[Form_FormName]” Note that the form name will be exactly as it’s displayed on the left hand list of modules in the editor. From there you can do anything you could have done with a “Me”, with one important consideration. If the object you’re trying to manipulate isn’t open or is in design view, the code will fail and throw an error!
Variables
No programming language is complete without variables and VBA is no exception. Variables are required to store and manipulate values through the code. If you’re using a value to do multiple things later, you’ll want a variable. The simplest definition of which is this:
Dim Var1
That declares a variable called Var1 as the default data type, a Variant. Variants are handy because they can change on the fly to a string, number, Boolean, whatever. You may call it a particular type of data like this:
Dim Var1 as Double
Now that variable is a type of number called a double, which is a number with a decimal that can hold probably more data than you need. If you try to set this variable to a string like “blah!”, VB will throw an data type mismatch error. So once you set a data type, you’ll need to stick with it.
Conditional Statements
Without decisions, our code would be dumb, so many times we’ll need to include stuff like if this, then do that. In basic, these are pretty intuitive.
If Var1=”Test” then
‘Do something here
End If
These can get more complicated with the addition of an “Else” before the end if, or simpler by keeping the action on the same line as the “If” statement, eliminating the need for an “End If” statement.
An alternative, when testing multiple values, is the Select Statement
Select Case Var1
Case “Test 1”
‘Do something
Case “Test 2”
‘Do something else
Case Else
‘Do a third thing, whenever nothing else matches
End Select
With this statement you can check a variable for many conditions and trap when it fits none of them with the “Case Else” part.
Loops
Looping in code can become more complicated than I care to explain here, but the concept is super important for more advanced programming. You’ll find these in all programming languages, with the only difference in how they’re called and ended. The idea is to execute a set of statements over and over again until a condition is met. If you forget to provide the ending criteria, you’ll get stuck in the loop forever, or until you stop it with a ctrl+break.
There are several loop types, the one I typically use is the Do… While
Dim I as integer
I=1
Do While I<100
I=I*2
MsgBox “I is now “ & I
Loop
This little loop will start with the variable “I” as a 1, then multiply by 2 until it passes 100. So the value of I will change like this per loop:
- I=2
- I=4
- I=8
- I=16
- I=32
- I=64
- I=128
At which point the loop will stop because the condition of I<100 is no longer met. These can get complicated, but become really handy when moving through the records of a query or table for example.
Error Handling
Rest assured that errors will happen in your code. Nobody is immune to this, no matter how experienced you are. What separates bad code from good, is how well you deal with the errors.
Visual Basic offers the On Error code to tell it what to do when an error occurs. These can include a “GoTo” statement to point to a label in your code, or you can even just tell VB to ignore them with “OnError Resume Next”. For many reasons, just telling the code to go about its merry way is a bad idea, so we’ll start with a simple trap.
Public Function MyCode()
OnError GoTo Trap
‘Do whatever
Exit Function
Trap:
Msgbox “Oops! “ & err.description
End Function
That little bit of code will tell the function to proceed to the marker “Trap” when an error occurs. Then the trap will display a message box showing you the error description. Note the “Exit Function” statement right before the “Trap:” label, that is required or the function will always display the error message, even when there’s no error at all.
Using this format you can alert your users to common error modes, or take appropriate actions yourself. If you’re going to continue the code and want to trap again of more errors occur, you’ll want to get out like this:
Trap:
‘Error actions
Err.Clear
On Error GoTo Trap
Resume ReturnLabel
That way the error is cleared, the error handling is reset, and the program goes right back to a label called “ReturnLabel”.
Break Points and Stepping
While developing or debugging your code, these tools will help you see exactly what your program is doing along the way.
A breakpoint can be added by simply clicking to the left of the code window. A red dot will appear, indicating a break point. Multiple break points may be added if you like. When the code is executed, it will stop at that line. Once your there, you can check out variable values and try commands with the immediate window.
Up on the Debugging tool bar (right click and show this one if you don’t see it) are a pair of step buttons. Step Into will go to the next line of code, and if that line happens to be another function or subroutine, it will move into that and allow you to step through that one too. Step over moves to the next line as well, but instead of traveling into a function or sub, it will go ahead and execute it then continue down the code you’re currently working on.
These tools can be invaluable to see what your variables are doing and where you’re trying to do something Access doesn’t like.
Naming Problems
One thing Access never did very well for me is let me know when I named a function or sub the same thing as another built in function. Instead of calling out the conflict, like the full blown Visual Studio does, it simply begins to behave strangely. It’s best to avoid really common things, like calling a function “Date()”. And when in doubt, check the object browser for the name you intend to use.
Also, save your work often and keep a backup before major code changes. Sometimes your file can just get screwy, and leave you no choice but to roll back whatever it is you changed and try again using different techniques. I suspect that trouble isn’t limited to Access and VBA, but it’s potentially disastrous either way
Am I good to go?
Nope… there’s no way to write a quick post that can make you able to write full featured programs. This should be enough to make you dangerous though! Remember, Google is your friend. I’m not sure I could have completed much programming without Googling little problems here and there. Through taking time, trying bits of code, and looking at what others have done, you’ll be programming in no time.