Pchełki VBA – odcinek 10: logger

https://xpil.eu/00a08 📋

Dzisiaj mamy jubileusz, pierwsza dziesiątka pchełek za nami!

Tym razem zamiast pchełki będzie pchła, a w zasadzie pchliszcze. Napiszemy dziś więcej kodu niż zwykle, bo i zadanie do wykonania będzie nieco trudniejsze. Ale może się czegoś przy okazji nauczymy...

Zadanie: napisać prosty logger, który dla jednego z arkuszy będzie rejestrował wszelkie działania na tym arkuszu: zmiany zaznaczenia, zmiany wartości komórek, aktywację / dezaktywację arkusza itd.

Zarys rozwiązania:

Napiszemy sobie procedurę o nazwie Loguj, która będzie zapisywała informacje o zdarzeniach arkusza do osobnego arkusza nazwanego "log". Jeżeli arkusz "log" nie istnieje, zostanie automatycznie utworzony. Jeżeli zaś - pechowo - istnieje i zawiera jakieś pożyteczne informacje... Hm, zobaczymy 😉

Następnie będziemy wołać procedurę Loguj z poszczególnych procedur obsługi zdarzeń arkusza, z odpowiednimi parametrami.

Kod:

Do nowego modułu wklejamy kod:

Option Explicit

Global WylaczLogowanie As Boolean

Public Enum Akcja
 ZmianaWartosci
 ZmianaZaznaczenia
 AktywacjaArkusza
 DezaktywacjaArkusza
 Przeliczenie
End Enum

Public Sub Loguj(a As Akcja, adres As String, wart As Variant)
 If WylaczLogowanie Then Exit Sub
 Dim s As Excel.Worksheet
 Dim cs As Excel.Worksheet
 On Error Resume Next
 Set s = Worksheets("log")
 If Err.Number <> 0 Then
  Set cs = ActiveSheet
  WylaczLogowanie = True
  Set s = Worksheets.Add
  s.Name = "log"
  cs.Activate
  WylaczLogowanie = False
 End If
 On Error GoTo 0
 Dim r As Excel.Range
 Set r = s.Range("A:A").SpecialCells(xlCellTypeLastCell).Offset(1, 0)
 r.Value = "Akcja: " & Switch(a = AktywacjaArkusza, "Aktywacja", a = DezaktywacjaArkusza, "Dezaktywacja", a = Przeliczenie, "Przeliczenie", a = ZmianaWartosci, "Zmiana wartości", a = ZmianaZaznaczenia, "Zmiana zaznaczenia", True, "?")
 If a = ZmianaWartosci Or a = ZmianaZaznaczenia Then
  r.Value = r.Value & ", adres: " & adres
  If a = ZmianaWartosci Then
   If IsArray(wart) Then
    r.Value = r.Value & ", nowe wartości:"
    Dim i As Long, j As Long
    For i = LBound(wart, 1) To UBound(wart, 1)
     For j = LBound(wart, 2) To UBound(wart, 2)
      r.Value = r.Value & " " & wart(i, j)
     Next j
    Next i
   Else
    r.Value = r.Value & ", nowa wartość: " & wart
   End If
  End If
 End If
End Sub

Do modułu arkusza, którego zdarzenia chcemy logować, wklejamy:

Option Explicit

Private Sub Worksheet_Activate()
 Loguj AktywacjaArkusza, "", Null
End Sub

Private Sub Worksheet_Calculate()
 Loguj Przeliczenie, "", Null
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 Loguj ZmianaWartosci, Target.Address, Target.Value
End Sub

Private Sub Worksheet_Deactivate()
 Loguj DezaktywacjaArkusza, "", Null
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Loguj ZmianaZaznaczenia, Target.Address, Null
End Sub

Omówienie kodu:

Option Explicit

Option Explicit było już omawiane, ale przypomnę: wymuszamy deklarowanie zmiennych przed ich użyciem. Pozorne utrudnienie, jednak na dłuższą metę bardzo pomocne i redukuje ilość błędów w kodzie już na etapie kompilacji.

Global WylaczLogowanie As Boolean

Deklarujemy zmienną globalną WylaczLogowanie, typu prawda/fałsz. Zmienna globalna to taka zmienna, którą "widać" również w innych modułach. Potrzebujemy jej w celu chwilowego wyłączenia logowania zdarzeń - szczegóły za chwilę.

Public Enum Akcja
 ZmianaWartosci
 ZmianaZaznaczenia
 AktywacjaArkusza
 DezaktywacjaArkusza
 Przeliczenie
End Enum

Słówko Enum umożliwia zdefiniowanie typu wyliczeniowego. Typ wyliczeniowy to taki typ danych, który może przechowywać jedną z zadanych uprzednio wartości. W naszym przypadku wartości te to rodzaje obsługiwanych przez nas zdarzeń.

Technicznie rzecz biorąc, typ Akcja jest typem całkowitoliczbowym, a kolejne wartości wyliczane są równe kolejnym liczbom naturalnym, począwszy od 1, a więc: ZmianaWartosci=1, ZmianaZaznaczenia=2 itd.

Public Sub Loguj(a As Akcja, adres As String, wart As Variant)

Definiujemy naszą główną procedurę logującą Loguj, z parametrami a (akcja, np. zmiana wartości albo aktywacja arkusza itd), adres (w przypadku zmiany zawartości komórki bądź zmiany wybranej komórki na inną, chcemy znać jej adres) wreszcie wart czyli wartość zmienianej komórki (lub komórek, jeżeli zmienimy kilka na raz, np. za pomocą ^V)

If WylaczLogowanie Then Exit Sub

Tutaj sprawdzamy czy globalna flaga WylaczLogowanie jest ustawiona, i jeżeli tak to od razu kończymy działanie procedury logującej - nic nie robimy. Czemu tak? Zobaczymy za chwilę...

Dim s As Excel.Worksheet
Dim cs As Excel.Worksheet

Tutaj deklarujemy dwie zmienne typu Worksheet (początkowo puste).

On Error Resume Next
Set s = Worksheets("log")

Włączamy obsługę błędu a następnie próbujemy ustawić zmienną s na arkusz o nazwie "log". Jeżeli się uda, fajnie, a jeżeli nie to wystąpi błąd, który obsłużymy za chwilę.

If Err.Number <> 0 Then

Tu sprawdzamy, czy w poprzedniej instrukcji wystąpił błąd. Jeżeli tak, oznacza to, że arkusz "log" nie istnieje i trzeba go utworzyć.

 Set cs = ActiveSheet

Ustawiamy zmienną cs na bieżący arkusz (dlaczego? wstawienie nowego arkusza "log" spowoduje automatyczne przełączenie się na niego, trzeba będzie potem jakoś wrócić na arkusz bieżący)

 WylaczLogowanie = True
 Set s = Worksheets.Add
 s.Name = "log"
 cs.Activate
 WylaczLogowanie = False
End If

Tutaj odrobina magii. Najpierw ustawiamy flagę WylaczLogowanie, a następnie dodajemy nowy arkusz i zapamiętujemy go w zmiennej s, potem nazywamy ten nowy arkusz "log", wreszcie wracamy do arkusza cs i na koniec czyścimy (zerujemy) flagę WylaczLogowanie.

Dlaczego tak?

Jak już wspomniałem, dodanie nowego arkusza "log" spowoduje automatyczne przełączenie się na ten arkusz. A przełączenie się na inny arkusz wygeneruje zdarzenie "Deactivate" bieżącego arkusza, które przecież będziemy automatycznie logować. W efekcie, procedura Loguj wywoła samą siebie, próbując zalogować zdarzenie bieżącego deaktywacji arkusza. Jednak nie zaloguje go, ponieważ flaga będzie ustawiona. Podobnie w drugą stronę - jak już utworzymy nowy arkusz i nazwiemy go "log", wracamy do naszego arkusza bieżącego, co wygeneruje zdarzenie "Activate", co spowoduje po raz kolejny wywołanie naszej procedury. I znów, powrót nie będzie zalogowany dzięki ustawionej fladze WylaczLogowanie.

On Error GoTo 0

Wyłączamy obsługę błędu.

Dim r As Excel.Range
Set r = s.Range("A:A").SpecialCells(xlCellTypeLastCell).Offset(1, 0)

Deklarujemy zmienną r typu Range a następnie ustawiamy ją na komórkę tuż poniżej ostaniej zajętej komórki w kolumnie A. Zmienna ta to nasz "kursor", za pomocą którego wpisujemy logowaną informację do arkusza "log".

Uwaga: jeżeli kolumna A jest pusta, metoda SpecialCells(xlCellTypeLastCell) zwróci komórkę A1, a więc logowanie zacznie się od komórki A2.

r.Value = "Akcja: " & Switch(a = AktywacjaArkusza, "Aktywacja", a = DezaktywacjaArkusza, "Dezaktywacja", a = Przeliczenie, "Przeliczenie", a = ZmianaWartosci, "Zmiana wartości", a = ZmianaZaznaczenia, "Zmiana zaznaczenia", True, "?")

Ustawiamy wartość komórki r na tekst "Akcja: " z dołączonym opisem akcji w zależności od wartości pierwszego parametru procedury Loguj. Po szczegóły działania funkcji Switch zapraszam do poprzednich odcinków.

If a = ZmianaWartosci Or a = ZmianaZaznaczenia Then
 r.Value = r.Value & ", adres: " & adres

Jeżeli obsługujemy akcję zmiany wartości komórki bądź zamiany zaznaczenia, zapisujemy do logu adres zmienianej / zaznaczanej komórki (lub komórek).

 If a = ZmianaWartosci Then

Jeżeli ponadto logowanie wywołała akcja zmiany wartości komórki (komórek), chcemy tą wartość również zalogować.

  If IsArray(wart) Then
   r.Value = r.Value & ", nowe wartości:"

Tu chwila uwagi. Parametr wart będzie albo pojedynczą wartością (jeżeli zmieniamy jedną komórkę) albo też dwuwymiarową ("prostokątną") tablicą wartości poszczególnych komórek (jeżeli zmieniamy kilka komórek na raz). Funkcja IsArray zwraca prawdę jeżeli testowana zmienna jest tablicą (wiele komórek). Tym samym dopisujemy na koniec naszej komórki tekst ", nowe wartości: " po czym musimy wymienić wszystkie wartości po kolei:

   Dim i As Long, j As Long
   For i = LBound(wart, 1) To UBound(wart, 1)
    For j = LBound(wart, 2) To UBound(wart, 2)
     r.Value = r.Value & " " & wart(i, j)
    Next j
   Next i

Tutaj wędrujemy po wszystkich elementach dwuwymiarowej tablicy wart i dopisujemy każdy element na koniec komórki r (poprzedzony spacją dla czytelności).
Funkcja LBound zwraca dolny indeks tablicy (w naszym przypadku będzie to zawsze 1), Funkcja UBound zaś zwraca górny (ostatni, maksmymalny) indeks tablicy. Dodatkowo, ponieważ tablica jest dwuwymiarowa, używamy drugiego parametru funkcji LBound/UBound, który oznacza numer wymiaru. Czyli zewnętrzna pętla For (zmienna i) będzie iterować po pierwszym wymiarze, a wewnętrzna (zmienna j) po drugim.

   Else
    r.Value = r.Value & ", nowa wartość: " & wart
   End If

W przeciwnym przypadku, jeżeli zmienna wart nie jest tablicą, zmieniona była tylko jedna komórka - logujemy jej wartość do komórki r.

  End If
 End If
End Sub

Na koniec domykamy wszystkie pootwierane wcześniej bloki If / Sub.

Teraz rzućmy okiem na sposób, w jaki będziemy wołać naszą procedurę Loguj:

Private Sub Worksheet_Activate()
 Loguj AktywacjaArkusza, "", Null
End Sub

Obsługa zdarzenia Activate arkusza nie wymaga podawania ani adresu komórki ani jej wartości - dotyczy bowiem całego arkusza a nie jego części.

Private Sub Worksheet_Calculate()
 Loguj Przeliczenie, "", Null
End Sub

Podobnie jak w poprzednim przypadku, przeliczenie jest zdarzeniem globalnym dla całego arkusza i nie wymaga przekazania wartości ani adresu komórki.

Private Sub Worksheet_Change(ByVal Target As Range)
 Loguj ZmianaWartosci, Target.Address, Target.Value
End Sub

Tutaj przekazujemy funkcji Loguj zarówno adres zmienianej komórki jak też jej wartość.

Private Sub Worksheet_Deactivate()
 Loguj DezaktywacjaArkusza, "", Null
End Sub

Dezaktywacja - podobnie jak aktywacja i przeliczenie - nie wymaga przekazywania informacji o poszczególnych komórkach.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Loguj ZmianaZaznaczenia, Target.Address, Null
End Sub

Zmiana zaznaczenia wymaga podania adresu nowo zaznaczonej komórki (bądź komórek), jednak bez nowej wartości (nie zmieniamy zawartości komórek, tylko zaznaczenie).

Wady rozwiązania?

Ano, jest kilka. Jedna jest na przykład taka, że wstawienie/skasowanie kolumny generuje zdarzenie Change dla całej kolumny, w efekcie czego logujemy bezsensownie milion pustych wartości, spowalniając operację wstawiania kolumny i zajmując mnóstwo miejsca w logu.
Ponadto, ograniczona jest ilość logowanych rodzajów zdarzeń - możemy wybierać spośród tego, co oferuje nam VBA, a faktycznych rodzajów zdarzeń jest o wiele więcej (jednak żeby się do nich dobrać trzeba już grzebnąć głębiej w bibliotekach systemowych).
Jeszcze jedna wada - po dotarciu do końca kolumny A procedura Loguj wygeneruje błąd (skończy się miejsce na kolejne wpisy).

Wszystkie te wady dałoby się bezproblemowo skorygować, ale przez to nasza pchła (i tak już przerośnięta) zaczęłaby przypominać siedmiogłowego dinozaura na sterydach.

 

https://xpil.eu/00a08 📋