VBA-werkbladfuncties gebruiken in Excel 2016 - dummies

Hoewel VBA een behoorlijk assortiment ingebouwde functies biedt, vindt u mogelijk niet altijd precies wat u nodig hebt. Gelukkig kunt u de meeste Excel-werkbladfuncties ook gebruiken in uw VBA-procedures. De enige werkbladfuncties die u niet kunt gebruiken, zijn die met een equivalente VBA-functie. U kunt bijvoorbeeld de RAND-functie van Excel (die een willekeurig getal genereert) niet gebruiken omdat VBA een equivalente functie heeft: Rnd.

VBA maakt de werkbladfuncties van Excel beschikbaar via het object WorksheetFunction, dat zich in het object Application bevindt. Hier is een voorbeeld van hoe u de SUM-functie van Excel kunt gebruiken in een VBA-statement:

Total = Application. WorksheetFunction. SUM (Bereik ("A1: A12"))

U kunt het gedeelte Toepassing of het gedeelte Werkbladfunctie van de uitdrukking weglaten. In beide gevallen, VBA komt erachter wat je aan het doen bent. Met andere woorden, deze drie uitdrukkingen werken allemaal precies hetzelfde:

Totaal = toepassing. WorksheetFunction. SUM (Range ("A1: A12")) Total = WorksheetFunction. SUM (Bereik ("A1: A12")) Totaal = Toepassing. SUM (Bereik ("A1: A12"))

Mijn persoonlijke voorkeur is om het onderdeel WorksheetFunction alleen te gebruiken om duidelijk te maken dat de code een Excel-functie gebruikt.

Voorbeelden van werkbladfuncties

Hier ontdekt u hoe u werkbladfuncties in uw VBA-expressies gebruikt.

De maximale waarde vinden in een bereik

Hier is een voorbeeld dat laat zien hoe u de MAX-werkbladfunctie van Excel gebruikt in een VBA-procedure. Deze procedure geeft de maximale waarde weer in kolom A van het actieve werkblad:

Een werkbladfunctie gebruiken in uw VBA-code.

Sub ShowMax () Dim TheMax As Double TheMax = WorksheetFunction. MAX (Bereik ("A: A")) MsgBox TheMax End Sub

U kunt de MIN-functie gebruiken om de kleinste waarde in een bereik te krijgen. En zoals u zou verwachten, kunt u andere werkbladfuncties op dezelfde manier gebruiken. U kunt bijvoorbeeld de LARGE-functie gebruiken om de k de grootste waarde in een bereik te bepalen. De volgende expressie laat dit zien:

SecondHighest = WorksheetFunction. LARGE (Range ("A: A"), 2)

Merk op dat de functie LARGE twee argumenten gebruikt. Het tweede argument vertegenwoordigt het k ste deel - 2, in dit geval (de op een na grootste waarde).

Een hypotheekbetaling berekenen

In het volgende voorbeeld wordt de PMT-werkbladfunctie gebruikt om een ​​hypotheekbetaling te berekenen. Drie variabelen worden gebruikt om de gegevens die als argumenten zijn doorgegeven aan de Pmt-functie op te slaan.Een berichtvenster geeft de berekende betaling weer.

Sub PmtCalc () Dim IntRate als Double Dim LoanAmt als dubbele dimperioden als lang IntRate = 0. 0625/12 Perioden = 30 * 12 LoanAmt = 150000 MsgBox werkbladfunctie. PMT (IntRate, Periods, -LoanAmt) End Sub

Zoals de volgende verklaring laat zien, kunt u de waarden ook rechtstreeks als de functieargumenten invoegen:

MsgBox WorksheetFunction. PMT (0, 0625/12, 360, -150000)

Het gebruik van variabelen om de parameters op te slaan, maakt de code echter indien nodig gemakkelijker leesbaar en wijzigbaar.

Een opzoekfunctie gebruiken

In het volgende voorbeeld worden de InputBox- en MsgBox-functies van VBA gebruikt, plus de VLOOKUP-functie van Excel. Het vraagt ​​om een ​​onderdeelnummer en krijgt vervolgens de prijs van een opzoektabel. Hieronder wordt bereik A1: B13 prijslijst genoemd.

Het bereik, genaamd Prijslijst, bevat prijzen voor onderdelen.

Sub GetPrice () Dim PartNum As Variant Dim Prijs als Double PartNum = InputBox ("Vul het subnummer in") Sheets ("Prices"). Prijs activeren = werkbladfunctie. VLOOKUP (PartNum, Range ("PriceList"), 2, False) MsgBox PartNum & "costs" & Price End Sub

Zo werkt de GetPrice-procedure:

• De InputBox-functie van VBA vraagt ​​de gebruiker om een ​​artikelnummer.

• Het onderdeelnummer dat de gebruiker invoert, wordt toegewezen aan de PartNum-variabele.

• De volgende instructie activeert het werkblad Prijs, voor het geval dit nog niet het actieve werkblad is.

• De code gebruikt de functie VERT.ZOEKEN om het onderdeelnummer in de tabel te vinden.

• Merk op dat de argumenten die u in deze verklaring gebruikt dezelfde zijn als die u zou gebruiken met de functie in een werkbladformule. Deze instructie wijst het resultaat van de functie toe aan de variabele Price.

• De code geeft de prijs voor het onderdeel weer via de functie MsgBox.

Deze procedure heeft geen foutafhandeling en mislukt jammerlijk als u een niet-bestaand artikelnummer invoert. (Probeer het maar eens.) Als dit een echte applicatie is die in een echt bedrijf wordt gebruikt, zou u een aantal uitspraken willen toevoegen die op een meer sierlijke manier omgaan met fouten.

Werkbladfuncties invoeren

U kunt het dialoogvenster Excel-past geen gebruik maken van een werkbladfunctie in een VBA-module. Voer in plaats daarvan dergelijke functies op de ouderwetse manier in: met de hand. U kunt echter kunt het dialoogvenster Plakken gebruiken om de functie te identificeren die u wilt gebruiken en om meer te weten te komen over de argumenten.

U kunt ook gebruikmaken van de optie Automatische lijstleden van VBE, die een vervolgkeuzelijst met alle werkbladfuncties weergeeft. Typ gewoon -toepassing. Werkbladfunctie , gevolgd door een punt. Vervolgens ziet u een lijst met de functies die u kunt gebruiken. Als deze functie niet werkt, kiest u de opdracht Tools → Options van VBE, klikt u op het tabblad Editor en plaatst u een vinkje naast Auto List-leden.

Een lijst met werkbladfuncties krijgen die u kunt gebruiken in uw VBA-code.

Meer informatie over het gebruik van werkbladfuncties

Nieuwkomers bij VBA verwarren vaak de ingebouwde functies van VBA en de werkmapfuncties van Excel. Een goede regel om te onthouden is dat VBA niet probeert het wiel opnieuw uit te vinden.Voor het grootste deel dupliceert VBA Excel-werkbladfuncties niet.

Voor de meeste werkbladfuncties die niet beschikbaar zijn als methode voor het object Worksheet Function, kunt u een equivalente ingebouwde VBA-operator of -functie gebruiken. De werkbladfunctie MOD is bijvoorbeeld niet beschikbaar in het object WorksheetFunction omdat VBA een equivalent heeft: de ingebouwde Mod-operator.

Conclusie? Als u een functie wilt gebruiken, moet u eerst bepalen of VBA iets heeft dat aan uw behoeften voldoet. Als dit niet het geval is, raadpleegt u de werkbladfuncties. Als al het andere faalt, kunt u mogelijk een aangepaste functie schrijven met behulp van VBA.