Naming Standards for Functions

In a good IDE (Integrated Development Environment; see Wikipedia for more details) like the one MS Access has, life can be quite easy. You can compile and the IDE will warn you of variables and function references that are incorrect.

However, if you have a lot of code (I have done apps with 10,000 or more lines) it can be challenging looking for function calls. Subroutines are easy, since the word “Call” always proceeds a use. With functions, it is not so easy. Compare the following examples of a sub call and a function call.

  • Call SomeSubroutine(“123”)
  • if SomeFunction(“123”) = “abc” Then

Next, compare a possible function call and a reference to an array term.

  • y = SomeFunction(3)
  • y = SomeArray(3)

If you need to make a change to a function (adding another parameter is a good example) you will need to change every reference to that function in your code. If you are not careful in the naming of your functions, this can present a problem finding them all.

More common to me is when reading code, am I looking at an array reference, or a function reference?

  • TotalValue = ValueOfGoods(2)

Is ValueOfGoods an array variable, or a function?

Naming standards will save you.

For variables, including arrays, I usually use a prefix indicating the data type.

  • strNames(3) represents a string variable
  • curValueOfGoods(3) represents a currency variable
  • basValueOfGoods(3) represents a function (bas = basic code) within the form
  • gbasValueOfGoods(3) represents a function at the global (Modules) level, so that any form can call it

This page has an exhaustive list of prefixes. Their style is a bit different than mine, but the point is to find one that works for you and use it.

Enhancing a Recorded VBA Macro in Excel

I know. It is not MS Access. But it is VBA and I bet a lot of you use Excel as well. AND, it was fun to do.

This video series shows me recording a very basic VBA macro in Excel, and then modifying it to make it more complex. Excel is great for recording a macro, but you have to add things like loops manually. This series is mainly about the loop.

VBA Settings – Part 2

This time we will look at how to colour code our VBA programming. I like to colour my comment lines with green text. Variables are red in my code.

Here is one of my 3 minute videos showing how I do it.

VBA Settings – Part 1

There are a variety of settings in MS Access that you will customize over time. This one, inside the VBA editor, is one of the most important. Requiring Variable Declaration is a real time saver, and gets rid of lots of “bugs” before they even get started.

Using the Immediate Window

The Immediate Window in MS Access is a great way to test the use of a function before using that function in a query or in VBA code. You start using the Immediate Window by pressing Ctrl-G on your keyboard.

Examples used in the video are:
? 5*8
? now
? dlookup(“[CourseName]”,”Courses”,”CourseID=2″)
? dlookup(“[StartDate]”,”Courses”,”CourseName=’Basket Weaving 101′”)
? dlookup(“[CourseName]”,”Courses”,”StartDate=#Jan 26, 2015#”)