Einführung:

Fehler wie #N/A, #NAME? und #VALUE! sind häufige Probleme, auf die Excel-Benutzer stoßen, wenn sie mit großen Datensätzen oder komplexen Formeln arbeiten. Das manuelle Überprüfen jeder Zelle in mehreren Excel-Dateien auf Fehler, kann zeitaufwändig sein. In diesem Artikel erfahren Sie, wie Sie den Prozess automatisieren können, indem Sie ein Makro erstellen, das auf mehreren Excel-Dateien ausgeführt wird, diese Fehler identifiziert und einen umfassenden Fehlerbericht bereitstellt.

Warum brauchen wir das?

Ein Makro zum Suchen von Fehlern wie #N/A, #NAME? und #VALUE in Excel kann Ihnen helfen, Fehler in Ihren Daten schnell zu identifizieren und zu beheben. Diese Fehler können beim Importieren von Daten, bei der Verwendung von Formeln oder bei der Verarbeitung von Daten auftreten. Durch die Verwendung eines Makros können Sie den Prozess automatisieren und Zeit sparen, indem Sie potenzielle Fehler effizient aufspüren und korrigieren. Optimieren Sie Ihre Arbeitsblätter und stellen Sie sicher, dass Ihre Daten korrekt und fehlerfrei sind.

Voraussetzungen:

Um diesem Tutorial folgen zu können, benötigen Sie grundlegende Kenntnisse in Excel und VBA (Visual Basic for Applications). Stellen Sie sicher, dass auf Ihrem Computer eine aktuelle Version von Microsoft Excel installiert ist.

Schritt 1: Bereiten Sie die makrofähige Arbeitsmappe vor

Erstellen Sie eine neue Excel-Arbeitsmappe und speichern Sie sie als makrofähige Arbeitsmappe (.xlsm), um die Ausführung von VBA-Code zu ermöglichen.

Schritt 2: Greifen Sie auf den Visual Basic-Editor zu

Drücken Sie „Alt+F11“, um den Visual Basic Editor (VBE) zu öffnen.

Klicken Sie im VBE-Fenster im Menü auf „Einfügen“ und wählen Sie „Modul“, um ein neues Modul zu erstellen.

Schritt 3: Schreiben Sie den VBA-Code

Fügen Sie im Modul den folgenden VBA-Code ein:

Sub CheckErrorsInFiles()

    Dim wb As Workbook

    Dim ws As Worksheet

    Dim filePath As String, filename As String

   

    Dim err_inFormulas As String, err_inConstants As String

    Dim arr_inFormulas() As String, arr_inConstants() As String

    Dim iter_inFormulas As Long, iter_inConstants As Long

    Dim isNull_inFormulas As Boolean, isNull_inConstants As Boolean

   

    \' Set the folder path where the Excel files are located

    filePath = "C:\\Users\\Cristin\\Desktop\\New folder\\" \' Update with your desired folder path

    \' Loop through each file in the folder

    filename = Dir(filePath)

   

    While filename <> ""

        \' Open the file

        Set wb = Workbooks.Open(filePath & filename)

        \' Loop through each worksheet in the workbook

        For Each ws In wb.Worksheets

            \'instantiate variables

            err_inFormulas = ""

            err_inConstants = ""

            isNull_inFormulas = False

            isNull_inConstants = False

            \'capture all the error cells (both in Formulas and Constants)

            On Error Resume Next

            err_inFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas, 16).Address

            err_inConstants = ws.Cells.SpecialCells(xlCellTypeConstants, 16).Address

            On Error GoTo 0

            \'checks if variable \'err_inFormulas\' is not empty

            \'if True, then store in array variable, otherwise, skip the process

            If Len(err_inFormulas) > 0 Then

                If InStr(err_inFormulas, ",") > 0 Then

                    arr_inFormulas = Split(err_inFormulas, ",")

                Else

                    arr_inFormulas(0) = err_inFormulas

                End If

            Else

                isNull_inFormulas = True

            End If

            \'checks if variable \'err_inConstants\' is not empty

            \'if True, then store in array variable, otherwise, skip the process

            If Len(err_inConstants) > 0 Then

                If InStr(err_inConstants, ",") > 0 Then

                    arr_inConstants = Split(err_inConstants, ",")

                Else

                    arr_inConstants(0) = err_inConstants

                End If

            Else

                isNull_inConstants = True

            End If

            \'checks if variable \'isNull_inFormulas\' is False (meaning its not empty)

            \'if True, then display the cell address, otherwise, skip the process

            If isNull_inFormulas = False Then

                For iter_inFormulas = LBound(arr_inFormulas) To UBound(arr_inFormulas)

                    Debug.Print "File: " & filename & " | Worksheet: " & ws.Name & " | Cell: " & arr_inFormulas(iter_inFormulas)

                Next iter_inFormulas

            End If

            \'checks if variable \'isNull_inConstants\' is False (meaning its not empty)

            \'if True, then display the cell address, otherwise, skip the process

            If isNull_inConstants = False Then

                For iter_inConstants = LBound(arr_inConstants) To UBound(arr_inConstants)

                    Debug.Print "File: " & filename & " | Worksheet: " & ws.Name & " | Cell: " & arr_inFormulas(iter_inConstants)

                Next iter_inConstants

            End If

        Next ws

        \' Close the workbook without saving changes

        wb.Close SaveChanges:=False

        \' Move to the next file

        filename = Dir()

    Wend

End Sub

Schritt 4: Passen Sie den Code an

Passen Sie die Variable filePath an den Ordnerpfad an, in dem sich Ihre Excel-Dateien befinden.

Passen Sie die Aktion an, die Sie ausführen möchten, wenn ein Fehler gefunden wird. Im bereitgestellten Beispiel zeigt der Code den Dateinamen, den Arbeitsblattnamen und die Zellenadresse im Direktfenster an. Sie können diesen Abschnitt ändern, um die Fehlerdetails in einem separaten Arbeitsblatt oder einer Protokolldatei zu speichern.

Schritt 5: Führen Sie das Makro aus

Drücken Sie „F5“ oder klicken Sie auf die Schaltfläche „Ausführen“ in der VBE-Symbolleiste, um das Makro auszuführen.

Das Makro durchläuft jede Excel-Datei im angegebenen Ordner und prüft auf #N/A, #NAME? und #VALUE! Fehler

Ausgabe im Direktfenster

Benötigen Sie einen VBA Programmierer?

Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).

Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular.