Tips en trucs voor Excel (v8 Jan 2024)

06-Jan-24: Toegevoegd een progress bar.

06-Jan-24: Toegevoegd een sleep method aan de timer class.

Inhoud

• Een klasse gebruiken voor persistente openbare variabelen
• Meet de executie tijd van programma code
• Versnel de uitvoering van VBA-code / macro
• Laat de voortgang zien tijdens executie van code
• Bewaar de waarde van een formulier regelt (dwz checkbox of textbox) tussen sessies
• Positie van (pop-up) venster op het scherm en werkblad
• Arrays gebruiken om het lezen en schrijven naar werkbladcellen te versnellen
• Een menu toevoegen aan de ribbon
• Eigen aangepaste functies maken
• Het verschil tussen HIDE en UNLOAD van een formulier
• Gebeurtenissen die worden geactiveerd door de formulierbesturingselementen
• Reguliere expressies met door de gebruiker gedefinieerde functies
• Uitlezen filter settings tabel

Een klasse gebruiken voor persistente publieke variabelen

In mijn Excel code maak ik vaak gebruik van variabelen die ik dan gebruik in alle bladen en modules. Ik wil bijvoorbeeld het nummer van de laatste regel van een tabel opslaan. Ik definiëer deze publike globale variabelen in een module. In een module die ik "InitPublics" noem, is er bijvoorbeeld een regel:

Public LastRowOfTable as Integer

Het probleem waarmee ik hiermee werd geconfronteerd, is dat deze variabelen hun waarde verliezen zodra de code wordt onderbroken door een runtime fout. Vooral tijdens het testen en foutzoeken is dit vervelend. Als je daarintegen een publieke konstante gebruikt, zoals:

Public LastRowOfTable as Integer = 10

Dan zal de waarde niet verloren gaan tijdens een runtime fout, maar als de lengte van de tabel verandert, kunt u de waarde van de konstante tijdens runtime niet wijzigen.

Een mogelijke oplossing hiervoor is om een ​​zg klasse attribuut als een (publieke) variabele te gebruiken. Een klasse attribuut heeft GET- en LET-methoden die worden geactiveerd tijdens het lezen en schrijven van de attribuut. U kunt deze methoden gebruiken om de waarde van de attribute / variabele te manipuleren. Een ander groot voordeel is dat je dynamische konstanten kunt maken. Dit betekent dat een variabele alleen door de code kan worden gelezen. Alleen binnen de klasse krijgt de variabele een waarde. De klasse voorkomt dus niet dat variabele waarden verloren gaan tijdens runtime fouten, maar het kan de variabelen initialiseren voordat ze daadwerkelijk worden gebruikt in de code. Als je eenmaal weet hoe je dit moet doen, is het werken met deze klassen vrij eenvoudig. Als demonstratie kunt u de volgende code in uw project hebben in een klassemodule genaamd ClassGlobalVariables:

Private pDemoReadProperty As Integer
Private pDemoReadWriteProperty As Integer

' DemoReadProperty
Public Property Get DemoReadProperty() As Integer
	If pDemoReadProperty > 0 Then pDemoReadProperty = pDemoReadProperty - 1
	If pDemoReadProperty = 0 Then pDemoReadProperty = 10
	DemoReadProperty = pDemoReadProperty
End Property

' DemoReadWriteProperty
Public Property Get DemoReadWriteProperty() As Integer
	If pDemoReadWriteProperty = 0 Then pDemoReadWriteProperty = 100
	DemoReadWriteProperty = pDemoReadWriteProperty
End Property
Public Property Let DemoReadWriteProperty(val As Integer)
	pDemoReadWriteProperty = val
End Property

Voor de demonstratie zijn er slechts 2 variabelen. Eén die alleen lezen is en één die zowel lezen als schrijven is. De GET procedure wordt geactiveerd wanneer de waarde van de variabele in de code wordt gelezen. De LET procedure wordt geactiveerd wanneer de variabele in de code wordt gedefiniëerd. Zoals u kunt zien, heeft de attrribuut 'alleen lezen' ook alleen een GET methode. In de GET methode van de alleen lezen attribuut wordt de waarde ingesteld op 10 als deze nog niet is geïnitialiseerd. Dit initiëert de variabele tijdens het eerste gebruik in de code, maar stelt deze ook opnieuw in als de waarde verloren gaat tijdens een runtime fout. Als demo heb ik een regel toegevoegd waar de waarde van de alleen lezen variabele wordt verlaagd met 1 elke keer dat de variabele wordt gelezen in de code. Dus de waarde verandert zonder dat deze in de code wordt veranderd.

Public GlobalVar As New ClassGlobalVariables

Sub DemoGlobalVars()

Dim i As Integer
Dim c As String

' read inital value of read only property
i = GlobalVar.DemoReadProperty

' read changed value read only property
i = GlobalVar.DemoReadProperty

' next line creates a run time error causing variables to loose their values
On Error Resume Next
i = i * c
On Error GoTo 0

' read again inital value of read only property
i = GlobalVar.DemoReadProperty

' read inital value of read and write property
i = GlobalVar.DemoReadWriteProperty

' next line will give a compiller error "Can't assign to read only property". Remove the comment to check this:
GlobalVar.DemoReadProperty = i - 1

' this sets the read write property
GlobalVar.DemoReadWriteProperty = i - 1

End Sub

Wanneer u de variabelen in uw code gebruikt, moet u de variabelenaam vooraf laten gaan door de naam GlobalVar gevolgd door een punt. Excel geeft je een dropdown van de gedefinieerde eigenschappen tijdens het typen, zodat het sneller kan worden ingevoerd en er minder kans is op typefouten. De eerste poging om de waarde van de alleen-lezen variabele te lezen, resulteert in de beginwaarde van 10 voor i. Bij de volgende poging zal dit 9 zijn omdat dit wordt verlaagd in de GET-methode. Een poging in de code om een ​​waarde toe te wijzen aan een alleen lezen variabele zal resulteren in een compileerfout.

Download het Excel-bestand om het zelf te proberen. Zie de klasse module 'ClassGlobalVariables' en de module 'Module1_GlobalVariables'.

Meet de executie tijd van code

De uitvoeringstijd van een stkuje programma code meten is eigenlijk vrij eenvoudig. Voor de uit te voeren code sla je de tijd op in een variabele en na het uitvoeren van de code kun je dan bepalen hoeveel tijd er verstreken is. Er is een klein addertje onder het gras, de standaard =Time() functie in excel heeft een nauwkeurigheid van 1 seconde. Dit is veel te grof om een goede meting te doen. Daarom heeft Microsoft een programma MicroTimer gepubliceerd die nauwkeuriger kan meten en wel tot op de microseconde.

Gebaseerd op deze code heb ik een zogenaamde klasse gemaakr genaamd ClassTimer en een aantal methoden gedefinieerd om gemakkelijk de metingen te kunnen doen. Met gemakkelijk bedoel ik met zo min mogelijk code in het gebruik. De klas module maakt het dan mogelijk om bv Timer.Start te gebruiken voor de code onder test en Timer.Finish na de code under test om de executie tijd te tonen. Zoals gezegd kan de Microtime() functie van Microsoft tot op de microseconde meten maar voor mij is milliseconden genoeg. Snelle code voer ik die dan uit in een loop van 1000x-1000000x om tijden te meten die goed af te lezen zijn. Samen met statements zoals het uitschakelen van events (zie een volgende sectie over het versnellen van de code) wordt de VBA code vooe het meten van de uitvoeringstijd:

Dim Timer As New ClassTimer
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.Cursor = xlWait ' to reduce cursor flicker and improve speed of code

Timer.Start
For i = 1 To 1000
	' 
	"place code to test here"
	' 
Next i
Timer.Finish

Application.Cursor = xlDefault
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Het resultaat wordt weggeschreven naar het immediate window in de IDE editor. met het commando Debug.Print. Dit zit er dan bv zo uit:

05/01/2024 15:32:57 === Timer demo ...
0,665         = Total from start

De eerste regel wordt geschreven door de methode Timer.Start. Deze methode zet ook de interne timer op 0. De Timer.Finish laat de waarde zien van de interne timer variabele. De klasse heeft een aantal methoden meer:

This class also has some more methods:
Timer.Inter: Om een tussenresultaat te tonen zonder de timer weer te resetten. Dit kan gebruikt worden bij langere code om meerdere punten te meten en toch de totale uitvoeringstijd te bepalen.
timer.Restart: Om de timer waarde te tonen én om de timer waarde op 0 terug te zetten. Dit is hetzelfde als timer.Inter gevolgd door timer.Start

Download het Excel-bestand om het zelf te proberen. Zoek naar de klasse 'ClassTimer' in de IDE editor.

Versnel de uitvoering van VBA-code / macro

Om de uitvoering van code te versnellen en schermflikker te voorkomen, zet ik de volgende regels voor en na de code:

	Application.ScreenUpdating = False
	Application.EnableEvents = False
	Application.Calculation = xlCalculationManual
	Application.Cursor = xlWait
	Application.ScreenUpdating = True
	Application.DisplayStatusBar = True
	Application.StatusBar = "Message that code is executing..."

	< code to execute >

	Application.StatusBar = ""
	Application.Cursor = xlDefault
	Application.Calculation = xlCalculationAutomatic
	Application.EnableEvents = True
	Application.ScreenUpdating = True

Application.ScreenUpdating : Stops updating/refreshing the screen while the code is running. This is especially effective when the worksheet cells that are updated by the VGA code are visible for the user.
Application.EnableEvents : This prevents code is interrupted by events. Event could be something like changing a value of a cell. Remember that cells could be changed by the code and would trigger events like the Worksheet_Change() event.
Application.Calculation : To not trigger the calculation when cells are changed by the code. Remember that cells could be changed by the code also would trigger the calculation of the sheet.
Application.Cursor : This changes the cursor to a waiting circle. To reduce cursor flicker. Is also an indication to the user that code is executing and seems to speed up code execution.
Application.StatusBar : Displays a message in the left lower corner of the excel window. This does not actually improve the execution speed of the code but it can make it clear to the user that something is running. Current status is saved before message is displayed. Status bas is restored after code execution.

If code is stopped during execution e.g. by an error then the status of the events is not restored since these statement were not executed. This is especially anoying for the cursos which is still in the wrong state (circle) and the events are no longer executed. Therefore I leave out these lines until the code if fully debugged.

If code is called by other subroutines then it is better to store the current status and restore after code is executed:

	Dim currEnableEvents as Boolean
	Dim currScreenUpdating as Boolean
	Dim currCalculationMode as xlCalculation
	Dim oldStatusBar As Boolean

	currEnableEvents = Application.EnableEvents
	currScreenUpdating = Application.ScreenUpdating
	currCalculationMode = Application.Calculation

	Application.ScreenUpdating = False
	Application.EnableEvents = False
	Application.Calculation = xlCalculationManual
	Application.Cursor = xlWait
	oldStatusBar = Application.DisplayStatusBar
	Application.DisplayStatusBar = True
	Application.StatusBar = "Message that code is executing..."

	< code to execute >

	Application.StatusBar = ""
	Application.DisplayStatusBar = oldStatusBar
	Application.Cursor = xlDefault
	Application.Calculation = currCalculationMode
	Application.EnableEvents = currEnableEvents
	Application.ScreenUpdating = currScreenUpdating

Application.ScreenUpdating: Stopt het updaten / vernieuwen van het scherm terwijl de code actief is.
Application.EnableEvents: Dit voorkomt dat code wordt onderbroken door gebeurtenissen. Event kan zoiets zijn als het veranderen van een waarde van een cel. Onthoud dat cellen door de code kunnen worden gewijzigd.
Application.Calculation: om de excel berekening niet te activeren wanneer cellen worden gewijzigd door de code.
Application.Cursor: Dit verandert de cursor in een wachtcirkel. Om het knipperen van de cursor te verminderen. Is ook een indicatie voor de gebruiker dat code wordt uitgevoerd en het versnelt ook de code versnelt !!
Application.StatusBar: Geeft een bericht weer in de linker benedenhoek van het Excel-venster. De huidige status wordt opgeslagen voordat het bericht wordt weergegeven. Status bas wordt hersteld na uitvoering van de code.

Tijdens het maken en ontwikkelen van VBA code was ik nieuwsgierig naar de uitvoeringstijd van stukjes veel gebruikte code. Dit omdat de code ook in loops gebruikt werden die vele malen werden doorlopen. Daarom een aantal testen gedaan met stukjes code en de tijd gemeten. Hieronder is het resultaat. De absolute waarden van de metingen kan nogal afhangen van de computer die je gebruikt, de windows versie en wellicht ook de excel versie. Je kunt in ieder geval de getallen vergelijken en iets zeggen over de relative snelheid van het stukje code. Metingen zijn gedaan op een PC met Windows 11 en een processor van de 11de generatie Intel(R) Core(TM) i7-11700 @ 2.50GHz samen met de ingeboudwe grafische chip UHD 750. Alle metingen zijn gedaan volgende de bovenstaande methode. Stukjes code werden 1x (Sec), 1000x (mSec) of zelfs 1000000x (µSec) uitgevoerd om een goede meting te krijgen. De getallen in de tabel zijn voor 1x uitvoering, dus teruggerekend.

Onderstaande tabel geeft de uitvoeringstijd voor de verschillende manieren om een Excel cell uit te lezen:

Een aantal metingen voor het uitlezen van een attribuut van een cell. Deze lijken allemaal min of meer dezelfde waarde te hebben:

De volgende tabel geeft een aantal metingen voor het schrijven naar een cel:

Dan nog een paar willekeurige VBA functions, methoden en stukjes code die ik regelmatig gebruik:

Select Case i
	Case 1
	Case 2
	Case 3
	Case 4
	Case 5
End Select

If i = 1 Then
	ElseIf i = 2 Then
	ElseIf i = 3 Then
	ElseIf i = 4 Then
	ElseIf i = 5 Then
End If

If Not Intersect(Target, Range("A58")) Is Nothing Then
	' code to execute
End If

If (Target.Column = 1) And (Target.Row = 58) Then
	' code to execute
End If

Uitboeringstijden voor opzoek functies:

For r = 1 To 100
	If Cells(r, 2) = "Row100" Then Exit For
Next r

De volgende tabel laat het verschil zien in het lezen van grote hoeveelheden cellen met de Cells() functie in vergelijking tot het werken met een Array. Zoals je kunt zien is het lezen d.m.v. een Array veel sneller. Let op dat de waarden nu in [mSec] staan:

For r = 1 To 100
	For c = 1 To 100
		shArr(r, c) = Cells(r, c)
	Next c
Next r

For r = 1 To 1000
	For c = 1 To 1000
		shArr(r, c) = Cells(r, c)
	Next c
Next r

De volgende tabel laat het verschil zien in het schrijven van grote hoeveelheden cellen met de Cells() functie in vergelijking tot het werken met een Array. Zoals je kunt zien is het schrijven d.m.v. een Array vééél sneller. Er zijn geen grote verschillen tussen het schrijven naar cellen of een range .Value, .Value2, .Formula or .Formula2 attribute. Ook het type van de array heeft weinig effect. Met de shArr gedeclareerd als integer, string, single, date enz geven allen hetzelfde resultaat. Ik heb voor de array dezelfde grootte gekozen als het bereik maar in werkelijkheid kan de shArr groter zijn dan het bereik waarnaar toe geschreven wordt. Let op dat de waarden nu in [mSec] staan:

For r = 1 To 100
	For c = 1 To 100
		Cells(r, c) = shArr(r, c)
	Next c
Next r

For r = 1 To 1000
	For c = 1 To 1000
		 Cells(r, c) = shArr(r, c)
	Next c
Next r

Progress Bar

Heb een formulier gemaakt om de voortgang van code uitvoering te kunnen laten zien aan de gebruiker. Dit is handig als de uitvoering wat langer duurt. Dit formulier bevat een gekleurde rechthoek die wordt aangepast in de breedte om een voortgang te simuleren. Het formulier kan worden ingesteld met een Min en een Max schaal waarde. Het formulier wordt bestuurd vanuit de code met een paar zg methodes van het formulier:
UserFormProgressBar.SetMinScale: Om de minimale waarde te zetten, dus 0% voortgang. De uitgangswaarde is 0
UserFormProgressBar.SetMaxScale: Om de maximale waarde te zetten, dus 100% voortgang. De uitgangswaarde is 100
UserFormProgressBar.SetProgress (): Om de actuele voortgangs waarde aan het formulier door te geven

Belangrijk om op te merken dat de parameter ShowModel = False gezet moet zijn voor het formulier in de excel editor. Hierdoor kan de code doorlopen terwijl het formulier getoond wordt. Normaal zou de code wachten tot het formulier gesloten wordt. Omdat hier geen gebruikers interactie is, is wachten niet nodig en in dit geval niet gewenst.

De volgende code laat zien hoe het formulier gebruikt wordt:

Sub ProgressBarDemo()
		Dim MaxLoopCntr As Long
		Dim r As Long
		
		MaxLoopCntr = 100
		
		' use these statements to initialize the progress bar and show form
		UserFormProgressBar.SetMaxScale = MaxLoopCntr
		UserFormProgressBar.SetMinScale = 0
		UserFormProgressBar.Show
		
		For r = 0 To MaxLoopCntr
			' update progress bar ...
			UserFormProgressBar.SetProgress (r)
			' update progress bar only every x values, to reduce impact on loop execution time.
			'If (r Mod 100 = 0) Then UserFormProgressBar.SetProgress (r)
			
			Sleep 10
			
		Next r
		
		UserFormProgressBar.Hide
		
		End Sub

UserFormProgressBar.SetMinScale: Deze regel is alleen nodig als de minimale waarde niet 0 is.
UserFormProgressBar.SetMaxScale: Deze regel is alleen nodig als de maximale waarde niet 100 is.
UserFormProgressBar.Show: Deze regel toont het formulier.
UserFormProgressBar.SetProgress (r): Deze regel is nodig om de voortgang te actualiseren met de opgegeven waarde. Voor hele grote waarde van de loop teller en met weinig code in de loop zelf, kan het zijn dat de voortgangs indicator een merkbare bijdrage heeft aan de uitvoeringstijd van de code. Om dit te voorkomen kan je de regel vervangen door If (r Mod 100 = 0) Then UserFormProgressBar.SetProgress (r). In dit geval wordt de voortgangs indicator dus pas elke 100ste keer aangeroepen. De tijd die UserFormProgressBar.SetProgress (r) gebuikt is echter relatief kort. Gemeten waarde was rond de 0,2 uSec. Dus zelfs met een teller tot 10000x is dit nog maar 2mSec bijdrage aan de loop code.

De code van het formulier is maar klein maar er zijn wel wat zaken om rekening mee te houden:

1. In de aanroep procedure wordt het formulier gesloten met UserFormProgressBar.Hide en dus niet met Unload UserFormProgressBar. Dit zorgt er voor dat het formulier weliswaar niet meer zichtbaar is maar zich nog wel bevindt in het geheugen. Hierdoor zijn de variabelen MinScale en MaxScale nog intact en behouden hun waarde. Dit kan handig zijn als het formulier meermaals wordt gebruikt in de code met dezelfde Min en Max schaal waarden.
2. Als de gebruiker het formulier sluit met de X in de rechterbovenhoed dan komt dit neer op een Unload UserFormProgressBar. Met andere woorden, het formulier is verwijderd uit het geheugen en alle variabelen. Als de code nog loopt en bijvoorbeeld een variabele UserFormProgressBar.SetMinScale wordt gebruikt of een methode UserFormProgressBar.SetProgress (r) wordt uitgevoerd dan zal dit de gebeurtenis UserFormProgressBar.Initialize activeren. Hier wordt MaxScale op 100 gezet (zou anders 0 zijn). Dit voorkomt een 'delen door 0' fout in de methode UserFormProgressBar.SetProgress.
3. In bovenstaand voorbeeld zal de regel UserFormProgressBar.SetMaxScale = MaxLoopCntr dus ook de gebeurtenis UserFormProgressBar.Initialize activeren. Dit wordt uitgevoegd voor de SetMaxScale wordt uitgevoegd. Daardoor krijgt de MaxScale variabele toch de waarde die wordt meegegeven. Deze overschrijft dus de 100 die vlak daarvoor is toegekend. Zolang het formulier in het geheugen zit zal UserFormProgressBar.Initialize niet meer worden uitgevoegd.
4. De gebeurtenis UserFormProgressBar.Activate wordt geactiveerd met de regel UserFormProgressBar.Show. Hier wordt de ActValue op 0 geezet zodat formulier initieel getoond wordt zonder voortgang. De ActValue kan nog steeds een waarde hebben van een vorig gebruik van de voortgangs indicator.

Bewaar de waarde van checkbox of textbox op een formulier tijdens sessies

EXCEL heeft een ingebouwd mechanisme om waarden van besturingselementen op gebruikersformulier op te slaan en op te halen. Dit wordt gedaan via een eigenschap genaamd "ControlSource". Het is aanwezig op de eigenschappen van een besturingselementen. Zie hieronder voor het CheckBox besturingselement:

Deze eigenschap ControlSource kan worden gemaakt om naar een cel te verwijzen. Dit behoudt de waarde ook wanneer de werkmap wordt opgeslagen. Als voorbeeld heb ik een controlebox gekoppeld aan cel B2 in Sheet "PopUp" en de andere aan B3:

In het bovenstaande voorbeeld worden de waarden direct op het blad opgeslagen. Zodra u een waarde op het formulier wijzigt, wordt deze in het blad opgeslagen. Er is geen aanvullende code nodig! Wanneer de werkmap wordt opgeslagen, worden de waarden dus ook automatisch opgeslagen. Wanneer het gebruikersformulier opnieuw wordt geactiveerd, gebruikt het de opgeslagen waarden in de gekoppelde cellen als de initiële opstartwaarde. Je kunt het blad verbergen en het een naam geven, zoals 'Instellingen'.

Download het Excel-bestand om het zelf te proberen. Zie het blad "PopUp" en formulier 'Userform1' en module 'Module2_WindowFunctions'.

Positie van (pop-up) venster op het scherm en werkblad

Wanneer u een formulier in uw code gebruikt dat verschijnt, wilt u het misschien nauwkeuriger positioneren. Excel biedt een paar opties in de eigenschappen van het formulier tijdens het ontwerp:

• Manual : formulier bewaard positie na sluiten. Aanvankelijk zal het gebruikersformulier linksboven op het scherm staan. Als de gebruiker de positie van het gebruikersformulier verandert door te slepen en het venster wordt 'gesloten' met de methode Userform.Hide, dan komt op het volgende formulier weer het formulier terug op zijn vorige/laatste positie. Als u het venster sluit met de X, wordt het gebruikersformulier verwijderd en keert de positie weer terug naar linksboven. Merk op dat dit buiten het Excel venster kan vallen als dit niet is gemaximaliseerd.
• CenterOwner : formulier verschijnt midden vertikaal en horizontaal van het Excel toepassingsvenster. Als het Excel venster is gemaximaliseerd, is dit hetzelfde als CenterScreen
• CenterScreen : formulier verschijnt midden verticaal en horizontaal van het scherm. Merk op dat dit buiten het Excel venster kan zijn als het Excel venster niet is gemaximaliseerd.
• WindowsDefault : formulier verschijnt rechtsboven in het scherm. Merk op dat dit buiten het Excel venster kan zijn als het Excel venster niet is gemaximaliseerd.

In de mode 'Manual' als u meer controle wilt of de positie dynamisch wilt wijzigen, kunt u dit doen in de VBA code. Er zijn een aantal Application-, Userform- en ActiveCell-eigenschappen die betrokken (kunnen) zijn. Dit wordt weergegeven in de onderstaande afbeelding:

Afgaande op bovenstaande figuur zou je verwachten dat de minimum waarden van .top en .left beide 0 zijn. Maar in een opstelling met meerdere monitors kunnen deze waarden negatief zijn, maar ook voorbij het einde van het scherm. Dit is afhankelijk van de monitor instellingen in de windows configuratie. De referentie voor zowel de .top als .left waarde is de linker boven hoek van meestal monitor #1. Dit is een opstelling met een laptop waarop twee schermen zijn aangesloten. Het laptop scherm is monitor #1:

Maar nadat ik de laptop sluit verandert de monitor nummering in windows en is nu plotseling monitor #2 de referentie. Voor de duidelijkheid zowel monitor #1 als #2 zijn de externe schermen:

Wanneer je de excel applicatie verplaatst naar bv monitor #2 dan krijg je negatieve waarden voor .left en mogelijk ook voor .top. Met dit alles in gedachten gebruik ik de volgende code om een formulier direct rechts van de active cell te openen:

Merk op dat deze procedure ByRef gebruikt voor de parameters, normaal gesproken is dit ByVal. Met ByRef kan de procedure de parameters wijzigen. Stel in dit geval om de .left en .top eigenschappen te veranderen. Deze procedure wordt aangeroepen vanuit het Userform.Initialize gebeurtenis:

Private Sub UserForm_Initialize()
	WindowPosRightOfSelection UserForm, ActiveCell
End Sub

Download het Excel-bestand om het zelf te proberen. Kijk in de module 'Module2_WindowFunctions' en het werkblad 'PopUp'

Opmerking: als u deze code rechtstreeks vanuit het gebruikersformulier zou uitvoeren, moet de 'w' worden vervangen door 'Userform' of 'Me' en moet de 'c' worden vervangen door 'ActiveCell. Bijvoorbeeld w.Top wordt Me.Top en c.Left wordt ActiveCell.Left

Het belangrijkste onderdeel van de routine is:

	w.Top = Application.Top + c.Top * ZoomLevel + CommandBars("Ribbon").Height - (w.Height / 2)
	w.Left = Application.Left + (c.Left + c.Width) * ZoomLevel + (w.Width / 4)

Deze code overschrijft de standaard opstartpositie die in het formulier is ingesteld. De ActiveCell eigenschappen worden beïnvloed door de zoominstelling van het Excel venster (in de rechter benedenhoek). Om de positie correct te berekenen, moet u de waarden ActiveCell.Left en ActiveCell.Top vermenigvuldigen met de zoomniveau waarde. Ik heb de eigenschap Ribbon height gebruikt om rekening te houden met een al dan niet samengevouwen lint.

• Door een gebruikersformulier te verbergen, Userform.Hide blijft het op de achtergrond. Dit betekent dat waarden op het formulier tijdens de sessie worden opgeslagen.
• Als u op de X van het gebruikersformulier klikt, wordt het 'verwijderd'. Dit betekent dat waarden op het formulier niet worden opgeslagen. Het formulier wordt opnieuw geïnitialiseerd (event:) UserForm_Initialize() wanneer het wordt weergegeven.
• Wanneer het gebruikersformulier zichtbaar wordt gemaakt, wordt de activeringsgebeurtenis geactiveerd UserForm_Activate()
• De ActiveCell.Top en ActiveCell.Left eigenschappen zijn de afstanden respectievelijk van Rij 1 en kolom A. Dus niet van links / boven in het Excel venster raster! Dit betekent dat wanneer het Excel venster wordt gebladerd en rij 1 en / of kolom A niet zichtbaar is een te hoge waarde voor de krijg je ActiveCell.Top en ActiveCell.Left eigenschappen
• Het is een goede gewoonte om te controleren of het gebruikersformulier niet buiten het scherm of buiten het Excel-venster staat. Als het gebruikersformulier buiten het scherm is geplaatst, kunt u het niet sluiten en dit kan de uitvoering van de code blokkeren.
• De Application.UsableHeight en Application.UsableWidth eigenschappen geven grofweg een afmeting die iets groter dan het excel raster grid.

Arrays gebruiken om het lezen en schrijven naar werkbladcellen te versnellen

Het lezen van en schrijven naar werkbladcellen neemt relatief veel van de verwerkingstijd van code in beslag. Een snellere manier om dit te doen, is door een array te gebruiken om de cellen op te slaan, te wijzigen en te herstellen. Snelheidsverhoging kan gemakkelijk een factor 100x zijn. Te volgen stappen als u een array wilt gebruiken.

• Definieer een array zonder dimensie, bijvoorbeeld ArrRange() als variant. Gebruik een type variant om met alle typen waarden (strings, gehele getallen, datums enz.) in een cel om te kunnen gaan.
• Definieer het bereik / gebied waaraan u wilt werken.
• Her-definieer de array met behulp van de bereik / gebied waarden voor de rijen en kolommen.
• Lees de cellen uit in de array.
• <code die de matrixwaarden manipuleert>"
• Schrijf de array terug naar de werkblad cellen

In Excel basic zou dit zoiets kunnen zijn als:

Sub DemoWriteCells()
Dim ArrRange() As Variant
Dim StartRange As String
Dim EndRange As String
Dim rDim As Integer
Dim cDim As Integer
Dim r As Integer
Dim c As Integer
Dim sTime As Date
Dim fTime As Date

' define the range to work with
TopLeftCell = "B5"
BottomRightCell = "BZ100"

' First directly write to cells on the worksheet
sTime = Now()
For r = Range(TopLeftCell).Row To Range(BottomRightCell).Row
	For c = Range(TopLeftCell).Column To Range(BottomRightCell).Column
		Worksheets("PopUp").Cells(r, c) = "w"
	Next c
Next r
fTime = Now() - sTime
MsgBox "Execution time direct write to cells = " + CStr(fTime), vbInformation

' Use array to read and write cells on worksheet
rDim = Range(BottomRightCell).Row - Range(TopLeftCell).Row
cDim = Range(BottomRightCell).Column - Range(TopLeftCell).Column
ReDim ArrRange(rDim, cDim)
ArrRange = Range(TopLeftCell + ":" + BottomRightCell).Value

sTime = Now()
For r = 1 To Range(BottomRightCell).Row - Range(TopLeftCell).Row + 1
	For c = 1 To Range(BottomRightCell).Column - Range(TopLeftCell).Column + 1
		ArrRange(r, c) = "A"
	Next c
Next r
Range(TopLeftCell + ":" + BottomRightCell).Value = ArrRange
fTime = Now() - sTime
MsgBox "Execution time using array = " + CStr(fTime), vbInformation

End Sub

Merk op dat de .value eigenschap van de cel in het bovenstaande voorbeeld wordt gebruikt om de gegevens te lezen en terug te schrijven. Excel slaat waarden en formules van een cel afzonderlijk van elkaar op. De eigenschap .formula bevat de formule en de eigenschap .value bevat het resultaat. Schrijven naar een cel met de eigenschap .value verliest de formule als die er was. Als u werkt met een gebied dat (ook) formules bevat, gebruik dan om .formule om de gegevens te lezen en te schrijven.

Download het Excel-bestand om het zelf te proberen. Bekijk het blad "Array" en de module 'Module2_Arrays'.

Een menu toevoegen aan het lint.

Om uw Excel-code gebruiksvriendelijker en professioneler te maken, kunt u opdrachtknoppen toevoegen aan het ingebouwde Excel-lint.

• U kunt zelf bepalen waar het nieuwe menu in het lint moet komen.
• U kunt het menu en de knoppen een korte naam geven. Deze naam is zichtbaar op het lint.
• U kunt een pictogram definiëren vanuit de ingebouwde standaardset. Zoek voor een lijst met namen naar 'msoImages' op internet.
• Of u voegt een nieuw pictogram toe aan CustomUI en gebruikt dat.
• U kunt knopinfo toevoegen aan uw knoppen. Dit verschijnt als de gebruiker met de muis over de knop beweegt.
• U kunt een knop in- of uitschakelen. Een uitgeschakelde knop wordt grijs weergegeven.

De volgende stappen zijn nodig om een ​​menu aan het lint toe te voegen:

• Download en installeer het hulpprogramma CustomUI als dit nog niet is gebeurd.
• Open vanuit CustomUI een xlsm-bestand en voeg xml-code toe voor het lint.
• Sla de xml-code op in Excel
• Open de xlsm in Excel. Je ziet nu het toegevoegde menu
• Voeg code toe aan een module in de xlsm om de gebeurtenissen die door de knoppen worden geactiveerd, af te handelen.

Hier is een voorbeeld van xml-code voor een menu in het lint:

	<ribbon>
		<tabs>
			<tab id="customTab" label="RADAR" insertAfterMso="TabHome"
				<group id="customGroup1" label="Tools"
					<button id="customButtonIMP"
						label="Import workload"
						size="large"
						screentip="Imports the capacity and planned workload from the VIRES export file."
						supertip="Refreshes the data in sheet WorkloadImport."
						onAction="cMenuImportWorkload"
						enabled = "true"
						imageMso="RecordsAddFromOutlook" />
					<button id="customButtonOPL"
						label="OPL list"
						size="large"
						screentip="Update the prospect/sales projects list from an OPL file on the server"
						supertip="Reads the OPL file and updates the project data."
						onAction="cMenuUpdateOPLRadar"
						enabled = "false"
						imageMso="CacheListData" />
					<button id="customButtonSORT"
						label="Sort OPL"
						size="large"
						screentip="Sorts the OPL list."
						supertip="1) Area[A-Z] 2) Scoring Chance[Planned,High,Medium] 3) Start Eng Req[Old-New]"
						onAction="cMenuSortOPLList"
						enabled = "true"
						imageMso="SortCustomExcel" />
					<button id="customButtonHLP"
						label="Manual"
						size="large"
						screentip="Open the user manual of the Radar excel file."
						supertip="Opens the pdf version of the user manual stored in smarteam under document number A_DOC384967."
						onAction="cMenuHelp"
						imageMso="FunctionsLogicalInsertGallery" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

En hier is de code voor het afhandelen van gebeurtenissen voor het bovenstaande lint:

' button [Import]
' Callback for customButtonRes onAction
'
Sub cMenuImportWorkload(control As Variant)
    ' insert code here to execute
    MsgBox "Menu button 'import workload' pressed", vbInformation
End Sub
'
' button [Update OPL]
' Callback for customButtonOPL onAction
'
Sub cMenuUpdateOPLRadar(control As Variant)
    ' insert code here to execute
    MsgBox "Menu button 'update opl radar' pressed", vbInformation
End Sub
'
' button [Sort OPL]
' Callback for customButtonSORT onAction
'
Sub cMenuSortOPLList(control As Variant)
    ' insert code here to execute
    MsgBox "Menu button 'sort OPL list' pressed", vbInformation
End Sub
'
' button [Manual]
' Callback for customButtonHLP onAction
'
Sub cMenuHelp(control As Variant)
    On Error Resume Next
    ActiveWorkbook.FollowHyperlink Address:="http://members.home.nl/rjut/EXCEL/CustomUI sample of ribbon.jpg"
    On Error GoTo 0
End Sub

Download het Excel-bestand voor de code. Bekijk module4_CustomMenuen het Excel-lint.

Eigen functies maken.

EXCEL heeft veel handige ingebouwde functies die u in uw spreadsheet kunt gebruiken. Soms mis je een functie die niet beschikbaar is. Ik had bijvoorbeeld een functie nodig om een ​​komma gescheiden string in verschillende individuele elementen te splitsen, elk in hun eigen cel: Een tekenreeks splitsen zoals "een, twee, drie". Drie woorden gescheiden door een komma. Als u elk element in een andere cel wilt, dan kunt u dit natuurlijk doen met de standaard ingebouwde 'tekst-naar-kolommen-functie':

... maar dit is statisch en ik wilde een dynamische splitsing (als de bron wijzigt dan moet resultaat dit ook volgen). Natuurlijk kunt u ook de standaard excel functies hiervoor gebruiken, maar dit is omslachtig. Eerst moet je de locatie van de komma's vinden en vervolgens pak je elk element uit in een andere cel. Excel VBA biedt hiervoor een zeer eenvoudige ingebouwde functie genaamd split(). U kunt een zogenaamde User Defined Function (UDF) maken die in de Excel-spreadsheet kan worden gebruikt en die de VBA splitfunctie gebruikt om het meeste werk te doen.

Er zijn een paar belangrijke dingen om te onthouden bij UDF's:

• Een UDF moet zich in een module bevinden./li>
• Een UDF moet een functie zijn die één resultaat oplevert.
• Een UDF-functie kan naar elke andere cel in de werkmap verwijzen, maar kan geen andere cellen in de spreadsheet of werkmap manipuleren. Excel staat dit niet toe tijdens de uitvoering van de code. Je krijgt geen foutmelding, de cel wordt eenvoudigweg niet aangepast.
• Een UDF is standaard een zogenaamde 'niet-vluchtige' functie. Dit betekent dat EXCEL het resultaat alleen (her)berekent als het direct afhangt van een andere cel in de werkmap. =MyUDF(A3) Hangt bijvoorbeeld af van cel A3 en wordt dus altijd herberekend als cel A3 verandert. Om ervoor te zorgen dat cellen met uw UDF correct worden herberekend, kunt u de functie dwingen om vluchtig te zijn of een andere celverwijzing gebruiken als parameter van de functie. Strikt genomen is er eigenlijk een derde methode en dat is om de herberekening van de volledige werkmap handmatig te forceren door op Ctrl-Alt-F9 te drukken. Als u de functie dwingt om vluchtig te zijn, wordt deze door Excel altijd opnieuw berekend, zelfs als de parameterwaarden van de functie niet zijn gewijzigd. Als je veel (duizenden) cellen hebt met een vluchtige functie, dan zal EXCEL altijd al deze duizend cellen herberekenen als je iets verandert in het werkblad. Mogelijk merkt u dan een vertraging na het wijzigen van een cel. Als u een celverwijzing als parameter gebruikt, zal EXCEL de UDF alleen herberekenen wanneer de cel waarnaar wordt verwezen verandert.

Als je meer wilt weten over UDF's in Excel, kijk dan op deze uitstekende website website. Zie hieronder voor een voorbeeld van een UDF-functie:

Function MyUDF3(SepList As String, SepChar As String, ElPos As Integer)
Dim SepArr() As String
Dim i As Integer
	' split the string into a zero based array
	SepArr = Split(SepList, SepChar)
	' return the requested element from the array
	MyUDF3 = SepArr(ElPos - 1)
End Function

Deze functie retourneert het ElPos'de element uit een lijst tekenreeks SepList waar elementen door worden gescheiden SepChar. Het kan worden gebruikt in een Excel cel als een formule, =MyUDF3(C9,";",2)die "b" in de cel retourneert wanneer cel C9 "a; b; c; d; e" bevat. Bij deze methode zal EXCEL het resultaat (her)berekenen als cel C9 wordt gewijzigd.

Als uw UDF niet naar andere cellen verwijst en u wilt dat de functie vluchtig wordt, dan:

Function MyUDF() As Double
	' Good practice to call this on the first line.
	Application.Volatile
	MyUDF = Now()
End Function

Deze functie geeft gewoon de tijd terug. Dit is natuurlijk al een ingebouwde EXCEL functie.

Download het Excel-bestand om het zelf te proberen. Bekijk de module Module2_UserDefineFunction en het blad 'MyUDF'

Er zitten een aantal UDF's in deze module:

• MyUDF1 voegt de waarde van 2 cellen toe en kan met een parameter worden ingesteld op vluchtig of niet-vluchtig. Het laat zien dat het altijd wordt herberekend, vluchtig of niet-vluchtig.
• MyUDF2 retourneert eenvoudig de tijd en kan ook met een parameter worden ingesteld op vluchtig of niet-vluchtig. Het laat zien dat het alleen wordt herberekend als het vluchtig is, parameter is WAAR.
• MyUDF3 is de lijstscheidingsfunctie. Het verwijst naar een andere cel voor de SepList parameter. Het laat zien dat het opnieuw wordt berekend wanneer de SepList cel waarnaar wordt verwezen , verandert. Het is eleganter als je deze functie in het blad met aanroept =MyUDF3($C9;",";COLUMN(D9)-COLUMN($C9)). Op deze manier veranderen de parameters naar de volgende index wanneer u de cel naar andere cellen kopieert.
• MyUDF4 is een andere lijstscheidingsfunctie. Het gebruikt een indirecte verwijzing naar een andere cel voor de SepList parameter. Het gebruikt deze indirecte ref om het aantal cellen tussen de aanroepende cel en de cel met de lijst te bepalen. Daarom heeft het de ElPos parameter niet nodig . Maar aangezien de celverwijzing indirect is, wordt deze niet automatisch herberekend ... tenzij u de manier verandert waarop u het noemt. In het Excel blad =MyUDF4("C"&ROW($C14);";") noemde ik de UDF. Door $C14 als referentie te gebruiken, wordt de cel opnieuw berekend wanneer de lijst in cel C14 verandert. Als u de formule naar andere rijen kopieert, wordt deze ook correct gewijzigd door Excel. Of u kunt natuurlijk de regel Application.Volatile aan uw code toevoegen om ervoor te zorgen dat deze altijd opnieuw wordt berekend. Dan zou je de functie gewoon kunnen aanroepen door =MyUDF4("C13";",").
• MyUDF5 is hetzelfde als MYUDF4 maar gedwongen vluchtig.
• MyUDF6 is met afstand de meest efficiente, korste en gebruikers vriendelijke versie. Deze werkt alleen met de nieuwe EXCEL array formulas die de zogenaamde 'spil' functionaliteit hebben. Dit is beschikbaar vanaf 2019/2020 in Excel Office 365 v16. De MyUDF6 functie bestaat uit slechts 1 regel code! Deze retourneert simpelweg het resultaat van de VBA split() functie. De return waarde is een array van meerdere waarden. In de vorige EXCEL versies zou dit omgezet worden naar een enkele waarde en die worden ingevuld in de cel waar de formula staat. In de nieuwste versie echter zal EXCEL alle waarden gebruiken en invullen in de cellen naast de formule cel. Dit heet op z'n engels 'spilling'. Om dit te laten werken moeten wel alle cellen in het spilbereik leeg zijn. Zo niet dan krijg je een spil error in de cel met de formule. Merk op dat de formule slechts in 1 cel wordt ingegeven. Merk ook op dat het spil bereik dynamisch wordt aangepast aan het aantal waarden dat de functie teruggeeft. Als je een cel selecteert binnen het spil bereik dan laat EXCEL het bereik zien met een blauwe omranding en wordt de formule weergegeven in grijs:
  
  Als je een andere cel in het spil bereik selecteert dan wordt de fourmule in grijs getoond.
  
  Als het spil bereik niet leeg is en een waarde dreigt te worden overschreven dan krijg je een SPIL error in de cel met de formule:
  
  

Voor het splitsen van een lijst gebruik ik het liefst de =MyUDF4("C"&ROW($C14);";"). Het wordt alleen opnieuw berekend wanneer dat nodig is en kan gemakkelijk worden gekopieerd naar andere kolommen en rijen.

Een zeer mooie optie in excel is dat u uw eigen helptekst kunt definiëren voor een door de gebruiker gedefinieerde functie. Deze helptekst wordt weergegeven in de Excel functiekiezer. Ook kunt u uw UDF's in hun eigen categorie plaatsen. Om dit te doen, moet u de volgende code uitvoeren voor elke gewenste functie. U hoeft deze code maar één keer uit te voeren. Daarom heb ik het in de ThisWorkbook-module in de Worksheet_Open() gebeurtenis geplaatst:

Application.MacroOptions _
	Macro:="MyUDF3", _
	Description:="Returns the n-th element from an one dimensional array", _
	Category:="User Defined", _
	ArgumentDescriptions:=Array("String with text reference to a cell with the array list with the separated elements.", "The separation character used in the list.")

Het resultaat wordt weergegeven in het pop-upformulier Functie-invoer:

In het bovenstaande voorbeeld zijn de MyUDFx gebruikersfunctiedefinities toegevoegd aan de (excel vooraf gedefinieerde) categorie 'User Defined'. U kunt ook uw eigen nieuwe categorie maken. Als voorbeeld:

Download het Excel-bestand om het zelf te proberen. Bekijk de module Module2_UserDefineFunctionen in het blad MyUDF.

Het verschil tussen HIDE en UNLOAD van een formulier.

Een excel-formulier wordt zichtbaar gemaakt met het commando UserFormX.Show in de vba-code. Het kan onzichtbaar worden gemaakt met de opdracht UserFormX.Hide of met de opdracht Unload UserFormX. Ze lijken allebei hetzelfde te doen, maar er is een verschil. Ik denk er graag zo over na: Met Hide wordt het formulier gewoon onzichtbaar gemaakt maar is het nog steeds actief op de achtergrond. Met Unload wordt het formulier volledig uit het geheugen verwijderd. Het verschil kan (hoeft niet) zichtbaar zijn wanneer u het formulier opnieuw opent met de opdracht UserFormX.Show. Wanneer het formulier werd gesloten met UserFormX.Hide het formulier wordt geopend op dezelfde positie als toen het werd gesloten en de waarden van de besturingselementen worden behouden. Als het formulier is gesloten Unload UserFormX, wordt het opnieuw geïnitialiseerd. Alle eigenschappen zijn ingesteld zoals gedefinieerd tijdens de ontwerptijd. De gebeurtenis 'UserFormX_Initialize' wordt geactiveerd wanneer het formulier voor de eerste keer wordt geopend of na het uitladen. De gebeurtenis 'UserFormX_Activate' wordt geactiveerd wanneer een formulier wordt geopend na verbergen en ook na lossen. Wanneer de gebeurtenis 'UserFormX_Initialize' wordt geactiveerd, wordt het formulier gebouwd, maar nog niet aan de gebruiker getoond. Wanneer de gebeurtenis 'UserFormX_Activate' wordt geactiveerd, is het formulier zojuist geopend en wordt het aan de gebruiker getoond. Dit is de reden waarom de manipulatiecode van de vensterpositie in het bovenstaande voorbeeld zich binnen de UserFormX_Initialize-gebeurtenis bevindt. In de onderstaande afbeelding heb ik geprobeerd dit grafisch te illustreren:

Het verschil is normaal gesproken niet zo relevant. U kunt 'de uit te voeren code' plaatsen wanneer het formulier wordt gestart in de gebeurtenis 'UserFormX_Activate'. Ik kwam een ​​aantal situaties tegen waarin dit verschil belangrijk werd:

• U wilt dat deze code wordt uitgevoerd voordat het formulier wordt weergegeven, bijvoorbeeld in de gebeurtenis UserFormX_Initialize. Dit betekent ook dat het formulier moet worden gesloten, Unload anders wordt de gebeurtenis UserFormX_Initialize niet geactiveerd na UserformX.Show
• Wanneer een formulier is ONGELADEN en u controleert (bijv. Result = CheckBox1.Value) Of wijzigt (bijv. CheckBox1 = False) De waarde van een CheckBox-besturingselement in uw code, dan activeert dit ook de UserForm_Initialize-gebeurtenis van het formulier waarop het besturingselement is ingeschakeld. In dit geval wil je het formulier sluiten met verbergen.

Download het Excel-bestand om het zelf te proberen. Bekijk blad 'OpenForm' en UserForm3.

Gebeurtenissen die worden geactiveerd door de formulierbesturingselementen.

De bedieningselementen op een gebruikersformulier kunnen een groot aantal gebeurtenissen veroorzaken. De gebeurtenissen kunnen worden geactiveerd door de gebruiker of door een andere VBA-code. Alle bedieningselementen lijken gebeurtenissen te hebben die kunnen worden geactiveerd, maar deze zijn niet voor alle bedieningselementen hetzelfde. Een veel gebruikte gebeurtenis is de gebeurtenis Click () die wordt gegenereerd wanneer de gebruiker met de muis op de knop klikt of op de ENTER-toets drukt wanneer de opdrachtknop de focus heeft (stippellijn zichtbaar op de knop). Hieronder vindt u een overzicht van enkele veelgebruikte gebeurtenissen:

Meer dan één gebeurtenis kan worden (en wordt meestal) veroorzaakt door een enkele actie. Hieronder staan ​​enkele voorbeelden van welke gebeurtenissen worden veroorzaakt door welke actie op een besturingselement:

Er zijn 2 eigenschappen die van invloed zijn op de gebeurtenissen die door een bedieningselement kunnen worden geactiveerd. Dit zijn "Ingeschakeld" en "Vergrendeld". Beide kunnen worden ingesteld op Waar of Onwaar.

De eigenschap Enabled die is ingesteld op False, grijpt het besturingselement op het gebruikersformulier uit. In deze staat genereert het geen gebeurtenissen meer wanneer erop wordt geklikt. Het kan ook niet de focus krijgen. Het lijkt dus helemaal dood.
Als de vergrendelde eigenschap is ingesteld op Waar, worden de bedieningselementen op het formulier normaal weergegeven. De gebruiker kan deze niet wijzigen / wijzigen. Het kan nog steeds focus krijgen. Het zal nog steeds enkele evenementen genereren.

Om dit alles te demonstreren, heb ik een UserForm2 gemaakt met de gebeurtenissen die de gebruiker op verschillende besturingselementen kan activeren.

Download het Excel-bestand om het zelf te proberen. Bekijk het blad 'Evenementen' en UserForm2.

Reguliere expressies met door de gebruiker gedefinieerde functies

De ingebouwde zoek / vervang-functie in Excel is erg handig en flexibel. Het kan tekst in cellen of formules vinden en ook bepaalde opmaak van een cel. In de gebruikersinterface kunnen enkele speciale tekens naar specifieke tekst zoeken. Ik denk dat de opties zijn:

• * om een ​​willekeurige combinatie van tekens te vinden. 'De * re' zou bijvoorbeeld een cel vinden die 'daar' bevat, maar ook 'ze waren'.
• ? om een ​​enkel karakter te vinden. 'De? E' zou bijvoorbeeld een cel vinden met 'daar' maar ook 'thema', maar niet 'ze waren'.
• ~ Om een ​​* of ? letterlijk te kunnen zoeken. U zou bijvoorbeeld ~* gebruiken om naar een enkele * te zoeken ALT + nnnn U kunt een 4-cijferige tekencode invoeren door de ALT-toets ingedrukt te houden terwijl u de 4 cijfers op het num-toetsenbord typt.

Editors zoals Notepad++ hebben een krachtigere zoek- en vervangfunctie. Notepad++ gebruikt een syntaxis voor tekst zoeken / vervangen die bekend staat als 'reguliere expressies'. Dit is een nogal cryptische maar krachtige syntaxis die complexere zoek- en vervangingsacties mogelijk maakt. De syntaxis met reguliere expressies is niet volledig gestandaardiseerd. Elke implementatie lijkt een beetje te verschillen van de andere. Veel veelgebruikte opties komen echter vaak voor.

Microsoft Office heeft al een ingebouwde verwerker voor reguliere expressies die via User Defined Functies beschikbaar kan worden gemaakt voor de Excel gebruiker. Ik heb een aantal van deze door de gebruiker gedefinieerde functies gemaakt, zodat u vertrouwd kunt raken met de (microsoft office) syntaxis van de reguliere expressies.

De engine voor reguliere expressies maakt deel uit van een bibliotheek die niet standaard is opgenomen in de Excel-werkmap. Je moet het zelf opnemen. Dit kan gedaan worden in de Visual Basic-editor via het menu Tools:

Selecteer de bibliotheek 'Microsoft VBSript regular expressions 5.5'. Zorg ervoor dat u de oude 1.0-versie niet opneemt.

In de werkmap heb ik een blad 'Regex' en een module 'Module2_RegularExpressions' toegevoegd. Om de door de gebruiker gedefinieerde functies zo compact mogelijk te houden, heb ik 4 door de gebruiker gedefinieerde functies gemaakt voor 4 verschillende scenario's (testen, zoeken, tellen en vervangen). Alle 4 functies zijn bijna identiek. Hieronder is die voor RegexTest.

Module1_RegularExpression:

Const ParGlobal As Boolean = False     ' FALSE=Single match, TRUE=Find all matches
Const ParIgnoreCase As Boolean = False ' FALSE=case sensitive, TRUE= a equals A
Const ParMultiLine As Boolean = False  ' FALSE=Anchor $ is end of line, TRUE=anchor $ is end of file. For text string use in this excel this should be FALSE.
Dim RegexObj As New VBScript_RegExp_55.RegExp ' Creates a static RegExp object used by the Regex routines in this module

Public Function RegexTest(Pattern As Range, SearchText As Range) As Boolean
	With RegexObj
		.Global = ParGlobal
		.Pattern = Pattern
		.IgnoreCase = ParIgnoreCase
		.MultiLine = ParMultiLine
	End With
	RegexTest = RegexObj.test(SearchText)
End Function

De eerste vier regels definiëren modulebrede constanten die worden gebruikt in elk van de 4 door de gebruiker gedefinieerde regex-functies.
De vijfde regel definieert een object voor reguliere expressie dat in de module wordt gebruikt. Dit object is één keer gemaakt. Dit verbetert de uitvoeringstijd. Met een 'Dim RegexObj' in elke functie duurde het ongeveer 3 seconden om 2000 cellen te verwerken. Met het moduleniveau 'RegexObj' duurt het ongeveer 1 seconde. De with-instructie stelt de parameters en het zoekpatroon in. Ten slotte wordt een methode 'test' aangeroepen met de SearchText
Opmerking: om een ​​of andere reden werkte de RegexReplace niet met het object op moduleniveau. Deze functie creëert zijn eigen statisch object. Als een lezer me kan vertellen waarom dit gebeurt, stuur me dan een mail !.

Het blad is als volgt opgebouwd:

• In kolom A staat een lijst met meer dan 2000 tekstvermeldingen. Dit is de SearchText
• Test: in kolom B is de door de gebruiker gedefinieerde functie RegexTest(Pattern, SearchText). De Pattern wordt ingevoerd in de kop van de kolom. De SearchText is direct links van de formule in kolom A. Het resultaat van de formule wordt getoond in de cel in kolom B. FALSE als de Pattern niet gevonden is SearchText, TRUE als deze wel gevonden is.
• Count: In kolom C staat RegexCount(Pattern, SearchText). De Pattern wordt ingevoegd in de kop van de kolom. De SearchText wordt direct links van de formule in kolom A ingegeven. Het resultaat van de formule wordt getoond in de cel in kolom C. 0 als niet gevonden, 1 als wel 1x gevonden, 2 als 2x gevonden enzovoort...
• Find: In kolom D staat RegexFind(Pattern, SearchText). De Pattern wordt ingevoegd in de kop van de kolom. De SearchText wordt direct links van de formule in kolom A ingegeven. Het resultaat van de formula wordt getoond in de cel in kolom D. #N/A als niet gevonden, De gevonden tekst als deze exact 1x voorkomt en #VALUE als het zoekpatroon meerdere keren voorkomt.
• Replace: In kolom E staat RegexReplace(Pattern, SearchText, ReplaceText). De Pattern wordt weer ingevoerd in de kop van kolom E. De SearchText wordt direct links van de formule ingevoerd in kolom A. De ReplaceText staat in de kop van kolom F. Het resultaat van deze formule wordt getoond in de cel in kolom E. De oorspronkelijke SearchText> wordt getoond als deze niet is gevonden, anders de vervangen tekst.
• In kolom H staat een snelle verwijzing naar de meest gebruikte syntaxis voor reguliere expressies van Microsoft Office. De volledige lijst is beschikbaar op de Microsoft website.

Als voorbeeld heb ik een patroon voor reguliere expressies gemaakt om de bestandsnaam van een nummer te vervangen door een andere bestandsnaam. De zoek / vervang patronen vervangen "The Artist" door "Artist, The".

De zoekstring die hiervoor wordt gebruikt is: 'Singles\\(The)\s(.+)\s-\s'
Dit ziet er nogal cryptisch uit, maar als je het in kleinere stukjes remt en het met de snelle referentie controleert, is het allemaal logisch.

• 'Singles' tekst zoekt / komt overeen met de tekst' Singles '. Houd er rekening mee dat de zoekopdracht hoofdlettergevoelig is.
• '\\' verwijst naar het backslash-teken na de tekst Singles.
• '(The)' vangt het woord 'The' op en slaat het op in groep 1. Groep 1 omdat het de eerste keer is dat een groep wordt gedefinieerd.
• '\s' stelt een spatie voor na 'The'
• '(.+)' legt de tekst vast tot '-' en slaat op in groep 2
• '\s-\s' is het streepje-scheidingsteken tussen artiest en titel inclusief de spaties

Een typisch zoekresultaat voor invoertekst "Singles \ The Rolling Stones - Emotional Rescue.Mp3" zou zijn "Singles \ The Rolling Stones -", ​​waarbij 'The' wordt vastgelegd in groep 1 en 'Rolling Stones' in groep 2.

De vervangende tekenreeks die hiervoor wordt gebruikt, is: 'Singles\\$2, $1 - '
Dit komt neer op:

• 'Singles' literal text 'Singles'
• '\\' om het teken \ in te voegen
• '$2' voegt groep 2 in
• ', ' voegt een komma en een spatie in
• '$1' voegt de eerste groep in
• ' - ' tot slot wordt de artiest en titelscheiding weer toegevoegd.

Het vervangende resultaat is "Singles\Rolling Stones. The - "
De functie retourneert een tekenreeks waarbij het zoekresultaat in de SearchText wordt vervangen. Dus "Singles \ The Rolling Stones -" wordt vervangen door "Singles \ Rolling Stones. The -"
Dit is iets dat niet kan worden bereikt met de ingebouwde zoek / vervang van Excel.

Download het Excel-bestand om het zelf te proberen. Bekijk blad 'Regex' en Module1_RegularExpression..

Uitlezen filter settings tabel

In VBA kun je met programma code de filter instellingen uitlezen en zetten. Laten we als voorbeeld de volgende tabel nemen:

De filter settings kun je uitlezen met bv de volgende code:

	Dim cr1 As Variant
	Dim cr2 As Variant
	Dim fon As Boolean
	Dim FilterText As Filter

	Set FilterText = ActiveSheet.AutoFilter.Filters(5)
	fon = FilterText.On
	If fon Then
		ope = FilterText.Operator
		cr1 = FilterText.Criteria1
		cr2 = FilterText.Criteria1
	End If

De 5 betekent de vijfde kolom van de tabel. De fon is alleen waar als kolom 5 filtering actief is. Als een andere kolom gefiltered wordt maar niet kolom 5 dan is deze waarde niet waar. De cr1 en cr2 zijn gedefinieerd als variant omdat de waarden van de Criteria1 en Criteria2 niet altijd een string bevat, zoals je beneden kunt zien. Eerste wat er moet gebeuren is controleren of het filter wel aan staat. Als deze niet aan staat dan zal een poging om de filter waarden Operator en Criteria1 en Criteria2 uit te lezen resulteren in een foutmelding.

Ik kwam tot de ontdekking dat deze methode toch niet werkt voor een multi-level datum filtering:

Zelfs als de filter aan staat, dus actief is voor de datum kolom dan zal een poging om de filter waarden Criteria1 en Criteria2 uit te lezen toch leiden tot een foutmelding. Weet niet of dit zo bedoeld is door de makers van EXCEL of dat dit een bug is. Je kunt wel de .On en .Operator uitlezen zonder problemen. Vreemde is ook dat je via de programma code wel de multi level filtering waarden kunt zetten, maar ze dus niet kunt terug uitlezen! Bijvoorbeeld de volgende code zet de filtering voor de (multi-level) datum:

	' filter date column
	' criteria2 array(depth, "m/d/yyyy hh:mm:ss")
	' depth sets filter depth, 0=year, 1=month, 2=day
	ActiveSheet.Range("A1:E8").AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(2, "5/3/1990")

Note: dat de datum formaat in de Amerikaanse stijl moet zijn mm/dd/yyyy. Voor multi level filter wordt de .Criteria1 niet gebruikt. Criteria2 is een zogenaamde array die bestaat uit paren van een nummer gevolgd door een datum in tekst. Het number geeft het nivo/tak aan van de filtering, 0 voor jaar, 1 voor maand en 2 voor dag. Met een 0 en een bepaalde datum kies je dus alle data in dat jaar. Het filter zal die dus laten zien.

De filter instellingen worden echter wel degelijk opgeslagen in de EXCEL werkboek. Dit is aan te tonen door de werkboek af te sluiten en weer te openen met een ingestelde filter. Na het openen zal EXCEL weer de filter laten zien zoals die was ingesteld bij het afsluiten. De filter instellingen worden opgeslagen in een sheetX.xml bestand welke onderdeel is van het ingepakte ZIP Excel xlsm bestand. Dit bestand kun je vinden door het .xlsm bestande te hernoemen naar .zip en uit te pakken. In de uitgepakte structuur vind je de xml terug. De X is het blad nummer, in mijn geval is dit 7. In het xml bestand worden de filter settings opgeslagen als:
<autoFilter ref="A1:E8" xr:uid="{8CFF16D5-699F-4856-ADBB-D7A55000C510}"><filterColumn colId="0"><filters><dateGroupItem year="2020" month="5" dateTimeGrouping="month"/><dateGroupItem year="1990" month="5" day="3" dateTimeGrouping="day"/></filters></filterColumn></autoFilter>

Excel filter object attributen, .Operator, .Criteria1, Criteria2.

Gebruikers keuze	Tabel resultaat	.Operator	.Criteria1	.Criteria2	Opmerking
1 waarde geselecteerd		0	"=Delta"	'undefined'	Er is geen omschrijving voor de 0 waarde. Dit werk alleen voor for a single level selectie lijsten, niet voor multi-level (datum) selecties!
2 waarden selecteerd		2 = xlOr	"=-102"	"=21"	Dit werk alleen voor for a single level selectie lijsten, niet voor multi-level (datum) selecties!
3 waarden geselecteerd		7 = xlFilterValues	{"=00:00:01", "=07:00", "=23:59:59"}	'undefined'	Criteria1 is 1d array with 3 values. Dit werk alleen voor for a single level selectie lijsten, niet voor multi-level (datum) selecties!
Start en eind waarden		1 = xlAnd	"=1‑1‑2020" ">=43831"	"=1‑1‑2021" "<="44197"	Speciaal filter tussen 1-1-2020 en 1-1-2021. Criteria1 en Criteria2 bevatten de datum in leesbare tekst en soms als een equivalent nummer. Niet duidelijk waneer de ene of de anders wordt getoond.
Datum filter eg 'Year To Date'		11 = xlDynamic	16	'undefined'	Criteria1 bevat specific datum filter, zie microsoft.com" voor een volledige lijst van alle mogelijke enumeraties.
Multi-level selection. ActiveWindow.AutoFilterDateGrouping = TRUE		7 = xlFilterValues	'undefined'	'undefined'	Zowel Criteria1 als Criteria2 hebben geen waarde. Een poging om Criteria2 te lezen in de VBA code zal resulteren in een runtime error. Geen mogelijkheid om de filter settings te achterhalen dmv Criteria2. Zelfs als de filter waarde gezet zou zijn door middel van VBA code kun je deze nog niet teruglezen met VBA code!
Single level datum selectie. ActiveWindow.AutoFilterDateGrouping = FALSE		2 = xlOr	"=03‑05‑90"	"=03‑05‑20"	Zelfde als bij de andere data type filters. Vergelijk de filtering hierboven van 2 nummerieke waarden. Criteria1 en Criteria2 datum formaat is hetzelfde als die in de excel tabel kolom is ingesteld.