VBA-Methoden: Arbeitsblätter zählen, Anzahl festlegen

Wie viele Arbeitsblätter hat eine Arbeitsmappe?

Befindest du dich in einer geöffneten Arbeitsmappe, kannst du das sofort sehen, wenn sich die Anzahl auf ein überschaubares Maß beschränkt.

Geht es aber um 95 oder 134 Arbeitsblätter, müsstest du zählen.

Das aber kannst du auch durch ein kleines Makro erledigen lassen.

Sub ArbBlattZählen1()
Dim intZahl As Integer
intZahl=ActiveWorkbook.Sheets.Count
MsgBox intZahl
End Sub

So sieht die Antwort aus. Bestätige sie mit „OK“.

Nichtnumerisch34

Lässt sich die Anzahl der Arbeitsblätter für eine andere, geöffnete, Mappe ermitteln?

Sub ArbBlattZählen2()
Dim intZahl As Integer
Workbooks(„VBA_Datei öffnen.xlsm“).Activate
intZahl=ActiveWorkbook.Sheets.Count
MsgBox intZahl
End Sub

Hier gibt die MsgBox z.B. die Anzahl „3“ zurück.

Nun fragst du dich, ob du diese Abfrage auch an eine geschlossene Datei richten kannst.

Nachfolgendes Makro verwendet die GetObject-Funktion, die ich hier nicht weiter erläutere. Nur soviel, GetObject wird für den Zugriff auf ein ActiveX-Object verwendet. Mit der Set-Anweisung wird dem Objekt die Objektvariable zugewiesen.

Sub ArbBlattZählen3()
Dim intZahl As Integer
Dim wb As Workbook
Set wb=GetObject(„C:\Excel\VBA\VBA_Arbeitsblätter Anzahl.xlsm“)
intZahl=wb.Sheets.Count
MsgBox intZahl
End Sub

Die MsgBox gibt hier die Anzahl „5“ zurück.

Anzahl Arbeitsblätter festlegen

Eine neue Excel-Arbeitsmappe enthält standardmäßig drei Arbeitsblätter. Dies ist in den Excel-Optionen unter „Allgemein“ so eingestellt.

Wenn du aber eine neue Mappe haben willst, die 12 Arbeitsblätter enthält, kannst du das mit einem Makro erledigen lassen.

Du fragst dazu zuerst die hinterlegte Anzahl der Arbeitsblätter für neue Arbeitsmappen ab.

Dim intBlatt As Integer
intBlatt=Application.SheetsInNewWorkbook

Dann legst du die Anzahl der Blätter für deine neue Mappe fest, in diesem Fall sollen es 12 sein.

Application.SheetsInNewWorkbook=12

Jetzt legst du eine neue Mappe an und gibst ihr einen Namen. Wie das geht, habe ich in meinem Beitrag „VBA-Methoden: Eine Datei neu erstellen und speichern als“ beschrieben:

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=“C:\Excel\VBA\VBA_NeuDatei.xlsx“

Den Ablageort gibst du mit der Pfadangabe gleich mit an.

Du hast jetzt eine neue Arbeitsmappe mit 12 Arbeitsblättern.

Nun solltest du die Einstellung für die Anzahl der Blätter wieder auf den Standard setzen und die Datei speichern und schließen.

Application.SheetsInNewWorkbook=intBlatt
ActiveWorkbook.Save
ActiveWorkbook.Close

Das Makro gesamt:

Sub ArbBlätter()
Dim intBlatt As Integer
intBlatt=Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook=12
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=“C:\Excel\VBA\VBA_NeuDatei.xlsx“
Application.SheetsInNewWorkbook=intBlatt
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Natürlich kannst du den 12 Arbeitsblättern in deiner neuen Mappe gleich noch neue Namen geben, z.B.“Januar“ bis „Dezember“.

Füge dazu im einfachsten Weg diese Codezeilen in dein Makro vor den Anweisungen .Save und .Close ein:

ActiveWorkbook.Sheets(1).Name=“Januar“
ActiveWorkbook.Sheets(2).Name=“Februar“

usw. bis „Dezember“.

Einen besseren Weg zeige ich mal in einem künftigen Beitrag.

Advertisements

VBA-Methoden: Arbeitsblätter, Druckvorschau und Drucken

Jeder, der am Computer mit Excel arbeitet, wird hin und wieder Ausdrucke ganzer Arbeitsblätter oder eines definierten Druckbereichs vornehmen.

In der Regel wird dem Druck eine Druckvorschau vorangehen.

Dieser Beitrag zeigt, wie mit einem Makro Druckvorschau und Drucken auf unterschiedliche Weise erledigt werden kann.

Weiterlesen „VBA-Methoden: Arbeitsblätter, Druckvorschau und Drucken“

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.

VBA-Methoden: Eine Datei schließen

Wenn eine Datei geöffnet und bearbeitet wurde, soll sie irgendwann auch wíeder geschlossen werden. Das wiederum kann manuell oder mit einem Makro erledigt werden.

Das manuelle Schließen war schon im Beitrag „VBA-Methoden: Eine Datei speichern“ ein Thema. Dabei wurde vor dem Schließen eine Speicherung mit einem Makro vorgenommen.

Wenn du eine Datei mit einem Makro schließen willst, gibt es auch hierbei verschiedene Möglichkeiten.

Einfaches Schließen einer Datei

Für das Schließen der aktiven Datei wird die Methode Close genutzt. Das geht so:

Sub SchließenDatei1()
ActiveWorkbook.Close
End Sub

Wenn du vorher nicht gespeichert hast, bekommst du diese Mitteilung:

Datei_schließen1

Erst wenn du dann „Speichern“ klickst, wird die Datei geschlossen. Das kannst du umgehen, wenn du den Speichern-Befehl in deinen Code mit einbaust. Zwei Möglichkeiten hast du hierfür:

Sub SchließenDatei2()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

oder so:

Sub SchließenDatei3()
ActiveWorkbook.Close SaveChanges:=True
End Sub

Nach dem Close-Befehl wird zusätzlich die Anweisung gegeben, dass die Speicherung von Veränderungen vorgenommen wird.

Schließen einer zusätzlich geöffneten Datei

Wie im Beitrag „VBA_Methoden: Eine Datei öffnen“ erläutert, hast du z.B. eine weitere Datei manuell oder mit einem Makro geöffnet. Du hast deine Bearbeitungen vorgenommen und willst die Datei nun über dein Makro wieder schließen. Eine Speicherung siehst du ebenfalls vor.

Sub SchließenDatei4()
Workbooks(„VBA_Datenblatt.xlsx“).Save
Workbooks(„VBA_Datenblatt.xlsx“).Close
End Sub

oder so:

Sub SchließenDatei5()
Workbooks(„VBA_Datenblatt.xlsx“).Close SaveChanges:=True
End Sub

Öffnen, Bearbeiten, Speichern und Schießen einer zusätzlichen Datei

Jetzt willst du die Vorgänge vom Öffnen bis zum Schließen einer zusätzlichen Datei mit einem Makro ausführen lassen.

Im Arbeitsblatt „Tabelle1“ der Zusatzdatei soll in Zelle B10 der Text „Bearbeitung ist erfolgt“ eingetragen werden. Die Bearbeitung wird mit einer MsgBox bestätigt.

Sub SchließenDatei6()
Workbooks.Open Filename:=“C:\Excel\VBA_Datenblatt.xlsx“
Worksheets(„Tabelle1“).Range(„B10″)=“Bearbeitung ist erfolgt“
MsgBox(„Eintrag erfolgt“)
Workbooks(„VBA_Datenblatt.xlsx“).Save
Workbooks(„VBA_Datenblatt.xlsx“).Close
End Sub

Schließen einer Datei nach dem Speichern

Die Zusatzdatei soll sofort geschlossen werden, wenn gespeichert wurde.

Sub SchließenDatei7()
If Workbooks(„VBA_Datenblatt.xlsx“).Saved=True Then
Workbooks(„VBA_Datenblatt.xlsx“).Close
Else
MsgBox(„Datei ist nicht gespeichert“)
End If
End Sub


Wurde nicht gespeichert, kann in diesem Fall auch nicht geschlossen werden. Du erhälst deshalb die Mitteilung „Datei ist nicht gespeichert“ mittels einer MsgBox.

VBA-Methoden: Eine Datei umbenennen

Wenn du eine Datei „speicherst als“, hast du zwei Dateien. Einmal die ursprüngliche und einmal die mit einem neuen Namen und / oder einer anderen Extension.

Manchmal willst du eine Datei einfach nur umbenennen. Auch dazu bietet VBA eine Lösung.

1. Einfach umbenennen

Die umzubenennende Datei ist geschlossen. Deshalb wird jeweils der gesamte Pfad mit angegeben.

Sub Umbenennen1()
Name „C:\Excel\VBA_Datenblatt.xlsx“ As „C:\Excel\VBA_Datenblatt_2.xlsx“
End Sub

Wenn du das Ganze wieder rückgängig machen willst, tauscht du im zweiten Code die Bezeichnungen der Dateien.

Sub Umbenennen2()
Name „C:\Excel\VBA_Datenblatt_2.xlsx“ As „C:\Excel\VBA_Datenblatt.xlsx“
End Sub

2. Umbenennen mit Variablen

Es gibt dann noch die Möglichkeit, für den alten und neuen Namen jeweils eine Variable zu verwenden. Für das erste Beispiel sieht der Code dann so aus:

Sub Umbenennen3()
Dim strAltname As String
Dim strNeuname As String
strAltname=“C:\Excel\VBA_Datenblatt.xlsx“
strNeuname=“C:\Excel\VBA_Datenblatt_2.xlsx“
Name strAltname As strNeuname
End Sub

3. Umbenennen nach Liste

Vielfach gibt es eine ganze Reihe von Dateien in einem Ordner, die umbenannt werden sollen, beispielsweise wenn die Jahreszahl im Namen enthalten ist und ein neues Jahr begonnen hat.

Auf dem Laufwerk sollen z.B. die Dateien

  • VBA-Umsatz_2016.xlsx
  • VBA-Kosten_2016.xlsx
  • VBA-Mengen_2016.xlsx

liegen. Sie sollen jeweils die Jahreszahl 2017 erhalten.

Da es auch sehr viel mehr als diese drei Dateien sein können, kann eine Auflistung im Arbeitsblatt nützlich sein.

Datei_umbenennen1

Der folgende Code wird die Umbenennung der Dateien in Spalte B in die Dateien in Spalte C. vornehmen.

Das Makro wurde sehr ausführlich aufgeschrieben, Schritt für Schritt, so dass auch VBA-Neulinge die Prozedur verstehen sollten.

Sub Umbenennen4()
Dim intZeile As Integer
Dim strPfad As String
Dim strAltname As String
Dim strNeuname As String
Dim strAlt As String
Dim strNeu As String
strPfad=“C:\Excel\“
For intZeile=3 To 5
strAltname=Cells(intZeile,2).Value
strNeuname=Cells(intZeile,3).Value
strAlt=strPfad & strAltname
strNeu=strPfad & strNeuname
Name strAlt As strNeu
Next intZeile
End Sub

Komfortabler wäre es. die Dateien, die umbenannt werden sollen, per VBA aus dem Ordner auszulesen, sie in einer weiteren Spalte umbenennen und dann die Änderungen im Ordner vorzunehmen.

Das Auslesen von Dateien aus Ordnern ist aber eine andere Geschichte, die ich hier nicht behandle.

VBA-Methoden: Eine Datei neu erstellen und speichern als

Es kann innerhalb einer VBA-Prozedur erforderlich werden, eine neue Excel-Arbeitsmappe zu erstellen. Gleichzeitig sollte der neuen Datei dann auch ein Name und ein Ablageort zugewiesen werden.

Weiterlesen „VBA-Methoden: Eine Datei neu erstellen und speichern als“

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