Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
In our last post we looked at running a query that accepts user input to determine the result set loaded into XL. I want to emphasize that although we used an Access database as our source, these same tools and processes work with Oracle, SQL Server, AS/400/iSeries/IBM i/Power i/System i (however you prefer to call it) databases and many others. If you need help connecting to your database, post a comment with what you’re trying to connect to and I’ll try to address it or I’ll point you to good resources.
There are lots of opportunities for improvement on that last post including adding summaries, charts, filters, sorts, etc. The first improvement I’d like to make is to include the clearing of any previous query into Macro 1 and thereby, eliminate one more step. Using the spreadsheet from before (if you didn’t save it, just recreate it using the prior post’s instructions. It only takes a few minutes and it’s good practice), edit Macro 1 (use Alt-F11 to bring up the VBE). Add this line just before the first line of code (but after the Sub Macro1() line):
This line is equivalent to right clicking the “Select All” box and selecting Clear Contents (If you turned on the Macro Recorder and performed those actions you’d get this line of code in a new subroutine). If you’re new to VBA, this will probably look strange to you so let me break it down.
VBA manipulates XL’s objects . One of the most common objects in an XL spreadsheet is a Range. A Range object can contain one or more cells. Cells is a special Range object that contains ALL cells in a spreadsheet. Cells is the XL object in that line of code.
VBA can invoke XL’s object methods. Methods are actions that are predefined and exposed (made available to us) for a given object. To invoke any of an object’s methods, use the syntax: <Object>.<Method>. ClearContents is the Cells’ method in that line of code.
VBA can read XL’s object properties. To read an object’s property into a variable use the syntax: <Variable> = <Object>.<Property>. Example: One of the Cells object’s properties is AllowEdit. AllowEdit indicates if the range can be edited on a protected worksheet. So to read the Cells’ AllowEdit property into variable A, write:
A = Cells.Address
VBA can set some of XL’s object properties. To set an object’s property use the syntax: <Object>.<Property> = <Value>. Example: One of the Cells object’s properties is Value. Value is the contents of Cells. So to set the Cells’ Value property to “A”, write:
Cells.Value = “A”
Include the Cells.ClearContents line in your Macro 1 and then re-run it using Alt-F8 as explained in the prior post and observer the results. Congratulations, you’ve just reduced the amount of steps needed to retrieve different results.