The Working Class

Next we're going to build a class to hold the values for each customer survey. This class will also store the location of the database worksheet and it will perform a data save to the database.

In the VBE, add a new class module and name it cCustSurvey. Add the following modulelevel variables to hold the various properties:

Private

m

lngID As Long

Private

m

strState As String

Private

m

strPhone As String

Private

m

blnHeardOfProduct As Boolean

Private

m

blnWantsProduct As Boolean

Private

m

blnFollowup As Boolean

Private

m

xlWksht As Worksheet

Private

m

oXL As cExcelUtils

The first six items are simply the data we'll enter on our UserForm plus the ID field that we'll generate from the database worksheet. The m_xlWksht variable will hold the location of the database worksheet, which we'll need for a couple of things. The m_oXL variable is how we'll determine where to put any new data.

Let's add the data properties first, and then we'll get into adding some functionality to the class. Add the following properties to the cCustSurvey class module:

Property Get ID() As Long

ID = m_lngID End Property

Property Get State() As String

State = m_strState End Property

Property Let State(newState As String)

m_strState = newState End Property

Property Get PhoneNumber() As String

PhoneNumber = m_strPhone End Property

Property Let PhoneNumber(newPhoneNumber As String)

m_strPhone = newPhoneNumber End Property

Property Get HeardOfProduct() As Boolean HeardOfProduct = m_blnHeardOfProduct End Property

Property Let HeardOfProduct(newHeardOf As Boolean)

m_blnHeardOfProduct = newHeardOf End Property

Property Get WantsProduct() As Boolean

WantsProduct = m_blnWantsProduct End Property

Property Let WantsProduct(newWants As Boolean)

m_blnWantsProduct = newWants End Property

Property Get Followup() As Boolean

Followup = m_blnFollowup End Property

Property Let Followup(newFollowup As Boolean)

m_blnFollowup = newFollowup End Property

Property Get DBWorkSheet() As Worksheet

Set DBWorkSheet = m_xlWksht End Property

Property Set DBWorkSheet(newSheet As Worksheet)

Set m_xlWksht = newSheet End Property

Notice that our ID property has no Property Let method, so it's read-only. The ID will be pulled from the database as it's needed. Everything else is rather generic until we get to our DBWorksheet property. This is where we are storing the worksheet that contains our database and must be set before the class can work.

We're going to add a GetNextID method to find the last row, grab the value from the first column, and then increment it by 1. This function will set the ID property's internal variable so we can retrieve it from the class once it's set.

Add the following code to the cCustSurvey class module:

Public Function GetNextID() As Long Dim lngReturn As Long lngReturn = m_xlWksht.Cells(Rows.Count, 1).End(xlUp).Value + 1 m_lngID = lngReturn ' set the ID property GetNextID = lngReturn End Function

This code is very similar to the FindEmptyRow method in the cExcelUtils class, but it's returning a cell value instead of a row number.

Next, add initialization and cleanup code:

Private Sub Class_Initialize() Set m_oXL = New cExcelUtils End Sub

Private Sub Class_Terminate()

Set m_oXL = Nothing End Sub

Tip As mentioned in previous chapters, the Class_Initialize method is a great place to set up any internal objects used by your custom classes, and the Terminate method is the place to clean these objects up when you're finished using your class.

Now let's make this class do some work. First let's add some validation code. We cannot save the record if the State and PhoneNumber properties do not contain data. Add a new function called ValidateData and type in the following code:

Public Function ValidateData() As Boolean Dim blnReturn As Boolean

If (Len(Me.PhoneNumber & "") * Len(Me.State & "")) = 0 Then blnReturn = False Else blnReturn = True End If

ValidateData = blnReturn End Function

By multiplying the lengths of the text values State and PhoneNumber, we can determine whether one is missing, because the math will always return 0 if we're multiplying by 0.

Create a new function named Save that returns a success flag. This function needs to know the row number of the next available row for data entry; it needs to know what sheet that row is on; and if there are no errors, it must return a Boolean True value. Here is the code for the Save method:

Public Function Save() As Boolean Dim lngNewRowNum As Long Dim blnReturn As Boolean

If m_xlWksht Is Nothing Then blnReturn = False GoTo Exit_Function End If lngNewRowNum = m_oXL.FindEmptyRow(m_xlWksht)

With m_xlWksht

.Cells(lngNewRowNum, 1).Value = Me.ID

.Cells(lngNewRowNum, 2).Value = Me.State

.Cells(lngNewRowNum, 3).Value = Me.PhoneNumber

.Cells(lngNewRowNum, 4).Value = Me.HeardOfProduct

.Cells(lngNewRowNum, 5).Value = Me.WantsProduct

.Cells(lngNewRowNum, 6).Value = Me.Followup End With

If Err.Number = 0 Then blnReturn = True End If

Exit_Function: Save = blnReturn Exit Function End Function

The first thing we're doing is checking to make sure our worksheet object still exists.

If m_xlWksht Is Nothing Then 'double check that we still have a valid object blnReturn = False GoTo Exit_Function End If

If it doesn't, we return a False value and exit the function. Next we get our empty row location from our cExcelUtils object:

lngNewRowNum = m_oXL.FindEmptyRow(m_xlWksht)

Then we use the m_xlWksht variable that contains the database worksheet and populate each column in the row with data from our class properties:

With m_xlWksht

.Cells(lngNewRowNum, 1).Value = Me.ID

.Cells(lngNewRowNum, 2).Value = Me.State

.Cells(lngNewRowNum, 3).Value = Me.PhoneNumber

.Cells(lngNewRowNum, 4).Value = Me.HeardOfProduct

.Cells(lngNewRowNum, 5).Value = Me.WantsProduct

.Cells(lngNewRowNum, 6).Value = Me.Followup End With

Finally, we check that we have not received any errors, set the success flag to True, and then exit the function:

If Err.Number = 0 Then blnReturn = True End If

Exit_Function: Save = blnReturn Exit Function

That's it for the cCustSurvey class. We've just built a class to hold our input values from the UserForm. It will find the next ID value for any new records and it will save the data to the worksheet we pass into the class in the next free row.

0 0

Post a comment