Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Das 1904-Datumssystem oder Wie kann ich mit negativen Zeiten rechnen?Tipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Zeit

(Tipp 227) Nachricht zum Beitrag an Autor Nach oben

Oftmals bekommt man auf die Frage Wie kann ich negative Zeiten darstellen? die Antwort Stelle unter Optionen/Berechnen auf das 1904-Datumssystem um.

Diese Umstellung hat jedoch gravierende Folgen, denn sämtliche Daten von Arbeitsmappen, die unter dem Standard-Datumssystem erfasst worden sind, sind nicht kompatibel dazu.

Im Standard wird dem Datum 01.01.1900 der Wert 1 zugewiesen, im 1904-System bekommt der 01.01.1904 diesen Wert. Das bedeutet, dass alle Daten des Standarddatumssystems um 4 Jahre verschoben werden und somit dieses System sich im Grunde nur für reine Stundenberechnungen eignet.

Zweitens kann man immer noch nicht negative Zeiten eingeben, sondern diese nur als Ergebnis einer Berechnung erhalten. So ist die Eingabe -04:00 nicht erlaubt, aber =01:00-05:00 liefert das gewünschte Ergebnis.

Bevor man also auf das 1904-Datumssystem umstellt, sollte man sich Gedanken darüber machen, ob man dieses wirklich benötigt oder einen anderen Weg wählt. Siehe auch:

Deklaration erzwingenMakro/Sub/ProzedurTipp

Kategorie: Basics ▸ Variablen

(Tipp 212) Nachricht zum Beitrag an Autor Nach oben

Visual Basic kann bei einem neuen Variablennamen nicht erkennen, ob es sich um eine neue oder um eine falsch geschriebene bestehende Variable handelt und erzeugt daher eine neue Variable. Probleme mit falsch benannten Variablen lassen sich jedoch vermeiden. Man kann einstellen, dass bei jeder Variablen, die nicht vorher explizit deklariert wurde, eine Fehlermeldung angezeigt wird.

Schreibt man zum Beispiel: Meldung = ... ist die Variable eine andere, als wenn man schreibt Meltung = ... Wenn man nun die Variable wieder einliest, ist der Wert der Variable Meltung leer, wenn der Wert vorher in die Variable Meldung gespeichert wurde.

Um solche Tippfehler zu vermeiden, fügt man am Anfang des Moduls folgende Anweisung an:

Option Explicit

Jetzt müssen alle Variablen explizit mit DIM deklariert werden.

Mit einer Moduloption kann man Visual Basic veranlassen, jedem erstellten Modul die Option Explizit hinzuzufügen.

  1. Im Visual Basic Editor Menü Extras > ▸ Optionen
  2. Register Editor
  3. Aktivieren des Kontrollkästchen Variablendeklaration erforderlich
  4. OK

Format und InhaltMakro/Sub/ProzedurTipp

Kategorien: Format ▸ Basics und Format ▸ Zahlen

(Tipp 236) Nachricht zum Beitrag an Autor Nach oben

Wenn ich das benutzerdefinierte Format "701-000-000-??" vergebe und nur die letzten beiden Ziffern eingebe, ist es mir nicht möglich, diese vollständige Zahl zu kopieren.

Der Inhalt einer Zelle ist das, was eingegeben wurde.

Das Formatieren einer Zelle ist das Ändern des Aussehens des Inhalts - nicht aber das Ändern des Inhaltes.

In der Bearbeitungsleiste sieht man den Inhalt der aktiven Zelle. Beispiel: In eine Zelle eine Zahl eingeben und diese als Währung formatieren. Dann werden das €-Zeichen und der Tausendertrennpunkt angezeigt. Nun die Zelle aktivieren und in die Bearbeitungsleiste sehen - da steht immer noch nur die Zahl drin, nicht aber das €-Zeichen.

Gibt man nach der Formatierung, die in der Frage genannt wurde, in eine Zelle z. B. die 13 ein, ist auch nur die 13 enthalten.

Man kann sich aber aber wie folgt behelfen:
In B1 schreibt man: ="701-000-000-"&A1.
Nun kann in A1 die Zahl eingeben werden; egal, wie A1 formatiert ist. B1 kann nun kopiert werden; beim Einfügen muss man nur darauf achten, dass der Wert eingefügt wird: Einfügeoptionen ▸ Werte.

Alternative:
Ein Makro, das dem Blatt zugeordnet ist und ungefähr wie folgt aufgebaut ist:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < > 1 Then Exit Sub If Cells(Target.Row, 1) < > "" And Len(Cells(Target.Row, 1)) < 3 Then _ Cells(Target.Row, 1) = "701-000-000-" & Cells(Target.Row, 1) End Sub

Formeln druckenFormellösungTipp

Kategorien: Drucken/Seite und Tabelle ▸ Formeln

(Tipp 182) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich anstelle der Ergebnisse die Formeln drucken?

Sollen alle Formeln des Blattes gedruckt werden:

  • Optionen
  • Erweitert
  • Optionen für dieses Arbeitsblatt anzeigen
  • Anstelle der berechneten Werte Formeln in Zellen anzeigen

Alternativ geht es auch mit der Tastenkombination Strg und ` (Graviszeichen, Taste rechts neben ß mit Umschalttaste drücken).

Sollen nur ausgewählte Formeln gedruckt werden, kann man als erstes in die Zelle ein Hochkomma (') eingeben, dann wird der Inhalt der Zelle als Text behandelt. Das empfiehlt sich übrigens auch dann, wenn eine längere Formel nicht beendet ist, aber die Mappe geschlossen werden soll.

Ab Excel 365

Sollen die Formeln zusätzlich zu den Ergebnissen gedruckt werden, können die mit der Funktion FORMELTEXT() angezeigt werden:

=FORMELTEXT(A1)

Integrierte Dialogfelder aufrufenMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Dialoge

(Tipp 437) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Die integrierten Dialogfelder von Excel können auch mit VBA aufgerufen werden. Dies geschieht einfach mit:

Application.Dialogs(Konstante).Show

Bei den Dialogfeldern können verschiedene Argumente mitgegeben werden, die natürlich bei jedem Element anders sind. Hierzu am besten einfach in die Hilfe sehen.

Manchmal muss man sich entscheiden, ob ein integriertes Dialogfeld oder ein herkömmliches Dialogfeld für die Aufgabe besser geeignet ist. Mit z. B. Application.GetOpenFilename kann schön der Pfad abgefragt werden, was mit xlDialogOpen schon nicht mehr so einfach ist.

lfd. Nr.KonstanteName
1xlDialogActivateAktivieren
2xlDialogActiveCellFontSchrift
3xlDialogAddinManagerAdd-In-Manager
4xlDialogAlignmentAusrichtung
5xlDialogApplyStyleFormatvorlage
6xlDialogArrangeAllFenster anordnen
7xlDialogAutoCorrectAutokorrektur
8xlDialogBorderRahmen
9xlDialogCalculationBeschriftungsoptionen
10xlDialogCellProtectionZellschutz
11xlDialogClearInhalte löschen
12xlDialogColorPaletteFarboptionen
13xlDialogColumnWidthSpaltenbreite
14xlDialogConditionalFormattingBedingte Formatierung
15xlDialogConsolidateKonsolidierung
16xlDialogCopyPictureBild kopieren
17xlDialogCreateNamesNamen erstellen
18xlDialogCustomizeToolbarAnpassen
19xlDialogCustomViewsAnsichten
20xlDialogDataSeriesReihe
21xlDialogDefineNameNamen definieren
22xlDialogDefineStyleFormatvorlage
23xlDialogDeleteFormatZahlenformat
24xlDialogDeleteNameNamen definieren
25xlDialogDemoteGruppierung
26xlDialogDisplayBildschirmanzeigeoptionen
27xlDialogEditDeleteZellen löschen
28xlDialogFileDeleteDatei löschen
29xlDialogFileSharingArbeitsmappe freigeben
30xlDialogFilterAdvancedSpezialfilter
31xlDialogFindFileDatei suchen/öffnen
32xlDialogFormatAutoAutoformat
33xlDialogFormatNumberZahlenformat
34xlDialogFormulaFindSuchen
35xlDialogFormulaGotoGehe zu
36xlDialogFormulaReplaceErsetzen
37xlDialogGoalSeekZielwertsuche
38xlDialogImportTextFileTextdatei importieren
39xlDialogInsertZellen einfügen
40xlDialogInsertHyperlinkHyperlink einfügen
41xlDialogInsertNameLabelBeschriftungsbereiche
42xlDialogInsertObjectObjekt einfügen
43xlDialogInsertPictureBild einfügen
44xlDialogNewDatei - Neu
45xlDialogOpenDatei öffnen
46xlDialogOptionsCalculationOptionen: Berechnung
47xlDialogOptionsEditOptionen: Bearbeitung
48xlDialogOptionsGeneralOptionen: Allgemein
49xlDialogOptionsListsAddOptionen: Liste
50xlDialogOptionsTransitionOptionen: Umsteigen
51xlDialogOptionsViewOtionen: Ansicht
52xlDialogPageSetupSeite einrichten
53xlDialogPasteSpecialInhalte einfügen
54xlDialogPatternsFormat: Muster
55xlDialogPrintDrucken
56xlDialogPrinterSetupDruckereinrichtung
57xlDialogPropertiesDateieigenschaften
58xlDialogProtectDocumentBlatt schützen
59xlDialogRoutingSlipMailverteiler
60xlDialogRowHeightZeilenhöhe
61xlDialogRunMakro
62xlDialogSaveAsSpeichern unter
63xlDialogSelectSpecialInhalte auswählen
64xlDialogSendMailMappe als Mail
65xlDialogSetBackgroundPictureHintergrundbild
66xlDialogSetPrintTitlesDrucktitel
67xlDialogSortSortieren
68xlDialogUnhideTabelle einblenden
69xlDialogWorkbookAddBlatt verschieben/kopieren
70xlDialogWorkbookNameBlatt umbenennen
71xlDialogWorkbookNewTabelle usw. einfügen
72xlDialogWorkbookProtectArbeitsmappe schützen
73xlDialogZoomZoom

Download: integrierte_dialogfelder.xlsm

Makros auflisten

Kategorie: Add-In ▸ VB-Projekt

(Tipp 581) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Makros eines Projekts übersichtlich auflisten und Mappen/Blätter ohne Makros speichern?

Das Add-In sammelt alle (nicht geschützten) Codeteile eines Projekts und schreibt sie übersichtlich in eine Tabelle. So können diese auch druckfertig aufbereitet werden.

Weiterhin besteht im Add-In die Möglichkeit, zu wählen, ob die aktive Tabelle oder die aktive Mappe gespeichert werden soll und ob dabei Makros aus der Kopie entfernt werden sollen.

Hinweis:

Damit Makros Zugriff auf den VBA-Code haben, muss in Excel folgende Einstellung vorgenommen werden:

  • Datei > Optionen
  • Trust Center (alt: Vertrauensstellungscenter)
  • Einstellungen für das Trust Center
  • Makroeinstellungen
  • [X] Zugriff auf das VBA-Projektmodell vertrauen

Sonst erscheint eine Fehlermeldung.

Download: makros_auflisten.xlam



Makros immer zur Verfügung stellenTipp

Kategorie: Basics ▸ VBA

(Tipp 102) Nachricht zum Beitrag an Autor Nach oben

Wie kann man erreichen, daß die Makros immer zur Verfügung stehen, egal, welche Mappe gerade offen ist?

Makros werden in der Regel in der Mappe gespeichert, in der sie benötigt werden. Aber es gibt auch Makros, die immer zur Verfügung stehen sollen, die also sofort beim Aufruf von Excel bereit sein sollen.

Häufig liest man hierzu die Empfehlung, man solle die Makros in der sogenannten Persönlichen Makroarbeitsmappe speichern. Diese Methode hat jedoch gravierende Nachteile. Besser ist es, wenn man sich Add-Ins erstellt und diese über den Add-Ins-Manager einblendet.

Erstellen eines Add-Ins

  1. Zuerst erstellt man ganz normal seine Makros/VBA-Routinen, indem man sie aufzeichnet oder selbst schreibt (siehe hierzu auch Wo gibt man nun die Makros ein?).
  2. Anschließend wählt man in Excel in der Mappe, in der sich die Makros befinden, den Befehl Datei - Speichern unter und gibt einen aussagekräftigen Dateinamen ein.
  3. Dann wählt man im Dialogfeld ganz unten den Dateityp Microsoft Excel-Add-In (*.xlam). Daraufhin wechselt Excel automatisch in den Pfad, in dem sich standardmäßig die Add-Ins befinden - man kann den Ordner auch wechseln. Hauptsache ist natürlich, dass man später weiß, wohin man gespeichert hat.
  4. Mit einem Klick auf Speichern steht das Add-In sofort zur Verfügung. Excel speichert es und ergänzt den Namen mit der Endung .xlam.

Nun ist das Add-In gespeichert und steht zur Verfügung, arbeiten kann man damit aber noch nicht. Um das Add-In zu aktivieren, geht man wie folgt vor:

Einbinden des Add-Ins

Aktuell
  1. Datei ▸ Optionen
  2. Add-Ins
  3. Verwalten: Excel-Add-Ins ▸ Los
  4. Durchsuchen
  5. Add-In-Datei suchen
  6. OK
  7. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
  8. OK
Ab Excel 2007
  1. Office-Schaltfläche (oben links)
  2. Excel-Optionen
  3. Add-Ins
  4. Verwalten: Add-Ins > Gehe zu ...
  5. Durchsuchen
  6. Add-In-Datei suchen
  7. OK
  8. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
  9. OK
Frühere Excel-Versionen
  1. Extras
  2. Add-Ins-Manager
  3. Durchsuchen
  4. Add-In-Datei suchen
  5. OK
  6. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
  7. OK

Fertig - nun stehen alle Makros dieses Add-Ins immer zur Verfügung, auch, wenn man Excel beendet und neu startet.

Nachträgliches Bearbeiten der Makros

Möchte man die Makros nachträglich ändern oder ergänzen, ist dies auch kein Problem. Man wechselt mit der Tastenkombination Alt + F11 in den VBA-Editor, in dem man nun (standardmäßig) links oben im Projektexplorer den Namen des Add-Ins findet. Doppelklickt man darauf, werden die einzelnen Elemente (Tabellen, Module, usw.) sichtbar und durch einen Doppelklick auf das entsprechende Element sieht man den Code der/des Makros bereits vor sich und kann ihn bearbeiten. Nicht vergessen, zu speichern!

Add-In in normale Excel-Datei umwandeln

Normalerweise sieht man das Add-In nur im VBA-Editor, nicht aber in Excel. Manchmal möchte man aber aus dem Add-In wieder eine "normale" Mappe erstellen, damit man die Tabellen in Excel bearbeiten kann. Das ist auch kein Problem. Man gibt dazu einfach irgendwo diesen Code ein und führt ihn aus. Wechselt man nun nach Excel, hat man als Mappe das Add-In vor sich und kann wie in einer normalen Excelmappe arbeiten:

Sub Test() Workbooks("Name_des_Add-ins.xla").Isaddin = False End Sub

personl.xls

Auch mit einer Datei mit dem Namen PERSONAL.XLSB kann man Makros allgemein verfügbar machen. Dazu wählt man folgende Schritte:

  1. Menü Extras - Makro - Aufzeichnen
  2. Namen des Makros eingeben
  3. Bei Makro speichern in wählen: Persönliche Makroarbeitsmappe
  4. Schritte durchführen, die das Makro später ausführen soll
  5. Aufzeichnung beenden
  6. Excel beenden, Frage nach Speichern mit Ja beantworten
  7. Excel neu starten

Nun ist die Datei PERSONAL.XLSB automatisch gespeichert und wird bei jedem Excelstart mitgeöffnet. Sie kann auch bearbeitet werden, indem man mit Alt + F11 in den Editor wechselt.

Auch wenn oft empfohlen wird, zentrale Makros in dieser Datei zu speichern, rate ich davon ab. Im Unterschied zu einem Add-In ist sie eine Mappe, die beim Excelstart mit aufgerufen wird und kann über das Menü Fenster - Einblenden eingeblendet werden. Daraus könnten sich Probleme ergeben, wenn mit der Workbooks-Auflistung gearbeitet wird. So wirkt sich z. B. folgender Code auch auf die PERSONAL.XLSB aus:

For each x in Workbooks ... Next

Auf ein Add-In wirkt es sich nicht aus.