Bedingte Formatierung mit VBA

Bedingte Formatierung mit VBA

Ein wiederkehrendes Thema bei Controllern, die mit SAP Reportings arbeiten – egal ob ERP Tabellenexporte oder wie ich mit BW oder Analysis für Office ist die Formatierung der Tabellen. 

Während ich eigentlich nicht gerne Flat Files liefere, lechzen die Endkunden nach schönen Farben. Hier habe ich für unsere Abteilung eigentlich mit einem eigenen Ribbon schöne Farbsets aufgrund unseres CD erstellt. Wenn die Tabellen jedoch regelmässig erstellt werden arbeite ich sehr gerne mit bedingten Formatierungen, damit sie immer gleich aussehen. 

Ein einfacher Code für bedingte Formatierungen:

Hier einige Erklärungen zu den Befehlen:

  • In Zeile 3 lösche ich sämtliche enthalten bedingten Formatierungen. Eine Krankheit von Excel mit bedingten Formatierungen ist, dass nach längerem Gebrauch ein Chaos herrscht. Diese machen sich mit Copy und Paste und beim Bearbeiten der Dateien ’selbstständig‘. Ich habe schon Files mit 1000 Einträgen mit bedingten Formatierungen gesehen. MIt einer Codezeile löscht man die alle…
  • In Zeile 5 markiere ich den Bereich, wo ich die bedingten Formatierungen erstelle. In Zeile 17 weite ich diese dann aus (ModifyApplies to Range). Man könnte natürlich schon in Zeile 3 alles markieren. Ist hier nur so dargestellt, damit auch die nachträgliche Änderung bekannt ist.
  • Mit dem restlichen Code sage ich dann: Wenn es in der Spalte E einen Wert hat, dann mache die Spalten E bis N fett und gelb hinterlegt. Die Grenzen sind endlos….
  • Wenn ich bedingten Formatierungen arbeite, dann verwende ich meistens die Formelfunktion. Wenn eine Bedingung erfüllt ist (WAHR), dann formatiere.  Es gibt auch viele andere Möglichkeiten. Probiere es aus. 
Veröffentlicht unter Excel VBA | Schreib einen Kommentar

Worksheet Creator

Ein weiteres kleines Excel Makro „Worksheet Creator“ .

– Pro Wert in der Liste wird ein separates Tabellenblatt erstellt.

 

 

 

 

 

 

Das Makro dahinter:

Sub ws_creator()

Dim rng As Range

For Each rng In Selection

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = rng

Next rng

End Sub

Veröffentlicht unter Excel, Excel VBA | Schreib einen Kommentar

Excel Indirekt –> Flexibel

Was macht man, wenn man in einer Tabelle bspw. mit Summewenn aus mehreren Tabellenblättern Werte holen will?

In einer Spalte steht, aus welchem Tabellenblatt die Informationen kommen sollen. Die Quell Tabellenblätter sehen identisch aus.

Funktioniert bestens mit Indirekt

(Auszug aus einer Datei: In der Spalte vorner stehen die Nummern der Tabellenblätter).

 

 

 

 

Die gewohnte Summwennformel sieht etwas speziell aus:

F237 ist hier der Verweis auf die (Flexible) Zelle mit dem Tabellenblatt, der Rest der Formel muss in Anführungs- und Schlusszeichen “ gesetzt werden. Dies ist dann der statische Teil.

Ohne Dynamik würde die gleiche Formel so aussehen:

Veröffentlicht unter Excel, Office | Schreib einen Kommentar

Aussichtstürme und -plattformen

Aussichtstürme und -plattformen

Unvollständig und Projekt für was anderes.

gelb –> Projekte
rot –> abgerissen
rest –> Besucht

Veröffentlicht unter Uncategorized | Schreib einen Kommentar

Excel : Array abfüllen

Eine einfache Möglichkeit, Arrays abzufüllen und Standards zu setzen. (ZB. wenn BO Queries die Tabellenköpfe wüst abfüllt).

Sub scm()

Dim scm_arr

scm_arr = Array(„“, „TATP“, „Auftrag ID“, „Auftrag“, „Projekt ID“, „Projekt“, „Planposition ID“, „Planposition“, „Teilprojekt“, „Kostenart ID“, „Kostenart“, „Kreditor ID“, „Kreditor“, „Belegart“, „Beleg Nr.“, „Belegtext“, „Geschäftsjahr“, „Periode/Jahr“, „Periode“, „Belegdatum“, „CHF“)

For i = 1 To UBound(scm_arr)

Debug.Print i

Cells(1, i).Value = i
Cells(2, i).Value = scm_arr(i)

Next i

Bemerkung: mit dem „“ am Anfang des Array fülle ich die Position 0 der Kette mit einem Blank. Der Vorteil ist dann die Steuerung der Loops, die immer bequem mit 1 beginnen und keine Kniffe benötigen.

Veröffentlicht unter Excel, Uncategorized | Schreib einen Kommentar

Funktion : Verketten2

Man kennt im Excel das verketten:

=Verketten(A1;A2;A3) verbindet mehrere Zellen.

Beispiel:

 

 

Etwas mühsam, wenn man oft, viele Zellen verknüpfen will. Einfacher ist die folgende Funktion:

=verketten2(range,trennzeichen)

 

 

 

Möglich mit diesem Makro:

Function Verketten2(ByRef bereich As Range, Trennzeichen As String) As String

Dim rng As Range

For Each rng In bereich
If rng <> „“ Then
Verketten2 = Verketten2 & rng & Trennzeichen
End If
Next

If Len(Verketten2) > 0 Then _

Verketten2 = Left(Verketten2, Len(Verketten2) – Len(Trennzeichen))
End If

End Function

Veröffentlicht unter Excel | Verschlagwortet mit , | Schreib einen Kommentar

Zahlensysteme

Zahlensysteme konvertieren

Einfaches Konvertieren von Binären, Hexa, Dezimal, Binäry und Ascii Nummern

https://www.binaryhexconverter.com/
http://www.arndt-bruenner.de/mathe/scripts/Zahlensysteme.htm

Veröffentlicht unter Geocaching, Konverter | Schreib einen Kommentar

ThisWorkbook vs. ActiveWorkbook

Es kommt in der Makro Programmierung immer mal wieder vor, dass das Makro nicht in dem Excel Workbook läuft, mit dem der User arbeitet.

Zwei Beispiele:

1. Ich ‚öffne‘ eine Dokumentation in einem neuen Worksheet.
2. Speichern unter einem anderen Namen.

Man muss wissen, wie die verschiedenen geöffneten Tabellenblätter angesteuert werden. Für die Unterscheidung wichtig:

Ist das Workbook, in dem das Makro läuft.

Ist das Aktive Workbook.

Für die konkreten Beispiele:

Ich benutze für meinen Arbeitsalltag ein eigenes Addin, das die gängisten Makros enthält. In diesem befindet sich auch ein (verstecktes) Tabellenblatt „Doku“. Mittels Makrobutton erstellt das Makro ein neues Tabellenblatt und kopiert das Blatt „Doku“ in dieses.

Bemerkungen zum Code:

wird verwendet, wenn man mit einem eigenen Ribbon arbeitet. Das Makro wird dann über einen Makrobutton angesteuert.

Normales Fehlerhandling. Falls etwas nicht klappt, bricht das Makro ab.

Mit der ersten Zeile wird die Bildschirmaktualisierung ausgeschaltet, das heisst erst wenn das Makro fertig ist, sieht man was wechselt. Hier zwar marginal, aber das beschleunigt rechenintensive Makros. Mit der zweiten Zeile unterdrückt man „Are You Sure“ Fragen…

Hier jetzt das eigentliche Thema: Zuerst wird ein neues Workbook hinzugefügt, dann Kopiere das Blatt „Doku“ aus ThisWorkbook (das Workbook wo das Makro läuft) zum ActiveWorkbook (das Workbook, das am Bildschirm eben erstellt wurde und deshalb aktiv ist).

Löscht die drei Tabellenblätter, die es ebenfalls im neuen Workbook hat. Sind leer und deshalb unnütz. Könnte man natürlich auch mit einer Schleife oder sonstwie löschen.
H

Veröffentlicht unter Excel, Excel VBA | Schreib einen Kommentar

Excel Funktion : Letzte Zeile und letzte Spalte

Welche ist die letzte Benutze Zeile und Spalte in einer Tabelle?

Eine Frage die sich insbesondere beim VBA programmieren ständig stellt. In meinen Excel mit Makros sind diese beiden Einzeiler deshalb standardmässig in einem „Funktionen“ Modul enthalten:


Lastrow(2) gibt als Resultat die letzte, in der Spalte 2 verwendete Zeile zurück.


Lastcol(2) gibt als Resultat die letzte, in der Reihe 2 verwendete Spalte zurück.

Wie gesagt, das brauche ich in der VBA Programmierung als Grundlage für weitere Makros. Dann muss ich diesen Code nicht ständig wiederholen (und nachschauen).

Veröffentlicht unter Excel, Office | Schreib einen Kommentar

Funktion SVERWEIS mit mehreren Resultaten

Ein Nachteil von Sverweis ist, man kriegt immer nur ein Resultat. Meist das Erste:

2017-06-09_16-17-27

Excel sucht in der definierten Tabelle und liefert das erste Resultat (auf jeden Fall wenn am Schluss die 0 steht, andere Varianten benutze ich kaum).

Was aber, wenn die Zieltabelle mehrere Resultate hätte? Wie kriege ich alle Treffer?

Die Lösung bietet die Funktion SVERWEIS2:

2017-06-09_16-18-18

Als Resultat werden semikolongetrennt sämtliche Suchtreffer geliefert. Die Eingabe der Formel funktioniert gleich wie SVERWEIS, mit dem Unterschied, dass die 0 am Schluss nicht notwendig ist. Mit Dank an Gwe, der diese Funktion bei uns implementiert hat.

Veröffentlicht unter Excel, Excel Funktionen | Schreib einen Kommentar