Category Archives: Excel-Objekte

Erzähle nicht, wie Du warst, sondern zeige, wie Du jetzt bist.

Wie oft muss ich es noch sagen: Programmiert sauber!

In einer Firma lief unter Excel 2007 ein Programm mit folgendem Code:

Dim BlattName

Sheets(„Diagramme“).Visible = True
Sheets(„nocheins“).Visible = True

BlattName = ActiveSheet.Name

Abgesehen davon dass die Variable „BlattName“ nicht sauber von Typ As String deklariert wurde, dass die Eigenschaft Visible eigentlich den Wert der Konstanten xlSheetVisible erhalten sollte und nicht True (True ist 1; xlSheetVisible ist -1) läuft es unter Excel 2016 an die Wand. Der Grund:

Nach Sheets(„Diagramme“).Visible = True ist der Fokus noch auf dem Blatt von dem aus der Code gestartet wurde. Werden jedoch zwei Blätter eingeblendet, wechselt Excel in der Version 2016 nun auf eines der eingeblendeten Blätter. Der Blattname lautet nun nicht mehr wird das ursprünglich aktive Blatt, sondern wie eines der Blätter, die zuvor ausgeblendet waren.

Das kann man gut mit einem Meldungsfenster verifizieren:

Sheets(„Diagramme“).Visible = True
Sheets(„nocheins“).Visible = True

BlattName = ActiveSheet.Name

MsgBox BlattName

Sauber programmieren heißt beispielsweise:

Dim BlattName As String
Dim xlBlattDiagramme As Worksheet
Dim xlBlattNochEins As Worksheet
Dim xlBlattAktual As Worksheet

Set xlBlattAktual = ActiveSheet
Set xlBlattDiagramme = Sheets(„Diagramme“)
Set xlBlattNochEins = Sheets(„nocheins“)

xlBlattDiagramme.Visible = xlSheetVisible
xlBlattNochEins.Visible = xlSheetVisible

BlattName = xlBlattAktual.Name
‚ wird eigentlich nicht mehr benötigt

MsgBox BlattName

 

Vorhin war’s noch da

Ich schaue mir eine fremde Datei an. Auf einem Tabellenblatt befindet sich ein Diagramm. Ich versuche herauszufinden, woher es die Daten bezieht. Die Quelle liegt auf einem anderen Blatt. Ich will zurück zu dem Blatt „Diagramm“ – doch das ist verschwunden.

Ich brauche schon eine Weile, bis ich dahiner komme:

In VBA befindet sich im Objekt Worksheet beim Ergebnis Deactivate folgender Befehl:

Private Sub Worksheet_Deactivate()
Sheets(„Diagramm“).Visible = xlSheetHidden
End Sub

Interessanter Mechanismus: Lieber Anwender, sobald du das Blatt verlässt, bekommst du es nicht mehr zu Gesicht. Natürlich kann man es sich über eine Schaltfläche wieder einblenden lassen …

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

Das Problem ist nicht mein leichter Knall. Das Problem ist jemanden zu finden, der einen möglichst kompatiblen Knall hat.

Ich versuche mittels VBA ein Bild auf 10 cm zu verkleinern. Beim ersten teil hilft der Makrorekorder. Jedoch: wenn das Bild nicht im Querformat, sondern im Hochformat vorliegt, muss ich die Height und nicht die Width verändern. Also gehe ich auf die Suche nach dem Befehl „Winkel“. In Visio heißt er Angle. Jedoch in Excel VBA?

Ein Blick in den Eigenschaften-Dialog (Größe und Position) zeigt: auf diesem Dialog heißt er „Drehung“. Und richtig: unter „Rotation“ werde ich fündig.

Kann Microsoft die Objekte, Eigenschaften und Methoden in den einzelnen Applikationen nicht gleich benennen? Es nervt!

Wer will schon den aalglatten Prinzen, wenn man schon den Hofnarren mit Dreitagebart haben kann?

Ist Ihnen das schon aufgefallen:
In einer Excelmappe gibt es zwei Tabellenblätter: Tabelle1 und Tabelle2. Tabelle1 liegt links; Tabelle2 rechts. Tabelle2 wird ausgeblendet.


Wenn man nun Tabelle1 kopiert: Kopie erstellen (ans Ende stellen) und anschließend Tabelle2 wieder einblendet: Liegt die Kopie nun links oder rechts von Tabelle2?


Die Lösung: sie liegt links von der ehemals ausgeblendeten Tabelle. „Ans Ende stellen“ heißt also: „Ans Ende der sichtbaren Tabellen stellen“. Ist das schlimm? Man sieht doch, wo die Tabellen liegen?
Die Antwort:
Wenn Sie per Programmierung ein Blatt in eine andere Datei kopieren, beispielsweise so:
Dim xlBlatt As Worksheet
Dim xlDatei As Workbook

Set xlDatei = Application.Workbooks.Open(„D:\Excel\Testdatei.xlsx“)
Set xlBlatt = ThisWorkbook.Worksheets(„Tabelle1“)
xlBlatt.Copy After:=xlDatei.Worksheets(xlDatei.Worksheets.Count)

MsgBox xlDatei.Worksheets(xlDatei.Worksheets.Count).Name
Nun liefert das Meldungsfenster nicht den Namen des kopierten Blattes, sondern den Namen des letzten Blattes (wenn es ausgeblendet war). Und: leider liefert die Methode Copy kein Objekt, also kein Verweis auf ein Tabellenblatt zurück.
Heißt: gut aufpassen! Sonst nervt das Ergebnis!

Ich bin im Niveau ganz flexibel

Ich versuche per VBA benutzerdefinierte Eigenschaften an eine Datei zu binden. Nichts leichter als das, denke ich:

20170126Benutzereigenschaften01

Man definiert eine Variable vom Typ CustomDocumentProperties (oder Property) und fügt zu der Sammlung ein weiteres Element mit der Methode Add hinzu. Sie möchte Name und Value. Klingt vernünftig. Ich werde jedoch eines Besseren belehrt:

20170126Benutzereigenschaften02

Typen unverträglich? Okay – dann ohne Objektverweis:

20170126Benutzereigenschaften03

Falsch Anzahl an Argumenten? Aber IntelliSense hat mir doch … Ein Blick in die Hilfe verrät, dass ich die CustomDocumentProperties vom Typ DocumentProperties deklarieren muss. Aha:

20170126Benutzereigenschaften04

Und richtig: Dort wird noch zwingend der Parameter „LinkToContent“ verlangt.

Nächster Test:

20170126Benutzereigenschaften05

??? Etwas probieren und schon habe ich die Lösung: Obwohl die Eigenschaft „Type“ in eckigen Klammern, also optional, angegeben wurde, ist dieser Wert zwingend erforderlich.

Kaum probiert man eine halbe Stunde – schon klappt es auch. Flexibilität braucht man schon – nicht nur im Niveau:

20170126Benutzereigenschaften06

we are not amused

Wirklich erstaunlich. In einer Excel sind mehrere Tabellenblätter ausgeblendet:

ausgeblendete Tabellenblätter

ausgeblendete Tabellenblätter

Die Codezeile:

ActiveWorkbook.Worksheets(„Tabelle4“).Activate

selektiert das letzte Blatt und liefert keinen Fehler.

Auch folgender Sachverhalt ist amüsant und erstaunlich:

Der Teilnehmer der VBA-Schulung wollte die Anzahl der Zeilen eines Bereiches mit:

MsgBox ActiveSheet.Range(„A1“).CurrentRegion.Rows.Count

ermitteln. Statt dessen vertippte er sich und schrieb:

MsgBox ActiveSheet.Range(„A1“).CurrentRegion.Count

CurrentRegion.Count liefert die Anzahl der Zellen des Bereichs. Ich hätte etwas anderes erwartet:

20160628Currentregion

Ich rücke ja schon – aber ich sehe den Schlauch nicht auf dem ich stehe

Hi. Was heißt hier „Objekt erforderlich“. Ich finde den Fehler in der Zeile

lngZeilen = xlZielZelle.CurrentRegion.Rows.Count

nicht. Einige Zeilen zuvor habe ich doch gesetzt:

Set xlZielZelle = xlZielBlatt.Range(„A1“)

Warum mag VBA das Objekt nicht?

Wo ist das fehlende Objekt?

Wo ist das fehlende Objekt?

Die Antwort: Dahinter liegt ein hübscher Denkfehler: Es ist richtig: Sie setzen

Set xlZielZelle = xlZielBlatt.Range(„A1“)

Allerdings mit

xlZielBlatt.Rows(„1:3“).Delete Shift:=xlUp

löschen Sie drei Zeilen, damit auch die Zelle A1 und damit wiederum den Verweis auf diese Zelle. Also – einfach noch einmal setzen nach dem Löschen:

Set xlZielZelle = xlZielBlatt.Range(„A1“)

Dann klappt es.

 

Workbooks – zwei Seelen wohnen ach in meiner Brust

In VBA bedeutet die Collection Workbooks einmal die Sammlung aller schon offenen Dateien, einmal die Sammlung der noch nicht offenen Dateien. Ist das nicht unlogisch?

Workbooks - wat is dat?

Workbooks – wat is dat?

Das ist richtig – das ist nicht ganz glücklich formuliert. Gemeint ist: Workbooks.Count, beziehungsweise Workbooks(1) aus der Sammlung der Dateien wird die Dateianzahl, beziehungsweise das Element mit der Nummer 1 herausgegriffen. Workbooks.Open, beziehungsweise Workbooks.Add bedeutet: Zu der Sammlung wird ein neues Element hinzugefügt (eine neue Instanz eingefügt). Sie haben recht: vielleicht hätte man zwei verschiedene Begriffe wählen sollen – Programmieranfänger wundern sich immer ein wenig …

 

Wo ist der Remote-Server-Computer

In einem Programm, das ich in VBA geschrieben habe, erscheint manchmal die Fehlermeldung: Der Remote-Server-Computer existiert nicht oder ist nicht verfügbar. Wo bitte steht denn mein Remote-Server-Computer?

Wo ist der Remote-Server-Computer?

Wo ist der Remote-Server-Computer?

Diese Meldung kann zwei Ursachen haben. Entweder Sie greifen mit einer Objektvariablen auf ein anderes Programm (beispielsweise Word oder Access) zu und schließen per Hand (oder per Programmierung) dieses Programm.

Oder Sie greifen auf ein anderes Programm zu und löschen nicht „sauber“ die Objektvariablen. Auch wenn Microsoft behauptet, dass eine Garbage-Collection die Variablen „sauber“ putzen würde, stelle ich ab und zu (nicht immer!) fest, dass dies nicht der Fall ist. Also, wenn Sie beispielsweise per Programmierung Word öffnen (Sie müssen natürlich einen Verweis auf die Word-Bibliothek setzen):

Dim wdApp As Word.Application
Dim wdDatei As Word.Document
Set wdApp = New Word.Application
wdApp.Visible = True
Set wdDatei = wdApp.Documents.Add

Dann sollten Sie am Ende sämtliche Objektvaribalen leeren. Und zwar so.
Set wdDatei = Nothing
Set wdApp = Nothing

Und bitte auch in der richtigen Reihenfolge – von „klein“ nach „groß“.

Aufzählungslisten

Kennen Sie das? Sie verwenden ein Objekt in VBA, setzen den Punkt dahinter, aber VBA verweigert die Anzeige der Auflistung.

Während Range(„A1“). funktioniert, geht es beispielsweise bei Cells(1, 1) nicht.

Während es bei ActiveWorkbook klappt, funktioniert es bei ActiveSheet nicht.

Ich weiß nicht, warum es bei den meisten Objekten funktioniert, bei einigen jedoch nicht. Aber ich weiß, wie man immer die Aufzählungsliste angezeigt bekommt:

Verwenden Sie eine Objektvariable. Also beispielsweise so:

Dim xlBlatt As Worksheet
Set xlBlatt = ActiveSheet
xlBlatt. <- Hier funktioniert es

Dim xlZelle As Range
Set xlZelle = Cells(1, 1)
xlZelle <- Hier funktioniert es

Nicht immer erhält man die Listen der Eigenschaften und Methoden.

Nicht immer erhält man die Listen der Eigenschaften und Methoden.