Ha csak most kezd el a VBA-val, akkor érdemes elkezdenie a VBA útmutató kezdőknek tanulmányozását. De ha tapasztalt VBA-szakértő, és fejlettebb dolgokat keres az Excel segítségével a VBA-val, akkor olvassa tovább.
Az a lehetőség, hogy az VBA-kódolást Excelben használja, egész világot nyit meg. automatizálás. Automatizálhatja a számításokat az Excel programban, a nyomógombok segítségével, és e-maileket is küldhet. Több lehetőség van a napi munkájának automatizálására a VBA-val, mint gondolnánk.
Speciális VBA útmutató a Microsoft Excelhez
A VBA kód Excelben történő írásának fő célja az, hogy az információk kinyerhetők legyenek. egy táblázatból, végezzen különféle számításokat rajta, majd írja vissza az eredményeket a táblázatba
Az alábbiakban a VBA leggyakoribb felhasználási módjait használjuk az Excelben.
E három példa segítségével képes legyen írni a saját speciális Excel VBA kódját.
Adatok importálása és számítások elvégzése
Az egyik leggyakoribb dolog, amelyet az emberek az Excelhez használnak számításokat végez az Excelön kívüli adatokkal kapcsolatban. Ha nem használja a VBA-t, ez azt jelenti, hogy manuálisan kell importálnia az adatokat, futtatnia a számításokat, és ezeket az értékeket egy másik lapra vagy jelentésre kell kiadnia.
In_content_1 all: [300x250] / dfp : [640x360]->A VBA segítségével automatizálhatja a teljes folyamatot. Például, ha minden hétfőn letölt egy új CSV-fájlt a számítógép egy mappájába, beállíthatja a VBA-kódot futtatásához, amikor kedden reggel nyitja meg a táblázatot.
A következő importkód futtassa és importálja a CSV fájlt az Excel táblázatába.
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
Nyissa meg az Excel VBA szerkesztő eszközt, és válassza a Sheet1 objektumot. Az objektum és módszer legördülő mezőiből válassza a Munkalapés az Aktiváláslehetőséget. Ez a kódot futtatja minden alkalommal, amikor megnyitja a táblázatot.
Ez létrehoz egy Sub Worksheet_Activate ()funkciót. Illessze be a fenti kódot ebbe a funkcióba.
Ez az aktív munkalapot Sheet1-re állítja, törli a lapot, csatlakozik a fájlhoz a strFileváltozóval megadott fájlútvonal segítségével, majd a A Ahurok a fájl minden során áthalad és az adatokat az A1 cellába kezdve helyezi a lapba.
Ha ezt a kódot futtatja, látni fogja, hogy a CSV fájl adatai importálódik az üres táblázatba, a 1. lap-ba.
Az importálás csak az első lépés . Ezután létrehoz egy új fejlécet az oszlophoz, amely tartalmazza a számítási eredményeket. Tegyük fel, hogy ebben a példában ki akarja számítani az egyes elemek eladásakor fizetett 5% -os adót.
A kód végrehajtásának sorrendje a következő:
A következő kód fogja végrehajtani ezeket a lépéseket.
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
Ez a kód megtalálja az utolsó sort az adatlapján, majd az első és az utolsó sor szerint beállítja a cellák tartományát (az eladási árakat tartalmazó oszlop). Ezután a kód áthúzza ezeket a cellákat, elvégzi az adó kiszámítását, és az eredményeket beírja az új oszlopba (5. oszlop).
Illessze be a fenti VBA kódot az előző kód alá, és futtassa a szkriptet. Az eredmények az E oszlopban jelennek meg.
Most, amikor megnyitja az Excel munkalapot, automatikusan kialszik, és az adatok legfrissebb másolatát kapja a CSV-fájlból. Ezután elvégzi a számításokat, és az eredményeket beírja a lapra. Többé nem kell manuálisan tennie!
Eredmények kiszámítása a gombnyomásokból
Ha inkább közvetlen irányítást szeretne végezni a számítások futtatásakor helyett egy vezérlőgombot használhat.
A vezérlőgombok hasznosak, ha ellenőrizni szeretnénk, hogy melyik számítást használjuk. Például ebben az esetben, mint fentebb, mi van, ha egy régióra 5% -os adómértéket és egy másik régióra 7% -os adómértéket akar használni?
Ugyanazt a CSV-importkódot engedélyezheti az automatikusan fut, de hagyja, hogy az adószámítási kód futjon, ha megnyomja a megfelelő gombot.
A fenti táblázat használatával válassza ki a Fejlesztőfület, majd válassza a Beszúrás lehetőséget.a szalag Vezérlőkcsoportjából. Válassza a nyomógombotaz ActiveX vezérlőt a legördülő menüből.
Rajzolja a nyomógombot a lap bármely részére, attól a helytől, ahonnan minden adat megy.
Kattintson a jobb gombbal a nyomógombra, és válassza a Tulajdonságoklehetőséget. A Tulajdonságok ablakban változtassa meg a képaláírást abban, amit meg szeretne jeleníteni a felhasználónak. Ebben az esetben ez lehet Számítsa ki az 5% -os adót.
Ezt a szöveget maga a nyomógomb tükrözi. Zárja be a tulajdonságokablakot, és kattintson duplán a nyomógombra. Ez megnyitja a kódszerkesztő ablakot, és a kurzor abban a funkcióban lesz, amely akkor fut, amikor a felhasználó megnyomja a nyomógombot.
Illessze be az adószámítási kódot a fenti szakaszból ebbe a funkcióba, az adómérték szorzóját 0,05-nél tartva. Ne felejtse el az aktív lap meghatározásához a következő 2 sort is beilleszteni.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Most ismételje meg a folyamatot egy második nyomógomb létrehozásával. Készítse el a feliratot Számítsa ki a 7% -os adót.
Kattintson duplán arra a gombra és illessze be ugyanazt a kódot, de tegye az adó szorzóját 0.07-re.
Most, attól függően, hogy melyik gombot nyomja meg, az adók oszlop ennek megfelelően kell kiszámítani.
Ha kész, mindkét nyomógomb megtalálható a lapon. Mindegyik eltérő adószámítást kezdeményez, és eltérő eredményeket ír az eredmény oszlopba.
Ennek szövegezéséhez válassza a Fejlesztőmenüt, majd válassza a Tervezési módmenüpontot a Vezérlők csoportból a szalagon a Tervezési mód. Ez aktiválja a nyomógombokat.
Próbáljon meg kiválasztani az összes nyomógombot, hogy megnézze, hogyan változik az „adók” eredmény oszlop.
E-mail számítás eredménye valakinek
Mit ha el szeretné küldeni e-mailben a táblázatban szereplő eredményeket valakinek?
A fenti eljárással létrehozhat egy másik gombot, amelynek neve E-mail lap a főnöknek. Ennek a gombnak az kódja magában foglalja az Excel CDO objektum használatát az SMTP e-mail beállítások konfigurálásához és az eredmények e-mail küldését felhasználó számára olvasható formátumban.
A szolgáltatás engedélyezéséhez ki kell választania a Eszközök és Irodalom. Görgessen le a Microsoft CDO for Windows 2000 Libraryelemre, engedélyezze azt, és válassza az OKlehetőséget.
A kódnak három fő része van, amelyet létrehoznia kell az e-mail küldéséhez és a táblázatkezelési eredmények beágyazásához.
Az első a változók tárolására szolgál. a tárgy, a cím és a cím, valamint az e-mail törzs.
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."
Természetesen a testnek dinamikusnak kell lennie, attól függően, hogy milyen eredmények a lapban, tehát itt hozzá kell adnia egy hurkot, amely átmegy a tartományon, kivonja az adatokat, és egy sort ír egyszerre a testhez.
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
A következő szakasz az SMTP-beállítások beállítását foglalja magában, hogy e-maileket küldhessen az SMTP-kiszolgálón keresztül. Ha a Gmailt használja, ez általában a Gmail e-mail címe, a Gmail jelszava és a Gmail SMTP szerver (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
Csere [email protected] és jelszó a saját fiókjának adataival.
Végül az e-mail küldés elindításához írja be a következő kódot.
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
Megjegyzés: Ha szállítási hibát észlel, amikor ezt a kódot futtatja, valószínűleg azért van, mert Google-fiókja megakadályozza a „kevésbé biztonságos alkalmazások” futtatását. Látogatnia kell a kevésbé biztonságos alkalmazások beállításai oldal -ben, és be kell kapcsolnia ezt a funkciót.
Miután engedélyezte ezt, az Ön e-mailje el lesz küldve. Így néz ki az a személy, aki megkapja az automatikusan létrehozott eredmények e-mailt.
Mint láthatja, sok minden valóban automatizálható az Excel VBA segítségével. Próbáljon meg játszani a kódrészletekkel, amelyekről a cikkben tanult, és készítsen saját egyedi VBA-automatizálást.