In deze zelfstudie leren we over de Excel VBA-functie
1) Wat is Visual Basic in Excel?
2) Hoe VBA in Excel te gebruiken?
3) Hoe een door de gebruiker gedefinieerde functie te creëren?
4) Hoe schrijf je Macro?
Hoe VBA-code te schrijven
Excel biedt de gebruiker een grote verzameling kant-en-klare functies, meer dan genoeg om de gemiddelde gebruiker tevreden te stellen. Er kunnen nog veel meer worden toegevoegd door de verschillende beschikbare invoegtoepassingen te installeren. De meeste berekeningen kunnen worden gemaakt met wat wordt geleverd, maar het duurt niet lang voordat je zou wensen dat er een functie was die een bepaalde taak deed, en je kunt niets geschikts vinden in de lijst. Je hebt een UDF nodig. Een UDF (User Defined Function) is gewoon een functie die je zelf maakt met VBA. UDF's worden vaak "Aangepaste functies" genoemd. Een UDF kan in een codemodule blijven die aan een werkmap is gekoppeld, in welk geval deze altijd beschikbaar is wanneer die werkmap is geopend. Als alternatief kunt u uw eigen invoegtoepassing maken met een of meer functies die u in Excel kunt installeren, net als een commerciële invoegtoepassing. UDF's zijn ook toegankelijk voor codemodules. Vaak worden UDF's door ontwikkelaars gemaakt om uitsluitend binnen de code van een VBA-procedure te werken en is de gebruiker zich nooit bewust van het bestaan ervan. Zoals elke functie kan de UDF zo eenvoudig of zo complex zijn als u wilt. Laten we beginnen met een makkelijke…
Een functie om de oppervlakte van een rechthoek te berekenen
Ja, ik weet dat je dit in je hoofd zou kunnen doen! Het concept is heel eenvoudig, zodat u zich kunt concentreren op de techniek. Stel dat je een functie nodig hebt om de oppervlakte van een rechthoek te berekenen. Je bladert door de verzameling functies van Excel, maar er is er geen die geschikt is. Dit is de uit te voeren berekening:
GEBIED = LENGTE x BREEDTE
Open een nieuwe werkmap en open vervolgens de Visual Basic Editor (Extra > Macro > Visual Basic Editor of ALT+F11).
Je hebt een module nodig om je functie in te schrijven, dus kies Invoegen > Module. In het lege moduletype: Functiegebied: en druk op BINNENKOMEN.De Visual Basic Editor voltooit de regel voor u en voegt een regel End Function toe alsof u een subroutine aan het maken bent. Tot nu toe ziet het er als volgt uit…
Functiegebied() Eindfunctie
Plaats uw cursor tussen de haakjes na "Gebied". Als je je ooit hebt afgevraagd waar de beugels voor zijn, staat je op het punt om erachter te komen! We gaan de "argumenten" specificeren die onze functie zal aannemen (an argument is een stukje informatie dat nodig is om de berekening uit te voeren). Type Lengte als dubbel, Breedte als dubbel en klik in de lege regel eronder. Houd er rekening mee dat terwijl u typt, er een schuifvenster verschijnt met alle dingen die van toepassing zijn op wat u typt.
Deze functie heet Leden automatisch weergeven. Als het ook niet verschijnt, is het uitgeschakeld (zet het aan op Extra > Opties > Editor) of je hebt misschien eerder een typefout gemaakt. Het is een zeer nuttige controle van uw syntaxis. Zoek het item dat je nodig hebt en dubbelklik erop om het in je code in te voegen. Je kunt het negeren en gewoon typen als je wilt. Je code ziet er nu zo uit…
Functiegebied (lengte als dubbel, breedte als dubbel) Eindfunctie:
Het aangeven van het gegevenstype van de argumenten is niet verplicht, maar wel logisch. Je had kunnen typen Lengte breedte en liet het zo, maar door Excel te waarschuwen welk gegevenstype u kunt verwachten, kan uw code sneller worden uitgevoerd en worden fouten in invoer opgepikt. De dubbele gegevenstype verwijst naar een getal (dat erg groot kan zijn) en staat breuken toe. Nu voor de berekening zelf. Druk in de lege regel eerst op de TAB toets om uw code te laten inspringen (waardoor het gemakkelijker te lezen is) en typ Oppervlakte = Lengte * Breedte. Hier is de voltooide code …
Functiegebied (lengte als dubbel, breedte als dubbel) Gebied = lengte * Breedte Eindfunctie:
U zult tijdens het typen een andere helpfunctie van de Visual Basic Editor opmerken, Automatische snelle info…
Het is hier niet relevant. Het doel is om u te helpen bij het schrijven van functies in VBA, door u te vertellen welke argumenten vereist zijn. U kunt uw functie meteen testen. Schakel over naar het Excel-venster en voer de cijfers voor Lengte en Breedte in afzonderlijke cellen in. Voer in een derde cel uw functie in alsof het een van de ingebouwde is. In dit voorbeeld bevat cel A1 de lengte (17) en cel B1 de breedte (6.5). In C1 typte ik =gebied(A1,B1) en de nieuwe functie berekende het gebied (110.5)…
Soms kunnen de argumenten van een functie optioneel zijn. In dit voorbeeld kunnen we de Breedte argument optioneel. Stel dat de rechthoek een vierkant is met lengte en breedte gelijk. Om te voorkomen dat de gebruiker twee argumenten hoeft in te voeren, kunnen we hem alleen de lengte laten invoeren en de functie die waarde twee keer laten gebruiken (d.w.z. lengte x lengte vermenigvuldigen). Dus de functie weet wanneer het dit kan doen, we moeten een opnemen IF-verklaring om het te helpen beslissen. Verander de code zodat het er zo uitziet…
Functiegebied (lengte als dubbel, optionele breedte als variant) Indien ontbreekt (breedte) dan Oppervlakte = lengte * lengte Anders Gebied = lengte * Breedte Einde indien einde Functie
Merk op dat het gegevenstype voor Breedte is gewijzigd in Variant om null-waarden toe te staan. Met de functie kan de gebruiker nu slechts één argument invoeren, b.v. =gebied(A1).De IF-instructie in de functie controleert of het argument Breedte is opgegeven en berekent dienovereenkomstig…
Een functie om het brandstofverbruik te berekenen
Ik houd graag het brandstofverbruik van mijn auto in de gaten, dus als ik brandstof koop, noteer ik de kilometerstand en hoeveel brandstof er nodig is om de tank te vullen. Hier in het VK wordt brandstof in liters verkocht. De kilometerteller van de auto (oké, het is dus een kilometerteller) registreert de afstand in mijlen. En omdat ik te oud en dom ben om te veranderen, begrijp ik alleen MPG (miles per gallon). Als je dat allemaal een beetje triest vindt, wat dacht je dan van dit. Als ik thuiskom, open ik Excel en voer de gegevens in een werkblad in dat de MPG voor mij berekent en de prestaties van de auto in kaart brengt. De berekening is het aantal kilometers dat de auto heeft afgelegd sinds de laatste tankbeurt, gedeeld door het aantal verbruikte liters brandstof…
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS BRANDSTOF
maar omdat de brandstof in liters komt en er 4.546 liter in een gallon is…
MPG = (MILES THE FILL - MILES LAST FILL) / LITER BRANDSTOF x 4.546
Hier is hoe ik de functie heb geschreven …
Functie MPG (StartMiles als geheel getal, FinishMiles als geheel getal, liters als enkelvoudig) MPG = (FinishMiles - StartMiles) / Liters * 4.546 Eindfunctie
en zo ziet het eruit op het werkblad…
Niet alle functies voeren wiskundige berekeningen uit. Hier is er een die informatie geeft …
Een functie die de naam van de dag geeft
Ik krijg vaak de vraag of er een datumfunctie is die de dag van de week als tekst geeft (bijvoorbeeld maandag). Het antwoord is nee*, maar het is vrij eenvoudig om er een te maken. (*Aanvulling: heb ik nee gezegd? Controleer de onderstaande opmerking om de functie te zien die ik ben vergeten!). Excel heeft de WEEKDAY-functie, die de dag van de week retourneert als een getal van 1 tot 7. Je kunt kiezen welke dag 1 is als je de standaard (zondag) niet leuk vindt. In het onderstaande voorbeeld retourneert de functie "5", waarvan ik weet dat het "donderdag" betekent.
Maar ik wil geen nummer zien, ik wil "donderdag" zien. Ik zou de berekening kunnen wijzigen door een VERT.ZOEKEN-functie toe te voegen die ergens naar een tabel verwijst die een lijst met getallen en een overeenkomstige lijst met dagnamen bevat. Of ik kan het hele ding op zichzelf staand hebben met meerdere geneste IF-statements. Te ingewikkeld! Het antwoord is een aangepaste functie…
Functie DayName(InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday(InputDate, vbSunday) Selecteer Case DayNumber Case 1 DayName = "zondag" Case 2 DayName = "maandag" Case 3 DayName = "dinsdag" Case 4 DayName = "woensdag" Case 5 DayName = "Donderdag" Case 6 DayName = "Friday" Case 7 DayName = "Zaterdag" Einde Selecteer Eindfunctie
Ik heb mijn functie "DayName" genoemd en er is één argument voor nodig, dat ik "InputDate" noem, wat (natuurlijk) een datum moet zijn. Dit is hoe het werkt…
- De eerste regel van de functie declareert een variabele die ik "DayNumber" heb genoemd en die een geheel getal zal zijn (d.w.z. een geheel getal).
- De volgende regel van de functie wijst een waarde toe aan die variabele met behulp van de WEEKDAY-functie van Excel. De waarde is een getal tussen 1 en 7. Hoewel de standaardwaarde 1=zondag is, heb ik het voor de duidelijkheid toch toegevoegd.
- eindelijk een Casusverklaring onderzoekt de waarde van de variabele en retourneert het juiste stuk tekst.
Zo ziet het eruit op het werkblad…
Toegang tot uw aangepaste functies
Als aan een werkmap een VBA-codemodule is gekoppeld die aangepaste functies bevat, kunnen die functies eenvoudig worden geadresseerd in dezelfde werkmap, zoals aangetoond in de bovenstaande voorbeelden. U gebruikt de functienaam alsof het een van de ingebouwde functies van Excel is.
U kunt de functies ook vinden in de Functiewizard (ook wel de functie Functie plakken genoemd). Gebruik de wizard om een functie op de normale manier in te voegen (Invoegen > Functie).
Scroll naar beneden in de lijst met functiecategorieën om te vinden Gebruiker gedefinieerde en selecteer het om een lijst met beschikbare UDF's te zien…
U kunt zien dat de door de gebruiker gedefinieerde functies geen andere beschrijving hebben dan het nutteloze bericht "Geen hulp beschikbaar", maar u kunt een korte beschrijving toevoegen…
Zorg ervoor dat u zich in de werkmap bevindt die de functies bevat. Ga naar Extra > Macro > Macro's. U zult uw functies hier niet zien, maar Excel weet ervan! In de Macronaam boven aan het dialoogvenster typt u de naam van de functie en klikt u op het dialoogvenster Opties knop. Als de knop grijs is, heb je de functienaam verkeerd gespeld, of zit je in de verkeerde werkmap, of hij bestaat niet! Dit opent een ander dialoogvenster waarin u een korte beschrijving van de functie kunt invoeren. Klik Oke om de beschrijving op te slaan en (hier is het verwarrende deel) klik op Annuleren om het dialoogvenster Macro te sluiten. Vergeet niet om de werkmap met de functie op te slaan. De volgende keer dat u naar de Functiewizard gaat, heeft uw UDF een beschrijving…
Net als macro's kunnen door de gebruiker gedefinieerde functies in elke andere werkmap worden gebruikt, zolang de werkmap die ze bevat, geopend is. Het is echter geen goede gewoonte om dit te doen. Het invoeren van de functie in een andere werkmap is niet eenvoudig. U moet de naam van de hostwerkmap toevoegen aan de functienaam. Dit is niet moeilijk als je vertrouwt op de Function Wizard, maar onhandig om handmatig uit te schrijven. De functiewizard toont de volledige namen van alle UDF's in andere werkmappen…
Als je de werkmap waarin je de functie hebt gebruikt opent op een moment dat de werkmap met de functie is gesloten, krijg je een foutmelding te zien in de cel waarin je de functie hebt gebruikt. Excel is het vergeten! Open de host-werkmap van de functie, herbereken en alles is weer in orde. Gelukkig is er een betere manier.
Als u door de gebruiker gedefinieerde functies wilt schrijven voor gebruik in meer dan één werkmap, kunt u het beste een Excel maken Toevoegen. Ontdek hoe u dit doet in de zelfstudie Een Excel-invoegtoepassing bouwen.
Aanvulling
Ik zou echt beter moeten weten! Zeg nooit, nooit, nooit! Nadat ik je heb verteld dat er geen functie is die de naam van de dag geeft, herinner ik me nu degene die dat wel kan. Kijk naar dit voorbeeld…
De functie TEKST retourneert de waarde van een cel als tekst in een specifieke getalnotatie. Dus in het voorbeeld had ik kunnen kiezen =TEKST(A1,"ddd") om "Do" terug te geven, =TEKST(A1,"mmmm") om "September" enz. terug te geven. De Excel-help heeft nog enkele voorbeelden van manieren om deze functie te gebruiken.
Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook.
We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op e-mailsite