Podrobný průvodce VBA pro MS Excel

Pokud právě začínáte s VBA, budete chtít začít studovat naše Průvodce VBA pro začátečníky. Ale pokud jste zkušeným odborníkem na VBA a hledáte pokročilejší věci, které můžete s VBA dělat v Excelu, pokračujte ve čtení.

Možnost používat kódování VBA v Excelu otevírá celý svět automatizace. Výpočty můžete automatizovat v Excelu, tlačítky a dokonce posílat e-maily. Existuje více možností automatizace vaší každodenní práce s VBA, než si můžete uvědomit.

Podrobný průvodce VBA pro Microsoft Excel

Hlavním cílem psaní kódu VBA v Excelu je, abyste mohli extrahovat informace z tabulky proveďte různé výpočty a poté výsledky zapište zpět do tabulky

Následuje nejběžnější použití VBA v Excelu.

Importovat data a provádět výpočty

Vypočítat výsledky od uživatele stisknutím tlačítka

Výsledky výpočtu e-mailem někomu

S těmito třemi příklady byste měli být schopen psát různé vlastní pokročilé kódy Excel VBA.

Import dat a provádění výpočtů

Jedna z nejčastějších věcí, které lidé používají Excel provádí výpočty dat, která existují mimo Excel. Pokud nepoužíváte VBA, znamená to, že musíte data importovat ručně, spustit výpočty a tyto hodnoty odeslat na jiný list nebo sestavu.

In_content_1 all: [300x250] / dfp : [640x360]->

Pomocí VBA můžete celý proces automatizovat. Pokud například každý pondělí stahujete do adresáře v počítači nový soubor CSV, můžete nakonfigurovat kód VBA tak, aby se spouštěl při prvním otevření tabulky v úterý ráno.

Následující importní kód bude spusťte a importujte soubor CSV do tabulky aplikace 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

Otevřete nástroj pro úpravy Excel VBA a vyberte objekt Sheet1. V rozevíracím seznamu objektů a metod vyberte Pracovní lista Aktivovat. Spustí se kód pokaždé, když otevřete tabulku.

Tím vytvoříte funkci Vedení listu_Activate (). Vložte výše uvedený kód do této funkce.

Nastaví aktivní list na List1, vymaže list, připojí se k souboru pomocí cesty k souboru, kterou jste definovali pomocí proměnné strFile, a pak Scykly smyčky přes každý řádek v souboru a umístí data do listu počínaje buňkou A1.

Pokud spustíte tento kód, uvidíte, že data souboru CSV se importuje do prázdné tabulky v List1.

Import je pouze první krok . Dále chcete vytvořit novou záhlaví pro sloupec, který bude obsahovat výsledky výpočtu. V tomto příkladu řekněme, že chcete vypočítat 5% daně zaplacené z prodeje každé položky.

Pořadí akcí, které by měl váš kód provést, je:

Vytvořit nový sloupec výsledků nazvaný taxe

Opakujte sloupec prodaných jednoteka vypočítejte daň z obratu.

Napište výsledky výpočtu do příslušného řádku v listu.

Následující kód provede všechny tyto 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 najde poslední řádek. ve vašem datovém listu a poté nastaví rozsah buněk (sloupec s prodejními cenami) podle prvního a posledního řádku dat. Pak kód prochází každou z těchto buněk, provede výpočet daně a zapíše výsledky do nového sloupce (sloupec 5).

Vložte výše uvedený kód VBA pod předchozí kód a spusťte skript. Výsledky se zobrazí ve sloupci E.

Nyní při každém otevření listu aplikace Excel automaticky vyjde a získá nejčerstvější kopii dat ze souboru CSV. Poté provede výpočty a výsledky zapíše do listu. Už nemusíte nic dělat ručně!

Vypočítat výsledky stiskem tlačítka

Pokud byste raději měli přímější kontrolu nad spuštěním výpočtů , namísto automatického spuštění při otevírání listu můžete použít ovládací tlačítko.

Ovládací tlačítka jsou užitečná, pokud chcete určit, které výpočty budou použity. Například, v tomto stejném případě jako výše, co když chcete použít 5% daňovou sazbu pro jednu oblast a 7% daňovou sazbu pro jinou oblast?

Můžete povolit stejný importní kód CSV spustit automaticky, ale nechat kód pro výpočet daně spustit, když stisknete příslušné tlačítko.

Pomocí stejné tabulky jako výše, vyberte kartu Vývojářa vyberte Vložitze skupiny Ovládací prvkyna pásu karet. Z rozbalovací nabídky vyberte Ovládací prvek ActiveX Control.

Nakreslete tlačítko na libovolnou část listu pryč, odkud budou data přecházet.

Klepněte pravým tlačítkem myši na tlačítko a vyberte možnost Vlastnosti. V okně Vlastnosti změňte titulek na to, co chcete uživateli zobrazit. V tomto případě to může být Vypočítat 5% daň.

Tento text uvidíte na samotném tlačítku. Zavřete okno vlastnostia poklepejte na samotné tlačítko. Tím se otevře okno editoru kódu a kurzor bude uvnitř funkce, která se spustí, když uživatel stiskne tlačítko.

Do této funkce vložte kód pro výpočet daně z výše uvedené části a multiplikátor daňové sazby ponechte na 0,05. Nezapomeňte zahrnout následující 2 řádky pro definování aktivního listu.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Nyní tento postup opakujte znovu a vytvořte druhé tlačítko. Vytvořte titulek Vypočítat 7% daň.

Poklepejte na toto tlačítko a vložte stejný kód, ale vytvořte multiplikátor daně 0,07.

Nyní, v závislosti na tom, které tlačítko stisknete, bude sloupec daně vypočítat podle toho.

Jakmile budete hotovi, budete mít na svém listu obě tlačítka. Každá z nich zahájí jiný výpočet daně a zapíše různé výsledky do sloupce výsledků.

Chcete-li to poslat textem, vyberte nabídku Vývojářa vyberte Návrhový režimze skupiny Ovládací prvky na pásu karet, abyste deaktivovali Návrhový režim. Tím se aktivují tlačítka.

Zkuste vybrat každé tlačítko a podívejte se, jak se mění sloupec výsledků daní.

Výsledky výpočtu e-mailu někomu

Co pokud chcete někomu poslat výsledky v tabulce e-mailem?

Stejným postupem můžete vytvořit další tlačítko s názvem E-mailové listy pro šéfa. Kód tohoto tlačítka bude zahrnovat použití objektu Excel CDO ke konfiguraci nastavení e-mailu SMTP a odeslání výsledků e-mailem ve formátu čitelném uživatelem.

Chcete-li tuto funkci aktivovat, musíte vybrat Nástroje a Reference. Přejděte dolů na Microsoft CDO pro Windows 2000 Library, povolte ji a vyberte OK.

Existují tři hlavní části kódu, které musíte vytvořit, abyste mohli odeslat e-mail a vložit výsledky tabulky.

První z nich je nastavení proměnných, které budou držet předmět, na a z adres a tělo 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."

Tělo musí být samozřejmě dynamické v závislosti na tom, jaké výsledky jsou v listu, takže zde musíte přidat smyčku, která prochází rozsahem, extrahovat data a do těla zapisovat řádek.

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

Další část obsahuje nastavení SMTP, takže můžete odesílat e-maily prostřednictvím serveru SMTP. Pokud používáte Gmail, jedná se obvykle o vaši e-mailovou adresu Gmail, heslo Gmail a server SMTP Gmailu (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

Nahradit [email protected] a heslo s údaji o vašem účtu.

Nakonec, chcete-li zahájit odesílání e-mailů, vložte následující 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: Pokud se při pokusu o spuštění tohoto kódu zobrazí chyba přenosu, je to pravděpodobně proto, že váš účet Google blokuje spuštění méně bezpečných aplikací. Budete muset navštívit stránka nastavení méně bezpečných aplikací a tuto funkci zapnout.

Poté, co je tato funkce povolena, bude váš e-mail odeslán. Vypadá to, že to vypadá osobě, která přijímá váš automaticky generovaný e-mail s výsledky.