In diesem Artikel zeige ich Ihnen einige der erstaunlichsten VBA Excel-Codes, mit denen Sie Ihre Arbeit optimieren können. VBA ist eine Programmiersprache, mit der die Funktionen von MS Excel und anderen MS Office-Anwendungen erweitert werden können. Es ist äußerst hilfreich für MS Excel-Benutzer, da es verwendet werden kann, um Ihre Arbeit zu automatisieren und Ihre Effizienz erheblich zu verbessern. Dieser Artikel führt Sie in VBA ein und zeigt Ihnen einige der nützlichsten, gebrauchsfertigen VBA-Codes. Sie können diese Makrobeispiele verwenden, um Ihre eigenen Skripte zu erstellen, die Ihren eigenen Anforderungen entsprechen.

Sie benötigen keine Programmiererfahrung, um die Informationen in diesem Artikel nutzen zu können, es wird jedoch erwartet, dass Sie über Grundkenntnisse in Excel verfügen. Wenn Sie ein Anfänger sind, würde ich Ihnen empfehlen, den Artikel 20 Excel-Formeln zu lesen, die Sie jetzt verwenden sollten, um mehr über die Kernfunktionen von Excel zu erfahren.

Ich habe für Sie eine Reihe gebrauchsfertiger VBA Excel-Makrobeispiele mit großartigen Funktionen vorbereitet, mit denen Sie Ihre Arbeit optimieren können. Um sie zu verwenden, müssen Sie sie in Ihrer Excel-Datei „installieren“. Der nächste Abschnitt befasst sich mit der Installation von Excel-Makros. Überspringen Sie diesen Teil, wenn Sie damit bereits vertraut sind.

So installieren Sie ein Makro

Drücken Sie in Excel die Tastenkombination Alt + F11. Dies führt Sie zum VBA-Editor in MS Excel. Klicken Sie dann mit der rechten Maustaste auf den Ordner Microsoft Excel Objects links und wählen Sie das Modul Insert =>. Dies ist der Ort, an dem die Makros gespeichert werden. Um das Makro zu verwenden, müssen Sie das Excel-Dokument als makrofähig speichern. Von der Registerkarte Datei => speichern unter, wählen Sie Speichern als Makro-fähige Arbeitsmappe (die .xlsm-Erweiterung) Jetzt ist es Zeit, Ihr erstes Makro zu schreiben!

1. Kopieren Sie Daten von einer Datei in eine andere.

Sehr nützliches Makro, da es zeigt, wie man eine Reihe von Daten aus vba kopiert und wie man eine neue Arbeitsmappe erstellt und benennt. Sie können es einfach an Ihre eigenen Anforderungen anpassen:

' CopyFiletoAnotherWorkbook() 'Kopieren Sie die Datenblätter("Beispiel 1").Bereich ("B4:C15").Kopieren 'Erstellen Sie eine neue Arbeitsmappe Arbeitsmappen.Hinzufügen 'Fügen Sie die Daten ActiveSheet.Paste 'Schalten Sie Anwendung Warnungen Anwendung.DisplayAlerts = False 'Speichert die neue Datei. Ändern Sie den Namen des Verzeichnisses. ActiveWorkbook.SaveAs Dateiname: ="C:\Temp\MyNewBook.xlsx" 'Anwendungsbenachrichtigungen wieder bei Anwendung aktivieren.DisplayAlerts = TrueEnd Sub 
2. Ausgeblendete Zeilen anzeigen
 
Gelegentlich enthalten große Excel-Dateien zur besseren Übersichtlichkeit ausgeblendete Zeilen. Hier ist ein Makro, das alle Zeilen eines aktiven Arbeitsblatts einblendet:
 
Sub ShowHiddenRows() Columns .EntireColumn.Versteckt = Falsche Zeilen.EntireRow.Versteckt = FalseEnd Sub
3. Leere Zeilen und Spalten löschen
Leere Zeilen in Excel sind ein Problem bei der Datenverarbeitung. So werden Sie sie los:
' DeleteEmptyRowsAndColumns() 'Deklarieren Sie Ihre Variablen. Dim myRange As Range Dim iCounter As Long 'Definieren Sie den Zielbereich. Setzen Sie myRange = ActiveSheet.UsedRange 'Starten Sie die umgekehrte Schleife durch den Zeilenbereich. Für iCounter = myRange.Tabellenzeilen.Zähle bis 1 Schritt -1 'Wenn die gesamte Zeile leer ist, löschen Sie sie. Wenn Anwendung.CountA(Zeilen(iCounter).EntireRow) = 0 Dann Zeilen(iCounter).Delete 'Remove comment to See which are the empty rows 'MsgBox "row " & iCounter & " is empty" End If 'Increment the counter down Next iCounter 'Schritt 6: Starten Sie die umgekehrte Schleife durch den Spaltenbereich. Für iCounter = myRange.Spalten.Zähle bis 1 Schritt -1 'Schritt 7: Wenn die gesamte Spalte leer ist, löschen Sie sie. Wenn Anwendung.CountA(Spalten(iCounter).EntireColumn) = 0 Dann Spalten(iCounter).Löschen Ende, wenn 'Schritt 8: Erhöhen Sie den Zähler nach unten Next iCounter End Sub
4. Finde eine leere Zelle
 
Sub FindEmptyCell() ActiveCell.Offset(1, 0).Wählen Sie Do While Not isEmpty(ActiveCell) ActiveCell.Offset(1, 0).Wählen Sie LoopEnd Sub
5. Ersetzen Sie leere Zellen durch einen Wert.
Wie bereits erwähnt, stören leere Zellen die Datenverarbeitung und die Erstellung von Pivot-Tabellen. Hier ist ein Code, der alle leeren Zellen durch 0 ersetzt. Dieses Makro hat eine sehr große Anwendung, da Sie damit N / A-Ergebnisse sowie andere Zeichen wie Punkte, Kommas oder doppelte Werte suchen und ersetzen können:
Sub FindAndReplace() 'Deklarieren Sie Ihre Variablen Dim myRange As Range Dim myCell As Range 'Speichern Sie die Arbeitsmappe, bevor Sie Zellen ändern? Select Case MsgBox("Diese Aktion kann nicht rückgängig gemacht werden. " & _ "Arbeitsmappe zuerst speichern?", vbYesNoCancel) Fall ist = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select 'Definieren Sie den Zielbereich. Set myRange = Selection 'Schleife durch den Bereich starten. Überprüfen Sie für jede myCell in myRange 'die Länge Null und fügen Sie dann 0 hinzu. Wenn Len(myCell.Value) = 0 Then myCell = 0 End If 'Holen Sie sich die nächste Zelle im Bereich Next MyCellEnd Sub
6. Zahlen sortieren
Das folgende Makro sortiert in aufsteigender Reihenfolge alle Zahlen aus der Spalte der aktiven Zelle. Doppelklicken Sie einfach auf eine beliebige Zelle aus einer Spalte, die Sie sortieren möchten.NB: Sie müssen den Code in Blatt 1 und nicht in ein Modul einfügen, um zu funktionieren:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel As Boolean) 'Deklarieren Sie Ihre Variablen Dim LastRow As Long 'Finde die letzte Zeile LastRow = Cells (Zeilen.Zählen, 1) .Ende (xlUp) .Row 'aufsteigend nach doppelt angeklickten Spaltenzeilen sortieren ("6:" & LastRow) .Sortieren _ Key1: = Zellen (6, ActiveCell.Spalte), _ Order1: = xlAscendingEnd Sub
7. Leere Leerzeichen entfernen
Gelegentlich enthalten Daten in der Arbeitsmappe zusätzliche Leerzeichen (Leerzeichen), die die Datenanalyse beeinträchtigen und Formeln beschädigen können. Hier ist ein Makro, das alle Leerzeichen aus einem vorgewählten Zellenbereich entfernt:
Sub TrimTheSpaces() 'Deklarieren Sie Ihre Variablen Dim myRange As Range Dim myCell As Range 'Speichern Sie die Arbeitsmappe, bevor Sie die Zellen ändern Select Case MsgBox("Diese Aktion kann nicht rückgängig gemacht werden. " & _ "Arbeitsmappe zuerst speichern?", vbYesNoCancel) Fall ist = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select 'Definieren Sie den Zielbereich. Set myRange = Selection 'Schleife durch den Bereich starten. Schneiden Sie für jede Myzelle in myRange die Leerzeichen ab. Wenn nicht isEmpty(myCell), Dann myCell = Trim(myCell) End If 'Holen Sie sich die nächste Zelle im Bereich Next MyCellEnd Sub
8. Markieren Sie doppelte Werte
Manchmal gibt es doppelte Werte in den verschiedenen Spalten, die wir beleuchten möchten. Hier ist ein Makro, das genau das tut:
Sub HighlightDuplicates() 'Deklarieren Sie Ihre Variablen Dim myRange As Range Dim myCell As Range 'Definieren Sie den Zielbereich. Set myRange = Selection 'Schleife durch den Bereich starten. Stellen Sie für jede myCell in myRange sicher, dass die Zelle über eine Textformatierung verfügt. Wenn Arbeitsblattfunktion.CountIf(myRange, myCell.Wert) > 1 Dann myCell.Inner.ColorIndex = 36 End If 'Holen Sie sich die nächste Zelle im Bereich Next MyCellEnd Sub
9. Markieren Sie die zehn wichtigsten Werte
 
Dieser Code hebt die zehn wichtigsten Werte aus einer Auswahl von Zellen hervor:
Sub TopTen() Selection.Formatbedingungen.AddTop10 Auswahl.Formatbedingungen(Auswahl.Formatbedingungen.Zählen).SetFirstPriority Mit Auswahl.Formatbedingungen(1) .TopBottom = xlTop10Top 'Ändern Sie hier den Rang, um eine andere Anzahl von Werten hervorzuheben .Rang = 10 .Prozent = Falsch Ende mit Mit Auswahl.Formatbedingungen(1).Schriftart .Farbe = -16752384 .TintAndShade = 0 Ende mit Auswahl.Formatbedingungen(1).Inner .PatternColorIndex = xlautomatisch .Farbe = 13561798 .TintAndShade = 0 Ende mit Auswahl.Formatbedingungen(1).StopIfTrue = FalseEnd Sub
Sie können den Code leicht optimieren, um eine unterschiedliche Anzahl von Werten hervorzuheben.
10. Markieren Sie größer als Werte
Wenn Sie diesen Code ausführen, wird ein Fenster angezeigt. Sie werden nach dem Wert gefragt, den Sie mit den ausgewählten Zellen vergleichen möchten.
Sub HighlightGreaterThanValues() Dim i Als Integer i = InputBox("Enter Greater Than Value", "Enter Value") Auswahl.Formatbedingungen.Delete 'Ändern Sie den Operator in xlLower, um die Auswahl unter den Werten hervorzuheben.Formatbedingungen.Fügen Sie Typ hinzu: = xlCellValue, Operator: = xlGreater, Formula1: = i Auswahl.Formatbedingungen(Auswahl.Formatbedingungen.Zählen).SetFirstPriority Mit Auswahl.Formatbedingungen(1) .Schriftart.Farbe = RGB(0, 0, 0) .Inner.Color = RGB(31, 218, 154) End WithEnd Sub
Sie können diesen Code optimieren, um auch niedrigere Werte hervorzuheben.
Ein einfaches Makro, das alle Zellen hervorhebt, die Kommentare enthalten:
Sub HighlightCommentCells() Auswahl.SpecialCells(xlCellTypeComments).Auswahl auswählen.Style= "Hinweis"Ende Sub
12. Markieren Sie Zellen mit falsch geschriebenen Wörtern
Dies ist äußerst nützlich, wenn Sie mit Funktionen arbeiten, die Zeichenfolgen verwenden. Hier ist, wie dieses Problem zu beheben:
 Sub ColorMispelledCells() Für jede cl In ActiveSheet.UsedRange Wenn nicht Anwendung.CheckSpelling(Wort:=kl.Text) Dann _ cl.Inner.ColorIndex = 28 Weiter clEnd Sub
 
13. Erstellen einer Pivot-Tabelle
Hier erfahren Sie, wie Sie eine Pivot-Tabelle aus MS Excel (Version 2007) erstellen. Besonders nützlich, wenn Sie jeden Tag einen benutzerdefinierten Bericht erstellen. Sie können die Erstellung der Pivot-Tabelle folgendermaßen optimieren:
Sub PivotTableForExcel2007() Dim SourceRange As Range Set SourceRange = Sheets("Sheet1").Bereich("A3:N86") Aktivworkbook.PivotCaches.Erstellen( _ sourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub
14. Aktive Arbeitsmappe in einer E-Mail anhängen
Mein Lieblings-VBA-Code. Sie können die Datei, an der Sie arbeiten, mit einer vordefinierten E-Mail-Adresse, einem Nachrichtentitel und einem Nachrichtentext anhängen und senden! Sie müssen zuerst den Verweis auf Microsoft Outlook festlegen (klicken Sie in Ihrem VBA-Editor auf tools => Referenzen und wählen Sie Microsoft Outlook).
Sub SendFIleAsAttachment() 'Deklarieren Sie Ihre Variablen 'Setzen Sie den Verweis auf die Microsoft Outlook-Objektbibliothek Dim OLApp Als Outlook.Anwendung Dim OLMail als Objekt 'Outlook öffnen Starten Sie eine neue E-Mail-Element Set OLApp = New Outlook.Anwendung Set OLMail = OLApp.CreateItem(0) OLApp.Sitzung.Logon 'Erstellen Sie Ihre E-Mail-Element und senden Sie mit OLMail .To = "[email protected]; [email protected] " .CC = "" .BCC = "" .Subject = "Dies ist die Betreffzeile" .Körper = "Hallo" .Anlage.Fügen Sie ActiveWorkbook hinzu.Vollständiger Name .Anzeige 'Wechseln zu .Senden zum Senden ohne Überprüfung Ende mit 'Memory cleanup Set OLMail = Nothing Set OLApp = NothingEnd'
15. Senden Sie alle Excel-Diagramme an eine PowerPoint-Präsentation
Ein sehr praktisches Makro, mit dem Sie alle Ihre Excel-Diagramme mit einem einzigen Klick in Ihre Powerpoint-Präsentation einfügen können:
Sub SendExcelFiguresToPowerPoint() 'Verweis auf Microsoft Powerpoint-Objektbibliothek festlegen 'Deklarieren Sie Ihre Variablen in PP als PowerPoint.Anwendung Dim PPPres Als Powerpoint.Präsentation Dim PPSlide Als PowerPoint.Slide Dim i As Integer 'Nach Diagrammen suchen; beenden, wenn keine Diagramme vorhanden sind Blätter ("Foliendaten").Wählen Sie Wenn ActiveSheet.ChartObjects.Count < 1 Then MsgBox "Keine Diagramme im aktiven Blatt vorhanden" Exit Sub End If 'PowerPoint öffnen und neue Präsentation erstellen Set PP = New PowerPoint.Anwendungssatz PPPres = PP.Präsentation.PP hinzufügen.Visible = True 'Startet die Schleife basierend auf der Diagrammanzahl für i = 1 Bis ActiveSheet.ChartObjects.Count 'Kopieren Sie das Diagramm als Bild ActiveSheet.ChartObjects(i).Diagramm.CopyPicture _ Größe:=xlScreen, Format:=xlPicture Anwendung.Warten (Jetzt + TimeValue("0:00:1")) 'Folien zählen und neue Folie als nächste verfügbare Foliennummer ppSlideCount = PPPres hinzufügen.Wasserrutschen.Anzahl gesetzt PPSlide = PPPres.Wasserrutschen.Hinzufügen (SlideCount + 1, ppLayoutBlank) PPSlide.Wählen Sie 'Fügen Sie das Bild ein und passen Sie seine Position an. Gehe zum nächsten Diagramm PPSlide.Form.Einfügen.Wählen Sie PP.ActiveWindow.Auswahl.In: ShapeRange.Richten Sie msoAlignCenters, True PP.ActiveWindow.Auswahl.In: ShapeRange.Align msoAlignMiddles, True Next i 'Speicherbereinigung Set PPSlide = Nichts Set PPPres = Nichts Set PP = NothingEnd Sub
16. Excel-Tabelle in MS Word senden
Excel-Tabellen werden normalerweise in Textdokumente eingefügt. Hier ist eine automatisierte Möglichkeit, Ihre Excel-Tabelle nach MS Word zu exportieren:
 Sub ExcelTableInWord() 'Verweis auf Microsoft Word-Objektbibliothek festlegen'Deklarieren Sie Ihre Variablen in myRange als Excel.Bereich Dim wd als Wort.Anwendung Dim wdDoc als Wort.Dokument Dim WdRange als Word.Bereich 'Kopieren Sie die definierten Bereichsblätter ("Umsatztabelle").Bereich ("B4:F10").Cop 'Öffnen Sie das Ziel Word-Dokument Set wd = New Word.Anwendungssatz wdDoc = wd.Belege.Öffnen Sie _ (ThisWorkbook.Pfad && "PasteTable.docx") wd.Visible = True 'Setze den Fokus auf das Ziel und setze WdRange = wdDoc.Lesezeichen ("DataTableHere").Range 'Löschen Sie die alte Tabelle und fügen Sie neue auf Fehler fortsetzen Nächsten WdRange.Tabellen(1).Löschen Sie WdRange.Einfügen 'In die Tabelle einfügen 'Spaltenbreiten anpassen WdRange.Tabellen(1).Spalten.setWidth _ (myRange.Breite / myRange.Spalten.Count), wdAdjustSameWidth 'Setzen Sie das Lesezeichen wdDoc.Bookmark.Add "DataTableHere", WdRange 'Speicherbereinigung Set wd = Nichts Set wdDoc = Nichts Set WdRange = NothingEnd Sub
17. Extrahieren Sie ein bestimmtes Wort aus einer Zelle
Wir können Formeln verwenden, wenn wir eine bestimmte Anzahl von Symbolen extrahieren möchten. Aber was ist, wenn wir nur das zweite Wort aus einem Satz oder einer Reihe von Wörtern in einer Zelle extrahieren möchten? Dazu können wir mit VBA eine benutzerdefinierte Excel-Funktion erstellen. Dies ist eine der wichtigsten VBA-Funktionen, da Sie damit Ihre eigenen Funktionen erstellen können, die in MS Excel nicht vorhanden sind. Lassen Sie uns zwei Funktionen erstellen: findword() und findwordrev() . Hier ist der VBA-Code dafür:
Funktion FindWord(Quelle als String, Position als Integer) Als String Bei Fehler fortsetzen Weiter FindWord = Split(WorksheetFunction.Trim(Source), " ")(Position - 1) Bei Fehler Gehe zu 0END FunctionFunction FindWordRev(Source Als String, Position Als Integer) As String Dim Arr() As String Arr = VBA.Teilen (Arbeitsblattfunktion.Trim(Source), " ") Bei Fehler Resume Next FindWordRev = Arr(UBound(Arr) - Position + 1) Bei Fehler GoTo 0End Function
Sehr schön, wir haben zwei cstom Excel-Funktionen erstellt. Versuchen Sie nun, sie in Excel zu verwenden. Die Funktion = FindWordRev (A1,1) nimmt das letzte Wort aus Zelle A1. Die Funktion = FindWord (A1,3) nimmt das dritte Wort aus Zelle A1 usw.
18. Schützen Sie Ihre Arbeitsmappe
Manchmal möchten wir die Daten in unserer Datei schützen, damit nur wir sie ändern können. Hier ist, wie dies mit VBA zu tun:
Sub ProtectSheets() 'Deklarieren Sie Ihre Variablen Dim ws als Arbeitsblatt 'Starten Sie eine Schleife durch alle Arbeitsblätter für jedes ws In ActiveWorkbook.Arbeitsblätter 'Schützen und Schleife zum nächsten Arbeitsblatt ws.Passwort schützen:="1234" Next wsEnd Sub
Herzlichen Glückwunsch! Da Sie dies noch lesen, sind Sie wirklich daran interessiert, VBA zu lernen. Wie Sie bereits selbst gesehen haben, ist die Programmiersprache VBA äußerst nützlich und kann uns viel Zeit sparen. Ich hoffe, Sie fanden diese Informationen hilfreich und nutzen sie, um ein Meister in MS Excel, VBA und Computersoftware im Allgemeinen zu werden.