Suggesties
Klasse en reikwijdte

1 Klasse in Excel

2 Voorbeeld van een klasse

3 Reikwijdte: bestand
3.1 CallByName
3.2 Objectvariabele
3.2.1 Local scope
3.2.2 Private scope
3.2.3 Public scope

4 Personal.xlsb
4.1 Application.Run
4.2 CallByName
4.3 Werkboekfunktie
4.4 Referentie naar Personal

5 AddIn
5.1 Aanmaak AddIn
5.2 Addin laden
5.2.1 met VBA
5.2.2 met scherm
5.2.3 handmatig
5.3 toegang tot de klassemodule
5.3.1 Application.Run
5.3.2 CallByName
5.3.3 Werkboekfunktie
5.3.4 Referentie naar AddIn
5.3.4.1 Rechtstreeks
5.3.4.2 CallByName
5.3.4.3 Sjabloon met referentie

6 Gebruikers in een netwerk

7 Samenwerken

1. Klasse in Excel

Met VBA-klassemodules kun je VBA-code standaardiseren en vereenvoudigen.
Elementen die vaak voorkomen kun je als een klasse definiëren.
Een klasse maakt altijd deel uit van het VBAProject van een bestand.
Vervolgens moet je ervoor zorgen dat die klasse voor andere code toegankelijk is.
Dan is het van belang te bepalen wat de reikwijdte van de klasse moet zijn.

De reikwijdte kan zijn:
- 1 enkele procedure (Macro of Functie).
- verschillende procedures in eenzelfde macromodule (van een werkboek, een werkblad of een Userform).
- alle procedures van een werkboek
- alle bestanden die je in Excel opent/maakt
- diverse gebruikers die op eenzelfde server werken

2. Voorbeeld van een klasse

Om dit te illustreren heb ik een klassemodule gemaakt met de naam 'c_volgnr'.
De code in deze klassemodule genereert een uniek oplopend volgnummer per jaar.
Die klasse heeft 1 eigenschap: 'Value'
De procedure Property Get Value is de enige code in de klassemodule.
De procedure Property Get Value wijst een waarde toe aan de klasse.
De waarde van de klasse is een volgnummer, dat begint met het huidige jaar.
Het te genereren volgnummer is gebaseerd op de naam van een bestand eindigend op '_nr'
Voor de eenvoud van het voorbeeld staat dat bestand in dezelfde directory als het bestand met de klasse.

Het bestand heet bijv. G:\OF\202000013_nr
en genereert de volgnummers 202000013, 202000014, 202000015, etc.

De code van de klassemodule
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
De code in de klasse
- c00 is de directory met het volgnummerbestand
- c01 is het huidige volgnummerbestand (of het ontbreken ervan)
- Value is de eigenschap van de klassemodule

- controleert of de directory een bestand bevat eindigend op '_nr' en beginnend met het huidige jaar.
- als zo'n bestand niet bestaat maakt de code het aan en slaat het op als: bijv. 202000000_nr, 202100000_nr, etc.
- verhoogt het getal in de naam in c01 met 1
- wijst dit getal toe aan de Value-eigenschap van de klassemodule
- hernoemt het bestand met een nieuw volgnummer: 202000001_nr, 202000002_nr, etc

3. Reikwijdte: bestand

Om gebruik te kunnen maken van de klassemodule dien je een nieuwe 'instantie' van de klassemodule te maken met de instructie

New c_volgnr
De waarde (= eigenschap .Value) van die nieuwe instantie kun je vervolgens uitlezen.
Daarvoor bestaan verschillende methoden.

3.1 CallByName

VBA bevat de funktie CallByName waarmee je een eigenschap van een object direkt kunt benaderen/uitlezen

In dit geval heet het uit te lezen object (de klasse) 'volgnr', met de eigenschap 'value'.
Voor de aanmaak van een nieuwe instantie van de klasse gebruiken we:
MsgBox CallByName(New c_volgnr,"Value",vbGet)
of
MsgBox CallByName(New c_volgnr,"Value",2)
Deze code kun je in iedere procedure in het document (ThisWorkbook, Sheet, Userform , Macromodule) met de klassemodule gebruiken.

3.2 Objectvariabele

Je kunt de nieuwe instantie van de klasse ook aan een objectvariabele toewijzen.
Hoe je dit moet/kunt doen is afhankelijk van de reikwijdte (scope) die je beoogt.

3.2.1. Local scope

Bij Local Scope kun je de variabele alleen binnen 1 macro of funktie gebruiken.
Wijs met de instructie 'Set ... = New ' een nieuwe instantie van de klassemodule toe aan een lokale objectvariabele.
Lees via de objectvariabele de waarde van de klassemodule.
Sub M_snb()
Set volgnr = New c_volgnr
MsgBox volgnr.Value
End Sub
voorbeeld local

3.2.2. Private Scope

Bij Private scope kunnen verschillende procedures (macros' of funkties) in dezelfde macromodule van de objectvariabele gebruik maken.
De objectvariable voor de nieuwe klasse-instantie dien je op macromodulenivo te declareren.
Daarvoor zet je in het 'declaratie'deel van de macromodule (de regels van de macromodule vóór de macro/funktie-procedures) de code waarmee je de klassemodule toewijst aan een objectvariabele.
De reikwijdte (Scope) van een macromodule heet 'Private Scope'

Je kunt dit doen in de macromodule van
- het Excel-bestand ('ThisWorkbook') en/of
- een werkblad ('Sheet1', 'Sheet2', etc.) en/of
- een Userform ('Userform1', 'Userform2', etc.)
- een algemene macromodule ('Module1', 'Module2', etc.)

Er zijn twee manieren om de objectvariabele toe te wijzen:
- met de instructie 'Dim ... As New ' of
- met de instructie 'Private ... As New'
Beide manieren zijn gelijkwaardig.

Je kunt de objectvariabele vervolgens gebruiken in een macro of een funktie.

Dim volgnr As New c_volgnr

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

Function F_snb()
F_snb=volgnr.value
End Function
of
Privatevolgnrt As New c_volgnr

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

Function F_snb()
F_snb=volgnr.value
End Function
voorbeeld private

3.2.3. Public Scope

Om alle procedures (macro's en funkties) in het Excelbestand dezelfde instantie van de klassemodule te laten gebruiken moeten we een publieke objectvariabele declareren.
Dat kan alleen in het declaratiedeel van een gewone macromodule.
Er is hiervoor dus minimaal 1 gewone macromodule nodig in het VBProject.
Aan die objectvariabele wijzen we een nieuwe instantie van de klasse toe.

Declareer de Public object variabele in de algemene macromodule
Public volgnr as New c_volgnr
Nu kun je op willekeurig welke plek in het VBproject van het werkboek volstaan met de code:
Sub M_snb()
MsgBox volgnr.value
End Sub
of
Function F_snb()
F_snb=volgnr.Value
End Function
voorbeeld public

4 Personal.xlsb

Om de klasse te gebruiken in ieder geopend Excelbestand kun je gebruik maken van het personal.xlsb bestand of van een AddIn.

Zet de klassemodule volgnr in het personal.xlsb bestand.

Vanuit een Excelbestand kun je de klassemodule in het Personal.xlsb bestand niet rechtstreeks benaderen.

Er zijn 4 methodes om toegang te kunnen krijgen tot de klassemodule in personal.xlsb

4.1 Application.Run

Maak in Personal.xlsb een algemene macromodule met een macro of een funktie die gebruik maakt van de klassemodule.

We noemen deze macromodule M_volg
Deze bevat de volgende code:
Function F_volgnr()
With New c_volgnr
F_volgnr = .Value
End With
End Function

Vanuit ieder geopend werkboek in Excel kunnen we deze funktie aanroepen met:
Sub M_snb()
MsgBox Application.Run("personal.xlsb!M_volg.F_volgnr")
End Sub

4.2 CallbyName

Voeg in het personal.xlsb bestand een methode toe in de macromodule van 'ThisWorkbook'.
(Met dank voor deze suggestie aan DerHoepp op http://www.office-loesung.de)
Function volgnr()
Set volgnr = New c_volgnr
End Function
De funktie volgnr maakt een nieuwe instantie aan van de klasse 'c_volgnr' en zet die nieuwe instantie in de objectvariabele 'volgnr'.
Vanuit ieder geopend werkboek in Excel kunnen we deze funktie aanroepen met:
Sub M_snb()
MsgBox CallByName(workbooks(1).volgnr,"Value",2)
End Sub
of
Sub M_snb()
MsgBox CallByName(workbooks("personal.xlsb").volgnr,"Value",vbGet)
End Sub

4.3 Werkboekfunktie

Voeg in het personal.xlsb bestand een methode toe in de macromodule van 'ThisWorkbook'.
(Met dank voor deze suggestie aan DerHoepp op http://www.office-loesung.de)
Function volgnr()
Set volgnr = New c_volgnr
End Function
De funktie volgnr maakt een nieuwe instantie aan van de klasse 'volgnr' en zet die nieuwe instantie in de objectvariabele 'volgnr'.

Vanuit ieder geopend werkboek in Excel kunnen we deze funktie aanroepen met:
Sub M_snb()
MsgBox Workbooks("personal.xlsb").volgnr.Value
End Sub
voorbeeld personal

4.4 Referentie naar personal.xlsb

In de VBEditor kun je per bestand aangeven van welke VBA-bibliotheken dat bestand gebruik maakt.
In VBA heten dat 'referenties'.
De referenties worden in ieder bestand als eigenschap opgeslagen.
Ook een Excelbestand kan beschouwd worden als een VBA-bibliotheek.
Dat geldt dus ook voor het personal.xlsb bestand.
Je hoeft een referentie maar éénmaal aan een bestand toe te voegen, want de referentie blijft als eigenschap van het bestand behouden: ook na sluiting.

Referenties moeten unieke namen hebben.
Excel gebruikt de naam van het VBProject van een bestand als referentienaam.
In een Excel-bestand heeft het VBProject standaard de naam 'VBProject'.
Dat geldt ook voor het personal.xlsb bestand.
Daardoor ontstaan er snel naamkonflikten.
Wijzig dus eerst de naam van het VBProject van personal.xlsb en/of van het bestand met de referentie

wijzig de naam van het VBProject in Personal.xlsb
Workbooks(1).VBProject.Name = "VB_000"
wijzig de naam van het VBProject in het bestand met de referentie:
ThisWorkbook.VBProject.Name = "VB_001"
In VBA kun je het personal.xlsb bestand als referentie toevoegen met deze code:
ThisWorkbook.VBProject.References.AddFromFile Workbooks(1).FullName
Zet de klassemodule ' c_volgnr' in het Personal.xlsb bestand.

Declareer vervolgens in een gewone macromodule in Personal.xlsb een public Objectvariabele die een nieuwe instantie van de klassemodule 'c_volgnr' bevat.
Public volgnr as New c_volgnr
Nu kun je vanuit het bestand met de referentie naar Personal.xlsb de waarde van de klassemodule oproepen met deze code:
Sub M_snb()
Msgbox volgnr.Value
End Sub
ook de funktie Callbyname werkt nu:
Sub M_snb()
MsgBox CallByName(volgnr, "value", 2)
End Sub
Om niet steeds met de hand of met VBA in een nieuw Excelbestand een referentie naar het Personal.xlsb bestand te hoeven leggen kun je een Ecelbestand met de referentie opslaan en als sjabloon gebruiken.

Als je het bestand als Excelsjabloon .xltm opslaat maakt Excel automatisch een kopie.
Als je het bestand niet als Excelsjabloon opslaat krijg je een kopie met de VBA-methode Workbooks.Add.
De referentie naar het Personal.xlsb bestand wordt 'vererfd' aan het nieuwe bestand.

5 Addin

In plaats van Personal.xlsb kun je een AddIn gebruiken.
Een Addin wordt niet-zichtbaar geladen.
De AddIn wordt als eigenschap van de applicatie (Excel) opgeslagen.
Een volgende keer dat Excel gestart wordt, wordt de Addin ook automatisch geladen.

5.1 aanmaak Addin

In de AddIn zet je de klassemodule 'c_volgnr' met de code:
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
Sla het bestand op als AddIn (.xlam), eigenschap IsAddin = True

voorbeeld addin

5.2 Addin laden

5.2.1 met VBA

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

5.2.1 met scherm

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

5.2.3 handmatig

Gebruik het tabblad 'ontwikkelaars' (Developers), optie Add-Ins

5.3 toegang tot de klassemodule

Evenals bij het personal.xlsb bestand kun je de klassemodule niet rechtstreeks benaderen.
Er zijn 4 methodes om dat toch te realiseren.

5.3.1 Application.Run

Application.Run kan een macro of funktie in een ander geopend Excel-bestand uit laten voeren.
Zet in de AddIn een algemene macromodule.
We noemen deze macromodule M_volg
Zet hierin een macro of een funktie die gebruik maakt van de klassemodule.
Deze bevat de volgende funktie:
Function F_volgnr()
With New c_volgnr
F_snb = .Value
End With
End Function
Vanuit ieder geopend werkboek in Excel kunnen we deze funktie aanroepen met:
Sub M_snb()
MsgBox Application.Run("volgnr.xlam!M_volg.F_volgnr")
End Sub

5.3.2 CallByName

In de macromodule van 'ThisWorkbook' van het Addin bestand zet je de werkboekfunktie:
Function volgnr()
Set volgnr = New c_volgnr
End Function
De funktie CallByName funktioneert via deze werkboekfunktie in 'ThisWorkbook':
MsgBox CallByName(Workbooks("volgnr.xlam").volgnr, "Value", 2)

5.3.3 Werkboekfunktie

In de macromodule van 'ThisWorkbook' van het Addin bestand zet je de funktie:
Function volgnr()
Set volgnr = New volgnr
End Function
In een Excelbestand kun je de klassemodule via deze werkboekfunktie in 'ThisWorkbook' aanroepen met
Sub M_snb()
MsgBox Workbooks("volgnr.xlam").volgnr.Value
End Sub
of
Sub M_snb()
MsgBox Workbooks(AddIns("volgnr").Name).volgnr.Value
End Sub
Het lukt niet met:
Sub M_snb()
MsgBox AddIns("volgnr").volgnr.Value
End Sub

5.3.4 Referentie naar AddIn

In de VBEditor kun je per bestand aangeven van welke VBA-bibliotheken dat bestand gebruik maakt.
In VBA heten dat 'referenties'.
De referenties worden in ieder bestand als eigenschap opgeslagen.
Ook een Excelbestand kan beschouwd worden als een VBA-bibliotheek.
Dus ook voor het AddIn bestand.
Je hoeft een referentie maar éénmaal aan een bestand toe te voegen, want de referentie blijft als eigenschap van het bestand behouden: ook na sluiting.

Referenties moeten unieke namen hebben.
Excel gebruikt de naam van het VBProject van een bestand als referentienaam.
In een Excel-bestand heeft het VBProject standaard de naam 'VBProject'.
Dat geldt ook voor een Excel Addin-bestand.
Daardoor ontstaan er snel naamkonflikten.
Wijzig dus eerst de naam van het VBProject van de AddIn en/of van het bestand met de referentie

Wijzig de naam van het VBProject in AddIn "volgnr.xlam" met VBA:
Workbooks("volgnr.xlam").VBProject.Name = "VB_000"
Wijzig in VBA de naam van het VBProject in het bestand met de referentie:

ThisWorkbook.VBProject.Name = "VB_001"
Voeg In VBA de AddIn als referentie toe met deze code:
ThisWorkbook.VBProject.References.AddFromFile Workbooks("volgnr.xlam").FullName
Zet de klassemodule 'c_volgnr' in het AddIn bestand.

Maak een gewone macromodule in het AddIn-bestand.
Declareer vervolgens in deze macromodule in de AddIn een public Objectvariabele die een nieuwe instantie van de klassemodule 'c_volgnr' bevat.
Public v_volgnr as New c_volgnr
Deze Public variabele maakt de klassemodule vanuit het Excelbestand met de referentie toegankelijk.

5.3.4.1 rechtstreeks

In de VBEditor tref je in de lijst met referenties de naam aan van het VBProject van de AddIn; in dit geval VB_000.
Nu kun je in VBA deze referentienaam 'VB_000' gebruiken voor de AddIn in plaats van Workbooks("volgnr.xlam").
De klassemodule benaderen we via de Public variabele 'v_volgnr' of via de werkboekfunktie 'volgnr'.

Lees de variabele 'v_volgnr'
Sub M_snb()
MsgBox VB_000.v_volgnr.value
End Sub
Lees de werkboekfunktie 'volgnr'
Sub M_snb()
MsgBox VB_000.ThisWorkbook.volgnr.value
End Sub

5.3.4.2 Callbyname

Sub M_snb()
MsgBox CallByName(VB_000.m_volgnr, "value", 2)
MsgBox CallByName(VB_000.Thisworkbook.volgnr, "value", 2)

MsgBox CallByName(Workbooks("volgnr.xlam").volgnr, "value", 2)
MsgBox CallByName(Workbooks(AddIns("volgnr").Name).volgnr, "value", 2)
End Sub

5.3.4.3 Sjabloon met referentie

Om niet steeds met de hand of met VBA in een nieuw Excelbestand een referentie naar een AddIn te hoeven leggen kun je een Excelbestand met de referentie opslaan en als sjabloon gebruiken.

Sla het Excel-bestand met de referentie op als .xlsb of .xlsm bestand.
Maak een kopie van het bestand met
Workbooks.Add "G:\OF\snb_ref.xlsb"
Sla het Excel-bestand met de referentie op als .xltm-bestand
Maak een kopie van het bestand met
Workbooks.add "G:\OF\snb_ref.xltm"
In al deze gevallen 'erft' het nieuwe bestand de referentie naar de AddIn uit het sjabloonbestand.

6 Gebruikers in een netwerk

Om verschillende gebruikers in een netwerk van dezelfde klassemodule gebruik te laten maken gebruik je een AddIn.
Sla het bestand op als AddIn (.xlam), eigenschap IsAddin = True op een lokatie die voor alle beoogde gebruikers toegankelijk is.
Zet de eigenschap van de AddIn op 'read-only', zodat iedere gebruiker een kopie van de AddIn gebruikt.

Iedere gebruiker kan nu de AddIn aan Excel toevoegen.

De Addin hoeft zo slechts op 1 plaats onderhouden te worden.

7. Samenwerken

Hierboven is aandacht besteed op welke verschillende manieren je 1 of meer gebruikers toegang kunt geven tot een klassemodule.
Dat staat los van de inhoud van de klassemodule.

In dit voorbeeld gaat het om een uniek volgnummer per jaar.
Een kwestie die vaak aan de orde komt in fora over Excel en/of VBA.

De voorgestelde methode in de klasse in dit voorbeeld is het lezen van de naam van een bestand en het wijzigen van de naam van een bestand zonder het te openen.
Daardoor kunnen vele gebruikers tegelijkertijd van deze methode gebruik maken.

De keuze waar dat volgnummerbestand opgeslagen moet worden is dan ook primair afhankelijk van de vraag voor welke gebruikers het bestand toegankelijk/beschikbaar moet zijn.
Het bestand moet opgeslagen worden in een directory die voor alle beoogde gebruikers beschikbaar is.
Iedere gebruiker moet de rechten hebben de naam van het bestand te lezen en te wijzigen.

Wijziging van de 'extensie' van het volgnummerbestand kan een onbeperkt aantal verschillende afzonderlijke soorten volgnummers opleveren (voor bijv. klanten, fakturen, creditfacturen, personeelsleden. leden, deelnemers etc.)

De kern is steeds welke directory het volgnummerbestand bevat.
In de voorbeeldcode is dat sn(0).

sn = Array(ThisWorkbook.path & "\", Year(Date) & "_")
Als de AddIn zich op de gemeenschappelijke server bevindt, bevindt het nummerbestand zich in dezelfde serverdirectory.
Je kunt dit naar believen aanpassen, mits de toegangsrechten van de beoogde gebruikers gegarandeerd zijn.