Brug regnearksfunktioner i en makro - VBA -kodeeksempler

Der er mange måder at bruge funktioner på i VBA. VBA leveres fyldt med mange indbyggede funktioner. Du kan endda oprette dine egne funktioner (UDF'er). Du kan dog også udnytte mange af Excels funktioner i VBA ved at bruge Application.WorksheetFunction.

Sådan bruges regnearksfunktioner i VBA

For at få adgang til en Excel -funktion i VBA skal du tilføje Application.WorksheetFunction foran den funktion, du vil ringe til. I eksemplet herunder kalder vi Excel's Max Function:

12	Dim maksimal værdi så langmaxvalue = Application.WorksheetFunction.Max (Range ("a1"). Værdi, Range ("a2"). Værdi)

Syntaksen for funktionerne er den samme, men du vil indtaste funktionsargumenterne ligesom alle andre VBA -funktioner.

Bemærk, at syntaks for Max -funktionen vises, når du skriver (ligner med VBA -funktioner):

Arbejdsark Funktionsmetode

WorksheetFunction er en metode til applikationsobjekt. Det giver dig adgang til mange (ikke alle) standard Excel -regnearksfunktioner. Generelt får du ikke adgang til regnearksfunktioner, der har en tilsvarende VBA -version.

Du kan se en liste over mange af de mest almindelige regnearkfunktioner nedenfor.

Application.WorksheetFunction vs.Application

Der er faktisk to måder at få adgang til disse funktioner:

Application.WorksheetFunction (som set ovenfor):

1	maxvalue = Application.WorksheetFunction.Max (Range ("a1"). Værdi, Range ("a2"). Værdi)

eller du kan udelade regnearksfunktionen

1	maxvalue = Application.Max (Range ("a1"). Value, Range ("a2"). Value)

Desværre vil udeladelse af regnearksfunktionen fjerne Intellisense, der viser syntaksen (se billedet ovenfor). Det har dog en stor potentiel fordel: Fejlhåndtering.

Hvis du bruger Application, og din funktion genererer en fejl, returnerer den fejlværdien. Hvis du bruger metoden WorksheetFunction, udsender VBA en fejl i løbetiden. Selvfølgelig kan du håndtere VBA -fejlen, men det er normalt bedre at undgå fejlen i første omgang.

Lad os se på et eksempel for at se forskellen:

Vlookup -regneark Funktionsfejlhåndtering

Vi vil forsøge at udføre en Vlookup, der ikke vil resultere i en kamp. Så Vlookup -funktionen returnerer en fejl.

Først vil vi bruge metoden WorksheetFunction. Bemærk, hvordan VBA kaster en fejl:

Dernæst udelader vi regnearksfunktionen. Læg mærke til hvordan

Dernæst vil vi udelade regnearksfunktionen. Læg mærke til, hvordan der ikke kastes en fejl, og i stedet indeholder funktionen ‘værdi’ fejlværdien fra Vlookup.

Liste over funktioner i VBA -regneark

Nedenfor finder du en liste over de fleste af de almindelige VBA -arbejdsarkfunktioner.

Fungere	Beskrivelse
Logisk
OG	Kontrollerer, om alle betingelser er opfyldt. SANDT FALSK
HVIS	Hvis betingelsen er opfyldt, gør noget, hvis ikke, gør noget andet.
IFERROR	Hvis resultatet er en fejl, skal du gøre noget andet.
ELLER	Kontrollerer, om nogen betingelser er opfyldt. SANDT FALSK
Opslag og reference
VÆLGE	Vælger en værdi fra en liste baseret på dens positionsnummer.
HLOOKUP	Slå en værdi op i den første række, og returner en værdi.
INDEKS	Returnerer en værdi baseret på kolonne- og rækkenumre.
KIG OP	Slår værdier op enten vandret eller lodret.
MATCH	Søger efter en værdi på en liste og returnerer sin position.
OVERFLADE	Vender retningen af ​​en række celler.
VLOOKUP	Slå en værdi op i den første kolonne, og returner en værdi.
Dato tid
DATO	Returnerer en dato fra år, måned og dag.
DATOVÆRDI	Konverterer en dato, der er gemt som tekst, til en gyldig dato
DAG	Returnerer dagen som et tal (1-31).
DAGE 360	Returnerer dage mellem 2 datoer i et 360 -dages år.
EDATE	Returnerer en dato, n måneder væk fra en startdato.
EOMONTH	Returnerer den sidste dag i måneden, n måneder væk dato.
TIME	Returnerer timen som et tal (0-23).
MINUT	Returnerer minuttet som et tal (0-59).
MÅNED	Returnerer måneden som et tal (1-12).
NETVÆRKSDAGE	Antal arbejdsdage mellem 2 datoer.
NETWORKDAYS.INTL	Arbejdsdage mellem 2 datoer, tilpassede weekender.
NU	Returnerer den aktuelle dato og klokkeslæt.
SEKUND	Returnerer den anden som et tal (0-59)
TID	Returnerer tiden fra en time, minut og sekund.
TIMEVALUE	Konverterer en tid, der er gemt som tekst, til en gyldig tid.
UGE	Returnerer ugedagen som et tal (1-7).
UGE	Returnerer ugetallet i et år (1-52).
ARBEJDSDAG	Datoen n arbejdsdage fra en dato.
ÅR	Returnerer året.
ÅRFRAC	Returnerer brøkdelen af ​​et år mellem 2 datoer.
ingeniørarbejde
KONVERTERE	Konverter tal fra en enhed til en anden.
Finansiel
FV	Beregner den fremtidige værdi.
PV	Beregner nutidsværdien.
NPER	Beregner det samlede antal betalingsperioder.
PMT	Beregner betalingsbeløbet.
SATS	Beregner renten.
NPV	Beregner den nuværende nutidsværdi.
IRR	Den interne afkast for et sæt periodiske CF'er.
XIRR	Den interne afkast for et sæt ikke-periodiske CF'er.
PRIS	Beregner prisen på en obligation.
INTRATE	Renten på et fuldt investeret værdipapir.
Information
ISERR	Test om celleværdien er en fejl, ignorerer #N/A. SANDT FALSK
FEJL	Test om celleværdien er en fejl. SANDT FALSK
ISEVEN	Test om celleværdien er jævn. SANDT FALSK
ISLOGISK	Test om cellen er logisk (SAND eller FALSK). SANDT FALSK
ISNA	Test om celleværdien er #N/A. SANDT FALSK
ISNONTEXT	Test om cellen ikke er tekst (tomme celler er ikke tekst). SANDT FALSK
ISNUMBER	Test om cellen er et tal. SANDT FALSK
ISODD	Test om celleværdien er ulige. SANDT FALSK
ISTEXT	Test om cellen er tekst. SANDT FALSK
TYPE	Returnerer værditypen i en celle.
Matematik
ABS	Beregner den absolutte værdi af et tal.
SAMLET	Definer og udfør beregninger for en database eller en liste.
LOFT	Runder et tal op til det nærmeste angivne multiplum.
COS	Returnerer cosinus for en vinkel.
GRADER	Konverterer radianer til grader.
DSUM	Summer database poster, der opfylder visse kriterier.
OGSÅ SELVOM	Runder til nærmeste lige heltal.
EXP	Beregner den eksponentielle værdi for et givet tal.
FAKTUM	Returnerer factorial.
ETAGE	Runder et tal ned til det nærmeste angivne multiplum.
GCD	Returnerer den største fælles divisor.
INT	Runder et tal ned til det nærmeste heltal.
LCM	Returnerer det mindst fælles multiplum.
LN	Returnerer den naturlige logaritme for et tal.
LOG	Returnerer logaritmen for et tal til en bestemt base.
LOG10	Returnerer basis-10-logaritmen for et tal.
MROUND	Afrunder et tal til et bestemt multiplum.
ULIGE	Runder til det nærmeste ulige heltal.
PI	Værdien af ​​PI.
STRØM	Beregner et tal hævet til en effekt.
PRODUKT	Multiplicerer en række tal.
KVITIENT	Returnerer heltalsresultatet af division.
RADIANER	Konverterer en vinkel til radianer.
RANDBETWEEN	Beregner et tilfældigt tal mellem to tal.
RUND	Afrunder et tal til et bestemt antal cifre.
RUND NED	Runder et tal ned (mod nul).
RUNDE OP	Runder et tal op (væk fra nul).
SYND	Returnerer sinus for en vinkel.
SUBTOTAL	Returnerer en opsummerende statistik for en række data.
SUM	Tilføjer tal sammen.
SUMIF	Summer tal, der opfylder et kriterium.
SUMIFS	Summer tal, der opfylder flere kriterier.
SUMPRODUKT	Multiplicerer matriser med tal og summerer det resulterende array.
TAN	Returnerer tangenten af ​​en vinkel.
Statistik
GENNEMSNIT	Gennemsnit tal.
GENNEMSNIT	Gennemsnit tal, der opfylder et kriterium.
GENNEMSNIT	Gennemsnit tal, der opfylder flere kriterier.
CORREL	Beregner korrelationen mellem to serier.
TÆLLE	Tæller celler, der indeholder et tal.
COUNTA	Tæl celler, der ikke er tomme.
COUNTBLANK	Tæller celler, der er tomme.
TÆLLE	Tæller celler, der opfylder et kriterium.
TÆLLER	Tæller celler, der opfylder flere kriterier.
VEJRUDSIGT	Forudsig fremtidige y-værdier fra lineær trendlinje.
FREKVENS	Tæller værdier, der falder inden for angivne områder.
VÆKST	Beregner Y -værdier baseret på eksponentiel vækst.
INTERCEPT	Beregner Y-skæringen for en linje, der passer bedst.
STOR	Returnerer den kth største værdi.
LINEST	Returnerer statistik om en trendlinje.
MAX	Returnerer det største antal.
MEDIAN	Returnerer mediannummeret.
MIN	Returnerer det mindste tal.
MODE	Returnerer det mest almindelige tal.
PERCENTIL	Returnerer kth percentilen.
PERCENTILE.INC	Returnerer kth percentilen. Hvor k er inklusiv.
PERCENTILE.EXC	Returnerer kth percentilen. Hvor k er eksklusivt.
KVARTIL	Returnerer den angivne kvartilværdi.
QUARTILE.INC	Returnerer den angivne kvartilværdi. Inklusive.
QUARTILE.EXC	Returnerer den angivne kvartilværdi. Eksklusiv.
RANG	Rang af et nummer i en serie.
RANK.AVG	Rang af et nummer i en serie. Gennemsnit.
RANK.EQ	Rang af et nummer i en serie. Toprangering.
HÆLDNING	Beregner hældningen fra lineær regression.
LILLE	Returnerer den kth mindste værdi.
STDEV	Beregner standardafvigelsen.
STDEV.P	Beregner SD for en hel befolkning.
STDEV.S	Beregner SD for en prøve.
STDEVP	Beregner SD for en hel befolkning
TREND	Beregner Y -værdier baseret på en trendlinje.
Tekst
REN	Fjerner alle tegn, der ikke kan udskrives.
DOLLAR	Konverterer et tal til tekst i valutaformat.
FIND	Lokaliserer tekstens placering i en celle.Case-sensitive.
VENSTRE	Afkorter tekst et antal tegn fra venstre.
LEN	Tæller antal tegn i teksten.
MIDT	Udtrækker tekst fra midten af ​​en celle.
PASSENDE	Konverterer teksten til den rette bogstav.
ERSTATTE	Erstatter tekst baseret på dens placering.
REPT	Gentager tekst flere gange.
RET	Afkortes tekst et antal tegn fra højre.
SØG	Finder placering af tekst i en celle. Ikke store og små bogstaver.
ERSTATNING	Finder og erstatter tekst. Sagsfølsom.
TEKST	Konverterer en værdi til tekst med et bestemt talformat.
TRIMME	Fjerner alle ekstra mellemrum fra tekst.