In onze vorige artikelen hebben we geleerd hoe we unieke waarden uit een bereik kunnen halen met behulp van een combinatie van verschillende Excel-functies. Hoewel ze fantastisch werken, maar ook complex zijn, valt niet te ontkennen. Als u vaak unieke waarden extraheert, kunnen die formules u vermoeien. Ook zij maken het bestand zwaar en traag.
In dit artikel zullen we dus leren hoe u een door de gebruiker gedefinieerde functie kunt maken die een bereik als argument neemt en alleen unieke waarden uit dat bereik retourneert. U kunt de code direct naar uw bestand kopiëren en direct gebruiken.
VBA-code voor unieke functie:
Functie UNIQUES(rng As Range) As Variant() Dim lijst As New Collection Dim Ulist() As Variant 'Elke waarde van rng toevoegen aan de collectie. Bij fout Hervatten volgende voor elke waarde In rng 'hier zijn waarde en sleutel hetzelfde. De verzameling staat geen dubbele sleutels toe, daarom blijven alleen unieke waarden over. list.Add CStr(Value), CStr(Value) Volgende bij fout Ga naar 0 'De lengte van de array definiëren tot het aantal unieke waarden. Aangezien de array begint bij 0, trekken we 1 af. ReDim Ulist(list.Count - 1, 0) 'Een unieke waarde aan de array toevoegen. For i = 0 To list.Count - 1 Ulist(i, 0) = list(i + 1) Next 'De array afdrukken UNIQUES = Ulist End Function
Kopieer de code naar de VB-editor van Excel.
De UNIQUE-functie gebruiken
De bovenstaande functie is een door de gebruiker gedefinieerde matrixfunctie met meerdere cellen. Het betekent dat u het bereik moet selecteren waar u de unieke lijst wilt afdrukken, vervolgens de formule moet schrijven en op de toetscombinatie CTRL+SHIFT+ENTER moet drukken.
In de gif hierboven hebben we een lijst met landen. Nu staan er veel dubbele landen op de lijst. We willen alleen de lijst met unieke landen krijgen.
Selecteer hiervoor een bereik waar u de unieke lijst wilt hebben. Schrijf nu deze formule:
=UNIEK(A2:B7) |
Druk op de toetscombinatie CTRL+SHIFT+ENTER. En het is gedaan. Alle unieke waarden worden weergegeven in het geselecteerde bereik.
Opmerking:Als het bereik dat u hebt geselecteerd meer is dan de unieke waarde, wordt de fout #N/A weergegeven. U kunt het gebruiken als een indicatie van het beëindigen van unieke waarden. Als u #N/A niet aan het einde van de lijst ziet, betekent dit dat er mogelijk meer unieke waarden zijn.
Verklaring van de code
Ik heb twee belangrijke concepten van VBA gebruikt in deze UD-functie. Collecties en door de gebruiker gedefinieerde array-functie. Ten eerste hebben we een verzameling gebruikt om de unieke waarden uit het opgegeven bereik te halen.
voor elke waarde in rng-lijst. CStr (waarde), CStr (waarde) toevoegen Volgende volgende
Omdat we een verzameling niet op het blad kunnen afdrukken, hebben we deze overgezet naar een andere array-UList.
voor i = 0 Naar lijst. Telling - 1 Ulist(i, 0) = lijst(i + 1) Volgende volgende
Opmerking:VBA-array-indexering begint bij 0 en collectie-indexering begint bij 1. Daarom hebben we 1 afgetrokken voor array in for-lus en 1 toegevoegd aan de indexering van lijstverzameling.
Uiteindelijk hebben we die array op het blad afgedrukt.
Er is een UNIEKE functie die niet beschikbaar is voor Excel 2016 en die hetzelfde doet. Die functie is beschikbaar in Excel 2019. Alleen de leden van het insiderprogramma hebben toegang tot die functie. Door deze techniek te gebruiken, kunt u op kantoor pronken omdat u MS voor bent.
Dus ja jongens, dit is hoe je een functie kunt maken die eenvoudig unieke waarden extraheert. Ik hoop dat ik voldoende uitleg heb gegeven om u dit te laten begrijpen. Als u specifieke vragen heeft over deze of een andere Excel VBA-gerelateerde vraag, stel deze dan in de opmerkingen hieronder.
Klik op de onderstaande link om het werkbestand te downloaden:
UNIQUES-functieVBA-verzamelingen gebruiken in Excel | Leer hoe u een verzameling kunt gebruiken waarmee u unieke waarden kunt krijgen.
Maak een VBA-functie om array terug te geven | Leer hoe u een door de gebruiker gedefinieerde matrixfunctie maakt die een matrix retourneert.
Arrays in Excel Formul|Leer wat arrays zijn in Excel.
Hoe een door de gebruiker gedefinieerde functie te creëren via VBA | Leer hoe u door de gebruiker gedefinieerde functies in Excel kunt maken
Een door de gebruiker gedefinieerde functie (UDF) uit een andere werkmap gebruiken met VBA in Microsoft Excel | Gebruik de door de gebruiker gedefinieerde functie in een andere werkmap van Excel
Retourneer foutwaarden van door de gebruiker gedefinieerde functies met VBA in Microsoft Excel | Leer hoe u foutwaarden kunt retourneren vanuit een door de gebruiker gedefinieerde functie