Excel VBA Class Modules

I came to the course hoping tolearn only one thing - class modules but unfortunately it was not covered.

Below are two of my programs. They do the same thing but the second is "with class modules".

I include "" because I don't think it's actually utlising the full features of a CM, ie there's no properties get, let or any functions.

My question is: how can my first program (without CMs) be adapted to use CMs in a meaningful way, not the way I have done.

Thanks



All this is in Workbook_No_CM

'*****

This is in ModFirst:

Option Explicit

Sub Start()

wsm.Cells.ClearContents

Call ModGetData.Getdata

Call ModFirst.Headings

With wsm

.Select
Cells.EntireColumn.AutoFit
Cells(1, 1).Select

End With

Set wb = Nothing
Set wks = Nothing
Set wsm = Nothing

End Sub

Sub Headings()

Dim HeadingsArray() As Variant

HeadingsArray = Array("Field1", "Field2", "Field3")

Dim HeadingsArrayCount As Integer

HeadingsArrayCount = UBound(HeadingsArray, 1)

wsm.Cells(1, 1).Resize(1, HeadingsArrayCount + 1) = HeadingsArray

Erase HeadingsArray()

End Sub

'*****

This is in ModGetData:

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strcon As String
Dim strSQL As String

Dim strfile As String

Sub Getdata()

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

rs.CursorLocation = adUseServer

strfile = wb.FullName

On Error Resume Next

strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strfile & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1;MaxScanRows=0"";"

cn.Open ConnectionString:=strcon

On Error GoTo 0

strSQL = "SELECT [A$].[Field1], [A$].[Field2], [A$].[Field3] " & _
"FROM [A$] " & _
"LEFT JOIN [B$] ON [A$].[Field3] = [B$].[Field3] " & _
"WHERE [B$].[Field3] Is Null " & _
"UNION " & _
"SELECT [B$].[Field1], [B$].[Field2], [B$].[Field3] " & _
"FROM [B$] " & _
"LEFT JOIN [A$] ON [B$].[Field3] = [A$].[field3] " & _
"WHERE [A$].[Field3] Is Null"

rs.Open Source:=strSQL, _
ActiveConnection:=cn

wsm.Cells(2, 1).CopyFromRecordset Data:=rs

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

'*****

This is in ModInitialise

Option Explicit

Global wb As Workbook
Global wks As Worksheet

Global wsa As Worksheet
Global wsb As Worksheet

Global wsm As Worksheet
Global wsi As Worksheet

Sub Initialise()

Set wb = ThisWorkbook

With wb

Set wsa = .Worksheets("A")
Set wsb = .Worksheets("B")

Set wsm = .Worksheets("Mismatches")
Set wsi = .Worksheets("Input")

End With

End Sub

'*****

This is in ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()

Call ModInitialise.Initialise

End Sub

'*****


All this is in WorkbookCM

'*****

This is in a module called ModFirst:

Option Explicit

Sub Start()

wsm.Cells.ClearContents

Dim MyGetData As ClsGetData

Set MyGetData = New ClsGetData

Call MyGetData.GetData

Set MyGetData = Nothing

Call ModFirst.Headings

With wsm

.Select
Cells.EntireColumn.AutoFit
Cells(1, 1).Select

End With

Set wb = Nothing
Set wks = Nothing
Set wsm = Nothing

End Sub

Sub Headings()

Dim HeadingsArray() As Variant

HeadingsArray = Array("Field1", "Field2", "Field3")

Dim HeadingsArrayCount As Integer

HeadingsArrayCount = UBound(HeadingsArray, 1)

wsm.Cells(1, 1).Resize(1, HeadingsArrayCount + 1) = HeadingsArray

Erase HeadingsArray()

End Sub

'*****

This is in a module called ModInitialise:

Option Explicit

Global wb As Workbook
Global wks As Worksheet

Global wsa As Worksheet
Global wsb As Worksheet

Global wsm As Worksheet
Global wsi As Worksheet

Sub Initialise()

Set wb = ThisWorkbook

With wb

Set wsa = .Worksheets("A")
Set wsb = .Worksheets("B")

Set wsm = .Worksheets("Mismatches")
Set wsi = .Worksheets("Input")

End With

End Sub

'*****

This is in ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()

Call ModInitialise.Initialise

End Sub

'*****

This is in a class module called ClsGetData:

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim strcon As String
Dim strSQL As String

Dim strfile As String

Sub GetData()

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

rs.CursorLocation = adUseServer

strfile = wb.FullName

On Error Resume Next

strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strfile & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1;MaxScanRows=0"";"

cn.Open ConnectionString:=strcon

On Error GoTo 0

strSQL = "SELECT [A$].[Field1], [A$].[Field2], [A$].[Field3] " & _
"FROM [A$] " & _
"LEFT JOIN [B$] ON [A$].[Field3] = [B$].[Field3] " & _
"WHERE [B$].[Field3] Is Null " & _
"UNION " & _
"SELECT [B$].[Field1], [B$].[Field2], [B$].[Field3] " & _
"FROM [B$] " & _
"LEFT JOIN [A$] ON [B$].[Field3] = [A$].[field3] " & _
"WHERE [A$].[Field3] Is Null"

rs.Open Source:=strSQL, _
ActiveConnection:=cn

wsm.Cells(2, 1).CopyFromRecordset Data:=rs

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

'*****