Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 59291 Skopiuj do schowka Rzeczywista długość tekstu
Autor Wiadomość
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1230 razy
Posty: 4212
Wysłany: 05-11-2017, 16:24   Rzeczywista długość tekstu

W tym poście, nawiązującym w pewnym sensie do mojego wcześniejszego wpisu, chciałbym pokazać kolejny przykład współpracy funkcji UDF z procedurami obsługi zdarzeń. Przy okazji chciałem zwrócić uwagę na pożyteczną, a rzadko wykorzystywaną metodę Dirty.
Rozważmy taki problem. Należy napisać funkcję UDF, która będzie zwracać rzeczywistą długość tekstu, umieszczonego w określonej komórce, której adres przekażemy w argumencie. W Excelu nie ma takiej funkcji, można tylko zliczyć znaki, ale to w przypadku czcionki proporcjonalnej, której najczęściej używamy, ma niewiele wspólnego z rzeczywistą długością tekstu. Długość tekstu możemy określić w punktach (właściwość Width) lub w umownych znakach (właściwość ColumnWidth). Tu będzie mi chodziło o tę drugą właściwość.
Aby uzyskać wynik najbardziej zbliżony do rzeczywistej długości tekstu w komórce należy zastosować metodę AutoFit, a następnie odczytać zmodyfikowaną szerokość komórki. Trudność polega na tym, że metoda AutoFit mocno ingeruje w środowisko Excela i w związku z tym nie może być wywołana przez funkcję UDF, nawet pośrednio. Może to zrobić wyłącznie procedura typu Sub. Najwygodniej byłoby wykorzystać tu procedury obsługi zdarzeń. Ale jak to połączyć w jedną spójną całość? Pomysł jest taki: funkcja UDF odczytuje długość tekstu z pola ID komórki. To może zrobić każda funkcja UDF. Pole ID jest czymś w rodzaju niewidocznego komentarza. Wypełnianiem pól ID mogą zająć się makra zdarzeniowe Worksheet_Calculate oraz Worksheet_Change, generowane po zatwierdzeniu przez użytkownika zawartości komórki po edycji.
W rozwiązaniu wykorzystuję wbudowany mechanizm automatycznego przeliczania arkusza po zmianach, wprowadzonych w komórce. Po zatwierdzeniu edycji Excel automatycznie przelicza wszystkie komórki zależne od komórki, w której była edycja (oraz komórki zawierające funkcje ulotne). Jeśli są wśród nich komórki zawierające formułę naszej funkcji, one również zostaną przeliczone. W trakcie tego przeliczania należy zapamiętać adresy argumentów funkcji, czyli komórek, których długość nas interesuje. Służy do tego publiczna tablica arg, której wielkość jest dynamicznie zmieniana w miarę potrzeby. Rozmiar tablicy określa zmienna ready. Po przeliczeniu arkusza generowane jest zdarzenie Calculate, którego obsługę można wykorzystać do wyznaczenia długości tekstów w komórkach, których adresy zostały zapisane w tablicy arg. Sam odczyt realizuje procedura GetFit, której opis podaję dalej. Po zdarzeniu Calculate generowane jest zdarzenie Change, które wykorzystuję do odczytania długości tekstu w komórce poddanej edycji. Odczytane wartości są umieszczane w polach ID odpowiednich komórek.
Odczytem długości tekstu w komórce zajmuje się procedura GetFit. Odbywa się to przy wyłączonym odświeżaniu ekranu i wyłączonej obsłudze zdarzeń. Najpierw należy zapamiętać w zmiennej roboczej bieżącą szerokość komórki (właściwość ColumnWidth), następnie zastosować metodę Columns.AutoFit, odczytać aktualną szerokość komórki i zapisać ją w polu ID, wreszcie odtworzyć pierwotną szerokość komórki i przywrócić odświeżanie ekranu i obsługę zdarzeń. Jest jeszcze jedna bardzo ważna rzecz, którą należy zrobić. Należy mianowicie komórkę, której pole ID zostało zmienione, oznaczyć jako Dirty, dzięki czemu natychmiast ta komórka i wszystkie inne komórki, które odwołują się do niej zostaną przeliczone (nastąpi w związku z tym przerwa w trakcie obsługi zdarzenia). Pozwoli to od razu uzyskać w arkuszu aktualne wartości. Jednak aby przy przeliczaniu formuł funkcji nie tworzyć na nowo listy adresów komórek należy zdefiniować odpowiednią zmienną wskaźnikową (flagę), która pozwoli odróżnić przypadki przeliczania funkcji przed i po wypełnieniu pola ID. U mnie ta zmienna nosi nazwę poDirty.
Metoda Dirty jest bardzo pożyteczna, niestety słabo znana i mało wykorzystywana, bo jej opis w dokumentacji jest niezbyt klarowny i nie wynika z niego jasno, jak należy się nią posługiwać. Powoduje ona przeliczenie wybranych komórek arkusza (metody Calculate działają inaczej). Istotne w użyciu tej metody jest to, że przelicza ona nie tylko tę komórkę, którą oznaczono jako Dirty, lecz również komórki od niej zależne. Dzięki temu obliczenia można przeprowadzić w jednym etapie, a w przypadku użycia metody Calculate wymagane jest ponowne przywołanie komórki do edycji lub ręczne przeliczenie arkusza. Przeliczenie jest uruchamiane natychmiast po wywołaniu metody Dirty. Nie ma znaczenia ustawienie właściwości EnableEvents. Należy upewnić się, że wszystko, co jest potrzebne do tego przeliczenia zostało wcześniej przygotowane. Metody Dirty nie można używać bezpośrednio w funkcjach UDF.
Jest jeszcze jeden problem wymagający rozwiązania, mianowicie inicjacja pliku po jego otwarciu. Pola ID niestety są resetowane przy otwieraniu pliku, dlatego po otwarciu pliku, który był wcześniej zapisany na dysku, należy cały obszar roboczy arkusza (UsedRange) przeglądnąć i uzupełnić wartości ID we wszystkich niepustych komórkach.
Cały projekt obejmuje 5 składników (nie licząc deklaracji zmiennych publicznych).
1. Procedura Workbook_Open ma na celu wypełnienie pól ID wszystkich niepustych komórek w obszarze roboczym arkusza. Jest to celowe, bo zawartość pól ID jest usuwana przy otwieraniu pliku. Na czas wykonania tej operacji wyłączone jest odświeżanie ekranu i obsługa innych zdarzeń. Pominięcie tej procedury w wielu przypadkach jest możliwe, gdyż w arkuszu wyświetlane są wcześniejsze wyniki, a w miarę edycji lub dodawania nowych danych pola ID są uzupełniane na bieżąco. W razie problemów z wyświetlaniem wyników należy dwukrotnie użyć kombinacji Ctrl+Alt+F9.
2. Procedura GetFit służy do aktualizacji pola ID komórki, której adres podano w wywołaniu. Pole to przechowuje faktyczną długość tekstu, zawartego w komórce w postaci stałej tekstowej lub będącego wynikiem działania formuły.
3. Procedura obsługi zdarzenia Change wywołuje procedurę GetFit dla tej komórki. Procedura kończy się wywołaniem metody Dirty, dzięki czemu formuły zależne zostaną przeliczone i będą mogły wyświetlić lub przekazać do dalszych obliczeń swoje wyniki.
4. Procedura obsługi zdarzenia Calculate również wywołuje procedurę GetFit dla wszystkich komórek, których adresy zostały wcześniej zapisane w tablicy arg.
5. Funkcja UDF o nazwie FitID przekazuje swój argument do tablicy arg i odczytuje zawartość pola ID komórki-argumentu, którą zwraca jako wynik.
Chociaż w tym przypadku mamy do czynienia ze współdziałaniem pary funkcja-procedura, to z punktu widzenia użytkownika „tandem” ten zachowuje się jak typowa funkcja — wynik jest przeliczany przy każdej zmianie danych.
Uwaga: zmiana rodzaju lub wielkości czcionki albo jej atrybutów (np. pochylenie) nie powodują przeliczenia arkusza, więc po takiej czynności należy przywołać komórkę do edycji i zatwierdzić albo dwukrotnie użyć kombinacji Ctrl+Alt+F9.

Rzecz_dlg_tekstu_forum.xlsm
Pobierz Plik ściągnięto 88 raz(y) 22.32 KB

ID posta: 333515 Skopiuj do schowka
 
 
kulasart
[Usunięty]

Wysłany: 05-11-2017, 18:47   

Maciej Gonet, całość wygląda bardzo ciekawie.
Żeby uzyskać przyjaźniejszą formę do przenoszenia pomiędzy projektami, proponowałbym przeniesienia praktycznie całego kodu do klasy.

Taki zabieg pozwoliłby na zapis UDF w postaci:
Kod:

Option Explicit
Public fitIdObj As FitIDClass

Function FitID(adr As Range)
    If fitIdObj Is Nothing Then Set fitIdObj = New FitIDClass
    FitID = fitIdObj.FitID(adr)
End Function


Wewnątrz klasy można bez problemu obsłużyć zdarzenia SheetCalculate oraz SheetChange podpinając się pod skoroszyt.
ID posta: 333522 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1230 razy
Posty: 4212
Wysłany: 13-11-2017, 13:18   

Kulasart, dziękuję za zainteresowanie, jeśli chodzi o tworzenie klas, nie jestem w tym biegły, jeśli zechcesz, to pokaż jak to powinno wyglądać, i ja się czegoś nowego nauczę.
Co do mojej funkcji, to jeszcze uzupełnienie. Ta funkcja nie będzie poprawnie obsługiwać tekstów w komórkach scalonych. Jeśli ktoś ma taką potrzebę, to można procedurę GetFit uzupełnić o fragment rozdzielający złączone komórki, a następnie łączący je ponownie.
Kod:
Sub GetFit(rng As Range)
   Dim szer As Double, mrg As Range, flm As Boolean
   With rng
      flm = .MergeCells
      If flm Then
        Set mrg = .MergeArea
        mrg.UnMerge
      End If
      szer = .ColumnWidth
      .Columns.AutoFit
      .ID = .ColumnWidth
      .ColumnWidth = szer
      If flm Then mrg.Merge
   End With
End Sub
ID posta: 333968 Skopiuj do schowka
 
 
kulasart
[Usunięty]

Wysłany: 13-11-2017, 18:41   

Maciej Gonet, nie ma sprawy. Pliczek w załączniku. Uwzględniłem w nim proponowaną przez Ciebie zmianę dot. obsługi scalonych komórek.

W zaproponowanym przez Ciebie rozwiązaniu dodałbym na początku procedury GetFit prostą instrukcję warunkową:
Kod:
If ready = 0 Then Exit Sub
Wydaje mi się, że obliczanie szerokości każdej zmienionej komórki jest wykonywane na wyrost. Wydajniejsze jest wyznaczanie szerokości tekstu tylko tych komórek, które rzeczywiście chcemy zbadać tj. tych, przekazanych jako parametr UDF.

ex59291.xlsm
Pobierz Plik ściągnięto 85 raz(y) 28.49 KB

ID posta: 333987 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe