Thursday, November 03, 2005

Excel VBA Macros

Microsoft recieves a lot of critcism for its inclusion of Visual Basic for Applications in many of its programs. This introduced us to the horrible menace of Macro Virii. However, there is an actual legitimate use for VBA as well. Lets consider this example. I will look at a request that I consistently get, which is to determine if a list of employees is active or not. The list can be anywhere from 100 to 10,000 employees. Obviously in the latter case, going through by hand to confirm their employment status is unfeasible. This is where VBA in Excel can come in handy.

The following Excel VBA macro will prompt the user for the Source Column, then prompt the user for the destination column, run the query for all rows and return the employees active status in the destination column. It utilizes ADO to retrieve the results from an Oracle Database. (Note: there are more efficiency tweaks that could be applied to this function to speed up the retrieval via ADO, however the function in its current form will suffice for 99 percent of all queries I use. I will leave these up to the reader to discover more optimized methods).

Sub get_ActiveStatus()
     'Replace with sql statement to use. Be sure the return columns is called "Status" for this query
     Const SQL_TO_USE As String = "SELECT 'X' Status FROM employees WHERE cd_emp_type <> 'X' AND no_emp = ?"
     'Define the row to start on to take into account header rows
     Const START_ROW As Integer = 2
     'The database connection string, change for each database used
     Const DATABASE_CONNECTION_STRING As String = "Provider=MSDAORA.1;Password=cxxxxxx;User ID=cxxx;Data Source=ts;Persist Security Info=True"

     'x is a generic counter, then there are the 3 objects used in ADO
     Dim x As Integer
     Dim con As New ADODB.Connection
     Dim com As New ADODB.Command
     Dim rec As ADODB.Recordset
     'Source and destination columns
     Dim srcColumn As String
     Dim dstColumn As String
     'Variable to store the number of rows to process
     Dim LastRow As Integer

     'Get the number of rows in this worksheet by using Excels internal countA function
     'I use this instead of looking for blank cells because this has shown itself to be more accurate
     If WorksheetFunction.CountA(Cells) > 0 Then
          LastRow = Cells.Find(What:="*", After:=[A1], _
          SearchOrder:=xlByRows, _
     End If

     'If there are no rows, exit out of the function
     If LastRow = 0 Then
          MsgBox "There are no rows in this worksheet", vbOKOnly
          Exit Sub
     End If

     'Set up the connections using the database string defined above
     con.ConnectionString = DATABASE_CONNECTION_STRING
     con.CursorLocation = adUseClient
     con.Mode = adModeRead

     'Set up our command object to do a prepared query using out constant defined above
     com.CommandType = adCmdText
     com.Prepared = True
     com.CommandText = SQL_TO_USE
     com.Parameters.Append com.CreateParameter("EmpID", adVarChar, adParamInput, 50)
     Set com.ActiveConnection = con

     'Prompt the user for the source column containint employee IDs and the destination
     'column indicating that the employee is active
     srcColumn = InputBox("Enter the Source Column: ")
     dstColumn = InputBox("Enter the Destination Column: ")

     'Set our generic counter to the start row defined in the constants section
     x = START_ROW

     'Loop from our starting row to the last row, on error use the general exception handler
     On Error GoTo Error_Occured
     For x = START_ROW To LastRow
          'set the employee ID to look up as the source column and row and execute query
          com("EmpID") = Trim(Range(Trim(srcColumn) & Trim(Str(x))).Value)
          Set rec = com.Execute

          'If there are records returned, set the destination to the returned result
          If (rec.RecordCount > 0) Then
               Range(Trim(dstColumn) & Trim(Str(x))).Value = rec("Status")
          End If

          'Set the recordset to nothing
          Set rec = Nothing

          'Do any background events, such as processing keypresses or switch windows

     'Close and clear all objects
     Set com = Nothing
     Set con = Nothing
Exit Sub

'The general exception handler
     'close the database connection and clear objects
     Set com = Nothing
     Set con = Nothing
     'Let the user know there is a problem
     MsgBox "An error occured with processing the macro", vbCritical
End Sub

What I would like to do now is have these macros available to me anytime I need them, however I do not want them to appear in Excel files I send out. So I will set up a personal file that will open each time I open Excel. These steps apply to Excel 2000.

First, open up Excel and create a new worksheet.

In your new sheet, delete the tabs for “Sheet 2” and “Sheet 3”.

Then go up to Window, and choose Hide.

Then Go to Tools, Macros, and Visual Basic Editor.

In the Visual Basic Editor, you can see that your new Worksheet is still available for Macro editing. We will create a new Module to house our Macros. So, right mouse click on the name of your worksheet, and choose Insert, and Module.

Under the property editor, name your Module whatever name you feel is appropriate. I have chosen “ExcelMacros” here.

Now paste the Macro into the modules code window. Since this Macro makes use of the Microsoft ActiveX data object, we need to add it as a reference. To do this, Tools, References, and select Microsoft ActiveX Data Objects from the list of libraries.

Once completed, choose File and Save. You will get a File Explorer dialog. Here, navigate to my user profiles section for Excel, which is at “C:\Documents and Settings\jward\ Application Data\Microsoft\Excel\XLSTART\” and I will save the file as “PersonalMacros.XLS”.

Once I exit out of Excel and reenter, I go back to the VBA editor, and I see that my personal macros is still there. The only drawback to this approach is that when I open Excel, I do not get a blank worksheet, which is a sacrifice I am willing to make.

I am lazy by nature, so I do not want to have to open up the Macro editor each time I want to run this script, so I will create a button on a toolbar for easy access. First, go to File and New to create a blank Worksheet. We won’t do anything with this worksheet, however Excel will not allow us to assign a Macro to a button unless a Worksheet is open and visible.

First, I right mouse click in the tool bar area and choose customize.

I then choose the toolbar tab, click the New button, and enter the name of my new Toolbar as “Personal Macros”.

By default, the new toolbars are already visible. With the Customize dialog box still open, I go over to the Commands tab, scroll down to under the Categories list box to Macros, and drag the Custom Menu Item over to my new toolbar.

I right mouse click on the toolbars “Custom Menu Item” to get the properties dialog, and I change the name to “Get Active Employee Status”.

I then right mouse click again, and choose “Assign Macro”.

When the dialog opens up, I can now choose the macro I created.

And that’s it. Now I can see this macro in action. I open up a document with Employee ID’s to test. I click on my button and am greeted with my dialog box asking for the Source Column.

Another dialog box prompting me for the destination column pops up, which I fill in. Once I hit OK, I can watch as the macro fills in my values for me.

You can see where this is a time saver. Almost all objects in Microsoft Office, and a lot in Windows are accessible through VBA. This is one of the reasons that Visual Basic is such a popular language. However, in some cases, it allows a little too much power for an office suite. In fact, I’ve seen things as far out as Port Scanners written in VBA. This is also one of the reasons that Microsoft receives so much criticism for VBA inclusion in so many Microsoft products. Many say this is a vendor lock in tactic, however I take it for what it is, a time saver in many cases. I have dozens of simple macros based off this one that allow me to very easily meet customers request for information at the click of a button. Incidentally, OpenOffice Calc program has in very recent versions incorporated a similar Macro language that I intend to look into. While not as easy to develop in as VBA, I am sure it is every bit just as powerful, if not more.

No comments: