Automatische Namensvergabe über VBA - Bereiche dynamisch

[ae]

Hallo zusammen,
nachdem ich Daten aus SAP exprtiert und lokal gespeichert habe, vergebe ich mit nachstehendem Code die Namen auf Basis der ersten Zeile für die jeweilige Spalte.

Code: Visual Basic

1. Sub Feldnamen_bereinigen()
2. Dim felder As Range 'feld
3. Dim zelle As Range
4. Dim i As Long 'letzte Spalte
5.  
6. Application.ScreenUpdating = False
7.  
8. 'Feldnamen bereinigen
9.  
10. i = ActiveSheet.UsedRange.Columns.Count
11.  
12. Set felder = ActiveSheet.Range(Cells(1, 1), Cells(1, i))
13. 'Bereich wird über Feldnamen definiert
14.  
15.     For Each zelle In felder 'bei anderen Sonderzeichen ggfs. erweitern
16.    
17.         zelle = Replace(zelle, " ", "_")
18.         zelle = Replace(zelle, "-", "_")
19.         zelle.Value = zelle.Value
20.    
21.     Next
22.    
23. 'automatische Namensvergabe auf 1. Zeile
24.  
25.     Range("A1").Select
26.     Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
27.     Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
28.    
29.     Range("A1").Select
30.  
31. Application.ScreenUpdating = True
32.  
33. End Sub
34.  

Das funktioniert soweit auch ohne Probleme, allerdings sind die Bereiche für die Namen nun starr auf der aktuellen letzten Zeile.

Da bei den Rohdaten jedoch permanent Datensätze hinzukommen oder wegfallen können, möchte ich den Bereich dynamisch gestalten.
Ich möchte das Makro ins Deactivate Ereignis des Tabellenblattes Rohdaten packen, so daß beim Verlassen des Tabellenblattes und dem Wechsel in ein Blatt Auswertungen die Bereiche dynamisch sind und auf die aktuelle Tabellengröße der Rohdaten passen.

Hat hierzu jemand eine Idee?

Als Beispiel habe ich mal eine Demoarbeitsmappe hochgeladen

[Officer]

Hallo Andreas,

wäre es möglich, dass Du den VBA-Code immer dann ausführen lässst, wenn das Tabellenblatt aktiviert wird. Damit würde der Bereichsname automatisch auf die letzte Zeile angepasst.

Wäre das eine Möglichkeit oder hab ich da was falsch verstanden?

Liebe Grüße

Alois

[ae]

Hallo Alois,
ich hatte das deactivate im Hinterkopf -
Das Problem ist, dass wenn ich ins Blatt mit den Rohdaten etwas einfüge dieses ja bereits aktiv ist.
Evtl. dann statt des activate Ereignisses dann doch das Change nehmen?
Aber da bin ich auch nicht klar gekommen -

Hatte auch überlegt zuerst alle Namen zu löschen und dann neu aufzusetzen - aber beisst sich immer die Katze in den Schwanz, da ich nicht weiß wie ich es schaffe dies zu machen nachdem ins Blatt mit den Rohdaten neues hinzugekommen ist.

Hatte auch schon dran gedacht die Namensvergabe ganz anders zu lösen.

Und zwar die Formel die in Excel (ohen VBA) Namen dynamisch erzeugt per VBA reinzuschreiben.
Etwa in der Art: =INDIREKT("Starten!A$2:"&ANZAHL2(Starten!$A:$A))


Aber da  weiss ich nicht wie ich das in VBA umsetzen soll.

[Officer]

Hallo Andreas,

ich hab mal schnell was geschraubt, siehe Anlage.
Wenn das Blatt "Starten" aktiviert wird, werden die Namen auf dem Blatt "vor_Bereinigung" neu gesetzt.
Ich habe das Ereignis Worksheet_Deactivate() auf dem Blatt "vor_Bereinigung" verwendet.

Viele Grüße

Alois

[ae]

Hallo Alois,
danke für die Unterstützung
das sieht schon ganz gut aus - Allerdings kommt jetzt ja immer diese blöde Aktualiseren Meldung
Die Zeile Application.DisplayAlerts = false bzw True habe ich noch wegen der Unterdrückung der Meldung reingenommen - aber das unterdrückt dann wieder die Namensvergabe !
Hast Du da auch noch eine Idee?

Nachtrag - eben ist mir aufgefallen, dass die Bereiche sich nicht ändern - es werden zwar die Feldnamen bereinigt - aber der Bereich bleibt auf der ursprünglichen Größe

[Officer]

Guten Morgen Andreas,

in dieser Variante habe ich die dyamische Namensvergabe per Formel gelöst. Dies ist deutlich einfacher.

Ich habe in der Beispieldatei, siehe Anlage, die Namen für die Spalten A-C definiert, die restlichen Spalten kannst Du auf die gleiche Weise mit Namen versehen. Die Namensvergabe musst du manuell für die Spalten durchführen. Die Anpassung auf die Spaltenlänge funktioniert automatisch.

Verwendet wird dazu folgende Formel für Spalte A: =INDIREKT("A1:A"&ANZAHL2(vor_Bereinigung!$A:$A))
Für den Namen in Spalte B musst Du dann nur den Bezug anpassen: =INDIREKT("B1:B"&ANZAHL2(vor_Bereinigung!$B:$B))

Viele Grüße

Alois

[ae]

Hallo Alois,

ok - da werde ich dann wohl doch nicht umhinkommen es so zu lösen.

Ich hatte gedacht, ich bekomme irgendwie das ganze so hin, dass genau diese über die Indirekt-Funktion erzeugten dynamischen Bereiche direkt im Makro erzeugt werden.

Werde da in einer stillen Stunde nochmals ein wenig drüber grübeln, ob ich da irgendwie eine Kombination aus Funktion und VBA gebastelt bekomme

Danke für die Unterstützung

[Beverly]

Hi ihr beiden, 

ich würde für die Namensdefinition nicht INDIREKT() sondern BEREICH.VERSCHIEBEN() verwenden:
- für Spalte A: =BEREICH.VERSCHIEBEN(vor_Bereinigung!$A$1;0;0;ANZAHL2(vor_Bereinigung!$A:$A);1)
- für Spalte B: =BEREICH.VERSCHIEBEN(vor_Bereinigung!$B$1;0;0;ANZAHL2(vor_Bereinigung!$A:$A);1)
- für Spalte C: =BEREICH.VERSCHIEBEN(vor_Bereinigung!$C$1;0;0;ANZAHL2(vor_Bereinigung!$A:$A);1)

Als Bezug für die Anzahl würde ich immer Spalte A verwenden, denn die dürfte auf jeden Fall immer gefüllt sein, wobei das bei den anderen auch mal nicht der Fall sein könnte, was dann zu einem falschen Bereich führen würde.

Weshalb BEREICH.VERSCHIEBEN() und nicht INDIREKT()? Soweit ich weiß, belegt wohl INDIREKT() mehr Speicherplatz als BEREICH.VERSCHIEBEN().


@Andreas,

hast du schon mal versucht, das Calculate-Ereignis zu verwenden, um dein Marko auszulösen?

Bis später,
Karin

[ae]

Hallo Karin,
danke für den Hinweis.
Das Calculate hatte ich noch nicht in Betracht.
Werde ich aber gleich mal testen.
Melde mich dann hier zurück sobald verwertbares vorhanden ist.

[ae]

Hallo zusammen!
Es passt ! Ich lösche jetzt einfach beim Deaktivieren zunächst ohne Nachfrage alle vorhandenen Namen - und dann auf ein neues.



Sobald jetzt Datensätze dazukommen oder wegfallen wird aktualisiert!

Danke für eure Unterstützung!

Code: Visual Basic

1.  
2. Sub Feldnamen_bereinigen()
3. Dim felder As Range 'feld
4. Dim zelle As Range
5. Dim i As Long 'letzte Spalte
6.  
7. Dim varName As Name
8.  
9. Application.ScreenUpdating = False
10. 'Namen ohne Nachfrage entfernen
11.  
12.  
13. For Each varName In ActiveWorkbook.Names
14. varName.Delete
15. Next varName
16.  
17. 'Feldnamen bereinigen
18.  
19. Set wsakt = ActiveSheet
20. Set wsroh = ThisWorkbook.Sheets("vor_Bereinigung")
21.  
22. i = wsroh.UsedRange.Columns.Count
23.  
24. Application.EnableEvents = False
25.  
26. wsroh.Select
27. Set felder = wsroh.Range(Cells(1, 1), Cells(1, i))
28.  
29.  
30. 'Bereich wird über Feldnamen definiert
31.    For Each zelle In felder 'bei anderen Sonderzeichen ggfs. erweitern
32.    
33.         zelle = Replace(zelle, " ", "_")
34.         zelle = Replace(zelle, "-", "_")
35.         zelle.Value = zelle.Value
36.     Next
37.    
38. 'automatische Namensvergabe auf 1. Zeile
39.    wsroh.Range("A1").Select
40.     Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
41.     Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
42.    
43.     Range("A1").Select
44.  
45. wsakt.Select
46. Application.EnableEvents = True
47. Application.ScreenUpdating = True
48.  
49. End Sub
50.  
51.  
52.  

[Thomas Ramel]

Grüezi zusammen

Hmmm, was ich an der Sache nicht so ganz verstehen kann ist, warum die Namen auf biegen und brechen mit VBA erstellt/manipuliert werden müssen...

Einmal sauber dynamisch eingerichtet passen sie diese dann doch immer der vorhandenen Datenmenge an und müssen nicht mehr angefasst werden.


Und wenn es um die Performance geht würde ich anstelle INDITEKT() und BEREICH.VERSCHIEBEN() heute auf INDEX() zurückgreifen.

[ae]

Hallo Thomas,
der Hintergrund ist, dass ich eine leere Mappe haben möchte.
Es steht in A1 nur eine Feldüberschrift sowie drunter ein Dummywert.
Mittels "reinkopieren" nach A1 sollen flache Tabellenstrukturen sofort ausgewertet werden können, ohne dass der User sich um Tabellennamen, Bereiche, ... kümmern muss.
Und die Anzahl der Feldbezeichnungen (Spalten) kann unterschiedlich sein.


Ich hab mal als Anlage die Demo wie ich das meine beigefügt.
Das Blatt Auswertungen und Pivot besteht darin bereits. Im nächsten Schritt sollen dann noch statt des manuellen Einfügens evtl. die Daten in die Vorlage geholt werden.

Die Vorlage soll dann eben im Endeffekt Daten in der Form wie sie die 2. Datei "Spieldaten" hat aufnehmen

[Thomas Ramel]

Grüezi Andreas

Ja, ok, aber warum muss der Bereich der für die PT als Basis dient dann jedesmal mit VBA ermittelt werden?

Die folgende Formel einem Bereichsnamen im Namensmanager zugewiesen tut ganz ohne VBA-Programmierung dasselbe:

=$A$1:INDEX($1:$65536;ANZAHL2($A:$A);ANZAHL2($1:$1))

Meinetwegen kann man auch BEREICH.VERSCHIEBEN() nehmen (wobei die halt volatil ist).

=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);ANZAHL2($1:$1))


Damit ist der Quellbereich für die PT immer definiert.
Beim Wechseln auf das Tabellenblatt mit der PT ist das Gerüst je eh leer, so gesehen macht das keinen Unterschied und Du kannst genau so die Daten da ins Tabellenblatt kopieren (oder auch mit einer Abfrage reinholen).

Wozu benötigst Du denn die Bereichsnamen welche die einzelnen Spalten umfassen?
(das ist wegen der Dynamik der Spaltenanzahl ohne VBA nicht lösbar).

[ae]

Hallo Thomas,
stimmt, die Pivot kann ich vorab definieren. Das hatte ich im Grunde nur in VBA gemacht, da ich die Namen eh dort definiere.

Gibt es einen Grund es dort wieder rauszunehmen und es stattdessen über Formel zu machen? Also ich meine so etwas wie Performancegründe?

Die Namen der Spalten sollen einfach der Lesbarkeit in den Formeln dienen.
Oder aber auch als Dropdowns über Daten Gültigkeit - Gerade in aus SAP exportierten Listen mit sehr vielen Spalten für Merkmale und Kennzahlen ist es grauenhaft ohne Namen zu arbeiten. Geht zumindest mir so.
Und die SAP Exporte mit denen ich häufig zu tun habe sind ein Grund warum ich eine weitgehend automatisierte  Vorlage haben mag. Das geht schon damit los, dass man sonst auch erst gleich wieder jedesmal die Namen die aus SAP kommen manuell ändern oder neu aufsetzen muß.
Hab mal einen Screenshot angehängt

Hintergrund dieser "Spielerei" ist dass ganze so weit wie möglich alles vorzubereiten und dann nur noch rein mit den Daten in die Vorlage.

[Thomas Ramel]

Grüezi Andreas

OK, wenn es um weitere Namen und deren Verarbeitung geht, macht das wohl Sinn.

Der 'Vorteil' des dynamsischen Namens (hier jetzt für die PT) besteht darin, dass er, einmal angelegt und erstellt, dann nicht mehr neu gesetzt und/oder verändert werden muss. er passt sich dann einfach der vorhandenen Datenmenge an.
Insofern wirkt sich das auch positiv auf die Performance aus, weil der Name dann nicht jedesmal wieder neu angelegt werden muss - das bewegt sich allerdings im Milli-Sekunden Bereich, denke ich mal.

Die weiter führenden Gedanken und Pläne waren mir so nicht bewusst/bekannt - wenn das so benötigt wird wie Du das beschreibst, dann macht es Sinn das Ganze per VBA anzulegen.

[ae]

Hallo Thomas,

die weiteren Gedanken waren natürlich so aus dem Beitrag auch nicht ersichtlich. Und in mein "Hirnkastel" kann man (Gott sei Dank!) noch nicht per PC schauen.

Nix destotrotz werde ich die Anregung für den dynamischen Namen der Pivot über Formel aufnehmen.

Da dies bisher noch nicht das Ende der Fahnenstange ist, bin ich vielleicht später froh um jede "Bremse" der Performance die ich vermieden habe.

Dein Beitrag hat mich dann auch heute morgen schon dazu angeregt in die Vorlage evtl. noch so etwas wie einen "On/off" Schalter fürs Deactivate-Tabellenereignis  der Rohdaten einzubauen.

Denn das Ganze wird ja, sobald man nur auch kurz mal was nachschaut, beim Verlassen der Tabelle erneut angestossen.

Mal schauen wie sich das Ganze alles noch realisieren lässt.