Na początek napiszę się co sprawia, że dobry kod jest dobry. Później przejdę do praktycznych elementów programowania.
Poszczególne fragmenty są mniej lub bardziej powiązane. Nie odważę się powiedzieć, że pewne zasady są ważniejsze niż inne. Zdecydowanie należy przyswoić sobie je wszystkie i zacząć stosować bez wyjątku. Zaoszczędzicie sobie tym dużo czasu i nerwów. Zapewniam, że wcześniej czy później każda z nich zemści się, jeżeli o niej zapomnimy.
Cechy dobrego kodu
- Prostota – Nie rób w dziesięciu instrukcjach coś co możesz zrobić w pięciu. Mniej instrukcji to mniej okazji na błąd. Każdą rzecz można zrobić na 10 sposobów, wybierz najprostszy.
- Czytelność – Twój kod musi być czytelny dla innych i samego Ciebie, kiedy wrócisz do niego za pół roku i będziesz się zastanawiać co oznacza zmienna o nazwie trx. Można było ją nazwać tax_rate_expenses.
- Modularność – VBA udostępnia procedury, korzystaj z nich. Składaj później program z nich jak z klocków. Samochód jest złożony z części o różnej funkcjonalność, niech twój program też tak działa.
- Wydajność – Czy twój program działa w czasie akceptowalnym dla użytkownika?
- Niezawodność – Czy twój program generuje błędy? Na jakie błędy jesteś przygotowany?
Przygotowanie do kodowania
Mamy już przygotowaną specyfikację od strony biznesowej. Czas zająć się dokładniejszym opisem. Rozrysuj sobie wszystkie formularze i arkusze. Praca pójdzie dużo szybciej, jeżeli nie będziesz projektował „na żywo”. Łatwiej coś zmienić na papierze, niż w edytorze. Zastanów się, które elementy są potrzebne a które zbędne. Możesz napisać swój program w pseudo kodzie.
1 2 3 4 5 6 7 8 9 10 |
Sub analiza() ' Sprawdź, czy data wejściowa jest prawidłowa ' Opróżnij docelowy arkusz ' Zaznacz dane mieszczące się w zakresie ' Kopiuj dane do docelowego arkusza ' Formatuj docelowe dane ' Stworz wykres End Sub |
Taki kod może być później bezpośrednio wpisany w module. Kolejne linie będziemy wtedy uzupełniać o prawdziwe instrukcje. Na przykład poniżej uzupełniam pierwszy komentarz.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Option Explicit Const gc_AdresDataWejsciowa As String = "A1" Const gc_MsgDataNieprawidlowa As String _ = "Data wejściowa nieprawidłowa" Sub analiza() ' Sprawdź, czy data wejściowa jest prawidłowa If Not IsDate(Range(gc_AdresDataWejsciowa)) Then MsgBox gc_MsgDataNieprawidlowa Exit Sub End If ' Opróżnij docelowy arkusz ' Zaznacz dane mieszczące się w zakresie ' Kopiuj dane do docelowego arkusza ' Formatuj docelowe dane ' Stworz wykres End Sub |
Komentarze
Dobrze napisane komentarze są nie do przecenienia. Każda procedura powinna w pierwszej linii zawierać komentarz co robi. Umieszczamy też komentarze przed bardziej skomplikowanymi fragmentami kodu. Nie należy też popadać w przesadę i umieszczać komentarz w każdej linii kodu. Dobra nazwa zmiennej lub procedury sama jest świetnym komentarzem.
Nazewnictwo zmiennych
To co na pierwszy rzut oka profesjonalistę od amatora, to stosowanie odpowiedniego nazewnictwa zmiennych i stałych. Ponieważ jest to dość obszerny temat omówię go w następnej części kursu.
Używanie stałych
Używanie literałów (bezpośrednio wpisanych wartości) w kodzie to jeden z najczęściej popełnianych błędów. Zamiast pisać:
1 2 3 4 5 |
Option Explicit Sub test() Sheets(1).Select End Sub |
należy napisać:
1 2 3 4 5 6 |
Option Explicit Const gc_ArkuszZrodlowy As Integer = 1 Sub test() Sheets(gc_ArkuszZrodlowy).Select End Sub |
Dzięki temu szybko możemy zmienić numer arkusza źródłowego, nawet jeżeli użyjemy go w wielu miejscach kodu.
Inny przykład. Jeżeli chcemy znaleźć pierwszy wolny wiersz w kolumnie możemy napisać:
1 2 3 4 5 6 |
Option Explicit Dim l As Integer Sub test() l = Range("A65536").End(xlUp).Row + 1 End Sub |
Dużo bezpieczniej będzie jednak jeżeli wyeliminujemy literały „A65536” i 1. Dodatkowo kod zamkniemy w funkcji FirstFreeRow, którą możemy później wielokrotnie używać do określenia pierwszego wolnego wiersza. Zmienna l uzyska też odpowiednią nazwę.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Option Explicit Const gc_SalesValuesColumn As Integer = 1 Const gc_NextRow As Integer = 1 Sub test() MsgBox "Nr pierwszego wolnego wiersza: " _ & FirstFreeRow(gc_SalesValuesColumn) End Sub Function FirstFreeRow(ii_ColumnSearched As Integer) As Long Dim i_FirstFreeRow As Integer i_FirstFreeRow = Cells(Rows.Count, ii_ColumnSearched). _ End(xlUp).Row + gc_NextRow FirstFreeRow = i_FirstFreeRow End Function |
Powyższy kod na pierwszy rzut oka jest bardziej skomplikowany. Jednak:
- Stworzyliśmy uniwersalną funkcję, która może przeszukiwać różne kolumny.
- Od razu widać, że przeszukujemy kolumnę z wartościami sprzedaży. Jeżeli wartości te przeniesiemy do innej kolumny, możemy łatwo zmodyfikować kod.
- Funkcja jest niezależna od wersji Excela (liczby wierszy, Rows.Count ).
Wcięcia
Wcięcia są najłatwiejszą metodą zwiększenia czytelności kodu. Każdy blok kodu powinien być wcięty o jeden tabulator względem bloku w którym się znajduje. Np.:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Option Explicit Const gc_ZakresZmiany As String = "A1:A20" Const gc_FontSizeMin = 10 Sub Formatuj() Dim ref_WorkCell As Range Dim i As Integer For Each ref_WorkCell In Range(gc_ZakresZmiany).Cells For i = 1 To Len(ref_WorkCell.Text) ref_WorkCell.Characters(i, i).Font.Size = gc_FontSizeMin + i Next i Next ref_WorkCell End Sub |
Powyżej nazwa procedury jest przesunięta najbardziej w lewo. Deklaracje zmiennych i pierwsza pętla są już przesunięte jeden tabulator w prawo. Wnętrze pętli For Each, czyli druga pętla są przesunięte jeszcze bardziej w prawo itd.
Używanie tylko zmiennych lokalnych
Jak ognia należy unikać zmiennych globalnych. Zmienne globalne mogą być zmieniane w całym pliku. Jeżeli kiedyś zauważymy, że nasza zmienna przyjmuje nieoczekiwaną wartość trudno może być znaleźć miejsce, gdzie jest zmieniana. W przypadku zmiennych lokalnych nasze poszukiwania ograniczają się do jednej procedury.
Dodatkowo zmienna globalna staje się stanem całego programu. Zmieniając wartość takiej zmiennej możemy nie pamiętać gdzie jest jeszcze używana i na jakie fragmenty kodu może mieć to wpływ.
1 2 3 4 5 6 7 8 9 10 |
Option Explicit Dim gdtm_DataSprzedazy As Date Sub analiza() gdtm_DataSprzedazy = InputBox("Podaj datę sprzedaży:") SprawdzanieDaty Filtrowanie Formatowanie End Sub |
1 2 3 4 5 6 7 8 9 10 |
Option Explicit Sub analiza() Dim dtm_DataSprzedazy As Date dtm_DataSprzedazy = InputBox("Podaj datę sprzedaży:") SprawdzanieDaty dtm_DataSprzedazy Filtrowanie dtm_DataSprzedazy Formatowanie End Sub |
W ten sposób wiemy, że zmienna dtm_DataSprzedazy nie będzie zmieniana nigdzie poza procedurą analiza, bo w niej jest zadeklarowana. Wiemy też dokładnie dla których procedur data sprzedaży jest istotna.
Używanie stałych globalnych
W odróżnieniu do zmiennych, stałe powinny być globalne. Staja się wtedy cechą całego programu. Jeżeli tworzymy sobie stałą gc_ArkuszSprzedazy, to chcemy, żeby w całym pliku ona wskazywał na ten sam arkusz. Jednocześnie zmieniając tą zmienną w jednym miejscu cały nasz program będzie od tej pory szukał danych sprzedaży w innym arkuszu. Innymi słowy, jeżeli na zielonym świetle się przejeżdża przez ulicę, to chcielibyśmy, żeby tak było na całym świecie.
Dobrą praktyką jest przerzucenie wszystkich stałych do osobnego modułu. Tworzymy w ten sposób swego rodzaju konfigurację programu. Jeżeli zastosujemy stałe we wszystkich kluczowym miejscach, to możemy znacznie modyfikować działanie całego programu poprzez przestawienie jednej wartości.
Ustawienia edytora
Dobrym zwyczajem jest włączenie Option Explicit i wyłączenie komunikatu o błędzie składni. Informacje o tym znajdują się w następnej części kursu.
Wyłączenie aktualizacji ekranu i przeliczania
Każda edycja komórki powoduje przeliczenie arkusza i odświeżenie ekranu. Znaczne przyspieszenie programu uzyskamy przez wyłączenie na chwilę tych dwóch rzeczy. W ten sposób zwiększamy wydajność programu.
1 2 |
Application.ScreenUpdating = False Application.Calculation = xlCalculationManual |
Po wykonaniu kodu, należy przywrócić te wartości, aby użytkownik mógł zobaczyć efekt działania programu.
1 2 |
Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic |
Pisanie małych części kodu
Czytelność i modularność kodu uzyskujemy dzięki stosowaniu krótkich procedur. Z reguły procedura powinna mieścić się na jednym ekranie. Jeżeli napisaliśmy większą procedurę warto zastanowić się nad podzieleniem jej na kilka mniejszych.
Stworzenie procedur o określonym wejściu i wyjściu ułatwia nam zdiagnozowanie ewentualnych problemów. Procedury mają argumenty określonych typów. Próba uruchomienia poniższego kodu zakończy się błędem.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Option Explicit Sub test() Dim int_TestowaZmienna As Integer sprawdzanieDaty int_TestowaZmienna End Sub Sub sprawdzanieDaty(idtm_DataWejsciowa As Date) If idtm_DataWejsciowa > Date Then MsgBox "Data nie może być w przyszłości" End If End Sub |
Excel zwraca nam uwagę, że próbujemy podać zmienną typu int do funkcji sprawdzanieDaty. W tej funkcji zabezpieczyliśmy się, że chcemy przyjąć jako wejście tylko datę.
Zachęcam do zadawania pytań w komentarzach. Napisz, jeżeli masz inne propozycje praktyk ułatwiających programowanie.
Comments 4
Mam pytanie do stałych. Mam skoroszyt z kilkoma arkuszami. Do tej pory w każdej procedurze miałem taki kod:
Dim wb As Excel.Workbook
Dim zakup As Excel.Worksheet
Set wb = ThisWorkbook
Set zakup = wb.Worksheets("zakup")
Chciałbym móc raz zdefiniować te zmienne dla całego programu (wtedy będą stałymi?), ale Excel nie pozwala mi na to. Gdy zmienię Dim na Const i przerzucę powyższy kod poza procedurę pojawia się błąd: Invalid Outside Procedure. Jak to zrobić poprawnie?
Dziękuję za pytanie. Polecam lekturę poste na temat zmiennych:
http://www.123office.pl/2013/08/kurs-vba-cz-10-zmienne.html
Podział na zmienne i stałe to jedno. Pierwsze można zmieniać, kolejne mają cały czas tą samą wartość. Podział na zmienne/stałe globalne i lokalne to drugie.
Można ten kod napisać tak:
Public Const gcon_ArkuszZakupowyNazwa As String = "zakup"
Public gref_wb As Excel.Workbook
Public gref_zakup As Excel.Worksheet
Sub test()
Set gref_wb = ThisWorkbook
Set gref_zakup = gref_wb.Worksheets(gcon_ArkuszZakupowyNazwa)
End Sub
Generalnie warto zamienić "wb" na coś znaczącego. Używanie zmiennych globalnych to też proszenie się o problemy.
Dziękuję za odpowiedź. W ogóle dziękuję za cały kurs, najlepszy z jakim miałem styczność do tej pory.
Czyli stałą będzie nazwa arkusza, ale już gref_zakup będzie zmienną, tyle że globalną? To chyba mija się z celem…
Ostatecznie zrobiłem w ten sposób, że
Set wb = ThisWorkbook
Set zakup = wb.Worksheets("zakup")
przeniosłem na początek modułu przed pierwszą procedurą. W ten sposób zmienna jest zdefiniowana dla całego modułu, prawda?
"Używanie zmiennych globalnych to też proszenie się o problemy." o jakich dokładnie problemach mowa? Czy zmienna może być zmieniona bez mojej wiedzy?
Set wb = ThisWorkbook
Set zakup = wb.Worksheets("zakup")
Te linie nie mogą być poza procedurą. Otrzyma Pan wtedy błąd "Invalid outside procedure".
To, czy zmienna jest globalna czy lokalna decyduje umieszczenie jej deklaracji, czyli "dim …". Opisane jest to poniżej:
http://www.123office.pl/2013/08/kurs-vba-cz-10-zmienne.html
"Używanie zmiennych globalnych to też proszenie się o problemy." o jakich dokładnie problemach mowa? Czy zmienna może być zmieniona bez mojej wiedzy?
Powyżej, w części "Używanie tylko zmiennych lokalnych" opisałem zagrożenie związane z używaniem zmiennych globalnych. Może mi Pan uwierzyć na słowo, albo samemu się przekonać, kiedy Pana kod będzie już miał kilka tysięcy linii i trudno będzie Panu znaleźć jaka jest historia stojąca za wartością danej zmiennej.