Purpose of classes

[tiredofit]

My workbook contains a userform and data on Sheet1 as shown:

The code is as follows:


This is in Userform1:

Option Explicit

    Dim LastRow As Integer

Private Sub cmdCancel_Click()

    Unload Me

End Sub

Private Sub cmdNew_Click()

    Dim iAnswer As Integer

    If Me.txtState <> vbNullString Or Me.txtPhone <> vbNullString Then

        iAnswer = MsgBox("There is unsaved data. Do you want to continue?", vbYesNo, "Unsaved Data")

        If iAnswer = vbYes Then

             Call ClearForm

        End If

    Else

        Call ClearForm

    End If

End Sub

Private Sub cmdSave_Click()

    If Me.txtState = vbNullString Or Me.txtPhone = vbNullString Then

        MsgBox "Can't save"

    Else

        Call SaveData
        Call ClearForm

    End If

End Sub

Private Sub SaveData()

    Dim EmptyRow As Integer

    EmptyRow = LastRow + 1

    With Sheet1

        .Cells(EmptyRow, 1).Value = Me.lblID.Caption
        .Cells(EmptyRow, 2).Value = Me.txtState.Value
        .Cells(EmptyRow, 3).Value = Me.txtPhone.Value
        .Cells(EmptyRow, 4).Value = Me.chkHeard.Value
        .Cells(EmptyRow, 5).Value = Me.chkInterested.Value
        .Cells(EmptyRow, 6).Value = Me.chkFollowup.Value

    End With

    Call UserForm_Initialize

End Sub

Private Sub ClearForm()

    With Me

        .txtPhone.Value = vbNullString
        .txtState.Value = vbNullString
        .chkHeard.Value = False
        .chkInterested.Value = False
        .chkFollowup.Value = False

    End With

End Sub

Private Sub UserForm_Initialize()

    Me.lblID.Caption = Sheet1.Cells(LRowInCol(wks:=Sheet1, Col:=1), 1) + 1

    LastRow = LRowInCol(wks:=Sheet1, Col:=1)

End Sub

Public Function LRowInCol(ByRef wks As Variant, _
                          ByRef Col As Variant) As Long

    On Error GoTo Correction

        If TypeName(wks) = "String" Then Set wks = Worksheets(wks)

        LRowInCol = wks.Columns(Col).Find(What:="*", _
                                                                  LookIn:=xlFormulas, _
                                                                  SearchOrder:=xlRows, _
                                                                  SearchDirection:=xlPrevious, _
                                                                  SearchFormat:=False).Row

Exitpoint:

    On Error GoTo 0

        Exit Function

Correction:

        LRowInCol = 1

    Resume Exitpoint

End Function

The above works so but is in a procedural fashion.

If it were to be done in an oo way, then we'll have:

This is in Userform1:

Option Explicit

Private m_oCustSurvey As cCustSurvey
Private m_blnSaved As Boolean
'
Private Sub cmdCancel_Click()

    ClearForm
    Unload UserForm1

End Sub

Private Sub cmdNew_Click()

    'sets form up for a new record

    Dim iAnswer As Integer

    'check that current record is saved (if any)

    If Not m_blnSaved Then 'see if any text data is entered that is not saved

    If (Len(Me.txtPhone.Value & "") + Len(Me.txtState.Value & "")) <> 0 Then

        iAnswer = MsgBox("There is unsaved data. Do you want to continue?", vbYesNo, "Unsaved Data")

        If iAnswer = vbYes Then

            ClearForm

        End If

    Else

        ClearForm

    End If

    End If

End Sub
Private Sub cmdSave_Click()
With m_oCustSurvey
.State = txtState.Text
.PhoneNumber = txtPhone.Text
.HeardOfProduct = chkHeard.Value
.WantsProduct = chkInterested.Value
.Followup = chkFollowup.Value
End With

If Not m_oCustSurvey.ValidateData Then
MsgBox "State and Phone Number required", vbOKOnly, "Cannot Save"
Exit Sub
Else
m_blnSaved = m_oCustSurvey.Save
End If

DoAfterSave m_blnSaved

End Sub

Private Sub DoAfterSave(success As Boolean)

If success Then
ClearForm
lblID.Caption = m_oCustSurvey.GetNextID
MsgBox "Record Saved"
Else
MsgBox "Could not save record"
End If

m_blnSaved = False 'resetting flag

End Sub

Private Sub ClearForm()

Me.txtPhone.Value = ""
Me.txtState.Value = ""
Me.chkHeard.Value = False
Me.chkInterested.Value = False
Me.chkFollowup.Value = False

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'prevent closing by X button or keystrokes

If CloseMode = vbFormControlMenu Then Cancel = True

End Sub

Private Sub UserForm_Initialize()

Set m_oCustSurvey = New cCustSurvey
Set m_oCustSurvey.DBWorkSheet = Sheets("Sheet1")
m_oCustSurvey.GetNextID
lblID.Caption = m_oCustSurvey.ID
m_blnSaved = False
ClearForm

End Sub

Private Sub UserForm_Terminate()

Set m_oCustSurvey = Nothing
End Sub

This is in cCustSurvey:

Option Explicit
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
'
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
Public Function Save() As Boolean
Dim lngNewRowNum As Long
Dim blnReturn As Boolean
  blnReturn = False
If m_xlWksht Is Nothing Then 'double check that we still have a valid object
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 'no error
blnReturn = True
End If

Exit_Function:
Save = blnReturn
Exit Function
End Function
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
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
Private Sub Class_Initialize()
Set m_oXL = New cExcelUtils
End Sub
Private Sub Class_Terminate()
Set m_oXL = Nothing
End Sub

This is in cExcelUtils:

Function FindEmptyRow(ws As Worksheet) As Long
Dim lngReturn As Long

  lngReturn = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  FindEmptyRow = lngReturn
End Function

It seems to be a lot of extra work to be using classes.

Have I missed a point?

Thanks

 

[mikerickson]

Do you have a question?

 

[tiredofit]

Do you have a question?

Sorry, was tidying up the code.

My question is: is all the extra effort of creating classes worth it?

 

[mikerickson]

Sometimes yes, sometimes no.
In your example, you have mixed an awful lot of stuff that I wouldn't have included in a cCustSurvey object.

The question is "what is a cCustSurvey thing"? It is the information, ID, State, Phone etc.
But are the cells where that data is stored part of a cCustSurvey thing? Its the coders choice, but if it is then the cells should change in the Property Let ID routine, etc.
Similarly, finding an empty row doesn't seem to be something that a Survey would "do", finding empty rows is something a coder would do, not the object itself.

Also the Next property. Next implies that each cCustSurvey is one of many, it implies a cCustSurveys object.

When constructing classes, it helps to look at them as stand alone objects that VBA manipulates.

 

[tiredofit]

Sometimes yes, sometimes no.
In your example, you have mixed an awful lot of stuff that I wouldn't have included in a cCustSurvey object.

The question is "what is a cCustSurvey thing"? It is the information, ID, State, Phone etc.
But are the cells where that data is stored part of a cCustSurvey thing? Its the coders choice, but if it is then the cells should change in the Property Let ID routine, etc.
Similarly, finding an empty row doesn't seem to be something that a Survey would "do", finding empty rows is something a coder would do, not the object itself.

Also the Next property. Next implies that each cCustSurvey is one of many, it implies a cCustSurveys object.

When constructing classes, it helps to look at them as stand alone objects that VBA manipulates.

Thanks for your reply.

The procedural method was done by me, to see how straightforward it was.

The oo method was lifted from this book:

https://www.apress.com/gp/book/9781590599570

it explains:

As you can see, we’re designing a very simple data collection tool. We’re going to write a
record to the database that is stored on Sheet1 in the UserForm workbook, and we want to do
some validation of the data before we save.

Before we begin, we need to think about a couple of functions we might need and how to
approach our code design. First, our form needs to know which worksheet to save the data to
(in this case, Sheet1 contains our database). It also needs to know the next available ID number
and the location of the next available row to place the data when we save the data.

A function that can tell us where the next available row in a worksheet is might be useful
in another project as well.

whose aim was to show how to create a userform in an oo way.

So are you saying what the book has done is not ideal? As I'm trying to understand the benefits of using classes, I was hoping the book provided a textbook solution.

 

[mikerickson]

I would disagree with the book, in that if the data, the cells, and the userform are all in a cCustSurvey object, then changing the data should change all of the object.
the Property Let ID routine should change all three. There shouldn't be a Save method, because the cells are part of the object, it is inherently "saved".

But if a cCustSurvey object is the data only, then a Save or a WriteToUserForm method would be appropriate.
BUT, if a cCustSurvey object is a stand-alone data structure (no cells, no userform), which UF controls get which property isn't an inherent feature of that data structure. ID could go into Userform1.txtID or Userform2.TextBox1, the data structure doesn't "know" that, so that info shouldn't be built into the Class.

One thing about the book, it is showing the formal way to construct Properties, but as a practical matter, what the book has as

Private m_strState As String
' ...
Property Get State() As String
    State = m_strState
End Property
Property Let State(newState As String)
    m_strState = newState
End Property

could be written with a Public variable in the class module

Public State As String

For simple properties like that a full exposition is not needed. BUT, if one were to integrate the cells and the data (as I mentioned above) the Property Let proceedure would be needed.

 

[tiredofit]

I would disagree with the book, in that if the data, the cells, and the userform are all in a cCustSurvey object, then changing the data should change all of the object.
the Property Let ID routine should change all three. There shouldn't be a Save method, because the cells are part of the object, it is inherently "saved".

But if a cCustSurvey object is the data only, then a Save or a WriteToUserForm method would be appropriate.
BUT, if a cCustSurvey object is a stand-alone data structure (no cells, no userform), which UF controls get which property isn't an inherent feature of that data structure. ID could go into Userform1.txtID or Userform2.TextBox1, the data structure doesn't "know" that, so that info shouldn't be built into the Class.

One thing about the book, it is showing the formal way to construct Properties, but as a practical matter, what the book has as

Private m_strState As String
' ...
Property Get State() As String
    State = m_strState
End Property
Property Let State(newState As String)
    m_strState = newState
End Property

could be written with a Public variable in the class module

Public State As String

For simple properties like that a full exposition is not needed. BUT, if one were to integrate the cells and the data (as I mentioned above) the Property Let proceedure would be needed.

Thanks for your detailed explanation.