Ich zeichne mit dem Befehl „Aktionen aufzeichnen“ mit OfficeSkript den Befehl „benutzerdefiniertes Zahlenformat #.##0,00 „km“ auf:
Das Skript sieht so aus:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set number format for range C2:C6 on selectedSheet
selectedSheet.getRange("C2:C6").setNumberFormatLocal("#,##0.00 \"\"km\"\"");
}
Ich lasse es abspielen:
Analog: ich zeichne eine Summe auf. Der Code:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set range C8 on selectedSheet
selectedSheet.getRange("C8").setFormulaLocal("=SUM(C2:C7)");
}
Das Ergebnis:
In die Zelle wird SUM statt SUMME eingetragen – Excel schafft die Lokalisierung noch nicht. Da muss Microsoft noch einmal ran …
Eigentlich wollte ich nur einen Befehl mit Office Skript aufzeichnen: formatiere den Text einer Zelle fett:
Geht nicht:
„Leider ist ein Problem aufgetreten. Wir können Ihr Skript nicht speichern. Unerwarteter Fehler beim Generieren und Speichern des Skripts. Klicken Sie auf „Aktionen aufzeichnen“, um erneut aufzuzeichnen.
Erstaunt starre ich auf den Bildschirm. Ich habe keine Ahnung, warum und woher das Problem rührt.
VBA-Schulung. Wir üben den Makrorekorder. Ändert man die Schriftart über das Dropdownsymbol in der Registerkarte „Start“,
zeichnet der Makrorekorder auf:
Sub Schriftart()
'
' Schriftart Makro
'
'
With Selection.Font
.Name = "Algerian"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
Also: der ganze Dialog (oder genauer: die Registerkarte „Schrift“ des Dialogs „Zeichen formatieren“) wird aufgezeichnet.
Ein Teilnehmer fragt, was denn aufgezeichnet wird, wenn man im Dialog „Zeichen formatieren“ die Schrift ändert und dies aufzeichnet. Peinlich: ich weiß es nicht. Wir zeichnen auf. Das gleiche Ergebnis – auch hier wird nicht der gesamte Dialog (wie beispielsweise beim „Seite einrichten“) aufgezeichnet, aber auch nicht nur der eine Befehl, der geändert wurde, sondern sämtliche Befehle des Registers:
VBA-Schulung. Ich zeige und erkläre den Makrorekorder.
Aufgabe: Zeichnen Sie ein Makro auf, das eine Zelle formatiert, beispielsweise: fett, kursiv, Schriftart, Schriftgröße, Ausrichtung, Hintergrundfarbe, … und testen es an einer anderen Zelle.
Eine Teilnehmerin beschwert sich, dass der Makrorekorder nicht „optimale Spaltenbreite“ aufzeichnet.
Was hat sie gemacht? Sie hat ihr Makro in einer anderen Zelle in einer anderen Spalte ausprobiert. Leider zeichnet der Makrorekorder „hart“ den Spaltennamen auf, beispielsweise:
Columns("D:D").EntireColumn.AutoFit
Später erkläre ich den Unterschied zwischen der relativen und der absoluten Aufzeichnung
deine Mails mit positiven Informationen und Anhang erreichten mich.
Themenwechsel 3 von 3:
Falls du an weiteren Fragen rund um EXCEL-VBA Interesse hast, informiere mich einfach. Dazu habe ich dir zwei Dateien mitgesendet. Die Lösung der Probleme ist kein MUSS.
Die Thematiken:
(1) ein Filterproblem ist bestimmt nicht so schwierig zu lösen. Eine Liste:
Kennst du Anrufe, die beginnen mit einem „hast du mal einen Moment Zeit?“ oder: „störe ich gerade?“
Richtig: Angelika rief am Wochenende an. Meine Kollegin Angelika. Sie bereitet gerade einen Excel-Makro-Kurs vor und suchte einige gute Gedanken für Befehle, die man mit dem Makrorekorder aufzeichnen könne. Dabei stieß sie auf die benutzerdefinierte Kopfzeile.
„Sag mal“, fragte sie, „wenn ich in der Kopfzeile aufzeichne: Seitennummerierung, Datum, Dateiname und Tabellenblattname passiert etwas ganz Komisches.“
Zuerst glaubte ich es nicht. Also – auch aufgezeichnet:
Eigentlich alles okay. Ich habe keine Ahnung, was hier passiert!
Ich rate Angelika, im Makrokurs nur den Firmennamen als Text aufzuzeichnen. Das klappt!
Okay – für Datum und Uhrzeit stehen die VBA-Befehle Date und Time zur Verfügung; für den Dateiname ActiveWorkbook.Name (oder Fullname), für den Blattnamen ActiveSheet.Name, aber für die Seitennummer? Ich weiß keine Lösung.
Danke an Angelika Meyer für diesen Hinweis!
Und nur wenig nach der Veröffentlichung des Artikels erreicht mich ein Kommentar von Ernst. Großartig – DAS ist des Rätsels Lösung! Hier sein Kommentar:
Hallo Rene,
dieses eigenartige Verhalten tritt bei mir (Excel 2019) nur dann auf,
wenn vor dem Festlegen der PageSetup-Eigenschaften die
Application.PrintCommunication-Eigenschaft auf False gesetzt wird. Wenn
die Kommunikation mit dem Drucker nicht abgeschaltet wird, tritt dieses
Verhalten nicht auf.
Bei der Makroaufzeichnung werden die Befehle
Application.PrintCommunication = False und Application.PrintCommunication = True
verwendet.
Bei Microsoft steht wohl folgender Hinweis:
Legen Sie die PrintCommunication-Eigenschaft auf False fest, um die
Ausführung von Code zu beschleunigen, mit dem PageSetup-Eigenschaften
festgelegt werden.
Legen Sie die PrintCommunication-Eigenschaft nach dem Festlegen der
Eigenschaften auf True fest, um alle im Cache vorhandenen
PageSetup-Befehle auszuführen.
In einem Formular steht eine Formel. Okay – es befinden sich eine Reihe an Formeln dort – aber einige sind besonders lang. Manchmal soll die Zelle mit einem Wert überschrieben werden und nicht mehr variablen sein (wenn bestimmte Voraussetzungen erfüllt werden, welche die Formel nicht abbildet); dann wiederum soll die Formel zurückgesetzt werden. Also gehe ich auf die Suche – wie heißt der VBA-Code der Formel. Die Berechnung lautet:
Merkwürdig. Ich importiere per VBA Daten aus anderen Tabellen in eine Arbeitsmappe. Die Daten der Importtabelle sind als intelligente Tabelle gespeichert, in der aktuellen Tabelle liegt auch eine intelligente Tabelle. Damit sich diese beiden Tabellen nicht überlagern, ist es wohl das Beste die Tabelle zu löschen. Aber wie heißt der Befehl „In Bereich konvertieren“?
Nun – der Makrorekorder hilft:
Sub ZurueckZuDummerTabelle()
'
' ZurueckZuDummerTabelle Makro
'
'
End Sub
Nein – der Makrorekorder hilft nicht! Er zeichnet nur den Befehl auf: lösche das Tabellenformat. Aber nicht: lösche die Tabelle. Also muss ich doch auf die Suche gehen. Ich werde schnell fündig: die Methode heißt Unlist. Damit klappt es:
For j = 1 To xlBlatt.ListObjects.Count
xlBlatt.ListObjects(j).TableStyle = ""
' -- lösche die Formtierung
xlBlatt.ListObjects(j).Unlist
' -- in Bereich (zurück) konvertieren
Next j
Interessant finde ich, dass man erst googlen muss, um herauszufinden, dass die Inversmatrix in Excel als Funktion MINV heißt und bei VBA als Worksheetfunction MINVERSE (sogar mit E am Schluss, da kommst echt net drauf von allein)…
Hallo Axel,
Zwei Tipps, damit die nicht googln musst, was MINV heißt:
Der Translator von Mourad Louha (excel-translator.de) Oder du verwendest die Formel, schaltest den Makrorekorder ein, editierst die Formel (bei mir: [F2]) und Makrorekorder STOPP. Er „übersetzt“ dann die Funktionen ins Englische.
Übrigens: Schöne Skizze:- da steckt viel Hirnschmalz drin …
Und stutze, weil das Objekt „Picture“ nicht in der Liste der Objektvariablen angeboten wird. Ich versuche es trotzdem:
Das Makro
Sub BildEinfuegen()
Dim xlBlatt As Worksheet
Dim xlBild As Picture
Set xlBlatt = ActiveSheet
Set xlBild = xlBlatt.Pictures.Insert("F:\Eigene Bilder\Erdbeertörtchen.JPG")
End Sub
läuft hervorragend. Okay – und was heißt: Bilder verschieben und verkleinern? Der Makrorekorder liefert:
Merkwürdig – ich hänge den veränderten Code an mein Makro:
Dim xlBlatt As Worksheet
Dim xlBild As Picture
Set xlBlatt = ActiveSheet
Set xlBild = xlBlatt.Pictures.Insert("F:\Eigene Bilder\Erdbeertörtchen.JPG")
With xlBild
.Width = .Width * 0.3169191919
.Height = .Height * 0.3169191919
.Left = 252
.Top = 54.75
.Visible = True
End With
HIER wird das Picture-Objekt erkannt. Und: läuft doch!
Ich melde mich dieses mal mit einer generellen Frage an Sie. Im Moment nervt mich nämlich PowerPoint. Es ist schon gemein, dass es da keinen Makrorekorder gibt.
Ich schätze mal, dass auch Sie ab und zu den Makrorekorder in Excel benutzen, wenn Ihnen mal dieser und jener Befehl nicht einfällt.
Nun, in PowerPoint geht das ja leider nicht. Daher meine Frage, wie gehen Sie eigentlich vor oder wo lesen Sie nach, wenn Sie PowerPoint-Kommandos benötigen und gerade nicht wissen, wie diese lauten?
Momentan beschäftige ich mich mit einer automatisierten Berichtserstellung aus Excel heraus. Das klappt auch alles soweit ganz gut, also neue Präsi aus Vorlage erstellen, Daten aus Excel in Diagramme oder Tabellen einfügen oder auch Diagramme von Excel nach PowerPoint zu exportieren. Schwierig wird es nur dann, wenn ich z.B. Formatierungen ausschließlich in PowerPoint durchführen muss. Ich habe z.B. ewig gebraucht um dahinter zu kommen, wie ich in einer PowerPoint-Tabelle eine Zelle farblich hervorhebe (Präsi.Slides(5).Shapes(„Inhaltsplatzhalter 3“).Table.Cell(a, 1).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)). Oder einen Pfeil einfügen (ActivePresentation.Slides(1).Shapes.AddShape Type:=msoShapeBentUpArrow, Left:=50, Top:=50, Width:=100, Height:=200), mit Animationen fange ich gar nicht erst an.
Haben Sie da einen Tipp oder ein schlaues Nachschlagewerk? Jemand hat mir mal erzählt (ich glaube, es war Andreas Thehos), dass man mit dem Makrorekorder in Word hier so einiges ableiten kann, aber da bin ich irgendwie noch nicht dahinter gekommen…
Ihnen schon einmal vielen Dank und viele Grüße,
Hallo Herr Dauphin,
das Problem des fehlenden Makrorekorders kenne ich –
darüberhinaus gibt es noch weitere Fallstricke:
Heute in der VBA-Schulung in einem chemischen Konzern. Wir üben Makrorekorder: ein benutzerdefiniertes Zahlenformat wird aufgezeichnet:
Eine Teilnehmerin fragte mich, warum Excel „Wasser“ in die Zelle einfügt. Ich habe zwei Mal hinschauen müssen, bis ich entdeckt hatte, dass Sie versehentlich auf die Zelle H20 geklickt hat und dies per Makrorekorder aufgezeichnet hat …
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:
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
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:
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.“
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 ?!?
Ein Teilnehmer fragte mich, warum sein Markorekorder nicht funktioniert. Tatsächlich: Beim Versuch ein Makro aufzuzeichnen kam eine erstaunliche Fehlermeldung:
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 …
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 …
Übrigens: In Excel 2013/2016 gibt es nur noch ein Symbol für Pivottabelle.
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 …
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.
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:
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!
Sie müssen dann in der Hilfe, im Objektkatalog, im Internet oder in einem guten Buch nachschlagen wie der zugehörige VBA-Befehl lautet.
