Als je net begint met het gebruik van VBA, moet je eerst de basisprincipes bestuderen. Een beginnershandleiding voor VBAMaar als u een doorgewinterde VBA-expert bent en op zoek bent naar meer geavanceerde mogelijkheden van 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 vervolgkeuzelijsten Object en Methode de optie Werkblad و ActiverenHierdoor wordt de code elke keer uitgevoerd wanneer u het spreadsheet opent.
Dit zal een functie creëren Sub Worksheet_Activate()Plak de bovenstaande code in deze functie.
Hiermee wordt het actieve werkblad ingesteld op Sheet1Scan het document en maak verbinding met het bestand via het bestandspad dat je in de variabele hebt opgegeven. strFileVervolgens de lus Met Door elke regel in het bestand te doorlopen en de gegevens op het werkblad te plaatsen, beginnend bij cel A1.
Als je deze code uitvoert, zie je dat de gegevens uit het CSV-bestand in je lege spreadsheet worden geïmporteerd. Sheet1.
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 dezelfde gegevenstabel als hierboven vermeld en selecteer het tabblad. Ontwikkelaaren selecteer Invoegen Van een groep Controls Selecteer een besturingselement in het lint. druk op de knop Selecteer ActiveX in het下拉菜单.
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 drukknop en selecteer AanbodWijzig in het venster Eigenschappen het bijschrift naar de tekst die u aan de gebruiker wilt tonen. In dit geval zou het bijvoorbeeld kunnen zijn: Bereken de belasting van 5%.
Deze tekst wordt ook op de knop zelf weergegeven. Venster sluiten. EigendommenDubbelklik op dezelfde knop. Hiermee wordt een code-editorvenster geopend en bevindt de cursor 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 een naam. Berekening van 7% belasting.
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 een lijst. Ontwikkelaaren selecteer Ontwerpmodus Uit de groep besturingselementen in het lint uitschakelen OntwerpmodusHierdoor 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 nog een knop maken met de naam Stuur het formulier per e-mail naar je baas. Door dezelfde procedure te volgen als hierboven beschreven, zal de code voor deze knop gebruikmaken van een Excel CDO-object om de SMTP-e-mailinstellingen te configureren en de resultaten in een gebruiksvriendelijk formaat te verzenden.
Om deze functie in te schakelen, moet u selecteren Hulpmiddelen en referenties. Scroll naar beneden naar Microsoft CDO voor Windows 2000-bibliotheekSchakel het 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
vervangen e-mail@website.com Het wachtwoord bevat uw 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
opmerkingAls je een overdrachtsfout ziet wanneer je deze code probeert uit te voeren, blokkeert je Google-account waarschijnlijk de uitvoering van 'minder veilige apps'. Je moet dan naar [link/reference] gaan. Instellingenpagina voor minder veilige apps En schakel deze optie in.
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.