Eine einfache Kreuztabelle in eine Datenbanktabelle umwandeln

 

Dieses kleine Programm verwandelt eine einfache Kreuztabelle in eine Datenbanktabelle, also eine Tabelle, die in Datenbanksprachen wie SQL verwendet werden kann. Mit „einfach“ ist gemeint, dass die Kreuztabelle keine geschachtelten Zeilen- oder Spaltenbeschriftungen haben darf.  Die folgende Tabelle ist eine einfache Kreuztabelle in diesem Sinne:

 

 

Die daraus gewonnene Datenbanktabelle zeigt das folgende Bild. Diese Tabelle hat immer drei Spalten. Jede Zelle im Inneren der Kreuztabelle wird zu einer Zeile in der Datenbanktabelle. Die Spaltenüberschriften (Attribute) der Datenbanktabelle sind zusätzliche Informationen, die aus der Kreuztabelle nicht hervor gehen.  Der Benutzer des Programms muss sie eingeben.

 

Die Frage ist, ob Zellen im Inneren der Kreuztabelle, in denen nichts oder 0 steht, auch in Zeilen der Datenbanktabelle umgewandelt werden sollen.In der dritten Spalte dieser Zeilen würde dann der Wert 0 stehen.  In der vorliegenden Lösung werden Nullen und Leerzellen nicht in die Datenbanktabelle übernommen, aber es  bedürfte nur einer geringen Änderung, sie zu berücksichtigen.

 

 

 

Aufruf und Benutzung des Programms

 

Auf dem Arbeitsblatt StartConversion befindet sich eine Schaltfläche zum Starten des Programms. Rechts daneben steht ein kleiner Anleitungstext (in englischer Sprache). Unter anderem geht es hier um einige Vorbedingungen für das Funktionieren des Programms.

 

 

Die umzuwandelnde Kreuztabelle muss sich auf einem Arbeitsblatt der aktuellen Arbeitsmappe befinden und sollte in der linken oberen Ecke dieses Arbeitsblatts beginnen, also in der Zelle A1. Genauer gesagt, kann die Zelle A1 leer sein, aber sie soll den Schnittpunkt zwischen der ersten Zeile und der ersten Spalte der Kreuztabelle bilden.

 

 Um das Programm aufzurufen, klickt der Benutzer auf die Schaltfläche „Start conversion to dbtable“. Es öffnet sich dann das Formular zur Eingabe der Parameter und zum Durchführen der Umwandlung (s. unten).

 

 

 

Das unten stehende Bild zeigt das Formular nach der Eingabe, aber noch vor dem Drücken der Schaltfläche <Start conversion>. Der Benutzer muss alle Eingabefelder ausfüllen, wobei das oberste Feld gefüllt werden kann, indem der Benutzer eine beliebige Zelle der Kreuztabelle selektiert.  Das unterste Textfeld ist kein Eingabefeld, sondern ein Feld für Nachrichten an den Benutzer.

 

 

Das nächste Bild zeigt das Formular, nachdem der Benutzer die Schaltfläche <Start conversion> gedrückt hat. Wie die Nachricht am Fuß des Formulars zeigt, war die Durchführung erfolgreich. Die Datenbanktabelle wurde auf das vom Benutzer angegebene Arbeitsblatt ausgegeben. Falls dieses Arbeitsblatt noch nicht vorhanden war, wurde es vom Programm angelegt.

 

 

 

 

Der Aufbau des Programms

 

Das Programm hat eine zweischichtigeArchitektur. Die obere Schicht besteht aus dem Formular crossToDbForm und dem dazu gehörigen Formularmodul. Die untere Schicht ist zweigeteilt. Das Modul matrVariant enthält den Programmkern in Form der Funktion crossToDB, welche die Umwandlung durchführt. Ebenfalls zur unteren Schicht gehört das Modul ValHlp, in dem sich allgemein verwendbare Funktionen zur Validierung von Eingaben befinden.

 

 

 

 

 

Der Code der Funktion crossToDB im Modul matrVariant

 

Vom Modul matrVariant betrachten wir hier nur die Funktion crossToDB und die Deklaration des Datentyps dbRow, welcher in dieser Funktion benutzt wird. Dieser Datentyp enthält lediglich ein Array mit drei Elementen vom Typ Variant. Wir benutzen dbRow, um darin Zeilen der Datenbanktabelle zwischenzuspeichern.

 

Neben dem Bereich cross der Kreuztabelle und den Namen der Spalten der zu bildenden Datenbanktabelle hat die Funktion crossToDB noch einen weiteren, optionalen Parameter zincl. Dieser Parameter bestimmt , ob Leerstellen bzw. Nullen in der Kreuztabelle zu Zeilen in der Datenbanktabelle werden sollen oder nicht.  Beachten Sie, dass diese Wahlmöglichkeit im oben dargestellten Formular  nicht angeboten wird. Man könnte das Formular aber noch dahin gehend ergänzen.

 

Option Explicit

 

'used in crossToDB

Type dbRow

      r(1 To 3) As Variant

End Type

 

'converts a non-nested cross table into a database table with

'columns col1Name, col2Name, and col3Name.

'Parameter zIncl indicates whether cells containing 0 or ""

'should result in rows of the database table (or should be ignored)

 

Public Function crossToDB(ByVal cross As Range, _

                                        ByVal col1Name As String, _

                                        ByVal col2Name As String, _

                                        ByVal col3Name As String, _

                                        Optional ByVal  zIncl As Boolean = False) As Variant

 

    Dim d() As dbRow             ‘array for dbtable rows

    Dim r() As Variant              ‘where dbtable is finally stored

    Dim i As Long, j As Long, dbCount As Long

 

    'data are collected in d

    ReDim  d(1 To 1)

    d(1).r(1) = col1Name

    d(1).r(2) = col2Name

    d(1).r(3) = col3Name

    dbCount = 1

    For i = 2 To  cross.rows.Count

        For j = 2 To  cross.Columns.Count

            If zIncl Or cross(i, j).Value <> 0 And cross(i, j).Value <> "" Then

                  dbCount = dbCount + 1

                  ReDim Preserve d(1 To dbCount)

                  d(dbCount).r(1) = cross(i, 1).Value

                  d(dbCount).r(2) = cross(1, j).Value

                  d(dbCount).r(3) = cross(i, j).Value

            End If

        Next j

    Next i

 

 

    'data are transferred from d to r (become non-nested')

    ReDim  r(1 To dbCount, 1 To 3)

    For i = 1 To  dbCount

        For j = 1 To 3

               r(i, j) = d(i).r(j)

        Next j

    Next i

 

    crossToDB = r

End Function

 

 

 

Der Code des Formularmoduls crossToDbForm

 

Die Verarbeitung der Eingaben geschieht in der Ereignisprozedur StartBtn_Click. Diese Prozedur bedient sich der Funktion ValidierungOK, um einen Teil der Eingaben zu validieren.  Schwieriger als die Namen der Spalten und des Ziel-Arbeitsblatts ist jedoch die Eingabe des Bereichs zu validieren, in dem sich die umzuwandelnde Kreuztabelle befindet. Deshalb werden Fehler, die aus falschen Eingaben dieses Bereichs beruhen, pauschal mit On Error GoTo abgefangen.

 

Option Explicit

 

Private Sub UserForm_Initialize()

     Me.CrossRngRefE.SetFocus

End Sub

 

Private Sub StartBtn_Click()

    Dim cRng As Range

    Dim db() As Variant

    Dim w As Worksheet

 

    If Not ValidierungOK Then GoTo errorhandler1

 

    On Error GoTo errorhandler1

    Set cRng = Range(Me.CrossRngRefE.Value).CurrentRegion

    db = matrVariant.crossToDB(cRng, Me.FirstColTBx.Text, _

                                              Me.SecondColTBx.Text, _

                                              Me.ThirdColTBx.Text)

 

    If Not Hlp.wrkshExists(Me.resultWsTBx.Text) Then Worksheets.Add.Name =  _

                                                                    Me.resultWsTBx.Text

    Set w = Worksheets(Me.resultWsTBx.Text)

    w.Cells.Clear

    w.Range(“A1:C” &UBound(db, 1)) = db

    Me.MsgTBx.Text = _

        “database table has been printed to worksheet “ &  Me.resultWsTBx.Text

    Exit Sub

 

errorhandler1:

      Me.MsgTBx.Text = “Error: Conversion was not possible”

End Sub

 

Private Function ValidierungOK() As Boolean

    If Not ValHlp.istName(Me.FirstColTBx.Text) Or _

        Not ValHlp.istName(Me.SecondColTBx.Text) Or _

        Not ValHlp.istName(Me.ThirdColTBx.Text) Or _

        Not ValHlp.istName(Me.resultWsTBx.Text) Or _

        Me.CrossRngRefE.Text = “” Then

                MsgBox “check your input”

                ValidierungOK = False

               Exit Function

    End If

   ValidierungOK = True

End Function

 

 

 

Der Code des Moduls ValHlp

 

Von den Funktionen dieses Moduls wird in dieser Anwendung nur istName benutzt. Diese Funktion greift ihrerseits auf die Funktion istBuchstabe zu. Hier ist der Code für die beiden Funktionen:

 

'ermittelt für den Wert s, ob er den Anforderungen für einen

'Namen genügt; Achtung: fängt nicht alle Fehler ab!

Public Function istName(ByVal s As String) As Boolean

    istName = True

    If Not istBuchstabe(Mid(s, 1, 1)) Then

         istName = False

    Else

        Dim iAs Integer

        For i = 1 To Len(s)

            If Not (istBuchstabe(Mid(s, i, 1)) Or Mid(s, i, 1) = " " _

                    Or Mid(s, i, 1) = "-" Or Mid(s, i, 1) = "." Or _

                    Mid(s, i, 1) = "'") Then

                istName = False

            End If

        Next

    End If

End Function

 

 

'prüft, ob das übergebene Zeichen ein Buchstabe ist; bezieht

'dabei auch Umlaute mit ein

Public Function istBuchstabe(ByVal c As String) As Boolean

    c = Mid(c, 1, 1)

    If c >= "A" And c <= "Z" Or _

       c >= "a" And c <= "z" Or _

       c = "Ä" Or c = "ä" Or c = "Ü" Or _

       c = "ü" Or c = "Ö" Or c = "ö" Then

                  istBuchstabe = True

    Else

                 istBuchstabe = False

    End If

End Function