Använd kalkylbladsfunktioner i ett makro - exempel på VBA -kod

Det finns många sätt att använda funktioner i VBA. VBA har många inbyggda funktioner. Du kan till och med skapa dina egna funktioner (UDF). Du kan dock också använda många av Excel -funktioner i VBA genom att använda Application.WorksheetFunction.

Så här använder du kalkylbladsfunktioner i VBA

För att komma åt en Excel -funktion i VBA, lägg till Application.WorksheetFunction framför funktionen som du vill ringa. I exemplet nedan kallar vi Excel: s maxfunktion:

12	Dimma maxvärdet så längemaxvalue = Application.WorksheetFunction.Max (Range ("a1"). Value, Range ("a2"). Value)

Syntaxen för funktionerna är densamma, men du kommer att ange funktionsargumenten precis som alla andra VBA -funktioner.

Lägg märke till att syntaxen för maxfunktionen visas när du skriver (liknande med VBA -funktioner):

ArbetsbladFunktionsmetod

WorksheetFunction är en metod för applikationsobjekt. Det ger dig tillgång till många (inte alla) vanliga Excel -kalkylbladsfunktioner. I allmänhet får du inte tillgång till några kalkylbladsfunktioner som har en motsvarande VBA -version.

Du kan se en lista över många av de vanligaste kalkylbladsfunktionerna nedan.

Application.WorksheetFunction vs.Application

Det finns faktiskt två sätt att komma åt dessa funktioner:

Application.WorksheetFunction (enligt ovan):

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

eller så kan du utelämna WorksheetFunction

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

Tyvärr kommer utelämning av arbetsbladsfunktionen att eliminera Intellisense som visar syntaxen (se bilden ovan). Det har dock en stor potentiell fördel: Felhantering.

Om du använder programmet och din funktion genererar ett fel returnerar det felvärdet. Om du använder metoden WorksheetFunction kommer VBA att kasta ett körtidsfel. Naturligtvis kan du hantera VBA -felet, men det är vanligtvis bättre att undvika felet i första hand.

Låt oss titta på ett exempel för att se skillnaden:

Vlookup -arbetsblad Funktionsfelhantering

Vi kommer att försöka utföra en Vlookup som inte kommer att resultera i en match. Så Vlookup -funktionen returnerar ett fel.

Först kommer vi att använda metoden WorksheetFunction. Lägg märke till hur VBA kastar ett fel:

Därefter utelämnar vi arbetsbladsfunktionen. Lägg märke till hur

Därefter utelämnar vi arbetsbladsfunktionen. Lägg märke till hur inget fel kastas och istället innehåller "värde" -funktionen felvärdet från Vlookup.

Funktionslista för VBA -kalkylblad

Nedan hittar du en lista över de flesta vanliga VBA WorksheetFunctions.

Fungera	Beskrivning
Logisk
OCH	Kontrollerar om alla villkor är uppfyllda. SANT FALSKT
OM	Om villkoret är uppfyllt, gör något, om inte, gör något annat.
IFERROR	Om resultatet är ett fel, gör något annat.
ELLER	Kontrollerar om några villkor är uppfyllda. SANT FALSKT
Sökning och referens
VÄLJA	Väljer ett värde från en lista baserat på dess positionsnummer.
HLOOKUP	Leta upp ett värde på den första raden och returnera ett värde.
INDEX	Returnerar ett värde baserat på dess kolumn- och radnummer.
SLÅ UPP	Slår upp värden antingen horisontellt eller vertikalt.
MATCH	Söker efter ett värde i en lista och returnerar sin position.
ÖVRIGT	Vänder riktningen för ett antal celler.
VLOOKUP	Leta upp ett värde i den första kolumnen och returnera ett värde.
Datum Tid
DATUM	Returnerar ett datum från år, månad och dag.
DATEVALUE	Konverterar ett datum som lagras som text till ett giltigt datum
DAG	Returnerar dagen som ett tal (1-31).
DAGAR 360	Returnerar dagar mellan 2 datum under ett 360 dagars år.
EDATE	Returnerar ett datum, n månader kvar från ett startdatum.
EOMONTH	Returnerar den sista dagen i månaden, n månader bort.
TIMME	Returnerar timmen som ett tal (0-23).
MINUT	Returnerar minuten som ett tal (0-59).
MÅNAD	Returnerar månaden som ett tal (1-12).
NÄTVERKSDAGAR	Antal arbetsdagar mellan 2 datum.
NETWORKDAYS.INTL	Arbetsdagar mellan 2 datum, anpassade helger.
NU	Returnerar aktuellt datum och tid.
ANDRA	Returnerar den andra som ett tal (0-59)
TID	Returnerar tiden från en timme, minut och sekund.
TIMEVALUE	Konverterar en tid som lagras som text till en giltig tid.
VECKODAG	Returnerar veckodagen som ett tal (1-7).
VECKAN	Returnerar veckonumret på ett år (1-52).
ARBETSDAG	Datumet n arbetsdagar från ett datum.
ÅR	Returnerar året.
YEARFRAC	Returnerar bråkdelen av ett år mellan 2 datum.
Teknik
KONVERTERA	Konvertera nummer från en enhet till en annan.
Finansiell
FV	Beräknar det framtida värdet.
PV	Beräknar nuvärdet.
NPER	Beräknar det totala antalet betalningsperioder.
PMT	Beräknar betalningsbeloppet.
BETYGSÄTTA	Beräknar räntan.
NPV	Beräknar nuvärdet.
IRR	Den interna avkastningen för en uppsättning periodiska CF: er.
XIRR	Den interna avkastningen för en uppsättning icke-periodiska CF: er.
PRIS	Beräknar priset på en obligation.
INTRATE	Räntan på ett fullt investerat värdepapper.
Information
ISERR	Testa om cellvärdet är ett fel, ignorerar #N/A. SANT FALSKT
FEL	Testa om cellvärdet är ett fel. SANT FALSKT
ÄR JÄMNT	Testa om cellvärdet är jämnt. SANT FALSKT
ISLOGISKT	Testa om cellen är logisk (SANT eller FALSK). SANT FALSKT
ISNA	Testa om cellvärdet är #N/A. SANT FALSKT
ISNONTEXT	Testa om cellen inte är text (tomma celler är inte text). SANT FALSKT
ISNUMBER	Testa om cellen är ett tal. SANT FALSKT
ISODD	Testa om cellvärdet är udda. SANT FALSKT
ISTEXT	Testa om cellen är text. SANT FALSKT
TYP	Returnerar värdetypen i en cell.
Matematik
magmuskler	Beräknar det absoluta värdet för ett tal.
AGGREGATE	Definiera och utför beräkningar för en databas eller en lista.
TAK	Avrundar ett tal upp till närmaste angivna multipel.
COS	Returnerar cosinus för en vinkel.
GRADER	Konverterar radianer till grader.
DSUM	Summer databasposter som uppfyller vissa kriterier.
ÄVEN	Avrundar till närmaste jämna heltal.
EXP	Beräknar det exponentiella värdet för ett givet tal.
FAKTUM	Returnerar faktorn.
GOLV	Avrundar ett tal ner till närmaste angivna multipel.
GCD	Returnerar den största gemensamma delaren.
INT	Avrundar ett tal ner till närmaste heltal.
LCM	Returnerar den minst gemensamma multipeln.
LN	Returnerar den naturliga logaritmen för ett tal.
LOGGA	Returnerar logaritmen för ett tal till en angiven bas.
LOG10	Returnerar bas-10-logaritmen för ett tal.
MROUND	Avrundar ett tal till en angiven multipel.
UDDA	Avrundar till närmaste udda heltal.
PI	Värdet på PI.
KRAFT	Beräknar ett tal som höjs till en effekt.
PRODUKT	Multiplicerar en rad siffror.
KVOT	Returnerar heltalets resultat av division.
RADIANS	Konverterar en vinkel till radianer.
RANDBETWEEN	Beräknar ett slumpmässigt tal mellan två nummer.
RUNDA	Avrundar ett tal till ett visst antal siffror.
AVRUNDA NEDÅT	Avrundar ett tal nedåt (mot noll).
RUNDA UPP	Avrundar ett tal uppåt (bort från noll).
SYND	Returnerar sinus för en vinkel.
DELSUMMA	Returnerar en sammanfattande statistik för en serie data.
BELOPP	Lägger ihop siffror.
SUMIF	Summanummer som uppfyller ett kriterium.
SUMIFIER	Summanummer som uppfyller flera kriterier.
SUMPRODUKT	Multiplicerar matriser med tal och summerar den resulterande matrisen.
SOLBRÄNNA	Returnerar tangenten för en vinkel.
Statistik
GENOMSNITT	Genomsnittssiffror.
GENNEMSNITT	Genomsnittssiffror som uppfyller ett kriterium.
GENNEMSNITT	Genomsnittssiffror som uppfyller flera kriterier.
CORREL	Beräknar korrelationen mellan två serier.
RÄKNA	Räknar celler som innehåller ett tal.
COUNTA	Räkna celler som inte är tomma.
RUNTBLANK	Räknar tomma celler.
RÄKTA	Räknar celler som uppfyller ett kriterium.
RÄKNINGAR	Räknar celler som uppfyller flera kriterier.
PROGNOS	Förutse framtida y-värden från linjär trendlinje.
FREKVENS	Räknar värden som faller inom angivna intervall.
TILLVÄXT	Beräknar Y -värden baserat på exponentiell tillväxt.
GENSKJUTA	Beräknar Y-avlyssningen för en linje som passar bäst.
STOR	Returnerar det kth största värdet.
LINEST	Returnerar statistik om en trendlinje.
MAX	Returnerar det största antalet.
MEDIAN	Returnerar mediannumret.
MIN	Returnerar det minsta antalet.
LÄGE	Returnerar det vanligaste numret.
PERCENTIL	Returnerar kth percentilen.
PERCENTIL.INC	Returnerar kth percentilen. Där k är inkluderande.
PERCENTILE.EXC	Returnerar kth percentilen. Där k är exklusivt.
KVARTIL	Returnerar det angivna kvartilvärdet.
QUARTILE.INC	Returnerar det angivna kvartilvärdet. Inklusive.
QUARTILE.EXC	Returnerar det angivna kvartilvärdet. Exklusiv.
RANG	Rankning av ett nummer inom en serie.
RANK.AVG	Rankning av ett nummer inom en serie. Genomsnitt.
RANK.EQ	Rankning av ett nummer inom en serie. Att skämta.
BACKE	Beräknar lutningen från linjär regression.
SMÅ	Returnerar det kth minsta värdet.
STDEV	Beräknar standardavvikelsen.
STDEV.P	Beräknar SD för en hel befolkning.
STDEV.S	Beräknar SD för ett prov.
STDEVP	Beräknar SD för en hel befolkning
TREND	Beräknar Y -värden baserat på en trendlinje.
Text
RENA	Tar bort alla tecken som inte kan skrivas ut.
DOLLAR	Konverterar ett tal till text i valutaformat.
HITTA	Lokaliserar textens position i en cell.Case-sensitive.
VÄNSTER	Avkortar text ett antal tecken från vänster.
LEN	Räknar antalet tecken i texten.
MITTEN	Extraherar text från mitten av en cell.
RÄTT	Konverterar text till rätt bokstav.
BYTA UT	Ersätter text baserat på platsen.
REPT	Upprepar texten ett antal gånger.
HÖGER	Avkortar text ett antal tecken från höger.
SÖK	Lokaliserar textens position i en cell. Inte skiftlägeskänslig.
ERSÄTTNING	Hittar och ersätter text. Skiftlägeskänsliga.
TEXT	Konverterar ett värde till text med ett visst talformat.
TRIM	Tar bort alla extra mellanslag från text.