VBA Ermittlung der definierten Benutzerformate

Moderator: ModerationP

VBA Ermittlung der definierten Benutzerformate

Beitragvon VBA-Lehrling » 15. Feb 2020, 12:13

Hallo Forum,

Google hat eigentlich auf (fast) alle Fragen eine Antwort. Das Problem: man muss den richtigen Suchbegriff kennen.

Ich war auf der Suche nacn einer Lösung, die derzeit definierten Benutzerformate einer Mappe auszulesen und mit den tatsächlich genutzten abzugleichen. So sollen unnötige Definitionen entfernt werden.

Und genau bei diesem Entfernen bin ich zufällig auf den Begriff "xlDialogFormatNumber" gestoßen. Bei der weiteren Suche mit diesem Begriff habe ich doch tatsächlich ein paar Beispiele gefunden, wie man die definierten Formate auslesen kann.

Ich habe mit den Infos aus den gefundenen Beispielen ein kleines Tool zusammengestellt, das so ausgelegt ist, dass es eine andere Mappe, die bereits geöffnet sein muss, nach den definierten Formaten durchsucht. Das erlaubt es mir, mit meinem Tool jede x-beliebige Mappe zu untersuchen. Wird keine 2. Mappe erkannt, wird die eigene Mappe untersucht.

Jetzt habe ich aktuell damit noch 2 Probleme:

1. Wenn die aktuellen Formate mit Hilfe von "Application.Dialogs(xlDialogFormatNumber).Show" ermittelt werden, flackert es auf meinem Bildschirm.

Frage 1: kann man das ggf. verhindern, und wenn "ja", wie?

2. Aus der Funktion, welche die Formate ermittlelt, wird ein Array zurückgegeben.

Frage 2: Wie kann ich den Inhalt des Array in die Spalte A ab Zeile 1 einfügen. Alle meine bisherigen Versuche ohne Loop sind gescheitert.

Meine Mustermappe:
Formattest.xlsm
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Grüße vom VBA-Lehrling

Windows 10 pro (64 Bit) und Office Home & Student 2019
Benutzeravatar
VBA-Lehrling
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 40
Registriert: 03. Okt 2019, 16:44

Re: VBA Ermittlung der definierten Benutzerformate

Beitragvon Ralf A » 15. Feb 2020, 12:19

...nun.... suche danach:
Excel + VBA + array in Bereich einfügen
Da wirst Du garantiert fündig
Wenn nicht, dann kannst Du Dich ja nochmal melden... :D
Ciao, Ralf
Der sicherste Ansatz für einen Irrtum ist der Glaube, alles im Griff zu haben.

Bild
Ralf A
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 626
Registriert: 17. Mär 2018, 11:47

Re: VBA Ermittlung der definierten Benutzerformate

Beitragvon VBA-Lehrling » 15. Feb 2020, 13:31

Hallo Ralf A,

natürlich habe ich mich im Internet schlau gemacht. Und ich habe verschiedene Techniken probiert:

Mit meiner Loop sind in Spalte 1 36 Zeilen gefüllt: das ist meine Ausgangsbasis.

Nächster Versuch: Range(Cells(1, 2), Cells(1 + UBound(FuncRC), 2)) = FuncRC

Es sind (in Spalte 2) wieder 36 Zeilen gefüllt, aber alle enthalten den Wert aus Array(0)

Nächster Versuch; Range("C:C") = FuncRC

Spalte 3 ist nun von 1 bis 1048675 mit dem Wert aus Array(0) gefüllt.

Das alles hatte ich zuvor schon getestet und - weil es nicht funktionierte - wieder verworfen.
Grüße vom VBA-Lehrling

Windows 10 pro (64 Bit) und Office Home & Student 2019
Benutzeravatar
VBA-Lehrling
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 40
Registriert: 03. Okt 2019, 16:44

Re: VBA Ermittlung der definierten Benutzerformate

Beitragvon Flotter Feger » 15. Feb 2020, 15:08

Hallo,

schon mal was von Range.ReSize() gehört ? Falls nicht ... in der Hilfe guggen ... das ist die F1-Taste ... :badgrin:

Sabina
Benutzeravatar
Flotter Feger
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 2494
Registriert: 24. Okt 2016, 16:40

Re: VBA Ermittlung der definierten Benutzerformate

Beitragvon 1Matthias » 16. Feb 2020, 15:03

Moin!
Also hier mal meine "Ideen" zu deinen beiden Problemen.
1. Das geht m.E. nicht (so einfach?). Mit dem sreenupdating schaltest du die sichtbaren Veränderungen aus (vereinfacht gesagt). Im Code wird aber eine Dialogbox aufgerufen und mittels Sendkeys manipuliert. M.W. gilt das screenupdating für dialoge nicht (sagt MS auch in der Hilfe - allerdings bei word, aber das ist ja bei EX meist ähnlich). Und dann sendet sendkeys immer an das aktuelle Fenster. Man könnte jetzt ggf. mittels API versuchen die Fensteranzeige des Dialogs zu unterdrücken. Ob dann sendkeys noch klappt weiß ich nicht. Vermtl. müsste du dann mit sendmessage o.ä. arbeiten. Das alles für ein bissl flackern - ist glaube ich zu viel.
Falls es aber wirklich stört hier mal eine Variante, bei der du ab Ex2007 (die mit dem xlsm) die individuellen Formate auslesen kannst. Die anderen gehen damit glaube ich nicht. DIe braucht man aber auch weniger, da man sie eh nicht löschen kann. Der Code ist noch ausbaufähig (prüfen ob es keine Wert gibt etc.). War nur auf die Schnelle, da mir deine Variante da noch nicht bekannt war.

https://www.herber.de/forum/cgi-bin/cal ... 84#1736984

2. Da warst du mit deinem "nächsten" Versuch schon fast richtig. Das Problem ist, dass dein Array nicht 2dimensional ist. Damit ist es wie ein waagerechtes Lineal. Wenn du das in einem senkrechten BEreich eintragen willst, wird die Lineal immer wieder neu hingelegt. Deshalb stand da auch nur immer der 1. Wert in den Zellen. MAcht Excel halt so. Um es richtig einzufügen, musst du es vorher drehen. Dann liegt es senkrecht da und passt auch rein. Auf deinen Code würde das so aussehen:
Code: Alles auswählen
[code]Range(Cells(1, 2), Cells(1 + UBound(FuncRC), 2)).Value = Application.Transpose(FuncRC)[/code]

Damit sollte es passen. Wie du den BEreich dann vorher auswählst, ob so wie du oder mit dem resize von Sabina (m.E. einfacher da nicht viel mitgerechnet werden muss, wie bei deiner Variante) ist egal.
VG
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 642
Registriert: 15. Aug 2017, 18:36

Re: VBA Ermittlung der definierten Benutzerformate

Beitragvon VBA-Lehrling » 17. Feb 2020, 08:50

Hallo 1Matthias,

es freut mich, dass Du Dich mit meinen Excel-Problemen beschäftigt hast.

Das Problem, den Inhalt des Arrays in eine Spalte zu bekommen, hatte ich inzwischen erfolgreich lösen können. Mit dem Resize alleine hat es nicht funktioniert: ich hatte zwar alle Spalten gefüllt, aber immer mit dem gleichen Inhalt; die Daten aus dem Array waren quer in die Zeile eingetragen und das in vielen Spalten. Dann hatte ich das mit dem Transpose gefunden, und mein Problem war gelöst.

Thema flackern:
Es ist ja so, dass man diesen Code nicht dauernd einsetzt. Schön wäre es natürlich schon gewesen, dass Flackern abstellen zu können. Den von Dir genannten Artikel (da taucht auch ein Name „Matthias" auf; Zufall?) hatte ich auch schon gefunden und mich damit beschäftigt. Man kann da vieles ohne Excel machen. Ich habe eine Excel-Mappe kopiert und ihr die Erweiterung ".ZIP" gegeben. Danach konnte ich den File mit dem Explorer öffnen und die Struktur einer Excel-Mappe sehen; auch den Teil mit den definierten Benutzerformaten. Wie in dem Artikel erwähnt, sind die Daten jedoch nicht in der Schreibweise gespeichert, wir sie im Format-Dialog angezeigt werden. Noch habe ich nicht herausgefunden, wie ich die gespeicherten Daten in das lesbare Format umsetzen muss. Es betrifft nicht alle Formate, sondern nur solche, in denen bestimmte Zeichen vorkommen. Die Logik in dem Mustercode versagt beim Trennen in einzelne Formate: es werden weniger Formate ausgegeben als vorhanden sind. Ein weiteres Problem dabei: die Formate werden in der internen Schreibweise gespeichert, während im Dialog die lokale Schreibweise genutzt wird.

Wenn ich einmal viel Zeit und Lust habe, werde ich dieses Thema nochmals aufgreifen.

Inzwischen habe ich meinen Code dahingehend erweitert, dass ich alle belegten Zellen der ausgewerteten Mappe hinsichtlich ihrer Formatierung scanne und in meiner Tabelle mit den definierten Formaten in weiteren Spalten den Namen der Tabelle und die Zell-Adresse, an welchem ein bestimmtes Format erstmalig gefunden wurde, eintrage. Außerdem zähle ich, wie oft jedes Format benutzt wird. So habe ich Tabellen gefunden, in denen eine ungenutzte Zelle eine "unmögliche" Formatierung hatte. Und diese Tabelle war und ist Vorlage für weitere Tabellen. Der absolute Hammer war eine Tabelle, welche bis zur maximalen Spalte belegt war. das habe ich an dem sehr hohe, Nutzungs-Count erkannt.

In einer weiteren Ausbaustufe möchte ich noch die Adressen aller Zellen mit einem bestimmten Format suchen (Doppelklick) und anzeigen, um das Aufspüren bestimmter Formate zu erleichtern.

Dir (und auch den Anderen, welche mir geantwortet haben), nochmals recht herzlichen Dank.
Grüße vom VBA-Lehrling

Windows 10 pro (64 Bit) und Office Home & Student 2019
Benutzeravatar
VBA-Lehrling
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 40
Registriert: 03. Okt 2019, 16:44

Re: VBA Ermittlung der definierten Benutzerformate

Beitragvon VBA-Lehrling » 18. Feb 2020, 11:08

Noch ein kleiner Nachtrag von mir:

Nachdem ich festgestellt habe, dass in der Style.xml-File nur die Userformate enthalten sind, habe ich es aufgegeben, über den von Matthias erwähnten Code "ohne Flackern" die Formate ermitteln zu können.

Grund: ich möchte nicht nur die nicht-Standard-Formate ermitteln, sondern auch die Standard-Formate, um darüber zu erkennen, ob irgendwo in einer Mappe/Tabelle ein unsinniges Format aktiviert wurde.

Ok: dann ist mein Thread-Titel wohl auch nicht ganz korrekt!!!
Grüße vom VBA-Lehrling

Windows 10 pro (64 Bit) und Office Home & Student 2019
Benutzeravatar
VBA-Lehrling
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 40
Registriert: 03. Okt 2019, 16:44


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: newone22 und 20 Gäste