Pchełki VBA – odcinek 4: od AAA do ZZZ

https://xpil.eu/N8u0Q 📋

Niedawno pisałem o tym, jak wygenerować wszystkie kombinacje dwuliterowe liter alfabetu łacińskiego. Udało się, i to bez krztyny kodu VBA

- uciekliśmy się wtedy do "brudnego" tricku i powyciągaliśmy te kombinacje z nazw kolumn arkusza.

Dziś wygenerujemy sobie wszystkie kombinacje trzyliterowe. A więc: AAA, AAB, AAC, ..., ZZX, ZZY, ZZZ.

Zacznijmy od policzenia ile ich właściwie będzie?

Liter alfabetu łacińskiego jest 26. Skąd to wiem? Ano, stąd: "A" ma kod ASCII 65 a "Z" ma kod 90, 90-65+1=26.

Kombinacji trzyliterowych będzie więc 26*26*26=17576. Niecałe osiemnaście tysięcy - wystarczająco mało, żeby zmieściło się do arkusza w jednej kolumnie. I to nawet w starym Excelu.

Teraz kod:

(kod należy wkleić do nowego modułu - jak dodać nowy moduł? zerknij do poprzednich odcinków)

Option Explicit

Public Sub kombinacje()
    Dim i As Integer, j As Integer, k As Integer, n1 As Integer, n2 As Integer
    Dim r As Excel.Range
    Set r = Range("A1")
    n1 = 65
    n2 = 90
    For i = n1 To n2
        For j = n1 To n2
            For k = n1 To n2
                r.Value = Chr(i) & Chr(j) & Chr(k)
                Set r = r.Offset(1, 0)
            Next k
        Next j
    Next i
End Sub

Zanim przejdę do tłumaczenia kodu (które będzie długie i nudne, jak cały ten blog), sprawdźmy, czy ten kod w ogóle działa. A więc, ustawiamy się kursorem gdziekolwiek wewnątrz powyższej procedury i naciskamy F5. Następnie zaglądamy do arkusza - jeżeli w kolumnie A widnieją wszystkie kombinacje od AAA do ZZZ, udało się. Jeżeli nie... to się nie udało.

OK, teraz - tradycyjnie, tłumaczymy kod. Linijka po linijce. Rzygi.

Option Explicit

To już było tłumaczone. Zajrzyj sobie do pierwszej lekcji.

Public Sub kombinacje()
...
End Sub

Blok Sub - End Sub też już był tłumaczony - w pierwszym odcinku.

    Dim i As Integer, j As Integer, k As Integer, n1 As Integer, n2 As Integer

Tu deklarujemy pięć różnych zmiennych numerycznych. Trzy pierwsze (czyli i, j, k) to zmienne kontrolne pętli For (będą trzy zagnieżdżone pętle For, stąd trzy zmienne). Dwie ostatnie (n1, n2) będą przechowywać zakres wartości zmiennych pętli (czyli zakres kodów ASCII znaków, dla których generujemy kombinacje - w naszym przypadku od 65 do 90)

Zanim przejdziemy dalej, rzućmy okiem czym jest pętla For.

Pętla to (w większości języków programowania) jedna z metod kontroli wykonania kodu. Kod może się rozgałęziać (a więc możemy chcieć wykonać bądź pominąć jakiś blok kodu pod pewnym warunkiem) - do tego celu służy w VBA konstrukcja If - End If, o której pisałem niedawno. Czasami jednak chcemy, żeby ten sam blok kodu wykonał się więcej niż raz - do tego służą pętle.

W VBA istnieją cztery rodzaje pętli. Bez wnikania w szczegóły działania, te rodzaje to:

Do-Loop, While-Wend, For-Next oraz For Each

Dwa pierwsze rodzaje pętli (Do-Loop oraz While-Wend) umożliwiają zdefiniowanie warunku wyjścia z pętli - nie definiują natomiast explicite ilości iteracji (iteracja to jednorazowe wykonanie pętli). Pętla For-Next wymaga numerycznej zmiennej kontrolnej - to nasz przypadek i za chwilę omówię go szerzej - wreszcie pętla For Each to nic innego jak pętla iterująca po elementach kolekcji.

Dzisiejszy bohater dnia to pętla For-Next. Działa ona następująco:

For x = a To b 
    ...
Next 

Tak zdefiniowana pętla wykona się w następujący sposób: najpierw do zmiennej x zostanie podstawiona wartość numeryczna a, następnie zostanie sprawdzony warunek czy przekroczyła b, jeżeli nie to wykona się po czym zostanie zwiększona o jeden i - jeżeli nie przekroczyła b - znów wykona się, znów pójdzie o jeden w górę i tak dalej. Prędzej czy później, przekroczy b o jeden i pętla zakończy się, przekazując sterowanie następnej linii kodu (tej po Next ).

    Dim r As Excel.Range

Tutaj deklarujemy nasz wirtualny kursor, czyli zmienną typu Range, za pomocą której będziemy wpisywać do bieżącej komórki wartość kombinacji, a potem "przesuwać się" do następnej komórki poniżej.

 Set r = Range("A1")

Tutaj ustawiamy nasz wirtualny kursor na pierwszej komórce w kolumnie A - plan jest taki, żeby po każdorazowym wyliczeniu kolejnej kombinacji przesunąć się do kolejnego wiersza.

    n1 = 65
    n2 = 90

Tutaj z kolei ustalamy wartości skrajne, pomiędzy którymi będą iterowane nasze pętle. Ponieważ chcemy wygenerować wszystkie kombinacje liter A-Z, ustawiamy n1 na kod ASCII litery A, a n2 na kod ASCII litery Z.

Przy okazji, Czytelnik-samobójca, który dotarł aż tutaj i jeszcze nie zaczął z nudów obgryzać kabelka od myszki (bądź też, nieszczęsny, ma mysz bezprzewodową, i właśnie zabiera się za kabel zasilający monitora), być może zauważył, że przypisując wartość do zmiennej typu Range użyłem słówka Set, ale nie zrobiłem tego przy podstawieniu wartości do zmiennej typu Integer. Czemu tak?

Ano temu, że składnia VBA wymaga użycia słówka Set przy podstawianiu do zmiennych typów złożonych, a nie wymaga go dla typów prostych. O różnicy między typem prostym a złożonym już pisałem - zapraszam do lektury pierwszych trzech odcinków po szczegóły.

OK, mamy wszystko czego potrzeba, czas najwyższy wygenerować jakieś kombinacje!

     For i = n1 To n2
        For j = n1 To n2
            For k = n1 To n2

Tutaj otwieramy trzy pętle For, zagnieżdżone jedna w drugiej. Pętla zewnętrzna (ze zmienną kontrolną i) reprezentuje pierwszy znak kombinacji, pętla środkowa (ze zmienną j) drugi znak, a pętla wewnętrzna - trzeci.

Gdybym miał podjąć próbę wyjaśnienia jak działają pętle zagnieżdżone, prawdopodobnie uciekłbym się do analogii astronomicznej. Wewnętrzna pętla to Księżyc zapętlony wokół Ziemi, pętla środkowa to Ziemia zapętlona wokół Słońca, wreszcie pętla zewnętrzna to Słońce krążące wokół, za przeproszeniem, jądra Galaktyki.

Ale nie podejmę takiej próby, gdyż spowodowałoby to zapewne więcej szkód niż pożytku, a także mogłoby spowodować, że Kopernik wraz z Galileuszem zapętliliby się z przerażenia w swoich grobach, komplikując całość zagadnienia do czwartej potęgi.

OK, lecimy dalej.

                r.Value = Chr(i) & Chr(j) & Chr(k)

Tu odbywa się magia właściwa, czyli wyliczenie trzyliterowej kombinacji. Żeby nie być posądzonym o lenistwo, omówię tę linijkę szczegółowo, nie bacząc na schnącą z przerażenia ślinę na ustach mojego ostatniego żyjącego Czytelnika.

Funkcja Chr(liczba) zwraca znak o kodzie ASCII równym podanej liczbie. Na przykład, Chr(66) zwróci literę "B" a Chr(32) - spację.

Operator & (po naszemu ampersand) służy do konkatenacji stringów. Pardon, łańcuchów tekstowych. Tekstów.

Konkate-co?

Konkatenacja to nic innego jak łączenie tekstów. Wynikiem konkatenacji tekstów "nudny" oraz "blog" będzie tekst "nudnyblog" - żeby nadać temu wynikowi jakąś wartość jakąś merytoryczną, trzeba tam jeszcze dokonkatenować spację, o tak: "nudny" & " " & "blog" da w wyniku "nudny blog".

Proste, prawda?

A więc tworzymy, za pomocą operatora konkatenacji, trzyznakowy tekst. Pierwszy znak ma kod ascii równy wartości zmiennej i, drugi j a trzeci k. Ponieważ na początku wszystkie trzy zmienne są równe 65, w wyniku dostaniemy tekst "AAA", który zostanie wpisany do komórki pod naszym wirtualnym kursorem r (czyli komórki A1).

Set r = r.Offset(1, 0)

Tutaj przesuwamy nasz wirtualny kursor o jeden wiersz w dół.

Metoda Offset(x,y) obiektu klasy Range zwraca inny obiekt tej klasy, przesunięty względem oryginalnego obiektu o x wierszy w dół oraz o y kolumn w prawo. Przykłady:

Range("A1").Offset(1,0) oznacza komórkę przesuniętą o jeden wiersz w dół względem komórki A1, czyli A2.

Range("C10").Offset(0,3) to komórka znajdująca się o 3 kolumny w prawo od komórki C10, czyli F10.

Range("B10").Offset(3,2) to komórka 3 wiersze w dół i dwie kolumny w prawo od komórki B10, czyli D13.

Range("A1:A10").Offset(1,1) to zakres komórek A1:A10 przesunięty o jedną komórkę w dół i w prawo, czyli B2:B11

Range("A1").Offset(1,2).Offset(3,4) to komórka przesunięta o trzy wiersze i cztery kolumny względem komórki przesuniętej o jeden wiersz i dwie kolumny względem komórki A1, czyli G5

Jeżeli podamy ujemną wartość przesunięcia, oznacza to przesunięcie w lewo (bądź w górę), czyli:

Range("A10").Offset(-9,0) da w wyniku komórkę A1.

I tak dalej.

Po przesunięciu naszego wirtualnego kursora o jeden wiersz w dół, zamykamy po kolei wszystkie trzy pętle:

             Next k
        Next j
    Next i

Co to oznacza?

Oznacza to, że pętla wewnętrzna (kontrolowana przez zmienną k) zwiększy wartość zmiennej k o jeden, po czym sprawdzi czy zmienna ta wyszła poza górny zakres (90). Ponieważ tak nie jest (zmienna po zwiększeniu o jeden ma teraz wartość 66), pętla wykona się po raz kolejny. Zauważmy, że obydwie pozostałe pętle są ciągle w trakcie swoich pierwszych iteracji (a więc zmienne i oraz j są ciągle równe 65). Tym samym, druga iteracja pętli wewnętrznej wygeneruje AAB w komórce A2, trzecia AAC w komórce A3 i tak dalej, aż do wartości AAZ, po wygenerowaniu której zmienna k osiągnie wartość 91 i pętla się zakończy. Sterowanie zostanie przekazane do linii bezpośrednio po linii "Next k", a więc do linii "Next j".

A więc?

Ano, właśnie zakończyła się pierwsza iteracja "środkowej" pętli. Wartość zmiennej j zostanie zwiększona o jeden. Ponieważ j jest ciągle mniejsze od 90 (a konkretnie jest równe 66), zostanie wykonany blok kodu znajdujący się wewnątrz pętli j - czyli znów pętla k, ponownie od k=65 do k=90. Tym samym wygenerowane zostaną wszystkie ciągi od ABA do ABZ. Potem j zwiększy się do 67 (ACA...ACZ) i tak dalej aż do 90 (AZA...AZZ).

Identycznie zadziała pętla zewnętrzna (ze zmienną i)

Tym samym, pętla wewnętrzna wykona się 17576 razy, pętla środkowa 676 razy a pętla zewnętrzna 26 razy.

Proste?

Ponieważ, obawiam się, ostatni świadomy Czytelnik uciekł w panice jakieś dwie pętle temu, ja również udam się teraz na zasłużony odpoczynek.

Wszelkie pytania (np. "którędy do ubikatora?") proszę pozostawiać w komentarzach.

https://xpil.eu/N8u0Q 📋