Utilizați funcțiile de foaie de lucru într-un Macro - Exemple de cod VBA

Există multe modalități de a utiliza funcții în VBA. VBA vine încărcat cu multe funcții încorporate. Puteți chiar să vă creați propriile funcții (UDF). Cu toate acestea, puteți utiliza, de asemenea, multe dintre funcțiile Excel în VBA utilizând Application.WorksheetFunction.

Cum se utilizează funcțiile foii de lucru în VBA

Pentru a accesa o funcție Excel în VBA adăugați Application.WorksheetFunction în fața funcției pe care doriți să o apelați. În exemplul de mai jos, vom apela funcția Max a Excel:

12	Reduceți valoarea maximă cât timpmaxvalue = Application.WorksheetFunction.Max (Range ("a1"). Value, Range ("a2"). Value)

Sintaxa funcțiilor este aceeași, cu toate acestea veți introduce argumentele funcției la fel ca orice altă funcție VBA.

Observați că sintaxa funcției Max apare atunci când tastați (similar cu funcțiile VBA):

Foaie de lucru Metoda funcției

WorksheetFunction este o metodă a obiectului Application. Vă permite accesul la multe (nu la toate) funcțiile standard ale foii de lucru Excel. În general, nu veți avea acces la nicio funcție a foii de lucru care are o versiune VBA corespunzătoare.

Puteți vedea mai jos o listă cu multe dintre cele mai comune funcții ale foii de lucru.

Application.WorksheetFunction vs. Application

Există de fapt două modalități de a accesa aceste funcții:

Application.WorksheetFunction (așa cum se vede mai sus):

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

sau puteți omite Foaia de lucru

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

Din păcate, omiterea Funcției Foaie de lucru va elimina Intellisense care afișează sintaxa (vezi imaginea de mai sus). Cu toate acestea, are un mare avantaj potențial: Eroare de manipulare.

Dacă utilizați aplicația și funcția dvs. generează o eroare, aceasta va returna valoarea erorii. Dacă utilizați metoda WorksheetFunction, VBA va arunca o eroare în timpul rulării. Desigur, puteți gestiona eroarea VBA, dar de obicei este mai bine să evitați eroarea în primul rând.

Să vedem un exemplu pentru a vedea diferența:

Foaia de lucru Vlookup Funcția de gestionare a erorilor

Vom încerca să realizăm un Vlookup care nu va avea ca rezultat un meci. Deci funcția Vlookup va returna o eroare.

În primul rând, vom folosi metoda WorksheetFunction. Observați cum VBA generează o eroare:

Apoi omitem Foaia de lucru. Observați cum

În continuare vom omite Funcția Foaie de lucru. Observați cum nu este aruncată nicio eroare și în schimb funcția „valoare” conține valoarea erorii din Vlookup.

Lista de funcții a foii de lucru VBA

Mai jos veți găsi o listă cu cele mai multe dintre funcțiile comune ale foii de lucru VBA.

Funcţie	Descriere
Logic
ȘI	Verifică dacă sunt îndeplinite toate condițiile. ADEVARAT FALS
DACĂ	Dacă condiția este îndeplinită, faceți ceva, dacă nu, faceți altceva.
IFERROR	Dacă rezultatul este o eroare, atunci faceți altceva.
SAU	Verifică dacă sunt îndeplinite condițiile. ADEVARAT FALS
Căutare și referințe
ALEGE	Alege o valoare dintr-o listă în funcție de numărul poziției sale.
CĂUTARE	Căutați o valoare în primul rând și returnați o valoare.
INDEX	Returnează o valoare bazată pe numerele de coloană și rând.
PRIVEȘTE ÎN SUS	Căutați valori orizontal sau vertical.
MECI	Căutați o valoare într-o listă și îi returnează poziția.
TRANSPUNE	Întoarce orientarea unei game de celule.
CĂUTARE	Căutați o valoare în prima coloană și returnați o valoare.
Data și ora
DATA	Returnează o dată din an, lună și zi.
DATEVALUE	Convertește o dată stocată ca text într-o dată validă
ZI	Returnează ziua ca număr (1-31).
ZILE360	Returnează zile între 2 date într-un an de 360 ​​de zile.
EDATAȚI	Returnează o dată, la n luni distanță de la o dată de început.
EOMONTH	Returnează ultima zi a lunii, data de n luni.
ORA	Returnează ora ca număr (0-23).
MINUT	Returnează minutul ca număr (0-59).
LUNĂ	Returnează luna ca număr (1-12).
ZILE DE REȚEA	Numărul de zile lucrătoare între 2 date.
NETWORKDAYS.INTL	Zile lucrătoare între 2 date, weekenduri personalizate.
ACUM	Returnează data și ora curente.
AL DOILEA	Returnează al doilea ca număr (0-59)
TIMP	Returnează timpul de la o oră, minut și secundă.
TIMEVALUE	Convertește o oră stocată ca text într-o oră validă.
SĂPTĂMÂNĂ	Returnează ziua săptămânii ca număr (1-7).
SĂPTĂMÂNĂ	Returnează numărul săptămânii într-un an (1-52).
ZI DE LUCRU	Data n zile lucrătoare de la o dată.
AN	Returnează anul.
YEARFRAC	Returnează fracțiunea de an între 2 date.
Inginerie
CONVERTIT	Convertiți numărul de la o unitate la alta.
Financiar
FV	Calculează valoarea viitoare.
PV	Calculează valoarea actuală.
NPER	Calculează numărul total de perioade de plată.
PMT	Calculează suma plății.
RATĂ	Calculează rata dobânzii.
VAN	Calculează valoarea actuală netă.
IRR	Rata internă de rentabilitate pentru un set de CF periodice.
XIRR	Rata internă de rentabilitate pentru un set de CF-uri non-periodice.
PREȚ	Calculează prețul unei obligațiuni.
INTRAREA	Rata dobânzii unui titlu investit integral.
informație
ISERR	Testați dacă valoarea celulei este o eroare, ignoră # N / A. ADEVARAT FALS
ISERROR	Testați dacă valoarea celulei este o eroare. ADEVARAT FALS
ESTE CHIAR	Testați dacă valoarea celulei este uniformă. ADEVARAT FALS
ISLOGIC	Testați dacă celula este logică (ADEVĂRAT sau FALS). ADEVARAT FALS
ISNA	Testați dacă valoarea celulei este # N / A. ADEVARAT FALS
ISNONTEXT	Testați dacă celula nu este text (celulele goale nu sunt text). ADEVARAT FALS
ISNUMBER	Testați dacă celula este un număr. ADEVARAT FALS
ISODD	Testați dacă valoarea celulei este impar. ADEVARAT FALS
ISTEXT	Testați dacă celula este text. ADEVARAT FALS
TIP	Returnează tipul de valoare dintr-o celulă.
Matematica
ABS	Calculează valoarea absolută a unui număr.
AGREGAT	Definiți și efectuați calcule pentru o bază de date sau o listă.
TAVAN	Rotunjește un număr până la cel mai apropiat multiplu specificat.
COS	Returnează cosinusul unui unghi.
GRADE	Convertește radianii în grade.
DSUM	Sumează înregistrările bazei de date care îndeplinesc anumite criterii.
CHIAR	Rotunjește la cel mai apropiat număr întreg.
EXP	Calculează valoarea exponențială pentru un număr dat.
FAPT	Returnează factorialul.
PODEA	Rotunjește un număr în jos, până la cel mai apropiat multiplu specificat.
GCD	Returnează cel mai mare divizor comun.
INT	Rotunjește un număr până la cel mai apropiat număr întreg.
LCM	Returnează cel mai mic multiplu comun.
LN	Returnează logaritmul natural al unui număr.
BUTURUGA	Returnează logaritmul unui număr la o bază specificată.
LOG10	Returnează logaritmul de bază 10 al unui număr.
MROUND	Rotunjește un număr la un multiplu specificat.
CIUDAT	Rotunjește la cel mai apropiat număr impar.
PI	Valoarea PI.
PUTERE	Calculează un număr ridicat la o putere.
PRODUS	Multiplică o serie de numere.
COEFICIENT	Returnează rezultatul întreg al diviziunii.
RADIANI	Convertește un unghi în radiani.
RANDBETWEEN	Calculează un număr aleatoriu între două numere.
RUNDĂ	Rotunjește un număr la un număr specificat de cifre.
ROTUNDAT	Rotunjește un număr în jos (spre zero).
A ROTUNJI	Rotunjește un număr în sus (departe de zero).
PĂCAT	Returnează sinusul unui unghi.
SUBTOTAL	Returnează o statistică sumară pentru o serie de date.
SUMĂ	Adună numere împreună.
SUMIF	Suma numerele care îndeplinesc un criteriu.
SUME	Suma numerele care îndeplinesc mai multe criterii.
SUMPRODUCT	Multiplică matrici de numere și însumează matricea rezultată.
TAN	Returnează tangenta unui unghi.
Statistici
IN MEDIE	Numere medii.
MEDIEIF	Medii de numere care îndeplinesc un criteriu.
MEDII	Medii de numere care îndeplinesc mai multe criterii.
CORREL	Calculează corelația a două serii.
NUMARA	Numără celulele care conțin un număr.
COUNTA	Numărați celulele care sunt necompletate.
COUNTBLANK	Numără celulele necompletate.
COUNTIF	Numără celulele care îndeplinesc un criteriu.
COUNTIFI	Numără celulele care îndeplinesc mai multe criterii.
PREVIZIUNE	Preziceți valorile y viitoare din linia de tendință liniară.
FRECVENȚĂ	Numără valorile care se încadrează în intervalele specificate.
CREŞTERE	Calculează valorile Y pe baza creșterii exponențiale.
INTERCEPTA	Calculează interceptarea Y pentru o linie care se potrivește cel mai bine.
MARE	Returnează cea de-a cincea cea mai mare valoare.
CEL MAI LIN	Returnează statistici despre un trendline.
MAX	Returnează cel mai mare număr.
MEDIAN	Returnează numărul mediu.
MIN	Returnează cel mai mic număr.
MOD	Returnează cel mai frecvent număr.
PERCENTIL	Returnează percentila a K.
PERCENTILE.INC	Returnează percentila a K. Unde k este inclusiv.
PERCENTILE.EXC	Returnează percentila a K. Unde k este exclusiv.
QUARTILE	Returnează valoarea quartilei specificată.
QUARTILE.INC	Returnează valoarea quartilei specificată. Inclusiv.
QUARTILE.EXC	Returnează valoarea quartilei specificată. Exclusiv.
RANG	Rangul unui număr dintr-o serie.
RANK.AVG	Rangul unui număr dintr-o serie. Medii.
RANK.EQ	Rangul unui număr dintr-o serie. A pacali.
PANTĂ	Calculează panta din regresia liniară.
MIC	Returnează cea de-a cincea cea mai mică valoare.
STDEV	Calculează abaterea standard.
STDEV.P	Calculează SD pentru o întreagă populație.
STDEV.S	Calculează SD-ul unui eșantion.
STDEVP	Calculează SD pentru o întreagă populație
TENDINŢĂ	Calculează valorile Y pe baza unei linii de trend.
Text
CURAT	Elimină toate caracterele neprimabile.
DOLAR	Convertește un număr în text în format valutar.
GĂSI	Localizează poziția textului într-o celulă. Sensibil la majuscule.
STÂNGA	Trunchiază textul unui număr de caractere din stânga.
LEN	Numără numărul de caractere din text.
MID	Extrage text din mijlocul unei celule.
ADEVĂRAT	Convertește textul în majuscule.
A INLOCUI	Înlocuiește textul pe baza locației sale.
REPT	Repetă textul de mai multe ori.
DREAPTA	Trunchiază un număr de caractere din dreapta.
CĂUTARE	Localizează poziția textului într-o celulă. Nu este sensibil la majuscule.
SUBSTITUI	Găsește și înlocuiește textul. Caz sensibil.
TEXT	Convertește o valoare în text cu un anumit format de număr.
TUNDE	Elimină toate spațiile suplimentare din text.