In diesem Lernprogramm werde ich die Arbeit mit Arbeitsmappen in Excel mit VBA behandeln.

In Excel ist eine ‚Arbeitsmappe‘ ein Objekt, das Teil der ‚Arbeitsmappen‘-Sammlung ist. Innerhalb einer Arbeitsmappe haben Sie verschiedene Objekte wie Arbeitsblätter, Diagrammblätter, Zellen und Bereiche, Diagrammobjekte, Formen usw.

Mit VBA können Sie eine Menge Dinge mit einem Arbeitsmappenobjekt tun, z. B. eine bestimmte Arbeitsmappe öffnen, Arbeitsmappen speichern und schließen, neue Arbeitsmappen erstellen, die Arbeitsmappeneigenschaften ändern usw.

Also fangen wir an.

Wenn Sie daran interessiert sind, VBA auf einfache Weise zu lernen, schauen Sie sich mein Online-Excel-VBA-Training an.

Referenzieren einer Arbeitsmappe mit VBA

Es gibt verschiedene Möglichkeiten, auf ein Arbeitsmappenobjekt in VBA zu verweisen. Die Methode, die Sie wählen, hängt davon ab, was Sie tun möchten. In diesem Abschnitt werde ich die verschiedenen Möglichkeiten zum Verweisen auf eine Arbeitsmappe zusammen mit einigen Beispielcodes behandeln.

Arbeitsmappennamen verwenden

Wenn Sie den genauen Namen der Arbeitsmappe haben, auf die Sie verweisen möchten, können Sie den Namen im Code verwenden.

Beginnen wir mit einem einfachen Beispiel.

Wenn Sie zwei Arbeitsmappen geöffnet haben und die Arbeitsmappe mit dem Namen – Examples aktivieren möchten.xlsx, können Sie den folgenden Code verwenden:

Sub ActivateWorkbook()Workbooks("Examples.xlsx").ActivateEnd Sub

Beachten Sie, dass Sie den Dateinamen zusammen mit der Erweiterung verwenden müssen, wenn die Datei gespeichert wurde. Wenn es nicht gespeichert wurde, können Sie den Namen ohne die Dateierweiterung verwenden.

Wenn Sie sich nicht sicher sind, welchen Namen Sie verwenden sollen, wenden Sie sich an den Projekt-Explorer.

Wenn Sie eine Arbeitsmappe aktivieren und eine bestimmte Zelle in einem Arbeitsblatt in dieser Arbeitsmappe auswählen möchten, müssen Sie die gesamte Adresse der Zelle angeben (einschließlich der Arbeitsmappe und des Arbeitsblattnamens).

Sub ActivateWorkbook()Workbooks("Examples.xlsx").Worksheets("Sheet1").ActivateRange("A1").SelectEnd Sub

Der obige Code aktiviert zuerst Sheet1 in den Beispielen.xlsx-Arbeitsmappe und wählt dann Zelle A1 im Blatt aus.

Sie werden oft einen Code sehen, in dem auf ein Arbeitsblatt oder eine Zelle / einen Bereich verwiesen wird, ohne auf die Arbeitsmappe zu verweisen. Dies geschieht, wenn Sie auf das Arbeitsblatt / die Bereiche in derselben Arbeitsmappe verweisen, in der sich der Code befindet und die auch die aktive Arbeitsmappe ist. In einigen Fällen müssen Sie jedoch die Arbeitsmappe angeben, um sicherzustellen, dass der Code funktioniert (mehr dazu im Abschnitt ThisWorkbook).

Indexnummern verwenden

Sie können auch anhand ihrer Indexnummer auf die Arbeitsmappen verweisen.

Wenn Sie beispielsweise drei Arbeitsmappen geöffnet haben, werden im folgenden Code die Namen der drei Arbeitsmappen in einem Meldungsfeld angezeigt (jeweils einzeln).

Sub WorkbookName()MsgBox Workbooks(1).NameMsgBox Workbooks(2).NameMsgBox Workbooks(3).NameEnd Sub

Der obige Code verwendet MsgBox – eine Funktion, die ein Meldungsfeld mit dem angegebenen Text / Wert anzeigt (in diesem Fall der Name der Arbeitsmappe).

Eines der Probleme, die ich oft bei der Verwendung von Indexnummern mit Arbeitsmappen habe, ist, dass Sie nie wissen, welche die erste und welche die zweite Arbeitsmappe ist und so weiter. Um sicherzugehen, müssten Sie den Code wie oben gezeigt oder ähnliches ausführen, um die geöffneten Arbeitsmappen zu durchlaufen und deren Indexnummer zu kennen.

Excel behandelt die zuerst geöffnete Arbeitsmappe mit der Indexnummer 1 und die nächste mit 2 usw.

Trotz dieses Nachteils kann die Verwendung von Indexnummern nützlich sein. Wenn Sie beispielsweise alle geöffneten Arbeitsmappen durchlaufen und alle speichern möchten, können Sie die Indexnummern verwenden. Da dies in diesem Fall für alle Arbeitsmappen gelten soll, sind Sie nicht um deren individuelle Indexnummern besorgt.

Der folgende Code würde alle geöffneten Arbeitsmappen durchlaufen und alle außer der Arbeitsmappe schließen, die diesen VBA-Code enthält.

Sub CloseWorkbooks()Dim WbCount As IntegerWbCount = Workbooks.CountFor i = WbCount To 1 Step -1If Workbooks(i).Name <> ThisWorkbook.Name ThenWorkbooks(i).CloseEnd IfNext iEnd Sub

Der obige Code zählt die Anzahl der geöffneten Arbeitsmappen und durchläuft dann alle Arbeitsmappen mit der For Each-Schleife.

Mithilfe der IF-Bedingung wird überprüft, ob der Name der Arbeitsmappe mit dem Namen der Arbeitsmappe übereinstimmt, in der der Code ausgeführt wird.

Wenn es sich nicht um eine Übereinstimmung handelt, wird die Arbeitsmappe geschlossen und zur nächsten verschoben.

Beachten Sie, dass wir die Schleife von WbCount bis 1 mit einem Schritt von -1 ausgeführt haben. Dies geschieht, da mit jeder Schleife die Anzahl der geöffneten Arbeitsmappen abnimmt.

Dieses Arbeitsbuch wird im späteren Abschnitt ausführlich behandelt.

ActiveWorkbook verwenden

ActiveWorkbook bezieht sich, wie der Name schon sagt, auf die aktive Arbeitsmappe.

Der folgende Code zeigt Ihnen den Namen der aktiven Arbeitsmappe.

Sub ActiveWorkbookName()MsgBox ActiveWorkbook.NameEnd Sub

Wenn Sie VBA verwenden, um eine andere Arbeitsmappe zu aktivieren, bezieht sich der ActiveWorkbook-Teil in der VBA danach auf die aktivierte Arbeitsmappe.

Hier ein Beispiel.

Wenn Sie eine Arbeitsmappe aktiv haben und den folgenden Code einfügen und ausführen, wird zuerst der Name der Arbeitsmappe mit dem Code und dann der Name der Beispiele angezeigt.xlsx (das durch den Code aktiviert wird).

Sub ActiveWorkbookName()MsgBox ActiveWorkbook.NameWorkbooks("Examples.xlsx").ActivateMsgBox ActiveWorkbook.NameEnd Sub

Beachten Sie, dass beim Erstellen einer neuen Arbeitsmappe mit VBA diese neu erstellte Arbeitsmappe automatisch zur aktiven Arbeitsmappe wird.

Verwenden von ThisWorkbook

ThisWorkbook bezieht sich auf die Arbeitsmappe, in der der Code ausgeführt wird.

Jede Arbeitsmappe hätte ein ThisWorkbook-Objekt als Teil davon (sichtbar im Projekt-Explorer).

‚ThisWorkbook‘ kann reguläre Makros (ähnlich denen, die wir in Modulen hinzufügen) sowie Ereignisprozeduren speichern. Eine Ereignisprozedur wird basierend auf einem Ereignis ausgelöst, z. B. durch Doppelklicken auf eine Zelle, Speichern einer Arbeitsmappe oder Aktivieren eines Arbeitsblatts.

Jede Ereignisprozedur, die Sie in diesem ‚ThisWorkbook‘ speichern, wäre in der gesamten Arbeitsmappe verfügbar, im Vergleich zu den Ereignissen auf Blattebene, die nur auf die spezifischen Blätter beschränkt sind.

Wenn Sie beispielsweise im Projekt-Explorer auf das ThisWorkbook-Objekt doppelklicken und den folgenden Code kopieren und einfügen, wird die Zellenadresse angezeigt, wenn Sie auf eine der Zellen in der gesamten Arbeitsmappe doppelklicken.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)MsgBox Target.AddressEnd Sub

Während die Hauptaufgabe von ThisWorkbook darin besteht, Ereignisprozeduren zu speichern, können Sie damit auch auf die Arbeitsmappe verweisen, in der der Code ausgeführt wird.

Der folgende Code würde den Namen der Arbeitsmappe zurückgeben, in der der Code ausgeführt wird.

Sub ThisWorkbookName()MsgBox ThisWorkbook.NameEnd Sub

Der Vorteil der Verwendung von ThisWorkbook (gegenüber ActiveWorkbook) besteht darin, dass es in allen Fällen auf dieselbe Arbeitsmappe verweist (die den Code enthält). Wenn Sie also einen VBA-Code zum Hinzufügen einer neuen Arbeitsmappe verwenden, ändert sich die ActiveWorkbook , aber ThisWorkbook verweist weiterhin auf die Arbeitsmappe mit dem Code.

Erstellen eines neuen Arbeitsmappenobjekts

Der folgende Code erstellt eine neue Arbeitsmappe.

Sub CreateNewWorkbook()Workbooks.AddEnd Sub

Wenn Sie eine neue Arbeitsmappe hinzufügen, wird diese zur aktiven Arbeitsmappe.

Der folgende Code fügt eine neue Arbeitsmappe hinzu und zeigt Ihnen dann den Namen dieser Arbeitsmappe an (dies wäre der Standardname des Book1-Typs).

Sub CreateNewWorkbook()Workbooks.AddMsgBox ActiveWorkbook.NameEnd Sub

Öffnen einer Arbeitsmappe mit VBA

Sie können VBA verwenden, um eine bestimmte Arbeitsmappe zu öffnen, wenn Sie den Dateipfad der Arbeitsmappe kennen.

Der folgende Code öffnet die Arbeitsmappe – Beispiele.xlsx, das sich im Ordner Dokumente auf meinem System befindet.

Sub OpenWorkbook()Workbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx")End Sub

Falls die Datei im Standardordner vorhanden ist, in dem VBA standardmäßig neue Dateien speichert, können Sie einfach den Namen der Arbeitsmappe angeben – ohne den gesamten Pfad.

Sub OpenWorkbook()Workbooks.Open ("Examples.xlsx")End Sub

Falls die Arbeitsmappe, die Sie öffnen möchten, nicht vorhanden ist, wird eine Fehlermeldung angezeigt.

Um diesen Fehler zu vermeiden, können Sie Ihrem Code einige Zeilen hinzufügen, um zuerst zu überprüfen, ob die Datei vorhanden ist oder nicht, und wenn sie vorhanden ist, versuchen Sie sie zu öffnen.

Der folgende Code würde den Speicherort der Datei überprüfen und wenn er nicht vorhanden ist, wird eine benutzerdefinierte Meldung angezeigt (nicht die Fehlermeldung):

Sub OpenWorkbook()If Dir("C:\Users\sumit\Documents\Examples.xlsx") <> "" ThenWorkbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx")ElseMsgBox "The file doesn't exist"End IfEnd Sub

Sie können auch das Dialogfeld Öffnen verwenden, um die Datei auszuwählen, die Sie öffnen möchten.

Sub OpenWorkbook()On Error Resume NextDim FilePath As StringFilePath = Application.GetOpenFilenameWorkbooks.Open (FilePath)End Sub

Der obige Code öffnet das Dialogfeld Öffnen. Wenn Sie eine Datei auswählen, die Sie öffnen möchten, wird der Dateipfad der Variablen filePath zugewiesen. Arbeitsmappe.Öffnen verwendet dann den Dateipfad, um die Datei zu öffnen.

Falls der Benutzer eine Datei nicht öffnet und auf die Schaltfläche Abbrechen klickt, wird filePath False . Um in diesem Fall einen Fehler zu vermeiden, haben wir die Anweisung ‚On Error Resume Next‘ verwendet.

Speichern einer Arbeitsmappe

Verwenden Sie den folgenden Code, um die aktive Arbeitsmappe zu speichern:

Sub SaveWorkbook()ActiveWorkbook.SaveEnd Sub

Dieser Code funktioniert für die Arbeitsmappen, die bereits zuvor gespeichert wurden. Da die Arbeitsmappe das obige Makro enthält, wenn es nicht als gespeichert wurde.xlsm (oder .xls) Datei, verlieren Sie das Makro, wenn Sie es als nächstes öffnen.

Wenn Sie die Arbeitsmappe zum ersten Mal speichern, wird eine Eingabeaufforderung wie unten gezeigt angezeigt:

Beim ersten Speichern ist es besser, die Option „Saveas“ zu verwenden.

Der folgende Code würde die aktive Arbeitsmappe als speichern.xlsm-Datei am Standardspeicherort (dies ist der Dokumentordner in meinem System).

Sub SaveWorkbook()ActiveWorkbook.SaveAs Filename:="Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

Wenn Sie möchten, dass die Datei an einem bestimmten Ort gespeichert wird, müssen Sie dies im Dateinamenwert erwähnen. Der folgende Code speichert die Datei auf meinem Desktop.

Sub SaveWorkbook()ActiveWorkbook.SaveAs Filename:="C:\Users\sumit\Desktop\Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

Wenn Sie möchten, dass der Benutzer die Option erhält, den Speicherort für die Datei auszuwählen, können Sie das Dialogfeld Saveas aufrufen. Der folgende Code zeigt das Dialogfeld Saveas und ermöglicht es dem Benutzer, den Speicherort auszuwählen, an dem die Datei gespeichert werden soll.

Sub SaveWorkbook()Dim FilePath As StringFilePath = Application.GetSaveAsFilenameActiveWorkbook.SaveAs Filename:=FilePath & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

Beachten Sie, dass Sie anstelle von FileFormat:=xlOpenXMLWorkbookMacroEnabled auch FileFormat:=52 verwenden können, wobei 52 der Code xlOpenXMLWorkbookMacroEnabled ist.

Speichern aller geöffneten Arbeitsmappen

Wenn Sie mehr als eine Arbeitsmappe geöffnet haben und alle Arbeitsmappen speichern möchten, können Sie den folgenden Code verwenden:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In Workbookswb.SaveNext wbEnd Sub

Das obige speichert alle Arbeitsmappen, einschließlich derjenigen, die noch nie gespeichert wurden. Die Arbeitsmappen, die zuvor nicht gespeichert wurden, werden am Standardspeicherort gespeichert.

Wenn Sie nur die zuvor gespeicherten Arbeitsmappen speichern möchten, können Sie den folgenden Code verwenden:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Path <> "" Thenwb.SaveEnd IfNext wbEnd Sub

Speichern und Schließen aller Arbeitsmappen

Wenn Sie alle Arbeitsmappen mit Ausnahme der Arbeitsmappe mit dem aktuellen Code schließen möchten, können Sie den folgenden Code verwenden:

Sub CloseandSaveWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Name <> ThisWorkbook.Name Thenwb.Close SaveChanges:=TrueEnd IfNext wbEnd Sub

Der obige Code würde alle Arbeitsmappen schließen (mit Ausnahme der Arbeitsmappe mit dem Code – ThisWorkbook). Falls es Änderungen in diesen Arbeitsmappen gibt, werden die Änderungen gespeichert. Falls eine Arbeitsmappe noch nie gespeichert wurde, wird das Dialogfeld Speichern unter angezeigt.

Speichern einer Kopie der Arbeitsmappe (mit Zeitstempel)

Wenn ich mit komplexen Daten und Daten in Excel-Arbeitsmappen arbeite, erstelle ich häufig verschiedene Versionen meiner Arbeitsmappen. Dies ist hilfreich, falls mit meiner aktuellen Arbeitsmappe etwas schief geht. Ich würde zumindest eine Kopie davon unter einem anderen Namen speichern lassen (und ich würde nur die Arbeit verlieren, die ich nach dem Erstellen einer Kopie geleistet habe).

Hier ist der VBA-Code, der eine Kopie Ihrer Arbeitsmappe erstellt und am angegebenen Speicherort speichert.

Sub CreateaCopyofWorkbook()ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy.xlsm"End Sub

Der obige Code würde jedes Mal, wenn Sie dieses Makro ausführen, eine Kopie Ihrer Arbeitsmappe speichern.

Während dies großartig funktioniert, würde ich mich wohler fühlen, wenn ich verschiedene Kopien speichern würde, wenn ich diesen Code ausführe. Der Grund, warum dies wichtig ist, ist, dass wenn ich einen versehentlichen Fehler mache und dieses Makro ausführe, die Arbeit mit den Fehlern gespeichert wird. Und ich hätte keinen Zugang zu der Arbeit, bevor ich den Fehler gemacht habe.

Um solche Situationen zu bewältigen, können Sie den folgenden Code verwenden, der bei jedem Speichern eine neue Kopie der Arbeit speichert. Außerdem werden Datum und Zeitstempel als Teil des Arbeitsmappennamens hinzugefügt. Dies kann Ihnen helfen, jeden Fehler zu verfolgen, den Sie gemacht haben, da Sie nie eines der zuvor erstellten Backups verlieren.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy" & Format(Now(), "dd-mm-yy-hh-mm-ss-AMPM") & ".xlsm"End Sub

Der obige Code würde jedes Mal, wenn Sie dieses Makro ausführen, eine Kopie erstellen und dem Arbeitsmappennamen einen Datums- / Zeitstempel hinzufügen.

Erstellen einer neuen Arbeitsmappe für jedes Arbeitsblatt

In einigen Fällen haben Sie möglicherweise eine Arbeitsmappe mit mehreren Arbeitsblättern, und Sie möchten für jedes Arbeitsblatt eine Arbeitsmappe erstellen.

Dies kann der Fall sein, wenn Sie monatliche /vierteljährliche Berichte in einer einzigen Arbeitsmappe haben und diese für jedes Arbeitsblatt in eine Arbeitsmappe aufteilen möchten.

Oder, wenn Sie abteilungsweise Berichte haben und diese in einzelne Arbeitsmappen aufteilen möchten, damit Sie diese einzelnen Arbeitsmappen an die Abteilungsleiter senden können.

Hier ist der Code, der für jedes Arbeitsblatt eine Arbeitsmappe erstellt, ihr den gleichen Namen wie dem des Arbeitsblatts gibt und sie im angegebenen Ordner speichert.

Sub CreateWorkbookforWorksheets()Dim ws As WorksheetDim wb As WorkbookFor Each ws In ThisWorkbook.WorksheetsSet wb = Workbooks.Addws.Copy Before:=wb.Sheets(1)Application.DisplayAlerts = Falsewb.Sheets(2).DeleteApplication.DisplayAlerts = Truewb.SaveAs "C:\Users\sumit\Desktop\Test\" & ws.Name & ".xlsx"wb.CloseNext wsEnd Sub

Im obigen Code haben wir zwei Variablen ‚ws‘ und ‚wb‘ verwendet.

Der Code durchläuft jedes Arbeitsblatt (unter Verwendung der For Each Next-Schleife) und erstellt eine Arbeitsmappe dafür. Außerdem wird die Kopiermethode des Arbeitsblattobjekts verwendet, um eine Kopie des Arbeitsblatts in der neuen Arbeitsmappe zu erstellen.

Beachten Sie, dass ich die SET-Anweisung verwendet habe, um die Variable ‚wb‘ jeder neuen Arbeitsmappe zuzuweisen, die vom Code erstellt wird.

Mit dieser Technik können Sie einer Variablen ein Arbeitsmappenobjekt zuweisen. Dies wird im nächsten Abschnitt behandelt.

Arbeitsmappenobjekt einer Variablen zuweisen

In VBA können Sie einer Variablen ein Objekt zuweisen und dann die Variable verwenden, um auf dieses Objekt zu verweisen.

Im folgenden Code verwende ich beispielsweise VBA, um eine neue Arbeitsmappe hinzuzufügen und diese Arbeitsmappe dann der Variablen wb zuzuweisen. Dazu muss ich die SET-Anweisung verwenden.

Sobald ich die Arbeitsmappe der Variablen zugewiesen habe, werden alle Eigenschaften der Arbeitsmappe auch der Variablen zur Verfügung gestellt.

Sub AssigntoVariable()Dim wb As WorkbookSet wb = Workbooks.Addwb.SaveAs Filename:="C:\Users\sumit\Desktop\Examples.xlsx"End Sub

Beachten Sie, dass der erste Schritt im Code darin besteht, „wb“ als Variable vom Typ Arbeitsmappe zu deklarieren. Dies teilt VBA mit, dass diese Variable das Arbeitsmappenobjekt enthalten kann.

Die nächste Anweisung verwendet SET , um die Variable der neuen Arbeitsmappe zuzuweisen, die wir hinzufügen. Sobald diese Zuweisung abgeschlossen ist, können wir die Variable wb verwenden, um die Arbeitsmappe zu speichern (oder etwas anderes damit zu tun).

Looping durch offene Arbeitsmappen

Wir haben bereits ein paar Beispiele Codes oben gesehen, die Schleife im Code verwendet.

In diesem Abschnitt erkläre ich verschiedene Möglichkeiten, offene Arbeitsmappen mit VBA zu durchlaufen.Angenommen, Sie möchten alle geöffneten Arbeitsmappen mit Ausnahme der mit dem darin enthaltenen Code speichern und schließen, dann können Sie den folgenden Code verwenden:

Sub CloseandSaveWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Name <> ThisWorkbook.Name Thenwb.Close SaveChanges:=TrueEnd IfNext wbEnd Sub

Der obige Code verwendet die For Each-Schleife, um jede Arbeitsmappe in der Arbeitsmappensammlung zu durchlaufen. Dazu müssen wir zuerst ‚wb‘ als Variable vom Typ Arbeitsmappe deklarieren.

In jedem Schleifenzyklus wird jeder Arbeitsmappenname analysiert, und wenn er nicht mit dem Namen der Arbeitsmappe übereinstimmt, die den Code enthält, wird er nach dem Speichern seines Inhalts geschlossen.

Das gleiche kann auch mit einer anderen Schleife erreicht werden, wie unten gezeigt:

Sub CloseWorkbooks()Dim WbCount As IntegerWbCount = Workbooks.CountFor i = WbCount To 1 Step -1If Workbooks(i).Name <> ThisWorkbook.Name ThenWorkbooks(i).Close SaveChanges:=TrueEnd IfNext iEnd Sub

Der obige Code verwendet die For Next-Schleife, um alle Arbeitsmappen mit Ausnahme derjenigen zu schließen, in der sich der Code befindet. In diesem Fall müssen wir keine Arbeitsmappenvariable deklarieren, sondern die Gesamtzahl der geöffneten Arbeitsmappen zählen. Wenn wir die Anzahl haben, verwenden wir die For Next Schleife, um jede Arbeitsmappe zu durchlaufen. Außerdem verwenden wir die Indexnummer, um in diesem Fall auf die Arbeitsmappen zu verweisen.

Beachten Sie, dass wir im obigen Code mit Schritt -1 von WbCount zu 1 schleifen. Dies ist erforderlich, da bei jeder Schleife die Arbeitsmappe geschlossen und die Anzahl der Arbeitsmappen um 1 verringert wird.

Fehler beim Arbeiten mit dem Arbeitsmappenobjekt (Laufzeitfehler ‚9‘)

Einer der häufigsten Fehler beim Arbeiten mit Arbeitsmappen ist – Laufzeitfehler ‚9‘ – Index außerhalb des Bereichs.

Im Allgemeinen sind VBA-Fehler nicht sehr informativ und überlassen es Ihnen oft, herauszufinden, was schief gelaufen ist.

Hier sind einige der möglichen Gründe, die zu diesem Fehler führen können:

• Die Arbeitsmappe, auf die Sie zugreifen möchten, ist nicht vorhanden. Wenn ich beispielsweise versuche, mit Workbooks (5) auf die fünfte Arbeitsmappe zuzugreifen, und nur 4 Arbeitsmappen geöffnet sind, wird dieser Fehler angezeigt.
• Wenn Sie einen falschen Namen verwenden, um auf die Arbeitsmappe zu verweisen. Zum Beispiel, wenn der Name Ihrer Arbeitsmappe Beispiele ist.xlsx und Sie verwenden Beispiel.xlsx. dann zeigt es Ihnen diesen Fehler.
• Wenn Sie keine Arbeitsmappe gespeichert haben und die Erweiterung verwenden, wird dieser Fehler angezeigt. Beispiel: Wenn der Name Ihrer Arbeitsmappe Book1 lautet und Sie den Namen Book1 verwenden.xlsx ohne es zu speichern, erhalten Sie diesen Fehler.
• Die Arbeitsmappe, auf die Sie zugreifen möchten, ist geschlossen.

Abrufen einer Liste aller geöffneten Arbeitsmappen

Wenn Sie eine Liste aller geöffneten Arbeitsmappen in der aktuellen Arbeitsmappe (der Arbeitsmappe, in der Sie den Code ausführen) erhalten möchten, können Sie den folgenden Code verwenden:

Sub GetWorkbookNames()Dim wbcount As Integerwbcount = Workbooks.CountThisWorkbook.Worksheets.AddActiveSheet.Range("A1").ActivateFor i = 1 To wbcountRange("A1").Offset(i - 1, 0).Value = Workbooks(i).NameNext iEnd Sub

Der obige Code fügt ein neues Arbeitsblatt hinzu und listet dann den Namen aller geöffneten Arbeitsmappen auf.

Wenn Sie auch den Dateipfad erhalten möchten, können Sie den folgenden Code verwenden:

Sub GetWorkbookNames()Dim wbcount As Integerwbcount = Workbooks.CountThisWorkbook.Worksheets.AddActiveSheet.Range("A1").ActivateFor i = 1 To wbcountRange("A1").Offset(i - 1, 0).Value = Workbooks(i).Path & "\" & Workbooks(i).NameNext iEnd Sub

Öffnen Sie die angegebene Arbeitsmappe durch Doppelklick auf die Zelle

Wenn Sie eine Liste von Dateipfaden für Excel-Arbeitsmappen haben, können Sie den folgenden Code verwenden, um einfach auf die Zelle mit dem Dateipfad zu doppelklicken, und diese Arbeitsmappe wird geöffnet.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)Workbooks.Open Target.ValueEnd Sub

Dieser Code wird im ThisWorkbook-Codefenster platziert.

Dazu:

• Doppelklicken Sie im Projekt-Explorer auf das ThisWorkbook-Objekt. Beachten Sie, dass sich das ThisWorkbook-Objekt in der Arbeitsmappe befinden sollte, in der Sie diese Funktionalität wünschen.
• Kopieren Sie den obigen Code und fügen Sie ihn ein.

Wenn Sie nun den genauen Pfad der Dateien haben, die Sie öffnen möchten, können Sie dies tun, indem Sie einfach auf den Dateipfad doppelklicken, und VBA öffnet diese Arbeitsmappe sofort.

Wohin mit dem VBA-Code

Sie fragen sich, wohin der VBA-Code in Ihrer Excel-Arbeitsmappe geht?

Excel hat ein VBA-Backend namens VBA Editor. Sie müssen den Code kopieren und in das Codefenster des VB Editor-Moduls einfügen.

Hier sind die Schritte, um dies zu tun:

1. Gehen Sie zur Registerkarte Entwickler.
2. Klicken Sie auf die Visual Basic-Option. Dadurch wird der VB-Editor im Backend geöffnet.
3. Klicken Sie im Projekt-Explorer-Bereich des VB-Editors mit der rechten Maustaste auf ein beliebiges Objekt für die Arbeitsmappe, in die Sie den Code einfügen möchten. Wenn Sie den Projekt-Explorer nicht sehen, gehen Sie zur Registerkarte Ansicht und klicken Sie auf Projekt-Explorer.
4. Gehen Sie zu Einfügen und klicken Sie auf Modul. Dadurch wird ein Modulobjekt für Ihre Arbeitsmappe eingefügt.
5. Kopieren Sie den Code und fügen Sie ihn in das Modulfenster ein.

Sie können auch die folgenden Excel VBA-Tutorials mögen:

• So zeichnen Sie ein Makro in Excel auf.
• Erstellen einer benutzerdefinierten Funktion in Excel.
• So erstellen und verwenden Sie Add-Ins in Excel.
• So setzen Sie Makros fort, indem Sie sie in die persönliche Makro-Arbeitsmappe einfügen.
• Holen Sie sich die Liste der Dateinamen aus einem Ordner in Excel (mit und ohne VBA).
• Verwendung der Excel VBA InStr-Funktion (mit praktischen BEISPIELEN).
• So sortieren Sie Daten in Excel mit VBA (Eine Schritt-für-Schritt-Anleitung).