VBA: HOW TO USE ARRAYS AS A CLASS MEMBER WHEN USING INTERFACE INHERITANCE

Jan 13, 2021 · 3 min read · VBA 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

In VBA we can do object-oriented programming with interface feature.

But since there is one restriction against class variables as described below,
we'll encounter syntax errors when we set an array accessible from the outside as a class variable in a class implementing interface.

In this article I'd like to describe the case and how to avoid it.

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

CREATION ENVIRONMENT

Windows 10 Home(64bit)
MSOffice 2016

WHAT THE CONSTRUCTION IS

ABOUT

There are two team class clsAnalysisTeam and clsNewTeam, both implementing interface clsAbsTeam.

Each team class has an array to store team member's name and the method to get member's name.

CLASS DIAGRAM

Class Diaglam Without Its Variables And Functions

ERROR OCCURED

THE CASE

I wrote each class as follows:

 1Option Explicit
 2
 3'**************************
 4'*Team Class Interface
 5'**************************
 6
 7'Consts
 8
 9'Vars
10
11
12'******************************************************************************************
13'*getter/setter
14'******************************************************************************************
15Public Property Let arrayMenberName(ByVal idx As Long, ByVal name As String)
16
17End Property
18
19Public Property Get arrayMenberName(ByVal idx As Long) As String
20
21End Property
22
23
24
25'Functions
26Public Function getMemberName(ByVal idx As Long) As String
27
28End Function
29

 1Option Explicit
 2
 3Implements clsAbsTeam
 4
 5'**************************
 6'*Team Class:  Analysis Teram
 7'**************************
 8
 9'Consts
10
11'Vars
12
13
14
15'******************************************************************************************
16'*Function : get the menber name of index
17'*Arg      : index number of target member
18'*Return   : the name
19'******************************************************************************************
20Private Function clsAbsTeam_getMemberName(ByVal idx As Long) As String
21    
22    'Consts
23    
24    'Vars
25    
26    '*** here name-returning process is inserted ***
27    
28    
29ExitHandler:
30
31    Exit Function
32        
33End Function

 1Option Explicit
 2
 3Implements clsAbsTeam
 4
 5'**************************
 6'*Team Class:  New Team
 7'**************************
 8
 9'Consts
10
11'Vars
12
13
14
15'******************************************************************************************
16'*Function : get the menber name of index, but new team has no member so it returns 'no member'.
17'*Arg      : index number of target member
18'*Return   : the name
19'******************************************************************************************
20Private Function clsAbsTeam_getMemberName(ByVal idx As Long) As String
21    
22    'Consts
23    
24    'Vars
25    
26    '*** here name-returning process is inserted ***
27    
28    
29ExitHandler:
30
31    Exit Function
32        
33End Function
34

Then, in order to give the class an array to store the member's name,
I added an array variable which scope is Public.

 1
 2'**************************
 3'*Team Class Interface
 4'**************************
 5
 6'Consts
 7
 8'Vars
 9Public arrayMenberName(1 To 6) As String
10
11
12'Functions
13Public Function getMemberName(ByVal idx As Long) As String
14
15End Function
16

Then, the compile error which says,
Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module
, apeears.

Compile Error

CAUSE

The VB6 specification doesn't allow the variables of some types such as an array or user defined types to be set in class module.
Thus we should prepare the mechanism which enables these variables to get accessed from outer modules.

HOW TO AVOID

In interface module I defined only getter/setter functions,
and gave the implementing class an array variable for outer modules to access the array through getter/setter.

 1Option Explicit
 2
 3'**************************
 4'*Team Class Interface
 5'**************************
 6
 7'Consts
 8
 9'Vars
10
11
12'******************************************************************************************
13'*getter/setter
14'******************************************************************************************
15Public Property Let arrayMenberName(ByVal idx As Long, ByVal name As String)
16
17End Property
18
19Public Property Get arrayMenberName(ByVal idx As Long) As String
20
21End Property
22
23
24
25'Functions
26Public Function getMemberName(ByVal idx As Long) As String
27
28End Function
29

 1Option Explicit
 2
 3Implements clsAbsTeam
 4
 5'**************************
 6'*Team Class:  Analysis Teram
 7'**************************
 8
 9'Consts
10
11'Vars
12Private myArrayMenberName(1 To 6) As String 'Max member number of 6
13
14
15
16'******************************************************************************************
17'*getter/setter
18'******************************************************************************************
19Private Property Let clsAbsTeam_arrayMenberName(ByVal idx As Long, ByVal name As String)
20    myArrayMenberName(idx) = name
21End Property
22
23Private Property Get clsAbsTeam_arrayMenberName(ByVal idx As Long) As String
24    clsAbsTeam_arrayMenberName = myArrayMenberName(idx)
25End Property
26
27
28
29'******************************************************************************************
30'*Function : get the menber name of index
31'*Arg      : index number of target member
32'*Return   : the name
33'******************************************************************************************
34Private Function clsAbsTeam_getMemberName(ByVal idx As Long) As String
35    
36    'Consts
37    
38    'Vars
39    
40    clsAbsTeam_getMemberName = "The " & idx & "th team member is " & myArrayMenberName(idx)
41    
42    
43ExitHandler:
44
45    Exit Function
46        
47End Function

 1Option Explicit
 2
 3Implements clsAbsTeam
 4
 5'**************************
 6'*Team Class:  New Team
 7'**************************
 8
 9'Consts
10
11'Vars
12
13
14'******************************************************************************************
15'*getter/setter
16'******************************************************************************************
17Private Property Let clsAbsTeam_arrayMenberName(ByVal idx As Long, ByVal name As String)
18    'nothing to do
19End Property
20
21Private Property Get clsAbsTeam_arrayMenberName(ByVal idx As Long) As String
22    clsAbsTeam_arrayMenberName = "There is no member in this new team."
23End Property
24
25'******************************************************************************************
26'*Function : get the menber name of index, but new team has no member so it returns 'no member'.
27'*Arg      : index number of target member
28'*Return   : the name
29'******************************************************************************************
30Private Function clsAbsTeam_getMemberName(ByVal idx As Long) As String
31    
32    'Consts
33    
34    'Vars
35    
36    clsAbsTeam_getMemberName = "There is no member in this new team."
37    
38    
39ExitHandler:
40
41    Exit Function
42        
43End Function

By writing above, we can avoid the compile error while setting an array to behave as expected in implementing class.

Incidentally, clsNewTeam has no member and the class doesn't have to have an array variable.

SAMPLE

CLASS DIAGRAM REVISIT

Class Diaglam

CALLING FUNCTION CODE

 1Option Explicit
 2
 3'**************************
 4'*Calling Module
 5'**************************
 6
 7
 8'******************************************************************************************
 9'*FUnction:  operation testing function
10'*Arg     :
11'*Return  :  True > normal termination; False > abnormal termination
12'******************************************************************************************
13Public Sub testFunc()
14    
15    'Consts
16    Const FUNC_NAME As String = "testFunc"
17    
18    'Vars
19    Dim team As clsAbsTeam
20    Dim coll As New Collection
21    
22    On Error GoTo ErrorHandler
23
24    'set names for analysis team members
25    Set team = New clsAnalysisTeam
26    team.arrayMenberName(1) = "佐藤"
27    team.arrayMenberName(3) = "Mike"
28    team.arrayMenberName(5) = "Abdallah"
29    
30    'add analysis team
31    'add new team
32    coll.Add team
33    coll.Add New clsNewTeam
34    
35    'output 3rd member's name for each team
36    If Not outputSelectedMemberName(coll, 3) Then GoTo ExitHandler
37
38ExitHandler:
39
40    Exit Sub
41    
42ErrorHandler:
43
44    MsgBox "An error has occurred and the macro will be terminated." & _
45           vbLf & _
46           "Func Name: " & FUNC_NAME & _
47           vbLf & _
48           "Error No." & Err.Number & vbNewLine & _
49           Err.Description, vbCritical, "Interface-Array-Member"
50        
51    GoTo ExitHandler
52        
53End Sub
54
55
56'******************************************************************************************
57'*FUnction: Outputs the name of the member whose number is given by index
58'*Arg     : collection of the team. All of them implements clsAbsTeam.
59'*Arg     : the index number
60'*Return  : True > normal termination; False > abnormal termination
61'******************************************************************************************
62Private Function outputSelectedMemberName(ByVal collTeam As Collection, ByVal idx As Long) As Boolean
63    
64    'Consts
65    Const FUNC_NAME As String = "outputSelectedMemberName"
66    
67    'Vars
68    Dim cntTeam As clsAbsTeam
69    
70    On Error GoTo ErrorHandler
71
72    outputSelectedMemberName = False
73    
74    For Each cntTeam In collTeam
75        Debug.Print cntTeam.getMemberName(idx)
76    Next cntTeam
77
78TruePoint:
79
80    outputSelectedMemberName = True
81
82ExitHandler:
83
84    Exit Function
85    
86ErrorHandler:
87
88    MsgBox "An error has occurred and the macro will be terminated." & _
89           vbLf & _
90           "Func Name: " & FUNC_NAME & _
91           vbLf & _
92           "Error No." & Err.Number & vbNewLine & _
93           Err.Description, vbCritical, "Interface-Array-Member"
94        
95    GoTo ExitHandler
96        
97End Function
98

DEMO

Run testFunc, and the following data will be output in Immediate Window.

1The 3th team member is Mike
2There is no member in this new team.

Translations:

"Japanese" |