VBA: THE BENEFIT OF USING CLASS AND THE WAY TO USE, WITH A SIMPLE SAMPLE PART 1

Nov 20, 2020 · 6 min read · Excel VBA HOMEMADE object-oriented ·

Overview

Help me improve my English!

As you probably see, I'm not a native English speaker.
If you find an English expression that feel incorrect or awkward, please let me know.

Message box of Disqus is under the article.
Or my E-Mail is here.

ABOUT THIS ARTICLE

Class in VBA is kind of minor and somehow hard-to-use than any other programming languages.
But actually there are some cases in which class enables us to code more safely, with more highly maintainability, and with less bugs.
So I would like to describe the benefits and how to use them. Moreover, I would also like to describe a simple sample of using Class as well.

You can download Excel file created for explanation and view its source code from here!

THE WAY TO USE CLASS

WHAT IS CLASS?

Class is a combination of used data information(i.e. Variables and Constants) and information of processing details the class has (i.e. Functions) in a box (i.e. Class module).
Having said that, it's not good to combine data and functions without any rules, but it must be a variable or function that defines one entity pointed to by the class and belongs to that entity.

I have to explain the term of 'object'.
An object is an instance of a class. Conversely, class is a blueprint of an object. A class is a description of the information of properties of an object. By instantiation, you can treat it as an entity, not as information, for the first time.

SAMPLE OF CLASS

For example, In case that one class expresses a human being, it has data (variables) such as eye, mouth, and body, and has processing details (functions) such as running, eating, and talking.

From a MSOffice point of view, the Range object in Excel is typical class object, having variables such as Address property for reference range, Row property for row number, and Value property for cell value, and having functions such as Select method for moving the cursor to range, AutoFit method for automatically adjusting row widths or column widths.

Also, What has a certain functionality can be class.
The example is what performs a series of processes in which it imports Excel Range object, draws lines as a table, and writes a total value of subtotal column. Let's name it as TableCreater. I will explain TableCreater in detail later.

Entity	Variables, Constants	Functions
Human Being	Eye Mouse Body	Run Eat Talk
Excel Range Object	Address Row Value	Select AutoFit
TableCreater	Target Range Column Number for Subtotal Header Color	Draw lines Set Header's Style Calculate Total Value

THE BENEFIT OF USING IT

ENABLES SAFER CODING

Variables and Constants belonging to class are basically declared in a scope of Private and are used by functions inner the class, so they are free from having it's value affected by alterring them accidentially (referring causes compiling error in the first place).

And, in the case that you want to refer them from outer the function, you can implement getter/setter procedure using Property Get statement and Property Let statement as a dedicated function, and interact with the outside through them.
This mechanism is called Encapsulation (or data hiding).

The example is following short code. This percent storing variable and Property Let procedure only accept a value between 0 and 100 as a percent and otherwise the variable is Null. In this way, you can enhance safety of your code by filtering or checking when getting or setting a value.

 1Private percentVal As Variant
 2
 3'**************************
 4'*Setter
 5'**************************
 6Public Property Let percent(ByVal v As Long)
 7    If v < 0 Or 100 < v Then percentVal = Null: Exit Property
 8    percentVal = v
 9End Property
10

ENABLES MORE HIGHLY MAINTAINABLE CODING

If you design functions of class use only its arguments or variables and constants declared inner the class as much as possible, this will reduce the frequency of using and being used by variables and functions from other classes and modules, and reduce the bondability of the codes to each other.
This mechanism is called loose coupling.

In this way, most of the impact of changing a processing of function and adding new functions can be inside the class, and you are free from suffering from the risk of unexpected behavior when changing the specification.

Also, this approach enhances the perspective of code and you can write code everyone can read easily.

REDUCES CODING MISTAKES BY EXECUTING A ROUTINE PROCESS

Class in VBA has functions which run when creating and destroying objects of the class.
When creating, Class_Initialize() runs, When destroying, Class_Terminate() runs.

If the routine processing which must be done when creating and destroying is described in these functions, this keep you from forgetting the processing or writing wrong processing.

Below is examples.

If the class use Mail Item of Outlook, you may want to get Outlook Application as soon as the class object is created, or may want to have the Mail Imte visible as soon as the class object is destroyed. If you forget to change its visibility, the macro user won't be aware of the mail created background.
If the class use Recordset of Access, you may want to connect Access Database as soon as the class object is created. Also, it's good to close RecordSet as soon as the class object is destroyed, in some cases, database itself too. If you forget to close the database, memory leaking or other unwelcome consequences may come.

KEEP IN MIND: CLASS FUNCTIONALITY OF VBA IS WEAK RELATIVELY

You had better realize the weakness of VBA class.

Class_Initialize doesn't have any arguments. So you can't assign class variables some value as soon as the class object is created.
In VBA, there is not the concept of Class Inheritance.
Class can have neither static variable or static function. That means you can't use these variables and functions unless you instantiate the class once.

I think these uncomfortabilities are weakness of VBA.

ONE SAMPLE OF HOW TO USE VBA CLASS

CREATION ENVIRONMENT

Windows10
MSOffice 2016

i. CREATE A CLASS MODULE IN VBE

TableCreater mentioned above appears again.

In VBE (Development Environment of VBA), select class module in insertion tab and create it.

1Option Explicit
2
3'**************************
4'*TableCreater
5'**************************
6

ii. DECLARE ITS VARIABLES AND CONSTANTS

As a variable, prepare following.

Target Range object
column number of subtotal column
header cell's color

Concurrently, describe Property Let procedure and Property Set procedure.

 1Option Explicit
 2
 3'**************************
 4'*TableCreater
 5'**************************
 6
 7
 8'Const
 9Private Const HEADER_COLOR = 15917529            'header cell color
10
11'Vars
12Private myRange As Range                         'range of target table
13Private myColumnSubTotal As Long                 'column number of subtotal
14
15
16'******************************************************************************************
17'*getter/setter
18'******************************************************************************************
19
20
21Public Property Set Range(ByVal pRng As Range)
22    Set myRange = pRng
23End Property
24
25
26Public Property Let ColumnSubTotal(ByVal num As Long)
27    'prohibit being refered with the Range isn't set yet
28    If myRange Is Nothing Then Err.Raise 1000, , "The range is not set."
29    'error if argument number is out of range of 'range' object.
30    If num < myRange.EntireColumn(1).Column Or myRange.EntireColumn(myRange.EntireColumn.Count).Column < num Then Err.Raise 1001, , "Invalid column number specification."
31    'set
32    myColumnSubTotal = num
33End Property
34
35

iii. Class_Initialize、Class_Terminateを記述する

Normally, We describe Class_Initialize and Class_Terminate.
But this itme there is nothing to do in them.

 1Option Explicit
 2
 3'**************************
 4'*TableCreater
 5'**************************
 6
 7
 8'(omission)
 9
10
11'******************************************************************************************
12'*Function ：
13'*Arg      ：
14'******************************************************************************************
15Private Sub Class_Initialize()
16    
17    'Const
18    Const FUNC_NAME As String = "Class_Initialize"
19    
20    'Vars
21    
22    On Error GoTo ErrorHandler
23    
24    'There's nothing special to do here this Class.
25
26ExitHandler:
27
28    Exit Sub
29    
30ErrorHandler:
31
32    MsgBox "An error has occurred and the macro will be terminated." & _
33           vbLf & _
34           "Func Name:" & FUNC_NAME & _
35           vbLf & _
36           "Error No." & Err.Number & vbNewLine & _
37           Err.Description, vbCritical, "TableCreater"
38        
39    GoTo ExitHandler
40        
41End Sub
42
43
44

iv. DESCRIBE EACH FUNCTION

Describe each processing detail as one function as a time.

Draw lines
Set header part's style
Calculate total value and output

  1Option Explicit
  2
  3'**************************
  4'*TableCreater
  5'**************************
  6
  7
  8'(omission)
  9
 10'******************************************************************************************
 11'*Function ：draw lines
 12'*Arg      ：
 13'*Return   ：True > normal termination; False > abnormal termination
 14'******************************************************************************************
 15Public Function drawLines() As Boolean
 16    
 17    'Const
 18    Const FUNC_NAME As String = "drawLines"
 19    
 20    
 21    On Error GoTo ErrorHandler
 22
 23    drawLines = False
 24    
 25    'prohibit being called with the Range isn't set yet
 26    If myRange Is Nothing Then Err.Raise 1000, , "The range is not set."
 27    
 28    'draw lines
 29    myRange.Borders.LineStyle = xlContinuous
 30
 31    drawLines = True
 32    
 33ExitHandler:
 34
 35    Exit Function
 36    
 37ErrorHandler:
 38
 39    MsgBox "An error has occurred and the macro will be terminated." & _
 40           vbLf & _
 41           "Func Name:" & FUNC_NAME & _
 42           vbLf & _
 43           "Error No." & Err.Number & vbNewLine & _
 44           Err.Description, vbCritical, "TableCreater"
 45        
 46    GoTo ExitHandler
 47        
 48End Function
 49
 50
 51'******************************************************************************************
 52'*Function ：set header part's style
 53'            The header is cells of the first row of given range.
 54'*Return   ：True > normal termination; False > abnormal termination
 55'******************************************************************************************
 56Public Function setStyleForHeader() As Boolean
 57    
 58    'Const
 59    Const FUNC_NAME As String = "setStyleForHeader"
 60    
 61    
 62    On Error GoTo ErrorHandler
 63
 64    setStyleForHeader = False
 65    
 66    'prohibit being called with the Range isn't set yet
 67    If myRange Is Nothing Then Err.Raise 1000, , "The range is not set."
 68    
 69    'change styles
 70    With myRange.Rows(1)
 71        'change background color
 72        .Interior.color = HEADER_COLOR
 73        'change font weight to bold
 74        .Font.Bold = True
 75        'change text alignment to center
 76        .HorizontalAlignment = xlCenter
 77    End With
 78        
 79    
 80    setStyleForHeader = True
 81    
 82    
 83ExitHandler:
 84
 85    Exit Function
 86    
 87ErrorHandler:
 88
 89    MsgBox "An error has occurred and the macro will be terminated." & _
 90           vbLf & _
 91           "Func Name:" & FUNC_NAME & _
 92           vbLf & _
 93           "Error No." & Err.Number & vbNewLine & _
 94           Err.Description, vbCritical, "TableCreater"
 95        
 96    GoTo ExitHandler
 97        
 98End Function
 99
100
101
102
103'******************************************************************************************
104'*Function ：calculate total value from subtotal column and output it
105'*Return   ：True > normal termination; False > abnormal termination
106'******************************************************************************************
107Public Function calcTotalFromSubTotal() As Boolean
108    
109    'Const
110    Const FUNC_NAME As String = "calcTotalFromSubTotal"
111    
112    'Vars
113    Dim sumVal As Long
114    Dim cell As Range
115    Dim subTotalOrder As Long
116    
117    On Error GoTo ErrorHandler
118
119    calcTotalFromSubTotal = False
120    
121    'prohibit being called with the Range isn't set yet
122    If myRange Is Nothing Then Err.Raise 1000, , "The range is not set."
123    
124    'prohibit being called with the column number for subtotal isn't set yet
125    If myColumnSubTotal = 0 Then Err.Raise 1002, , "The column number for subtotal is not set."
126        
127    'calculate the order of subtotal column
128    subTotalOrder = myColumnSubTotal - myRange(1).Column + 1
129        
130    'calculate total value, except for header row
131    For Each cell In myRange.Columns(subTotalOrder).Cells.Offset(1).Resize(myRange.Columns(subTotalOrder).Cells.Offset(1).Cells.Count - 1)
132        'add only numeric value
133        If IsNumeric(cell.Value) Then sumVal = sumVal + cell.Value
134    Next cell
135    If sumVal = 0 Then GoTo TruePoint
136    
137    'write the total value in the bottom cell of subtotal column
138    With myRange.Columns(subTotalOrder).Rows(myRange.Columns(subTotalOrder).Cells.Count).Offset(1)
139        .Value = sumVal
140        'refer the label cell
141        With .Offset(, -1)
142            'write a label
143            .Value = "Total"
144            'draw lines to label cell and total cell
145            .Resize(, .Columns.Count + 1).Borders.LineStyle = xlContinuous
146        End With
147        
148    End With
149       
150TruePoint:
151       
152    calcTotalFromSubTotal = True
153    
154ExitHandler:
155
156    Exit Function
157    
158ErrorHandler:
159
160    MsgBox "An error has occurred and the macro will be terminated." & _
161           vbLf & _
162           "Func Name:" & FUNC_NAME & _
163           vbLf & _
164           "Error No." & Err.Number & vbNewLine & _
165           Err.Description, vbCritical, "TableCreater"
166        
167    GoTo ExitHandler
168        
169End Function
170

At this point coding for class is completed.
You got the class expressing what creates table with data and behaviors.

v. USE THE CLASS FROM AN EXTERNAL FUNCTION

Following is 'base' sheet containing two table data.

Base

Based on each table data, I created functions which creates corresponging table in new sheet.
Of course theses functions uses TableCreater.

  1
  2'******************************************************************************************
  3'*Function ：create a table for template A in base sheet through TableCreater
  4'            creation location: new sheet
  5'******************************************************************************************
  6Public Sub TestTemplateA()
  7    
  8    'Const
  9    Const FUNC_NAME As String = "TestTemplateA"
 10    
 11    'Vars
 12    Dim ws As Worksheet
 13    Dim tableRange As Range
 14    Dim objTableCreater As TableCreater
 15    
 16    On Error GoTo ErrorHandler
 17    
 18    With ThisWorkbook
 19        'create new sheet
 20        Set ws = .Worksheets.Add(, .Worksheets(.Worksheets.Count))
 21        ws.Name = FUNC_NAME & "_" & Format(Now, "yyyymmddhhnnss")
 22        
 23        'copy template range from
 24        Set tableRange = ws.Range(ws.Cells(2, 2), ws.Cells(9, 4))
 25        tableRange.Value = .Worksheets(BASE_SHEET).Range(.Worksheets(BASE_SHEET).Cells(3, 2), .Worksheets(BASE_SHEET).Cells(10, 4)).Value
 26        
 27        'instanciate TableCreater
 28        Set objTableCreater = New TableCreater
 29        
 30        'set params
 31        Set objTableCreater.Range = tableRange
 32        objTableCreater.ColumnSubTotal = 4
 33        
 34        'draw lines: if error, shift to the exit process
 35        If Not objTableCreater.drawLines Then GoTo ExitHandler
 36         
 37        'set styles for header part for emphasis: if error, shift to the exit process
 38        If Not objTableCreater.setStyleForHeader Then GoTo ExitHandler
 39        
 40        'calc total: if error, shift to the exit process
 41        If Not objTableCreater.calcTotalFromSubTotal Then GoTo ExitHandler
 42        
 43        'adjust column widths
 44        tableRange.EntireColumn.AutoFit
 45        
 46    End With
 47    
 48
 49ExitHandler:
 50    
 51    'release memory
 52    Set objTableCreater = Nothing
 53    Set ws = Nothing
 54    Set tableRange = Nothing
 55    
 56    Exit Sub
 57    
 58ErrorHandler:
 59
 60    MsgBox "An error has occurred and the macro will be terminated." & _
 61           vbLf & _
 62           "Func Name:" & FUNC_NAME & _
 63           vbLf & _
 64           "Error No." & Err.Number & vbNewLine & _
 65           Err.Description, vbCritical, "TableCreater"
 66        
 67    GoTo ExitHandler
 68        
 69End Sub
 70
 71
 72
 73
 74
 75
 76'******************************************************************************************
 77'*Function ：create a table for template B in base sheet through TableCreater
 78'            creation location: new sheet
 79'******************************************************************************************
 80Public Sub TestTemplateB()
 81    
 82    'Const
 83    Const FUNC_NAME As String = "TestTemplateB"
 84    
 85    'Vars
 86    Dim ws As Worksheet
 87    Dim tableRange As Range
 88    Dim objTableCreater As TableCreater
 89    
 90    On Error GoTo ErrorHandler
 91    
 92    With ThisWorkbook
 93        'create new sheet
 94        Set ws = .Worksheets.Add(, .Worksheets(.Worksheets.Count))
 95        ws.Name = FUNC_NAME & "_" & Format(Now, "yyyymmddhhnnss")
 96        
 97        'copy template range from
 98        Set tableRange = ws.Range(ws.Cells(2, 2), ws.Cells(8, 8))
 99        tableRange.Value = .Worksheets(BASE_SHEET).Range(.Worksheets(BASE_SHEET).Cells(13, 2), .Worksheets(BASE_SHEET).Cells(19, 8)).Value
100        
101        'instanciate TableCreater
102        Set objTableCreater = New TableCreater
103        
104        'set params
105        Set objTableCreater.Range = tableRange
106        objTableCreater.ColumnSubTotal = 8
107        
108        'draw lines: if error, shift to the exit process
109        If Not objTableCreater.drawLines Then GoTo ExitHandler
110         
111        'set styles for header part for emphasis: if error, shift to the exit process
112        If Not objTableCreater.setStyleForHeader Then GoTo ExitHandler
113        
114        'calc total: if error, shift to the exit process
115        If Not objTableCreater.calcTotalFromSubTotal Then GoTo ExitHandler
116        
117        'adjust column widths
118        tableRange.EntireColumn.AutoFit
119        
120    End With
121    
122
123ExitHandler:
124    
125    'release memory
126    Set objTableCreater = Nothing
127    Set ws = Nothing
128    Set tableRange = Nothing
129    
130    Exit Sub
131    
132ErrorHandler:
133
134    MsgBox "An error has occurred and the macro will be terminated." & _
135           vbLf & _
136           "Func Name:" & FUNC_NAME & _
137           vbLf & _
138           "Error No." & Err.Number & vbNewLine & _
139           Err.Description, vbCritical, "TableCreater"
140        
141    GoTo ExitHandler
142        
143End Sub
144
145

Running them results:

Table A Table B

vi. WHAT YOU GOT FROM USING THE CLASS

With TableCreater, I think the perspective of code is good.
We'll realize that the sentence including objTableCreater.something is related to creation of table.

AT THE END

TABLE_CREATER SAMPLE AND SOURCE CODE

Please refer Here!

SEQUEL OF THIS ARTICLE

With a example of TableCreater, I regretted a little that the benefit of encapsulation and routine processing in initialization and termination is not expressed.
So, I created a sequel of this article.

VBA: THE BENEFIT OF USING CLASS AND THE WAY TO USE, WITH A SIMPLE SAMPLE PART 2

Translations:

"Japanese" |