Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Tymczasowe ukrywanie formuł

Maciej Gonet · Wysłany: 09-10-2022, 01:22 **Tymczasowe ukrywanie formuł**

Czasem dobrze by było mieć w tej samej komórce wartość stałą i formułę.

Może to odnosić się do sytuacji, gdy normalnie korzystamy z wartości typowej obliczonej formułą, ale w pewnych okolicznościach chcemy mieć możliwość ręcznej korekty tej wartości.
Bywa też tak, że formuły są złożone i chcemy na pewien czas zrezygnować z ich aktualizacji (przeliczania), ale nie w całym arkuszu, tylko w wybranych komórkach.
Innym razem w komórce odliczamy czas i chcemy ten stoper na chwilę zatrzymać, ale z możliwością kontynuacji.

Osiągnięcie takich celów wprost nie jest możliwe, bo zawsze wprowadzenie stałej do komórki nadpisuje formułę, jeśli tam wcześniej była. Można jednak wykorzystać pewne sposoby, aby przechować formułę z komórki przez pewien czas w innym miejscu, a później odtworzyć ją, gdy zajdzie potrzeba.

Znam cztery takie sposoby, ale każdy z nich ma jakieś wady. Pewnie znalazłoby się i więcej bardziej wyrafinowanych pomysłów, ale liczę, że i te cztery mogą być przydatne w sprzyjających okolicznościach.

Dokładniejszy opis i proste przykłady w załączonych plikach.

Tymczasowe ukrywanie formuł.docx

Pobierz Plik ściągnięto 167 raz(y) 23.05 KB

Ukrywanie formuł.xlsm

Pobierz Plik ściągnięto 154 raz(y) 32.05 KB

Rafał B. · Wysłany: 09-10-2022, 12:00

Czy analizował Kolega możliwość dopisania customego węzła do XML? Wydaje się, że to powinno się udać. W przypadku sukcesu nie wiem jak ewentualnie z utrzymaniem tych danych w binarnym .xlsb

Maciej Gonet · Wysłany: 09-10-2022, 15:28

Obawiam się, że na te pytania nie potrafię odpowiedzieć. W ogóle nie bardzo rozumiem, o co pytasz. Ja nie jestem profesjonalnym informatykiem, zajmowałem się Excelem o tyle, o ile było mi to potrzebne w pracy. XML nie był obiektem mojego zainteresowania, również z plików .xlsb korzystam sporadycznie i nigdy nie analizowałem niuansów związanych z tymi plikami.

Napisałem to, co gdzieś znalazłem, sprawdziłem, zebrałem i przystosowałem do swoich potrzeb i tyle. Każdy może to teraz dalej testować i oswajać według woli i potrzeb. A ja nie mam wiele więcej do dodania, chyba że byłyby jakieś sprawy wymagające bardziej szczegółowego wyjaśnienia.

Rafał B. · Wysłany: 10-10-2022, 00:28

Nie chodziło mi o żadne wymyślne koncepty, ale faktycznie może napisałem w sposób niejasny.

Wiadomo, że upraszczając .xlsx i .xlsm są zestawami plików xml. I potencjalnie można spróbować do takiego XMLa dodać własny węzeł (o ile nie ma jakiejś wyrafinowanej walidacji przez Excela) lub twórcy nawet może przewidzieli jakiś klucz, gdzie można customowe dane dodać(?). Mamy bezpośredni dostęp do takiej formy poprzez właściwość Value z parametrem 11 (enumka XlRangeValueDataType.xlRangeValueXMLSpreadsheet) co m.in. poruszyłem w tym wątku całkiem niedawno https://www.excelforum.pl...lue-vt72373.htm

Co do pytania o .xlsb, to oczywiście format własny Excela, binarny, więc potencjalnie przy konwersji .xlsm do .xlsb takie "nadmiarowe" dane mogą ulegać usunięciu.

Ale z odpowiedzi Kolegi rozumiem, że temat otwarty pod tym względem; w wolnej chwili spróbuję (chyba, że mnie ktoś uprzedzi i powiadomi o wyniku na forum), bo takie potencjalne rozwiązanie byłoby czystsze i ogólniej nieco lepsze od zaproponowanych w tym wątku, bo nie zabierałoby żadnej funkcjonalności. No chyba że przeoczyliśmy jakąś właściwość nie typu `read-only` obiektu Range i da się prościej :-D

Rafał B. · Wysłany: 29-12-2023, 01:54

Dawno nie miałem okazji pisać czegoś ambitniejszego w VBA poza tzw. utrzymywaniem kodu i małymi przeróbkami, więc korzystając z przerwy świątecznej coś tam nareszcie wykodziłem nowego.

W temacie wątka okazało się, że intuicja i tym razem mnie nie zawiodła: skoro współczesny plik excela jest de facto paczką XML, to przypuszczałem, że w VBA przewidziano możliwość dopisania swoich węzłów do tego dokumentu. Zamiast przekopywać dokumentację, zapytałem model GPT4 i raczył był mi zasugerować użycie nieznanej mi kompletnie wcześniej metody `Workbook.CustomXMLParts.Add`.

Niestety chyba (?) nie ma możliwości przyporządkowania takiego węzła bezpośrednio do XML obiektu range, co chwilowo ostudziło moją euforię. Ale z powodu pilnej potrzeby opracowania rozwiązania optymalizacyjnego (włączanie/wyłączanie kalkulacji nie wchodziło w rachubę) powróciłem do tematu i tak powstało rozwiązanie oparte na dwóch napisanych przeze mnie klasach.

Podstawą jest obiekt klasy CustomXmlData. Pomaga w dodawaniu danych do XML skoroszytu. Zamiast opisu zacytuję przykład użycia z załączonego pliku:

Kod:

' CustomXmlData - przykład wykorzystania klasy do zapisu danych do skoroszytu
' bez użycia żadnych dzieci klasy Worksheet jak np. Range

''
' Przykład - zapis
'
Public Sub Example0PrepareTestCase()

With CustomXmlData

' simple key-value data
.Add data:="4.34", id:="RateEUR", namespace:="BuyRate"
.Add data:="3.94", id:="RateUSD", namespace:="BuyRate"
.Add data:="4.00", id:="RateUSD", namespace:="SellRate"

' przy braku przestrzeni nazw, będzie użyta przestrzeń domyślna
.Add data:="Titelitury", id:="fancyName"

' 2D array
.Add data:=Array( _
Array("name", "Jan"), _
Array("lastname", "Iksiński"), _
Array("age", "62")), _
id:="p0187", _
namespace:="personalData"
End With
End Sub

''
' Przykład - odczyt
'
Public Sub Example0ReadSimpleData()

With CustomXmlData

' odczyt prostych wartości:

MsgBox .GetValueById(id:="fancyName")
' alternatywny sposób:
' MsgBox .GetDataAsCollection(id:="fancyName")("value")

MsgBox .GetValueById(id:="RateUSD", namespace:="SellRate")
' alternatywny sposób:
' .GetDataAsCollection(id:="RateUSD", namespace:="SellRate")("value")

MsgBox "Wiek zanonimizowanowego pacjenta id=`p0187` wynosi " & _
.GetValuesById(id:="p0187", namespace:="personalData")("age") & _
" lat(a)"

' przykład usunięcie wartości
.Delete id:="fancyName"
MsgBox .GetValueById(id:="fancyName") ' rzuca wyjątek

End With
End Sub

Rafał B. · Wysłany: 29-12-2023, 01:58

I tutaj wchodzi już docelowe rozwiązanie: na tę klasę CustomXmlData utworzyłem wrapper w postaci klasy RangeFormula, która już wykonuje właściwe zadanie- czyli ułatwia przechowanie i zapis formuł do arkusza. Przykład użycia w kontekście tabeli:

Kod:

'''
' Odpalane jednorazowo przy działającej formule.
' Zapisze istniejącą formułę z pierwszej komórki podanego zakresu,
' którą potem będzie można odtworzyć.
'
Public Sub Test2PrepareTestCase()

' "test_suma" i "test_potega" to pomocnicze nazwy, można je pominąć
' (wówczas do uruchamiania będzie potrzeba
' przy użyciu podawać obiekt Range)

With TestSheet2.ListObjects(1)

RangeFormula.SaveByRange .ListColumns("suma").DataBodyRange, , "test_suma"

RangeFormula.SaveByRange .ListColumns("potega").DataBodyRange, , "test_potega"

End With
End Sub

'''
'
'
Public Sub Test2values()

With RangeFormula
.ApplyValues "test_suma"
.ApplyValues "test_potega"
End With
End Sub

'''
'
'
Public Sub Test2formulas()

With RangeFormula
.ApplyFormulas "test_suma"
.ApplyFormulas "test_potega"
End With
End Sub

Reasumując nie ma potrzeby operowania na klasie CustomXmlData, jedynie RangeFormula (ale obie muszę być dołączone w projekcie). Te klasy rozdzieliłem głównie z powodu bałaganu jaki powstał przy braku ich separacji.

To przesłane rozwiązanie to na razie szkic pisany dość szybko "na kolanie" i zawiera kilka mniej lub bardziej poważnych spraw do poprawy, które zazwyczaj opisałem gdzieś tam w formie TODO w komentarzach.
Na pewno wymagałoby także optymalizacji, moze też zawierać jakieś "śmieci" ze starszych wersji, którcyh nie wychwyciłem, a które powinny zostać usunięte.

Zadanie początkowo proste okazało się mieć wiele pułapek, stąd koncepcja podczas pisania zmieniała się kilkukrotnie i stąd brak pewnej elegancji. Kod jest "niepudrowany", więc puryści jak np. master_mix :mrgreen:

może być zdegustowany prostym wykorzystaniem w klasie RangeFormula innej klasy (CustomXmlData), gdzie oczywiście powinien być zastosowany jako warstwa pośrednia interfejs eksponujący metody. Mam świadomość wielu mankamentów, po prostu takie rozwiązania można poprawiać w nieskończoność, a chciałem już jakiś zarys zaprezentować i udowodnić na zasadzie proof of concept od pomysłu do realizacji, że idąc tą drogą można wyeliminować w zasadzie wszystkie poważne wady innych rozwiązań w temacie przechowywania formuł.

RangeFormulaClass.xlsm

Pobierz Plik ściągnięto 71 raz(y) 95.85 KB

Maciej Gonet · Wysłany: 29-12-2023, 02:23

Bardzo dziękuję za szczegółowe przedstawienie swojego pomysłu i koncepcji rozwiązania. Jest to już jednak dość wysoki poziom i trzeba się temu przyjrzeć z lekką głową.
Będę miał o czym myśleć w Nowy Rok.

A tymczasem życzę Tobie i nam wszystkim, żebyś częściej tu do nas zaglądał, póki Forum jeszcze istnieje. Zawsze można się czegoś ciekawego od Ciebie dowiedzieć.

master_mix · Wysłany: 30-12-2023, 22:22

Rafał B. miło Cie widzieć , już się martwiłem że utknąłeś pod stertą pieluch i się szybko nie pokażesz, a ludzi z ciętym językiem mało na tym forum :mrgreen:

Ciekawy przykład i ciekawe podejście.
Już kiedyś rozważałem wykorzystanie tych nowych klas z serii CustomXML..., które głownie dla zastosowań VSTO się pojawiły, ale myślałem pod kątem zapamiętywania ostatnich wpisywanych wartości - temat porzuciłem, trochę mnie znudził a trochę lipa z tym zapisywaniem binarnym.
Znam sporo osób które notorycznie zapisują xlsb, lub nawet xls. - Edit: W xlsb działa wszystko prawidłowo, jednak jak ktoś zapisze xls, to lipa. Na szczęście już rzadkość ...

Klasy ciekawe i pożyteczne, zastosowań można znaleźć pewnie jeszcze kilka. Sporo metod własności i zdarzeń do wykorzystania.

Rafał B. · Wysłany: 31-12-2023, 15:15

Dzięki za wskazówki i spostrzeżenia! :danke

Ad 1. Co do klas statycznych, to faktycznie ciężko mnie przekonać do zmiany na moduły ze względu na a'la enkapsulację i Intellisense; nie lubię jak w wielkim projekcie IDE podpowiada mi tysiące zbędnych funkcji w złym kontekście, gdzie powinno podpowiadać tylko standardowe, przez co spowalnia mi to pisanie kodu.
Stosuję klasy statyczne w zasadzie tylko do narzędzi, które nie mają swojego stanu (pól). Wielu uznaje to za `code smell`, ale moja praktyka akurat nie podziela obaw co do tego zagadnienia. Modułów używam głównie do skryptów, które korzystają oczywiście ze stosownych klas.

Z wad klas statycznych (i ogólnie klas) widzę tylko jedną i jest to błąd projektowy VBA (może VB6 też?), że brak public const i public enum. Stałe i enumy nie są eksponowane i dostępne bezpośrednio (bez żadnych Property Get i powoływania instancji). Jako obejście załączam stałe/enumy zwykle w zwykłym module, stosując dobre nazewnictwo.

Ad 2.
Pisząc te klasy starałem się mieć to na uwadze, żeby ktoś mógł skorzystać z owoców mojej pracy. Skoro zwróciłeś uwagę, że ich stosowanie jest dość trudne, to jednak chyba na tym polu niestety poległem., chociaż to był jeden z moich głównych celów- mało metod, wygoda i ekspresywne nazwy.

Co do pozostałych punktów to zawierają cenne wskazówki, ale muszę zrozumieć o co chodzi, przysiądę do tego jeszcze po Nowym Roku. MSXML stosowałem tylko do parsowania węzła, nie wiedziałem że można inaczej pomijając manipulacje stringami. Nie użylem worksheet.codename, bo chciałem pierwotnie, by można było używać klasy na innym pliku niż bieżący (poza ThisWorkbook). Alle po zastanowieniu to chyba byłby niepotrzebny overkill z mojej strony i ciężko wyobrazić sobie taki scenariusz w praktyce.

PS
Co do mojego kodu- jakby ktoś testował- to wymagany jest hotfix (metoda getFullAdress powinna otaczać apostrofami nazwę arkusza, żeby nie rzucało wyjątkiem przy arkuszach, których nazwa zawiera spację).

Poza tym rozwiązanie w tej wersji "beta" działa produkcyjnie zadziwiająco dobrze i bezproblemowo: na arkuszach bardzo obciążonych formułami, które nie wymagają częstego odświeżania, cały kod na zdarzenie Activate

Kod:

With RangeFormula

.ApplyFormulas (...)

Me.Calculate

.ApplyValues (...)

End With

Ale ulepszę klasy za kilka dni stosując wybrane wskazówki od master_mix i ewentualnie innych użytkowników, którzy podzielą się swoimi spotrzeżeniami, bo kilka fajnych wskazówek już dostałem.

PPS
co do ilości metod, to raczej podejście stosowałbym w myśl upraszczania rzeczy jak się da, ale nie bardziej. Zamiast Do(Byval AorB as something) lepsze jest DoA() i DoB(). Łatwiej taki kod przetestować, jest czytelniejszy i łatwiejszy w stosowaniu. Ale może jestem za bardzo pod wpływem Wujka Boba po przeczytaniu słynnego Czystego Kodu :mrgreen:

Po raz któryś brakuje na forum opcji edycji postów po 30 minutach, w jednym poście mógłbym aktualizować wersję rozwiązania. Dla fioletowych można byłoby rozważyć taki przywilej... ;-)

master_mix · Wysłany: 02-01-2024, 16:23

Pisałem tego posta nad ranem w nowy rok , i już kończyłem, kiedy wywaliło forum w kosmos ...
Nie byłem pijany i na pewno to nie moja wina :D
Więc piszę od nowa.

Rafał B. · Wysłany: 02-01-2024, 22:11

Z enumami to był skrót myślowy- można je stosować, ale jest powszechnie znany związany z nimi bug, który w najmniej spodziewanym momencie pojawi się u klienta, bo pojawia się losowo, czyli niesławne `Compile error: Constant expression required`, dlatego nie stosuję ich w klasach, bo lubię spać spokojnie.

Cytat:

intellisense jest też i dla modułu

to oczywiste, że jest Intellisense, ale chodziło nie o brak Intellisense, tylko za dużo śmieciowych podpowiedzi w Intellisense, pisałem o tym wielokrotnie. Mam projekt na grube kilkadziesiąt tysięcy linii kodu, wiele klas, modułów. Każda nazwa publicznego makra/skryptu zaśmieca mi Intellisense. Wielu to nie przeszkadza, ale mnie pisze się szybciej, gdy mam mniej podpowiedzi do wyboru. Ale poza tym konstrukcja With jest ograniczona do obiektów (i chyba tylko typów), więc do modułu nie da rady bez kombinacji. A lubię ją stosować With, bo dobrze(!) wykorzystana zwiększa przejrzystość i szybkość pisania kodu (nawet czasem szybkośc działania, bo kod trzyma referencję do onbiektu, jak to często robimy z .Range) i brakuje mi jej w innych językach.

Cytat:

Uważam że ładowanie do klas MSXML plików xml i zapisywanie, opóźni kod.

Przecież pisałem, że cenna wskazówka i nie wiedziałem o możliwości uniknięcia tego, więc nie trzeba mnie przekonywać do tej zmiany! :mrgreen:

Natomiast co do przykładu z interfejsami: nie widzę żadnych zalet tworzenia dwóch klas PrivateClass i PublicClass, według mnie lepiej to zrobić w jednej klasie, czyli implementować wszystko w ramach PublicClass odnosząc się do nomenklatury Kolegi. Wszystkto zapewnia deklarowany scope-zasięg widoczności Private/Public metod i akcesorów.
W ogóle w OOP każde powoływanie instancji (w przypadku VBA "new JakaśKlasa") wewnątrz klasy jest podejrzane z gruntu zasad SOLID, bo powinno być wstrzykiwanie zależności. A w tym przykładzie robimy zależność PublicClass korzysta z PrivateClass.

EDYCJA
chyba po namyśle zrozumiałem ideę: ta PublicClass miała być jedna sądząc po eksponowaniu jej metod, a PrivateClass może być potencjalnie wiele (jako poszczególne implementacje) i po zmianie implementacji na np. PrivateClassV2 zmieniamy tylko konstruktor (i typ pola) klasy PublicClass. Czyli de facto jest to ominięcie wady jezyka, że interfejs nie może implementować innych interfejsów i PublicClass pełni tę rolę "superinterfejsu".

Jeśli tak, to ja wolę jednak utworzenie interfejsu odpowiadającego od razu wymaganiom (czyli dodatkowego łączącego metody InterfaceClass1 i InterfaceClass2). Dodatkowa praca, ale później same benefity. Bo wówczas mamy np. ITaxCalculator i tylko podstawiamy implementacje w jakimś makrze w Module1. Czyli:

Kod:

Dim obj As ITaxCalculator
Set obj = New DefaultTaxCalculator
'Set obj = New NowyLadTaxCalculator ' zmiana kryteriów obliczania podatku
'Set obj = New JeszczeNowszyLadTaxCalculator ' zmiana kryteriów obliczania podatku

tax = obj.GetTax(params)

Wówczas moim zdaniem bardziej widoczny jest urok interfejsów, bo w ogóle nie grzebiemy w istniejącym kodzie poza zmianą "new XXXX", jedynie dodajemy implementację i zmieniamy jedną linijkę w głównym makrze. Każdy, kto utrzymywał dużą aplikację, szczególnie w VBA, wie jakim koszmarem jest zmiana wymagań biznesowych klienta. Tydzień temu umawialiśmy się na X jako niezmiennik, a dziś dzwoni i zmieniamy, co przy źle napisanej apce ciągnie lawinę zmian i potencjalnych problemów. Dobre wykorzystanie interfejsów pomaga zmniejszyć ten ból to minimum. A każda zmiana istniejącego kodu, to potencjalnie problem.

Ale co do implementacji, to już kwestie preferencji, nie zbaczajmy z wątku za bardzo :)