Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Wyszukiwanie sformatowanych komórek

Maciej Gonet · Wysłany: 10-09-2024, 17:59 **Wyszukiwanie sformatowanych komórek**

Dziś chciałem powrócić do tematu wyszukiwania sformatowanych komórek. Chociaż w zasadzie wszyscy wiemy, że formatowanie nie powinno stanowić danych podlegających przetwarzaniu, a jedynie wizualizować wyniki przetwarzania, ale wciąż pojawiają się pytania o różne aspekty odczytu i zliczania komórek sformatowanych w określony sposób.

W VBA można wyszukiwać komórki sformatowane za pomocą metody Find. Rzadko się o tym pisze, ale ta metoda ma również parametr SearchFormat, który informuje o tym, czy chcemy wyszukiwać komórki sformatowane. Niestety obsługa tego wyszukiwania jest mało intuicyjna, bo wspomniany parametr przyjmuje tylko wartość True lub False, natomiast w jaki sposób komórka miałaby być sformatowana, to już trzeba określić w innym miejscu, definiując obiekt FindFormat.

W tym obiekcie można opisać wiele aspektów formatowania, ale tylko stałego (nie warunkowego), co niestety jest pewnym mankamentem.
Niemniej, gdy przebrniemy ten etap, mamy możliwość wyszukiwania komórek w sposób dość selektywny, czego funkcje Excela nie zapewniają.

Drugi problem, który się pojawia przy wyszukiwaniu to duplikaty. Bardzo często wiele komórek spełnia założone kryteria, ale metoda Find wyszukuje komórki pojedynczo. W Excelu okienko dialogowe Find pozwala nam wyszukać wiele komórek za jednym razem, ale możemy sobie tylko na nie popatrzeć, ewentualnie zaznaczyć (po wykonaniu dodatkowych czynności), ale dalsza praca z nimi jest już utrudniona. W VBA wyszukiwanie wielu komórek trzeba zorganizować samemu pisząc odpowiednią pętlę. Do tego materiały Microsoftu polecają metodę FindNext, która ma powtarzać wyszukiwanie wg tych samych kryteriów. Niestety nie jest napisane, że ta metoda ignoruje formatowanie, więc do wyszukiwania komórek sformatowanych się nie nadaje. Drugi mankament metody FindNext, to fakt, że nie działa w funkcjach UDF (a metoda Find działa), zatem gdy chcemy używać wyszukiwania jako funkcji, trzeba poprzestać na powtarzaniu Find w pętli.

Na ogół wygodniej posługiwać się funkcją niż makrem, ale tu trzeba rozwiązać kolejny problem. Obiekt przechowujący wzorzec formatu wyszukiwania (FindFormat) jest tylko jeden, a więc jeśli chcemy przeprowadzać w arkuszu kilka wyszukiwań według różnych kryteriów, to trzeba przechować stan tego obiektu pozostawiony przez użytkownika i odtworzyć go po wykonaniu wyszukiwania funkcją. Niestety w VBA nie ma uniwersalnej metody kopiowania i późniejszego odtwarzania obiektów. Trzeba żmudnie kopiować pojedyncze wartości właściwości i gdzieś je utrwalić w celu późniejszego odtworzenia. Po przeanalizowaniu kilku możliwości doszedłem do wniosku, że najskuteczniej będzie użyć do tego łańcucha tekstowego z separatorami dobranymi tak, żeby nie kolidowały z ewentualnymi zapisami właściwości (tu głównie może chodzić o NumberFormat i NumberFormatLocal, gdzie mogą być używane i przecinki i średniki, więc trzeba postawić na jakiś bardziej egzotyczny separator. Ja wybrałem pionową kreskę (pipe). To można zmodyfikować w razie potrzeby.

Jeszcze jeden problem do rozwiązania w przypadku funkcji to ograniczenia niektórych działań, które są zarezerwowane dla makroinstrukcji. Do nich należy m.in. zapis do obiektu FindFormat. Można to obejść stosując zapis metodą pośrednią z wykorzystaniem metody Evaluate.

W załącznikach dokładniejszy opis funkcji oraz przykłady użycia.

Implementacja metody Range_Find jako funkcji UDF.docx

Pobierz Plik ściągnięto 31 raz(y) 23.33 KB

ZnajdzFormat_UDF_przykłady.xlsm

Pobierz Plik ściągnięto 33 raz(y) 29.93 KB