Als je net begint met VBA, is het raadzaam om eerst de VBA-beginnershandleiding te bestuderen . Maar als je een ervaren VBA-gebruiker bent en op zoek bent naar meer geavanceerde mogelijkheden met VBA in Excel, lees dan verder.
De mogelijkheid om VBA-opmaak in Excel te gebruiken opent een hele wereld aan automatiseringsmogelijkheden. Je kunt Excel-berekeningen, knoppen en zelfs het verzenden van e-mails automatiseren. Er zijn meer mogelijkheden om je dagelijkse werk met VBA te automatiseren dan je misschien beseft.
Geavanceerde VBA-handleiding voor Microsoft Excel
Het belangrijkste doel van het schrijven van VBA-code in Excel is om informatie uit een spreadsheet te halen, er verschillende berekeningen op uit te voeren en de resultaten vervolgens weer naar de spreadsheet terug te schrijven.
Hieronder volgen de meest voorkomende toepassingen van VBA in Excel.
- Gegevens importeren en berekeningen uitvoeren
- Resultaten berekenen op basis van muisklikken van de gebruiker.
- De resultaten van de berekeningen per e-mail naar iemand versturen
Aan de hand van deze drie voorbeelden zou u in staat moeten zijn om zelf diverse geavanceerde Excel VBA-codes te schrijven.
Gegevens importeren en berekeningen uitvoeren
Een van de meest voorkomende toepassingen van Excel is het uitvoeren van berekeningen op gegevens die zich buiten Excel bevinden. Als u geen VBA gebruikt, betekent dit dat u de gegevens handmatig moet importeren, de berekeningen moet uitvoeren en de resultaten naar een ander werkblad of rapport moet exporteren.
Met VBA kunt u het hele proces automatiseren. Als u bijvoorbeeld elke maandag een nieuw CSV-bestand downloadt naar een map op uw computer, kunt u uw VBA-code zo configureren dat deze wordt uitgevoerd wanneer u uw spreadsheet op dinsdagochtend voor het eerst opent.
De volgende importcode wordt uitgevoerd en het CSV-bestand wordt in uw Excel-spreadsheet geïmporteerd.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Open de Excel VBA-editor en selecteer het object Sheet1. Kies in de vervolgkeuzemenu's Object en Methode de optie Werkblad en vervolgens Activeren . Hierdoor wordt de code elke keer uitgevoerd wanneer u het spreadsheet opent.
Hiermee wordt een Sub Worksheet_Activate() functie aangemaakt . Plak de bovenstaande code in deze functie.
Dit stelt het actieve werkblad in op Blad1 , scant het blad, maakt verbinding met het bestand via het bestandspad dat u hebt opgegeven met de variabele strFile , en vervolgens doorloopt de With -lus elke regel in het bestand en plaatst de gegevens in het blad, beginnend bij cel A1.
Als je deze code uitvoert, zie je dat de gegevens uit het CSV-bestand worden geïmporteerd in je lege spreadsheet, in Blad1.
Importeren is slechts de eerste stap. Vervolgens moet u een nieuwe kolomkop aanmaken die de resultaten van uw berekeningen bevat. Laten we in dit voorbeeld zeggen dat u de 5% belasting wilt berekenen die over de verkoop van elk artikel is betaald.
De volgorde waarin uw code de acties moet uitvoeren is:
- Maak een nieuwe resultatenkolom aan met de naam belastingen.
- Herhaal dit over een kolom verkochte eenheden En bereken de omzetbelasting.
- Schrijf de resultaten van de berekeningen in de daarvoor bestemde rij op het papier.
De volgende code voert al deze stappen uit.
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") 'Vind de laatste rij en kolom 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
Deze code zoekt de laatste rij van uw gegevensblad en stelt vervolgens het bereik van cellen (de kolom met de verkoopprijzen) in op basis van de eerste en laatste rij met gegevens. De code doorloopt vervolgens elk van deze cellen, berekent de btw en schrijft het resultaat naar uw nieuwe kolom (kolom 5).
Plak de bovenstaande VBA-code onder de vorige code en voer het script uit. Je ziet de resultaten verschijnen in kolom E.
Telkens wanneer u nu uw Excel-werkblad opent, haalt het programma automatisch de meest recente versie van de gegevens uit het CSV-bestand op. Vervolgens voert het de berekeningen uit en schrijft de resultaten naar het werkblad. U hoeft niets meer handmatig te doen!
Bereken de resultaten door op de knop te drukken.
Als u liever zelf bepaalt wanneer berekeningen worden uitgevoerd, in plaats van dat ze automatisch worden gestart wanneer het werkblad wordt geopend, kunt u in plaats daarvan een bedieningsknop gebruiken.
Bedieningsknoppen zijn handig als u de gebruikte berekeningen wilt beïnvloeden. Stel bijvoorbeeld dat u in hetzelfde scenario als hierboven een belastingtarief van 5% voor de ene regio en 7% voor de andere regio wilt hanteren.
Je kunt ervoor kiezen om dezelfde CSV-importcode automatisch te laten uitvoeren, maar de code voor de belastingberekening pas te laten uitvoeren wanneer op de betreffende knop wordt gedrukt.
Gebruik hetzelfde spreadsheet als hierboven vermeld, selecteer het tabblad Ontwikkelaar en vervolgens Invoegen in de groep Besturingselementen op het lint. Selecteer het ActiveX -drukknopbesturingselement in het vervolgkeuzemenu.
Teken de drukknop op een willekeurig deel van het papier, ver weg van de plek waar gegevens zouden worden opgeslagen.
Klik met de rechtermuisknop op de knop en selecteer Eigenschappen . Wijzig in het venster Eigenschappen het bijschrift naar de tekst die u aan de gebruiker wilt tonen. In dit geval zou dat bijvoorbeeld 'Bereken 5% belasting' kunnen zijn.
Je ziet deze tekst terug op de knop zelf. Sluit het venster Eigenschappen en dubbelklik op de knop. Hiermee open je een code-editor en de cursor bevindt zich in de functie die wordt uitgevoerd wanneer de gebruiker op de knop klikt.
Plak de code voor de belastingberekening uit het bovenstaande gedeelte in deze functie en houd de belastingfactor op 0.05. Vergeet niet de volgende twee regels toe te voegen om het actieve werkblad te identificeren.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1")
Herhaal nu het proces en maak een tweede knop aan. Geef deze de naam " 7% belastingberekening".
Dubbelklik op deze knop en plak dezelfde code, maar stel de belastingvermenigvuldiger in op 0.07.
Afhankelijk van welke knop u indrukt, wordt de belastingkolom dienovereenkomstig berekend.
Als je klaar bent, zie je beide knoppen op je werkblad. Elke knop start een andere belastingberekening en toont verschillende resultaten in de kolom 'Resultaten'.
Om dit te testen, selecteer je het menu Ontwikkelaar en vervolgens Ontwerpmodus in de groep besturingselementen in het lint om de Ontwerpmodus uit te schakelen . Hierdoor worden de knoppen geactiveerd.
Probeer op elke knop te klikken om te zien hoe de resultatenkolom 'Belastingen' verandert.
De accountresultaten naar iemand e-mailen
Wat als je de resultaten van het spreadsheet via e-mail naar iemand wilt sturen?
Je kunt een andere knop maken met de naam ' E-mail het spreadsheet naar je baas' met behulp van dezelfde procedure als hierboven beschreven. De code voor deze knop gebruikt een Excel CDO-object om de SMTP-e-mailinstellingen te configureren en de resultaten in een gebruiksvriendelijk formaat via e-mail te verzenden.
Om deze functie in te schakelen, selecteert u Extra en referenties . Scroll omlaag naar Microsoft CDO voor Windows 2000-bibliotheek , schakel deze in en selecteer OK.
Er zijn drie belangrijke codeonderdelen die u moet maken om een e-mail te verzenden en de resultaten van een spreadsheet mee te sturen.
De eerste stap is het instellen van variabelen om het onderwerp, de "aan"- en "van"-adressen en de e-mailtekst te bewaren.
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 = "Betaalde belastingen dit kwartaal" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "Hieronder vindt u een overzicht van de betaalde belastingen over de verkopen dit kwartaal."
Uiteraard moet de tekst dynamisch zijn, afhankelijk van de resultaten in het artikel. Daarom moet je hier een lus toevoegen die het bereik doorloopt, de gegevens extraheert en elke keer een regel in de tekst schrijft.
Set StartCell = Range("A1"). 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
In het volgende gedeelte wordt beschreven hoe u SMTP instelt, zodat u e-mails kunt verzenden via uw SMTP-server. Als u Gmail gebruikt, is dit meestal uw Gmail-e-mailadres, uw Gmail-wachtwoord en de 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@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "wachtwoord" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Set .Configuration = CDO_Config End With
Vervang email@website.com en password door uw eigen accountgegevens.
Om de e-mail te verzenden, voert u tot slot de volgende code in.
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 Foutafhandeling: Als Err.Description <> "" Dan MsgBox Err.Description
Let op : Als u een overdrachtsfout ziet wanneer u deze code probeert uit te voeren, blokkeert uw Google-account waarschijnlijk de optie 'Minder veilige apps'. U moet naar de instellingenpagina voor 'Minder veilige apps' gaan en deze optie inschakelen.
Nadat je dit hebt ingeschakeld, wordt je e-mail verzonden. Zo ziet de e-mail eruit voor de ontvanger.
Zoals je ziet, kun je met Excel VBA heel wat automatiseren. Experimenteer met de codefragmenten die je in dit artikel hebt geleerd en creëer je eigen unieke VBA-automatiseringen.
Reacties zijn gesloten.