Wie du Werte mit VBA ersetzen kannst

Der Beitrag in der letzten Woche beschäftigte sich mit dem Ersetzen von Werten in Excel.

Mit dem heutigen Beitrag zeige ich dir das Ersetzen von Werten mit einem Makro in VBA. Dabei verwende ich die gleichen Beispiele wie im vorangegangenen Beitrag.

Im ersten Beispiel sollte „Düssel“ im Wort „Düsseldorf“ durch „Deggen“ ersetzt werden. „Düsseldorf steht z.B. in Zelle B119.

Das Makro dazu ist dieses:

Sub Ersetzen1()
Cells(121,2).Formula=“=REPLACE(B119,1,6,““Deggen““)“
End Sub

In VBA wird für das Ersetzen die Methode REPLACE verwendet. Das Makro nimmt das Wort aus B119 (Düsseldorf), ersetzt die Zeichenfolge ab dem 1. Zeichen, insgesamt 6 Zeichen, durch das Wort „Deggen“ und trägt den neuen Begriff (Deggendorf) in B121 ein.

In einem zweiten Beispiel soll die Jahreszahl 2016 im abgebildeten Bereich durch 2017 ersetzt werden.

Excel_Ersetzen5          Excel_Ersetzen8

Die Umsetzung realisierst du mit diesem Makro:

Sub Ersetzen2()
Dim rngZelle As Range
For Each rngZelle in Range(„B58:B65“)
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub

Das Makro deklariert die Variable rngZelle. Mit einer For-Next-Schleife werden die Zellinhalte des Bereiches durch „2017“ ersetzt.

Das dritte Beispiel sieht ähnlich aus. Du markierst den Bereich B58:B65, Zellinhalt ist die Jahreszahl 2016. Mit diesem Makro wandelst du die Zahl 2016 in 2017 um:

Sub Ersetzen3()
Dim rngZelle As Range
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub

Was ist anders als im zweiten Beispiel? Richtig, du ersetzt nicht die Werte in einem definierten Bereich, sondern in einem selektierten Bereich.

Im vierten Beispiel gibst du die alte und die neue Jahreszahl in separaten Zellen vor.

VBA_Ersetzen1

Und dies ist das Makro dazu:

Sub Ersetzen4()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Range(„E82“)
strNeu=Range(„E83“)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rng>Zelle
End Sub

Beachte bitte, dass auch hier wieder auf den selektierten Bereich abgestellt wurde.

Im fünften Beispiel verwendest du eine Inputbox zum Abfragen der alten und der neuen Jahreszahl.

Sub Erstezen5()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Inputbox(„altes Jahr“,,“2016″)
strNeu=Inputbox(„neues Jahr“,,“2017″)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rngZelle
End Sub

In einem letzten Beispiel geht es darum, die Jahreszahl „2015“ durch „2016“ und die Jahreszahl „2016“ durch „N.N.“ zu ersetzen. Das folgende Bild zeigt links den Zustand vor dem Makro und rechts danach:

VBA_Ersetzen2

Mit diesem Makro setzt du dein Vorhaben um:

Sub Ersetzen6()
Dim rngZelle As Range
For Each rngZelle in Range(„B93:B100“)
If rngZelle.Value=“2016″ Then
rngZelle=REPLACE(rngZelle.Value,“2016″,“N.N.“)
End If
If rngZelle.Value=“2015″ Then
rngZelle=REPLACE(rngZelle.Value,“2015″,“2016″)
End If
Next rngZelle
End Sub

Warum ersetzt du zuerst die Zahl „2016“? Bedenke, wenn du zuerst „2015“ durch „2016“ ersetzt, steht in jeder Zelle des Bereiches „2016“. Diese Zahl ersetzt du dann mit „N.N.“ Das Ergebnis wäre, dass in allen Zellen „N.N.“ steht.

Also ersetzt du zuerst „2016“ durch „N.N.“ und dann „2015“ durch „2016“.

Bei ganzen Spalten oder ganzen Zeilen gehst du analog vor.

Was machst du mit der Funktion ERSETZEN bzw. REPLACE? Lasse es uns in einem Kommentar wissen.

Advertisements

VBA-Steuerelemente: Die TextBox

Die TextBox kann als Steuerelement sowohl im Arbeitsblatt als auch in einer UserForm verwendet werden.

In diesem Beitrag geht es um die Verwendung im Arbeitsblatt.

Aus Anwendungsprogrammen kennst du sicherlich Fälle, in denen TextBoxen zu finden sind. Z.B. kann es in einem Finanzbuchhaltungsprogramm eine Kontoauskunft geben.

In einer TextBox gibst du die Konto-Nr. ein und in einer zweiten TextBox wird der Konto-Name ausgegeben.

Hierzu hast du dir eine kleine Liste mit ein paar Konten gesucht. [1]

textbox1

In eine der folgenden TextBoxen gibst du manuelle eine Konto-Nr. ein. Mit einem Makro willst du den dazugehörigen Konto-Namen finden und in die zweite TextBox eintragen.

Sub TextBox1()
Dim strKonto As String
Dim rngFund As Range
strKonto=Tabelle1.TextBox3.Value
Set rngFund=Range(„B4:B27“).Find(strKonto,LookIn:=xlValues)
Tabelle1.TextBox4.Text=rngFund.Offset(0,1)
End Sub

 Was geschieht?

TextBox1 enthält die Information „Konto-Nr.“, TextBox 2 enthält „Bezeichnung“.

Den Inhalt von TextBox3 (deine Eingabe) deklarierst du als Variable „strKonto“. Die Fundstelle in obiger Liste deklarierst du als Variable „rngFund“. Der Variablen „strKonto“ weist du den wert aus TextBox1 zu.

Jetzt befiehlst du, dass im Bereich B4:B27 der Wert von strKonto gefunden werden soll. Diese Zelle deklarierst du als „rngFund“

In TextBox4 soll letztlich der Wert eingetragen werden, den du, ausgehend von rngFund und mittels der Funktion Offset (=BEREICH.VERSCHIEBEN) in der Spalte rechts daneben (C4:C27) findest.

textbox2

Das Makro hat richtig den zugehörigen Konto-Namen gefunden und eingetragen.

Die Inhalte der TextBoxen (3 und 4) kannst du hiermit löschen:

Sub TextBox2()
Tabelle1.TextBox3.Text=““
Tabelle1.TextBox4.Text=““
End Sub

Für den umgekeh´rten Fall, Eingabe des Namens und Suche der Nummer, verwendest du z.B. dieses Makro:

Sub TextBox3()
Dim strName As String
Dim rngFund As Range
strName=Tabelle1.TextBox4.Value
Set rngFund=Range(„C4:C27“).Find(strName,LookIn:=xlValues)
Tabelle1.TextBox3.Text=rngFund.Offset(0,-1)
End Sub

Wie kann es nun weitergehen?

Für die Kontoauskunft gibt es nun die Möglichkeiten, sich den Saldo des Kontos oder die Kontobewegungen für ein Geschäftsjahr und/oder einer Periodenauswahl anzeigen zu lassen. Dem könnte sich dann noch eine Druckvorschau  und der Ausdruck anschließen.

Dazu müsstest du ein Makro schreiben, das dies alles machen kann und dabei auf die Inhalte der TextBoxen 3 und 4 zugreift.

Das aber ist eine andere Geschichte, die in diesem Beitrag nicht mehr eingebaut werden kann.

Quellen:

[1] DATEV-Standardkontenrahmen (SKR) 04, 2015,

http://www.werz-kraft-vogt.de/fileadmin/Dateien/Dateien(SKR 04.pdf

Bilder dynamisch in Berichte einfügen

Größere Unternehmen haben oft Niederlassungen in anderen Staaten. Das monatliche Berichtswesen aus diesen Niederlassungen folgt in der Regel einer einheitlichen Vorgabe.

Angenommen, du hast die Aufgabe, die monatlichen Meldungen aus den Niederlassungen in die erwünschte Berichtsform zu bringen. Wie wäre es, das Berichtsdesign mit der jeweiligen Nationalflagge ein wenig aufzufrischen?

Excel bietet dazu mehrere Möglichkeiten, von denen ich dir eine reine Excel- und eine VBA-Variante zeigen will.

Weiterlesen „Bilder dynamisch in Berichte einfügen“

Mit Excel schalten und walten

Schaltflächen für den Makrostart

Schaltflächen werden in Excel benötigt, wenn ein VBA-Makro gestartet werden soll.

Um eine Schaltfläche in das Arbeitsblatt zu holen, gehst Du üblicherweise in der Multifunktionsleiste auf Entwicklertools / Einfügen und wählst eine Schaltfläche als Formularsteuerelement oder als ActiveX-Steuerelement aus.

Die Form der Schaltfläche ist immer ein Rechteck, das Du über die Größeneinstellung evtl. noch zum Quadrat umformen kannst.

Weiterlesen „Mit Excel schalten und walten“

Die InputBox in VBA

Statt Berichtsvariablen über DropDown-Listen auszuwählen und sie dadurch einer Prozedur zur Verfügung zu stellen, kann die Auswahl und das Bereitstellen in VBA auch über eine sogenannte InputBox erfolgen.

Darum geht es in diesem Beitrag.

Weiterlesen „Die InputBox in VBA“

So realisierst du Verzweigungen in VBA (Teil 2)

In Teil 1 zum Thema Verzweigungen habe ich dir gezeigt, wie Verzweigungen mit If … Then und mit If … Then … Else in einem Makro realisierbar sind.

Der heutige Teil 2 beschäftigt sich mit Verzweigungen mittels der Funktion CHOOSE und über Select Case.

Weiterlesen „So realisierst du Verzweigungen in VBA (Teil 2)“

So realisierst du Verzweigungen in VBA (Teil 1)

Um es ganz genau zu nehmen, ist bei diesem Thema zwischen bedingten Anweisungen und Verzweigungen zu unterscheiden.

Bei Wikipedia bin ich mit einer einfachen Erklärung zu beiden Begriffen fündig geworden:

„Eine Bedingte Anweisung ist in der Programmierung ein Programmabschnitt, der nur unter einer bestimmten Bedingung ausgeführt wird. Eine Verzweigung legt fest, welcher von zwei (oder mehr) Programmabschnitten, abhängig von einer (oder mehreren) Bedingungen, ausgeführt wird. Bedingte Anweisungen und Verzweigungen bilden, zusammen mit den Schleifen, die Kontrollstrukturen der Programmiersprachen. Sie gehören zu den wichtigsten Bestandteilen der Programmierung, da durch sie ein Programm auf unterschiedliche Zustände und Eingaben reagieren kann.“ [1]

Aus dem „einfachen“ Excel kennst du die Möglichkeiten, mit der WENN-Funktion in verschiedene Richtungen zu steuern. Möglicherweise hast du auch schon mit der WAHL-Funktion Berechnungen dieser Art ausgeführt.

Dieser Beitrag will Erläuterungen geben, wie mit VBA Verzweigungen programmiert werden können.

Vier Möglichkeiten habe ich dazu heraus gesucht, die das Thema allerdings nicht erschöpfend behandeln dürften.

Weiterlesen „So realisierst du Verzweigungen in VBA (Teil 1)“