Convert to class module

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,520
Office Version
  1. 2019
Platform
  1. Windows
The following code works as expected:

Code:
' This is in a standard module.

Option Explicit

Public Sub Consolidate()

    Dim Templates As String
    Dim wb As Workbook

    Dim ws As Worksheet
    Dim SourceRange As Range

    Data.Range("A1").CurrentRegion.Offset(1, 0).Clear

    ChDir ThisWorkbook.Path & "\Templates\"

    Templates = Dir("*.xlsm")

    Do Until Templates = vbNullString

        Set wb = Workbooks.Open(Filename:=Templates)

        For Each ws In wb.Worksheets

            If ws.CodeName = "Data" Then

                Set SourceRange = ws.Range("A1 & ":B" & FnLastRow.LRow(wks:=ws))

                SourceRange.Copy Destination:=Data.Cells(FnLastRow.LRow(wks:=Data) + 1, 2)
               
            End If

        Next ws

        Application.CutCopyMode = False

        wb.Close

        Templates = Dir

    Loop

End Sub

Public Function LRow(ByRef wks As Worksheet) As Long

    On Error GoTo Correction

        With wks
       
            LRow = wks.Cells.Find(What:="*", _
                                  After:=.Cells(.Rows.Count, .Columns.Count), _
                                  SearchDirection:=xlPrevious, _
                                  SearchOrder:=xlByRows).Row
           
        End With
   
Exitpoint:

    On Error GoTo 0
   
    Exit Function
   
Correction:

        LRow = 1

    Resume Exitpoint

End Function

but I would like to convert it to using a class module.

I have got this:

Code:
' This is in a standard module:

Public Sub Consolidate()
   
    Dim MyConsolidate As ClsConsolidate
    Set MyConsolidate = New ClsConsolidate
   
    Set MyConsolidate.wks = wksSwabbed
   
    MyConsolidate.LastRow = FnLastRow.LRow(wks:=MyConsolidate.wks)
   
    Call MyConsolidate.Consolidate
   
    Set MyConsolidate = Nothing
   
End Sub


'This is in ClsConsolidate:

Option Explicit

    Private pLastRow As Long
    Private pwks As Worksheet
   
Public Property Get LastRow() As Long

    LastRow = pLastRow
   
End Property

Public Property Let LastRow(ByVal LRow As Long)

    pLastRow = LRow
   
End Property

Public Property Get wks() As Worksheet

    Set wks = pwks
   
End Property

Public Property Set wks(ByVal w As Worksheet)

    Set pwks = w
   
End Property

Public Sub Consolidate()
   
    Me.wks.Range("A1 & ":B" & Me.LastRow).Clear
   
    ChDir ThisWorkbook.Path & "\Templates\"
   
    Dim Templates As String
   
    Templates = Dir("*.xlsm")

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim SourceRange As Range
   
    Do Until Templates = vbNullString

        Set wb = Workbooks.Open(Filename:=Templates)

        For Each ws In wb.Worksheets

            If ws.CodeName = "Data" Then

        Set SourceRange = ws.Range("A1 & ":B" & FnLastRow.LRow(wks:=ws))

                SourceRange.Copy Destination:=Me.wks.Cells(FnLastRow.LRow(wks:=Me.wks) + 1, 2)
               
            End If

        Next ws

        Application.CutCopyMode = False

        wb.Close

        Templates = Dir

    Loop

    Set wb = Nothing
    Set ws = Nothing
    Set SourceRange = Nothing

End Sub

and indeed it does work but I have a feeling something's not quite right, in that the interface should be more than just LastRow and wks. Ideally I would like the interface to include wb, ws and SourceRange but those 3 variables are used within the loop, so it seems I cannot set them in the standard module beforehand.

Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,143,840
Messages
5,721,100
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top