Ak s VBA^(VBA) ešte len začínate , mali by ste začať študovať našu príručku VBA pre začiatočníkov^{(VBA guide for beginners)} . Ale ak ste skúsený odborník na VBA a hľadáte pokročilejšie veci, ktoré môžete robiť s VBA v Exceli^(Excel) , pokračujte v čítaní.

Možnosť používať kódovanie VBA v ^(VBA)Exceli^(Excel) otvára celý svet automatizácie. Môžete automatizovať výpočty v Exceli^(Excel) , pomocou tlačidiel a dokonca aj odosielať e-maily. Existuje viac možností na automatizáciu vašej každodennej práce s VBA , ako si možno uvedomujete.

Pokročilá príručka VBA pre Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

Hlavným cieľom písania kódu VBA v ^(VBA)Exceli^(Excel) je, aby ste mohli extrahovať informácie z tabuľky, vykonávať na nej rôzne výpočty a potom zapísať výsledky späť do tabuľky.

Nasledujú najbežnejšie použitia jazyka VBA^(VBA) v Exceli^(Excel) .

• Importujte^(Import) údaje a vykonávajte výpočty
• Vypočítajte^(Calculate) výsledky, keď používateľ stlačí tlačidlo
• Pošlite^(Email) niekomu výsledky výpočtu e-mailom

Pomocou týchto troch príkladov by ste mali byť schopní napísať celý rad vlastného pokročilého kódu Excel VBA .

Import údajov a vykonávanie výpočtov^{(Importing Data and Performing Calculations)}

Jednou z najbežnejších vecí, na ktoré ľudia používajú Excel , je vykonávanie výpočtov s údajmi, ktoré existujú mimo Excelu^(Excel) . Ak nepoužívate VBA , znamená to, že musíte manuálne importovať údaje, spustiť výpočty a vypísať tieto hodnoty do iného hárku alebo zostavy.

S VBA môžete automatizovať celý proces. Ak máte napríklad každý pondelok stiahnutý nový súbor ^(Monday)CSV do adresára vo svojom počítači , môžete svoj kód VBA nakonfigurovať tak, aby sa spustil pri prvom otvorení tabuľky v utorok^(Tuesday) ráno.

Nasledujúci importovací kód sa spustí a importuje súbor CSV do tabuľky Excel .

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

Otvorte nástroj na úpravu jazyka Excel VBA^{(Excel VBA)} a vyberte objekt Sheet1 . V rozbaľovacích poliach objektu a metódy vyberte Pracovný hárok^(Worksheet) a Aktivovať^(Activate) . Toto spustí kód pri každom otvorení tabuľky.

Tým sa vytvorí funkcia Sub Worksheet_Activate() . Vložte vyššie uvedený kód do tejto funkcie.

Toto nastaví aktívny pracovný hárok na Hárok1^(Sheet1) , vymaže hárok, pripojí sa k súboru pomocou cesty k súboru, ktorú ste definovali pomocou premennej strFile , a potom cyklus With prejde každým riadkom v súbore a umiestni údaje do hárka počnúc bunkou A1. .

Ak spustíte tento kód, uvidíte, že údaje súboru CSV sa importujú do vašej prázdnej tabuľky v Hárok1^(Sheet1) .

Import je len prvým krokom. Ďalej chcete vytvoriť novú hlavičku pre stĺpec, ktorý bude obsahovať výsledky vašich výpočtov. V tomto príklade povedzme, že chcete vypočítať 5 % dane zaplatené za predaj každej položky.

Poradie akcií, ktoré by mal váš kód vykonať, je:

1. Vytvorte nový stĺpec výsledkov s názvom dane^(taxes) .
2. Prejdite stĺpec predaných jednotiek^{(units sold)} a vypočítajte daň z obratu.
3. Výsledky výpočtu zapíšte do príslušného riadku v hárku.

Nasledujúci kód vykoná všetky tieto kroky.

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

Tento kód nájde posledný riadok vo vašom hárku údajov a potom nastaví rozsah buniek (stĺpec s predajnými cenami) podľa prvého a posledného riadka údajov. Potom kód prejde každou z týchto buniek, vykoná výpočet dane a zapíše výsledky do vášho nového stĺpca (stĺpec 5).

Prilepte vyššie uvedený kód VBA pod predchádzajúci kód a spustite skript. Výsledky uvidíte v stĺpci E.

Teraz zakaždým, keď otvoríte pracovný hárok programu Excel^(Excel) , automaticky sa vypne a získa najnovšiu kópiu údajov zo súboru CSV . Potom vykoná výpočty a výsledky zapíše do hárku. Už nemusíte nič robiť ručne!

Vypočítajte výsledky stlačením tlačidla^{(Calculate Results From Button Press)}

Ak uprednostňujete priamu kontrolu nad spustením výpočtov namiesto automatického spúšťania pri otvorení hárka, môžete namiesto toho použiť ovládacie tlačidlo.

Ovládacie^(Control) tlačidlá sú užitočné, ak chcete ovládať, ktoré výpočty sa použijú. Napríklad v tom istom prípade ako je uvedené vyššie, čo ak chcete použiť 5 % sadzbu dane pre jeden región a 7 % sadzbu dane pre iný región?

Môžete povoliť, aby sa rovnaký importný kód CSV spustil automaticky, ale kód výpočtu dane ponechajte spustený, keď stlačíte príslušné tlačidlo.

Pomocou rovnakej tabuľky ako vyššie vyberte kartu Vývojár^(Developer) a v skupine Ovládacie prvky^(Controls) na páse s nástrojmi vyberte položku Vložiť . ^(Insert)V rozbaľovacej ponuke vyberte tlačidlo ^{(push button)}ActiveX Control .

Nakreslite tlačidlo na ľubovoľnú časť hárku mimo miesta, kam budú smerovať údaje.

Kliknite pravým tlačidlom myši na tlačidlo a vyberte položku Vlastnosti^(Properties) . V okne Vlastnosti^(Properties) zmeňte popis na to, čo chcete zobraziť používateľovi. V tomto prípade to môže byť Calculate 5% Tax .

Tento text uvidíte na samotnom tlačidle. Zatvorte okno vlastností^(properties) a dvakrát kliknite na samotné tlačidlo. Tým sa otvorí okno editora kódu a váš kurzor bude vo vnútri funkcie, ktorá sa spustí, keď používateľ stlačí tlačidlo.

Do tejto funkcie prilepte kód výpočtu dane z časti vyššie, pričom ponechajte násobiteľ sadzby dane na hodnote 0,05. Nezabudnite zahrnúť nasledujúce 2 riadky, aby ste definovali aktívny hárok.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Teraz zopakujte proces znova a vytvorte druhé tlačidlo. Vytvorte nadpis Calculate 7% Tax .

Dvakrát kliknite na^{(Double-click)} toto tlačidlo a vložte rovnaký kód, ale použite daňový multiplikátor na 0,07.

Teraz sa podľa toho, ktoré tlačidlo stlačíte, vypočíta stĺpec daní.

Po dokončení budete mať na hárku obe tlačidlá. Každý z nich spustí iný výpočet dane a do stĺpca výsledku zapíše iné výsledky. 

Ak chcete tento text napísať, vyberte ponuku Vývojár^(Developer) a vyberte Režim návrhu^{(Design Mode)} zo skupiny Ovládacie prvky^(Controls) na páse s nástrojmi, čím deaktivujete režim návrhu^{(Design Mode)} . Tým sa aktivujú tlačidlá. 

Skúste vybrať každé tlačidlo, aby ste videli, ako sa mení stĺpec výsledkov „dane“.

Pošlite niekomu výsledky výpočtu e-mailom^{(Email Calculation Results to Someone)}

Čo ak chcete výsledky v tabuľke poslať niekomu e-mailom?

Rovnakým postupom uvedeným vyššie môžete vytvoriť ďalšie tlačidlo s názvom E-mailový list šéfovi . ^{(Email Sheet to Boss)}Kód pre toto tlačidlo bude zahŕňať použitie objektu Excel CDO na konfiguráciu nastavení e-mailu SMTP a odoslanie výsledkov e-mailom vo formáte čitateľnom pre používateľa.

Ak chcete povoliť túto funkciu, musíte vybrať položku Nástroje a odkazy^{(Tools and References)} . Prejdite nadol na Microsoft CDO for Windows 2000 Library , povoľte ju a vyberte OK .

Existujú tri hlavné časti kódu, ktoré musíte vytvoriť, aby ste mohli odoslať e-mail a vložiť výsledky tabuľky.

Prvým je nastavenie premenných, ktoré budú obsahovať predmet, adresy^(From) príjemcu a odosielateľa a telo e-mailu.

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

Samozrejme, telo musí byť dynamické v závislosti od toho, aké výsledky sú v hárku, takže tu budete musieť pridať slučku, ktorá prechádza rozsahom, extrahuje údaje a zapisuje riadok po riadku do tela.

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

Ďalšia časť zahŕňa nastavenie nastavení SMTP , aby ste mohli odosielať e-maily cez váš server SMTP . Ak používate službu Gmail^(Gmail) , je to zvyčajne vaša e-mailová adresa služby Gmail , heslo služby Gmail a server ^(Gmail)SMTP služby Gmail^{(Gmail SMTP)} (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

Nahraďte [email protected] a heslo svojimi podrobnosťami o účte.

Nakoniec na začatie odosielania e-mailu vložte nasledujúci kód.

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

Poznámka^(Note) : Ak sa pri pokuse o spustenie tohto kódu zobrazí chyba prenosu, je to pravdepodobne preto, že váš účet Google blokuje spustenie „menej bezpečných aplikácií“. Budete musieť navštíviť stránku nastavení menej bezpečných aplikácií^{(less secure apps settings page)} a zapnúť túto funkciu.

Po aktivácii bude váš e-mail odoslaný. Takto to vyzerá pre osobu, ktorá dostane váš automaticky generovaný e-mail s výsledkami.

Ako vidíte, je toho veľa, čo môžete skutočne automatizovať pomocou Excel VBA . Skúste sa pohrať s útržkami kódu, o ktorých ste sa dozvedeli v tomto článku, a vytvorte si vlastné jedinečné automatizácie VBA .

An Advanced VBA Guide For MS Excel

If you are just getting started with VBA, thеn you’ll want to start 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

• Najlepší sprievodca VBA (pre začiatočníkov), aký budete kedy potrebovať

• Ako vytvoriť makro alebo skript VBA v programe Excel

• Sprievodca všetkými rozšíreniami súborov programu Excel a ich význam

• Ako odstrániť prázdne riadky v Exceli

• Opravte chybové hlásenie MS Office „Nedá sa overiť licencia“.

• Ako triediť podľa dátumu v Exceli

• Ako odstrániť mriežku v Exceli

• Prečo by ste mali používať pomenované rozsahy v Exceli

• Ako presúvať stĺpce v Exceli

• Ako rýchlo vložiť viac riadkov v Exceli

• Pomocou nástroja Excel na vyhľadávanie cieľov analýzy What-If

• Zoskupte riadky a stĺpce v pracovnom hárku programu Excel

• Čo je Microsoft Publisher? Sprievodca pre začiatočníkov

• Ako vytvoriť viacero prepojených rozbaľovacích zoznamov v Exceli

• Ako vložiť CSV alebo TSV do pracovného hárka programu Excel

• 2 spôsoby použitia funkcie Transponovať v Exceli

• Ako používať absolútne odkazy v Exceli

• Ako zlúčiť údaje vo viacerých súboroch programu Excel

• Ako odstrániť duplicitné riadky v Exceli

• Použite názvy dynamických rozsahov v Exceli na flexibilné rozbaľovacie zoznamy