Gegevens sorteren in Excel VBA
Excel heeft een uitstekende manier om een reeks tabelgegevens te sorteren met behulp van het lint op de Excel-frontend, en op een gegeven moment zult u deze functionaliteit waarschijnlijk in uw VBA-code willen gebruiken. Gelukkig is dit heel eenvoudig te doen.
U vindt het front-end dialoogvenster door op het pictogram 'Sorteren' te klikken in de groep 'Sorteren en filteren' van het tabblad 'Gegevens' op het Excel-lint. U moet eerst een reeks tabelgegevens selecteren.
U kunt ook Alt-A-S-S gebruiken om het dialoogvenster voor een aangepaste sortering weer te geven.
De sorteermethode is sterk verbeterd in latere versies van Excel. Vroeger was het sorteren beperkt tot drie niveaus, maar je kunt nu zoveel niveaus invoeren als je nodig hebt, en dit geldt ook binnen VBA.
U kunt alle aangeboden sorteerfuncties in het Excel Sorteer-dialoogvenster opnemen in uw VBA-code. De sorteerfunctie in Excel is snel en sneller dan alles wat u zelf in VBA zou kunnen schrijven, dus profiteer van de functionaliteit.
Merk op dat wanneer u een sortering uitvoert in VBA, de sorteerparameters hetzelfde blijven in het front-end sorteervenster. Ze worden ook opgeslagen wanneer de werkmap wordt opgeslagen.
Als een gebruiker hetzelfde bereik van tabelgegevens selecteert en op het pictogram Sorteren klikt, zien ze al uw parameters die zijn ingevoerd door uw VBA-code. Als ze een soort van eigen ontwerp willen maken, zullen ze eerst al je sorteerniveaus moeten verwijderen, wat erg vervelend voor ze zal zijn.
Als u de parameters in uw code niet wijzigt en vertrouwt op standaardwaarden, kan het zijn dat de gebruiker wijzigingen heeft aangebracht die doorwerken in uw VBA-sortering en onverwachte resultaten kunnen opleveren, die zeer moeilijk te debuggen kunnen zijn .
Gelukkig is er een Clear-methode in VBA om alle sorteerparameters opnieuw in te stellen, zodat de gebruiker een schoon sorteerdialoogvenster ziet
| 1 | Werkbladen ("Blad1").Sorteren.SorterenVelden.Wissen |
Het is een goede gewoonte om de sorteerparameters in VBA te wissen voordat en nadat het sorteren is voltooid.
Praktisch gebruik van de sorteermethode in VBA
Wanneer tabelgegevens in Excel worden geïmporteerd, is dit vaak in een zeer willekeurige volgorde. Het kan worden geïmporteerd uit een CSV-bestand (door komma's gescheiden waarden) of het kan afkomstig zijn van een link naar een database of webpagina. U kunt er niet op vertrouwen dat het in een vaste volgorde van de ene import naar de andere gaat.
Als u deze gegevens in uw werkblad aan een gebruiker presenteert, kan de gebruiker het moeilijk vinden om naar een enorme hoeveelheid gegevens te kijken en deze te begrijpen, die qua volgorde overal aanwezig zijn. Misschien willen ze de gegevens groeperen of bepaalde delen ervan knippen en plakken in een andere toepassing.
Ze willen bijvoorbeeld ook de best betaalde werknemer zien, of de werknemer met de langste diensttijd.
Met behulp van de sorteermethode in VBA kunt u opties aanbieden waarmee de gebruiker gemakkelijk kan worden gesorteerd.
Voorbeeldgegevens om Excel-sortering met VBA te demonstreren
We hebben eerst enkele voorbeeldgegevens nodig die in een werkblad worden ingevoerd, zodat de code alle beschikbare faciliteiten binnen VBA kan demonstreren.
Kopieer deze gegevens naar een werkblad (genaamd 'Blad1') precies zoals weergegeven.
Merk op dat er verschillende celachtergrondkleuren en lettertypekleuren zijn gebruikt, omdat deze ook als sorteerparameters kunnen worden gebruikt. Sorteren met cel- en letterkleuren wordt later in het artikel gedemonstreerd. Merk ook op dat in cel E3 de afdelingsnaam in kleine letters is.
U hebt de celinterieur en letterkleuren niet nodig als u de voorbeelden van sorteren op cel en letterkleur niet wilt gebruiken.
Een macro opnemen voor een VBA-sortering
VBA-code voor sorteren kan behoorlijk ingewikkeld worden, en het kan soms een goed idee zijn om de sortering aan de voorkant van Excel uit te voeren en een macro op te nemen om u te laten zien hoe de code werkt.
Helaas kan de opnamefunctie een enorme hoeveelheid code genereren omdat deze vrijwel elke beschikbare parameter instelt, ook al zijn de standaardwaarden voor veel parameters acceptabel voor uw sorteerbewerking.
Het geeft je echter wel een heel goed idee van wat er komt kijken bij het schrijven van VBA-sorteercode, en een voordeel is dat de opgenomen code altijd voor je zal werken. Uw eigen code moet mogelijk worden getest en debuggen om deze correct te laten werken.
Onthoud dat voor een bewerking die in VBA wordt uitgevoerd, er geen functie voor ongedaan maken is, dus het is een goed idee om een kopie van de tabelgegevens op een ander werkblad te maken voordat u begint met het schrijven van uw sorteercode.
Als u bijvoorbeeld een eenvoudige sortering op de bovenstaande voorbeeldgegevens deed, sorteren op werknemer, zou de opname de volgende code genereren:
| 123456789101112131415161718 | Submacro1()Bereik ("A1:E6"). SelecteerActiveWorkbook.Worksheets("Blad1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add2 Key:=Bereik ("A2:A6"), _SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormaalMet ActiveWorkbook.Worksheets("Blad1").Sorteren.SetBereikbereik("A1:E6").Kop = xlJa.MatchCase = False.Oriëntatie = xlTopToBottom.SortMethode = xlPinYin.Van toepassing zijnEindigt metEinde sub |
Dit is een vrij groot stuk code, en veel ervan is niet nodig omdat er standaardparameters worden gebruikt. Als u echter onder tijdsdruk staat om een project af te ronden en u heeft snel wat code nodig die werkt, dan kunt u deze eenvoudig in uw eigen VBA-code plakken.
Als u uw code echter begrijpelijk en eleganter wilt maken, zijn er andere opties beschikbaar.
VBA-code om een sortering op één niveau uit te voeren
Als u bij het opnemen van een macro de voorbeeldcode alleen op werknemer wilt sorteren, is de code heel eenvoudig:
| 1234567 | Sub SingleLevelSorteer()Werkbladen ("Blad1").Sorteren.SorterenVelden.WissenBereik ("A1:E6"). Sorteertoets1:=Bereik ("A1"), Header:=xlJaEinde sub |
Dit is veel gemakkelijker te begrijpen dan de geregistreerde code omdat het de standaardwaarden accepteert, bijvoorbeeld oplopend sorteren, dus het is niet nodig om de parameters in te stellen op standaardwaarden. Hierbij wordt ervan uitgegaan dat je vooraf een ‘Clear’ statement hebt gebruikt.
De 'Clear'-methode wordt in eerste instantie gebruikt om ervoor te zorgen dat elke sorteerparameter voor dat werkblad wordt teruggezet naar de standaardwaarden. Een gebruiker kan de parameters eerder op verschillende waarden hebben ingesteld, of een eerdere sortering in VBA heeft ze mogelijk gewijzigd. Het is belangrijk om bij het sorteren uit te gaan van een standaardpositie, anders kunt u gemakkelijk verkeerde resultaten krijgen.
De Clear-methode stelt de Header-parameter niet opnieuw in en het is raadzaam deze in uw code op te nemen, anders kan Excel proberen te raden of een headerrij aanwezig is of niet.
Voer deze code uit met de voorbeeldgegevens en uw werkblad ziet er als volgt uit:
VBA-code om een sortering op meerdere niveaus uit te voeren
U kunt binnen uw code zoveel sorteerniveaus toevoegen als nodig is. Stel dat u eerst wilt sorteren op afdeling en vervolgens op startdatum, maar in oplopende volgorde voor de afdeling en aflopende volgorde voor startdatum:
| 12345678 | Sub MultiLevelSorteren()Werkbladen ("Blad1").Sorteren.SorterenVelden.WissenBereik ("A1:E6"). Sorteersleutel1:=Bereik ("E1"), Key2:=Bereik ("C1"), Header:=xlJa, _Volgorde1:=xlOplopend, Volgorde2:=xlAflopendEinde sub |
Merk op dat er nu twee sleutels zijn in de sort-instructie (Key1 en Key2). Sleutel1 (Afdelingskolom E) wordt eerst gesorteerd en vervolgens wordt Sleutel2 (Startdatum kolom C) gesorteerd op basis van de eerste sortering.
Er zijn ook twee bestelparameters. Order1 is gekoppeld aan Key1 (afdeling) en Order2 is gekoppeld aan Key2 (Startdatum). Het is belangrijk om ervoor te zorgen dat sleutels en bestellingen in de pas lopen met elkaar.
Voer deze code uit met de voorbeeldgegevens en uw werkblad ziet er als volgt uit:
De kolom Afdeling (E) staat in oplopende volgorde en de kolom Startdatum (C) in aflopende volgorde.
Het effect van dit soort is het meest merkbaar als we kijken naar Jane Halfacre (rij 3) en John Sutherland (rij 4). Ze zitten allebei in Finance, maar Jane Halfacre begon vóór John Sutherland en de datums worden in aflopende volgorde weergegeven.
Als het bereik van tabelgegevens elke lengte kan hebben, kunt u het UsedRange-object gebruiken om het sorteerbereik te definiëren. Dit werkt alleen als er alleen tabelgegevens op het werkblad staan, omdat alle waarden buiten de gegevens onjuiste resultaten geven voor het aantal rijen en kolommen.
| 1234567 | Sub MultiLevelSorteren()Werkbladen ("Blad1").Sorteren.SorterenVelden.WissenWorksheets("Blad1").UsedRange.Sort Key1:=Bereik("E1"), Key2:=Bereik("C1"), Header:=xlJa, _Volgorde1:=xlOplopend, Volgorde2:=xlAflopendEinde sub |
Dit voorkomt het probleem als u de methode ‘End(xlDown)’ gebruikt om het sorteerbereik te definiëren. Als er een lege cel in het midden van de gegevens staat, wordt alles na de lege cel niet opgenomen, terwijl UseRange naar de laatste actieve cel in het werkblad gaat.
Sorteren op celkleur
Sinds Excel 2007 is sorteren op de achtergrondkleur van een cel nu mogelijk, wat een enorme flexibiliteit biedt bij het ontwerpen van uw sorteercode in VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor()Werkbladen ("Blad1").Sorteren.SorterenVelden.WissenActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add2 Key:=Bereik ("A2:A6"), _SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormaalMet ActiveWorkbook.Worksheets("Blad1").Sorteren.SetBereikbereik("A1:E6").Van toepassing zijnEindigt metEinde sub |
Deze code sorteert het voorbeeldgegevensbereik (A2:A6) op basis van de achtergrondkleur van de cel. Merk op dat er nu een extra parameter is genaamd 'SortOn' die de waarde 'xlSortOnCellColor' heeft.
Merk op dat de parameter 'SortOn' alleen kan worden gebruikt door een werkbladobject en niet door een bereikobject.
Hierdoor is de code ingewikkelder dan voor een sortering met celwaarden.
Deze code gebruikt een sleutelwaarde voor de sortering die het hele gegevensbereik dekt, maar u kunt afzonderlijke kolommen specificeren als de sleutel voor de achtergrondkleursortering en meerdere niveaus gebruiken, zoals eerder getoond.
Nadat u deze code hebt uitgevoerd, ziet uw werkblad er nu als volgt uit:
Sorteren op letterkleur
De sorteerfunctie in Excel VBA biedt nog meer flexibiliteit doordat u op lettertypekleuren kunt sorteren:
| 1234567891011121314 | Sub SingleLevelSortByFontColor()Werkbladen ("Blad1").Sorteren.SorterenVelden.WissenActiveWorkbook.Worksheets("Blad1").Sort.SortFields.Add(Range("A2:A6"), _xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)Met ActiveWorkbook.Worksheets("Blad1").Sorteren.SetBereikbereik("A1:E6").Kop = xlJa.Oriëntatie = xlTopToBottom.Van toepassing zijnEindigt metEinde sub |
De code voor het sorteren op letterkleur is veel ingewikkelder dan voor de celachtergrondkleur. De parameter 'SortOn' heeft nu de waarde 'xlSortOnFontColor'.
Merk op dat u de oriëntatie moet specificeren als 'xlTopToBottom' en dat u een kleur moet specificeren om op te sorteren. Dit wordt gespecificeerd in RGB-termen (Rood, Groen, Zwart) met waarden van 0 tot 255.
Nadat u deze code tegen de voorbeeldgegevens hebt uitgevoerd, ziet uw werkblad er nu als volgt uit:
Sorteren met kleuren in VBA is veel ingewikkelder dan sorteren op meerdere niveaus, maar als je sorteercode niet werkt (wat kan gebeuren als een parameter ontbreekt of je de code niet correct hebt ingevoerd), kun je altijd terugvallen op opname een macro en het integreren van de opgenomen code in uw VBA.
Andere parameters gebruiken bij VBA-sortering
Er zijn een aantal optionele parameters die u in uw VBA-code kunt gebruiken om uw sortering aan te passen.
Sorteren op
SortOn kiest of de sortering celwaarden, celachtergrondkleuren of cellettertypekleuren gebruikt. De standaardinstelling is Celwaarden.
| 1 | SortOn = xlSortOnValues |
Volgorde
Volgorde bepaalt of de sortering in oplopende of aflopende volgorde wordt uitgevoerd. De standaardinstelling is Oplopend.
| 1 | Bestelling = xlOplopend |
Gegevensoptie:
DataOption kiest hoe tekst en getallen worden gesorteerd. De parameter xlSortNormal sorteert numerieke en tekstgegevens afzonderlijk. De parameter xlSortTextAsNumbers behandelt tekst als numerieke gegevens voor de sortering. De standaardwaarde is xlSortNormaal.
| 1 | DataOption = xlSortNormaal |
kop
Header kiest of het gegevensbereik in tabelvorm een koprij heeft of niet. Als er een kopregel is, wilt u deze niet in de sortering opnemen.
Parameterwaarden zijn xlYes, xlNo en xlYesNoGuess. xlYesNoGuess laat het aan Excel over om te bepalen of er een koprij is, wat gemakkelijk tot inconsistente resultaten kan leiden. Het gebruik van deze waarde wordt niet aanbevolen.
De standaardwaarde is XNo (geen koprij in de gegevens). Bij geïmporteerde gegevens is er meestal een kopregel, dus zorg ervoor dat u deze parameter instelt op xlJa.
| 1 | Kop = xlJa |
MatchCase
Deze parameter bepaalt of de sortering hoofdlettergevoelig is of niet. Optiewaarden zijn True of False. Als de waarde False is, worden waarden in kleine letters als hetzelfde beschouwd als waarden in hoofdletters. Als de waarde True is, toont de sortering het verschil tussen hoofdletters en kleine letters binnen de sortering. De standaardwaarde is False.
| 1 | MatchCase = False |
Oriëntatie
Deze parameter bepaalt of er naar beneden door de rijen wordt gesorteerd, of over alle kolommen. De standaardwaarde is xlTopToBottom (sorteer door rijen). U kunt xlLeftToRight gebruiken als u horizontaal wilt sorteren. Waarden zoals xlRows en xlColumns werken niet voor deze parameter.
| 1 | Oriëntatie = xlTopToBottom |
Sorteermethode
Deze parameter wordt alleen gebruikt voor het sorteren van Chinese talen. Het heeft twee waarden, xlPinYin en xlStroke. xlPinYin is de standaardwaarde.
xlPinYin sorteert met behulp van de fonetische Chinese sorteervolgorde voor tekens. xlStroke sorteert op het aantal slagen in elk teken.
Als je een sorteermacro opneemt, zal deze parameter altijd in de code worden opgenomen, en je hebt je misschien afgevraagd wat het betekende. Tenzij u echter met gegevens in het Chinees te maken hebt, heeft het weinig zin.
| 1 | Sorteermethode = xlPinYin |
Een dubbelklikgebeurtenis gebruiken om tabelgegevens te sorteren
In alle functionaliteit die Microsoft in de sorteermethoden voor VBA had opgenomen, bevatte het geen eenvoudige manier om op een kolomkop te dubbelklikken en alle tabelgegevens op basis van die specifieke kolom te sorteren.
Dit is echt een handige functie om te hebben, en het is gemakkelijk om de code te schrijven om het te doen.
| 12345678910111213141516171819202122232425262728293031323334 | Privé subwerkblad_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)'Er wordt aangenomen dat gegevens beginnen bij cel A1'Maak drie variabelen om de geselecteerde doelkolom en de maximale kolom en rij van _ vast te leggen' de tabelgegevensDim Col als geheel getal, RCol zo lang, RRow zo lang'Controleer of de gebruiker op de kopregel heeft gedubbelklikt - rij 1 anders sluit u subAls Target.Rij 1 Sub afsluiten'Vang de maximale rijen in het gegevensbereik in tabelvorm met behulp van het object 'UsedRange'RCol = ActiveSheet.UsedRange.Columns.Count'Vang de maximale kolommen in het tabelgegevensbereik vast met behulp van het 'UsedRange'-objectRRow = ActiveSheet.UsedRange.Rows.Count'Controleer of de gebruiker niet heeft gedubbelklikt op een kolom buiten het gegevensbereik in tabelvorm'Als Target.Column > RCol dan Sub afsluiten'Vastleggen van de kolom waarop de gebruiker heeft gedubbelklikt'Col = Doel.Kolom'Vorige sorteerparameters wissen'ActiveSheet.Sort.SortFields.Clear'Sorteer het tabelbereik zoals gedefinieerd door het maximum aantal rijen en kolommen van het 'UsedRange'-object'Sorteer de tabelgegevens met behulp van de kolom waarop de gebruiker dubbel heeft geklikt als sorteersleutel'ActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, Col), Header:=xlJa'Selecteer cel A1 - dit is om ervoor te zorgen dat de gebruiker niet in de bewerkingsmodus wordt gelaten nadat het sorteren is _' voltooidActiveSheet.Bereik ("A1").SelecteerEinde sub |
Deze code moet op de dubbelklikgebeurtenis worden geplaatst op het blad met de tabelgegevens. Dit doe je door op de naam van het werkblad te klikken in het venster Projectverkenner (linkerbovenhoek van het VBE-scherm) en vervolgens 'Werkblad' te selecteren in de eerste vervolgkeuzelijst in het codevenster. Selecteer 'BeforeDoubleClick' in de tweede vervolgkeuzelijst en voer vervolgens uw code in.
Merk op dat er geen namen, bereiken of celverwijzingen hard gecodeerd zijn in deze code, behalve het verplaatsen van de cursor naar cel A1 aan het einde van de code. De code is ontworpen om alle benodigde informatie te halen uit de celcoördinaten waarop de gebruiker heeft gedubbelklikt en de grootte van het gegevensbereik in tabelvorm.
Het maakt niet uit hoe groot het gegevensbereik in tabelvorm is. De code haalt nog steeds alle vereiste informatie op en kan worden gebruikt voor gegevens die overal in uw werkmap worden bewaard zonder dat u waarden hoeft in te voeren.
De enige veronderstelling die is gemaakt, is dat er een kopregel is in de tabelgegevens en dat het gegevensbereik begint bij cel A1, maar de startpositie voor het gegevensbereik kan eenvoudig binnen de code worden gewijzigd.
Elke gebruiker zal onder de indruk zijn van deze nieuwe sorteerfunctie!
De sorteerfunctie uitbreiden met VBA
Microsoft heeft een enorme flexibiliteit geboden bij het sorteren met behulp van een breed scala aan parameters. Binnen VBA kunt u dit echter verder brengen.
Stel dat u waarden met een vetgedrukt lettertype bovenaan uw gegevens wilt sorteren. Er is geen manier om dit in Excel te doen, maar u kunt de VBA-code schrijven om dit te doen:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold()'Maak variabelen om het aantal rijen en kolommen voor de tabelgegevens vast te houden'Dim RR zo lang, RCol zo lang, N zo lang'Schakel het bijwerken van het scherm uit zodat de gebruiker niet kan zien wat er gebeurt - ze kunnen _ zien'waarden worden gewijzigd en vragen u zich af waarom'Application.ScreenUpdating = False'Vast het aantal kolommen in het gegevensbereik in tabelvorm'RCol = ActiveSheet.UsedRange.Columns.Count'Vast het aantal rijen binnen het gegevensbereik in tabelvorm'RRow = ActiveSheet.UsedRange.Rows.Count'Herhaal alle rijen in het gegevensbereik in tabelvorm en negeer de koprij'Voor N = 2 Naar RRij'Als een cel een vetgedrukt lettertype heeft, plaats dan een voorloopwaarde 0 tegen de celwaarde'Als ActiveSheet.Cells(N, 1).Font.Bold = True DanActiveSheet.Cells(N, 1).Waarde = "0" & ActiveSheet.Cells(N, 1).WaardeStop alsVolgende N'Wis eerdere sorteerparameters'ActiveSheet.Sort.SortFields.Clear'Sorteer het gegevensbereik in tabelvorm. Alle waarden met een leidende 0-waarde worden naar boven verplaatstActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, 1), Header:=xlJa'Doorloop alle rijen in het gegevensbereik in tabelvorm en negeer de koprij'Voor N = 2 Naar RRij'Als een cel een vetgedrukt lettertype heeft, verwijder dan de eerste 0-waarde van de celwaarde naar _' herstel de originele waardenAls ActiveSheet.Cells(N, 1).Font.Bold = True DanActiveSheet.Cells(N, 1).Waarde = Mid(ActiveSheet.Cells(N, 1).Waarde, 2)Stop alsVolgende N'Schakel het bijwerken van het scherm weer in'Application.ScreenUpdating = TrueEinde sub |
De code berekent de grootte van het gegevensbereik in tabelvorm met behulp van het object 'UsedRange' en doorloopt vervolgens alle rijen erin. Wanneer een vet lettertype wordt gevonden, wordt een voorloopnul voor de celwaarde geplaatst.
Er vindt dan een sortering plaats. Omdat de sortering in oplopende volgorde staat, komt alles met een nul ervoor bovenaan de lijst te staan.
De code doorloopt vervolgens alle rijen en verwijdert de voorloopnullen, waardoor de gegevens worden hersteld naar de oorspronkelijke waarden.
Deze code sorteert met vetgedrukte lettertypen als criterium, maar u kunt gemakkelijk andere celkenmerken op dezelfde manier gebruiken, bijvoorbeeld cursief lettertype, puntgrootte van tekst, onderstrepingsteken, lettertypenaam, enz.
