Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
Up until now, I have been showing you how to record and modify macros to bring data into a spreadsheet, format it, display it in a pivot table, and chart it. While this method works, it leaves some housekeeping problems to be solved everytime you do it. Enough.
This post starts us on the path of creating a library of functions to “snap together” and (in a very few cases) modify to quickly produce our final product, ready for delivery to our waiting customers (Users).
The first function, however, isn’t a function (sorry). It’s a template for all other functions. We’ll get this, and some theory, out of the way and then get right into building our library. Here is the template:
'Use this to create your own functions. The basic idea is everything that isn't 'tied to a button should be a function. Functions can be called like Subroutines 'but can also be checked for Success or Failure return codes if needed.
'1) Replace every instance of "Template" to your routine's name '2) Change the "As Boolean" if your function returns something other than ' Success or Failure
Function Template() As Boolean
' Routine Name: Description of Routine
' Parameters: None ' Example: bResult = Template(Parm1, Parm2)
' Date Init Modification ' 01/01/01 CWH Initial Programming
On Error GoTo ErrHandler ' Template = Failure 'Assume the Worst
' Begin code here
' End code here Template = Success 'Successful finish
ErrHandler: If Err.Number <> 0 Then MsgBox _ "Template - Error#" & Err.Number & vbCrLf & Err.Description, _ vbCritical, "Error", Err.HelpFile, Err.HelpContext ' Begin additional error cleanup processing code here On Error Resume Next 'Remove this if no additional error cleanup processing ' End additional error cleanup processing code here On Error GoTo 0
The above template uses two user defined global constants: Success and Failure. These constants provide more meaningful results for return codes than just True or False. To define these constants and make them available throughout your project, you must add this tiny bit of code at the top of your module:
'General Spreadsheet Routines
Global Const Success = False Global Const Failure = True
This includes a comment to identify the version of your module. Any time you change your module and save it, it’s a good idea to identify the version using the date.
I have also included a brief description of our library “General Spreadsheet Routines“.
The Option Explicit is a preference of mine. It tells the VBA compiler not to accept any variables that haven’t been defined. I use this because I make mistakes and I’d rather the compiler tell me when I have forgotten to properly define a variable than have my customers call me about a bug in my code. If you don’t make mistakes, then by all means, remove this line – it’s useless otherwise.
Now – back to the template. As you can see, there’s not much to it. It’s just a shell with some error handling and places to put standard documention.
It’s been my experience that most coders like to see a few things on any routine they might have to work on that they didn’t create themselves. They want:
- A brief description of the routine (why it’s important)
- Defninitions for the parameters (if any)
- An example of how to use the routine
- A list of guilty parties, when they committed their crimes, and what the heck they were thinking
Like I said, I make mistakes. Sometimes, so does XL/Windows. That’s why error handling is critical. You hope your users never experience an error, but if they do, they better never be plopped into your code. They won’t know what to do and you’ll get a frustrated user’s call. Only coders should see code. So to make sure that happens, every routine should have error handling, and the first thing every routine does is to turn it on with “On Error GoTo ErrHandler”.
The second thing is to provide your routine with a default end state. Most often, that’s Failure so unless your routine gets all the way to the end, it will accurately return Failure, not Success, and give the calling routine a chance to deal with it.
Many coders like to put an “Exit Function” just before the error handling routine. I don’t. I think it’s confusing. Instead, I test for errors (If Err.Number <> 0 ). If there are any, I leverage VBA’s error descriptions and present the user with an alert with whatever text VBA provides. Hopefully, I’m the only one seeing these alerts during my testing. In that case, the descriptions help greatly. In any case, an alert is far better for the end user than being dumped into code.
In almost all cases, the error handling routines in the template work without modification, except for changing the name “Template” in the line “”Template – Error#” & Err.Number & vbCrLf & Err.Description, “.
To use the template, copy it, paste it, search and replace all instances of “Template” to your routine’s name, fix the documentation, and add code where designated.
Next posts: WorkSheetExists, PivotTableExists, ChartExists routines followed by: Settings; Setup_Pivot; Setup_PivotChart; and finally, the Pivot_Template, the only routine in this bunch you’ll need to modify to easily make pivot tables and charts for your data. Stay tuned.