Suggestion
Class and Scope

1 Class in Excel

2 Class: example

3 Scope: 1 file
3.1 CallByName
3.2 Object variable
3.2.1 Local scope
3.2.2 Private scope
3.2.3 Public scope

4 Application Scope:Personal.xlsb
4.1 Application.Run
4.2 CallByName
4.3 Workbookfunction
4.4 Reference to Personal.xlsb
4.4.1 Template with reference to Personal.xlsb

5 Application Scope:AddIn
5.1 Create the AddIn
5.2 Load AddIn
5.2.1 In VBA
5.2.2 With dialog window
5.2.3 Manually
5.3 Access to the Classmodule
5.3.1 Application.Run
5.3.2 CallByName
5.3.3 Workbookfunction
5.3.4 Reference to the AddIn
5.3.4.1 Direct Access
5.3.4.2 CallByName
5.3.4.3 Template with reference

6 Users in a network

7 Collaboration

1. Class in Excel

In VBA class modules can standardize and simplify VBA code.
Elements that occur frequently can be defined as a class.
A class is always part of the VBA project of a file.
Next, you need to make sure that the class is accessible to other code.
Therefore it is important to determine what the scope of the class should be.

The range can be:
- 1 single procedure (Macro or Function): procedure level
- several procedures in the same macromodule (of a workbook, a worksheet or a Userform): moduel level
- all procedures in a workbook/file: file level
- all open files in Excel: application level
- several users working on the same server: network level

2. Class example

To illustrate this I created a classmodule called 'c_seqnr'.
. The code in this classmodule generates a unique consecutive number per year.
That class has 1 property: 'Value'
The procedure Property Get Value is the only code in the classmodule.
The procedure Property Get Value assigns a value to the class.
The value of the class is a year, followed by a serial number.
The sequence number to generate is based on the name of a file ending with '_nr'.
For simplicity sake that file is in the same directory that contains the file with the class.

E.g the file's name is G:\OF\202000013_nr
and the class generates the consecutive numbers 202000013, 202000014, 202000015, etc.

The code in the Classmodule
Public Property Get Value()
c00 = ThisWorkbook.path & "\"
c01 = Dir(c00 & Year(Date) & "*_nr")

If c01 = "" Then
Open c00 & Year(Date) & "00000_nr" For Output As 1
Close
Else
Value = Val(c01) + 1
Name c00 & c01 As c00 & Value & "_nr"
End If
End Property
the code explained
- c00 is the directory of the serialnumber file
- c01 is the name of the file
- Value is the number in the file's name plus 1
- checks whether the directory contains a file ending with '_nr' and starting with the current year.
- if such a file does not exist, the code creates it and saves it as: e.g. 202000000_nr, 202100000_nr, etc.
- reads the name of the file
- adds 1 to the number part in the name
- assigns the new number to the classmodule's property 'Value'
- renames the file with a new serial number: 202000001_nr, 202000002_nr, etc.

3. Scope: file

In order to use the classmodule you have to create a new 'instance' of the classmodule with the instruction.
New c_seqnr
You can then read out the value (= property .Value) of that new instance.
Several methods exist to read the resulting value

3.1 CallByName

VBA contains the function CallByName with which you can access/read a property of an object directly.

In this case, the object to be read (the class) is called 'c_seqnr', with the property 'value'.
To create a new instance of the class and to read its value we use:
MsgBox CallByName(New c_seqnr, "Value",vbGet)
or
MsgBox CallByName(New c_seqnr, "Value",2)
You can use this code in every procedure in the document (ThisWorkbook, Sheet, Userform, Macromodule) with the classmodule.

3.2 Object variable

You can also assign the new instance of the class to an object variable.
How you should/can do this depends on the scope that you intend.

3.2.1. Local scope

With Local Scope you can only use the variable within 1 macro or function.
The instruction 'Set ... = New ' assigns a new instance of the classmodule to a local object variable.
Use the object variable to read the value of the classmodule.
Sub M_snb()
Set seqnr = New c_seqnr
MsgBox seqnr.Value
End Sub
example local

3.2.2. Private Scope

In Private scope, different procedures (macros or functions) can make use of the object variable in the same macromodule.
The object variable for the new class instance must be declared on the macromodule level.
To do this, in the 'declaration' part of the macromodule (the space in the macromodule before the macro/function procedures) you put the code with which you assign the classmodule to an object variable.
The scope of a macro module is called 'Private Scope'

. You can do this in the macromodule of
- the Excel file ('ThisWorkbook') and/or
- a worksheet ('Sheet1', 'Sheet2', etc.) and/or
- a Userform ('Userform1', 'Userform2', etc.)
- a general macromodule ('Module1', 'Module2', etc.)

There are two ways to assign the object variable:
- with the instruction 'Dim ... As New ' or
- with the instruction 'Private ... As New'
Both methods are equivalent.

You can subsequently use the object variabele in a macro or in a function.
Dim seqnr As New c_seqnr

Sub M_snb()
MsgBox seqnr.Value
msgbox F_snb
End Sub

Function F_snb()
F_snb = seqnr.value
End Function
or
Private seqnr As New c_seqnr

Sub M_snb()
MsgBox seqnr.Value
Msgbox F_snb
End Sub

Function F_snb()
F_snb = seqnr.value
End Function
example private

3.2.3. Public Scope

In order to give all procedures (macros and functions) in the Excel file access to the same instance of the classmodule, we have to declare a public object variable.
This can only be done in the declaration section of an ordinary macromodule.
This means that at least 1 ordinary macro module is required in the VBP project.
We assign a new instance of the class to that object variable.

Declare the Public object variable in the general macromodule
Public seqnr as New c_seqnr
Now you can use this code anywhere in the VB project of the workbook
Sub M_snb()
MsgBox seqnr.value
End Sub
or
Function F_snb()
F_snb = seqnr.Value
End Function
example public

4 Application Scope: Personal.xlsb

In order to apply the class in every open Excel file you can use the Personal.xlsb file or an Addin.

Create the classmodule 'c_seqnr' in the Personal.xlsb file.

You cannot get direct access to the classmodule in the Personal.xlsb file from another Excel file.

There are 4 methods though to access the classmodule in Personal.xlsb

4.1 Application.Run

Create a normal macromodule in Personal.xlsb.
Create a macro or a function that refers to the classmodule 'c_seqnr' in Personal.xlsb.

We name this macromodule 'M_seqnr'
It encompasses this code:
Function F_seqnr()
With New c_seqnr
F_seqnr = .Value
End With
End Function

Now we can call this function from every loaded Excel file using:
Sub M_snb()
MsgBox Application.Run("personal.xlsb!M_seqnr.F_seqnr")
End Sub
or
Sub M_snb()
MsgBox Application.Run("personal.xlsb!F_seqnr")
End Sub

4.2 CallbyName

Create a new workbookfunction in the macromodule of 'ThisWorkbook' of the Personal.xlsb file.
(I owe this suggestion to DerHoepp in http://www.office-loesung.de)
Function seqnr()
Set seqnr = New c_seqnr
End Function
This function 'seqnr' creates a new instance of the class 'c_seqnr' and assigns the new instance to object variable 'seqnr'.

We can call this function in every loaded Excel file using:
Sub M_snb()
MsgBox CallByName(workbooks(1).seqnr,"Value",2)
End Sub
or
Sub M_snb()
MsgBox CallByName(workbooks("personal.xlsb").seqnr,"Value",vbGet)
End Sub

4.3 Workbookfunction

Create a new workbookfunction in the macromodule of 'ThisWorkbook' of the Personal.xlsb file.
(I owe this suggestion to DerHoepp in http://www.office-loesung.de)
Function seqnr()
Set seqnr = New c_seqnr
End Function
This function 'seqnr' creates a new instance of the class 'c_seqnr' and assigns the new instance to object variable 'seqnr'.

We can call this function in every loaded Excel file using:
Sub M_snb()
MsgBox Workbooks("personal.xlsb").seqnr.Value
End Sub
example personal

4.4 Reference to Personal.xlsb

In the VBEditor you can specify which VBA libraries that file uses.
In VBA these are called 'references'.
The references are stored in each file as file properties.
An Excel file can be regarded as a VBA library.
This also applies to the personal.xlsb file.
. You only need to add a reference once since the reference remains as a property of the file: even after closing.

References must have unique names.
Excel uses the VBProject name of a file as the reference name.
In an Excel file, the VBProject has the name 'VBProject' by default.
This also applies to the Personal.xlsb file.
. As a result, name conflicts quickly arise.
So you'd better start changing the name of the VBProject in Personal.xlsb and/or of the file with the reference.

Steps

1. change the name of the VBProject in Personal.xlsb
Workbooks(1).VBProject.Name = "VB_000"
2. change the name of the VBProject in the reference-containing file:
ThisWorkbook.VBProject.Name = "VB_001"
3. Add the Personal.xlsb file as a reference to the open Workbook:
ThisWorkbook.VBProject.References.AddFromFile Workbooks(1).FullName
4. Create the classmodule 'c_seqnr' in the Personal.xlsb file.

5. Create a normal macromodule in Personal.xlsb: e.g. 'M_seqnr'

6. Declare in 'M_seqnr' a public Object variable, containing a new instance of the classmodule 'c_seqnr'.
Public v_seqnr as New c_seqnr
Read from the file with the reference to Personal.xlsb: Public Variable v_seqnr
Sub M_snb_pv()
MsgBox v_seqnr.Value

MsgBox VB_000.v_seqnr.Value
End Sub
Public Function
Sub M_snb_pf()
MsgBox F_seqnr

MsgBox VB_000.F_seqnr
End Sub
Workbookfunction
Sub M_snb_wbf()
MsgBox seqnr.Value

MsgBox VB_000.ThisWorkbook.seqnr.Value
End Sub
example reference personal

4.4.1 Template with the reference

To avoid having to create a reference to the Personal.xlsb file in a new Excel file, you can save an Ecel file with the reference and use it as a template.

If you save the file as an Excel template .xltm, Excel automatically creates a copy.
If you save the file as an .xlsm or .xlsb file, you will get a copy using the VBA method Workbooks.Add.
The reference to the Personal.xlsb file is 'inherited' to the new file.

5 Addin

An Addin can serve as alternative to the Personal.xlsb.
A loaded Addin is invisible.
The Application (Excel) stores installed Addins aa a property of the application.
The next time Excel is loaded the Addin is loaded too.

5.1 Create the Addin

Create the Classmodule 'c_seqnr' in the file:
Public Property Get Value()
c00 = ThisWorkbook.path & "\"
c01 = Dir(c00 & Year(Date) & "*_nr")

If c01 = "" Then
Open c00 & Year(Date) & "00000_nr" For Output As 1
Close
Else
Value = Val(c01) + 1
Name c00 & c01 As c00 & Value & "_nr"
End If
End Property
Save the file as AddIn (.xlam e.g. "seqnr.xlam"), property IsAddin = True

example addin

5.2 Addin loading

5.2.1 in VBA

Sub M_snb()
c00 = "G:\OF\"
AddIns.Add(c00 & "seqnr.xlam", False).Installed = True
End Sub

5.2.1 in window

Sub M_snb()
Application.Dialogs(321).Show
End Sub

5.2.3 manually

Use the CcommandTab 'Developers', option Add-Ins

5.3 access to the classmodule

Similar to the Personal.xlsb file you cant' access the classmodule directly.
4 Methods provide the means.

5.3.1 Application.Run

Create a normal macromodule in seqnr.xlam.
We name this macromodule 'M_seqnr'
Create a macro or a function that refers to the classmodule 'c_seqnr' in seqnr.xlam.

It encompasses this code:
Function F_seqnr()
With New c_seqnr
F_snb = .Value
End With
End Function
Now we can call this function from every loaded Excel file using:
Sub M_snb()
MsgBox Application.Run("seqnr.xlam!M_seqnr.F_seqnr")
End Sub

5.3.2 CallByName

Create a new workbookfunction in the macromodule of 'ThisWorkbook' of the seqnr.xlam file.
(I owe this suggestion to DerHoepp in http://www.office-loesung.de)
Function seqnr()
Set seqnr = New c_seqnr
End Function
This function 'seqnr' creates a new instance of the class 'c_seqnr' and assigns the new instance to object variable 'seqnr'.

We can call this function in every loaded Excel file using:
MsgBox CallByName(Workbooks("seqnr.xlam").seqnr, "Value", 2)

5.3.3 Workbookfunction

Create a new workbookfunction in the macromodule of 'ThisWorkbook' of the seqnr.xlam file.
(I owe this suggestion to DerHoepp in http://www.office-loesung.de)
Function seqnr()
Set seqnr = New seqnr
End Function
This function 'seqnr' creates a new instance of the class 'c_seqnr' and assigns the new instance to object variable 'seqnr'.

We can call this function in every loaded Excel file using:
Sub M_snb()
MsgBox Workbooks("seqnr.xlam").seqnr.Value
End Sub
or
Sub M_snb()
MsgBox Workbooks(AddIns("seqnr").Name).seqnr.Value
End Sub
This method isn't succesful:
Sub M_snb()
MsgBox AddIns("seqnr").seqnr.Value
End Sub

5.3.4 Reference to the AddIn

In the VBEditor you can specify which VBA libraries that file uses.
In VBA these are called 'references'.
The references are stored in each file as file properties.
An Excel file can be regarded as a VBA library.
This also applies to the Addin 'seqnr.xlam' file.
. You only need to add a reference once since the reference remains a property of the file: even after closing.

References must have unique names.
Excel uses the VBProject name of a file as the reference name.
In an Excel file, the VBProject has the name 'VBProject' by default.
This also applies to the seqnr.xlam file.
. As a result, name conflicts quickly arise.
So you'd better start changing the name of the VBProject in seqnr.xlam and/or of the file with the reference.

Steps

1. change the name of the VBProject in seqnr.xlam
Workbooks("seqnr.xlam").VBProject.Name = "VB_000"
2. change the name of the VBProject in the reference-containing file:
ThisWorkbook.VBProject.Name = "VB_001"
3. Add the AddIn seqnr.xlam file as a reference to the open Workbook:
ThisWorkbook.VBProject.References.AddFromFile Workbooks("seqnr.xlam").FullName
4. Create the classmodule 'c_seqnr' in the Addin seqnr.xlam file.

5. Create a normal macromodule in Addin seqnr.xlam: e.g. 'M_seqnr'

6. Declare in 'M_seqnr' a public Object variable, containing a new instance of the classmodule 'c_seqnr'.
Public v_seqnr as New c_seqnr
Read the public variable in the file with the reference to seqnr.xlam.

5.3.4.1 directly

In the list of references in the VBEditor you will find the VBProject name 'VB_000' in the AddIn 'seqnr.xlam'.
You can use this reference name 'VB_000' instead of Workbooks("seqnr.xlam").
To get accees to the classmodule we use the Public variable 'v_seqnr' or the workbookfunction 'seqnr'.

Read the value of variable 'v_seqnr'
Sub M_snb()
MsgBox VB_000.v_seqnr.value
End Sub
Read the value of workbookfunction 'seqnr'
Sub M_snb()
MsgBox VB_000.ThisWorkbook.seqnr.value
End Sub
Read the value of function 'F_seqnr' in macro module 'M_seqnr'
Sub M_snb()
MsgBox VB_000.F_seqnr
End Sub

5.3.4.2 CallByName

Read the Public variable
Sub M_snb()
MsgBox CallByName(VB_000.v_seqnr, "value", 2)
MsgBox CallByName(VB_000.v_seqnr, "value", vbGet)

End Sub
Sub M_snb()
MsgBox CallByName(Workbooks("seqnr.xlam").v_seqnr, "value", 2)
MsgBox CallByName(Workbooks("seqnr.xlam").v_seqnr, "value", vbGet)
End Sub
Read the Workbookfunction
Sub M_snb()
MsgBox CallByName(VB_000.ThisWorkbook.seqnr, "value", 2)
MsgBox CallByName(VB_000.ThisWorkbook.seqnr, "value", vbGet)
End Sub
Sub M_snb()
MsgBox CallByName(Workbooks("seqnr.xlam"). ThisWorkbook.seqnr, "value", 2)
MsgBox CallByName(Workbooks(AddIns("seqnr").Name).ThisWorkbook.seqnr, "value", 2)
End Sub

5.3.4.3 Template with reference

To avoid having to create a reference to the AddIn seqnr.xlam in every new Excel file, you can save an Excel file with the reference and use it as a template.

Save the Excel file and its reference as .xlsb or .xlsm file.
Open a copy of this file:
Workbooks.Add "G:\OF\snb_ref.xlsb"
The reference to the AddIn 'seqnr.xlam' is 'inherited' to the new file.

Save the Excel file and its referenca as a template (.xltm)
Open a new file based on this template
Workbooks.open "G:\OF\snb_ref.xltm"
The reference to the AddIn 'seqnr.xlam' is 'inherited' to the new file.

6 Users in a network

To allow different users in a network to use the same classmodule, use an AddIn.
. Save the file as AddIn (.xlam), property IsAddin = True in a location accessible to all intended users.
Set the property of the AddIn to 'read-only', so that each user uses a copy of the AddIn.

Every user can now add the AddIn to Excel.

In this way, the Addin only needs to be maintained in one place.

7. Cooperation

Above we have discussed the different ways in which you can give 1 or more users access to a classmodule.
This is independent of the content of the classmodule.

This example creates a unique serial number per year.
An issue that is often raised in forums about Excel and/or VBA.

The proposed method in the class in this example is to read the name of a file and change the name of a file without opening it.
As a result, many users can use this method at the same time.

The choice of where to store the serial number file therefore depends primarily on which users the file should be accessible/available to.
The file must be stored in a directory available to all intended users.
Every user must have the rights to read and change the name of the file.

Changing the 'extension' of the serial number file can result in an unlimited number of different individual types of serial numbers (e.g. for customers, invoices, credit notes, staff members, members, participants, etc.)

. The core is always which directory contains the serial number file.
In the example code it is sn(0).

sn = Array(ThisWorkbook.path & "\", Year(Date) & "_")
. If the AddIn is on a common server, the number file is in the same server directory.
You can change this at will, provided that the access rights of the intended users are guaranteed.