Napredni priročnik VBA za MS Excel

Če šele začenjate z VBA, boste želeli začeti študirati našo VBA vodič za začetnike. Če pa ste izkušen strokovnjak za VBA in iščete naprednejše stvari, ki jih lahko počnete z VBA v Excelu, nadaljujte z branjem.

Sposobnost uporabe kodiranja VBA v Excelu odpira cel svet avtomatizacije. Izračune lahko avtomatizirate v Excelu, pritisnete gumbe in celo pošljete e -pošto. Obstaja več možnosti za avtomatizacijo vašega vsakodnevnega dela z VBA, kot se morda zavedate.

Napredni priročnik VBA za Microsoft Excel

Glavni cilj pisanja kode VBA v Excelu je, da lahko izvlečete podatke iz preglednice, izvedete različne izračune in nato rezultate zapišete nazaj v preglednico

V nadaljevanju so najpogostejše uporabe VBA v Excelu.

• Uvozite podatke in izvedite izračune
• Izračunajte rezultate, ko uporabnik pritisne gumb
• Rezultate izračuna pošljite nekomu po e -pošti

S temi tremi primeri bi morali napisati različne lastne napredne kode Excel VBA.

Uvoz podatkov in izvedba izračunov

Ena najpogostejših stvari, za katere ljudje uporabljajo Excel, je izračun podatkov, ki obstajajo zunaj Excela. Če ne uporabljate VBA, to pomeni, da morate ročno uvoziti podatke, zagnati izračune in te vrednosti prenesti na drug list ali poročilo.

Z VBA lahko avtomatizirate celoten proces. Na primer, če imate vsak ponedeljek v imeniku v računalniku naloženo novo datoteko CSV, lahko svojo kodo VBA konfigurirate tako, da se zažene, ko v torek zjutraj prvič odprete preglednico.

Naslednja uvozna koda se bo zagnala in uvozila datoteko CSV v Excelovo preglednico.

Zatemni ws kot delovni list, strFile As String Set ws = ActiveWorkbook. Listi ("List1") Celice. ClearContents strFile = “c: \ temp \ purchases.csv” Z ws. Poizvedbene tabele. Dodaj (Povezava: = "TEXT;" & strFile, Cilj: = ws. Obseg ("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Posveži. Končaj s

Odprite orodje za urejanje programa Excel VBA in izberite predmet Sheet1. V spustnih poljih predmeta in metode izberite Delovni list in Aktiviraj. To bo zagnalo kodo vsakič, ko odprete preglednico.

To bo ustvarilo a Sub Worksheet_Activate () funkcijo. Prilepite zgornjo kodo v to funkcijo.

S tem nastavite aktivni delovni list na List 1, počisti list, se poveže z datoteko po poti datoteke, ki ste jo določili z datoteko strFile spremenljivko, nato pa Z zanka kroži po vsaki vrstici v datoteki in podatke postavi na list, začenši s celico A1.

Če zaženete to kodo, boste videli, da so podatki datoteke CSV uvoženi v prazno preglednico v List 1.

Uvoz je le prvi korak. Nato želite ustvariti novo glavo stolpca, ki bo vseboval vaše rezultate izračuna. V tem primeru recimo, da želite izračunati 5% davek, plačan pri prodaji vsakega artikla.

Vrstni red dejanj, ki jih mora izvesti vaša koda, je:

1. Ustvari nov stolpec z rezultati davkov.
2. Krog skozi prodanih enot stolpec in izračunajte prometni davek.
3. Rezultate izračuna zapišite v ustrezno vrstico na listu.

Naslednja koda bo izvedla vse te korake.

Dim LastRow As Long
Zatemni StartCell As Range
Zatemni vrstico števca kot celo število
Dim rng As Range, celica As Range
Zatemni fltDavek kot dvojni
Nastavi StartCell = Obseg ("A1")
'Poišči zadnjo vrstico in stolpec
LastRow = ws. Celice (ws. Vrstice. Štej, StartCell. Stolpec). Konec (xlUp). Vrstica
Nastavite rng = ws. Domet (ws. Celice (2, 4), ws. Celice (LastRow, 4))
rowCounter = 2
Celice (1, 5) = "davki"
Za vsako celico V rng
fltTax = celica. Vrednost * 0,05
Celice (rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Naslednja celica

Ta koda najde zadnjo vrstico na vašem listu podatkov in nato nastavi obseg celic (stolpec s prodajnimi cenami) glede na prvo in zadnjo vrstico podatkov. Nato se koda vrti skozi vsako od teh celic, izvede izračun davka in zapiše rezultate v vaš novi stolpec (stolpec 5).

Zgornjo kodo VBA prilepite pod prejšnjo kodo in zaženite skript. Rezultate boste videli v stolpcu E.

Zdaj, vsakič, ko odprete Excelov delovni list, bo samodejno ugasnil in prejel najnovejšo kopijo podatkov iz datoteke CSV. Nato bo izvedel izračune in rezultate zapisal na list. Ni vam treba več ničesar narediti ročno!

Izračunajte rezultate s pritiskom na gumb

Če želite imeti bolj neposreden nadzor nad izvajanjem izračunov in ne samodejno, ko se odpre list, lahko namesto tega uporabite nadzorni gumb.

Gumbi za upravljanje so uporabni, če želite nadzorovati, kateri izračuni se uporabljajo. Na primer, v tem primeru, kot je opisano zgoraj, kaj, če želite uporabiti 5% davčno stopnjo za eno regijo in 7% davčno stopnjo za drugo regijo?

Lahko dovolite, da se ista uvozna koda CSV samodejno zažene, kodo za izračun davka pa pustite teči, ko pritisnete ustrezen gumb.

Z isto preglednico kot zgoraj izberite datoteko Razvijalec zavihek in izberite Vstavi Iz Nadzor skupine na traku. Izberite pritisni gumb ActiveX Control iz spustnega menija.

Potisnite gumb na kateri koli del lista stran od mesta, kamor bodo šli vsi podatki.

Z desno tipko miške kliknite gumb in izberite Lastnosti. V oknu Lastnosti spremenite napis v tisto, kar želite prikazati uporabniku. V tem primeru je lahko Izračunajte 5% davek.

To besedilo se bo odražalo na samem gumbu. Zaprite lastnosti okno in dvokliknite sam gumb. S tem se odpre okno urejevalnika kod in vaš kazalec bo znotraj funkcije, ki se bo zagnala, ko bo uporabnik pritisnil na gumb.

Kodo za izračun davka iz zgornjega razdelka prilepite v to funkcijo, pri čemer mora biti multiplikator davčne stopnje 0,05. Ne pozabite vključiti naslednjih dveh vrstic, da določite aktivni list.

Zatemni kot delovni list, strFile kot niz
Nastavite ws = ActiveWorkbook. Listi ("List1")

Zdaj ponovite postopek in ustvarite drugi gumb. Naredite napis Izračunajte 7% davek.

Dvokliknite ta gumb in prilepite isto kodo, vendar naj bo davčni multiplikator 0,07.

Odvisno od tega, kateri gumb pritisnete, bo stolpec davkov ustrezno izračunan.

Ko končate, boste na listu imeli oba gumba. Vsak od njih bo sprožil drugačen izračun davka in v stolpec z rezultati zapisal različne rezultate.

Če želite to poslati, izberite datoteko Razvijalec meni in izberite Način oblikovanja za onemogočanje oblikujte skupino Controls na traku Način oblikovanja. S tem boste aktivirali potisne gumbe.

Poskusite izbrati vsak gumb, da vidite, kako se spremeni stolpec z rezultati »davki«.

Pošljite nekomu rezultate izračunavanja po e -pošti

Kaj pa, če želite nekomu poslati rezultate v preglednici po e -pošti?

Ustvarite lahko drug gumb, imenovan E -poštni list pošljite šefu po enakem postopku zgoraj. Koda za ta gumb bo vključevala uporabo predmeta Excel CDO za konfiguriranje nastavitev e-pošte SMTP in pošiljanje rezultatov po e-pošti v uporabniško berljivi obliki.

Če želite omogočiti to funkcijo, morate izbrati Orodja in reference. Pomaknite se navzdol do Knjižnica Microsoft CDO za knjižnico Windows 2000, omogočite in izberite v redu.

Kodo, ki jo morate ustvariti za pošiljanje e -poštnega sporočila in vdelavo rezultatov preglednice, so trije glavni odseki.

Prva je nastavitev spremenljivk, ki bodo vsebovale zadevo, naslove Do in Od, ter telo e -pošte.

Zatemni CDO_Mail kot objekt
Zatemni CDO_Config kot objekt
Zatemni SMTP_Config kot varianto
Zatemni strSubject As String
Zatemni strIz As String
Dim strTo As String
Zatemni strCc kot niz
Zatemni strBcc kot niz
Dim strBody As String
Dim LastRow As Long
Zatemni StartCell As Range
Zatemni vrstico števca kot celo število
Dim rng As Range, celica As Range
Zatemni fltDavek kot dvojni

Nastavite ws = ActiveWorkbook. Listi ("List1")
strSubject = "Davki, plačani v tem četrtletju"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "Sledi razčlenitev davkov, plačanih pri prodaji v tem četrtletju."

Seveda mora biti telo dinamično, odvisno od rezultatov na listu, zato boste tukaj dodati zanko, ki gre skozi obseg, izvleče podatke in naenkrat zapiše vrstico v telo.

Nastavite StartCell = Razpon ("A1") 'Poišči zadnjo vrstico in stolpec. LastRow = ws. Celice (ws. Vrstice. Štej, StartCell. Stolpec). Konec (xlUp). Vrstica. Nastavite rng = ws. Domet (ws. Celice (2, 4), ws. Celice (LastRow, 4)) rowCounter = 2. strBody = strBody & vbCrLf Za vsako celico V rng strBody = strBody & vbCrLf strBody = strBody & "Prodali smo" & celice (rowCounter, 3). Vrednost & "od" & Celice (rowCounter, 1) .Vrednost _ & "za" & Celice (rowCounter, 4) .Vrednost & "in plačani davki" & Cells (rowCounter, 5) .Vrednost & "." rowCounter = rowCounter + 1. Naslednja celica

Naslednji razdelek vključuje nastavitev nastavitev SMTP, tako da lahko pošiljate e -pošto prek strežnika SMTP. Če uporabljate Gmail, je to običajno vaš e -poštni naslov v Gmailu, geslo za Gmail in strežnik Gmail SMTP (smtp.gmail.com).

CDO_Mail = CreateObject ("CDO.Message") On Error GoTo Error_Handling. Nastavi CDO_Config = CreateObject ("CDO.Configuration") CDO_Config. Obremenitev -1. Nastavite SMTP_Config = CDO_Config. Polja s 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") = "geslo" .Item (" http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465. .Item (" http://schemas.microsoft.com/cdo/configuration/smtpusessl") = Res. .Nadgradnja. Končaj z nastavitvijo CDO_Mail .Configuration = CDO_Config. Končaj s

Zamenjati [email protected] in geslo s podatki o svojem računu.

Za začetek pošiljanja e -pošte vnesite naslednjo kodo.

CDO_Mail. Zadeva = strSubject
CDO_Mail. From = strFrom
CDO_Mail. To = strTo
CDO_Mail. TextBody = strBody
CDO_Mail. CC = strCc
CDO_Mail. BCC = strBcc
CDO_Mail. Pošlji
Error_Handling:
Če je napaka. Opis <> "" Potem MsgBox Err. Opis

Opomba: Če pri poskusu zagona te kode vidite transportno napako, je to verjetno zato, ker vaš Google Račun preprečuje izvajanje »manj varnih aplikacij«. Boste morali obiskati stran z nastavitvami manj varnih aplikacij in vklopite to funkcijo.

Ko je to omogočeno, bo poslano vaše e -poštno sporočilo. Tako izgleda osebi, ki prejme vaše samodejno generirano e -poštno sporočilo o rezultatih.

Kot vidite, je veliko stvari, ki jih lahko dejansko avtomatizirate z Excelom VBA. Poskusite se z delčki kode, o katerih ste izvedeli v tem članku, in ustvarite svojo edinstveno avtomatizacijo VBA.