Learning Visual Basic for Excel
My lovely wife has a rather large project on her hands. She’s trying to get an unwieldy set of Excel workbooks under control. It has become clear to her that she will need Excel forms with data validation and limited choices for some columns. I agreed to undertake the task of learning Visual Basic for Excel.
Warning: if you have no experience writing any kind of computer code or script, my recommendations do not apply to you. I wrote my first program in Fortran in 1967. I’m gradually adapting to the object model. Let’s just say these resources are not for beginners.
Let me say at the outset that I’m doing most of my learning using Excel 2011 for the Mac. VBA for that version of Excel is buggy — barely beta test quality. Luckily we have one Windows 7 computer with Excel 2013 which is the production machine. I have Excel 2007 on my ancient Sony notebook (still running XP). I’ll put up a separate article about the bugs in the 2011 version. There’s also a short note about what I’ve found so far at the end of this piece. Today I’m accenting the positive.
I’ve found two resources that are very valuable. For questions about your code and debugging, StackOverflow.com has a set of discussion boards organized by “tags” instead of the usual hierarchical structure. That means if you know all or part of the tag, you just type the word into the forums and a list of tags that have that word will pop up. (Click the image to see a larger picture.)
Like most technical forums, StackOverflow works best if you register. There is a rating system that will be useful if you plan to participate frequently. If you’re asking for help, be sure to have either a concrete example or some VBA code. The folks who answer questions are not going to write all your software for you.
As for learning Excel VBA, the best resource I’ve found is Cynthia Brown’s channel on Youtube. At least that’s where I found her. It turned out that Ms. Brown teaches CS 106 (Visual Basic for Excel) at Portland State University. Her web page for that class includes PowerPoint presentations, many Excel examples, land a few Word files. She has also self-published a book on Amazon.com ($2.99 available only as a Kindle edition). Some of the code listings in the e-book are small and difficult to read on Kindle for the Mac. Mysteriously, some code is very readable, while other blocks are, um, difficult:
Which is why you’ll want the Excel examples, PowerPoint files, and, especially, the Word files that list the video files used in each segment. Ms. Brown writes very structured code. Her videos, however, are titled rather haphazardly. There are a lot of them, so trying to find a topic using the video title will be difficult. At the end of each major section on the course website there is a Word file with a link titled “Video Links for Part <n> (docx).” These files contain the actual URLs of the videos in question. Copy and paste into your browser’s address bar and you’re good to go.
To order Cynthia Brown’s Book click the obvious link.
You will need all of these resources. The information is presented differently in each medium. Ms. Brown does most of her work using Excel 2010 for Windows. If you’re using Excel 2011 for the Mac and something that works for her goes wrong, it’s probably a bug in VBA for the Mac. I’ve discovered two fairly significant issues so far — FormatCurrency() includes a blank space instead of the decimal point and background colors in some buttons don’t seem to work in at least one block of VBA code. If you’re in a 100 percent Apple environment, consider VMware Fusion to run Windows in a virtual machine on your Mac.
Between Cynthia Brown’s detailed instructions and help at StackOverflow you should be good to learn VBA for Excel.