Înregistratorul de macrocomenzi Excel are multă putere, dar are limitele sale. Așa cum se prevede într-un alt articol, macro recorderul înregistrează adesea coduri inutile și nu poate înregistra lucruri precum logica sau interacțiunile cu alte programe. Poate fi, de asemenea, greu de utilizat pentru macro-uri mai lungi - s-ar putea să vă regăsiți în scenariu în prealabil acțiunile dvs. doar pentru a evita să faceți greșeli costisitoare.
Acest articol își propune să vă ajute să începeți codarea macro-urilor de la zero în VBA. Veți afla unde sunt stocate macro-urile, veți scrie o macro de bază și veți învăța elementele de bază ale programării în VBA folosind variabile, logică și bucle.
Noțiuni de bază
VBA și Editorul Visual Basic
VBA, sau Visual Basic pentru aplicații, este limbajul în care sunt scrise macrocomenzile. Toate macrocomenzile sunt stocate ca cod VBA, indiferent dacă sunt codificate manual sau create cu înregistratorul de macrocomenzi.
Puteți accesa tot codul VBA dintr-un registru de lucru folosind Editorul Visual Basic. Acesta este un editor de text și depanator special care este încorporat în toate aplicațiile Office, inclusiv Excel. De obicei, veți deschide acest editor cu ALT + F11 comandă rapidă de la tastatură în Excel, dar o puteți accesa și din Excel Dezvoltator filă dacă o aveți activată.
Exploratorul de proiecte
The Project Explorer este o fereastră din Editorul VB care vă arată toate elementele care pot avea cod VBA în ele. Dacă nu vedeți această fereastră, apăsați F5 pentru a face să apară sau să selecteze Project Explorer de la Vedere meniul.
Dacă faceți dublu clic pe un element din Project Explorer, se va afișa codul pentru acel element. Există mai multe tipuri de elemente care pot apărea în Project Explorer:
- Cărți de lucru
- Fișe de lucru
- UserForms
- Module de clasă
- Module (macrocomenzile sunt stocate în aceste elemente)
Deși toate aceste tipuri de articole pot include cod VBA, cea mai bună practică este codarea macro-urilor în module.
Realizarea primei dvs. macro
Utilizarea listei de macrocomenzi
Lista Macros vă arată toate macrocomenzile din registrul dvs. de lucru. Din această listă puteți edita o macrocomandă existentă sau puteți crea una nouă.
Pentru a crea o nouă macro utilizând lista Macros:
- Selectați fila Dezvoltator și faceți clic pe Macrocomenzi (sau apăsați ALT + F8)
- Tastați un nume nou pentru macro, apoi faceți clic pe „Creați”
După ce faceți clic pe „Creați”, editorul VB va apărea, afișând macrocomenzile nou create. Excel va crea un nou modul pentru macro dacă este necesar.
Manual în Editorul VB
Puteți adăuga manual o nouă macrocomandă fără lista de macrocomenzi. Aceasta este opțiunea mai bună dacă doriți să specificați modulul în care este salvată macrocomanda.
Pentru a adăuga manual o macro:
- Deschideți editorul VB (ALT + F11)
- Fie:
- Adăugați un nou modul făcând clic pe Insert> Module din meniu (modulul se va deschide automat)
-
- SAU, faceți dublu clic pe un modul existent în Project Explorer pentru a-l deschide
- În modul, tastați codul pentru noua dvs. macro
Sub MyMacro () End Sub
Aceste două rânduri indică începutul și sfârșitul unei macrocomenzi numite „MyMacro” (rețineți parantezele care sunt necesare). Aceasta va apărea în dialogul „Vizualizați macrocomenzile” din Excel și poate fi atribuit unui buton (chiar dacă nu face încă nimic).
Adăugați un cod la macro
Acum, să adăugăm un cod între rândurile „Sub” și „End Sub” pentru a face ca această macro să facă ceva:
Sub MyMacro () Range („A1”). Valoare = „Hello World!” Sfârșitul Sub
Structuri de bază de cod
Obiectul Range
Excel VBA folosește Range Object pentru a reprezenta celule pe o foaie de lucru. În exemplul de mai sus, se creează un obiect Range cu codul Gama („A1”) pentru a accesa valoarea celulei A1.
Obiectele Range sunt utilizate în principal pentru a seta valorile celulei:
Interval („A1”). Valoare = 1
Interval („A1”). Valoare = „Prima celulă”
Observați că atunci când definiți valorile celulei ca numere, trebuie doar să introduceți numărul, dar când introduceți text, trebuie să înconjurați textul cu ghilimele.
Gamele pot fi, de asemenea, utilizate pentru a accesa multe proprietăți ale celulelor, cum ar fi fontul, chenarele, formulele și multe altele.
De exemplu, puteți seta fontul unei celule în Bold astfel:
Range („A1”). Font.Bold = True
De asemenea, puteți seta formula unei celule:
Interval („A1”). Formula = „= Sum (A2: A10)”
În Excel, puteți selecta un bloc de celule cu cursorul (să zicem, de la A1 la D10) și să le setați pe toate în bold. Obiectele Range pot accesa blocuri de celule astfel:
Gama („A1: D10”). Font.Bold = True
De asemenea, puteți face referire la mai multe celule / blocuri simultan:
Gama („A1: D10, A12: D12, G1”). Font.Bold = True
Formatul pentru acesta este același cu formatul pe care l-ați utiliza atunci când selectați celule pentru formula SUM () în Excel. Fiecare bloc este separat printr-o virgulă, iar blocurile sunt notate prin celulele din stânga sus și din dreapta jos, separate prin două puncte.
În cele din urmă, obiectele Range au metode încorporate pentru a efectua operații comune pe o foaie de lucru. De exemplu, vă recomandăm să copiați unele date dintr-un loc în altul. Iată un exemplu:
Range („A1: D10”). Range Copy („F1”). PasteSpecial xlPasteValues Range („F1”). PasteSpecial xlPasteFormats
Aceasta copiază celulele A1: D10 în clipboard, apoi face un PasteSpecial () începând din celula C1 - la fel cum ați face manual în Excel. Rețineți că acest exemplu arată cum să utilizați PasteSpecial () pentru a lipi numai valori și formate - există parametri pentru toate opțiunile pe care le-ați vedea în dialogul Lipire specială.
Iată un exemplu de lipire „Toate” într-o altă foaie de lucru:
Range („A1: D10”). Copiere foi („Sheet2”). Range („A1”). PasteSpecial xlPasteAll
Dacă Declarații
Cu un Dacă afirmație, puteți face o secțiune de cod să ruleze numai „dacă” o anumită afirmație este adevărată.
De exemplu, poate doriți să faceți o celulă îndrăzneață și să o colorați în roșu, dar numai „dacă” valoarea din celulă este mai mică de 100.
Dacă Range („A4”). Valoare <100 Apoi Range („A4”). Font.Bold = True Range („A4”). Interior.Color = vbRed End Dacă
Structura corectă a unei instrucțiuni If este următoarea (parantezele pătrate indică componentele opționale):
Daca atunci
[Altfel Dacă atunci]
[Altfel]
End If
Puteți include cât mai multe AltfelDacă blochează după cum doriți să testați mai multe condiții. De asemenea, puteți adăuga un Altfel bloc care rulează numai dacă nu sunt îndeplinite niciuna dintre celelalte condiții din instrucțiunea If.
Iată un alt exemplu bazat pe cel precedent, în care celula este formatată în mai multe moduri diferite, în funcție de valoare:
Dacă Range ("A4"). Valoare <100 Then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Valoare <200 Then Range ( „A4”). Font.Bold = False Range („A4”). Interior.Color = vbGalben Else Range („A4”). Font.Bold = False Range („A4”). Interior.Color = vbGreen End If
În exemplul de mai sus, celula nu are caractere aldine în blocurile ElseIf, unde valoarea nu este sub 100. Puteți cuib Dacă instrucțiunile pentru a evita duplicarea codului, astfel:
Dacă Range ("A4"). Valoare <100 Apoi Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' descifrarea fontului o singură dată If Range ("A4"). Valoare <200 Apoi Range ("A4"). Interior.Color = vbGalben Else Range ("A4"). Interior.Color = vbVerde End Dacă End If
Variabile
A Variabil este o bucată de memorie utilizată pentru a stoca informații temporare în timp ce rulează o macro. Acestea sunt adesea folosite în bucle ca iteratoare sau pentru a reține rezultatul unei operații pe care doriți să o utilizați de mai multe ori pe parcursul unei macro.
Iată un exemplu de variabilă și cum o puteți utiliza:
Sub ExtractSerialNumber () Dim strSerial As String 'aceasta este declarația variabilei' 'As String' înseamnă că această variabilă este menită să conțină textul 'configurând un număr de serie pretins: Gama ("A4"). Valoare = „serial # 804567-88 "'Analizați numărul de serie din celula A4 și atribuiți-l variabilei strSerial = Mid (Range (" A4 "). Valoare, 9)' utilizați acum variabila de două ori, în loc să trebuiască să analizați numărul de serie de două ori Range (" B4 ”). Valoare = strSerial MsgBox strSerial End Sub
În acest exemplu de bază, variabila „strSerial” este utilizată pentru a extrage numărul de serie din celula A4 utilizând funcția Mid () și apoi este folosit în alte două locuri.
Modul standard de a declara o variabilă este după cum urmează:
Dim orice nume [La fel de tip]
- orice nume este numele pe care decideți să îl dați variabilei
- tip este tipul de date al variabilei
„[As tip] ”Parte poate fi omisă - dacă da, variabila este declarată ca tip Variant, care poate conține orice fel de date. Deși sunt perfect valabile, tipurile de variante ar trebui evitate, deoarece pot duce la rezultate neașteptate dacă nu sunteți atent.
Sunt reguli pentru nume variabile. Trebuie să înceapă fie cu o literă, fie cu un caracter de subliniere, nu pot avea spații, puncte, virgule, ghilimele sau caracterele „! @ & $ # ”.
Iată câteva exemple de declarații variabile:
Dim strFilename As String "stil de nume bun - descriptiv și folosește prefixul Dim i As Long" stil de nume rău - acceptabil numai pentru unii iteratori Dim SalePrice As Double - nu prea descriptiv, folosește prefix, fără tip de date
Toate aceste exemple utilizează scheme de denumire ușor diferite, dar toate sunt valabile. Nu este o idee rea să prefixați un nume de variabilă cu o formă scurtă a tipului său de date (conform unora dintre aceste exemple), deoarece vă face codul mai ușor de citit dintr-o privire.
VBA include o mulțime de elemente de bază tipuri de date. Cele mai populare includ:
- Şir (folosit pentru a păstra date text)
- Lung (folosit pentru a menține numere întregi, adică fără zecimale)
- Dubla (folosit pentru a menține numere cu virgulă mobilă, adică zecimale)
O listă completă a tipurilor de date intrinseci VBA poate fi găsită aici: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Variabile de obiect de gamă
Este posibil să creați și variabile care să facă referire la obiectele de gamă. Acest lucru este util dacă doriți să faceți referire la un anumit interval din codul dvs. în mai multe locuri - în acest fel, dacă trebuie să modificați intervalul, trebuie să îl modificați doar într-un singur loc.
Când creați o variabilă obiect Range, trebuie să o „setați” la o instanță dintr-un Range. De exemplu:
Dim rMyRange As Range Set rMyRange = Range („A1: A10; D1: J10”)
Dacă lăsați afirmația „Set” afară atunci când atribuiți o variabilă Range, va rezulta o eroare.
Bucle
Buclele sunt blocuri care repetă codul din interiorul lor de un anumit număr de ori. Acestea sunt utile pentru a reduce cantitatea de cod pe care trebuie să o scrieți și vă permit să scrieți o singură bucată de cod care efectuează aceleași acțiuni pe multe elemente conexe diferite.
Pentru următorul
A Pentru următorul bloc este o buclă care se repetă de un anumit număr de ori. Folosește o variabilă ca iterator pentru a număra de câte ori a rulat, iar această variabilă iterator poate fi utilizată în interiorul buclei. Acest lucru face buclele For-Next foarte utile pentru iterarea prin celule sau tablouri.
Iată un exemplu care parcurge celulele din rândurile 1 la 100, coloana 1 și le setează valorile la valoarea variabilei iterator:
Dim i Long For i = 1 To 100 Cells (i, 1) .Value = i Next i
Linia „Pentru i = 1 până la 100” înseamnă că bucla începe de la 1 și se termină după 100. Puteți seta orice număr de început și de sfârșit doriți; puteți utiliza și variabile pentru aceste numere.
În mod implicit, buclele pentru următorul număr de 1. Dacă doriți să numărați cu un număr diferit, puteți scrie bucla cu un explicit Etapa clauză:
Pentru i = 5 până la 100 Pasul 5
Această buclă va începe la 5, apoi se adaugă 5 la „i” de fiecare dată când bucla se repetă (deci „i” va fi 10 la a doua repetare, 15 la a treia și așa mai departe).
Folosind Etapa, puteți face și o buclă să conteze înapoi:
Pentru i = 100 la 1 Pasul -1
Poti de asemenea cuib Bucle For-Next. Fiecare bloc necesită propria variabilă cu care să se numere, dar puteți utiliza aceste variabile oriunde doriți. Iată un exemplu despre modul în care este util în Excel VBA:
Dim i cât de lung, j cât de lung pentru i = 1 până la 100 pentru j = 1 până la 100 de celule (i, j). Valoare = i * j Următorul j Următorul i
Acest lucru vă permite să parcurgeți ambele rânduri și coloane.
AVERTIZARE: deși este permis, nu ar trebui să MODIFICAȚI NICIODATĂ variabila iteratorului dintr-un bloc For-Next, deoarece folosește acel iterator pentru a urmări bucla. Modificarea iteratorului poate provoca o buclă infinită și poate bloca macro-ul. De exemplu:
Pentru i = 1 Până la 100 i = 1 Următorul i
În această buclă, „I” nu va trece niciodată peste 2 înainte de a fi resetat la 1, iar bucla se va repeta pentru totdeauna.
Pentru fiecare
Pentru fiecare blocurile sunt foarte asemănătoare cu blocurile For-Next, cu excepția faptului că nu folosesc un contor pentru a specifica de câte ori se bucură. În schimb, un bloc For-Each ia o „colecție” de obiecte (cum ar fi o gamă de celule) și rulează de câte ori există obiecte în acea colecție.
Iată un exemplu:
Dim r ca interval pentru fiecare r din interval ("A15: J54") Dacă r.Value> 0 Apoi r.Font.Bold = True End Dacă următorul r
Observați utilizarea variabilei obiectului Range „r”. Aceasta este variabila iterator utilizată în bucla For-Each - de fiecare dată prin buclă, „r” primește o referință la următoarea celulă din Range.
Un avantaj al utilizării buclelor For-Each în Excel VBA este că puteți parcurge toate celulele dintr-un interval fără bucle de cuibărire. Acest lucru poate fi la îndemână dacă trebuie să parcurgeți toate celulele dintr-un interval complex, cum ar fi Gama („A1: D12, J13, M1: Y12”).
Un dezavantaj al buclelor For-Each este că nu aveți control asupra ordinii în care sunt procesate celulele. Cu toate că in practica Excel va face o buclă prin celule în ordine, teoretic ar putea procesa celulele într-o ordine complet aleatorie. Dacă trebuie să procesați celulele într-o anumită ordine, ar trebui să utilizați în schimb bucle For-Next.
Do-Loop
În timp ce blocurile For-Next folosesc contoare pentru a ști când să se oprească, Do-Loop blocurile rulează până când se îndeplinește o condiție. Pentru a face acest lucru, utilizați un Pana cand clauză fie la începutul, fie la sfârșitul blocului, care testează starea și determină oprirea buclei atunci când respectiva condiție este îndeplinită.
Exemplu:
Dim str As String str = "Buffalo" Do Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Loop Range ("A1"). Value = str
În această buclă, „Buffalo” este concatenat la „str” de fiecare dată prin buclă Până când se potrivește cu propoziția așteptată. În acest caz, testul se efectuează la începutul buclei - dacă „str” a fost deja propoziția așteptată (ceea ce nu este pentru că nu am început-o așa, dar dacă) bucla nu ar rula nici măcar .
Puteți face bucla să ruleze cel puțin o dată mutând clauza Until până la capăt, astfel:
Do str = str & "" & "Buffalo" Loop Until str = “Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo”
Puteți utiliza oricare versiune are sens în macro.
AVERTIZARE: puteți provoca o buclă infinită cu un bloc Do-Loop dacă condiția Until nu este îndeplinită niciodată. Scrieți întotdeauna codul dvs., astfel încât condiția Până să fie îndeplinită cu siguranță atunci când utilizați acest tip de buclă.
Ce urmeaza?
După ce ați înțeles elementele de bază, de ce nu încercați să învățați câteva tehnici mai avansate? Tutorialul nostru de la https://easyexcel.net/excel/learn-vba-tutorial/ se va baza pe tot ceea ce ați învățat aici și vă va extinde abilitățile cu Evenimente, UserForms, optimizarea codului și multe altele!
