EXCEL tips & tricks

Tips en trucs voor Excel (v6 Aug 2020)

Inhoud

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'.

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
	pplication.Calculation = xlCalculationManual
	Application.Cursor = xlWait
	oldStatusBar = Application.DisplayStatusBar
	Application.DisplayStatusBar = True
	Application.StatusBar = "Message that code is executing..."

	< code to execute >

	Application.StatusBar = False
	Application.DisplayStatusBar = oldStatusBar
	Application.Cursor = xlDefault
	Application.Calculation = xlCalculationAutomatic
	Application.EnableEvents = True
	Application.ScreenUpdating = True

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.

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:

Excel userform control property ControlSource

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:

Excel userform controls value storage demo

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:

Eigenschappen van Excel formulier Startup Position

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:

Vensterposities in Excel eigenschappen

Ik gebruik bijvoorbeeld de volgende code in een module om een ​​gebruikersformulier rechts van een geselecteerde cel te plaatsen (zoals weergegeven in de bovenstaande afbeelding):

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.

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.

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.

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

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':

De Excel-functie voor tekst naar kolom in het lint

... 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:

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:

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:

Door gebruiker gedefinieerde functie pop-up venster

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:

Door gebruiker gedefinieerd functiecategorie venster

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:

Illustratie van verbergen en niet verbergen in combinatie met Initialiseren en Activeren van gebeurtenissen

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:

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:

Voorbeelden van getriggerde gebeurtenissen door acties
Gebeurtenis: Parameters: Uitleg:
ContolX_Change() - Deze gebeurtenis wordt geactiveerd wanneer de gebruiker de waarde van de controle heeft gewijzigd of de waarde programmatisch is gewijzigd in VBA-code.
ContolX_Click() - Deze gebeurtenis wordt geactiveerd wanneer de gebruiker op het besturingselement heeft geklikt of aan het besturingselement een waarde in VBA-code is toegewezen.
ContolX_MouseDown()
ContolX_MouseUp()
Knop
X
Y
Deze gebeurtenis wordt geactiveerd wanneer de gebruiker op het besturingselement heeft geklikt.
De X- en Y-punten geven een relatief punt aan waar de gebruiker heeft geklikt. TopLeft is 0,0. X is naar rechts en Y is naar beneden.
Knop is de muisknop die is ingedrukt. Links = 1, Rechts = 2, DblClick = 4
ContolX_KeyDown()
ContolX_KeyUp()
KeyCode Deze gebeurtenis wordt geactiveerd wanneer de gebruiker een toets heeft ingedrukt of losgelaten.
De KeyCode bevat de waarde van de ingedrukte / losgelaten toets.
Klik hier voor een lijst met KeyCodes
ContolX_KeyPress() KeyAscii Deze gebeurtenis wordt geactiveerd wanneer de gebruiker op een toets heeft gedrukt.
De KeyAscii houdt het karakter van de toets of toets ingedrukt.
Het verschil tussen een KeyCode en een KeyAscii is dat een KeyCode 1 toets op het toetsenbord vertegenwoordigt. Een KeyAscii is een echt zichtbaar teken dat kan worden gelezen. Shift-toets heeft bijvoorbeeld KeyCode = 16 dec, Letter A heeft KeyCode = 65 dec. Het typen van Shift + A resulteert in 2 sleutelcodes, één voor de shift-toets en één voor de A-toets, maar slechts 1 ASCII-code 65
Klik hier voor een ASCII-tekentabel

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:

Voorbeelden van getriggerde gebeurtenissen door acties
Besturingselement: Actie: Gebeurtenissen geactiveerd in volgorde: Uitleg
CommandButton Muisklik op de knop wanneer de controle focus heeft
  1. CommandButtonX_MouseDown()
  2. CommandButtonX_MouseUp()
  3. CommandButtonX_Click
Het gebruik van Click() om de ingedrukte knop te detecteren, wordt het meest gebruikt.
CommandButton ENTER-toets ingedrukt als bediening focus heeft
  1. CommandButtonX_KeyDown()
  2. CommandButtonX_Click
  3. CommandButtonX_KeyUp()
Het gebruik van Click () om de ingedrukte knop te detecteren, wordt het meest gebruikt.
Label Muis klik op het label
  1. LabelX_Click
Image Muis klik op de afbeelding
  1. ImageX_MouseDown()
  2. ImageX_MouseUp()
De X & Y-waarden kunnen worden gebruikt om een ​​bepaalde actie uit te voeren, afhankelijk van waar in de afbeelding de gebruiker heeft geklikt
CheckBox Muis klik op het selectievakje
  1. CheckBoxX_MouseDown()
  2. CheckBoxX_MouseUp()
  3. CheckBoxX_Change
  4. CheckBoxX_Click
  5. CheckBoxX_AfterUpdate
CheckBox Wijzig de waarde programmatisch, bijvoorbeeld CheckBox1.Value = Waar
  1. CheckBoxX_Change
  2. CheckBoxX_Click
OptionButton Muisklik op een niet-aangevinkte optieknop X die deel uitmaakt van een groep
  1. OptionButtonN_Exit()
  2. OptionButtonX_Enter()
  3. OptionButtonX_MouseDown()
  4. OptionButtonX_MouseUp()
  5. OptionButtonN_Change
  6. OptionButtonX_Change
  7. OptionButtonX_Click
  8. OptionButtonX_AfterUpdate
De eerste EXIT () gebeurtenis is van een andere optieknop in dezelfde groep.
Na de muisklik krijg je 2 veranderingsgebeurtenissen, een voor de vorige knop en een voor de huidige knop.
TextBox Klikken in een tekstvak en het bewerken van de inhoud gevolgd door op de ENTER-toets te drukken
  1. TextBoxX_MouseDown()
  2. TextBoxX_MouseUp()
  3. TextBoxX_KeyDown
  4. TextBoxX_KeyPress
  5. TextBoxX_Change
  6. TextBoxX_KeyUp
  7. TextBoxX_KeyDown
  8. TextBoxX_Exit
gebeurtenissen verschijnen na klikken in het tekstvak.
Voor elke ingedrukte toets krijg je een KeyDown / Up-gebeurtenis
Voor elk afdrukbaar / zichtbaar personage krijg je een KeyAscii-gebeurtenis.
Voor elke wijziging aan de inhoud krijg je een veranderingsgebeurtenis.
De laatste KeyDown is voor de ENTER-toets.
Algemeen Wanneer controle X de focus verliest door op de TAB-toets te drukken of op een andere controle te klikken.
  1. ControlX_Exit()
Algemeen Wanneer controle X focus krijgt door op de TAB-toets te drukken of op controle X te klikken.
  1. ControlX_Enter()

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.

Beheert eigenschappen ingeschakeld en vergrendeld

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.

UserForm2 schermafbeelding met gebeurtenissen die zijn geactiveerd door gebruikersacties op de bedieningselementen

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:

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:

Visual basic tools Menu met visuele middelen om een ​​bibliotheek aan de werkmap toe te voegen

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

Visual basic keuzemenu bibliotheek selectie

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:

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.

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:

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:

Filter tabel

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:
Multi level date filter
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
Single value selected with multi level filter
Show single value in table 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
Twee waarden geselecteerd met single level filter
Two values listed in the table 2 = xlOr "=-102" "=21" Dit werk alleen voor for a single level selectie lijsten, niet voor multi-level (datum) selecties!
3 waarden geselecteerd
Drie waarden geselecteerd met single level filter
Drie waarden getoond in de tabel 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
Start en eind waarden geselecteerd met filter
Customer between start and end value 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'
Single value selected with filter
Customer selection 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
Two values selected with multi level filter
Customer multi selection 2 values 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
Twee datum waarden geselecteerd met single level selectie
Single level datum selectie 2 waarden 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.