Category Archives: Makrorekorder

Wenn du aufhörst, über mich Lügen zu erzählen, werde ich aufhören, über dich Wahrheiten zu verbreiten.

Schon wieder bin ich erstaunt. Ein Kunde möchte, dass seine Pivottabelle, die ich mit VBA erzeuge, kursiv formatiert wird. Da ich keine Ahnung habe, wie der Befehl lautet ein Pivottabellenformat für das Stripset zu ändern, greife ich auf den Makrorekorder zurück. Er zeichnet auf:

Sub Kursiv()
ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1). _
Clear
ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1). _
Font.FontStyle = „Kursiv“
With ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements( _
xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.799981688894314
End With

[…]

Ich bin verblüfft: der Befehl

.Font.FontStyle = „Kursiv“

sieht nicht sehr sprach- und länderunabhängig aus. Ich teste – es läuft. Ich ändere die Zeile in die mir bekannte Eigenschaft Italic:

ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1).Font.Italic = True

Und: es läuft noch immer! Dieser Befehl gefällt mir besser. Makrorekorder – warum kannst du das nicht selbst?

Being funny is not everyone’s first choice

Auf nichts ist mehr Verlass!

Für einen Kunde habe ich ein Add-In für Excel erstellt. Am Ende werden daten in einer Pivottabelle zusammengefasst. Der Kunde möchte noch ein bisschen Farbe ins Spiel bringen. Kein Problem, denke ich – in Pivottabellen lustige Formatierungen einschalten kann man mittels der Pivottable-Formate erreichen. Doch wie heißen sie in Excel? Der Makrorekorder hilft. Ich zeichne die Farbe auf:

ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1). _
Clear
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399914548173467
End With

Wunderbar!

Und Linien? Ich zeichne auf:

With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeTop)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeBottom)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeLeft)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeRight)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With

[…]

Ich teste – lasse den Code abspielen. Was passiert? Nichts! Teste erneut, …

Ich habe eine Weile benötigt, bis ich gesehen hat, dass der Makrorekorder eine dünne schwarze Linie mit der Eigenschaft LineStyle = xlNone aufzeichnet hat ?!? Also: raus damit. Code noch „putzen“ – und schon läuft es!

xlDateiNeu.TableStyles.Add „Rene-Stil“

With xlDateiNeu.TableStyles(„Rene-Stil“)
.ShowAsAvailablePivotTableStyle = True
.ShowAsAvailableTableStyle = False
.ShowAsAvailableSlicerStyle = False
.ShowAsAvailableTimelineStyle = False
End With

xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).StripeSize = 2
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399945066682943
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeTop)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeBottom)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeLeft)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeRight)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With

I ¤ non ASCII-characters

Letzte Woche in der Excel-VBA-Schulung. Um die Objekte näher zu bringen, beginne ich mit mit dem Makrorekorder. Wir zeichnen eine Reihe Befehle auf. Beispielsweise:

Füge in die Kopfzeile ein Bild ein:

ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
„F:\Eigene Bilder\Bali\PIC00020.jpg“
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = „“
.PrintTitleColumns = „“
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = „“
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = „“
.CenterHeader = „“
.RightHeader = „&G“
.LeftFooter = „“
.CenterFooter = „“
.RightFooter = „“
[…]

Das Ergebnis:

Wir löschen das Bild und führen das Makro erneut aus. Das Ergebnis: nichts! Ein Blick in den Dialog „Seite einrichten“ zeigt jedoch, dass etwas in der Kopfzeile ist. Ein Bild?

Der Grund ist schnell gefunden: Die Zeile:

Application.PrintCommunication = False

„Gibt an, ob die Kommunikation mit dem Drucker aktiviert ist.“ Deshalb wird zwar das Bild eingefügt aber nicht angezeigt. Also: Zeile löschen – und schon funktioniert es. Manchmal (oft!?!) liefert der Makrorekorder eben doch nicht den besten Code …

Übrigens: auf der Microsoft-Seite findet sich folgende Erklärung:

„Legen Sie die PrintCommunication-Eigenschaft auf False fest, um die Ausführung von Code zu beschleunigen, der PageSetup-Eigenschaften festlegt. Legen Sie die PrintCommunication-Eigenschaft auf True fest, nachdem Sie Eigenschaften zum Ausführen eines Commits aller zwischengespeicherten PageSetup-Befehle festgelegt haben.“

Aha!

 

Wer Rechtschreibfehler findet darf sie behalten.

Amüsant: Ich darf eine Zelle „mfg“ nennen, also ihr den Namen „mfg“ geben.

Aber „mfg2“ darf ich sie nicht nennen:

Der Grund ist einleuchtend: da man Namen über das Namensfeld (links neben der Bearbeitungsleiste) vergeben kann, würde ein dort eingegebener Name zur Zelle MFG2 springen. Deshalb dürfen auch nicht die Namen „MF2“ oder „M2“ vergeben werden. Nur „mfg_2“.

Nicht mehr verständlich ist es jedoch, wenn Sie ein Makro mit dem Makrorekorder aufzeichnen, das sie „mfg2“ nennen. DAS ist nicht erlaubt.

Ganz unverständlich wird die Sache jedoch, wenn Sie im Visual Basic-Editor ein Makro erstellen, das den Namen „mfg2“ trägt. DORT ist der Name erlaubt und bereitet keine Probleme ?!?

Es gibt Dinge über die rede ich nicht einmal mit mir selbst.

Amüsante Sache heute in der VBA-Schulung:

Ein Teilnehmer fragte mich, warum sein Markorekorder nicht funktioniert. Tatsächlich: Beim Versuch ein Makro aufzuzeichnen kam eine erstaunliche Fehlermeldung:

20161121makro01

Komisch?!

Komisch?!

Zuerst schaute ich nach, ob noch ein Makro läuft. Nein. Alles war okay – das Stoppen brachte nicht den gewünschten Erfolg. Dann sah ich es: in einem Modul war ein fehlerhaftes Makro – wahrscheinlich hatte der Teilnehmer beim Kopieren das Sub – End Sub vergessen.

Übrigens: Trotz Fehlermeldung konnte das Makro aufgezeichnet werden …

20161121makro03

Rekorder kaputt?

Die häufigste Frage in der letzten VBA-Schulung war: Warum funktioniert mein Makrorekorder nicht:

20160629Makro01

Das kann verschiedene Ursachen haben (und ich glaube, meine Teilnehmer haben alle gefunden):

 

In VBA läuft noch ein Programm: Ausschalten!

20160629Makro02

Der Cursor befindet sich in einer Zelle: Raus!

20160629Makro03

Die Arbeitsmappe wurde freigegeben: Aufheben!

20160629Makro04

Der Änderungsmodus wurde eingeschaltet: Aufheben!

20160629Makro05

Das Projekt wurde mit einem Kennwort geschützt: Aufheben!

20160629Makro06

Modern ist, was man selbst trägt. Unmodern ist, was andere tragen (Oscar Wilde)

Hätten Sie es gewusst? Oder verstanden? Wie lautet die VBA-Eigenschaft (oder -Methode) für klassisches Design einer Pivottabelle?

Klassisches Design

Klassisches Design

Die Antwort: Der Makrorekorder leistet hier gute Dinge:

With ActiveSheet.PivotTables(„xyz“)
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With

Ich gestehe: Alleine wäre ich nie darauf gekommen.

Wenn zwei das Gleiche tun, ist es noch lange nicht dasselbe

Der Makrorekorder zeichnet manchmal erstaunlich Dinge auf. Vor allem ist unverständlich, warum er für den gleichen Befehl völlig unterschiedlichen Code aufzeichnet. In der letzten VBA-Schulung stellten wir fest, dass Einfügen / PivotTable in Excel 2010 entweder folgendes Makro aufzeichnet:
Sub Pivot01()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
„Datenbank!R1C1:R76C6″, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=“Tabelle1!R3C1″, TableName:=“PivotTable1“, _
DefaultVersion:=xlPivotTableVersion14
End Sub
Oder folgendes:

Sub Pivot2()
Sheets.Add
ActiveWorkbook.Worksheets(„Tabelle1“).PivotTables(„PivotTable1″).PivotCache. _
CreatePivotTable TableDestination:=“Tabelle2!R3C1“, TableName:= _
„PivotTable2“, DefaultVersion:=xlPivotTableVersion14
End Sub

Je nachdem, ob man auf das obere Symbol oder das untere klickt …

20160626Pivot02

20160626Pivot01

Übrigens: In Excel 2013/2016 gibt es nur noch ein Symbol für Pivottabelle.

Zu viel Code

Kennen Sie das? Wenn Sie einen Befehl – einen einzigen Befehl mit dem Makrorekorder aufzeichnen, zeichnet Excel sehr, sehr viel Code auf. Warum?

Wenn Sie beispielsweise den Befehl aufzeichnen – Schrift ändern, dann erhalten Sie:

With Selection.Font
.Name = „Arial“
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

Wenn Sie aufzeichnen „schreibe in die Kopfzeile den Firmennamen“ erhalten Sie über 50 Zeilen Code für einen Befehl (ActiveSheet.PageSetup.RightHeader = „xyz“ hätte genügt):

... zu viel Code ...

… zu viel Code …

Der Grund: Excel zeichnet an vielen Stellen nicht den einen Befehl, sondern sämtliche Befehle des Dialogfeldes auf, also beispielsweise „Zellen formatieren“ oder „Seite einrichten“. Nun sind Sie gefragt, um die nicht nötigen Zeilen zu löschen.

Makrorekorder zeichnet falschen Code auf

Was passiert denn hier? Ich zeichne mit dem Makrorekorder etwas auf, rufe den Befehl auf – und erhalte eine Fehlermeldung.

Die Antwort: Das ist richtig. An einigen (zugegeben, sehr wenigen) Stellen zeichnet Excel falsch auf. Wenn Sie beispielsweise die Datenüberprüfung mit einer Formel aufzeichnen:

Makrorekorder

Makrorekorder

zeichnet Excel folgenden Code auf:

With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlLessEqual, Formula1:=“=TODAY(“
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = „“
.ErrorTitle = „“
.InputMessage = „“
.ErrorMessage = „“
.ShowInput = True
.ShowError = True
End With

Erstaunlicherweise fehlt nach dem TODAY die schließende Klammer.

Oder Sie zeichnen die Formel auf:

=rechts(F2;1)<>“ “

also – verbiete am Ende des Textes ein Leerzeichen.

Der Makrorekorder

Der Makrorekorder

Dann zeichnet der Makrorekorder auf:

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=“=rechts(F2;1)<>““ „““

Der korrekte Befehl müsste jedoch lauten:

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=“=right(F2,1)<>““ „““

Also in der US-amerikanischen Schreibweise. Der Makrorekorder schafft die Übersetzung an dieser Stelle leider nicht.

Der Makrorekorder zeichnet nicht auf

Das ist richtig: Eine Dinge werden nicht aufgezeichnet. Beispielweise die Eingabe der Eigenschaften. Wenn Sie diesen Befehl aufzeichnen, erhalten Sie ein leeres Makro:

Der Makrorekorder zeichnet nicht auf!

Der Makrorekorder zeichnet nicht auf!

Sie müssen dann in der Hilfe, im Objektkatalog, im Internet oder in einem guten Buch nachschlagen wie der zugehörige VBA-Befehl lautet.