Werkbladfuncties gebruiken in een macro - VBA-codevoorbeelden

Er zijn veel manieren om functies in VBA te gebruiken. VBA wordt geleverd met veel ingebouwde functies. U kunt zelfs uw eigen functies (UDF's) maken. U kunt echter ook veel van de Excel-functies in VBA gebruiken door Application.WorksheetFunction te gebruiken.

Werkbladfuncties gebruiken in VBA

Om toegang te krijgen tot een Excel-functie in VBA, voegt u Application.WorksheetFunction toe voor de functie die u wilt aanroepen. In het onderstaande voorbeeld noemen we de Max Function van Excel:

12 Dim maximale waarde zo langmaxvalue = Application.WorksheetFunction.Max(Range("a1").Value, Range("a2").Value)

De syntaxis van de functies is hetzelfde, maar u voert de functieargumenten in zoals u bij elke andere VBA-functie zou doen.

Merk op dat de syntaxis van de Max-functie verschijnt wanneer u typt (vergelijkbaar met VBA-functies):

Werkblad Functie Methode

WorksheetFunction is een methode van Application-object. Hiermee hebt u toegang tot veel (niet alle) standaard Excel-werkbladfuncties. Over het algemeen krijgt u geen toegang tot werkbladfuncties met een overeenkomstige VBA-versie.

Hieronder ziet u een lijst met veel van de meest voorkomende werkbladfuncties.

Toepassing.WerkbladFunctie versus toepassing

Er zijn eigenlijk twee manieren om toegang te krijgen tot deze functies:

Application.WorksheetFunctie (zoals hierboven te zien):

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

of u kunt de werkbladfunctie weglaten

1 maxvalue = Toepassing.Max(Bereik("a1").Waarde, Bereik("a2").Waarde)

Helaas zal het weglaten van de werkbladfunctie de Intellisense elimineren die de syntaxis weergeeft (zie afbeelding hierboven). Het heeft echter één groot potentieel voordeel: Foutafhandeling.

Als u Application gebruikt en uw functie een fout genereert, wordt de foutwaarde geretourneerd. Als u de methode WorksheetFunction gebruikt, genereert VBA een runtime-fout. Natuurlijk kunt u de VBA-fout aan, maar het is meestal beter om de fout in de eerste plaats te vermijden.

Laten we een voorbeeld bekijken om het verschil te zien:

Vlookup-werkblad Functie Foutafhandeling

We zullen proberen een Vlookup uit te voeren die niet tot een match leidt. Dus de Vlookup-functie retourneert een fout.

Eerst gebruiken we de methode WorksheetFunction. Merk op hoe VBA een foutmelding geeft:

Vervolgens laten we de werkbladfunctie weg. Merk op hoe de

Vervolgens laten we de werkbladfunctie weg. Merk op hoe er geen fout wordt gegenereerd en in plaats daarvan bevat de functie 'waarde' de foutwaarde van de Vlookup.

Lijst met VBA-werkbladfuncties

Hieronder vindt u een lijst met de meeste veelvoorkomende VBA-werkbladfuncties.

FunctieBeschrijving
Logisch
ENControleert of aan alle voorwaarden is voldaan. WAAR ONWAAR
INDIENAls aan de voorwaarde is voldaan, doe dan iets, zo niet, doe dan iets anders.
IFERRORAls het resultaat een fout is, doe dan iets anders.
OFControleert of aan eventuele voorwaarden is voldaan. WAAR ONWAAR
Opzoeken en verwijzen
KIEZENKiest een waarde uit een lijst op basis van het positienummer.
HORIZ.ZOEKENZoek een waarde op in de eerste rij en retourneer een waarde.
INHOUDSOPGAVERetourneert een waarde op basis van de kolom- en rijnummers.
OPZOEKENZoekt waarden horizontaal of verticaal op.
BIJ ELKAAR PASSENZoekt naar een waarde in een lijst en geeft zijn positie terug.
TRANSPERENDraait de richting van een reeks cellen om.
VERT.ZOEKENZoek een waarde op in de eerste kolom en retourneer een waarde.
Datum Tijd
DATUMRetourneert een datum van jaar, maand en dag.
DATUMWAARDEConverteert een als tekst opgeslagen datum naar een geldige datum
DAGRetourneert de dag als een getal (1-31).
DAGEN360Retourneert dagen tussen 2 datums in een jaar van 360 dagen.
BEWERKENRetourneert een datum, n maanden verwijderd van een startdatum.
EOMONTHRetourneert de laatste dag van de maand, n maanden afwezigheidsdatum.
UURRetourneert het uur als een getal (0-23).
MINUUTRetourneert de minuut als een getal (0-59).
MAANDRetourneert de maand als een getal (1-12).
NETWERKDAGENAantal werkdagen tussen 2 data.
NETWERKDAGEN.INTLWerkdagen tussen 2 data, aangepaste weekenden.
NURetourneert de huidige datum en tijd.
TWEEDERetourneert de tweede als een getal (0-59)
TIJDRetourneert de tijd van een uur, minuut en seconde.
TIJDSWAARDEConverteert een tijd die is opgeslagen als tekst naar een geldige tijd.
WEEKDAGRetourneert de dag van de week als een getal (1-7).
WEEKNUMMERRetourneert het weeknummer in een jaar (1-52).
WERKDAGDe datum n werkdagen vanaf een datum.
JAARGeeft het jaar terug.
JAARFRACRetourneert de breuk van een jaar tussen 2 datums.
Engineering
OVERZETTENConverteer nummer van de ene eenheid naar de andere.
financieel
FVBerekent de toekomstige waarde.
PVBerekent de huidige waarde.
NPERBerekent het totale aantal betalingstermijnen.
PMTBerekent het betalingsbedrag.
TARIEFBerekent de rentevoet.
NPVBerekent de netto contante waarde.
IRRHet interne rendement voor een reeks periodieke CF's.
XIRRHet interne rendement voor een set niet-periodieke CF's.
PRIJSBerekent de prijs van een obligatie.
INTRAATDe rentevoet van een volledig belegd effect.
Informatie
ISERRTest of celwaarde een fout is, negeert #N/A. WAAR ONWAAR
ISERRORTest of de celwaarde een fout is. WAAR ONWAAR
ISEVENTest of de celwaarde even is. WAAR ONWAAR
ISLOGISCHTest of cel logisch is (TRUE of FALSE). WAAR ONWAAR
ISNATest of de celwaarde #N/A is. WAAR ONWAAR
ISNONTTEXTTest of cel geen tekst is (lege cellen zijn geen tekst). WAAR ONWAAR
ISNUMBERTest of cel een getal is. WAAR ONWAAR
IS VREEMDTest of de celwaarde oneven is. WAAR ONWAAR
ISTEXTTest of cel tekst is. WAAR ONWAAR
TYPERetourneert het type waarde in een cel.
Wiskunde
buikspierenBerekent de absolute waarde van een getal.
AGGREGAATDefinieer en voer berekeningen uit voor een database of een lijst.
PLAFONDRondt een getal naar boven af ​​op het dichtstbijzijnde gespecificeerde veelvoud.
COSRetourneert de cosinus van een hoek.
GRADENConverteert radialen naar graden.
DSOMSomt databaserecords op die aan bepaalde criteria voldoen.
OOK ALRondt af op het dichtstbijzijnde even gehele getal.
EXPBerekent de exponentiële waarde voor een bepaald getal.
FEITRetourneert de faculteit.
VLOERRondt een getal naar beneden af ​​op het dichtstbijzijnde gespecificeerde veelvoud.
GCDRetourneert de grootste gemene deler.
INTRondt een getal naar beneden af ​​op het dichtstbijzijnde gehele getal.
LCMRetourneert het kleinste gemene veelvoud.
LNRetourneert de natuurlijke logaritme van een getal.
LOGRetourneert de logaritme van een getal naar een opgegeven grondtal.
LOG10Retourneert de logaritme met grondtal 10 van een getal.
MROUNDRondt een getal af op een opgegeven veelvoud.
VREEMDRondt af op het dichtstbijzijnde oneven gehele getal.
PIDe waarde van PI.
STROOMBerekent een getal verheven tot een macht.
PRODUCTVermenigvuldigt een reeks getallen.
QUOTIËNTRetourneert het gehele resultaat van deling.
RADIANENConverteert een hoek naar radialen.
RANDTUSSENBerekent een willekeurig getal tussen twee getallen.
RONDERondt een getal af op een opgegeven aantal cijfers.
BENEDEN AFRONDENRondt een getal naar beneden af ​​(naar nul).
NAAR BOVEN AFRONDENRondt een getal naar boven af ​​(weg van nul).
ZONDERetourneert de sinus van een hoek.
SUBTOTAALRetourneert een samenvattende statistiek voor een reeks gegevens.
SOMTelt getallen bij elkaar op.
SUMIFTelt getallen op die aan een criterium voldoen.
SOMMENTelt getallen op die aan meerdere criteria voldoen.
SOMPRODUCTVermenigvuldigt reeksen getallen en somt de resulterende reeks op.
BRUINENRetourneert de tangens van een hoek.
Statistieken
GEMIDDELDGemiddelden cijfers.
GEMIDDELDEIFGemiddelden van getallen die voldoen aan een criterium.
GEMIDDELDE IFSGemiddelden van getallen die aan meerdere criteria voldoen.
CORRELBerekent de correlatie van twee reeksen.
GRAAFTelt cellen die een getal bevatten.
AANTALTel cellen die niet leeg zijn.
LEEGTelt cellen die leeg zijn.
AANTAL.ALSTelt cellen die voldoen aan een criterium.
AANTAL.ALSTelt cellen die aan meerdere criteria voldoen.
VOORSPELLINGVoorspel toekomstige y-waarden van lineaire trendlijn.
FREQUENTIETelt waarden die binnen gespecificeerde bereiken vallen.
GROEIBerekent Y-waarden op basis van exponentiële groei.
ONDERSCHEPPENBerekent het Y-snijpunt voor een best passende lijn.
GROOTRetourneert de k-grootste waarde.
LIJNSCHRetourneert statistieken over een trendlijn.
MAXRetourneert het grootste getal.
MEDIAANRetourneert het mediaangetal.
MINRetourneert het kleinste getal.
MODUSRetourneert het meest voorkomende getal.
PERCENTIELRetourneert het k-percentiel.
PERCENTIEL.INCRetourneert het k-percentiel. Waar k inclusief is.
PERCENTIEL.EXCRetourneert het k-percentiel. Waar k exclusief is.
KwartielRetourneert de opgegeven kwartielwaarde.
KWARTIEL.INCRetourneert de opgegeven kwartielwaarde. Inclusief.
KWARTIEL.EXCRetourneert de opgegeven kwartielwaarde. Exclusief.
RANGRang van een getal binnen een reeks.
RANG.AVGRang van een getal binnen een reeks. Gemiddelden.
RANG.EQRang van een getal binnen een reeks. Een grap uithalen.
HELLINGBerekent de helling van lineaire regressie.
KLEINRetourneert de kde kleinste waarde.
STDEVBerekent de standaarddeviatie.
STDEV.PBerekent de SD van een hele populatie.
STDEV.SBerekent de SD van een monster.
STDEVPBerekent de SD van een hele populatie
TRENDBerekent Y-waarden op basis van een trendlijn.
Tekst
SCHOONVerwijdert alle niet-afdrukbare tekens.
DOLLARConverteert een getal naar tekst in valutanotatie.
VINDLokaliseert de positie van tekst in een cel. Hoofdlettergevoelig.
LINKSKnipt tekst een aantal tekens van links af.
LENTelt het aantal tekens in tekst.
MIDDENExtraheert tekst uit het midden van een cel.
JUISTConverteert tekst naar de juiste hoofdletters.
VERVANGENVervangt tekst op basis van de locatie.
REPTHerhaalt tekst een aantal keer.
RECHTSAFKnipt tekst een aantal tekens van rechts af.
ZOEKENLokaliseert de positie van tekst in een cel. Niet hoofdlettergevoelig.
VERVANGINGZoekt en vervangt tekst. Hoofdlettergevoelig.
TEKSTConverteert een waarde naar tekst met een specifieke getalnotatie.
TRIMVerwijdert alle extra spaties uit tekst.
wave wave wave wave wave