# Class Module of Table properties, Record Table HeaderName and Column number to dictionary

This code creates a class module that record headers in to a dictionary which then allows for copy and paste data from another file where by the headers match. This helps to create a more dynamic table i.e. users can add/remove columns without changing the code (as long as they observe limitations).

The idea:

• File 1 D1 <--- Dict that contain header as KEY and column number as Value
• File 2 D2 <--- Dict that contain header as KEY and column number as Value

Cycle through D1 header and call out D2 (key)i.e. D1 header is "SKU" in File 1, so I call out D2("SKU"). If it exists, I will grab that column data in File 2 and paste it into File 1.

I have been coding VBA for quite sometime now and it is only recently that I have begun diving into doing up some class module.

Some questions on my mind is, when should I stop including functions and properties in a class? I am creating a class module where by it record table properties (i.e. row of header column). I went on further to create a retrieve data function where it gets data from other table with the same class.

1. Am I overdoing it? I tend to want to put all the function in one class. I have been thinking of adding more function like assigning workbook, worksheets, function to filldown certain columns etc, is this a good idea, or should I leave this as a separate function outside the class module?

2. If I want to set all classes to have the same default initialization do I declare a public const in the class module itself or the normal modules?

Here is my class module:

Private pHeaderNames As Object

Private pDataRowStart As Long
Private pInputColStart As Long
Private pTableColStart As Long

Private pTblWorksheet As Worksheet
Private pFileURL As String
Private pFileName As String

'---------------------------------------------- Initialization'
Private Sub Class_Initialize()
pDataRowStart = 2
pTableColStart = 1
pInputColStart = pTableColStart
End Sub

Public Property Get FileURL() As String
FileURL = pFileURL
End Property

Public Property Let FileURL(Value As String)
pFileURL = Value
End Property

Public Property Get FileName() As String
FileName = pFileName
End Property

Public Property Let FileName(Value As String)
pFileName = Value
End Property

Function OpenWorkbook(URL As String, Name As String)
pFileURL = URL
pFileName = Name
Call Workbook_open(pFileURL, pFileName)
End Function

Public Property Let SetSheet(Value As String)
If pFileName - "" Then
Set pTblWorksheet = ActiveWorkbook.Worksheets(Value)
Else
Set pTblWorksheet = Workbook(pFileName).Worksheets(Value)
End If
End Property

Public Property Get TblWorksheet() As Worksheet
TblWorksheet = pTblWorksheet
End Property

Public Property Let TblWorksheet(Sheet As Worksheet)
pTblWorksheet = Sheet
End Property

Public Property Get HeaderLastCol() As Long
End Property

Public Property Let HeaderLastCol(Value As Long)
End Property

Public Property Let HeaderRow(Value As Long)
End Property

Public Property Get HeaderRow() As Long
End Property

Public Property Let SubHeaderRow(Value As Long)
End Property

Public Property Get SubHeaderRow() As Long
End Property

'---------------------------------------------- InputColStart'
Sub SetInputColStart(KEY As Variant)
End Sub

Public Property Get InputColStart() As Long
InputColStart = pInputColStart
End Property

Public Property Let InputColStart(Value As Long)
pInputColStart = Value
End Property

'---------------------------------------------- DataRowStart'
Public Property Get DataRowStart() As Long
DataRowStart = pDataRowStart
End Property

Public Property Let DataRowStart(Value As Long)
pDataRowStart = Value
End Property

'---------------------------------------------- TableColStart'
Public Property Get TableColStart() As Long
TableColStart = pTableColStart
End Property

Public Property Let TableColStart(Value As Long)
pTableColStart = Value
End Property

For i = pTableColStart To pHeaderLastCol
End If
Next i
End With
End Sub

Else
End If
End Function

End Function

Next
End Function

For i = pTableColStart To pHeaderLastCol
End If
Next i
End With
End Sub

Else
SubHeaderName = "" 'or raise an error...
End If
End Function

End Function

Next
End Function

Function RetrieveData(FromSht As Worksheet, ByVal FromTable As cTable)

Dim KEY As String

'CurrentSht = ActiveSheet
For i = pTableColStart To pHeaderLastCol
GoTo Nexti
Else
With FromSht
.Activate
rD_LastRow = 10
Set Source = .Range(.Cells(FromTable.DataRowStart, FromTable.HeaderName(KEY)), _
End With

With CurrentSht
.Activate
.Range(.Cells(DataRowStart, i), _
.Cells(DataRowStart, i)) _
.Resize(Source.Rows.Count, Source.Columns.Count).Cells.Value = Source.Cells.Value
End With
End If
Nexti:
Next i
End Function


Here is the module, so you can see that I have always need to declare headerRow, subHeaderRow and Datarowstart, even though I have a default initialization set in the class module. Is there away to change the default initialization based on the main module, or should I just have to do it in the class module? (I trying to make the class portable.)

Sub test()

Dim sht As Worksheet

Set wb = ActiveWorkbook
Set sht = wb.Sheets("Skin(Units)")

With Worksheets("Skin(Units)")
.Activate
Set SkinUnits = New cTable

Debug.Print TypeName(SkinUnits)
SkinUnits.DataRowStart = 4

SkinUnits.SetInputColStart ("Start")
End With

With Worksheets("Pain(Units)")
.Activate
Set PainUnits = New cTable

PainUnits.DataRowStart = 4

PainUnits.SetInputColStart ("Start")
Debug.Print TypeName(sht), TypeName(SkinUnits)
Call test22222(SkinUnits)
Call PainUnits.RetrieveData(sht, SkinUnits)

End With

End Sub

Function test22222(ByVal X As cTable)

End Function

• There seem to be some leftover formatting issue, can you verify your code? Nexti: Next i End Function – Ludisposed Aug 17 '18 at 7:20

First, having parameters in initialization is overloading - and no it's not possible in VBA. Your best bet is to create constants and a new function for assigning those constants to properties.

1. Calling functions/subs is obsolete. Just do Sub argument, argument or x = function(argument)

2. Always turn on Option Explicit. You can have it automatically by going to Tools -> Options in the VBE and checking the Require Variable Declaration option. This way if you have any variables not defined, the compiler will let you know. Right now you have nothing declared in your test module.

3. Sub Test and Function test22222 tell me nothing about what they do. Name your procedures something useful - this may just be throw-away for you, but it's on code review, so by definition it's not.

4. Function test22222(ByVal X As cTable) - why is this a function? It doesn't have a return type so it would be a sub instead.

5. Naming, besides just being clear and concise, you should adhere to Standard VBA naming conventions - variables would be camelCase and only constants would be UPPER_CASE

'---------------------------------------------- DataRowStart'
Public Property Get DataRowStart() As Long
DataRowStart = pDataRowStart
End Property

Public Property Let DataRowStart(Value As Long)
pDataRowStart = Value
End Property

'---------------------------------------------- TableColStart'
Public Property Get TableColStart() As Long
TableColStart = pTableColStart
End Property


You may think these headers make the code more clear, but it would actually be more clear without the clutter.

pHeaderRow = 1
pDataRowStart = 2
pTableColStart = 1


The usage of constants would be a good idea here

Const HEADER_ROW as Long = 1
Const DATA_ROW_START as Long = 2
Const TABLE_COLUMN_START as Long = 1


This moves it out of the initialization and makes it easier to see that they are constants when in use.

In your properties you use the variable value a lot. Try to make it more precise. Also, I avoid using variables named Value and Sheet because they are key words for the VBE.

### cTable

So you've made a Class, that is great! They are sometimes difficult to start using because they seem so foreign (to many VBA writers). What I suggest is to think of this class as what it is - an object. That object then has properties.

For example, a Range is an object and it has properties like count and value.

Objects can also have methods. With Range you can do something complex like AutoFilter or something simple like clear

By remembering how VBA uses its object, you can build you own objects in the same way. This would suggest that if you feel you're overdoing it, and your object isn't that complex, then yes you are overdoing it.

Right now your class creates two objects - Dictionaries. Dictionaries are already objects (or classes) with properties and methods. Putting two of those into a single object means that your class is a collection. So then let's see if this collection of dictionaries would have some of these properties -

Header row, header names? Yes. Beginning rows and columns - I don't know, wouldn't that be a property of the source sheet? Speaking of the sheet - are both of these dictionaries always coming from the same sheet or file? If not, then maybe your class should be an object instead of a collection of objects.

Would a collection of dictionaries open a workbook? Probably not. That's probably something you'd want to do outside of the class, but that's just my opinion. There's no right or wrong here, just what you think makes sense. But, consider all the properties you didn't use in your test - why not? Because they aren't inherent to the object?

If pFileName - "" Then
Else


Instead of "" VBA has a constant for that - vbNullString. Always prefer that over empty quotes.

  With FromSht
.Activate


No! Be sure to avoid things like .Activate - it just slows the code down by needing to fiddle with the spreadsheet while doing everything else behind the scenes. There's a good question on StackOverflow addressing this. If you know the target sheet, just use the target sheet.

Speaking of target sheet

Public Property Let HeaderRow(Value As Long)

This Cells is just assuming it needs to use the ActiveSheet. If you need to use a specific sheet, give that sheet a name. Either a variables or an actual name. Worksheets have a CodeName property - View Properties window (F4) and the (Name) field (the one at the top) can be used as the worksheet name. This way you can avoid Sheets("mySheet") and instead just use mySheet.
Your formatting of the functions in the class is good - great usage of the line continuation _.