Ako tek počinjete s VBA -om , onda ćete htjeti početi proučavati naš VBA vodič za početnike^{(VBA guide for beginners)} . Ali ako ste iskusni VBA stručnjak i tražite naprednije stvari koje možete učiniti s VBA -om u Excelu^(Excel) , nastavite čitati.

Mogućnost korištenja VBA kodiranja u Excelu^(Excel) otvara cijeli svijet automatizacije. Možete automatizirati izračune u Excelu^(Excel) , tipke, pa čak i slati e-poštu. Postoji više mogućnosti za automatizaciju vašeg svakodnevnog rada s VBA nego što mislite.

Napredni VBA vodič za Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

Glavni cilj pisanja VBA koda u Excelu^(Excel) je tako da možete izdvojiti informacije iz proračunske tablice, izvršiti razne izračune na njoj, a zatim rezultate zapisati natrag u proračunsku tablicu

Sljedeće su najčešće upotrebe VBA u Excelu^(Excel) .

• Uvezite^(Import) podatke i izvršite izračune
• Izračunajte^(Calculate) rezultate od korisnika koji pritisne gumb
• Pošaljite^(Email) nekome rezultate izračuna

Uz ova tri primjera trebali biste biti u mogućnosti napisati razne vlastite napredne Excel VBA koda.

Uvoz podataka i izvođenje izračuna^{(Importing Data and Performing Calculations)}

Jedna od najčešćih stvari za koje ljudi koriste Excel je izvođenje izračuna na podacima koji postoje izvan Excela^(Excel) . Ako ne koristite VBA , to znači da morate ručno uvesti podatke, pokrenuti izračune i ispisati te vrijednosti na drugi list ili izvješće.

S VBA možete automatizirati cijeli proces. Na primjer, ako imate novu CSV datoteku koja se preuzima u direktorij na vašem računalu svakog ponedjeljka^(Monday) , možete konfigurirati svoj VBA kôd da se pokrene kada prvi put otvorite proračunsku tablicu u utorak^(Tuesday) ujutro.

Sljedeći kod za uvoz pokrenut će se i uvesti CSV datoteku u vašu Excel proračunsku tablicu.

Dim ws As Worksheet, strFile As String

Set 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

Otvorite Excel VBA alat za uređivanje i odaberite objekt Sheet1 . Iz padajućih okvira objekata i metoda odaberite Radni list^(Worksheet) i Aktiviraj^(Activate) . To će pokrenuti kod svaki put kada otvorite proračunsku tablicu.

Ovo će stvoriti funkciju Sub Worksheet_Activate() . Zalijepite gornji kod u tu funkciju.

Ovo postavlja aktivni radni list na Sheet1 , briše list, povezuje se s datotekom pomoću putanje datoteke koju ste definirali s varijablom strFile , a zatim petlja With prolazi kroz svaki redak u datoteci i stavlja podatke u list počevši od ćelije A1 .

Ako pokrenete ovaj kod, vidjet ćete da su podaci iz CSV datoteke uvezeni u vašu praznu proračunsku tablicu, u Sheet1 .

Uvoz je samo prvi korak. Zatim želite stvoriti novo zaglavlje za stupac koji će sadržavati vaše rezultate izračuna. U ovom primjeru, recimo da želite izračunati poreze od 5% plaćenih na prodaju svake stavke.

Redoslijed radnji koje vaš kod treba poduzeti je:

1. Napravite novi stupac rezultata koji se zove porezi^(taxes) .
2. Prođite kroz stupac prodanih jedinica^{(units sold)} i izračunajte porez na promet.
3. Upišite rezultate izračuna u odgovarajući redak na listu.

Sljedeći kod će izvršiti sve ove korake.

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

Ovaj kod pronalazi zadnji redak u vašem listu podataka, a zatim postavlja raspon ćelija (stupac s prodajnim cijenama) prema prvom i posljednjem retku podataka. Zatim kod petlja kroz svaku od tih ćelija, izvodi izračun poreza i upisuje rezultate u vaš novi stupac (stupac 5).

Zalijepite gornji VBA kod ispod prethodnog koda i pokrenite skriptu. Vidjet ćete rezultate prikazane u stupcu E.

Sada, svaki put kada otvorite svoj radni list programa Excel^(Excel) , on će se automatski ugasiti i dobiti najsvježiju kopiju podataka iz CSV datoteke. Zatim će izvršiti izračune i rezultate zapisati na list. Više ne morate ništa raditi ručno!

Izračunajte rezultate pritiskom na gumb^{(Calculate Results From Button Press)}

Ako biste radije imali izravniju kontrolu nad izvođenjem izračuna, umjesto da se izvode automatski kada se list otvori, umjesto toga možete koristiti kontrolni gumb.

Kontrolni^(Control) gumbi korisni su ako želite kontrolirati koji se izračuni koriste. Na primjer, u ovom istom slučaju kao gore, što ako želite koristiti poreznu stopu od 5% za jednu regiju i stopu od 7% za drugu?

Možete dopustiti da se isti uvozni kôd CSV -a automatski pokrene, ali ostavite kôd za obračun poreza da se pokrene kada pritisnete odgovarajući gumb.

Koristeći istu proračunsku tablicu kao gore, odaberite karticu Razvojni programer^(Developer) i odaberite Umetni^(Insert) iz grupe Kontrole^(Controls) na vrpci. Odaberite tipku ^{(push button)} ActiveX Control s padajućeg izbornika.

Nacrtajte gumb na bilo koji dio lista dalje od mjesta gdje će ići podaci.

Desnom tipkom kliknite gumb i odaberite Svojstva^(Properties) . U prozoru Svojstva^(Properties) promijenite Naslov u ono što želite prikazati korisniku. U ovom slučaju to može biti Calculate 5% Tax .

Vidjet ćete da se ovaj tekst odražava na samom gumbu. Zatvorite prozor svojstava^(properties) i dvaput kliknite na sam gumb. Ovo će otvoriti prozor za uređivanje koda, a vaš će kursor biti unutar funkcije koja će se pokrenuti kada korisnik pritisne tipku.

Zalijepite kod za obračun poreza iz gornjeg odjeljka u ovu funkciju, zadržavajući množitelj porezne stope na 0,05. Ne zaboravite uključiti sljedeća 2 retka kako biste definirali aktivni list.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Sada ponovite postupak ponovno, stvarajući drugi gumb. Napravite natpis Calculate 7% Tax .

Dvaput kliknite^{(Double-click)} na taj gumb i zalijepite isti kod, ali postavite porezni multiplikator 0,07.

Sada, ovisno koji gumb pritisnete, stupac poreza će se izračunati u skladu s tim.

Kada završite, imat ćete oba gumba na svom listu. Svaki od njih će pokrenuti drugačiji obračun poreza i upisat će različite rezultate u stupac rezultata. 

Da biste to poslali, odaberite izbornik Razvojni programer^(Developer) i odaberite Način dizajna^{(Design Mode)} iz grupe Kontrole^(Controls) na vrpci da biste onemogućili način dizajna^{(Design Mode)} . Ovo će aktivirati tipke. 

Pokušajte odabrati svaki gumb da biste vidjeli kako se mijenja stupac rezultata "porezi".

Pošaljite nekome rezultate izračuna^{(Email Calculation Results to Someone)}

Što ako želite poslati rezultate u proračunskoj tablici nekome putem e-pošte?

Možete stvoriti još jedan gumb pod nazivom Pošaljite list šefu^{(Email Sheet to Boss)} koristeći isti postupak iznad. Kôd za ovaj gumb uključivat će korištenje Excel CDO objekta za konfiguriranje postavki SMTP e-pošte i slanje rezultata e-poštom u formatu koji može čitati korisnik.

Da biste omogućili ovu značajku, morate odabrati Alati i reference^{(Tools and References)} . Pomaknite se prema dolje do Microsoft CDO za Windows 2000 knjižnicu^{(Microsoft CDO for Windows 2000 Library)} , omogućite je i odaberite U redu^(OK) .

Postoje tri glavna odjeljka koda koji trebate izraditi da biste poslali e-poruku i ugradili rezultate proračunske tablice.

Prvi je postavljanje varijabli koje drže predmet, adrese primatelja i pošiljatelja^(From) te tijelo e-pošte.

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."

Naravno, tijelo mora biti dinamično ovisno o tome koji su rezultati u listu, pa ćete ovdje morati dodati petlju koja prolazi kroz raspon, izdvaja podatke i upisuje redak po red u tijelo.

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

Sljedeći odjeljak uključuje postavljanje SMTP postavki tako da možete slati e-poštu putem svog SMTP poslužitelja. Ako koristite Gmail , to je obično vaša Gmail adresa e-pošte, lozinka za Gmail i Gmail SMTP^{(Gmail SMTP)} poslužitelj (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

Zamijenite [email protected] i lozinku podacima o svom računu.

Na kraju, da biste pokrenuli slanje e-pošte, umetnite sljedeći kod.

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

Napomena^(Note) : ako vidite pogrešku u prijenosu kada pokušavate pokrenuti ovaj kôd, vjerojatno je zato što vaš Google račun blokira pokretanje "manje sigurnih aplikacija". Morat ćete posjetiti stranicu postavki manje sigurnih aplikacija^{(less secure apps settings page)} i UKLJUČITI ovu značajku.

Nakon što je to omogućeno, vaša će e-pošta biti poslana. Ovako to izgleda osobi koja primi vašu automatski generiranu e-poštu s rezultatima.

Kao što možete vidjeti postoji mnogo toga što zapravo možete automatizirati s Excel VBA . Pokušajte se poigrati s isječcima koda o kojima ste naučili u ovom članku i stvorite vlastite jedinstvene VBA automatizacije.

An Advanced VBA Guide For MS Excel

If you are just getting started with VBA, then yоu’ll want to stаrt out studying our VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.

The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.

Advanced VBA Guide For Microsoft Excel

The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet

The following are the most common uses of VBA in Excel.

• Import data and perform calculations
• Calculate results from a user pressing a button
• Email calculation results to someone

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

Importing Data and Performing Calculations

One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.

With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.

The following import code will run and import the CSV file into your Excel spreadsheet.

Dim ws As Worksheet, strFile As String

Set 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

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.

This will create a Sub Worksheet_Activate() function. Paste the code above into that function.

This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.

If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.

Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.

The order of actions your code should take is:

1. Create new results column called taxes.
2. Loop through the units sold column and calculate the sales tax.
3. Write the calculate results to the appropriate row in the sheet.

The following code will accomplish all of these steps.

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

This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).

Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.

Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!

Calculate Results From Button Press

If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.

Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?

You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.

Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.

Draw the pushbutton onto any part of the sheet away from where any data will go.

Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.

You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.

Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.

Double-click that button and paste the same code, but make the tax multiplier 0.07.

Now, depending which button you press, the taxes column will be calculated accordingly.

Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column. 

To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons. 

Try selecting each push button to see how the “taxes” result column changes.

Email Calculation Results to Someone

What if you want to send the results on the spreadsheet to someone via email?

You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.

To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.

There are three main sections to the code you need to create to send out an email and embed spreadsheet results.

The first is setting up variables to hold the subject, To and From addresses, and the email body.

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."

Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.

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

The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (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

Replace [email protected] and password with your own account details.

Finally, to initiate the email send, insert the following code.

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

Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.

After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.

As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.

Related posts

• Najbolji VBA vodič (za početnike) koji će vam ikada trebati

• Kako stvoriti VBA makro ili skriptu u Excelu

• Vodič za sve ekstenzije Excel datoteka i što one znače

• Kako izbrisati prazne retke u Excelu

• Popravite poruku o pogrešci MS Officea "Ne mogu provjeriti licencu".

• Kako popraviti redak u Excelu

• Kako otvoriti Word i Excel u sigurnom načinu rada

• Kako izračunati Z-score u Excelu

• Kako umetnuti CSV ili TSV u Excel radni list

• Kako spojiti ćelije, stupce i retke u Excelu

• Kako koristiti Excelovu značajku govornih ćelija

• Brzo izgovorite brojeve u Wordu i Excelu

• Kako ukloniti crte mreže u Excelu

• Kako podijeliti Excel datoteku za jednostavnu suradnju

• Centrirajte svoje podatke radnog lista u Excelu za ispis

• Koristite nazive dinamičkog raspona u Excelu za fleksibilne padajuće izbornike

• Kako pretraživati ​​u Excelu

• Provjerite sve hiperveze u MS Word dokumentu za nekoliko sekundi

• Grupirajte retke i stupce u Excel radnom listu

• Koristite tipkovnicu za promjenu visine retka i širine stupca u Excelu