"Funksjon" og "Sub"-prosedyrer i VBA

Innebygde VBA-funksjoner

Før du begynner å lage dine egne VBA-funksjoner, er det greit å vite at Excel VBA har en rik samling av forhåndsbygde innebygde funksjoner som du kan bruke mens du skriver koden.

En liste over disse funksjonene kan sees i VBA-editoren:

• Åpne en Excel-arbeidsbok og start VBA-editoren (klikk for å gjøre dette Alt + F11), og trykk deretter på F2.
• Velg et bibliotek fra rullegardinlisten øverst til venstre på skjermen VBA.
• En liste over innebygde VBA-klasser og funksjoner vises. Klikk på funksjonsnavnet for å vise dens korte beskrivelse nederst i vinduet. pressing F1 åpner den elektroniske hjelpesiden for den funksjonen.

I tillegg finner du en komplett liste over innebygde VBA-funksjoner med eksempler på Visual Basic Developer Center.

Egendefinerte prosedyrer "Funksjon" og "Sub" i VBA

I Excel Visual Basic plasseres et sett med kommandoer som utfører en bestemt oppgave i en prosedyre. Funksjon (Funksjon) eller Sub (Subrutine). Hovedforskjellen mellom prosedyrene Funksjon и Sub er det prosedyren Funksjon returnerer resultat, prosedyre Sub - ikke.

Derfor, hvis du trenger å utføre handlinger og få et resultat (for eksempel summere flere tall), brukes vanligvis prosedyren Funksjon, og for ganske enkelt å utføre noen handlinger (for eksempel endre formateringen av en gruppe celler), må du velge prosedyren Sub.

argumenter

Ulike data kan sendes til VBA-prosedyrer ved hjelp av argumenter. Argumentlisten spesifiseres når en prosedyre deklareres. For eksempel prosedyren Sub i VBA legger det gitte heltall (heltall) til hver celle i det valgte området. Du kan sende dette nummeret til prosedyren ved å bruke et argument, som dette:

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

Husk at det å ha argumenter for prosedyrer Funksjon и Sub i VBA er valgfritt. Noen prosedyrer krever ikke argumenter.

Valgfrie argumenter

VBA-prosedyrer kan ha valgfrie argumenter. Dette er argumenter som brukeren kan spesifisere om de vil, og hvis de er utelatt, bruker prosedyren standardverdiene for dem.

Gå tilbake til forrige eksempel, for å gjøre et heltallsargument til en funksjon valgfritt, vil det bli erklært slik:

Sub AddToCells(Valgfri i Som heltall = 0)

I dette tilfellet, heltallsargumentet i standard vil være 0.

Det kan være flere valgfrie argumenter i en prosedyre, som alle er oppført på slutten av argumentlisten.

Sende argumenter etter verdi og etter referanse

Argumenter i VBA kan overføres til en prosedyre på to måter:

• ByVal – sende et argument etter verdi. Dette betyr at bare verdien (det vil si en kopi av argumentet) sendes til prosedyren, og derfor vil eventuelle endringer som gjøres i argumentet inne i prosedyren gå tapt når prosedyren avsluttes.
• AvRef – sende et argument ved referanse. Det vil si at den faktiske adressen til argumentplasseringen i minnet sendes til prosedyren. Eventuelle endringer som gjøres i et argument inne i prosedyren vil bli lagret når prosedyren avsluttes.

Bruke nøkkelord ByVal or AvRef i prosedyreerklæringen kan du spesifisere hvordan argumentet overføres til prosedyren. Dette er vist i eksemplene nedenfor:

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

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

Husk at argumenter i VBA sendes som referanse som standard. Med andre ord, hvis søkeord ikke brukes ByVal or AvRef, så sendes argumentet ved referanse.

Før du fortsetter med prosedyrene Funksjon и Sub mer detaljert vil det være nyttig å ta en ny titt på funksjonene og forskjellene mellom disse to typene prosedyrer. Følgende er korte diskusjoner av VBA-prosedyrer Funksjon и Sub og enkle eksempler vises.

VBA-prosedyre «Funksjon»

VBA-redaktøren gjenkjenner prosedyren Funksjonnår den støter på en gruppe kommandoer innelukket mellom følgende åpnings- og avslutningssetninger:

Funksjon ... Avslutt funksjon

Som nevnt tidligere, prosedyren Funksjon i VBA (i motsetning til Sub) returnerer en verdi. Følgende regler gjelder for returverdier:

• Datatypen til returverdien må deklareres i overskriften på prosedyren Funksjon.
• Variabelen som inneholder returverdien må ha samme navn som prosedyren Funksjon. Denne variabelen trenger ikke å deklareres separat, da den alltid eksisterer som en integrert del av prosedyren. Funksjon.

Dette er godt illustrert i følgende eksempel.

VBA-funksjonseksempel: Utføre en matematisk operasjon på 3 tall

Følgende er et eksempel på en VBA-prosedyrekode Funksjon, som tar tre argumenter av typen Dobbel (dobbel presisjon flyttall tall). Som et resultat returnerer prosedyren et annet nummer av typen Dobbellik summen av de to første argumentene minus det tredje argumentet:

Funksjon SumMinus(dNum1 Som Dobbel, dNum2 Som Dobbel, dNum3 Som Dobbel) Som Dobbel SumMinus = dNum1 + dNum2 - dNum3 Sluttfunksjon

Denne veldig enkle VBA-prosedyren Funksjon illustrerer hvordan data sendes til en prosedyre gjennom argumenter. Du kan se at datatypen som returneres av prosedyren er definert som Dobbel (ordene sier Som dobbel etter listen over argumenter). Dette eksemplet viser også hvordan resultatet av prosedyren Funksjon lagret i en variabel med samme navn som prosedyrenavnet.

Kalle VBA-prosedyren "Funksjon"

Hvis ovennevnte enkle prosedyre Funksjon satt inn i en modul i Visual Basic-editoren, kan den kalles fra andre VBA-prosedyrer eller brukes på et regneark i en Excel-arbeidsbok.

Kalle VBA-prosedyren "Funksjon" fra en annen prosedyre

Prosedyre Funksjon kan kalles fra en annen VBA-prosedyre ved ganske enkelt å tilordne den prosedyren til en variabel. Følgende eksempel viser et kall til en prosedyre Summinus, som ble definert ovenfor.

Sub main() Dim total som dobbel total = SumMinus(5, 4, 3) End Sub

Kalle VBA-prosedyren "Function" fra et regneark

VBA-prosedyre Funksjon kan kalles fra et Excel-regneark på samme måte som enhver annen innebygd Excel-funksjon. Derfor prosedyren opprettet i forrige eksempel Funksjon - Summinus kan kalles ved å skrive inn følgende uttrykk i en regnearkcelle:

=SumMinus(10, 5, 2)

VBA prosedyre «Sub»

VBA-redaktøren forstår at det ligger en prosedyre foran seg Subnår den støter på en gruppe kommandoer innelukket mellom følgende åpnings- og avslutningssetninger:

Sub ... End Sub

VBA-prosedyre “Sub”: Eksempel 1. Senterjustering og endring av skriftstørrelse i et valgt celleområde

Tenk på et eksempel på en enkel VBA-prosedyre Sub, hvis oppgave er å endre formateringen av det valgte celleområdet. Cellene er sentrert (både vertikalt og horisontalt) og skriftstørrelsen endres til brukerspesifisert:

Sub Format_Centered_And_Sized(Valgfri iFontSize Som heltall = 10) Selection.Horizontal Alignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Denne prosedyren Sub utfører handlinger, men returnerer ikke et resultat.

Dette eksemplet bruker også et valgfritt argument Skriftstørrelse. Hvis argumentet Skriftstørrelse ikke gått til prosedyre Sub, så er standardverdien 10. Men hvis argumentet Skriftstørrelse gått over til prosedyre Sub, vil det valgte celleområdet bli satt til skriftstørrelsen spesifisert av brukeren.

VBA-underprosedyre: Eksempel 2: Senterjustering og fet skrift i valgt celleområde

Følgende prosedyre ligner på den som nettopp ble diskutert, men denne gangen, i stedet for å endre størrelse, bruker den en fet skriftstil på det valgte celleområdet. Dette er en eksempelprosedyre Sub, som ikke tar noen argumenter:

Sub Format_Centered_And_Fet() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Fet = True End Sub

Kalle "Sub"-prosedyre i Excel VBA

Kalle VBA-prosedyren "Sub" fra en annen prosedyre

For å kalle en VBA-prosedyre Sub fra en annen VBA-prosedyre, må du skrive nøkkelordet Anrop, prosedyrenavn Sub og videre i parentes er argumentene for prosedyren. Dette er vist i eksemplet nedenfor:

Sub main() Call Format_Centered_And_Sized(20) End Sub

Hvis prosedyren Format_Centered_And_Sized har mer enn ett argument, må de skilles med komma. Som dette:

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

Ring VBA-prosedyren "Sub" fra regnearket

Prosedyre Sub kan ikke legges direkte inn i en Excel-arkcelle, slik det kan gjøres med en prosedyre Funksjonfordi prosedyren Sub returnerer ikke en verdi. Imidlertid prosedyrer Sub, som ikke har noen argumenter og er erklært som offentlig (som vist nedenfor) vil være tilgjengelig for brukere av regnearket. Således, hvis de enkle prosedyrene diskutert ovenfor Sub satt inn i en modul i Visual Basic Editor, prosedyren Format_Centered_And_Fet vil være tilgjengelig for bruk i et Excel-regneark, og prosedyren Format_Centered_And_Sized – vil ikke være tilgjengelig fordi den har argumenter.

Her er en enkel måte å kjøre (eller utføre) en prosedyre Sub, tilgjengelig fra regnearket:

• Press Alt + F8 (trykk på tasten andre og mens du holder den nede, trykker du på tasten F8).
• I listen over makroer som vises, velg den du vil kjøre.
• Press Kjør (løpe)

For å utføre en prosedyre Sub raskt og enkelt kan du tilordne en hurtigtast til den. For dette:

• Press Alt + F8.
• I listen over makroer som vises, velg den du vil tilordne en hurtigtast til.
• Press parametere (Alternativer) og i dialogboksen som vises, skriv inn hurtigtasten.
• Press OK og lukk dialogen Makro (Makro).

Oppmerksomhet: Når du tilordner en hurtigtast til en makro, sørg for at den ikke brukes som standard i Excel (f.eks. Ctrl + C). Hvis du velger en allerede eksisterende hurtigtast, vil den bli tilordnet til makroen på nytt, og som et resultat kan brukeren starte makroen ved et uhell.

VBA-prosedyreomfang

Del 2 av denne opplæringen diskuterte omfanget av variabler og konstanter og rollen til nøkkelord. offentlig и Privat. Disse nøkkelordene kan også brukes med VBA-prosedyrer:

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

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

Husk at hvis før du erklærer en VBA-prosedyre Funksjon or Sub nøkkelordet ikke er satt inn, er standardegenskapen satt for prosedyren offentlig (det vil si at den vil være tilgjengelig overalt i dette VBA-prosjektet). Dette er i motsetning til variabeldeklarasjoner, som som standard er det Privat.

Tidlig utgang fra VBA-prosedyrene "Function" og "Sub"

Hvis du trenger å avslutte utførelsen av en VBA-prosedyre Funksjon or Sub, uten å vente på dens naturlige slutt, så er det operatører for dette Utgangsfunksjon и Exit Sub. Bruken av disse operatørene er vist nedenfor med en enkel prosedyre som eksempel. FunksjonEn som forventer å få et positivt argument for å utføre ytterligere operasjoner. Hvis en ikke-positiv verdi sendes til prosedyren, kan ingen ytterligere operasjoner utføres, så brukeren skal vises en feilmelding og prosedyren skal avsluttes umiddelbart:

Funksjon VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 Hvis sVAT_Rate <= 0 Then MsgBox "Forventet en positiv verdi på sVAT_Rate men mottatt " & sVAT_Rate Avslutt funksjon End If ... End Function

Vær oppmerksom på at før du fullfører prosedyren Funksjon - VAT_Beløp, er en innebygd VBA-funksjon satt inn i koden MsgBox, som viser en advarselspopup til brukeren.