At some point, you may find it necessary to use VBA to get your job within a database done. Also, there are cases where some databases model a complex set of business rules and requirements and you must use VBA generously.
The most important thing to understand is that there are no truly hard and fast rules regarding how to be a good programmer. However, since the time of vacuum tubes, several programmers have observed patterns and thus contributed to a set of guidelines that help other programmers, regardless of whether they are using C, COBOL, Java, Visual Basic, or any one of many programming languages available.
This article deals in generalities and a bit of programming theory. The overall objective is to provide a maturing programmer with a framework to work within when considering how they will implement solutions to the problem at hand. Thus, the article is intended to call your attention to *potential* best practices, but it is still *your* responsibility to decide whether you will apply these recommendations or not.
Have hammer, will see nails
The first thing to consider is a caveat: VBA is truly a wonderful way to extend your application's functionality, and it can be tempting to turn to it for problems for which there exists a better solution without need for VBA. There's a saying- if your only tool is a hammer, everything starts to look like a nail. Thus, just because it's possible in VBA, it doesn't mean you should do it in VBA. SQL has its niches, and so does Access's built-in functionality and bound forms/reports/controls. Therefore, you may want to make a point of asking yourself what is the right tool for the job before swinging away.
Naming Conventions & Reserved Words
- Naming Conventions & Reserved Words
- Option Explicit and Variable Declaration
- Data Types do matter
- Scope of Action
- Another Meaning of Scope
- Compile early and compile often and test your code just as often
- How do I code this?
- Using Constants, Functions, and Properties
- Eight atomic procedures are better than one big sub
- Using class modules to maximize reusability and reduce work
Our first step is to decide on a naming convention. It actually doesn't matter what you ultimately decide on. The real criteria here is that this is a convention you are able to stick with throughout the life of project. It has failed as soon as you stray away from the convention because you found something more convenient.
The most commonly used convention among Access developers is Leszynski-Reddick style
. That should give you an idea of what is usually included in such conventions.
Furthermore, you should be aware of 'Reserved Words'; it may seem intuitive to name a field storing dates "Date", but how would Access know if you're talking about a field named "Date" or a function named "Date"? This kind of naming can create confusion and unpredictable behavior. Generally, if there's a function, object, or property that has a name, it's a reserved word and thus should be avoided (or at least prefixed/suffixed).
Here's a list of reserved words
that will serve as a good starting point and give you a sense of what constitutes a reserved word.
Option Explicit and Variable Declaration
The single thing that will do the most to help you to code efficiently is to require variable declaration for everything.
This can be set by Tools -> Options. On the dialog, select Editor tab and check "Require Variable Declaration"
This will automatically insert a line at top of every module:
This protects you from accidentally creating extra variables that you did not intend. For example,
Public Sub TryMe()
Dim st As String
sr = "foo"
Without the option explicit, we would end up with two variables, st
which is never set, while sr
now has value of "foo" which should have been set for the variable st
. With Option Explicit, this would raise an error and thus call your attention to the troublesome variable which you can then take steps to correct.
Another benefit of using Option Explicit is that it helps to reinforce the good habit of specifying a data type explicitly. The default data type for any variable is Variant when we do not specify a type. While variant has its uses, it is relatively expensive because VBA has to resolve the type every time you use a variant.
Data Types do matter
Dim x 'This will be initialized implicitly as a variant. Not good.
Dim y As Integer 'Better now we have an explicit data type
As alluded to earlier, it is important to be very precise in what data type you use. Each data type has its costs in terms of memory consumption and time to process that data type.
Generally, it is best to use the smallest data type that fits your needs. For instance, an Integer data type will serve you well if you do not need a number less than -32768 or greater than 32767.
Dim obj As Object 'May be iffy
Dim wrksht As Excel.Worksheet 'This is more explicit
Declaring a generic Object data type is also expensive because, like the Variant data type, VBA has to resolve the capability of data type Object before it can use it. For programmers who actually need late binding
, this is a legitimate use.
The bottom line is that you need to know various data types that are available to you and be explicit when defining variables. This also works for disambiguating different libraries. Suppose we used both ADO and DAO (a problem area for those using Access 2000, 2002, and 2003 with MS flip-flopping between the libraries). There is nothing wrong with using them both as long we disambiguate:
Dim drst As DAO.Recordset
Dim arst As ADODB.Recordset
There is one more point to remember when working with data types- it's easy to end up with several different types when you think you're using just one. This operation actually involves two data types:
Dim dtMyDate As Date
dtMyDate = "2008-31-10"
Which has two data types, but VBA will automatically coerce the string to a date for you, but this may not be always what you want. Therefore, there are instances where you will have problem with data types. It will be useful for you to remember that not everything is what it seems to be. This is especially true when you are dealing with objects such as controls or recordsets where you may set various data type to its various properties; being careless can have unintended side effects.
Furthermore, if it happens that you have two different data types but need to perform operations with both, it's usually a good idea to be explicit in casting them.
Scope of Action
MyString = MyInt + MyDbl*
'How does VBA know if you wanted a Double stored as a String or an Integer stored as a String?
MyString = Cstr(CDbl(MyInt) + MyDbl)
'This explicitly tells VBA that you want your Integer cast to Double, added to another Double, then cast as a String.
One simple concept becomes incredibly important all too soon when dealing with VBA. That concept is "scope of action" - which in programming terms, means - how far-reaching is this program's operation? How much do you want it to do? How much data will it have to "touch" to accomplish its goal?
At one end of the spectrum, we can talk about a bit of VBA code that is associated with a single control on a single form, with the intent of making the control's background pale green when a number is positive or pink when the number is negative. This is a very simple code requirement that probably has the smallest possible scope of action. In VBA terms, the hardest part here is knowing where to put the code, which will probably not exceed five or six lines including the IF statement and its two main branches - one for the positive case and one for the negative case.
At the other end of the spectrum, VBA can read a directory, open a file, control a loop that reads records, import data to a table via Recordset operations, and update a progress bar on a display while ALL of the above is going on. Or it can open an Excel Workbook to read various cells. It can open a Word document to perform searches and statistical operations. It can do all of these at once.
With this incredible range of action, VBA can be your friend for all sizes of problems. However, there are times when VBA is more of a hindrance than a help. For beginners, the temptation is prevalent to write some VBA code to convert some complex data from one format to another. However, an update query can often do this without any VBA code at all. OR you can write the VBA code as a function that could then be used by a query. That means that the data conversion would be in VBA code but record navigation would be done via SQL.
Therefore, one useful skill is to know whether some action is or is not within the range of actions available within VBA. It is recommended that any person wanting to solve a problem with VBA should first know other aspects of Access and SQL syntax. VBA can and will help you "re-invent wheels." This sometimes leads to a glut of wheels, which isn't necessarily a good thing.
Another Meaning of Scope
VBA actions can have implied scope. However, the VBA routines themselves also potentially have a specific scope in terms of "visibility." The visibility of a VBA function or subroutine depends on where it is defined and who can call it (activate it). Scope of visibility, in simplest terms, is "who/what can see it?"
Access supports two types of VBA (code) modules. These are the standard modules and the class modules. A standard module is accessible from an object shown in the Module tab of the database window. Code in a general module could be activated from any form or report you have defined.
Forms and reports have class modules. All private procedures in a class module is visible only from the object associated with that module, and public procedure are accessible only when the object is open. A closed form doesn't exist in the list of forms. (It is, however, in the "Documents" or "AllForms" collection. But that's a concept for another lesson.)
A common mistake is to define a function in a class module for form A and expect form B to be able to use that function. If you want to reuse a function in multiple forms, it must be stored in a general module. This, too, is a practical application of the concept of "scope."
One fine point: The scope of action for a piece of VBA code does not have to match the scope of visibility for that code. But some authorities suggest that the two should be related by your choice of module design.
Therefore, an issue to consider when you are thinking about using VBA is to decide HOW you want to use it - from many places or one – and how much you want to do with it - narrow scope of action or wide-open scope of action. "Ambitious" is a word that accurately describes a novice VBA programmer wishing to write his/her first VBA subroutine to have a wide scope of action from a class module.
So when you are contemplating VBA as a solution, ask yourself two questions. (1) Who/what will need to do this? (2) To what must this be done when run from each of the possible answers to #1? THEN and ONLY THEN are you ready to think about the steps required to implement a VBA solution to your problem.
Compile early and compile often and test your code just as often
The biggest favor you can do to yourself is to make a habit of compiling your code frequently. This aids the process of debugging because you confine yourself to only last few lines you added since the last successful compilation.
Likewise, you definitely want to get in habit of testing your code often. By focusing on writing one procedure at a time and testing it, you can make the process simpler. Of course, this is not always possible, so discretion is required in determining what constitutes smallest block of code that can be meaningfully tested.
This is especially true with "Object or With variable not set
" or "Too few parameters; expected 1
" and similar run time errors. Run time errors are those not caught by the compiler, which only looks at syntax errors. Thus, it may be not always clear what triggered the error in first place. You do not want to see that error after having written a hundred lines of code without having had compiled & tested the code.
How do I code this?
A common situation that crops up even for most experienced programmers is they have a process that they haven't quite coded for and don't know the appropriate methods, properties, or statements to execute the statement. When you consider that Windows APIs collectively can have 60,000 different procedures, it should become apparent that even the C programmer has to know how to get the data he needs to program, just as much as we need to programming in VBA.
Therefore, a good habit is to get quite familiar with help files. I even reference help files for several methods that I've already used just to make sure I am aware of all possible side effects of using those methods. At this point, I should note that for Access 97, help files is quite extensive, but not as in 2000 & 2003. In case of 2003, I actually find that using search from the Visual Basic Editor to get the appropriate information is more effective than using the search in Access UI.
Another useful tool is to use Object Browser (can be called by pressing F2 or Views -> Object Browser), which is very useful for a quick perusal of different libraries, objects contained inside each of libraries and what arguments they take and/or return.
Also, it bears repeating that for any problem you are having right now, someone had it before you and in all probability already solved it, so it is usually a matter of searching effectively on various websites. Sometimes they are contained in whitepapers, technical documentation, and/or kb/msdn articles provided by Microsoft. Sometimes they are just a post on a bulletin board. The greater length you are willing to go through to read the fine print and test it out, the better programmer you will be.
Finally, there are times where it's just beneficial to test for yourself instead of running on a wild goose chase or waiting for an MVP to swoop in and rescue you or even if you just plain don't understand. This also implies that your debugging skills should be honed so you can be fully informed when examining the result of the test case. Tips on debugging is beyond the scope of this paper but there are several resources detailing different tools used in debugging.
Using Constants, Functions, and Properties
A rule of thumb is that whenever you have a magic number, a formula or something that exemplifies your business rules, it belongs as a constant, function, and/or property of a module.
For magic numbers such as Pi of a circle, Euler's constant 'e
', or anything that will never change ever, constants makes the most sense. (If you need a magic number to be stored persistently but not immutable, the lookup table may be more appropriate.)
Const Pi As Double = 3.14159
With numbers that you use as a part of business rules... say, calculating tax rates, functions are more appropriate.
Public Function TaxRate(SubTotal As Decimal) As Decimal
TaxRate = SubTotal + (SubTotal * CurrentTaxRate)
Note that the 'CurrentTaxRate' isn't defined in that function- that is because it is up to you to decide the best place to store such information. Perhaps querying a table holding history of all tax rates is what your business requires. Or the business may be content having one tax rate that is updated periodically. Regardless of how you decide to implement this, functions are big time-savers.
A prime example is when you need to use calculated controls. It's easy to say "Why write a function when I can just do it all in a calculated control with the control source set to "=SubTotal + (SubTotal * CurrentTaxRate)"?
Well, that is true. Now say that you have 100 of them. Now you've been told that you must charge a shipping & handling fee after the tax. Now you could go and change every one of 100 calculated controls, or you could just go to the function and change it once and all the 100 controls will work as expected. Your decision.
Ideally, your programs should be a bunch of black boxes that can be strung up with a given string so you do not have to go through the arduous process of figuring out which one line causes an error in a giant main function (and there are plenty of errors that will raise on one line but is actually caused by a bad line that could be several lines earlier).
Furthermore, by far the biggest advantage of using functions (and subroutines as well) in a standard module is that it's incredibly easy to debug. You do not even need to open a form to debug it. You do not need to run the application and test it as if you were an end user to debug it. You only need to run it either by clicking the play button or calling it from immediate window (the small window at the bottom of the Visual Basic Editors):
Result will be printed in the immediate window
Property is like Public variables (aka Globals) but is much more versatile and provides you with more control over how it can be manipulated.
A good example would be when you want to use a listbox and its rowsource needs to be changed for every record. Instead of referring to the querydef and recordset every time you fire the OnCurrent event of the form, you can just make it a property and actually concentrate on modifying the parameters. This is what it would look like in the form's module:
Private rst As DAO.Recordset
Property Get qdf() As QueryDef
'MyQuery is a Parameter Query with the SQL:
'SELECT a FROM table WHERE b = [lngPrimaryKey];
Set qdf = CurrentDb.QueryDefs("MyQuery")
Private Sub Form_OnCurrent()
qdf.Parameters = Me.MyPrimaryKey 'Set the parameter
Me.MyListBox.Recordset = qdf.OpenRecordset
Note that qdf is a read-only property. You can refer to it from everywhere within the form's module (actually, from anywhere as properties are public by default but can be set private if so desired), and if you need to change the name, there's only one place to do it. Everything else will just work™.
Eight atomic procedures are better than one big sub
A common mistake for beginning programmers is to beam with pride over their one-hundred line subroutine. Rarely, if ever, should any procedure should be that many lines. This usually results because you try to wrap a process into a single procedure, when each procedure should in theory represent single task.
It's much easier to write out a procedure that does only one thing but does it well, and then pipe several simple-minded procedures together to generate a complex output. In fact, that is how programmers have been doing it for decades. Suppose we had a process that required us to extract a set of data, format it into something, then export it out. It would be easier to write one sub:
Public Sub Foo()
But this is better for a production environment:
Public Sub foo()
Public Function Extract(MyData As Arguments)
Public Function Format(MyData As Arguments)
Public Function Export(MyData As Arguments)
Now, a case can be made that that the second approach is more work than first one because you now have to write four procedures instead of one. However, by writing four smaller procedures, you significantly simplify your debugging process because you only need to know that your function works as expected then forget it. Furthermore, down the road, you may discover that you needed a similar task for a different process... Well, golly, it's a function so it's ready to go! Increasing reusability of your code will make you more productive.
Also, it's easier to go back five years later and re-read the atomic functions and understand what they do, rather than reading the big sub and wondering what the heck you did there. So in this sense, the second one leads itself to better documentation without requiring you to write comments as would certainly be required in first example.
Using class modules to maximize reusability and reduce work
One common task that crops frequently is automating Excel and manipulating its spreadsheet. Thus, it's not uncommon to see code for initializing such automation such as this:
Private Sub foo()
Dim objExcel As New Excel.Application
Dim objWorkBook As Excel.WorkBook
Dim objWorkSheet As Excel.WorkSheet
Set objWorkBook = Excel.OpenWorkbook("MyWorkBook")
Set objWorksheet = objWorkBook.Worksheets(0)
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
Whew, that was a lot of work managing several objects, especially if all we wanted to do was to export a bunch of data in a specific worksheet for same workbook. And then multiply that by every time we need to create a new procedure to deal with different aspects (e.g. maybe to read off a worksheet for example, or to add new worksheet or whatever).
Some may use public variables to help resolve this issue but that has its own set of problems. One good example is if you set a public variable to this worksheet but later refer to it thinking it was set for that worksheet, you have a problem.
A Class module is a perfect solution in this circumstance. You only need to declare one line:
Dim MySpreadSheet As New MyExcelClass
And the class module will run through the steps to initialize the application for you, retrieve the workbook and worksheet for you from this single line. Depending on what methods and properties you give to a class module you can even make it as simple as:
In which the method AppendNewData (written by you) already knows the correct position to place the new data, so you only need to write the class module once, then just refer to the methods of that class module. Intellisense (that tiny scrolldown menu that pops up to help you complete code) also will work for your custom class modules.
If you find yourself needing classes; Here's a primer.
Generally, your code should aim to be highly cohesive
yet loosely coupled.
Whenever you turn to VBA to fill in the niches to meet your business requirements, it is possible for you to start out with a generic procedure that encapsulates the task at hand. When you have a rough draft, it become more apparent how you can refactor
your code into black boxes that are 1) easy to use, 2) can be used for several different tasks, 3) are self-documenting or self-evident. Developing those habits will serve you well in long time.
Last thing. Whenever a solution seems out of reach, or the light bulb above your head turns on, no matter how brilliant and crystal clear it is, you want to first ask yourself: Who else has had this problem or task in front of them, and how did they solved it? Their solution is just a search away. By doing this, a solution can probably be found to your insurmountable problem, or if others solved similar tasks differently than you had planned to, then maybe your planned solution is not the best way to approach your task. Paraphrasing AWF's geoergedwilkinson: "People are very clever in their ability to twist VBA or Access to do easy things in the hardest way possible". A critical eye is what saved me from running down a long alley only to hit a wall or re-inventing unnecessarily complicated wheel when there already exist a nice & simple wheel.