Of Excel Data and Object Orientation

Earlier in this book, I promised that we'd see object-oriented solutions to our coding problems in Excel 2007. Let's take our manager list-creation code and the code that lists a manager's staff, and convert them to classes. Normally, this is the way I would directly approach a solution, but up to this point we've been exploring some of the VBA possibilities in Excel 2007. Open DataAccessSample04.xlsm and save it as DataAccessSample06.xlsm. Open Modulel in the VBE and review the GetManagerList subroutine. We can break its functionality down to just a few items. The problem with that is it's doing a number of unrelated things. It's setting up the worksheet for data import, opening a connection to the database, getting data, putting it on the worksheet, and then formatting and cleaning up the worksheet.

When we build our objects, we will pay strict attention to the separation of functionality. The rule of thumb is that objects should do only one well-defined job. Of course there are exceptions, but if you plan carefully, you will develop objects that provide a clearly defined set of methods and properties, providing a focused set of functionality.

The first thing we're doing in our original code is setting up the worksheet by activating it and then clearing a contiguous region in preparation for importing our data:

Set xlSheet = Sheets("Sheet2")

xlSheet.Activate

Range("Al").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range( "Al").Select

Then we're instantiating and opening a connection to our data: Set cnn = New ADODB.Connection sConnString = "Provider=SQLNCLI;Server=MYSERVERNAME\SQLEXPRESS;" * & "Database=AdventureWorks;Trusted_Connection=yes;"

cnn.Open sConnString

Next, we get our data into an ADO recordset and place it on our worksheet:

sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName," * & " Person.Contact.LastName FROM Person.Contact" * & " INNER JOIN HumanResources.Employee" *

& " ON Person.Contact.ContactID = HumanResources.Employee.ContactID" * & " WHERE (((HumanResources.Employee.EmployeeID) In" * & " (SELECT HumanResources.Employee.ManagerID" * & " FROM HumanResources.Employee)));"

Set rs = New ADODB.Recordset rs.Open sSQL, cnn, adOpenDynamic

Sheets("Sheet2").Activate Range("Al").CopyFromRecordset rs

And finally, we do a quick bit of formatting the sheet by using the AutoFit command to resize the data columns:

xlSheet.Select Range( "Al").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Range( "Al").Select

These are four simple units of functionality that we can provide in a very generic and reusable object-oriented solution.

In the VBE, add a new class module and name it cData. Add a second new class module and name it cExcelSetup. These will contain the code that will provide all of the functionality provided in our standard code module.

Let's work with cExcelSetup first, and create an object that can provide our worksheet setup and cleanup functionality.

Add three module-level variables:

Private m_xlSheet As Worksheet Private m_rngInitialCellSelect As Range Private m_rngDataRegionStart As Range

These are the private variables that will hold key property values for us. Next we'll create read/write properties to set and retrieve our property values:

Public Property Get Worksheet() As Worksheet

Set Worksheet = m_xlSheet End Property

Public Property Set Worksheet(newSheet As Worksheet)

Set m_xlSheet = newSheet End Property

Public Property Get InitialCellSelection() As Range

Set InitialCellSelection = m_rngInitialCellSelect End Property

Public Property Set InitialCellSelection(newCell As Range)

Set m_rngInitialCellSelect = newCell End Property

Public Property Get DataRegionStart() As Range Set DataRegionStart = m_rngDataRegionStart End Property

Public Property Set DataRegionStart(newCellAddress As Range)

Set m_rngDataRegionStart = newCellAddress End Property

The GetInitialCellSelection and DataRegionStart properties both return Range objects. We'll be using the GetInitialCellSelection property to determine where our cursor will be after our code runs. The DataRegionStart property sets and returns the cell that begins our data region. This is used when we clear the sheet at the start of our procedures and when we perform our autofit during cleanup.

Even though we've got Property Get and Set functions for these two properties, we're going to create an initialization function that allows us to set them both at once. This give us the advantage of using less client code to accomplish the task of setting two properties, yet gives us the flexibility of using the property settings directly if we need to.

Public Sub SetKeyCells(InitialCell As Range, DataRegionStart As Range) Set m_rngInitialCellSelect = InitialCell Set m_rngDataRegionStart = DataRegionStart End Sub

Now that we've got our key properties laid out, we can concentrate on adding our setup and cleanup code.

Add a new subroutine called SetupWorksheet, and add the following code:

Public Sub SetupWorksheet() Me.Worksheet.Activate ClearRegion

Me.InitialCellSelection.Select End Sub

This code corresponds to our original code from our standard module:

Set xlSheet = Sheets("Sheet2")

xlSheet.Activate

Range("A1").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range( "A1").Select

The first and last lines of the SetupWorksheet routine correspond to the first and last lines of our original code. There is a call to a private method called ClearRegion that does the work of the remaining original code:

Private Sub ClearRegion() m_xlSheet.Activate Me.DataRegionStart.Activate Selection.CurrentRegion.Select Selection.ClearContents End Sub

Add one last function to do our autofit cell formatting, and clean up the worksheet:

Public Sub DoAutoFit() Me.Worksheet.Select Me.DataRegionStart.Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Me.InitialCellSelection.Select End Sub

By now, I'm sure you've noticed that this code is very similar to the original code in our standard module. The major difference is that rather than referring to specific cells, we are using internal class properties such as Me.DataRegionStart.Select.

That's all there is to our cExcelSetup class. Let's create our cData class to populate our worksheet with data. Add the following private module-level variables:

Private m_cnn As ADODB.Connection Private m_rs As ADODB.Recordset Private m_sConnString As String Private m_sSQL As String

These are the same tools we've been using all along to connect to our data and return sets of data from the AdventureWorks database. We're going to create properties to hold our connection string and SQL statement. We'll also create methods (functions) to open and close our ADO connections and recordsets.

Add the following Property Get/Lets:

Public Property Get ConnectString() As String

ConnectString = m_sConnString End Property

Public Property Let m_sConnString = End Property

Public Property Get

SQL = m_sSQL End Property

Public Property Let m_sSQL = newSQL End Property

Next we are going to add methods to open and close our ADO Connection object:

Function OpenConnection()

If m_sConnString <> "" Then m_cnn.Open m_sConnString

Else

MsgBox "Cannot open connection", vbOKOnly, "cData: OpenConnection Error" End If End Function

ConnectString(newString As String) newString

SQL() As String SQL(newSQL As String)

Function CloseConnection()

m_cnn.Close End Function

Note that the OpenConnection method is using the private variable m_sConnString to return the connection string to the AdventureWorks database.

Next we'll create a new function called GetData and add the following code:

Function GetData() As ADODB.Recordset m_rs.Open m_sSQL, m_cnn, adOpenDynamic

Set GetData = m_rs End Function

This function returns a dataset based on an SQL statement passed in from the private variable m_sSQL, and uses the private connection object to connect to the database. In reality, this is a very simplistic method. In the real world, we would probably add arguments or properties for the cursor type, location, and other key settings, but for our example this will suffice.

Our last order of business for this class is setting its initialization and termination methods. It is good practice to initialize any internal objects and data variables, and the Class_Initialize method is the place to do it. When using internal objects like the ADO objects, using the Class_Terminate method allows us a place to clean them up when the object goes out of scope in our client code.

Private Sub Class_Initialize() m_sConnString = "" m_sSOL = ""

Set m_cnn = New ADODB.Connection Set m_rs = New ADODB.Recordset Set m_prm = New ADODB.Parameter Set m_cmd = New ADODB.Command End Sub

Private Sub Class_Terminate() Set m_cnn = Nothing Set m_rs = Nothing Set m_prm = Nothing Set m_cmd = Nothing End Sub

Let's take a look at both classes in their entirety (shown in Listings 2-7 and 2-8). Then we'll create client code to use these objects and compare them to the original code in Module1.

Listing 2-7. cExcelSetup Class Code Option Explicit

Private m_xlSheet As Worksheet Private m_rngInitialCellSelect As Range Private m_rngDataRegionStart As Range

Public Property Get Worksheet() As Worksheet

Set Worksheet = m_xlSheet End Property

Public Property Set Worksheet(newSheet As Worksheet)

Set m_xlSheet = newSheet End Property

Public Property Get InitialCellSelection() As Range

Set InitialCellSelection = m_rngInitialCellSelect End Property

Public Property Set InitialCellSelection(newCell As Range)

Set m_rngInitialCellSelect = newCell End Property

Public Property Get DataRegionStart() As Range Set DataRegionStart = m_rngDataRegionStart End Property

Public Property Set DataRegionStart(newCellAddress As Range)

Set m_rngDataRegionStart = newCellAddress End Property

Public Sub SetKeyCells(InitialCell As Range, DataRegionStart As Range) Set m_rngInitialCellSelect = InitialCell Set m_rngDataRegionStart = DataRegionStart End Sub

Public Sub SetupWorksheet() Me.Worksheet.Activate ClearRegion

Me.InitialCellSelection.Select End Sub

Private Sub ClearRegion() m_xlSheet.Activate Me.DataRegionStart.Activate Selection.CurrentRegion.Select Selection.ClearContents End Sub

Public Sub DoAutoFit() Me.Worksheet.Select Me.DataRegionStart.Select Selection.CurrentRegion.Select Selection.Columns.AutoFit Me.InitialCellSelection.Select End Sub

Listing 2-8. cData Class Code Option Explicit

Private m_cnn As ADODB.Connection Private m_rs As ADODB.Recordset Private m_sConnString As String Private m_sSQL As String

Public Property Get ConnectString() As String

ConnectString = m_sConnString End Property

Public Property Let ConnectString(newString As String)

m_sConnString = newString End Property

Public Property Get SQL() As String

SQL = m_sSQL End Property

Public Property Let SQL(newSQL As String)

m_sSQL = newSQL End Property

Function OpenConnection()

If m_sConnString <> "" Then m_cnn.Open m_sConnString

Else

MsgBox "Cannot open connection", vbOKOnly, "cData: OpenConnection Error" End If End Function

Function CloseConnection()

m_cnn.Close End Function

Function GetData() As ADODB.Recordset m_rs.Open m_sSQL, m_cnn, adOpenDynamic

Set GetData = m_rs End Function

Private Sub Class_Initialize() m_sConnString = "" m_sSQL = ""

Set m_cnn = New ADODB.Connection Set m_rs = New ADODB.Recordset End Sub

Private Sub Class_Terminate() Set m_cnn = Nothing Set m_rs = Nothing End Sub

0 0

Post a comment