in dit artikel laat ik u enkele van de meest verbazingwekkende VBA Excel-codes zien die u kunt gebruiken om uw werk te optimaliseren. VBA is een programmeertaal, die kan worden gebruikt om de mogelijkheden van MS Excel en andere MS Office-toepassingen uit te breiden. Het is zeer nuttig voor MS Excel-gebruikers, omdat het kan worden gebruikt om uw werk te automatiseren en aanzienlijk verbeteren van uw efficiëntie. Dit artikel introduceert u VBA en laat u enkele van de meest nuttige, klaar om VBA-codes te gebruiken die er zijn. U kunt deze macro voorbeelden gebruiken om uw eigen scripts te maken die aan uw eigen behoeften voldoen.

u hebt geen programmeerervaring nodig om gebruik te maken van de informatie in dit artikel, maar er wordt van u verwacht dat u basiskennis van Excel hebt. Als u een beginnende gebruiker bent, raad ik u aan om het artikel 20 Excel-formules te lezen die u nu moet gebruiken om meer te leren over de kernfunctionaliteiten van Excel.

Ik heb voor u een aantal kant-en-klare VBA Excel Macro voorbeelden voorbereid met geweldige functionaliteit die u kunt gebruiken om uw werk te optimaliseren. Om ze te gebruiken, moet u ze “installeren” in uw Excel-bestand. De volgende paragraaf gaat over Excel macro installatie. Sla dit deel over als je hier al bekend mee bent.

Hoe installeer ik een macro

in Excel, druk op de toetsencombinatie alt + F11. Dit brengt u naar de VBA-editor in MS Excel. Klik vervolgens met de rechtermuisknop op de map Microsoft Excel objecten aan de linkerkant en selecteer Insert => Module. Dit is de plaats waar de macro ‘ s worden opgeslagen. Om gebruik te maken van de macro, moet u het Excel-document opslaan als macro-ingeschakeld. In het tabblad file = > opslaan als, kies Opslaan als macro-enabled werkmap (de .XLSM extensie) nu is het tijd om je eerste macro te schrijven!

1. Kopieer gegevens van het ene bestand naar het andere.

zeer nuttige macro, omdat het laat zien hoe u een gegevensbereik vanuit vba kunt kopiëren en hoe u een nieuwe werkmap kunt maken en een naam geven. U kunt het eenvoudig upgraden naar uw eigen vereisten:

Sub CopyFiletoAnotherWorkbook() 'Copy the Data Sheets ("Example 1").Bereik ("B4: C15").Kopiëren ' Maak een nieuwe werkmap werkmappen.Toevoegen ' plak de data ActiveSheet.Plak ' uit te schakelen applicatie waarschuwingen toepassing.DisplayAlerts = False ' sla het nieuwe bestand op. Wijzig de naam van de map. ActiveWorkbook.SaveAs Bestandsnaam:="C:\Temp\MyNewBook.XLSX "' Turn applicatie waarschuwingen terug op de toepassing.DisplayAlerts = TrueEnd Sub

2. Toon verborgen rijen

af en toe bevatten grote Excel-bestanden Verborgen regels voor betere duidelijkheid. Hier is een macro die alle rijen van een actief werkblad zichtbaar maakt:

Sub ShowHiddenRows() kolommen.Hele kolom.Verborgen = Valse Rijen.EntireRow.Hidden = FalseEnd Sub

3. Lege rijen en kolommen verwijderen

lege rijen in Excel zijn een probleem met gegevensverwerking. Hier is hoe je van ze af kunt komen:

Sub Deleteeemptyrowsandcolumns() 'Declare your variables. Dim MyRange As Range Dim iCounter As Long ' Definieer het doelbereik. Stel MyRange = ActiveSheet In.UsedRange ' start omgekeerde looping door het bereik van rijen. Voor iCounter = MyRange.Rij.Tel tot 1 stap -1 ' als de hele rij leeg is, verwijder deze dan. Indien Toepassing.CountA (rijen (iCounter).EntireRow ) = 0 dan rijen (iCounter).Verwijder ' remove comment to See which are the empty rows 'MsgBox " row"& iCounter &" is empty " End If 'Increment the counter down Next iCounter' Stap 6: Start reverse looping door het bereik van kolommen. Voor iCounter = MyRange.Kolom.Tel tot 1 stap -1 ' Stap 7: als de hele kolom leeg is, verwijder deze dan. Indien Toepassing.CountA(kolommen (iCounter).EntireColumn) = 0 dan kolommen (iCounter).Einde Verwijderen Als " Stap 8: Verhoog de teller naar beneden volgende Icounter einde Sub

4. Zoek een lege cel

Sub FindEmptyCell() ActiveCell.Offset (1, 0).Selecteer Doen Terwijl Niet IsEmpty (ActiveCell) ActiveCell.Offset (1, 0).Selecteer LoopEnd Sub

5. Vervang lege cellen door een waarde.

zoals eerder vermeld, interfereren lege cellen met gegevensverwerking en het aanmaken van draaitabellen. Hier is een code die alle lege cellen vervangt door 0. Deze macro heeft een zeer grote toepassing omdat u deze kunt gebruiken om n/A-resultaten te vinden en te vervangen, evenals andere tekens zoals puntjes, komma ’s of dubbele waarden:

Sub FindAndReplace () 'Declare your variables Dim MyRange As Range Dim MyCell As Range' sla de werkmap op voordat u de cellen wijzigt? Selecteer Case MsgBox ("kan deze actie niet ongedaan maken. "& _ " werkmap eerst opslaan?", vbYesNoCancel) Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select ' Definieer het doelbereik. Set MyRange = Selection ' start looping door het bereik. Voor elke MyCell in MyRange 'Check voor nul lengte voeg dan 0. Als Len (MyCell.Value) = 0 then MyCell = 0 End If ' Get the next cell in the range Next Mycellend Sub

6. Sorteer getallen

de volgende macro sorteert in oplopende volgorde alle getallen uit de kolom van de actieve cel. Dubbelklik gewoon op een cel uit een kolom die u wilt sorteren.NB: u moet de code in blad 1 zetten en niet in een module om te werken:

privé Subwerkblad _voorvoordoubleclick (ByVal doel als bereik, annuleren als Boolean) 'Declare your Variables Dim LastRow As Long 'Find last row = Cells (Rows.Tellen, 1).Einde (xlUp).Row ' Sorteer oplopend op dubbelgeklikt kolomrijen ("6:"& LastRow) .Sorteren _ Key1: = Cellen (6, ActiveCell.Kolom), _ Order1: = xlAscendingEnd Sub

7. Lege spaties verwijderen

soms bevatten gegevens in de werkmap extra spaties (spaties) die de gegevensanalyse kunnen verstoren en de formules kunnen beschadigen. Hier is een macro die alle spaties uit een voorgeselecteerd cellenbereik verwijdert:

Sub TrimTheSpaces() 'Declare your variables Dim MyRange As Range Dim MyCell As Range 'sla de werkmap op voordat u cellen wijzigt Selecteer Case MsgBox ("kan deze actie niet ongedaan maken. "& _ " werkmap eerst opslaan?", vbYesNoCancel) Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select ' Definieer het doelbereik. Set MyRange = Selection ' start looping door het bereik. Voor elke MyCell in MyRange 'Trim de ruimtes. Als niet IsEmpty(MyCell) dan Mycell = Trim (MyCell) eindigen als 'Get the next cell in the range Next Mycellend Sub

8. Markeer dublicated values

soms zijn er dubbele waarden in de verschillende kolommen die we willen verlichten. Hier is een macro die precies dat doet:

Sub HighlightDuplicates() 'Declare your variables Dim MyRange As Range Dim MyCell As Range 'Definieer het doelbereik. Set MyRange = Selection ' start looping door het bereik. Voor elke MyCell in MyRange 'zorg ervoor dat de cel tekstopmaak heeft. Als Werkblad Functie.CountIf (MyRange, MyCell.Waarde) > 1 dan MyCell.Interieur.ColorIndex = 36 einde als ' haal de volgende cel in het bereik Next MyCellEnd Sub

9. Markeer de top tien waarden

deze code zal de top tien waarden uit een selectie van cellen markeren:

Sub toppen() selectie.Formatvoorwaarden.AddTop10 Selectie.Formatvoorwaarden (Selectie.Formatvoorwaarden.Tellen).Stel Eerste Prioriteit In Bij Selectie.Formatvoorwaarden (1).TopBottom = xlTop10Top ' wijzig hier de rang om een ander aantal waarden te markeren .Rang = 10 .Percentage = False Einde Met Met Selectie.Formatvoorwaarden (1).Letter .Kleur = -16752384 .TintAndShade = 0 Eindig Met Met Selectie.Formatvoorwaarden (1).Interieur .Patrooncolorindex = xlAutomatic .Kleur = 13561798 .TintAndShade = 0 Eindig Met Selectie.Formatvoorwaarden (1).StopIfTrue = FalseEnd Sub

U kunt de code eenvoudig aanpassen om een ander aantal waarden te markeren.

10. Markeer groter dan waarden

wanneer u deze code uitvoert, wordt een venster gevraagd. Het vraagt u de waarde die u wilt vergelijken met de cellen die u hebt geselecteerd.

Sub HighlightGreaterThanValues() Dim I als Integer i = InputBox ("Enter Greater Than Value"," Enter Value") selectie.Formatvoorwaarden.Delete ' Verander de Operator in xlLower om selectie lager dan waarden te markeren.Formatvoorwaarden.Voeg Type toe: = xlCellValue, Operator: = xlGreater, Formula1: = I selectie.Formatvoorwaarden (Selectie.Formatvoorwaarden.Tellen).Stel Eerste Prioriteit In Bij Selectie.Formatvoorwaarden (1).Letter.Kleur = RGB (0, 0, 0).Interieur.Color = RGB (31, 218, 154) End WithEnd Sub

U kunt deze code aanpassen om ook lagere waarden te markeren.

een eenvoudige macro die alle cellen markeert die opmerkingen bevatten:

Sub HighlightCommentCells() selectie.Speciale cellen (xlCellTypeComments).Selecteer Selectie.Style= "Note" End Sub

12. Markeer cellen met verkeerd gespelde woorden

Dit is uiterst handig als u werkt met functies die tekenreeksen bevatten, maar iemand heeft de tekenreeks met een fout ingevoerd en uw formules werken niet. Hier is hoe dit probleem op te lossen:

 Sub ColorMispelledCells() voor elke cl in ActiveSheet.UsedRange Indien Niet Toepassing.CheckSpelling (Woord:= cl.Tekst) dan _ cl . Interieur.ColorIndex = 28 Next clEnd Sub

13. Maak een draaitabel

Hier kunt u een draaitabel maken vanuit MS Excel (versie 2007). Vooral handig, wanneer u het maken van een aangepaste rapport elke dag. U kunt het aanmaken van de draaitabel op de volgende manier optimaliseren:

Sub PivotTableForExcel2007() Dim SourceRange As Range Set SourceRange = Sheets("Sheet1").Bereik ("A3:N86") ActiveWorkbook.Draaiknopen.Create (_SourceType: = xlDatabase, _ SourceData:=SourceRange, _ Version: = xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ Tablenaam:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Voeg een actieve werkmap toe in een e-mail

Mijn favoriete VBA-code. Hiermee kunt u het bestand waar u aan werkt toevoegen en verzenden met een vooraf gedefinieerd e-mailadres, berichttitel en berichttekst! U moet eerst een verwijzing naar Microsoft Outlook instellen (in uw VBA-editior klikt u op tools => referenties en kiest u Microsoft Outlook).

Sub SendFIleAsAttachment() 'Declare your variables 'Set reference to Microsoft Outlook Object library Dim OLApp As Outlook.Toepassing Dim OLMail als Object ' Open Outlook start een nieuw e-mailitem Set Olapp = New Outlook.Applicatie Set OLMail = OLApp.CreateItem (0) OLApp.Sessie.Login ' Build your mail item and send With OLMail .To = "[email protected]; [email protected]". CC="".BCC ="".Onderwerp = "Dit is de onderwerpregel".Lichaam = "Hallo daar".Bijlage.ActiveWorkbook Toevoegen.Volledige naam .Display ' veranderen naar .Verzenden om te verzenden zonder te herzien eindigt met ' Memory cleanup Set OLMail = Nothing Set OLApp = NothingEnd Sub

15. Stuur alle Excel-diagrammen naar een PowerPoint-presentatie

een zeer handige macro waarmee u al uw Excel-diagrammen in uw Powerpoint-presentatie kunt toevoegen met slechts een enkele klik:

Sub SendExcelFiguresToPowerPoint() 'Set reference to Microsoft Powerpoint Object Library 'Declare your variables Dim PP As PowerPoint.Toepassing Dim PPPres Als PowerPoint.Presentatie Dim PPSlide Als PowerPoint.Dim I als geheel getal ' Check voor grafieken; afsluiten als er geen diagrammen bestaan ("Dia Data").Selecteer Of ActiveSheet.Chartobjecten.Count < 1 Then MsgBox "No charts existing the active sheet" Exit Sub End If ' Open PowerPoint and create new presentation Set PP = New PowerPoint.Toepassingsset PPPres = PP.Presentaties.PP toevoegen.Visible = True ' Start de lus op basis van het aantal kaarten voor i = 1 naar ActiveSheet.Chartobjecten.Count ' kopieer de grafiek als een afbeelding ActiveSheet.Chartobjecten (i).Grafiek.CopyPicture _ grootte: = xlScreen, formaat: = Xlpicture toepassing.Wacht (Nu + TimeValue ("0: 00:1")) 'Tel dia' s en voeg nieuwe dia toe als het volgende beschikbare dianummer ppSlideCount = PPPres.Dia.Count Set PPSlide = PPPres.Dia.Voeg(SlideCount + 1, ppLayoutBlank) PPSlide toe.Selecteer ' plak de afbeelding en pas de positie Aan; Ga naar volgende grafiek PPSlide.Shapes.Plakken.Selecteer PP.ActiveWindow.Selectie.ShapeRange.Uitlijnen msoAlignCenters, True PP.ActiveWindow.Selectie.ShapeRange.Align msoAlignMiddles, True Next I ' Memory Cleanup Set PPSlide = Nothing Set PPPres = Nothing Set PP = NothingEnd Sub

16. Excel-tabel verzenden in MS Word

Excel-tabellen worden meestal in tekstdocumenten geplaatst. Hier is een geautomatiseerde manier om uw Excel-tabel naar MS Word te exporteren:

 Sub ExcelTableInWord() 'Set reference to Microsoft Word Object library 'Declare your variables Dim MyRange As Excel.Bereik Dim wd als Woord.Toepassing Dim wdDoc als Word.Document Dim WdRange Als Word.Bereik ' kopieer de gedefinieerde bereikbladen ("omzettingstabel").Bereik ("B4: F10").Cop ' Open het doel Word document Set wd = nieuw woord.Applicatieset wdDoc = wd.Document.Open _ (ThisWorkbook.Path && " PasteTable.docx") wd.Visible = True ' focus op het doel bladwijzer Set Wdrange = wdDoc.Bladwijzers ("DataTableHere").Bereik ' verwijder de oude tabel en plak Nieuw Op Fout hervatten volgende WdRange.Tabellen (1).Wdrange Verwijderen.Plak 'plak in de tabel' pas kolombreedtes aan WdRange.Tabellen (1).Kolom.SetWidth _ (MyRange.Breedte / MyRange.Kolom.Count), wdAdjustSameWidth 'plaatst de bladwijzer wdDoc.Bladwijzer.Voeg "DataTableHere" toe, Wdrange ' Memory cleanup Set wd = Nothing Set wdDoc = Nothing Set WdRange = NothingEnd Sub

17. Extract een specifiek woord uit een cel

We kunnen formules gebruiken als we een bepaald aantal symbolen willen extraheren. Maar wat als we alleen het tweede woord uit een zin of een reeks woorden in een cel willen halen? Om dit te doen, kunnen we een aangepaste Excel-functie maken met VBA. Dit is een van de meest iportant VBA-functionaliteiten, omdat u hiermee uw eigen functies kunt maken die niet bestaan in MS Excel. Laten we verder gaan en twee functies maken: findword() en findwordrev(). Hier is de vba-code voor dit:

functie FindWord(bron als tekenreeks, positie als geheel getal) als tekenreeks bij fout hervatten volgende FindWord = Split(werkblad functie.Trim(Source),"") (Position - 1) On Error GoTo 0end Function FindWordRev (Source As String, Position As Integer) As String Dim Arr () As String Arr = VBA.Splitsen (Werkblad Functie.Trim(Source), " ") On Error Resume Next FindWordRev = Arr(Ubund (Arr) - Position + 1) On Error GoTo 0end Function

erg mooi, we hebben twee Cstom Excel-functies gemaakt. Probeer ze nu te gebruiken in Excel. De functie = FindWordRev (A1,1) neemt het laatste woord uit cel A1. De functie = FindWord (A1,3) neemt het derde woord uit cel A1, enz.

18. Bescherm uw werkmap

soms willen we de gegevens in ons bestand beschermen zodat alleen wij het kunnen wijzigen. Zo doe je dit met VBA:

Sub ProtectSheets() 'Declare your variables Dim ws As werkblad 'start looping door alle werkbladen voor elke ws in ActiveWorkbook.Werkbladen beschermen en lus naar het volgende werkblad ws.Bescherm wachtwoord:= "1234" Next Wsend Sub

Gefeliciteerd! Omdat je dit nog steeds leest, ben je echt enthousiast over het leren van VBA. Zoals u zelf al hebt gezien, is de VBA-programmeertaal uiterst nuttig en kan ons veel tijd besparen. Ik hoop dat je deze informatie nuttig vond en het gebruiken om een meester te worden in MS Excel, VBA en computersoftware in het algemeen.