Starting to learn VBA

For years, I’d wanted to learn VBA (Visual Basic for Applications) so that I could leverage more of what Excel, Access, and the whole Microsoft Suite have to offer.  Heck, I also wanted to learn more cool tricks so I could impress my colleagues.  But, like with many things in the academic/learning world, unless you have something specific to relate to, a real-life purpose or application for the material that you are learning, the learned “stuff” resides in your brain only temporarily.  So, it wasn’t until such real-world reasons for using VBA presented themselves that I started to really dig in.

Figure 1: VBA Startup Window

One such example of a problem is converting a name that is in all capitals (or all lowercase, for that matter) into the proper case, where only the first letter of each word (or middle initial) is capitalized.  We discussed this problem earlier in the context of Microsoft Excel in an earlier post.  Unfortunately, there is no Proper() function available in Access.  Enter VBA.

Now, when you first play with VBA, it can be intimidating.  For those of us used to the grid look and feel of Excel, the ‘coding’ window is unfamiliar.  But I promise you, once you begin to use it, you’ll wonder what you were ever afraid of.

First, let’s open the VBA Editor, where you will be doing your work.  You can open the VBA window in two ways that I know.  (If you know of more, please comment.

The two ways are:
Alt-F11 and Ctrl-G

Once opened, you’ll see a window like Figure 1.

Now is the fun (and easy part).  Again, learning how to code in VBA is not as big a hurdle as it seems.  Especially if you, like many of us, are just looking to find specific tools to help you work more efficiently.  But trying to get up to speed and knowledgeable quickly when you have dozens of other tasks to complete and deadlines to meet can present a huge challenge.  Well, do like I do and cut that time down to almost instantly by using all available resources.  For advanced help with questions, I use Experts Exchange (www.Experts-Exchange.com).  This is the most valuable tool in my toolbox right now. I use them for help with any and almost everything Excel or Access related.  Although it’s  a paid service, it is well worth it.  The Experts will help you look like a genius in the workplace.

Like most, I also use Google.  I’ll often check this route before heading down the road toward Experts Exchange.  For example, a search for”proper function equivalent in access” produced, among others, the following research result:

http://support.microsoft.com/kb/815282

Bam!  Exactly what I needed.

Next Step? First, open a new module (see Figure 2).  This is like a new sheet of paper on which you will write (or paste your code).  In this case, we’ll be pasting from the site mentioned above.  It’s that simple.

Figure 2: Opening and Pasting into VBA Editor

In this case, we wrote a function. You can also create subroutines (which I’ll get into in a later post). But functions are great because they are allowed in queries, forms, and reports just like they were one of the built-in functions such as iif, month, or isdate (I picked three random functions).

Figure 3: Custom Proper Function Usage

After pasting the code, the function is ready for use.  Simply use it like you would any other function. (Figure 3)