Dacă tocmai începeți cu VBA, atunci veți dori să începeți să studiați Ghid VBA pentru începători. Dar dacă sunteți un expert VBA experimentat și căutați lucruri mai avansate pe care le puteți face cu VBA în Excel, atunci continuați să citiți.
Capacitatea de a utiliza codarea VBA în Excel deschide o lume întreagă de automatizare. Puteți automatiza calculele în Excel, butoane și chiar trimiteți e-mailuri. Există mai multe posibilități de a vă automatiza munca zilnică cu VBA decât vă puteți da seama.
Ghid VBA avansat pentru Microsoft Excel
Principalul obiectiv al scrierii codului VBA în Excel este astfel încât să puteți extrage informații dintr-o foaie de calcul, efectuați o varietate de calcule pe ea, apoi scrieți rezultatele înapoi la foaia de calcul
Următoarele sunt cele mai frecvente utilizări ale VBA în Excel.
Cu aceste trei exemple, ar trebui să să poți scrie o varietate de coduri VBA Excel excelente.
Importarea datelor și efectuarea calculelor
Unul dintre cele mai obișnuite lucruri pe care oamenii le folosesc Excel pentru efectuează calcule pentru datele care există în afara Excelului. Dacă nu utilizați VBA, asta înseamnă că trebuie să importați manual datele, să executați calculele și să dați acele valori într-o altă foaie sau raport.
In_content_1 all: [300x250] / dfp : [640x360]->Cu VBA, puteți automatiza întregul proces. De exemplu, dacă aveți un nou fișier CSV descărcat într-un director de pe computer în fiecare zi de luni, puteți configura codul VBA pentru a fi rulat atunci când deschideți prima foaie de calcul marți dimineață.
Următorul cod de import va rulați și importați fișierul CSV în foaia de calcul Excel.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = “c:\temp\purchases.csv” With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Deschideți instrumentul de editare Excel VBA și selectați obiectul Sheet1. Din casetele derulante obiect și metodă, alegeți Foaie de lucruși Activare. Acest lucru va rula codul de fiecare dată când deschideți foaia de calcul.
Aceasta va crea o funcție Subfoaie de lucru_Activare (). Inserați codul de mai sus în acea funcție.
Aceasta setează fișa de lucru activă pe Foaie1, șterge foaia, se conectează la fișier folosind calea fișierului pe care l-ați definit cu variabila strFileși apoi Cubucla parcurge fiecare linie din fișier și plasează datele în foaie începând de la celula A1.
Dacă rulați acest cod, veți vedea că datele fișierului CSV este importat în foaia de calcul goală, în Foaie1.
Importarea este doar primul pas . În continuare, doriți să creați un antet nou pentru coloana care va conține rezultatele calculului. În acest exemplu, să spunem că doriți să calculați impozitele de 5% plătite la vânzarea fiecărui articol.
Ordinea de acțiuni pe care ar trebui să o întreprindă codul dvs. este:
Codul următor va îndeplini toate aceste etape.
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
Cells(1, 5) = "taxes"
For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell
Acest cod găsește ultimul rând în fișa dvs. de date, apoi setați gama de celule (coloana cu prețurile de vânzare) în funcție de primul și ultimul rând de date. Apoi, codul se bucle prin fiecare dintre aceste celule, efectuează calculul impozitelor și scrie rezultatele în noua dvs. coloană (coloana 5).
Lipiți codul VBA de mai sus sub codul anterior și rulați scriptul. Vei vedea rezultatele afișate în coloana E.
Acum, de fiecare dată când deschideți foaia de lucru Excel, acesta va ieși automat și va primi cea mai nouă copie de date din fișierul CSV. Apoi, va efectua calculele și va scrie rezultatele pe foaie. Nu mai trebuie să faceți nimic manual!
Calculați rezultatele de la apăsarea butonului
Dacă preferați să aveți un control mai direct asupra calculelor executate , în loc să rulați automat la deschiderea foii, puteți utiliza în schimb un buton de control.
Butoanele de control sunt utile dacă doriți să controlați care sunt calculele utilizate. De exemplu, în același caz ca mai sus, ce se întâmplă dacă doriți să utilizați o taxă de 5% pentru o regiune și o rată de impozit de 7% pentru o altă?
Ați putea permite același cod de import CSV să rulează automat, dar lăsați să funcționeze codul de calcul fiscal când apăsați butonul corespunzător.
Utilizând aceeași foaie de calcul ca mai sus, selectați fila Dezvoltatorși selectați Inserațidin grupul Controluridin panglică. Selectați butonul butonControl ActiveX din meniul derulant.
Trageți butonul pe orice parte a foii, departe de unde vor merge orice date.
Faceți clic dreapta pe butonul și selectați Proprietăți. În fereastra Proprietăți, schimbați Legenda la ceea ce doriți să afișați utilizatorului. În acest caz, ar putea fi Calculat impozitul de 5%.
Veți vedea acest text reflectat chiar pe butonul push. Închideți fereastra proprietățiși faceți dublu clic pe butonul în sine. Aceasta va deschide fereastra editorului de cod, iar cursorul dvs. va fi în interiorul funcției care va rula atunci când utilizatorul apasă butonul.
Lipiți codul de calcul fiscal din secțiunea de mai sus în această funcție, păstrând multiplicatorul cotei de impozit la 0,05. Nu uitați să includeți următoarele 2 linii pentru a defini foaia activă.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Acum, repetați procesul din nou, creând un al doilea buton. Faceți legendă Calculați 7% impozite.
Faceți dublu clic pe acel buton și lipiți același cod, dar faceți multiplicatorul fiscal 0.07.
Acum, în funcție de ce buton apăsați, coloana de taxe va fi calculat în consecință.
După ce ați terminat, veți avea ambele butoane apăsate pe foaia dvs. Fiecare dintre ei va iniția un calcul fiscal diferit și va scrie rezultate diferite în coloana de rezultate.
Pentru a scrie acest text, selectați meniul Dezvoltatorși selectați Mod de proiectareformând grupul Controluri din panglică pentru a dezactiva Modul de proiectarestrong>. Aceasta va activa butoanele apăsate.
Încercați să selectați fiecare buton apăsat pentru a vedea cum se schimbă coloana de rezultate „impozite”.
Rezultatele calculului prin e-mail la cineva
Ce dacă doriți să trimiteți rezultatele pe foaia de calcul către cineva prin e-mail?
Puteți crea un alt buton numit Fișă de e-mail către șeffolosind aceeași procedură de mai sus. Codul acestui buton va implica utilizarea obiectului Excel CDO pentru a configura setările de e-mail SMTP și a trimite prin e-mail rezultatele într-un format care poate fi citit de utilizator.
Pentru a activa această caracteristică, trebuie să selectați Instrumente și Referințe. Derulați în jos până la CDO Microsoft pentru Windows 2000 Library, activați-l și selectați OK.
Există trei secțiuni principale ale codului pe care trebuie să-l creezi pentru a trimite un e-mail și a încorpora rezultatele foii de calcul.
Prima este setarea variabilelor de păstrat subiectul, adresele către și de la și corpul de e-mail.
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Desigur, corpul trebuie să fie dinamic în funcție de rezultatele obținute. în foaie, deci aici va trebui să adăugați o buclă care parcurge intervalul, să extragă datele și să scrie o linie la un moment dat în corp.
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))
rowCounter = 2
strBody = strBody & vbCrLf
For Each cell In rng
strBody = strBody & vbCrLf
strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
& " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
rowCounter = rowCounter + 1
Next cell
Următoarea secțiune implică configurarea setărilor SMTP, astfel încât să puteți trimite e-mailuri prin intermediul serverului dvs. SMTP. Dacă utilizați Gmail, aceasta este de obicei adresa dvs. de e-mail Gmail, parola dvs. Gmail și serverul SMTP Gmail (smtp.gmail.com).
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Înlocuiți [email protected] și parolă cu propriile detalii ale contului.
În sfârșit, pentru a iniția trimiterea prin e-mail, introduceți următorul cod.
CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description
Notă: dacă vedeți o eroare de transport atunci când încercați să rulați acest cod, este posibil, deoarece contul dvs. Google blochează rularea „aplicațiilor mai puțin sigure”. Va trebui să accesați pagina cu setări de aplicații mai puțin sigure și să activați această caracteristică.
După ce este activat, e-mailul dvs. va fi trimis. Așa seamănă cu persoana care primește e-mailul rezultatelor generate automat.
După cum puteți vedea, există o mulțime de automatizări cu Excel VBA. Încercați să vă jucați cu fragmentele de cod despre care ați aflat în acest articol și să creați propriile dvs. automatizări VBA unice.