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:

Thisworkbook.Name

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

Activeworkbook.Name

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.

Sub doku(control As IRibbonControl)

On Error GoTo Fehler

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Workbooks.Add
    ThisWorkbook.Sheets("Doku").Copy Before:=ActiveWorkbook.Sheets(1)
    ActiveWorkbook.Sheets(4).Delete
    ActiveWorkbook.Sheets(3).Delete
    ActiveWorkbook.Sheets(2).Delete
    
Fehler:
Exit Sub
    
End Sub

Bemerkungen zum Code:

(control AS iRibbonControl) 

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

On Error Go To Fehler 
Fehler: Exit Sub

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

Application.Screenupdating = False
Application.DisplayAlerts = False

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…

Workbooks.Add
ThisWorkbook.Sheets("Doku").Copy Before:=ActiveWorkbook.Sheets(1)

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).

ActiveWorkbook.Sheets(4).Delete
ActiveWorkbook.Sheets(3).Delete
ActiveWorkbook.Sheets(2).Delete

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 VBA | Hinterlasse 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).

Function lastrow(usedrow As Variant) As Variant

lastrow = ActiveSheet.Cells(Rows.Count, usedrow).End(xlUp).Row              'letzte Reihe

End Function
Function lastcol(usedcol As Variant) As Variant

lastcol = ActiveSheet.Cells(usedcol, Columns.Count).End(xlToLeft).Column    'letzte Spalte

End Function
Veröffentlicht unter Uncategorized | Hinterlasse 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.

Public Function SVERWEIS2(ByVal Suchkriterium As Variant, _
ByVal Bereich As Range, ByVal Spaltenindex As Integer) As String

Dim arr() As Variant
Dim ws As Worksheet
Dim rng As Range
Dim str As String
Debug.Print Application.Caller.Worksheet.Index

Set ws = Bereich.Parent
Set rng = ws.UsedRange
Debug.Print rng.Address
Set Bereich = Intersect(Bereich, rng)
Debug.Print Bereich.Address
arr = ws.Range(Bereich.Address)

str = ""
For i = LBound(arr) To UBound(arr)

If Suchkriterium = arr(i, 1) And Suchkriterium <> "" And Not (IsEmpty(Suchkriterium)) And arr(i, 1) <> "" And Not (IsEmpty(arr(i, 1))) And Not (IsEmpty(arr(i, Spaltenindex))) Then
Debug.Print arr(i, Spaltenindex)
If str <> "" Then
str = str & ";" & arr(i, Spaltenindex)
Else
str = arr(i, Spaltenindex)
End If
End If

Next i
If str <> "" Then
SVERWEIS2 = str
Else
SVERWEIS2 = "#NV"
End If

End Function
Veröffentlicht unter Excel Funktionen | Hinterlasse einen Kommentar

Benutzerdefinierte Funktionen

Unter einer Standardfunktion versteht man prinzipiell eine von Excel zur Verfügung gestellte Funktion. Das ist der Textteil in der Formel. Bspw. der sverweis.

=sverweis(a1;c1:d10;2;0)

Das Gesamte ist die Formel. Der SVERWEIS ist die Funktion.

2017-06-09_16-03-22

Spannend wird es, wenn man selber solche Funktionen bauen will. Es gibt sehr viele nützliche Dinge, die bei mir mehr oder weniger in Gebrauch sind, wo ich mich frage, warum die keine Standardfunktionen sind?

Einige dieser Beispiele werde ich in der Kategorie Excel Funktionen gerne zur Verfügung stellen.

Einbauen kann man die beispielsweise in der persönlichen Arbeitsmappe. Wer keine Ahnung hat, wo die ist. Bitte googeln

Veröffentlicht unter Excel VBA | Hinterlasse einen Kommentar

Sheets erstellen

Ein einfaches Makro um Tabellenblätter zu erstellen:

– Die gewünschten Namen der Tabellenblätter im Excel in eine Liste schreiben, markieren und das Makro ausführen:

2017-06-09_15-47-55

2017-06-09_15-47-43

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 VBA | Hinterlasse einen Kommentar

Lightart

10947370_10152934850580999_8354542679922946_o

Veröffentlicht unter Lightart, Pix | Verschlagwortet mit | Hinterlasse einen Kommentar

@znuk_ch

Mein Blog wird bis auf weiteres nicht aktualisiert.

Wer von mir lesen will, @znuk_ch veröffentlicht Rätsel auf http://www.geoblog.ch und twittert ab und zu…

 

Veröffentlicht unter Uncategorized | Hinterlasse einen Kommentar