CRC Checksummen für bestimmte Zellen

Forumthread: CRC Checksummen für bestimmte Zellen

17.10.2007 08:15:15

Thorsten

Hallo,
ich möchte folgendes Problem lösen:
Eine Excel-Tabelle enthält Zellen mit
Formeln
Texte
Eingabefeldern
Die Formeln und Texte sollen geschützt sein, sodass der Benutzer nur die Eingabefelder ändern kann. Nun möchte ich Prüfen, ob der Benutzer nicht doch das Passwort gegnackt und Formeln oder Texte geändert hat. Ansatz:
Ich möchte für die gesperrten Formel und Textfelder eine CRC Checksumme berechenen. Ich kann dies nicht für die ganze Datei machen, da durch Änderung der Eingabefelder die CRC Datei-Checksumme nahezu zwangsläufig geändert wird.
Die Checksumme soll also einmal beim erstellen des Tabellenblattes errechnet werden und dann in einer gesperrten Zelle hinterlegt sein. Beim Speichern oder als Auto_close kann dann ein Makro diese Checksumme erneut berechnen und dem Anwender melden, ob es eine Veränderung innerhalb der Formeln bzw. Texte gegeben hat. Hinzu kommt, dass dieser Mechanismus auch von wenig versierten EXCEL-Usern verwendert werden soll.
Hierzu benötige ich Infos zu folgenden Arbeitspunkten:
Damit das ganze einfach bleibt, benötige ich eine Idee die Bezüge von ausgewählten Zellen in ein Makro zu übergeben, .selection funktioniert also nicht. Hintergrund: Ich markiere nach dem Erstellen das gesammte Tabellenblatt und sperre alle Zellen. Anschlissend wähle ich die Eingabezellen aus, hebe die Sperre auf und schütze das Tabellenblatt. Vor dem Schützen könnte ich die Zellen auswählen die zu schützen sind bzw. Formeln und Texte enthalten. Von diesen soll die CRC Summe berechnet werden - aber dies könnten je nach Tabellenaufbau immer andere Zellen sein (es soll universell bleiben)
Das Makro sollte einfach und schnell sein - es muss nicht unbedingt ein abgleich mit einer CRC Prüftabelle erfolgen. Möglich wäre z.B. auch über Primzahlen ähnlich der Prüfziffern von Ausweisen oder Banknoten zu arbeiten.
Vielen Dank für Tipps, Ideen und Codes im voraus
Thorsten

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: CRC Checksummen für bestimmte Zellen

17.10.2007 16:26:00

Wolli

Hallo Thorsten, darf ich mal ganz blöd fragen: Wenn ein Nutzer das Passwort geknackt hat und eine geschützte Zelle verändert, bekommt er sicherlich mit, dass die Prüfung danach Alarm schlägt. Nun muss er nur die neue Prüfsumme in das Vergleichsfeld eintragen (das kann er, schließlich ist das Passwort ja geknackt) und schon hat er seine Spuren verwischt. Mit dieser Methode wärest Du nicht wirklich vertrauenswürdig. Innerhalb von Excel dürfte es schwierig sein, eine in sich schlüssige, nachprüfbare Lösung zu bauen, die nicht von einem gewitzten Passwortknacker übertölpelt werden kann. Vielleicht reicht es ja doch aus, einfach ein gutes Passwort zu wählen und anzunehmen, dass es nicht geknackt wird :-) Gruß, Wolli

AW: CRC Checksummen für bestimmte Zellen

17.10.2007 21:46:00

Thorsten

Hallo Wolli,
richtig, es sei denn, die Checksumme steht auf einem anderem "Blatt" und zwar in einem schreibgeschützen Verzeichnis auf einem Server, auf den nur vertrauenswürdige Personen schreibzugriff haben.
Hintergrund: Es geht um die Validierung / Verifizierung von Excel-Berechungen unter GMP. Hier gibt es faktisch zwei Varianten: Unter GMP muss eine Berechnung von einer zweiten Person unabhängig verifiziert werden, d.h. die Berechnung wird z.B. mit Taschenrechner nachge-x-t. Anschliessend unterschreibt die zweite Person, dass Sie die Berechnung mittels Taschenrechner nachvollzogen hat und mit dem selben Datensatz um selben Ergebnis kommt ...
Alternativ kann eine validierte Software (Excel-Sheet) verwendet werden. Eine Validierung ist der Nachweis, dass das Sheet zur Berechnung geeignet ist, also grundsätzlich ein Ergebnis liefert, welches auch auf anderem Wege nachvollziehbar ist. Es ist sozusagen eine einmalige Verifizierung mit einem "Standarddatensatz", der ein vorher bekanntes Ergebnis ergeben muss. Nach erfolgter Validierung wäre das Sheet so zu schüzten, dass diese Validierung fortan gültig ist. Das ist aber wie Du selbst geschrieben hast kaum möglich. (Unabhängig davon geht es auch bei GMP darum, Fehler zu vermeiden. Krimineller vorsatz ist kaum auszuschliessen).
Ich habe folgende Idee, die einen Mittelweg darstellt. Ich weiss aber noch nicht, ob es realisierbar ist:
Ein Ersteller schreibt eine SOP samt zugehörigem Excel-Sheet zur Auswertung. Die Formeln werden genau einmal geprüft, anschliessend wird die Checksumme z.B. in einer zweiten Excel-Tabelle im geschützen Verzeichnis hinterlegt. Die eigentliche Auswertetabelle kann nun darauf zugreifen. Alternativ kann die Checksumme auch direkt in der SOP hinterlegt sein. Zur Verifizierung muss der zweite Prüfer nun nicht mehr durch Unterschrift bestätigen, dass er alle Berechnungen nachvollzogen hat, sondern nur, dass die Eingabedaten korrekt sind und die Checksumme mit der hinterlegten übereinstimmt. Fertig ist die Verifizierung. Die Prüfung via Abgleich mit der geschützen Tabelle dient dabei nur als Hilfestellung.
OK?
Gruss
Thorsten

AW: CRC Checksummen für bestimmte Zellen

18.10.2007 09:56:44

Wolli

Moin Thorsten,
a) was ist GMP und SOP?
b) wäre es nicht möglich, die verifizierte Berechnung in eine Tabelle zu schreiben, diese ALS DATEI zu schützen und die gesamte Datei z.B. mit einem MD5-Hashwert zu versehen? Dann kann derjenige, der damit arbeiten will, zunächst prüfen, ob die Datei unangetastet ist (MD5 erneut erzeugen und vergleichen), anschließend öffnet er sie und arbeitet darin.
Evtl. kann man von vornherein ein paar Makros einbauen, die mit 1-2 Klicks die passenden Eingabezellen freischalten. Variable Inhalte, die geändert werden dürfen, die jedoch nicht ergebnisrelevant sind, könnte man ja in eine zweite Tabelle auslagern, die nicht verifiziert werden muss.
Gruß, Wolli

AW: CRC Checksummen für bestimmte Zellen

18.10.2007 12:17:00

Wolli

Hallo nochmal, ich habe mal eine Funktion gebastelt, die eine Prüfsumme für eine Formel ermittelt (Siehe VBA-Editor Modul 1).
https://www.herber.de/bbs/user/46872.xls
Vielleicht kannst Du damit tatsächlich schon arbeiten. Schlauere Leute als ich (oder solche mit mehr Zeit) können diese Funktion sicherlich so umbauen, dass sie statt einer zusammengeschusterten Quersumme einen ernsthaften Hashwert zurückgibt.
Gruß, Wolli

Infobox / Tutorial

CRC Checksummen für bestimmte Zellen in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um eine CRC-Prüfzelle in Excel zu implementieren, kannst Du folgende Schritte befolgen:

Zellen vorbereiten: Wähle die Zellen aus, deren Inhalte Du schützen möchtest (Formeln und Texte).
Blatt schützen: Sperre das gesamte Blatt und hebe die Sperre für die Eingabefelder auf.
VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Neues Modul erstellen: Klicke mit der rechten Maustaste auf "VBAProject (DeinWorkbookName)" und wähle "Einfügen" > "Modul".

CRC-Funktion hinzufügen: Füge folgenden Code in das Modul ein, um eine CRC32-Prüfzelle zu berechnen:

Function CRC32(s As String) As Long
    Dim i As Long, j As Long, crc As Long
    Dim table(0 To 255) As Long

    For i = 0 To 255
        crc = i
        For j = 0 To 7
            If (crc And 1) Then
                crc = (crc \ 2) Xor &HEDB88320
            Else
                crc = crc \ 2
            End If
        Next j
        table(i) = crc
    Next i

    crc = &HFFFFFFFF
    For i = 1 To Len(s)
        crc = (crc \ 256) Xor table((crc And &HFF) Xor Asc(Mid(s, i, 1)))
    Next i
    CRC32 = crc Xor &HFFFFFFFF
End Function

Checksumme berechnen: Erstelle eine Zelle, in der Du die CRC-Prüfzelle berechnest. Zum Beispiel: =CRC32(A1 & B1 & C1) (ersetze A1, B1, C1 durch die Zellen, die Du prüfen möchtest).
Makro zum Überprüfen der Checksumme: Schreibe ein Makro, das bei jedem Speichern die CRC-Prüfzelle erneut berechnet und überprüft, ob sie mit der gespeicherten übereinstimmt.

Häufige Fehler und Lösungen

Fehler: CRC berechnet nicht korrekt
Lösung: Stelle sicher, dass die Eingabewerte korrekt sind und keine Leerzeichen oder unerwarteten Zeichen enthalten.
Fehler: VBA funktioniert nicht
Lösung: Überprüfe die Makrosicherheitseinstellungen in Excel. Gehe dazu auf Datei > Optionen > Trust Center > Einstellungen für das Trust Center > Makroeinstellungen und aktiviere "Alle Makros aktivieren".

Alternative Methoden

Wenn Du eine einfachere Lösung suchst, kannst Du auch die MD5-Hash-Funktion verwenden. Hier ist ein Beispiel für eine MD5-Funktion in VBA:

Function MD5Hash(str As String) As String
    Dim enc As Object
    Dim bytes() As Byte
    Dim hash() As Byte
    Dim i As Integer

    Set enc = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    bytes = StrConv(str, vbFromUnicode)
    hash = enc.ComputeHash_2((bytes))

    For i = LBound(hash) To UBound(hash)
        MD5Hash = MD5Hash & Right("0" & Hex(hash(i)), 2)
    Next i
End Function

Diese Funktion kann ebenfalls verwendet werden, um die Prüfzellen zu validieren, indem Du =MD5Hash(A1 & B1 & C1) verwendest.

Praktische Beispiele

CRC-Checksumme für ein Formular: Angenommen, Du hast ein Formular mit den Zellen A1, B1, und C1. Verwende =CRC32(A1 & B1 & C1) in D1, um die Prüfzelle zu berechnen.
MD5-Hash für eine Texteingabe: Mit =MD5Hash(E1) kannst Du den MD5-Hash für den Text in E1 berechnen, um die Integrität zu überprüfen.

Tipps für Profis

Sichere Speicherung: Speichere die CRC- oder MD5-Hashes in einem geschützten Verzeichnis, um Manipulationen zu vermeiden.
Regelmäßige Überprüfung: Implementiere ein regelmäßiges Makro, das die Prüfzellen überprüft und bei Abweichungen eine Warnung ausgibt.
Versionskontrolle: Halte verschiedene Versionen Deiner Arbeitsmappe, um Änderungen nachvollziehen zu können.

FAQ: Häufige Fragen

1. Wie kann ich die CRC-Prüfzelle in einer geschützten Datei speichern?
Du kannst die CRC-Prüfzelle in einer separaten, geschützten Excel-Datei speichern und den Zugriff darauf regulieren.

2. Ist die CRC-Berechnung in allen Excel-Versionen verfügbar?
Ja, die vorgestellten VBA-Funktionen sollten in allen modernen Excel-Versionen (ab Excel 2007) funktionieren, solange Makros aktiviert sind.

3. Wie kann ich die MD5-Hash-Funktion verwenden?
Die MD5-Hash-Funktion kann ähnlich wie die CRC32-Funktion aufgerufen werden, indem Du die Eingabewerte an die Funktion übergibst.

Beliebteste Forumthreads (12 Monate)

CRC Checksummen für bestimmte Zellen

Forumthread: CRC Checksummen für bestimmte Zellen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

CRC Checksummen für bestimmte Zellen in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread