Phil's Office Secrets - Tipps und Tricks für Excel und Excel-VBA

Zusammenfassung
Wer sich schon mal geärgert hat, dass in Microsoft Excel nur die letzten 16 Befehle rückgängig gemacht werden können, kann in der Windows-Registry die Anzahl Rückgängig-Ebenen erhöhen.

1. Öffnen Sie mit dem Windows-Programm "RegEdit" die Registry
2. Wählen Sie den Zweig "HKey_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft Excel"
3. Führen Sie den Menübefehl "Bearbeiten/Neu/DWORD-Wert" aus
4. Geben Sie als neuen Schlüssel-Name "UndoHistory" ein
5. Führen Sie den Menübefehl "Bearbeiten/Ändern" aus
6. Aktivieren Sie unter "Basis" die Option "Dezimal"
7. Geben Sie für "Wert" eine Zahl zwischen 1 und 100 ein

Die bei "Wert" eingetragene Zahl steht für die maximale Anzahl möglicher Undo-Aktionen. Bitte beachten Sie, dass der Wert nicht kleiner als 1 oder grösser als 100 gewählt werden sollte (Empfehlung von Microsoft). Wählen Sie die Anzahl nur so gross wie Sie später tatsächlich Undo-Ebenen benötigen werden, da jede Undo-Ebene beim Excel-Start eine bestimmte Anzahl Bytes Arbeitsspeicher vorbelegt. Je grösser die Anzahl Ebenen, desto kleiner der verfügbare Speicher für die Daten der geöffneten Arbeitsmappen.

Laut Microsoft funktioniert obiges bei Windows 95 und NT 4.0 bei Verwendung von Excel 97. Man kann aber davon ausgehen, dass bei anderen Windows-Versionen wie z.B. Windows 2000 und anderen Excel-Versionen der Trick ebenfalls funktioniert. Gemäss Spezifikationen besitzt auch Excel 2000 standardmässig 16 Rückgängig-Ebenen, sodass der Registry-Schlüssel sehr wahrscheinlich auch unter Excel 2000 korrekt funktioniert. Sicherheitshalber sollten Sie in diesem Fall zuerst eine Sicherungskopie der Registry anlegen (Menü "Registrierung/Registrierungsdatei exportieren").

2] Umgehungslösung zum Statuszeile-Bug des Autofilters

Zusammenfassung
Die AutoFilter-Funktion von Excel 97 und Excel 2000 besitzt einen Bug. Anstelle der Information "Gefundene Datensätze: x von y" wird in der Statuszeile lediglich das Wort "Filtermodus" angezeigt.

Wenn man auf die Datensätze-Information in der Statuszeile angewiesen ist, kann man eine Umgehungslösung verwenden.

Vorgehen
1. Menübefehl Extras/Optionen
2. Registerseite Berechnen
3. Berechnung auf 'Manuell' einstellen
4. Optionen-Dialogfenster schliessen
5. Autofilter neu ausführen

Anmerkung
Zum Autofilter-Bug gibt es zwei kleine VBA-Programme, die auch ohne die Umgehungslösung die Anzahl gefilterte Datensätze korrekt ermitteln. Den VBA-Programmcode finden Sie hier.

3] Zeichnen-Funktionen/Objekt einfügen entsperren

Zusammenfassung
Kann es sein, dass der Objekt einfügen-Dialog (Menü Einfügen/Objekt) zwar erscheint, nach Auswählen von beispielsweise "Microsoft WordArt" die Fehlermeldung "Objekt kann nicht eingefügt werden" erscheint?

Falls dem so ist, dann lässt sich das Problem wie nachstehend beschrieben beheben.

Vorgehen
1. Visual Basic-Editor öffnen (Alt + F11)
2. Im Projektexplorer das Excel-Objekt "DieseArbeitsmappe" markieren
3. Im Eigenschaftenfenster den Eintrag "DisplayDrawingObjects" kontrollieren
4. Falls der Eintrag auf "3 - xlHide" steht, dann ändern auf "-4104 - xlDisplayShapes".

Zurück in Excel funktioniert dann alles wieder wie gewohnt.

4] Jahre, Monate und Tage zwischen Start- und End-Datum berechnen

Zusammenfassung
Möchte man den Unterschied zwischen zwei Datumswerten als Anzahl Jahre, Monate und Tage ausgeben, hilft nur eine längere Formel.

Zellformel
=JAHR(A2)-JAHR(A1)-WENN(ODER(MONAT(A2)<MONAT(A1);UND(MONAT(A2)=MONAT(A1); TAG(A2)<TAG(A1)));1;0) & " Jahre, " &
MONAT(A2)-MONAT(A1)+WENN(UND(MONAT(A2)<=MONAT(A1);TAG(A2)<TAG(A1));11;WENN(UND(MONAT(A2)<MONAT(A1);
TAG(A2)>=TAG(A1));12;WENN(UND(MONAT(A2)>MONAT(A1);TAG(A2)<TAG(A1));-1))) & " Monate, " &
A2-DATUM(JAHR(A2);MONAT(A2)-WENN(TAG(A2)<TAG(A1);1;0);TAG(A1)) & " Tage"

Edith Mangold und Jochen Seyffert haben eine kürzere Lösung gefunden:

Zellformel
=DATEDIF(A1;A2;"y")&" Jahre, "&DATEDIF(A1;A2;"ym")&" Monate, "&DATEDIF(A1;A2;"md")&" Tage"

5] Anzahl Tage zwischen einem beliebigen Datum und Heute

Zusammenfassung

Brauchen Sie die Anzahl Tage zwischen einem bestimmten Datum und dem heutigen Datum? Mit der nachstehenden einfachen Formel erhalten Sie das gesuchte Ergebnis. Das angegebene Datum kann in der Vergangenheit oder in der Zukunft liegen.

Syntax
=DATUM([Jahr];[Monat];[Tag])-HEUTE()

Zellformel
=DATUM(2002;3;26)-HEUTE()

6] 5-er Rundung/Runden auf 5 Rappen bei Währung CHF

Zusammenfassung
Es existieren zwei bekannte Formeln für das Runden der zweiten Dezimalstelle einer beliebigen Zahl auf den Wert 5. Hier beide Varianten:

Formel
=RUNDEN(Bezug/5;2)*5
=RUNDEN(Bezug*20;0)/20

7] Laufzeitfehler 438 "Objekt unterstützt diese Eigenschaft oder Methode nicht"

Zusammenfassung
Beschreibung folgt in Kürze...

Laufzeitfehler Nr. 438 "Objekt unterstützt diese Eigenschaft oder Methode nicht"

Selection.SpecialCells(xlCellTypeComments).Select

Weitere Informationen zu diesem Thema finden Sie unter "Bugs: Die Selection-Eigenschaft und ihre Tücken".

8] Statisches Diagramm trotz AutoFilter-Einsatz

Zusammenfassung
Wird unterhalb einer Liste ein Diagramm eingefügt, welches auf den Daten der Liste basiert, ändert sich das Diagramm bei jeder Verwendung des Autofilters. Wenn man möchte, dass das Diagramm immer die ungefilterten Listendaten zeigt, genügt die Änderung einer Einstellung im Optionen-Dialog.

Vorgehen
1. Diagramm anklicken, sodass es markiert ist
2. Optionen-Dialog öffnen (Menü Extras/Optionen)
3. Auf Registerseite "Diagramm" wechseln
4. Option "Nur sichtbare Zellen werden gezeichnet" deaktivieren

9] Genauer Objekttyp eines Zeichnungsobjektes ermitteln

Zusammenfassung
Mit der TypeName-Funktion von VBA lässt sich der Typ einer Variable oder eines Objektes herausfinden.

Lösung

?TypeName(Selection)    '-> Rectangle
?TypeName(ActiveSheet.Shapes(1))   '-> Shape
?TypeName(ActiveSheet.Shapes(1).DrawingObject)   '-> Rectangle
?ActiveSheet.Shapes(1).Type   '-> 1 für 'msoAutoShape'
?ActiveSheet.Shapes(1).AutoShapeType   '-> ID des AutoShapes oder -2 für 'Mixed' bzw. kein AutoShape

10] Herausfinden, ob ein Zeichnungsobjekt ein Bild ist

Zusammenfassung
Die übliche Methode zum Herausfinden, ob ein Zeichnungsobjekt ein Bild ist, ist die Abfrage der Type-Eigenschaft des Shape-Objektes. Besitzt die Eigenschaft den Wert 13 (=msoPicture), so ist das Shape ein Bild.

?ActiveSheet.Shapes(1).Type (13 für 'msoPicture')

Eine andere, sehr wenig bekannte Möglichkeit besteht darin, mit der TypeName-Funktion das vom Shape repräsentierte Objekt zu ermitteln. Mit der folgenden Abfrage wird dies allerdings nicht erreicht, da der Objekttyp eines Shapes verständlicherweise Shape lautet.

?TypeName(ActiveSheet.Shapes(1)) (Shape)

Damit das tatsächlich dargestellte Objekt ausgegeben wird, muss die Eigenschaft 'DrawingObject' angegeben werden.

?TypeName(ActiveSheet.Shapes(1).DrawingObject) (Picture)

Dadurch erhalten wir den Typ des vom Shape dargestellten Objektes, weil das DrawingObject-Property ein Objekt zurückgibt. Würde man dagegen...

?TypeName(ActiveSheet.Shapes(1).Type) (Long)

Restliche Beschreibung folgt in Kürze...

?ActiveSheet.Shapes(1).Name (Picture 1)
?ActiveSheet.Shapes(1).AutoShapeType (-2 für 'Mixed')

11] Wann sollte man 'Value' und wann 'Text' bei Zellen verwenden?

Zusammenfassung
Oft werden die Value- und Text-Eigenschaft einer Zelle bzw. eines Bereiches (d.h. des Range-Objektes, z.B. Range("A1").Text) für denselben Zweck verwendet, nämlich für die Abfrage oder Zuweisung von Zell-Inhalten. Doch in welcher Situation sollte man 'Value' und wann 'Text' benutzen? Nachfolgend wird beschrieben, welche Hauptunterschiede die beiden Eigenschaften aufweisen und wann welche Eigenschaft verwendet werden sollte.

Inhalt einer einzelnen Zelle abfragen
a) Zelle A1 enthält die Zahl 123456.789 (Zahlenformat Standard).
?Range("A1").Value -> 123456.789 (als Zahl)
?Range("A1").Text -> 123456.789 (als Text)

b) Zelle A1 besitzt gleichen Inhalt wie in a). Breite der Spalte A wird so verkleinert, dass anstelle der Zahl "###" angezeigt wird.
?Range("A1").Value -> 123456.789 (als Zahl)
?Range("A1").Text -> ###

Inhalt einer einzelnen Zelle zuweisen
   Range("A1").Value = 123   -> 123 (Zahl, Standardformat)
   Range("A1").Text = 123    -> Meldung Laufzeitfehler Nr. 9 "Index ausserhalb des gültigen Bereichs"
   Range("A1").Value = "123"   -> 123 (Zahl, Standardformat)
   Range("A1").Text = "123"    -> Meldung Laufzeitfehler Nr. 9 "Index ausserhalb des gültigen Bereichs"
   Range("A1").Value = "ABC"   -> ABC (Text, Standardformat)
   Range("A1").Text = "ABC"    -> Meldung Laufzeitfehler Nr. 9 "Index ausserhalb des gültigen Bereichs"

Inhalt eines Zellbereiches abfragen
a) Zellbereich A1-B2 enthält die Zahl 123456.789 (Zahlenformat Standard).
?Range("A1:B2").Value -> Meldung Laufzeitfehler Nr. 13 "Typen unverträglich" oder Nr. 7 "Nicht genügend Speicher"
?Range("A1:B2").Text -> 123456.789 (als Text)

b) Zellbereich A1-B2 enthält verschiedene Zahlen (Zahlenformat Standard).
?Range("A1:B2").Value -> Meldung Laufzeitfehler Nr. 13 "Typen unverträglich" oder Nr. 7 "Nicht genügend Speicher"
?Range("A1:B2").Text -> Null

Inhalt einem Zellbereich zuweisen
   Range("A1:B2").Value = 123   -> 123 (Zahl, Standardformat)
   Range("A1:B2").Text = 123    -> Meldung Laufzeitfehler Nr. 9 "Index ausserhalb des gültigen Bereichs"
   Range("A1:B2").Value = "123"   -> 123 (Zahl, Standardformat)
   Range("A1:B2").Text = 123    -> Meldung Laufzeitfehler Nr. 9 "Index ausserhalb des gültigen Bereichs"
   Range("A1:B2").Value = "ABC"   -> ABC (Text, Standardformat)
   Range("A1:B2").Text = "ABC"    -> Meldung Laufzeitfehler Nr. 9 "Index ausserhalb des gültigen Bereichs"

12] Herausfinden, ob Zellen den gleichen Inhalt besitzen

Zusammenfassung
Wir benützen dazu eine spezielle Eigenheit des Range-Objektes.

Die Abfrage der Text-Eigenschaft von Range gibt bei einem Bereich von Zellen mit gleichem Inhalt den Textinhalt zurück. Besitzen die Zellen des Bereiches nicht den selben Inhalt respektive besitzt auch nur eine einzige Zelle einen abweichenden Inhalt, so enthält die Text-Eigenschaft den Ausdruck Null.

So prüfen Sie, ob beispielsweise die Zellen A1, B4, C2, G13 und J5 den gleichen Inhalt besitzen:

MsgBox Range("A1, B4, C2, G13, J5").Text

Achtung: Keine Spaltenbreite darf zu klein sein (nicht ###)!

13] Unterschiedliche Zellen zweier Spalten in der ersten Spalte markieren

Zusammenfassung
Microsoft Excel bietet eine einfache Möglichkeit zum Herausfinden der Unterschiede zwischen zwei Spalten. Diese Möglichkeit befindet sich als Option "Zeilenunterschiede" auf dem Fenster "Inhalte auswählen", welches via das Gehe zu-Fenster geöffnet wird. Standardmässig werden die gefundenen Unterschiede, also die abweichenden Zellen, in der zweiten Spalte markiert. Sollen die Unterschiede in der ersten Spalte gekennzeichnet werden, muss ein leicht anderes Vorgehen gewählt werden.

Vorgehen
1. Erste Spalte markieren
2. Strg-Taste drücken und halten
3. Zweite Spalte markieren
4. Menübefehl "Gehe zu" ausführen (oder Taste F5 drücken)
5. Schaltfläche "Inhalte" klicken
6. Option "Zeilenunterschiede" wählen
7. Schaltfläche "OK" klicken

14] Werte mehrerer Zellen multiplizieren

Die SUMME-Tabellenfunktion ist wohl einer der bekanntesten Funktionen von Microsoft Excel und wird oft benutzt. Weniger bekannt ist die PRODUKT-Funktion, mit der man mehrere Werte bzw. Zellen miteinander multiplizieren kann.

Anzahl Sekunden eines Jahrhunderts berechnen:

=60*60*24*365*100

=PRODUKT(60;60;24;365;100)

15] Häufigkeitsverteilung von Zahlen basierend auf bestimmten Wertbereiche ermitteln

Zusammenfassung
Die in Excel standardmässig enthaltenen Funktionen können so manche Aufgabestellung auf einfache Art und Weise lösen. Das Problem ist meistens nur, dass man erstens nicht weiss, dass die benötigte Funktion existiert und zweitens weder den Funktionsnamen noch die korrekte Funktionsverwendung kennt. Auch für die Berechnung einer Häufigkeitsverteilung stellt Excel eine Funktion zur Verfügung, die nachstehend beschrieben und anhand eines Praxisbeispiels gezeigt wird.

Beispiel
Eine Liste verschiedener Aktivitäten soll analysiert werden. Jede Aktivität besitzt die Information, zu wieviel Prozent sie erledigt ist. Wir möchten herausfinden, wieviele Aktivitäten wie weit fortgeschritten sind, basierend auf den Wertbereichen 0-50 %, 51-75 %, 76-85 % und >86 %.

Häufigkeitsverteilung

Die Obergrenzen unserer Wertbereiche werden in Zellen eingetragen (der Bereich >86 % muss nicht eingegeben werden, da er sich automatisch ergibt).

Syntax: =HÄUFIGKEIT(Daten; Klassen)
Daten: Matrix einer Wertmenge oder Bezug auf eine Wertmenge (D4 bis D12)
Klassen: Bezug auf die Wertbereiche (F4 bis F6)

Bevor die Formel eingegeben wird, muss der Bereich G4 bis G7 markiert werden, da HÄUFIGKEIT eine Matrixfunktion ist und somit mehrere Werte zurückgibt. Die Formel "=HÄUFIGKEIT(D4:D12;F4:F6)" wird in der Bearbeitungsleiste eingegeben (siehe Abbildung) und mit der Tastenkombination Strg+Umschalt+Enter (bzw. Ctrl+Shift+Enter) bestätigt.

16] Bestimmte Zelle in einer Liste markieren

Zusammenfassung
Nehmen wir folgende Aufgabestellung an:
Die Tabelle2 enthält in der Spalte A eine Liste mit verschiedenen Zahlen. Jede Zeile enthält eine Zahl. In Tabelle1 soll in der Zelle A1 eine Zahl eingegeben und anschliessend automatisch in der Tabelle2 diejenige Zelle markiert werden, welche die eingegebene Zahl enthält.

Eine For-Each-Next-Schleife, eine ähnliche Schleifenstruktur oder die Find-Methode würde die Aufgabe lösen. Wir behelfen uns jedoch mit der VERGLEICH-Funktion (engl. MATCH), welche uns die Position des gesuchten Elementes liefert.

Worksheets("Tabelle2").Range("B10").Value = "=MATCH(Tabelle1!A1,A15:A99,0)+14"
Worksheets("Tabelle2").Range("A" & Cells(10, 2).Value).Select

Erläuterung
- Für die Formel "=MATCH..." wird irgend eine Zelle auf Tabelle2 benötigt. Im obigen Beispiel wird B10 verwendet. Wichtig ist, dass die Zelladresse mit den Argumenten in "Cells(10, 2)" übereinstimmt (B10: Zeile 10, Spalte 2).
- In "MATCH(Tabelle..." steht "A15:A99". Das ist die erste und die letzte Zelle der zu durchsuchenden Datenliste in Spalte A.
- Die Addition "+14" steht quasi für den Offset der zu markierenden Zelle von oben. Da die Liste bei Zeile 15 beginnt, müssen die ersten 14 Zeilen nicht berücksichtigt werden, also +14. Würde die Liste z.B. in Zeile 33 beginnen, wäre die Addition +32.
- Die Tabellennamen (Tabelle1 und Tabelle2) müssen allenfalls angepasst werden.

Public Sub InitializeShowMatchedCell()
Application.OnKey "{ENTER}", "ShowMatchedCell"
ActiveWorkbook.NewWindow
ActiveWorkbook.Windows.Arrange
ActiveWorkbook.Windows(2).Activate
ActiveWorkbook.Worksheets("Tabelle2").Select
ActiveWorkbook.Windows(2).Activate
ActiveWorkbook.Worksheets("Tabelle1").Select
End Sub

Public Sub ShowMatchedCell()
Worksheets("Tabelle2").Range("A2:A99").Borders.LineStyle = 0
Worksheets("Tabelle2").Range("A" & CStr(Application.Evaluate("IF(ISNA(MATCH(Tabelle1!A1,Tabelle2!A2:A99,0)+1)," & _
CStr(Worksheets("Tabelle2").UsedRange.Rows.Count) & ",MATCH(Tabelle1!A1,Tabelle2!A2:A99,0)+1)"))).Borders.LineStyle = 9
End Sub

17] Anzuzeigender Datensatz bei Öffnen der Datenmaske festlegen

Zusammenfassung
Mit der ShowDataForm-Methode des Worksheet-Objektes kann man die Datenmaske aufrufen, z.B. mit "ActiveSheet.ShowDataForm". Der Nachteil dieser Methode ist, dass ein Datumswert (z.B. 15.02.2002) in der Datenmaske mit dem englischen Datumsformat (also 2/15/2002) angezeigt wird. Der Grund liegt an VBA als Programmiersprache: Weil VBA grundsätzlich auf Englisch "hört", wird das englische Format benutzt. Eine lokalisierte Formatierungsmöglichkeit existiert nicht. Der Aufruf mittels ShowDataForm ist für "deutsche Daten" somit ungeeignet. Durch das Aufrufen des Datenmaske-Menübefehls mittels Execute wird dieses Problem umgangen.

VBA-Code
Datenmaske öffnen und erster Datensatz zeigen:
Application.CommandBars.FindControl(Id:=860).Execute

Mit einem kleinen Trick kann die Maske gesteuert werden, und zwar indem Tastencodes vorausgeschickt werden. Hier die wichtigsten Aktionen:

DataForm öffnen und zweiter Datensatz zeigen:
SendKeys "{Down}"
Application.CommandBars.FindControl(Id:=860).Execute

Dritter Datensatz zeigen:
SendKeys "{Down 2}"
Pro weiteren Datensatz einfach die Zahl hinter "Down" jeweils um 1 erhöhen.

Im ersten Feld die Zahl 5 suchen:
SendKeys "%s{5}%t"

Im ersten Feld die Zahl 5 und im zweiten Feld den Begriff "Ja" suchen:
SendKeys "%s{5}{tab}{Ja}%t"

Im dritten Feld den Begriff "OK" suchen:
SendKeys "%s{tab}{tab}{OK}%t"
Hinweis: Mit "%s" wird ALT+S, mit "%t" ALT+T ausgelöst, was dem Shortcut der Schaltfläche "Suchkriterien" bzw. "Nächsten suchen" entspricht.

Neuer Datensatz anlegen:
SendKeys "%n{A}{tab}{9}"

Neuer Datensatz anlegen, in erstes Feld "A" und in zweites Feld "9" eintragen:
SendKeys "%n{A}{tab}{9}"
Hinweis: "%n" ist der Shortcut der Schaltfläche "Neu".

Wichtig:
Damit obiges klappt, muss mindestens eine Zelle der Datenliste selektiert sein, oder - das geht auch - sämtliche Zellen der Liste (inklusive Überschriften!). Wenn nur ein paar Zellen der Datenliste markiert sind, erscheint die Fehlermeldung "Spaltenüberschriften konnten nicht bestimmt werden" (ausser wenn die markierten Zellen die Überschriften-Zellen umfassen).

Weitere Informationen über SendKeys erhalten Sie hier:

Weitere Informationen

The eXpLorer: Senden von Tastenbefehlen

18] Andere Office-Anwendung mit VBA starten

Zusammenfassung
Muss aus Excel heraus eine andere Office-Anwendung gestartet werden, bedient man sich häufig der Shell-Funktion von VBA. Damit die Anwendung jedoch gestartet werden kann, muss der Shell-Funktion der genaue Pfad der exe-Datei mitgegeben werden. Solange die zu startende Anwendung aus der gleichen Office-Version wie Excel stammt, könnte man behelfsmässig den Pfad von Excel nehmen (Application.Path) und voraussetzen, dass die exe-Datei der anderen Anwendung im gleichen Verzeichnis wie Excel.exe steht. Sobald es sich um eine Anwendung einer anderen Office-Version handelt, weicht der Programmpfad mit grosser Wahrscheinlichkeit ab. Damit nicht ein (nicht gerade einfach zu programmierenden) Zugriff auf die Windows Registry vorgenommen werden muss, um den Pfad der gesuchten Anwendung herauszufinden, stellt Excel eine spezielle Methode zum Starten von Office-Anwendung zur Verfügung.

VBA-Code
Application.ActivateMicrosoftApp Anwendung

Konstanten für Anwendung
xlMicrosoftAccess
xlMicrosoftFoxPro
xlMicrosoftMail
xlMicrosoftPowerPoint
xlMicrosoftProject
xlMicrosoftSchedulePlus
xlMicrosoftWord

Tipp!
Nach Eintippen von "Application.ActivateMicrosoftApp" erscheint nicht wie üblich die Auswahlliste mit den zur Verfügung stehenden Konstanten. Die Liste kann jedoch angezeigt werden, indem Sie zuerst den Namen der Konstantenauflistung mit anschliessendem Punkt eingeben:

Application.ActivateMicrosoftApp XlMSApplication.xlMicrosoftWord

19] Apostroph-Zeichen in Zellen entfernen

Zusammenfassung
Durch das Voranstellen des Apostroph-Zeichens kann bekanntlich eine Zahl als Text in eine Zelle eingegeben werden (z.B. '5). Die Zelle behält dabei das Standard-Zahlenformat (d.h. erhält nicht das Format 'Text') und besitzt nach wie vor den effektiven Wert (in unserem Beispiel somit 5 und nicht '5). Doch wie kann der Zellinhalt wieder in einen numerischen Wert umgewandelt werden? Eigentlich sehr einfach indem das Apostroph entfernt wird (Zelle mit F2 editieren). Doch was tun, wenn man bei sehr vielen Zellen das Apostroph entfernen möchte? Muss wirklich jede Zelle einzeln editiert werden? Die Antwort lautet Nein, denn es gibt einen Trick:

Weil das Apostroph nicht zum eigentlichen Zellwert (Eigenschaft Value) und auch nicht zum Zelltext (Eigenschaft Text) gehört sondern in einer separaten Eigenschaft namens Prefix vermerkt ist, können wir die Value der Zelle einfach neu setzen. Das hat zur Folge, dass der Zellwert (also ohne das Apostroph) der Zelle neu zugewiesen wird. Für eine (die aktive) Zelle sieht der VBA-Code so aus:

ActiveCell.Value = ActiveCell.Value

Die Werte aller verwendeten Zellen des gesamten Arbeitsblattes können natürlich auch neu zugewiesen werden. Dazu verwenden wir jedoch nicht eine For-Each-Next-Schleife und sprechen jede Zelle einzeln an (was man vermutlich tun würde) sondern nutzen eine spezielle Eigenheit des Range- bzw. des UsedRange-Objektes, da erstens UsedRange ein Objekt vom Typ Range ist und zweitens ein Range-Objekt eine Zelle oder einen Zellbereich enthält. Diese Rahmenbedingungen erlauben es uns auf eine umständliche Schleife zu verzichten, und können daher daher folgende VBA-Anweisung schreiben:

Range(ActiveSheet.UsedRange.Address, ActiveSheet.UsedRange.Address).Value = _
Range(ActiveSheet.UsedRange.Address, ActiveSheet.UsedRange.Address).Value

Alternativ dazu können Sie auch den zu konvertierenden Zellbereich zuerst markieren und dann diese Anweisung ausführen:

Selection.Value = Selection.Value

Es ist auf den ersten Blick vielleicht ein bisschen erstaunlich, dass die obigen Anweisungen alle Apostroph-Zeichen entfernen, aber es funktioniert tatsächlich.

Achtung!
Durch die erneute Wert-Zuweisung werden Zellinhalte, die beispielsweise einen Bezug besitzen (z.B. steht in Zelle A1 '=B1'), in den entsprechenden Wert der bezogenen Zelle umgewandelt. Steht z.B. in der Zelle B1 die Zahl 7, so steht nachher in der Zelle A1 ebenfalls die Zahl 7 und nicht mehr '=B1'.

20] Installierte Schriften auflisten

Zusammenfassung
Weder die Programmiersprache VBA noch das Objektmodell von Microsoft Excel stellen ein Objekt bzw. ein Auflistungsobjekt bereit, welches den Zugriff auf die auf dem Rechner installierten Schriften ermöglicht. Wenn man eine Liste der verfügbaren Schriften benötigt, kann man sich eines Tricks bedienen. Die Format-Symbolleiste von Microsoft Excel besitzt standardmässig ein DropDown-Listenfeld mit den installierten Schriftarten. Diese Liste kann man mittels VBA-Code auslesen.

Liste der installierten Schriften
Abbildung: DropDown-Liste "Schriften"

VBA-Code
Sub ListFontNames()
Dim intFont As Integer
Dim objFontControl As Object
Set objFontControl = Application.CommandBars("formatting").FindControl(Id:=1728)
For intFont = 0 To objFontControl.ListCount - 1
ActiveSheet.Cells(intFont + 1, 1).Value = objFontControl.List(intFont + 1)
Next
Set objFontControl = Nothing
End Sub

21] Bedingtes Benutzerdefiniertes Zahlenformat

Zusammenfassung
Beim Benutzerdefinierten Zahlenformat (Menübefehl Format/Zelle oder Tastenkombination Strg+1) ist ja bestens bekannt, dass der erste Abschnitt für die Formatierung von positiven und der zweite Abschnitt für die negativen Werte steht. Weniger bekannt ist, dass der dritte Abschnitt für Nullwerte verwendet wird und noch weniger, dass nach dem dritten Semikolon ein alternativer Text bestimmt werden kann. Vermutlich nahezu unbekannt ist, dass eine Formatierungsbedingung definiert werden kann, die sich aus einem Vergleichsoperator (=, <, >, <=, >=, <>) und dem Bedingungsausdruck zusammensetzt (z.B. =1 oder <>50). Wird eine Bedingung angegeben, so gilt diese für den ersten Abschnitt. Der zweite Abschnitt formatiert nach wie vor negative Zahlen, der dritte Abschnitt jedoch steuert nun für die Formatierung derjenigen Eingaben, welche die Bedingung nicht erfüllen. Der vierte Abschnitt bleibt gleich (Übrigens ist auch wenig bekannt, dass mit dem At-Zeichen (@) die Position der Zahl in einem allfällig anzuzeigenden Text bestimmt wird. Auch die Farbcodes werden eher selten eingesetzt).

Verglichen mit der Funktion "Bedingte Formatierung" (Menübefehl Format/Bedingte Formatierung) besitzt die Bedingte Zahlenformatierung insbesondere diese drei Vorteile:
- Die formatierte Zahl kann zusammen mit einem Text angezeigt werden.
- Anstelle eines fehlerhaften Zellinhaltes kann ein Alternativtext angezeigt werden.
- Der Zellwert wird nur formatiert angezeigt, d.h. bleibt unverändert.

Hinweis:
Die Bedingte Formatierung wird hauptsächlich zum Festlegen von erlaubten Zell-Eingaben benutzt. Alle nicht die Bedingungen erfüllenden Eingaben werden zurückgewiesen. Bei der Bedingten Zahlenformatierung dagegen können beliebige Werte in die Zelle eingegeben werden, wobei die Bedingung nur die zu verwendende Formatierung steuert.

Praxis-Beispiel für ein benutzerdefiniertes Zahlenformat mit Bedingung

Aufgabe:
Die Zahl für 'Anzahl Tage' soll nach folgenden Kriterien formatiert und angezeigt werden.
- Zahl 0            -> "0 Tage", Farbe unverändert (Standard: Schwarz)
- Zahl 1            -> "1 Tag", Farbe Grün
- Zahlen >1       -> Zahl und "Tage", Farbe unverändert (Standard: Schwarz)
- Zahlen <0       -> Negative Zahl und "Tage", Farbe Blau
- Nicht-Zahlen   -> "Ungültig: " und Zelltext in Hochkomma, Farbe Rot
- Dezimalstellen -> Keine (Abschneiden)

Lösung:
Benutzerdefiniertes Zahlenformat (mit Farben)
[Grün][=1] 0 "Tag";[Rot]-0 "Tage";0 "Tage";[Blau]"Ungültig: '"@"'"

Lösung 2:
Benutzerdefiniertes Zahlenformat (ohne Farben)
[=1] 0 "Tag";-0 "Tage";0 "Tage";"Ungültig: '"@"'"

=WENN(ODER(A1=1;A1=-1);"Tag";"Tage")
=WENN(ABS(A1)=1;"Tag";"Tage")
=WENN(ODER(A1=1;A1=-1);A1 & " Tag";A1 & " Tage")
=WENN(ABS(A1)=1;A1 & " Tag";A1 & " Tage")

22] Zeilenumbruch in Formel erzwingen

Zusammenfassung
In einer Formel lässt sich ein Zeilenumbruch einfügen, indem man das Zeichen mit dem Zeichencode 10 verwendet. Dazu benutzt man die Tabellenfunktion ZEICHEN.

Formel (Beispiel)
=A1 & " " & B1 & ZEICHEN(10) & C1 & ZEICHEN(10) & ZEICHEN(10) & D1

Zeilenumbruch in einer Formel
Abbildung: Ausschnitt eines Tabellenblattes mit einer Zellformel

23] Direkte Zellbearbeitung mit VBA aktivieren

Zusammenfassung
Wie kann ich per Makro die Zellbearbeitung aktivieren, sodass der Textcursor in der Zelle blinkt? Die Antwort auf diese Frage ist einfach: Der direkte Bearbeitungsmodus wird bekanntlich aktiviert, indem man einen Doppelklick auf die Zelle ausführt oder die Taste F2 drückt. Man muss also nur das Drücken von F2 simulieren. Das wird mit der SendKeys-Methode des Application-Objektes von Microsoft Excel-VBA erledigt.

VBA-Code
Application.SendKeys "{F2}"

Weitere Informationen über SendKeys und das Senden von Tasten finden Sie hier:

Weitere Informationen

The eXpLorer: Senden von Tastenbefehlen

24] Anzahl Druckseiten eines Arbeitsblattes feststellen

Zusammenfassung
Möchten Sie mit VBA herausfinden, wie viele Druckseiten ein Arbeitsblatt umfasst? Dazu muss man die Anzahl horizontale Seitenwechsel und vertikale Seitenwechsel kennen, zu beiden Werten die Zahl 1 addieren und dann die beiden Resultate multiplizieren. Der Zugriff auf die Seitenwechsel erfolgt mittels HPageBreaks und VPageBreaks. Die dazu erforderliche Formel sieht so aus:

Berechnungsformel
Druckseiten = (Seitenwechsel horizontal + 1) × (Seitenwechsel vertikal + 1)

VBA-Code
MsgBox (ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1)

25] Wechselseitige Eingabemöglichkeit für zwei Zellen

Zusammenfassung
Zum Unterbinden von Falscheingaben durch den Benutzer muss man manchmal eine wechselseitige Eingabemöglichkeit realisieren. Dazu verwendet man am besten zwei Gültigkeitsprüfungen.

Beispiel:
Wenn in Zelle C1 bereits etwas steht, soll die Eingabe in Zelle D1 gesperrt sein (und umgekehrt).

Vorgehen
Gehen Sie wie folgt vor:
1. Zelle D1 selektieren
2. Menübefehl Daten/Gültigkeit ausführen
3. Option "Benutzerdefiniert" aktivieren
4. Formel eingeben: =(C1="")
5. Fehlermeldung festlegen
6. Dialogfenster mit OK schliessen
7. Zelle C1 selektieren
8. Menübefehl Daten/Gültigkeit ausführen
9. Option "Benutzerdefiniert" aktivieren
10. Formel eingeben: =(D1="")
11. Fehlermeldung festlegen
12. Dialogfenster mit OK schliessen

26] Letzte benutzte Zelle einer bestimmten Spalte ermitteln

Zusammenfassung
Nachfolgend wird vorgestellt, wie man die letzte benutzte Zelle einer bestimmten Spalte herausfinden kann.

VBA-Code
Adresse der letzten Zelle der Spalte A ausgeben
MsgBox ActiveSheet.Range("A65536").End(xlUp).Address

Wert der letzten Zelle der Spalte A ausgeben
MsgBox ActiveSheet.Range("A65536").End(xlUp).Value

Zellformel
Adresse der letzten Zelle der Spalte A ausgeben
="A" & MAX(NICHT(ISTLEER(A1:A65535))*ZEILE(1:65535))

Wert der letzten Zelle der Spalte A ausgeben
=INDIREKT("A" & MAX(NICHT(ISTLEER(A1:A65535))*ZEILE(1:65535)))

27] Feststellen ob Druckbereich eines Tabellenblattes festgelegt wurde

Zusammenfassung
Zum Verhindern von Drucken-Fehlermeldungen ist es zu empfehlen, vor dem Drucken zu überprüfen, ob der Druckbereich des Tabellenblattes festgelegt wurde. Dazu benötigt man nicht mal zwingend ein VBA-Makro. Die Überprüfung lässt sich nämlich auch mit einer Zellformel durchführen.

Zellformel
=WENN(ISTNV(FEHLER.TYP(Tabelle1!Druckbereich));"Druckbereich festgelegt";WENN(FEHLER.TYP(Tabelle1!Druckbereich)=5;"Druckbereich nicht festgelegt";"Druckbereich festgelegt"))

28] Adresse der in der oberen linken Ecke des Mappenfensters sichtbaren Zelle feststellen

Zusammenfassung
Manchmal benötigt man die Information, welche Zelle sich gerade links oben im Fenster der Arbeitsmappe befindet. Mit der hier vorgestellten Zellformel können Sie ihre Adresse herausfinden.

Zellformel
=TEIL(INFO("Ursprung");FINDEN(":";INFO("Ursprung"))+1;9)

29] Zellen des Druckbereiches markieren

Zusammenfassung
Beschreibung folgt...

VBA-Code
Range("Druckbereich").Select

Vorgehen
1. Menübefehl "Bearbeiten/Gehe zu" (oder Taste F5)
2. Eintrag "Druckbereich" auswählen
3. OK klicken

30] Standard-Dialog anzeigen für Schriftformatierung durch Benutzer

Zusammenfassung
Excel besitzt bekanntlich eine ganze Reihe integrierter Dialoge, die auch in VBA-Programmen verwendet werden können. Das folgende Beispiel zeigt, wie ein Benutzer Schriftart und -formatierungen anhand des Standard Schrift-Dialoges bestimmen kann.

VBA-Code
Range("Z99").Select
If Application.Dialogs(xlDialogActiveCellFont).Show Then
  With Selection.Font
    LetterFont = .Name
    LetterColor = .ColorIndex
    LetterSize = .Size
    LetterStyle = .FontStyle
    .Underline = xlNone
  End With
End If

31] Entwurfsmodus einschalten

Zusammenfassung
Sie können mit VBA den Entwurfsmodus aktivieren, indem Sie folgende Anweisung ausführen:

VBA-Code
Application.CommandBars.FindControl(Id:=1605).Execute

32] Excel-Benutzeroberfläche sperren

Zusammenfassung
Es gibt eine Möglichkeit, wie man die Benutzeroberfläche von Microsoft Excel komplett sperren kann.

Sperre aktivieren
Application.DataEntryMode = xlStrict

Sperre deaktivieren
Application.DataEntryMode = xlOff

Application.DataEntryMode = xlOn

Weitere Informationen

The SOSCQ Page - Codebeispiele: Bearbeitung der aktiven Arbeitsmappe durch den Benutzer verhindern

33] Arbeitsblattname in Zelle anzeigen

Zusammenfassung
Mit dieser Formel wird der Name des aktiven Blattes in eine Zelle geschrieben:

Zellformel
=TEIL(ZELLE("Dateiname");FINDEN("]";ZELLE("Dateiname"))+1;31)

Zusammenfassung
Mit der folgenden Formel wird der Name des Arbeitsblattes "Tabelle1" in eine Zelle geschrieben:

Zellformel
=TEIL(ZELLE("Dateiname";Tabelle1!A1);FINDEN("]";ZELLE("Dateiname";Tabelle1!A1))+1;31)

34] Auf mehrere Spalten und Zeilen gleichzeitig verweisen

Zusammenfassung
Um gleichzeitig mit mehreren Zeilen oder Spalten zu arbeiten, erstellen Sie eine Objektvariable und verwenden Sie die Union-Methode, um mehrere Aufrufe mit der Rows- bzw. Columns-Eigenschaft zu verbinden. Im folgenden Beispiel wird das Format der Zeilen eins, drei und fünf im ersten Tabellenblatt der aktiven Arbeitsmappe in fett geändert.

VBA-Code
Sub SeveralRows()
ActiveWorkbook.Worksheets("Tabelle1").Activate
Dim rngUnion As Range
Set rngUnion = Union(Rows(1), Rows(3), Rows(5))
rngUnion.Font.Bold = True
Set rngUnion = Nothing
End Sub

35] Text in Kommentarfeld einfügen

Zusammenfassung
Der Befehl Einfügen (bzw. Tastenkombination Strg+V) wird vom Makro-Recorder nicht als Paste oder dergleichen aufgezeichnet. Der eingefügte Text wird in Zeichenfolgen à 200 Zeichen aufgeteilt und dann der Text-Eigenschaft des Kommentars zugewiesen.

36] Minimiertes Fenster einer Microsoft-Anwendung mit Excel-Makro wiederherstellen

Zusammenfassung
...

Application.ActivateMicrosoftApp xlMicrosoftWord

- oder -

Application.ActivateMicrosoftApp XlMSApplication.xlMicrosoftWord

37] Ausführung eines Makros über das Makro-Dialogfenster verhindern

Zusammenfassung
Es gibt tatsächlich eine Möglichkeit, eine Makroprozedur so anzupassen, damit sie nicht über den Makro-Dialog ausgeführt werden kann.

Makro-Dialog: Prozedurname Paragraph
Abbildung: Makroprozedur namens "§" im Makro-Dialog

VBA-Code
Sub §()
MsgBox "Hallo!"
End Sub

Sub Test()
§
End Sub

38] Makro-Ausführung beschleunigen

Zusammenfassung
Es gibt ein paar simple Möglichkeiten, wie man ein Makro schneller machen kann.

Fenster des VBA-Editors minimieren

VBA-Editor schliessen

39] Daten in Datei speichern ohne Datei zu schliessen

Zusammenfassung
Bekanntlich reserviert die Open-Anweisung von VBA einen so genannten E/A-Puffer und legt den Zugriffsmodus für diesen Puffer fest. Wenn man also eine Datei mit For Output oder For Append öffnet, werden alle Ausgaben in den Puffer geschrieben. Erst beim Schliessen der Datei mittels Close werden die Daten endgültig in die Datei gespeichert.

Wenn man möchte, dass immer die aktuellsten Daten auch für andere Programme zur Verfügung stehen, muss man somit die Datei nach jeder Ausgabe schliessen. Dabei wird aber erstens die Dateinummer freigegeben, und zweitens muss man für neue Ausgaben die Datei erst wieder öffnen (und zwar mit For Append). Auch wenn man sicherstellen möchte, dass bei einem Programmabsturz die noch nicht gespeicherten Daten nicht verloren gehen, sollte man ebenfalls die Datei immer schliessen und wieder öffnen (Anmerkung des Autors: Wenn ein Programm mit der End-Anweisung oder die Beenden-Schaltfläche des VBA-Editors beendet wird, werden automatisch alle Daten aus dem Ausgabepuffer in die Datei übertragen und die Datei geschlossen). Einmal abgesehen davon, dass das ständige Schliessen und erneute Öffnen höchst unschön ist, ist dieses Prozedere vor allem sehr langsam.

10'000 Zeilen

10.602 Sekunden

0.481 Sekunden

4.53 %

22.04 mal schneller

VBA-Code
Sub WriteData()
Open "C:\Daten\EineDatei.txt" For Output As #1
    Print #1, "Erster Datensatz"
    LOF 1
    MsgBox "Der erster Datensatz wurde gespeichert"
    Print #1, "Zweiter Datensatz"
Close #1
End Sub

Sub WriteDataDemoOpenClose()
Dim intCounter As Integer
Dim dblTimer As Double
dblTimer = Timer
For intCounter = 1 To 10000
    Open "C:\Demo1.txt" For Append As #1
      Print #1, "Datensatz " & intCounter
    Close #1
Next intCounter
Debug.Print "Open/Close: " & Format$(Timer - dblTimer, "0.000")
End Sub

Sub WriteDataDemoLOF()
Dim intCounter As Integer
Dim dblTimer As Double
dblTimer = Timer
Open "C:\Demo2.txt" For Output As #1
    For intCounter = 1 To 10000
      Print #1, "Datensatz " & intCounter
      LOF 1
    Next intCounter
Close #1
Debug.Print "LOF: " & Format$(Timer - dblTimer, "0.000")
End Sub

Weitere Informationen

The SOSCQ Page - Codebeispiele: Daten in eine Datei schreiben ohne die Datei zu schliessen

WB01727_.gif (1537 Byte)

Haben Sie Fragen, Anregungen oder einen Fehler entdeckt?
Mail senden an: philipp_von_wartburg@yahoo.de