"Functie"- en "Sub"-procedures in VBA

Ingebouwde VBA-functies

Voordat u begint met het maken van uw eigen VBA-functies, is het goed om te weten dat Excel VBA een rijke verzameling vooraf gebouwde ingebouwde functies heeft die u kunt gebruiken tijdens het schrijven van uw code.

Een lijst van deze functies kan worden bekeken in de VBA-editor:

• Open een Excel-werkmap en start de VBA-editor (klik om dit te doen Alt + F11) en druk vervolgens op F2.
• Selecteer een bibliotheek in de vervolgkeuzelijst linksboven in het scherm VBA.
• Er verschijnt een lijst met ingebouwde VBA-klassen en -functies. Klik op de functienaam om de korte beschrijving onderaan het venster weer te geven. drukken op F1 opent de online helppagina voor die functie.

Daarnaast is een volledige lijst met ingebouwde VBA-functies met voorbeelden te vinden in het Visual Basic Developer Center.

Aangepaste procedures "Functie" en "Sub" in VBA

In Excel Visual Basic wordt een reeks opdrachten die een specifieke taak uitvoeren in een procedure geplaatst. Functie (Functie) of Sub (Subroutine). Het belangrijkste verschil tussen de procedures: Functie и Sub is dat de procedure? Functie retourneert resultaat, procedure Sub - niet.

Daarom, als u acties moet uitvoeren en een resultaat moet krijgen (bijvoorbeeld meerdere getallen optellen), wordt meestal de procedure gebruikt Functie, en om eenvoudig enkele acties uit te voeren (bijvoorbeeld de opmaak van een groep cellen wijzigen), moet u de procedure selecteren Sub.

argumenten

Verschillende gegevens kunnen met behulp van argumenten aan VBA-procedures worden doorgegeven. De lijst met argumenten wordt opgegeven bij het declareren van een procedure. Bijvoorbeeld de procedure Sub in VBA voegt het gegeven gehele getal (Integer) toe aan elke cel in het geselecteerde bereik. U kunt dit nummer aan de procedure doorgeven met een argument, zoals dit:

Sub AddToCells(i As Integer) ... End Sub

Houd er rekening mee dat het hebben van argumenten voor procedures Functie и Sub in VBA is optioneel. Sommige procedures vereisen geen argumenten.

Optionele argumenten

VBA-procedures kunnen optionele argumenten hebben. Dit zijn argumenten die de gebruiker desgewenst kan specificeren, en als ze worden weggelaten, gebruikt de procedure de standaardwaarden voor hen.

Terugkerend naar het vorige voorbeeld, om een ​​integer argument voor een functie optioneel te maken, zou het als volgt gedeclareerd worden:

Sub AddToCells (Optioneel i As Integer = 0)

In dit geval is het gehele argument i de standaardwaarde is 0.

Er kunnen verschillende optionele argumenten in een procedure zijn, die allemaal aan het einde van de lijst met argumenten worden vermeld.

Argumenten doorgeven op waarde en op referentie

Argumenten in VBA kunnen op twee manieren aan een procedure worden doorgegeven:

• DoorVal - een argument doorgeven op waarde. Dit betekent dat alleen de waarde (dat wil zeggen, een kopie van het argument) wordt doorgegeven aan de procedure, en daarom gaan alle wijzigingen die in het argument in de procedure worden aangebracht, verloren wanneer de procedure wordt afgesloten.
• DoorRef - een argument doorgeven door middel van verwijzing. Dat wil zeggen, het feitelijke adres van de argumentlocatie in het geheugen wordt doorgegeven aan de procedure. Alle wijzigingen die in een argument binnen de procedure zijn aangebracht, worden opgeslagen wanneer de procedure wordt afgesloten.

Trefwoorden gebruiken DoorVal or DoorRef in de proceduredeclaratie kunt u specificeren hoe het argument aan de procedure wordt doorgegeven. Dit wordt weergegeven in onderstaande voorbeelden:

Sub AddToCells(ByVal i As Integer) ... End Sub

Sub AddToCells(ByRef i As Integer) ... End Sub

Onthoud dat argumenten in VBA standaard door verwijzing worden doorgegeven. Met andere woorden, als er geen trefwoorden worden gebruikt DoorVal or DoorRef, dan wordt het argument door verwijzing doorgegeven.

Voordat u verder gaat met de procedures: Functie и Sub in meer detail zal het nuttig zijn om nog eens te kijken naar de kenmerken en verschillen tussen deze twee soorten procedures. Hieronder volgen korte besprekingen van VBA-procedures: Functie и Sub en eenvoudige voorbeelden worden getoond.

VBA-procedure «Functie»

De VBA-editor herkent de procedure Functiewanneer het een groep opdrachten tegenkomt tussen de volgende openings- en sluitingsinstructies:

Functie ... Einde Functie

Zoals eerder vermeld, is de procedure Functie in VBA (in tegenstelling tot Sub) geeft een waarde terug. De volgende regels zijn van toepassing op retourwaarden:

• Het gegevenstype van de geretourneerde waarde moet worden gedeclareerd in de kop van de procedure Functie.
• De variabele die de retourwaarde bevat, moet dezelfde naam hebben als de procedure Functie. Deze variabele hoeft niet apart te worden gedeclareerd, omdat deze altijd een integraal onderdeel van de procedure vormt. Functie.

Dit wordt goed geïllustreerd in het volgende voorbeeld.

VBA-functievoorbeeld: een wiskundige bewerking uitvoeren op 3 cijfers

Het volgende is een voorbeeld van een VBA-procedurecode: Functie, waarvoor drie argumenten van het type nodig zijn Dubbel (dubbele precisie drijvende-kommagetallen). Als resultaat retourneert de procedure een ander nummer van het type Dubbelgelijk aan de som van de eerste twee argumenten minus het derde argument:

Functie SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 Eindfunctie

Deze zeer eenvoudige VBA-procedure Functie illustreert hoe gegevens via argumenten aan een procedure worden doorgegeven. U kunt zien dat het gegevenstype dat door de procedure wordt geretourneerd, is gedefinieerd als: Dubbel (de woorden zeggen Als dubbel na de lijst met argumenten). Dit voorbeeld laat ook zien hoe het resultaat van de procedure Functie opgeslagen in een variabele met dezelfde naam als de procedurenaam.

De VBA-procedure "Functie" noemen

Als de bovenstaande eenvoudige procedure Functie ingevoegd in een module in de Visual Basic-editor, kan deze worden aangeroepen vanuit andere VBA-procedures of worden gebruikt op een werkblad in een Excel-werkmap.

Roep VBA-procedure "Functie" aan vanuit een andere procedure

Procedure Functie kan worden aangeroepen vanuit een andere VBA-procedure door die procedure eenvoudig toe te wijzen aan een variabele. Het volgende voorbeeld toont een aanroep van een procedure summum, die hierboven werd gedefinieerd.

Sub main() Dim totaal als Dubbel totaal = SumMinus(5, 4, 3) End Sub

Roep VBA-procedure "Functie" aan vanuit een werkblad

VBA-procedure Functie kan worden aangeroepen vanuit een Excel-werkblad op dezelfde manier als elke andere ingebouwde Excel-functie. Daarom is de procedure die in het vorige voorbeeld is gemaakt Functie - summum kan worden aangeroepen door de volgende uitdrukking in een werkbladcel in te voeren:

=SumMinus(10, 5, 2)

VBA-procedure «Sub»

De VBA-editor begrijpt dat er een procedure voor staat Subwanneer het een groep opdrachten tegenkomt tussen de volgende openings- en sluitingsinstructies:

Sub ... Einde Sub

VBA-procedure “Sub”: Voorbeeld 1. Middenuitlijning en verandering van lettergrootte in een geselecteerd celbereik

Overweeg een voorbeeld van een eenvoudige VBA-procedure Sub, wiens taak het is om de opmaak van het geselecteerde cellenbereik te wijzigen. De cellen zijn gecentreerd (zowel verticaal als horizontaal) en de lettergrootte wordt gewijzigd in de door de gebruiker opgegeven grootte:

Sub Format_Centered_And_Sized(Optioneel iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Deze procedure Sub voert acties uit maar retourneert geen resultaat.

Dit voorbeeld gebruikt ook een Optioneel argument Lettertypegrootte. Als het argument Lettertypegrootte niet doorgegeven aan procedure Sub, dan is de standaardwaarde 10. Als het argument Lettertypegrootte doorgegeven aan procedure Sub, dan wordt het geselecteerde cellenbereik ingesteld op de lettergrootte die door de gebruiker is opgegeven.

VBA-subprocedure: voorbeeld 2: gecentreerd en vet lettertype in geselecteerd celbereik

De volgende procedure is vergelijkbaar met degene die zojuist is besproken, maar deze keer wordt in plaats van het formaat te wijzigen een vetgedrukte tekenstijl toegepast op het geselecteerde cellenbereik. Dit is een voorbeeldprocedure Sub, waarvoor geen argumenten nodig zijn:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

De "Sub" -procedure aanroepen in Excel VBA

Roep VBA-procedure "Sub" op vanuit een andere procedure

Een VBA-procedure oproepen Sub van een andere VBA-procedure, moet u het trefwoord schrijven Bel, procedurenaam Sub en verder tussen haakjes staan ​​de argumenten van de procedure. Dit wordt weergegeven in het onderstaande voorbeeld:

Sub main() Oproep Format_Centered_And_Sized(20) End Sub

Als de procedure Format_Centered_And_Sized meer dan één argument heeft, moeten ze worden gescheiden door komma's. Soortgelijk:

Sub main() Oproep Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Roep de VBA-procedure "Sub" op vanuit het werkblad

Procedure Sub kan niet rechtstreeks in een Excel-bladcel worden ingevoerd, zoals kan worden gedaan met een procedure Functieomdat de procedure Sub geeft geen waarde terug. Echter, procedures Sub, die geen argumenten hebben en worden gedeclareerd als Publieke (zoals hieronder weergegeven) zal beschikbaar zijn voor gebruikers van het werkblad. Dus, als de eenvoudige procedures die hierboven zijn besproken, Sub ingevoegd in een module in de Visual Basic Editor, de procedure Format_Centered_And_Bold zal beschikbaar zijn voor gebruik in een Excel-werkblad, en de procedure Format_Centered_And_Sized – zal niet beschikbaar zijn omdat het argumenten heeft.

Hier is een eenvoudige manier om een ​​procedure uit te voeren (of uit te voeren): Sub, toegankelijk via het werkblad:

• Media Alt + F8 (druk op toets anders en terwijl u deze ingedrukt houdt, drukt u op de toets F8).
• Selecteer de macro die u wilt uitvoeren in de lijst met macro's die wordt weergegeven.
• Media lopen (rennen)

Een procedure uitvoeren: Sub snel en eenvoudig kunt u er een sneltoets aan toewijzen. Voor deze:

• Media Alt + F8.
• Selecteer in de lijst met macro's die verschijnt de macro waaraan u een sneltoets wilt toewijzen.
• Media parameters (Opties) en voer in het dialoogvenster dat verschijnt de sneltoets in.
• Media OK en sluit het dialoogvenster Macro (Macro).

Aandacht: Wanneer u een sneltoets toewijst aan een macro, zorg er dan voor dat deze niet standaard wordt gebruikt in Excel (bijvoorbeeld CTRL + C). Als u een reeds bestaande sneltoets selecteert, wordt deze opnieuw toegewezen aan de macro en als gevolg daarvan kan de gebruiker de macro per ongeluk starten.

Bereik VBA-procedure

Deel 2 van deze tutorial besprak de reikwijdte van variabelen en constanten en de rol van trefwoorden. Publieke и Privé. Deze trefwoorden kunnen ook worden gebruikt met VBA-procedures:

Public Sub AddToCells(i As Integer) ... End Sub

Private Sub AddToCells (i As Integer) ... End Sub

Onthoud dat als voordat u een VBA-procedure declareert Functie or Sub trefwoord niet is ingevoegd, is de standaardeigenschap ingesteld voor de procedure Publieke (dat wil zeggen, het zal overal in dit VBA-project beschikbaar zijn). Dit in tegenstelling tot variabele declaraties, die standaard zijn Privé.

Vroegtijdige beëindiging van VBA-procedures "Functie" en "Sub"

Als u de uitvoering van een VBA-procedure moet beëindigen Functie or Sub, zonder te wachten op het natuurlijke einde, dan zijn er operators Functie verlaten и Exit Sub. Het gebruik van deze operatoren wordt hieronder getoond aan de hand van een eenvoudige procedure als voorbeeld. FunctieA die een positief argument verwacht om verdere bewerkingen uit te voeren. Als een niet-positieve waarde wordt doorgegeven aan de procedure, kunnen er geen verdere bewerkingen worden uitgevoerd, dus de gebruiker moet een foutmelding krijgen en de procedure moet onmiddellijk worden afgesloten:

Functie VAT_Amount(sVAT_Rate als enkelvoudig) Als enkelvoudig VAT_Amount = 0 If sVAT_Rate <= 0 Then MsgBox "Verwachtte een positieve waarde van sVAT_Rate maar ontvangen " & sVAT_Rate Exit Function End If ... End Function

Houd er rekening mee dat voordat u de procedure voltooit: Functie - BTW_Bedrag, een ingebouwde VBA-functie wordt in de code ingevoegd MsgBox, die een waarschuwingspop-up voor de gebruiker weergeeft.