Drewslair.com: Programming Microsoft Access

XI - Algorithms

It's good to know the commands that you can use in Visual Basic for Applications (VBA) and after you've worked with the language for awhile, you'll have a decent number of them memorized to the point where you won't have to rely on Intellisense. Keywords can be looked up in the help file or online, however, and after the basics that I've mentioned here, you pretty much learn them as you need them. As with most other things, it's about practice.

The other half of programming is knowing how to approach a problem and break it down into steps that can be coded with the right commands. That series of steps is what's referred to as an algorithm; the instructions that a method, a function or a combination of procedures will use in order to accomplish a task. You could compare it to a home repair project. The keywords and commands are the tools you will use and the algorithm is the instructions for installing a lock or replacing a light fixture.

The tricky thing about algorithms is that, as I've mentioned before, there often is no one set of instructions. Very often, it's up to the programmer to analyze the problem and find a good way to solve it in code. This applies on a small scale for individual tasks that involve one or two methods as well as on a large scale for entire projects where the developer must determine the best way he or she can to store data or translate a company's workflow into a series of entry forms and reports. This is why I consider programming to be an engineering discipline. Theoretical projects that you take on to see if something can be done or just to try out a new function can be done but the real challenges and substance come when the programmer has to apply his or her wealth of technical knowledge and experience to design a practical solution to meet someone's needs - often by a deadline.

I'm avoiding referring to "the best" or "the most efficient" solution because you probably won't find such a thing at first or even for awhile after you've been programming. Even after you have years of experience at this, there's always someone with more experience or just a fresh perspective who can come along and say "Why don't we do it this way?" and when you honestly look at their solution, you realize they're right. Programmers are also just as likely to second-guess each other as anybody else and there are as many egos among programmers as in any other field even though there's just as little room for them.

After you know the basics of VBA syntax (keywords, commands, structures, etc.) and understand some of the best practices such as commenting and error handling, the rest comes with the patience to approach a problem logically, the willingness to make a few mistakes and the understanding that things seldom work right the first time. That's why in later chapters, I'll talk about debugging the code so when things do break, they break on your desktop rather than the user's.

As for determining the algorithms for your code, it's an individual practice that can't be taught beyond looking at many examples of what works and what doesn't and lots of practice. In programming, as in many other fields such as writing, photography and music, it's important to sample the work of others to see how they do things. For VBA, I've found the Usenet group comp.databases.ms-access to be one of my most valuable resources over the years when I needed to know how to do something. This and other sites like it on the Internet are places where programmers can share knowledge and ask questions.

I've shared a number of examples throughout these articles which I would encourage you to analyze to see how I did things. Certainly, don't take this as the last word, though. The more sources you find for programming advice, the more you'll learn. Reading someone else's code can be an intimidating thing and I know at one time it was the last thing I wanted to do until I took a job where I had to do it all the time. Eventually I learned to do just what is done when solving a problem; break it down into pieces and take it a piece at a time.

In an earlier chapter, I included an example function that parsed the filename from a path reference supplied to it and I thought this would be a good example with which to show the basic steps of developing an algorithm.

1. Determine the need.

You have to understand what the need is before you can code for it. Sometimes, that means getting through layers of distractions and asking a lot of questions in order to help the users define it. In this case, it's simple -

In Windows and DOS, path references include folders and subfolders as well as the filename. For this exercise, we only want the filename and need a way to separate it from the rest of the string.

2. What are the rules?

As a programmer, one thing that you will often deal with are business rules. These are rules that define how the business must operate or the structure that the data must follow in order to satisfy the customer's needs. Issues addressed by business rules include:

How often or how many times product can be ordered by a specific business customer
Under what conditions a discount can be applied
How long data must be retained
What type of notifications must be sent to a customer
What type of payments can be accepted and what data is required for payment

The business rules for a project can affect all aspects of the application, from what tools are used to design it and the type of security that's applied down to the design of specific methods and functions.

This example is a smaller task so the rules are simple.

1. Everything to the right of the last backslash in the path reference is to be treated as the filename.

2. The code must make this filename available for use by other code.

Notice there's no mention here of how the code is supposed to do that or how it is to make the value available. Business rules are supposed to specify what is to be done, not how to do it. The second rule actually teeters on the edge of this limitation but from the perspective of this project, I'm allowing it. This is an important issue and the line between those two things can get fuzzy sometimes but remembering this can save a lot of confusion between the programmer and the business.

3. Determine the steps.

Now we're getting to the fun part. For different projects, you might ask some of these questions:

Where is the data coming from and in what format?
Do I need to store or transmit the data? How?
What entry forms might be needed to satisfy the rules and what type of controls would work best?
What queries will I need to create to get the information I need?
What tasks can be broken out into their own functions and should those functions be available to other projects?

These are just a few of the questions you will learn to ask as you work on projects and, when programming professionally, you could even have the unique pleasure of writing the answers in the form of a technical specification for review prior to coding.

In this case, we're just parsing a string so here are the basic rules:

High-level (summary):
Create a function that will accept the path reference as a string parameter, extract the filename from it and return the filename as a string.

Low-level (algorithm):

Determine the length of the string.

Use the length as the limit in a For ... Next loop with which to examine each character starting at the end of the string.

When a folder indicator ("\") is found, return the portion of the string after that point as the filename.

4. Write the code and test.

Public Function GetFileName(PathReference As String) As String

'Declare and set variables
Dim PathLength As Integer
Dim Counter As Integer
Dim NameOfFile As String, FileChar As String

NameOfFile = ""
PathLength = Len(PathReference)

'Parse PathReference and return file name at the end.

For Counter = PathLength To 1 Step -1
FileChar = Mid(PathReference, Counter, 1)
If FileChar <> "\" Then
NameOfFile = FileChar & NameOfFile
Else
Exit For
End If
Next Counter

GetFileName = NameOfFile

End Function

Even after years of programming, I do not write code from top to bottom. In this example, I remember starting with the most obvious feature, the For loop and then remembering "Oh yeah, I'll need a counter variable for that." and going back up a few lines to declare the variable and while I was there I might have thought to declare an integer variable to hold the path length. After I'd set down the first draft of the function, I remembered to insert a line setting a string variable to a blank string for safety before anything else was done to it. Then there were error corrections, ad hoc changes, etc...

After I finished writing and testing the code and found it to work, I decided to change a few things to make it more efficient.

Public Function GetFileName(PathReference As String) As String

'Declare and set variables
Dim PathLength As Integer
Dim Counter As Integer
Dim NameOfFile As String

PathLength = Len(PathReference)

'Parse PathReference and return file name at the end.
For Counter = PathLength To 1 Step -1
If Mid(PathReference, Counter, 1) = "\" Then
    'Take everything to the right of the character
    'as the filename.
    NameOfFile = Right(PathReference, (PathLength - Counter))
    Exit For
End If
Next Counter

GetFileName = NameOfFile

End Function

You can see more about this function in the section on For ... Next loops. There were two versions because after I wrote the first draft, tested it and found it to work, I looked at the code and thought "I really made that a lot harder than it needed to be." and went back to simplify it. There's no shame in admitting that your code can be optimized further. This goes back to what I said about solutions rarely being right the first time.

If you'd like an exercise that will demonstrate how many ways there are to do one thing in VBA, you could redesign this function yourself. There's another way to find the last backslash in the string using VBAs InStr() function which you can find more information on in the Access help files. Have fun!

5. Test some more.

Seriously - throw everything you can at your code and try every way you can imagine to break it. I know it's tedious and it looks like it works so why tempt fate, right? Trust me; finding a bug in your code and fixing it before it goes to the customer can feel a bit like having someone shoot at you and miss but it's better than what you feel when the customer finds the bug. VBA also offers some good tools for testing code so there's really no good reason not to do it.

There have also been times when I've spent hours, or even a day or three, trying to find the right approach to a problem. Sometimes, it happens because you don't understand something as well as you think you do or because you've gotten so wrapped up in the problem that you can't see straight anymore and need to walk away from it for a bit. Sometimes, showing it to another programmer and asking for advice helps because programming, like other fields where knowledge is the main tool, is best when done through collaboration with others.

Back to Series Index