VBA, Excel, dynamisch Funktionen zuweisen

[jobo]

Hallo,

nachdem Vinzenz mir hierschon helfen konnte, Tabellenblätter und deren Inhalte aus den Arbeitsmappen in einem Verzeichnis auszulesen, versuche ich nun, der generierten Liste von Mappen und Tabellen eine Art "onclick"-Funktionalität zuzuordnen, oder eben eine zweite Spalte mit einem Button, der dann den Wert z.B. der linken Zelle als Paramter übergibt. Ziel ist es, mit einem Klick in der dynamisch generierte Mappen-Tabellen-Übersicht zB. eine neue Zusammenfassung für Mappe1.xls-Tabelle3 zu erstellen. Dazu muss der Button natürlich wissen, dass jetzt Mappe1.xls-Tabelle3 gemeint ist und nicht Mappe4.xls-Tabelle5. Wie wäre der Ansatz?

Gruß

jobo

[Vinzenz Mai]

Hallo,

Ziel ist es, mit einem Klick in der dynamisch generierte Mappen-Tabellen-Übersicht zB. eine neue Zusammenfassung für Mappe1.xls-Tabelle3 zu erstellen. Dazu muss der Button natürlich wissen, dass jetzt Mappe1.xls-Tabelle3 gemeint ist und nicht Mappe4.xls-Tabelle5. Wie wäre der Ansatz?

wenn Du in Mappe1.xls im Tabellenblatt Tabelle3 auf etwas klickst, dann ist Tabelle3 das ActiveWorksheet- und Mappe1.xls das ActiveWorkbook-Objekt.

Freundliche Grüße

Vinzenz

[jobo]

Hallo Vinzenz,

»» Ziel ist es, mit einem Klick in der dynamisch generierte Mappen-Tabellen-Übersicht zB. eine neue Zusammenfassung für Mappe1.xls-Tabelle3 zu erstellen. Dazu muss der Button natürlich wissen, dass jetzt Mappe1.xls-Tabelle3 gemeint ist und nicht Mappe4.xls-Tabelle5. Wie wäre der Ansatz?

wenn Du in Mappe1.xls im Tabellenblatt Tabelle3 auf etwas klickst, dann ist Tabelle3 das ActiveWorksheet- und Mappe1.xls das ActiveWorkbook-Objekt.

Ja, aber ich bin ja in Ausgabe.xls in der Tabelle Ausgabe. Und habe dort eine Liste generiert, in Spalte A stehten die Workbooks, in B die darin enthaltenen Tabellen. Ich dachte erst mit Klick auf sagen wir B5 in dem "Produkt123" steht, eine Funktion aufrufen zu können. Vermutlich aber geht das so nicht (a la HTML-Javascript einen Event einer Zelle zuzordnen). Ich könnte aber die Zelle markieren, und dann den Button "GeneriereNeueTabelleMitÜbersicht" klicken, der sich dazu im aktuellen Tabellenblatt an der markierten Zelle (ActiveCell) orientiert. Das Makro würde dann KundeAbc.xls öffnen, darin die Tabelle "Produkt123" ausählen, die Einträge dort zusammenfassen und in Ausgabe.xls eine neues Tabellenblatt generieren (AusgabeKundeAbcProdukt123), das wiederum die zusammengefassten Daten in Form einer Rechung oder ähnlichem präsentiert.

Dank und Gruß

Robert aka jobo

[Jörg]

Moin Robert,

Ja, aber ich bin ja in Ausgabe.xls in der Tabelle Ausgabe. Und habe dort eine Liste generiert, in Spalte A stehten die Workbooks, in B die darin enthaltenen Tabellen. Ich dachte erst mit Klick auf sagen wir B5 in dem "Produkt123" steht, eine Funktion aufrufen zu können. Vermutlich aber geht das so nicht (a la HTML-Javascript einen Event einer Zelle zuzordnen).

doch.

Klicke im VBA-Editor doppelt auf die Tabelle Ausgabe. Dann hast Du ein Klassenmodul dieser Tabelle, in die Du eintragen kannst:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  
  
End Sub

In diesem Code kannst Du dann den Target abfragen, zum Beispiel:

• Target
• Target.Address
• Target.Row
• Target.Column
• usw.

Aus diesen Angaben kannst Du Dir alles zusammenbasteln, was Du brauchst.

Füge aber am Anfang eine Abfrage ein, dass wirklich nur eine Zelle markiert ist, weil es sonst Fehlermeldungen gibt, zum Beispiel:

if selection.cells.count > 1 then exit sub

Viele Grüße

Jörg

[jobo]

Hallo,

»» Ja, aber ich bin ja in Ausgabe.xls in der Tabelle Ausgabe. Und habe dort eine Liste generiert, in Spalte A stehten die Workbooks, in B die darin enthaltenen Tabellen. Ich dachte erst mit Klick auf sagen wir B5 in dem "Produkt123" steht, eine Funktion aufrufen zu können. Vermutlich aber geht das so nicht (a la HTML-Javascript einen Event einer Zelle zuzordnen).

doch.

Klicke im VBA-Editor doppelt auf die Tabelle Ausgabe. Dann hast Du ein Klassenmodul dieser Tabelle, in die Du eintragen kannst:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

  
Fein, das ist also ein Eventhandler, der sich aufs aktuelle Worksheet bezieht und deshalb immer "Worksheet\_SelectionChange" heißt (analog zu Javascripts "onclick", "onmouseout") und als Paramter immer den selected Range enthält? Der Variablenname "Target" wäre also frei wählbar? Is ByVal Pflicht, oder wäre ByRef auch sinnvoll? Gibt es ein Fachwort in VBA für dieses Eventhandling (da gibts ja sicher noch mehr als "SelectionChange", oder?  
  

> Füge aber am Anfang eine Abfrage ein, dass wirklich nur eine Zelle markiert ist, weil es sonst Fehlermeldungen gibt, zum Beispiel:  
>   
> if selection.cells.count > 1 then exit sub  
  
Fein. Weiter oben "addete" (;-) ich noch eine Frage, wie ich zB. rausbekomme, ob die Tabelle mit dem Namen im aktuellen Sheet schon existiert. <https://forum.selfhtml.org/?t=188131&m=1251822>.  
  
Bisher habe ich die Prozeduren immer der Tabelle zugeordnet (Doppelklich wie oben beschrieben). "Normal" ist wohl, Makros erstmal dem ganzen Workbook zur Verfügung zu stellen? (Doppelklick auf "diese Arbeitsmappe")?  
  
Dank und Gruß  
  
jobo

[Jörg]

Moin,

Fein, das ist also ein Eventhandler, der sich aufs aktuelle Worksheet bezieht und deshalb immer "Worksheet_SelectionChange" heißt (analog zu Javascripts "onclick", "onmouseout")

das kann man IMHO so vergleichen.

und als Paramter immer den selected Range enthält? Der Variablenname "Target" wäre also frei wählbar? Is ByVal Pflicht, oder wäre ByRef auch sinnvoll?

Nein. Der/das Target wird zurückgegeben und ist die angeklickte Zelle. Teste das mal mit der Routine, indem Du einfach einträgst:

msgbox target.address

Dann hast Du die Adresse der angeklickten Zelle. Mit

msgbox target.address(false, false)

kriegst Du auch die $ weg. ;-)

Gibt es ein Fachwort in VBA für dieses Eventhandling

Ja, Eventhandler. ;-)

(da gibts ja sicher noch mehr als "SelectionChange", oder?

Die siehst Du, wenn der Cursor in der Routine steht, oben rechts im Dropdown.

Fein. Weiter oben "addete" (;-) ich noch eine Frage, wie ich zB. rausbekomme, ob die Tabelle mit dem Namen im aktuellen Sheet schon existiert. https://forum.selfhtml.org/?t=188131&m=1251822.

Das würde ich mit Find machen:

dim objGefunden as object

set objgefunden = workbooks(sowieso).sheets(sowieso).columns(sowieso).find(Blattname, lookat:=xlwhole)
if objgefunden is nothing then … Blattname ist nicht vorhanden
set objgefunden = nothing

Bisher habe ich die Prozeduren immer der Tabelle zugeordnet (Doppelklich wie oben beschrieben). "Normal" ist wohl, Makros erstmal dem ganzen Workbook zur Verfügung zu stellen? (Doppelklick auf "diese Arbeitsmappe")?

Das kann man so nicht sagen, weil es davon abhängig ist, was man vorhat.

Oft ist es aber nützlich, Routinen in allgemeinen Modulen zu erstellen und diese dann per Eventhandler (ggf. mit Übergabeparametern) aufzurufen. Genauso kann es nützlich sein, eigene Klassen, eigene Funktionen (UDF), Klassenmodule der Anwendung, … zu erstellen - je nach Bedarf. Natürlich kann man den Code auch gleich in das Event schreiben.

[jobo]

Hallo Jörg,

Nein. Der/das Target wird zurückgegeben und ist die angeklickte Zelle. Teste das mal mit der Routine, indem Du einfach einträgst:

msgbox target.address

ByVal und ByRef hat nichts damit zu tun, dass ich mit Target.Value = "was neues" einen neuen Wert zB. zuordnen kann.

Die siehst Du, wenn der Cursor in der Routine steht, oben rechts im Dropdown.

Wieso heißt es zB. "BeforeDoubleClick"? Vermutlich, weil die Prozedur zwar nach dem Doppelklich aufgerufen wird, aber bevor die eigentlich Funktion des Doppelklicks (Zellinhalt ändern) angeboten wird.

Das würde ich mit Find machen:

dim objGefunden as object

set objgefunden = workbooks(sowieso).sheets(sowieso).columns(sowieso).find(Blattname, lookat:=xlwhole)
if objgefunden is nothing then … Blattname ist nicht vorhanden
set objgefunden = nothing

Das hieße, Blattname ist nicht in dem Zell-Range vorhanden. Ich würde umgekehrt prüfen wollen, ob Book1.xls:Blatt3 existiert als Arbeitsmappen-Tabellenblatt-Kombination. Sonst versuche ich ja was zu öffnen, was garnicht da wäre (was in dem Fall aber vermutlich garnicht vorkommen "kann", weil die Übersicht ja gerade per Auslesen erstellt wurde).

Dank und Gruß

jobo

[Jörg]

Moin,

ByVal und ByRef hat nichts damit zu tun, dass ich mit Target.Value = "was neues" einen neuen Wert zB. zuordnen kann.

wenn ich jetzt nichts übersehen habe, brauchst Du weder ByVal noch ByRef. Nützlich wäre es zum Beispiel, wenn man ausgelagerte Subs oder Functions verwendet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Verdopple(Target.Value)
End Sub

Function Verdopple(ByVal varWert)
Verdopple = 0
If IsNumeric(varWert) Then Verdopple = varWert * 2
End Function

Wieso heißt es zB. "BeforeDoubleClick"? Vermutlich, weil die Prozedur zwar nach dem Doppelklich aufgerufen wird, aber bevor die eigentlich Funktion des Doppelklicks (Zellinhalt ändern) angeboten wird.

Ja - meist wird man da keinen Unterschied feststellen. Sichtbar wird es zum Beispiel so:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Hallo"
End Sub

Wenn die MsgBox kommt, blinkt der Cursor noch nicht in der Zelle.

Das hieße, Blattname ist nicht in dem Zell-Range vorhanden. Ich würde umgekehrt prüfen wollen, ob Book1.xls:Blatt3 existiert als Arbeitsmappen-Tabellenblatt-Kombination. Sonst versuche ich ja was zu öffnen, was garnicht da wäre (was in dem Fall aber vermutlich garnicht vorkommen "kann", weil die Übersicht ja gerade per Auslesen erstellt wurde).

Du kannst auch verwenden:

if not objgefunden is nothing then … Blattname ist vorhanden

Dann wird reagiert, wenn der Name gefunden wurde.

[jobo]

Hallo,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Hallo"
End Sub

Wenn die MsgBox kommt, blinkt der Cursor noch nicht in der Zelle.

»» Das hieße, Blattname ist nicht in dem Zell-Range vorhanden. Ich würde umgekehrt prüfen wollen, ob Book1.xls:Blatt3 existiert als Arbeitsmappen-Tabellenblatt-Kombination. Sonst versuche ich ja was zu öffnen, was garnicht da wäre (was in dem Fall aber vermutlich garnicht vorkommen "kann", weil die Übersicht ja gerade per Auslesen erstellt wurde).

Und das "Cancel" hilft wobei?

Du kannst auch verwenden:

if not objgefunden is nothing then … Blattname ist vorhanden

Dann wird reagiert, wenn der Name gefunden wurde.

Ja, "aber" dann habe ich Deinen Codeschnippsel vielleicht missverstanden. Ich möchte nicht testen ob string "bla" im Range B1:B10 vorkommt, sondern ob "Name1" als Tabellenblatt in Workbook "Abc" vorkommt. So dass ich Workbooks.open("Abc.xls") mach kann um dann mit Workbooks("Abc.xls").Worksheets("Name1") darauf zugreifen kann, ohne einen Fehler zu bekommen Worksheet "Name1" does not exist oder was dann kommen würde.

Gruß

jobo

[Jörg]

Moin,

Und das "Cancel" hilft wobei?

setze es mal auf true: ;-)

cancel = true

Damit wird die eigentliche Funktion des Doppelklicks (Editiermodus der Zelle) nicht ausgeführt.

Ja, "aber" dann habe ich Deinen Codeschnippsel vielleicht missverstanden. Ich möchte nicht testen ob string "bla" im Range B1:B10 vorkommt, sondern ob "Name1" als Tabellenblatt in Workbook "Abc" vorkommt. So dass ich Workbooks.open("Abc.xls") mach kann um dann mit Workbooks("Abc.xls").Worksheets("Name1") darauf zugreifen kann, ohne einen Fehler zu bekommen Worksheet "Name1" does not exist oder was dann kommen würde.

Dann haben wir aneinander vorbeigeredet - ich war wohl irritiert, weil Du von Blattnamen in der Tabelle schriebst.

Dann gibt es zwei Möglichkeiten:

1. Du gehst mit einer Schleife über die Blätter

dim bolVorhanden as boolean, objBlatt as object
bolvorhanden = false
for each objblatt in workbooks("XYZ").sheets
if objblatt.name = "XXX" then bolvorhanden = true: exit for
next

2. Du provozierst einen Fehler und behandelst den:

dim varA

on error goto nichtvorhanden
vara = workbooks("XXX").sheets("YYY").range("A1")
goto vorhanden

NICHTVORHANDEN:
Dein Code, wenn das Blatt fehlt

VORHANDEN:
Hier geht es weiter

err.clear
on error goto 0

Viele Grüße

Jörg

[jobo]

Hallo Jörg,

Moin,

»» Und das "Cancel" hilft wobei?

setze es mal auf true: ;-)

cancel = true

Damit wird die eigentliche Funktion des Doppelklicks (Editiermodus der Zelle) nicht ausgeführt.

Gut zu wissen. Macht ja Sinn, wenn die Felder nicht user-editierbar (readonly) sein sollen.

»» Ja, "aber" dann habe ich Deinen Codeschnippsel vielleicht missverstanden. Ich möchte nicht testen ob string "bla" im Range B1:B10 vorkommt, sondern ob "Name1" als Tabellenblatt in Workbook "Abc" vorkommt. So dass ich Workbooks.open("Abc.xls") mach kann um dann mit Workbooks("Abc.xls").Worksheets("Name1") darauf zugreifen kann, ohne einen Fehler zu bekommen Worksheet "Name1" does not exist oder was dann kommen würde.

Dann gibt es zwei Möglichkeiten:

1. Du gehst mit einer Schleife über die Blätter

dim bolVorhanden as boolean, objBlatt as object
bolvorhanden = false
for each objblatt in workbooks("XYZ").sheets
if objblatt.name = "XXX" then bolvorhanden = true: exit for
next

Gut, das geht ja auch prima. Sowas wie "ObjectExists()" gibts wohl nicht, ist ja mit o.g. auch nicht wirklich nötig.

2. Du provozierst einen Fehler und behandelst den:

dim varA

on error goto nichtvorhanden
vara = workbooks("XXX").sheets("YYY").range("A1")
goto vorhanden

NICHTVORHANDEN:
Dein Code, wenn das Blatt fehlt

VORHANDEN:
Hier geht es weiter

err.clear
on error goto 0

Naja, lieber in dem Fall wohl nicht, schön aber auch die Syntax hier mit goto und on error mal zu sehen bzw. zu wissen, dass das so auch geht

Danke!

Gruß

Robert aka jobo

[Jörg]

Moin Robert,

»» cancel = true
»»
»» Damit wird die eigentliche Funktion des Doppelklicks (Editiermodus der Zelle) nicht ausgeführt.

Gut zu wissen. Macht ja Sinn, wenn die Felder nicht user-editierbar (readonly) sein sollen.

ja, das Cancel gibt es auch noch bei ein paar anderen Ereignissen und ist manchmal ganz nützlich. Vor allem bei Userformen kann man das oft gebrauchen, wenn ein Dialog erst geschlossen werden darf, wenn bestimmte Voraussetzungen erfüllt sind.

Gut, das geht ja auch prima. Sowas wie "ObjectExists()" gibts wohl nicht, ist ja mit o.g. auch nicht wirklich nötig.

Zumindest habe ich davon noch nie gelesen.

Naja, lieber in dem Fall wohl nicht, schön aber auch die Syntax hier mit goto und on error mal zu sehen bzw. zu wissen, dass das so auch geht

Allerdings sollte GoTo nur beim Errorhandling verwendet werden, da es sonst als unsauberer Stil gilt. Bei Excel ist es jedoch manchmal auch eine Gratwanderung: Durch Verwendung sonst eigentlich unüblicher Methoden kann man manchmal eine Performance-Steigerung erreichen. Wie in diesem Fall: Bei der Schleife kann es passieren, dass sie sehr viele Male durchlaufen werden muss, bei Nichtvorhandensein eines Blattes sogar über alle Blätter. Bei der Fehler-Variante geht es wesentlich schneller.

Viele Grüße

Jörg

[jobo]

Hallo Jörg,

Allerdings sollte GoTo nur beim Errorhandling verwendet werden, da es sonst als unsauberer Stil gilt. Bei Excel ist es jedoch manchmal auch eine Gratwanderung: Durch Verwendung sonst eigentlich unüblicher Methoden kann man manchmal eine Performance-Steigerung erreichen. Wie in diesem Fall: Bei der Schleife kann es passieren, dass sie sehr viele Male durchlaufen werden muss, bei Nichtvorhandensein eines Blattes sogar über alle Blätter. Bei der Fehler-Variante geht es wesentlich schneller.

Verstehe: ich spar mir die Schleife, greife direkt ins Dunkle und schau, ob ich was dort erwische oder nicht. Macht Sinn, dass das schneller geht, je länger die Alternativschleifen wären.

Gruß

jobo

[jobo]

Hallo Vinzenz,

wie würde man denn am elegantesten überprüfen, ob bereits ein Tabellenblatt namens "abc" existiert?

Und wie vermeide ich Laufzeitfehler 13, Typen unverträglich? Das tauchte anscheinend auf, weil in einer Zelle, die ich durchlaufe, #WERT steht (also "NULL" ???)

' Durchlaufe die Spalte B im aktuellen Tabellenblatt
    For Each mycell In ws.range("B1:B" & ws.UsedRange.Rows.Count)
        ' Flag setzen, Kategorie noch nicht vorhanden in Ausgabetabelle
        CategoryFound = "notfound"
        'in 0 umwandeln, wenn da Text drinne steht
        myvalue = mycell.Value
        If VarType(myvalue) = 8 Then
            myvalue = 0
        End If
        'nur wenn was in Spalte C steht

If ws.range("C" & mycell.Row).Value <> "" _
        And ws.range("C" & mycell.Row).Value <> " " _
        And myvalue <> 0 Then

Diese letzten drei Zeilen markiert der Debugger. Der Test "myvalue <> 0" bringt wohl den Typenunverträglichkeitsfehler, wenn der Wert "#Wert" ist, also auf eine nicht verfügbare Resource zB. verweist.

Dank und Gruß

Robert aka jobo

[Rouven]

Hello,

ich sag nicht, dass das die Beste Variante ist, aber du kannst einen entsprechenden Error-Handler definieren. Über Err.Number kannst du feststellen, ob es sich hierbei tatsächlich um das bekannte Problem handelt.

MfG
Rouven

[jobo]

Hallo,

Hello,

ich sag nicht, dass das die Beste Variante ist, aber du kannst einen entsprechenden Error-Handler definieren. Über Err.Number kannst du feststellen, ob es sich hierbei tatsächlich um das bekannte Problem handelt.

Ich möchte den Fehler ja vermeiden. Bei PHP gäbe es zB. "isset". Workarround wäre mit einem try-catch-block den Fehler, wenn man ihn den nicht vermeiden kann (weil es nicht geht, den Variablentyp vorher abzufragen, was aber hier gehen sollte), in Kauf zu nehmen und dann eben aufzufangen (catch-block).

Gruß

jobo

[Jörg]

Moin,

Ich möchte den Fehler ja vermeiden.

dann prüfe die Zelle zum Beispiel so

if not iserror(mycell.Value) then …

Viele Grüße

Jörg

[jobo]

Hallo,

dann prüfe die Zelle zum Beispiel so

if not iserror(mycell.Value) then …

Fein. Ich habe die Alternative probiert:

If IsError(myvalue) Then
            myvalue = 0
        End If

Dann kann ich den restlichen Code so lassen und muss nicht den kompletten Schleifenblock in ein If packen, denn aus dem For Each komme ich ja nicht raus, ohne komplett zu exiten, oder. (Bei PHP gibts ja "continue", also nächster Wert, neben "return", dem komplette Abbruch)

Gruß

jobo

[Jörg]

Moin moin,

Dann kann ich den restlichen Code so lassen und muss nicht den kompletten Schleifenblock in ein If packen, denn aus dem For Each komme ich ja nicht raus, ohne komplett zu exiten, oder. (Bei PHP gibts ja "continue", also nächster Wert, neben "return", dem komplette Abbruch)

Du kannst For verlassen, solltest es auch. Wenn das gesuchte Kriterium erfüllt ist (Wert wurde gefunden), kommst Du mit

exit for

aus der Schleife. Es wäre ja verschwendete Performance, die Schleife trotzdem weiterlaufen zu lassen.

Viele Grüße

Jörg

[jobo]

Hallo,

Du kannst For verlassen, solltest es auch. Wenn das gesuchte Kriterium erfüllt ist (Wert wurde gefunden), kommst Du mit

exit for

aus der Schleife. Es wäre ja verschwendete Performance, die Schleife trotzdem weiterlaufen zu lassen.

Jau, aber ich möchte u.u. ja nicht komplett verlassen, sondern nur einen Durchlauf überspringen.

  
foreach ($kinder as $kind) {  
 if($kind->sex == "male") {  
  continue;  
 }  
 $maedchenNamen[] = $kind->name;  
}  

würde in PHP die Mädchennamen einsammeln. In dem Beispiel macht das "continue" wohl nicht soviel Sinn, soll ja nur zeigen, wie ich in PHP zum nächsten Eintrag hüpfen kann.

Gruß

jobo

[Jörg]

Moin,

dann mache es so:

  
foreach ($kinder as $kind) {  
    if($kind->sex != "male") {  
        $maedchenNamen[] = $kind->name;  
    }  
}  

Viele Grüße

Jörg

[jobo]

Hallo Jörg,

dann mache es so:

foreach ($kinder as $kind) {
    if($kind->sex != "male") {
        $maedchenNamen[] = $kind->name;
    }
}

  
Genau. Ich wollte nur für mich festhalten, dass es dieses "continue" in VBA so nicht gibt. O.g. hilft natürlich genauso in dem Fall. Merci!  
  
Gruß  
  
jobo

[Jörg]

Hi Robert,

Genau. Ich wollte nur für mich festhalten, dass es dieses "continue" in VBA so nicht gibt.

naja, man könnte ein GoTo mit Sprungmarke einbauen. *g*

Lieber nicht - siehe anderes Posting. ;-)

Viele Grüße

Jörg