Separating code

[tiredofit]

Instead of having all this in a standard module:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String

Dim sSQL As String
    Set xlSheet = Sheets("Sheet2")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
    Range("A1").Select

    Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=D2FZSC51\SQLEXPRESS;" _
& "Database=AdventureWorks;Trusted_Connection=yes;"

    cnn.Open sConnString

    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("A1").CopyFromRecordset rs

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

rs.Close
cnn.Close

Set rs = Nothing
Set cnn = Nothing
    Set xlSheet = Nothing

it has been suggested to break it into two classes:

cData

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

and cExcelSetup

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

What I don't understand is why is this declared Private?

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

when all other subs are Public?

PS Don't say because you want it only accessible within the class!

I also noticed that when it is Private, I cannot write:

Me.ClearRegion

which surprises me because I thought as it's in the same class, it should be able to refer to itself with the keyword Me.

Thanks

 

[Joe4]

One of the things making it "Private" hides it from the Macros listing when people go to "View Macros". So things that you don't want them to see or run manually from outside the VB Editor you can put "Private" in front of.

You have set "ClearRegion" to be a procedure. So if you wanted to call it, you can use:
Call ClearRegion
or just
ClearRegion

To see how "Me" is used in Excel, and the limitations (you can only use it under certain scenarios!), see this: Me Keyword in Excel Macros & VBA - TeachExcel.com.

 

[tiredofit]

One of the things making it "Private" hides it from the Macros listing when people go to "View Macros". So things that you don't want them to see or run manually from outside the VB Editor you can put "Private" in front of.

You have set "ClearRegion" to be a procedure. So if you wanted to call it, you can use:
Call ClearRegion
or just
ClearRegion

To see how "Me" is used in Excel, and the limitations (you can only use it under certain scenarios!), see this: Me Keyword in Excel Macros & VBA - TeachExcel.com.

Thanks but normally to hide macros, I just put:

option private module

at the top of the module.

 

[Joe4]

If you use Excel enough, one thing you begin to realize is that there are often multiple different ways to accomplish something.

Quote from this link here:

Excel VBA Sub Procedures: The Complete Tutorial

Quickly learn about the most important aspects of Excel VBA Sub procedures. Names and Scope. VBA Sub Procedures Vs. Function Procedures. How to call a Sub.

powerspreadsheets.com

If you type “Private” at the beginning of a VBA Sub procedure, the only procedures that are able to access it are those stored in the same VBA module.

If, on the other hand, you use “Public”, the VBA Sub procedure has no access restrictions. Despite this, if the procedure is located in a module that uses the Option Private Statement, the VBA Sub procedure can't be referenced outside the relevant project.

 

[tiredofit]

If you use Excel enough, one thing you begin to realize is that there are often multiple different ways to accomplish something.

Quote from this link here:

Excel VBA Sub Procedures: The Complete Tutorial

Quickly learn about the most important aspects of Excel VBA Sub procedures. Names and Scope. VBA Sub Procedures Vs. Function Procedures. How to call a Sub.

powerspreadsheets.com

Thanks, that's why everyone "knows" Excel and are "experts"