Crearea unei funcții definite de utilizator (UDF) în Excel VBA (Ultimate Guide)

Cu VBA, puteți crea o funcție personalizată (numită și funcție definită de utilizator) care poate fi utilizată în foile de lucru la fel ca funcțiile obișnuite.

Acestea sunt utile atunci când funcțiile Excel existente nu sunt suficiente. În astfel de cazuri, puteți crea propria funcție personalizată definită de utilizator (UDF) pentru a satisface nevoile dvs. specifice.

În acest tutorial, voi acoperi totul despre crearea și utilizarea funcțiilor personalizate în VBA.

Dacă sunteți interesat să învățați VBA într-un mod ușor, consultați Instruire online VBA Excel.

Ce este o procedură de funcționare în VBA?

O procedură funcțională este un cod VBA care efectuează calcule și returnează o valoare (sau o matrice de valori).

Folosind o procedură funcțională, puteți crea o funcție pe care o puteți utiliza în foaia de lucru (la fel ca orice funcție Excel obișnuită, cum ar fi SUM sau VLOOKUP).

Când ați creat o procedură funcțională utilizând VBA, o puteți utiliza în trei moduri:

1. Ca formulă în foaia de lucru, unde poate lua argumente ca intrări și returnează o valoare sau o serie de valori.
2. Ca parte a codului de subrutină VBA sau alt cod funcțional.
3. În formatarea condiționată.

Deși există deja 450+ funcții Excel încorporate disponibile în foaia de lucru, este posibil să aveți nevoie de o funcție personalizată dacă:

• Funcțiile încorporate nu pot face ceea ce doriți să faceți. În acest caz, puteți crea o funcție personalizată pe baza cerințelor dvs.
• Funcțiile încorporate pot realiza munca, dar formula este lungă și complicată. În acest caz, puteți crea o funcție personalizată ușor de citit și de utilizat.

Rețineți că funcțiile personalizate create utilizând VBA pot fi semnificativ mai lente decât funcțiile încorporate. Prin urmare, acestea sunt cele mai potrivite pentru situațiile în care nu puteți obține rezultatul folosind funcțiile încorporate.

Funcția vs. Subrutină în VBA

O „Subrutină” vă permite să executați un set de cod în timp ce o „Funcție” returnează o valoare (sau o matrice de valori).

Pentru a vă oferi un exemplu, dacă aveți o listă de numere (atât pozitive, cât și negative) și doriți să identificați numerele negative, iată ce puteți face cu o funcție și un subrutină.

Un subrutină poate parcurge fiecare celulă din interval și poate evidenția toate celulele care au o valoare negativă în ea. În acest caz, subrutina ajunge să schimbe proprietățile obiectului de gamă (prin schimbarea culorii celulelor).

Cu o funcție personalizată, o puteți folosi într-o coloană separată și poate returna un TRUE dacă valoarea dintr-o celulă este negativă și FALSE dacă este pozitivă. Cu o funcție, nu puteți modifica proprietățile obiectului. Aceasta înseamnă că nu puteți schimba culoarea celulei cu o funcție în sine (cu toate acestea, o puteți face folosind formatarea condiționată cu funcția personalizată).

Când creați o funcție definită de utilizator (UDF) utilizând VBA, puteți utiliza acea funcție în foaia de lucru la fel ca orice altă funcție. Voi acoperi mai multe despre acest lucru în secțiunea „Diferite moduri de utilizare a unei funcții definite de utilizator în Excel”.

Crearea unei funcții simple definite de utilizator în VBA

Permiteți-mi să creez o funcție simplă definită de utilizator în VBA și să vă arăt cum funcționează.

Codul de mai jos creează o funcție care va extrage părțile numerice dintr-un șir alfanumeric.

Funcția GetNumeric (CellRef As String) ca Long Dim StringLength Ca Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Următorul i GetNumeric = Funcția de finalizare a rezultatului

Când aveți codul de mai sus în modul, puteți utiliza această funcție în registrul de lucru.

Mai jos este modul în care această funcție - GetNumeric - poate fi folosit în Excel.

Acum, înainte să vă spun cum este creată această funcție în VBA și cum funcționează, trebuie să știți câteva lucruri:

• Când creați o funcție în VBA, aceasta devine disponibilă în întregul registru de lucru la fel ca orice altă funcție obișnuită.
• Când introduceți numele funcției urmat de semnul egal cu, Excel vă va afișa numele funcției în lista funcțiilor potrivite. În exemplul de mai sus, când am introdus = Obține, Excel mi-a arătat o listă care avea funcția mea personalizată.

Cred că acesta este un bun exemplu când puteți utiliza VBA pentru a crea o funcție simplă de utilizat în Excel. Puteți face același lucru și cu o formulă (așa cum se arată în acest tutorial), dar acest lucru devine complicat și greu de înțeles. Cu acest UDF, trebuie doar să treceți un singur argument și veți obține rezultatul.

Anatomia unei funcții definite de utilizator în VBA

În secțiunea de mai sus, v-am dat codul și v-am arătat cum funcționează funcția UDF într-o foaie de lucru.

Acum hai să ne scufundăm profund și să vedem cum este creată această funcție. Trebuie să plasați codul de mai jos într-un modul din Editorul VB. Acoper acest subiect în secțiunea - „Unde se pune codul VBA pentru o funcție definită de utilizator”.

Funcția GetNumeric (CellRef As String) ca Long 'Această funcție extrage partea numerică din șirul Dim StringLength Ca Integer StringLength = Len (CellRef) Pentru i = 1 La StringLength Dacă este Numeric (Mid (CellRef, i, 1)) Apoi Rezultat = Rezultat & Mid (CellRef, i, 1) Următorul i GetNumeric = Funcția de finalizare a rezultatului

Prima linie a codului începe cu cuvântul - Funcție.

Acest cuvânt spune VBA că codul nostru este o funcție (și nu un subrutină). Cuvântul Funcție este urmat de numele funcției - GetNumeric. Acesta este numele pe care îl vom folosi în foaia de lucru pentru a utiliza această funcție.

• Numele funcției nu poate avea spații în ea. De asemenea, nu puteți denumi o funcție dacă se confruntă cu numele unei referințe de celulă. De exemplu, nu puteți denumi funcția ABC123, deoarece se referă și la o celulă din foaia de lucru Excel.
• Nu ar trebui să acordați funcției dvs. același nume ca cel al unei funcții existente. Dacă faceți acest lucru, Excel va da preferință funcției încorporate.
• Puteți utiliza o subliniere dacă doriți să separați cuvintele. De exemplu, Get_Numeric este un nume acceptabil.

Numele funcției este urmat de câteva argumente între paranteze. Acestea sunt argumentele de care funcția noastră ar avea nevoie de la utilizator. Acestea sunt la fel ca argumentele pe care trebuie să le furnizăm funcțiilor încorporate Excel. De exemplu, într-o funcție COUNTIF, există două argumente (interval și criterii)

În paranteză, trebuie să specificați argumentele.

În exemplul nostru, există un singur argument - CellRef.

Este, de asemenea, o practică bună să specificați la ce tip de argument se așteaptă funcția. În acest exemplu, deoarece vom furniza funcția o referință de celulă, putem specifica argumentul ca un tip „Range”. Dacă nu specificați un tip de date, VBA ar considera că este o variantă (ceea ce înseamnă că puteți utiliza orice tip de date).

Dacă aveți mai multe argumente, le puteți specifica pe cele din aceeași paranteză - separate printr-o virgulă. Vom vedea mai târziu în acest tutorial despre cum să utilizați mai multe argumente într-o funcție definită de utilizator.

Rețineți că funcția este specificată ca tip de date „Șir”. Acest lucru ar spune VBA că rezultatul formulei ar fi de tipul de date String.

Deși pot folosi un tip de date numerice aici (cum ar fi lung sau dublu), acest lucru ar limita intervalul de numere pe care îl poate returna. Dacă am un șir lung de 20 de numere pe care trebuie să îl extrag din șirul general, declararea funcției ca Long sau Double ar da o eroare (deoarece numărul ar fi în afara domeniului său). Prin urmare, am păstrat tipul de date de ieșire a funcției ca Șir.

A doua linie a codului - cea în verde care începe cu un apostrof - este un comentariu. Când citiți codul, VBA ignoră această linie. Puteți utiliza acest lucru pentru a adăuga o descriere sau un detaliu despre cod.

A treia linie a codului declară o variabilă „StringLength” ca tip de date Integer. Aceasta este variabila în care stocăm valoarea lungimii șirului care este analizat de formulă.

A patra linie declară variabila Rezultat ca un tip de date String. Aceasta este variabila în care vom extrage numerele din șirul alfanumeric.

A cincea linie atribuie lungimea șirului din argumentul de intrare variabilei „StringLength”. Rețineți că „CellRef” se referă la argumentul care va fi dat de utilizator în timp ce folosește formula din foaia de lucru (sau o folosește în VBA - pe care o vom vedea mai târziu în acest tutorial).

A șasea, a șaptea și a opta linie fac parte din bucla For Next. Bucla rulează de câte ori există mai multe caractere în argumentul de intrare. Acest număr este dat de funcția LEN și este atribuit variabilei „StringLength”.

Deci, bucla rulează de la „1 la Stringlength”.

În cadrul buclei, instrucțiunea IF analizează fiecare caracter al șirului și, dacă este numerică, adaugă acel caracter numeric variabilei Rezultat. Folosește funcția MID din VBA pentru a face acest lucru.

A doua ultimă linie a codului atribuie valoarea rezultatului funcției. Această linie de cod asigură faptul că funcția returnează valoarea „Rezultat” înapoi în celulă (de unde este apelată).

Ultima linie a codului este End Function. Aceasta este o linie de cod obligatorie care spune VBA că codul funcției se termină aici.

Codul de mai sus explică diferitele părți ale unei funcții personalizate tipice create în VBA. În secțiunile următoare, ne vom scufunda profund în aceste elemente și vom vedea, de asemenea, diferitele moduri de a executa funcția VBA în Excel.

Argumente într-o funcție definită de utilizator în VBA

În exemplele de mai sus, unde am creat o funcție definită de utilizator pentru a obține partea numerică dintr-un șir alfanumeric (GetNumeric), funcția a fost concepută pentru a lua un singur argument.

În această secțiune, voi prezenta cum să creați funcții care nu iau niciun argument pentru cele care acceptă mai multe argumente (necesare, precum și argumente opționale).

Crearea unei funcții în VBA fără niciun argument

În foaia de lucru Excel, avem mai multe funcții care nu acceptă argumente (cum ar fi RAND, AZI, ACUM).

Aceste funcții nu depind de niciun argument de intrare. De exemplu, funcția TODAY ar returna data curentă, iar funcția RAND va returna un număr aleatoriu între 0 și 1.

Puteți crea astfel de funcții similare și în VBA.

Mai jos este codul care vă va da numele fișierului. Nu ia niciun argument deoarece rezultatul pe care trebuie să îl returneze nu depinde de niciun argument.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Codul de mai sus specifică rezultatul funcției ca tip de date String (deoarece rezultatul pe care îl dorim este numele fișierului - care este un șir).

Această funcție atribuie valoarea „ThisWorkbook.Name” funcției, care este returnată atunci când funcția este utilizată în foaia de lucru.

Dacă fișierul a fost salvat, acesta returnează numele cu extensia de fișier, altfel pur și simplu îi dă numele.

Cele de mai sus au însă o problemă.

Dacă numele fișierului se schimbă, nu se va actualiza automat. În mod normal, o funcție se reîmprospătează ori de câte ori există o modificare a argumentelor de intrare. Dar, deoarece nu există argumente în această funcție, funcția nu se recalculează (chiar dacă schimbați numele registrului de lucru, închideți-l și apoi redeschideți din nou).

Dacă doriți, puteți forța o recalculare utilizând comanda rapidă de la tastatură - Control + Alt + F9.

Pentru a face formula să recalculeze ori de câte ori există o modificare în foaia de lucru, trebuie să o linie de cod.

Codul de mai jos face ca funcția să se recalculeze ori de câte ori există o modificare în foaia de lucru (la fel ca alte funcții similare ale foii de lucru, cum ar fi funcția TODAY sau RAND).

Funcție WorkbookName () Ca șir de aplicații.Volatile True WorkbookName = ThisWorkbook.Name Final Function

Acum, dacă modificați numele registrului de lucru, această funcție se va actualiza ori de câte ori există vreo modificare în foaia de lucru sau când redeschideți acest registru de lucru.

Crearea unei funcții în VBA cu un singur argument

Într-una dintre secțiunile de mai sus, am văzut deja cum să creăm o funcție care să ia un singur argument (funcția GetNumeric acoperită mai sus).

Să creăm o altă funcție simplă care să ia un singur argument.

Funcția creată cu codul de mai jos ar converti textul de referință în majuscule. Acum avem deja o funcție în Excel, iar această funcție este doar pentru a vă arăta cum funcționează. Dacă trebuie să faceți acest lucru, este mai bine să utilizați funcția UPPER încorporată.

Funcție ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) Funcție finală

Această funcție utilizează funcția UCase în VBA pentru a modifica valoarea variabilei CellRef. Apoi atribuie valoarea funcției ConvertToUpperCase.

Deoarece această funcție acceptă un argument, nu este necesar să folosim partea Application.Volatile aici. De îndată ce argumentul se schimbă, funcția se va actualiza automat.

Crearea unei funcții în VBA cu mai multe argumente

La fel ca funcțiile foii de lucru, puteți crea funcții în VBA care acceptă mai multe argumente.

Codul de mai jos ar crea o funcție care va extrage textul înainte de delimitatorul specificat. Este nevoie de două argumente - referința celulei care are șirul de text și delimitatorul.

Funcția GetDataBeforeDelimiter (CellRef As Range, Delim As String) ca String Dim Rezultat ca String Dim DelimPosition Ca Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Rezultat = Stânga (CellRef, DelimPosition) GetDataBeforeDelimiter = Funcția de finalizare a rezultatului

Când trebuie să utilizați mai multe argumente într-o funcție definită de utilizator, puteți avea toate argumentele din paranteză, separate printr-o virgulă.

Rețineți că pentru fiecare argument, puteți specifica un tip de date. În exemplul de mai sus, „CellRef” a fost declarat ca tip de date pentru interval și „Delim” a fost declarat ca tip de date String. Dacă nu specificați niciun tip de date, VBA consideră că acestea sunt o variantă de tip de date.

Când utilizați funcția de mai sus în foaia de lucru, trebuie să dați referința celulei care are textul ca prim argument și caracterul (caracterele) delimitator (e) între ghilimele ca al doilea argument.

Apoi verifică poziția delimitatorului utilizând funcția INSTR în VBA. Această poziție este apoi utilizată pentru a extrage toate caracterele dinaintea delimitatorului (folosind funcția STÂNGA).

În cele din urmă, atribuie rezultatul funcției.

Această formulă este departe de a fi perfectă. De exemplu, dacă introduceți un delimitator care nu se găsește în text, ar da o eroare. Acum puteți utiliza funcția IFERROR din foaia de lucru pentru a scăpa de erori sau puteți utiliza codul de mai jos care returnează întregul text atunci când nu găsește delimitatorul.

Funcția GetDataBeforeDelimiter (CellRef As Range, Delim As String) ca String Dim Rezultat ca String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Dacă DelimPosition <0 atunci DelimPosition = Len (CellRef) Rezultat = Left ( CellRef, DelimPosition) GetDataBeforeDelimiter = Funcția de finalizare a rezultatului

Putem optimiza în continuare această funcție.

Dacă introduceți textul (din care doriți să extrageți partea dinaintea delimitatorului) direct în funcție, aceasta vă va da o eroare. Mergeți mai departe … încercați!

Acest lucru se întâmplă deoarece am specificat „CellRef” ca tip de date de interval.

Sau, dacă doriți ca delimitatorul să se afle într-o celulă și să utilizați referința celulei în loc să o codificați în formulă, nu puteți face acest lucru cu codul de mai sus. Acest lucru se datorează faptului că Delim a fost declarat ca un tip de date șir.

Dacă doriți ca funcția să aibă flexibilitatea de a accepta introducerea directă de text sau referințele de celule de la utilizator, trebuie să eliminați declarația tipului de date. Acest lucru ar ajunge să facă argumentul ca o variantă de tip de date, care poate lua orice tip de argument și îl poate procesa.

Codul de mai jos ar face acest lucru:

Funcția GetDataBeforeDelimiter (CellRef, Delim) Ca rezultat String Dim Ca șir Dim DelimPosition Ca întreg DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Dacă DelimPosition <0 atunci DelimPosition = Len (CellRef) Rezultat = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Funcția de finalizare a rezultatului

Crearea unei funcții în VBA cu argumente opționale

Există multe funcții în Excel în care unele dintre argumente sunt opționale.

De exemplu, legenda funcției VLOOKUP are 3 argumente obligatorii și un argument opțional.

Un argument opțional, așa cum sugerează și numele, este opțional de specificat. Dacă nu specificați unul dintre argumentele obligatorii, funcția dvs. vă va da o eroare, dar dacă nu specificați argumentul opțional, funcția dvs. ar funcționa.

Dar argumentele opționale nu sunt inutile. Acestea vă permit să alegeți dintr-o serie de opțiuni.

De exemplu, în funcția VLOOKUP, dacă nu specificați al patrulea argument, VLOOKUP efectuează o căutare aproximativă și dacă specificați ultimul argument ca FALSE (sau 0), atunci se potrivește exact.

Amintiți-vă că argumentele opționale trebuie să vină întotdeauna după toate argumentele necesare. Nu puteți avea argumente opționale la început.

Acum să vedem cum să creați o funcție în VBA cu argumente opționale.

Funcționează doar cu un argument opțional

Din câte știu, nu există nicio funcție încorporată care să ia numai argumente opționale (pot să mă înșel aici, dar nu mă pot gândi la o astfel de funcție).

Dar putem crea unul cu VBA.

Mai jos este codul funcției care vă va oferi data curentă în formatul dd-mm-aaaa dacă nu introduceți niciun argument (adică lăsați-l necompletat) și în formatul „dd mmmm, aaaa” dacă introduceți ceva ca argument (adică orice, astfel încât argumentul să nu fie gol).

Funcție CurrDate (opțional fmt Ca variantă) Dim Rezultat dacă lipsește (fmt) Atunci CurrDate = Format (Data, "zz-ll-aaaa") Altfel CurrDate = Format (Data, "zz mmmm, aaaa") Sfârșit Dacă funcția Sfârșit

Rețineți că funcția de mai sus folosește „IsMissing” pentru a verifica dacă argumentul lipsește sau nu. Pentru a utiliza funcția IsMissing, argumentul dvs. opțional trebuie să fie de tipul de date variante.

Funcția de mai sus funcționează indiferent de ceea ce introduceți ca argument. În cod, verificăm doar dacă argumentul opțional este furnizat sau nu.

Puteți face acest lucru mai robust luând doar valori specifice ca argumente și afișând o eroare în restul cazurilor (așa cum se arată în codul de mai jos).

Funcție CurrDate (opțional fmt ca variantă) Dim Rezultat dacă lipsește (fmt) Apoi CurrDate = Format (Data, "zz-ll-aaaa") ElseIf fmt = 1 Apoi CurrDate = Format (Data, "dd mmmm, aaaa") Altfel CurrDate = CVErr (xlErrValue) End If End Function

Codul de mai sus creează o funcție care arată data în formatul „zz-ll-aaaa” dacă nu este furnizat niciun argument și în format „zz mmmm, aaaa” când argumentul este 1. Acesta dă o eroare în toate celelalte cazuri.

Funcția cu Argumente obligatorii și opționale

Am văzut deja un cod care extrage partea numerică dintr-un șir.

Acum să aruncăm o privire la un exemplu similar care ia atât argumentele necesare, cât și argumentele opționale.

Codul de mai jos creează o funcție care extrage partea de text dintr-un șir. Dacă argumentul opțional este ADEVĂRAT, dă rezultatul cu majuscule, iar dacă argumentul opțional este FALS sau este omis, dă rezultatul așa cum este.

Funcția GetText (CellRef As Range, OptionCase TextCase = False) Ca String Dim StringLength Ca Integer Dim Result As StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Rezultat = Rezultat & Mediu (CellRef, i, 1) Următorul i Dacă TextCase = Adevărat atunci Rezultat = UCase (Rezultat) GetText = Funcția de finalizare a rezultatului

Rețineți că, în codul de mai sus, am inițializat valoarea „TextCase” ca False (căutați în paranteză în prima linie).

Făcând acest lucru, ne-am asigurat că argumentul opțional începe cu valoarea implicită, care este FALS. Dacă utilizatorul specifică valoarea ca ADEVĂRAT, funcția returnează textul cu litere mari și dacă utilizatorul specifică argumentul opțional ca FALS sau îl omite, atunci textul returnat este așa cum este.

Crearea unei funcții în VBA cu o matrice ca argument

Până acum am văzut exemple de creare a unei funcții cu argumente opționale / obligatorii - în care aceste argumente erau o singură valoare.

De asemenea, puteți crea o funcție care poate lua ca argument o matrice. În funcțiile foii de lucru Excel, există multe funcții care acceptă argumente matrice, cum ar fi SUM, VLOOKUP, SUMIF, COUNTIF etc.

Mai jos este codul care creează o funcție care dă suma tuturor numerelor pare din intervalul specificat al celulelor.

Funcție AddEven (CellRef as Range) Dim Cell as Range for Each Cell in CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End if End If Next Cell AddEven = Result End Funcţie

Puteți utiliza această funcție în foaia de lucru și puteți furniza gama de celule care au ca numere ca argument. Funcția ar returna o singură valoare - suma tuturor numerelor pare (așa cum se arată mai jos).

În funcția de mai sus, în loc de o singură valoare, am furnizat o matrice (A1: A10). Pentru ca acest lucru să funcționeze, trebuie să vă asigurați că tipul de date al argumentului poate accepta o matrice.

În codul de mai sus, am specificat argumentul CellRef ca Range (care poate lua o matrice ca intrare). De asemenea, puteți utiliza varianta tipului de date aici.

În cod, există o buclă For Each care trece prin fiecare celulă și verifică dacă este un număr de nu. Dacă nu este, nu se întâmplă nimic și se mută în celula următoare. Dacă este un număr, verifică dacă este par sau nu (utilizând funcția MOD).

La final, se adaugă toate numerele pare și suma este alocată înapoi funcției.

Crearea unei funcții cu un număr nedefinit de argumente

În timp ce creați unele funcții în VBA, este posibil să nu știți numărul exact de argumente pe care un utilizator dorește să le furnizeze. Deci, este necesar să creați o funcție care să accepte cât mai multe argumente sunt furnizate și să le utilizați pentru a returna rezultatul.

Un exemplu de astfel de funcție de foaie de lucru este funcția SUM. Îi puteți furniza mai multe argumente (cum ar fi acesta):

= SUM (A1, A2: A4, B1: B20)

Funcția de mai sus ar adăuga valorile în toate aceste argumente. De asemenea, rețineți că acestea pot fi o singură celulă sau o serie de celule.

Puteți crea o astfel de funcție în VBA având ultimul argument (sau poate fi singurul argument) ca opțional. De asemenea, acest argument opțional ar trebui să fie precedat de cuvântul cheie „ParamArray”.

‘ParamArray’ este un modificator care vă permite să acceptați câte argumente doriți. Rețineți că utilizarea cuvântului ParamArray înainte de un argument face ca argumentul să fie opțional. Cu toate acestea, nu este nevoie să folosiți cuvântul opțional aici.

Acum să creăm o funcție care poate accepta un număr arbitrar de argumente și care ar adăuga toate numerele din argumentele specificate:

Funcție AddArguments (ParamArray arglist () Ca variantă) Pentru fiecare arg In arglist AddArguments = AddArguments + arg Următorul arg End Function

Funcția de mai sus poate lua orice număr de argumente și poate adăuga aceste argumente pentru a da rezultatul.

Rețineți că puteți utiliza doar o singură valoare, o referință de celulă, un boolean sau o expresie ca argument. Nu puteți furniza o matrice ca argument. De exemplu, dacă unul dintre argumentele dvs. este D8: D10, această formulă vă va da o eroare.

Dacă doriți să puteți utiliza ambele argumente cu mai multe celule, trebuie să utilizați codul de mai jos:

Funcție AddArguments (ParamArray arglist () Ca variantă) Pentru fiecare arg In arglist Pentru fiecare celulă În arg AddArguments = AddArguments + Celulă Următoarea celulă Următoarea arg Funcție de finalizare

Rețineți că această formulă funcționează cu mai multe celule și referințe de matrice, cu toate acestea, nu poate procesa valori sau expresii codificate. Puteți crea o funcție mai robustă verificând și tratând aceste condiții, dar aici nu este intenția.

Aici intenția este de a vă arăta cum funcționează ParamArray, astfel încât să puteți permite un număr nedefinit de argumente în funcție. În cazul în care doriți o funcție mai bună decât cea creată de codul de mai sus, utilizați funcția SUM din foaia de lucru.

Crearea unei funcții care returnează o matrice

Până acum am văzut funcții care returnează o singură valoare.

Cu VBA, puteți crea o funcție care returnează o variantă care poate conține o întreagă serie de valori.

Formulele matrice sunt, de asemenea, disponibile ca funcții încorporate în foile de lucru Excel. Dacă sunteți familiarizat cu formulele matrice din Excel, știți că acestea sunt introduse folosind Control + Shift + Enter (în loc de doar Enter). Puteți citi mai multe despre formulele matrice aici. Dacă nu știți despre formulele matrice, nu vă faceți griji, continuați să citiți.

Să creăm o formulă care returnează o matrice de trei numere (1,2,3).

Codul de mai jos ar face acest lucru.

Funcția ThreeNumbers () Ca variantă Dim NumberValue (1 până la 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue Final Funcție

În codul de mai sus, am specificat funcția „ThreeNumbers” ca variantă. Aceasta îi permite să dețină o serie de valori.

Variabila „NumberValue” este declarată ca o matrice cu 3 elemente. Deține cele trei valori și o atribuie funcției „ThreeNumbers”.

Puteți utiliza această funcție în foaia de lucru introducând funcția și apăsând tasta Control + Shift + Enter (țineți apăsată tasta Control și Shift și apoi apăsați Enter).

Când faceți acest lucru, acesta va reveni 1 în celulă, dar, în realitate, deține toate cele trei valori. Pentru a verifica acest lucru, utilizați formula de mai jos:

= MAX (ThreeNumbers ())

Utilizați funcția de mai sus cu Control + Shift + Enter. Veți observa că rezultatul este acum 3, deoarece este cea mai mare valoare din matrice returnată de funcția Max, care obține cele trei numere ca rezultat al funcției noastre definite de utilizator - ThreeNumbers.

Puteți utiliza aceeași tehnică pentru a crea o funcție care returnează o matrice de nume lunare așa cum se arată în codul de mai jos:

Function Months () As Variant Dim Dim MonthName (1 To 12) MonthName (1) = "January" MonthName (2) = "February" MonthName (3) = "March" MonthName (4) = "April" MonthName (5) = "May" MonthName (6) = "June" MonthName (7) = "July" MonthName (8) = "August" MonthName (9) = "September" MonthName (10) = "October" MonthName (11) = "November "MonthName (12) =" December "Months = MonthName End Function

Acum, când introduceți funcția = Luni () în foaia de lucru Excel și utilizați Control + Shift + Enter, va returna întreaga matrice de nume de luni. Rețineți că vedeți doar ianuarie în celulă, deoarece aceasta este prima valoare din matrice. Acest lucru nu înseamnă că matricea returnează doar o valoare.

Pentru a vă arăta faptul că returnează toate valorile, faceți acest lucru - selectați celula cu formula, accesați bara de formule, selectați întreaga formulă și apăsați F9. Aceasta vă va arăta toate valorile pe care le returnează funcția.

Puteți utiliza acest lucru folosind formula INDEX de mai jos pentru a obține o listă cu toate numele lunilor dintr-o singură dată.

= INDEX (Luni (), ROW ())

Acum, dacă aveți multe valori, nu este o practică bună să atribuiți aceste valori una câte una (așa cum am făcut mai sus). În schimb, puteți utiliza funcția Array în VBA.

Deci, același cod în care creăm funcția „Luni” ar deveni mai scurt așa cum se arată mai jos:

Luni funcționale () ca luni variabile = matrice („ianuarie”, „februarie”, „martie”, „aprilie”, „mai”, „iunie”, _ „iulie”, „august”, „septembrie”, „octombrie” , „Noiembrie”, „decembrie”) Funcția de sfârșit

Funcția de mai sus utilizează funcția Array pentru a atribui valorile direct funcției.

Rețineți că toate funcțiile create mai sus returnează o matrice orizontală de valori. Aceasta înseamnă că, dacă selectați 12 celule orizontale (să spunem A1: L1) și introduceți formula = Lunile () în celula A1, acesta vă va oferi toate numele lunii.

Dar dacă doriți aceste valori într-un interval vertical de celule.

Puteți face acest lucru folosind formula TRANSPOSE din foaia de lucru.

Pur și simplu selectați 12 celule verticale (contigue) și introduceți formula de mai jos.

Înțelegerea sferei unei funcții definite de utilizator în Excel

O funcție poate avea două domenii - Public sau Privat.

• A Domeniul public înseamnă că funcția este disponibilă pentru toate foile din registrul de lucru, precum și pentru toate procedurile (Sub și Funcție) din toate modulele din registrul de lucru. Acest lucru este util atunci când doriți să apelați o funcție dintr-un subrutină (vom vedea cum se face acest lucru în secțiunea următoare).
• A Domeniul privat înseamnă că funcția este disponibilă numai în modulul în care există. Nu îl puteți folosi în alte module. De asemenea, nu îl veți vedea în lista de funcții din foaia de lucru. De exemplu, dacă numele funcției dvs. este „Luni ()” și introduceți funcția în Excel (după semnul =), acesta nu vă va afișa numele funcției. O puteți folosi, totuși, dacă introduceți numele formulei.

Dacă nu specificați nimic, funcția este o funcție publică în mod implicit.

Mai jos este o funcție care este o funcție privată:

Funcție privată WorkbookName () As String WorkbookName = ThisWorkbook.Name Funcție finală

Puteți utiliza această funcție în subrutine și procedurile din aceleași module, dar nu o puteți utiliza în alte module. De asemenea, această funcție nu va apărea în foaia de lucru.

Codul de mai jos ar face această funcție publică. De asemenea, va apărea în foaia de lucru.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Diferite moduri de utilizare a unei funcții definite de utilizator în Excel

După ce ați creat o funcție definită de utilizator în VBA, o puteți folosi în mai multe moduri diferite.

Să prezentăm mai întâi modul de utilizare a funcțiilor din foaia de lucru.

Utilizarea UDF-urilor în foi de lucru

Am văzut deja exemple de utilizare a unei funcții create în VBA în foaia de lucru.

Tot ce trebuie să faceți este să introduceți numele funcțiilor și acesta apare în inteligență.

Rețineți că pentru ca funcția să apară în foaia de lucru, trebuie să fie o funcție publică (așa cum se explică în secțiunea de mai sus).

De asemenea, puteți utiliza caseta de dialog Inserare funcție pentru a insera funcția definită de utilizator (utilizând pașii de mai jos). Acest lucru ar funcționa numai pentru funcțiile care sunt publice.

• Accesați fila Date.
• Faceți clic pe opțiunea „Inserați funcția”.
• În caseta de dialog Insert Function, selectați User Defined ca categorie. Această opțiune apare numai atunci când aveți o funcție în Editorul VB (și funcția este Publică).
• Selectați funcția din lista tuturor funcțiilor definite de utilizator public.
• Faceți clic pe butonul Ok.

Pașii de mai sus ar insera funcția în foaia de lucru. De asemenea, afișează o casetă de dialog Argumente funcționale care vă va oferi detalii despre argumente și rezultat.

Puteți utiliza o funcție definită de utilizator la fel ca orice altă funcție din Excel. Acest lucru înseamnă, de asemenea, că îl puteți utiliza cu alte funcții Excel încorporate. De exemplu. formula de mai jos ar da numele registrului de lucru cu majuscule:

= UPPER (WorkbookName ())

Utilizarea funcțiilor definite de utilizator în procedurile și funcțiile VBA

Când ați creat o funcție, o puteți utiliza și în alte sub-proceduri.

Dacă funcția este publică, poate fi utilizată în orice procedură din același modul sau din modul diferit. Dacă este privat, acesta poate fi utilizat numai în același modul.

Mai jos este o funcție care returnează numele registrului de lucru.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Procedura de mai jos apelează funcția și apoi afișează numele într-o casetă de mesaje.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

De asemenea, puteți apela o funcție din altă funcție.

În codurile de mai jos, primul cod returnează numele registrului de lucru, iar al doilea returnează numele cu majuscule apelând prima funcție.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Funcție WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Funcție de sfârșit

Apelarea unei funcții definite de utilizator din alte registre de lucru

Dacă aveți o funcție într-un registru de lucru, puteți apela această funcție și în alte registre de lucru.

Există mai multe moduri de a face acest lucru:

1. Crearea unui supliment
2. Funcția de salvare în registrul personal de lucru macro
3. Referirea funcției dintr-un alt registru de lucru.

Crearea unui supliment

Prin crearea și instalarea unui supliment, veți avea funcția personalizată disponibilă în toate registrele de lucru.

Să presupunem că ați creat o funcție personalizată - „GetNumeric” și o doriți în toate registrele de lucru. Pentru a face acest lucru, creați un nou registru de lucru și aveți codul funcției într-un modul din acest nou registru de lucru.

Acum urmați pașii de mai jos pentru a-l salva ca supliment și apoi instalați-l în Excel.

• Mergeți la fila Fișier și faceți clic pe Salvare ca.
• În caseta de dialog Salvare ca, schimbați tipul „Salvați ca” la .xlam. Numele pe care îl atribuiți fișierului ar fi numele programului dvs. de completare. În acest exemplu, fișierul este salvat cu numele GetNumeric.
  • Veți observa că calea fișierului în care este salvat se schimbă automat. Puteți să o utilizați pe cea implicită sau să o modificați dacă doriți.
• Deschideți un nou registru de lucru Excel și accesați fila Dezvoltator.
• Faceți clic pe opțiunea Excel Add-ins.
• În caseta de dialog Add-ins, răsfoiți și localizați fișierul salvat și faceți clic pe OK.

Acum programul de completare a fost activat.

Acum puteți utiliza funcția personalizată în toate registrele de lucru.

Salvarea funcției în registrul de lucru Macro personal

Un registru personal de lucru macro este un registru de lucru ascuns în sistemul dvs. care se deschide ori de câte ori deschideți aplicația Excel.

Este un loc unde puteți stoca coduri macro și apoi accesa aceste macrocomenzi din orice registru de lucru. Este un loc minunat pentru a stoca acele macrocomenzi pe care doriți să le utilizați des.

În mod implicit, nu există un registru de lucru macro personal în Excel. Trebuie să îl creați înregistrând o macro și salvând-o în registrul de lucru Personal macro.

Puteți găsi pașii detaliați despre cum să creați și să salvați macrocomenzi în registrul de lucru personal pentru macrocomenzi aici.

Referirea funcției dintr-un alt registru de lucru

În timp ce primele două metode (crearea unui program de completare și utilizarea registrului de lucru macro personal) ar funcționa în toate situațiile, dacă doriți să faceți referire la funcția dintr-un alt registru de lucru, acel registru de lucru trebuie să fie deschis.

Să presupunem că aveți un registru de lucru cu numele „Caiet de lucru cu Formula ”, și are funcția cu numele „GetNumeric '.

Pentru a utiliza această funcție într-un alt registru de lucru (în timp ce Caiet de lucru cu Formula este deschis), puteți utiliza formula de mai jos:

= „Caiet de lucru cu Formula”! GetNumeric (A1)

Formula de mai sus va utiliza funcția definită de utilizator în Caiet de lucru cu Formula înregistrați și vă oferă rezultatul.

Rețineți că, deoarece numele registrului de lucru are spații, trebuie să îl încadrați între ghilimele unice.

Utilizarea instrucțiunii funcției de ieșire VBA

Dacă doriți să ieșiți dintr-o funcție în timp ce codul rulează, puteți face acest lucru folosind instrucțiunea „Exit Function”.

Codul de mai jos ar extrage primele trei caractere numerice dintr-un șir de text alfanumeric. De îndată ce primește cele trei caractere, funcția se termină și returnează rezultatul.

Funcția GetNumericFirstThree (CellRef as Range) Long Dim StringLength as Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Rezultat = Rezultat & Mediu (CellRef, i, 1) GetNumericFirstThree = Rezultatul Sfârșit Dacă Următorul i Sfârșitul funcției

Funcția de mai sus verifică numărul de caractere numerice, iar când primește 3 caractere numerice, iese din funcția din bucla următoare.

Depanarea unei funcții definite de utilizator

Există câteva tehnici pe care le puteți utiliza în timp ce depanați o funcție definită de utilizator în VBA:

Depanarea unei funcții personalizate utilizând caseta de mesaje

Utilizați funcția MsgBox pentru a afișa o casetă de mesaj cu o anumită valoare.

Valoarea afișată poate fi bazată pe ceea ce doriți să testați. De exemplu, dacă doriți să verificați dacă codul se execută sau nu, orice mesaj ar funcționa și dacă doriți să verificați dacă buclele funcționează sau nu, puteți afișa o anumită valoare sau contorul de bucle.

Depanarea unei funcții personalizate prin setarea punctului de întrerupere

Setați un punct de întrerupere pentru a putea parcurge fiecare linie pe rând. Pentru a seta un punct de întrerupere, selectați linia unde doriți și apăsați F9 sau faceți clic pe zona verticală gri care este lăsată liniilor de cod. Oricare dintre aceste metode ar introduce un punct de întrerupere (veți vedea un punct roșu în zona gri).

Odată ce ați setat punctul de întrerupere și ați executat funcția, acesta merge până la linia punctului de întrerupere și apoi se oprește. Acum puteți parcurge codul folosind tasta F8. Apăsând o dată F8 se trece la următoarea linie din cod.

Depanarea unei funcții personalizate utilizând Debug.Print în cod

Puteți utiliza instrucțiunea Debug.Print în codul dvs. pentru a obține valorile variabilelor / argumentelor specificate în fereastra imediată.

De exemplu, în codul de mai jos, am folosit Debug.Print pentru a obține valoarea a două variabile - „j” și „Rezultat”

Funcția GetNumericFirstThree (CellRef as Range) Long Dim StringLength as Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Then J = J + 1 Rezultat = Rezultat & Mediu (CellRef, i, 1) Debug.Print J, Rezultat GetNumericFirstThree = Rezultatul Sfârșit Dacă Următorul i Sfârșitul funcției

Când acest cod este executat, acesta arată următoarele în fereastra imediată.

Funcții încorporate Excel vs. Funcție definită de utilizator VBA

Există puține avantaje puternice ale utilizării funcțiilor încorporate Excel față de funcțiile personalizate create în VBA.

• Funcțiile încorporate sunt mult mai rapide decât funcțiile VBA.
• Când creați un raport / tablou de bord folosind funcțiile VBA și îl trimiteți unui client / coleg, acesta nu ar trebui să se îngrijoreze dacă macrocomenzile sunt activate sau nu. În unele cazuri, clienții / clienții se sperie văzând un avertisment în bara galbenă (care le cere pur și simplu să activeze macrocomenzile).
• Cu funcțiile Excel încorporate, nu trebuie să vă faceți griji cu privire la extensiile de fișiere. Dacă aveți macrocomenzi sau funcții definite de utilizator în registrul de lucru, trebuie să le salvați în .xlsm.

Deși există multe motive puternice pentru a utiliza funcțiile încorporate Excel, în câteva cazuri, este mai bine să folosiți o funcție definită de utilizator.

• Este mai bine să utilizați o funcție definită de utilizator dacă formula dvs. integrată este imensă și complicată. Acest lucru devine și mai relevant atunci când aveți nevoie de altcineva pentru a actualiza formulele. De exemplu, dacă aveți o formulă uriașă formată din multe funcții diferite, chiar și schimbarea unei referințe la o celulă poate fi obositoare și predispusă la erori. În schimb, puteți crea o funcție personalizată care să ia doar unul sau două argumente și să facă tot greul backend-ului.
• Când trebuie să faceți ceva care nu poate fi realizat de funcțiile încorporate Excel. Un exemplu în acest sens poate fi atunci când doriți să extrageți toate caracterele numerice dintr-un șir. În astfel de cazuri, beneficiul utilizării unei funcții definite de utilizator depășește negativele sale.

Unde să puneți codul VBA pentru o funcție definită de utilizator

Când creați o funcție personalizată, trebuie să introduceți codul în fereastra de cod pentru registrul de lucru în care doriți funcția.

Mai jos sunt pașii pentru a introduce codul pentru funcția „GetNumeric” în registrul de lucru.

1. Accesați fila Dezvoltator.
2. Faceți clic pe opțiunea Visual Basic. Aceasta va deschide editorul VB în backend.
3. În panoul Explorator de proiecte din Editorul VB, faceți clic dreapta pe orice obiect pentru registrul de lucru în care doriți să inserați codul. Dacă nu vedeți Project Explorer, accesați fila View și faceți clic pe Project Explorer.
4. Accesați Insert și faceți clic pe Module. Aceasta va insera un obiect modul pentru registrul dvs. de lucru.
5. Copiați și lipiți codul în fereastra modulului.

Vă pot plăcea, de asemenea, următoarele tutoriale Excel VBA:

• Lucrul cu celule și intervale în Excel VBA.
• Lucrul cu foi de lucru în Excel VBA.
• Lucrul cu registrele de lucru folosind VBA.
• Cum se utilizează bucle în Excel VBA.
• Evenimente Excel VBA - Un ghid ușor (și complet)
• Utilizarea declarațiilor IF Then Else în VBA.
• Cum să înregistrați o macro în Excel.
• Cum se execută o macro în Excel.
• Cum să sortați datele în Excel folosind VBA (Un ghid pas cu pas).
• Funcția Excel VBA InStr - explicată cu exemple.