Walidacja danych nazywana jest także kontrolą lub sprawdzaniem poprawności. Opcja ta występuje w Excelu i służy do nakładania ograniczeń na dane wpisywane do komórek. W przypadku aplikacji VBA, temat ten jest znacznie ważniejszy niż można sądzić. Decyduje bowiem o jakości danych, które posłużą nam później do przygotowania raportów. Kontrola poprawności na formularzu odbywa się głównie na poziomie zdarzeń pola tekstowego.
Spór o koncepcję
W temacie walidacji danych wyróżniłbym trzy koncepcje: inwazyjną, liberalną i pośrednią. Literatura zaleca podejście kompromisowe, formularze internetowe cechuje nastawienie liberalne – Klienci zaś zwykle proszą o rozwiązania restrykcyjne.
Jak widać, sprawa nie jest taka prosta i decyzję o tym, jaką koncepcję wybrać, najlepiej przedyskutować z Klientem. W poniższym artykule opiszę każdą z nich – pokażę również z jakimi zdarzeniami się wiąże.
Formularz bazowy
Rozważmy te koncepcje na konkretnym przykładzie.

Aby uprościć temat, przygotowałem prosty formularz.
Składa się on z czterech pól tekstowych (powiązanych z nimi etykiet) oraz dwóch przycisków.
Pierwsze trzy pola są obowiązkowe, pole z adresem e-mail jest opcjonalne.
Podejście inwazyjne
W pierwszym akapicie napisałem, że moi Klienci zwykle proszą o rozwiązanie restrykcyjne. W praktyce używają bardziej dosadnych słów np. idiotoodporne.
Prośba o rozwiązanie inwazyjne jest podyktowana brakiem wiary w Excelowe skille przyszłych użytkowników aplikacji. Opiekunowie zakładają, że operatorzy mogą sporo napsuć, dlatego trzeba ich zawczasu zdyscyplinować.
Podejście inwazyjne będzie więc polegać, nawet nie na szybkiej informacji o błędzie, co na zmuszeniu użytkownika do podania właściwej informacji. Ten przymus jest osiągany dzięki pewnym blokadom.
Zdarzenie KeyDown
Najbardziej inwazyjnym zdarzeniem jest w mojej ocenie KeyDown. Pozwala ono przechwycić kod wpisanego znaku, zanim wyświetli go w polu tekstowym. Możemy więc sprawdzić czy dany znak jest odpowiedni i zdecydować, czy go wyświetlić czy też nie. Instrukcja KeyCode = 0 zablokuje wyświetlenie znaku.
Zazwyczaj to zdarzenie stosuje się wtedy, gdy chcemy wymusić na użytkowniku wpisanie tylko cyfr np. dla numeru telefonu.
1 2 3 4 5 6 7 8 9 10 |
Private Sub txtTelefon_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) If Shift = 1 Then KeyCode = 0 'Blokuj gdy wciśnięty Shift Select Case KeyCode Case 8, 9, 48 To 57, 96 To 105 'Tylko cyfry, tab i backspace :-) Case Else: KeyCode = 0 End Select End Sub |
Zdarzenie BeforeUpdate
Zdarzeniem nieco mniej inwazyjnym jest BeforeUpdate. Co prawda, pozwala nam wpisać litery w polu z numerem telefonu, ale restrykcyjna kontrola odbywa się przy próbie opuszczenia kontrolki. Zdarzenie to posiada parametr Cancel, który ustawiony na True nie pozwala użytkownikowi przejść do innej kontrolki.
Dla pola z numerem telefonu, możemy ustawić warunek: „Nie wypuszczaj użytkownika, dopóki nie wpisze liczby”. Operator programu będzie więc zmuszony wykasować każdy znak, który nie jest liczbą. W przeciwnym razie, nie będzie mógł wyjść z tego pola.
1 2 3 4 5 |
Private Sub txtTelefon_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) If Not IsNumeric(Me.txtTelefon.Text) Then Cancel = True End Sub |
Oczywiście samo wpisanie liczby nie gwarantuje sukcesu – potrzebujemy jeszcze sprawdzić długość numeru. Na potrzeby tego opracowania, odpuszczę jednak dodatkową walidację.
Zauważmy, że użycie tego zdarzenia w parze z KeyDown nie będzie miało większego sensu. Jeżeli użytkownik może wpisać tylko cyfry (KeyDown), to wiadomo, że wynikiem będzie jakaś liczba i nie ma potrzeby dodatkowo tego sprawdzać (BeforeUpdate).
Warto jednak zauważyć, że dane wpisujemy w polu tekstowym. Wpisując liczbę, w wyniku otrzymamy wartość tekstową typu String. Funkcja IsNumeric dokonuje jednak niejawnej konwersji i wpis np. ”12345” uzna za liczbę.
Podejście kompromisowe
Ta koncepcja pozwala użytkownikowi wpisać to co chce, jednak dość szybko i subtelnie informuje go o tym, że wartość jest nieprawidłowa.
Zdarzenie Change
Zdarzenie to jest wyzwalane za każdym razem przy zmianie wpisu – reaguje na dodanie lub usunięcie znaku. Możemy więc wykorzystać je, aby na bieżąco sprawdzać poprawność wpisu. Jeśli informacja nie jest liczbą, możemy np. wypełnić tło na różowo – kolor czerwony wydaje się tu zbyt drażniący.

1 2 3 4 5 6 7 8 9 |
Private Sub txtTelefon_Change() If IsNumeric(Me.txtTelefon.Text) Then Me.txtTelefon.BackColor = vbWindowBackground Else Me.txtTelefon.BackColor = rgbPink End If End Sub |
Co jednak dziwne – zdarzenie to, nie pozwala sterować kolorem ramki. Aktualizacja barwy ma miejsce dopiero przy przejściu do innej kontrolki… Być może dlatego, że obramowanie jest parametrem opcjonalnym, zaś tło wbudowanym… Dopytam o to na forach.
Zdarzenie AfterUpdate
Sporo osób nie widzi różnicy pomiędzy zdarzeniami BeforeUpdate i AfterUpdate. Wydaje się, że oba zachodzą w momencie wyjścia z pola tekstowego – podobnie zresztą jak zdarzenie Exit.
Różnica dotyczy parametru Cancel. Otóż, jeśli został on ustawiony na True, wówczas wyjście z kontrolki jest zablokowane i nie zachodzą zdarzenia AfterUpdate i Exit. BeforeUpdate zachodzi więc przy samej próbie opuszczenia kontrolki (bez względu na efekt). Jeśli mamy gwarancję przejścia do innej kontrolki – warto użyć AfterUpdate.

Widzimy, że po aktywacji pola txtMail, ramka kontrolki txtTelefon podświetliła się na czerwono.
Jest to informacja dla użytkownika, że wypełnił to pole w sposób nieprawidłowy. Sposób powiadomienia jest jednak subtelny. Nie blokujemy pracy, ale użytkownik widzi, że pole zawiera błąd.
Podejście liberalne
Jeśli wypełnimy jakiś formularz internetowy (np. zapis na newsletter lub założenie konta) to zauważmy, że kontrola poprawności jest tam najsłabsza i zwykle odbywa się dopiero po kliknięciu w przycisk główny tj. Dodaj/Zapisz. Wcześniej nie otrzymujemy żadnych informacji zwrotnych – możemy wpisać co chcemy.
Gdy jednak nie uzupełnimy pól lub zrobimy to błędnie – każde z nich zostanie podświetlone i opatrzone stosownym komentarzem. Informacja ta może być ewentualnie bardziej ogólna i wyświetlać po prostu ostrzeżenie (na czerwono) o braku danych.

Jak zrobić coś takiego w VBA?

Należy napisać osobną funkcję typu Boolean w module formularza.
Powinna ona sprawdzić czy wszystkie wymagane pola zostały uzupełnione prawidłowo. Jeśli tak nie jest, możemy wyróżnić ramki kontrolek na czerwono i odkryć etykietę, która pokazuje komunikat z błędem.

Warto rozważyć zestawienie tego mechanizmu ze zdarzeniem AfterUpdate.
Gdy zaczniemy ponownie uzupełniać kontrolki – czerwone ramki zostaną zamienione z powrotem na czarne. Będzie to informacja dla użytkownika, że dane są ok.
Kod – moduł formularza
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
Option Explicit 'Zmienne prywatne Dim mbOK As Boolean ' Zapamiętuje, czy użytkownik nacisnął OK lub Anuluj. Dim mbWalidacja As Boolean ' Sprawdza czy wymagane dane zostały uzupełnione 'Właściwości publiczne Public Property Get OK() As Boolean: OK = mbOK: End Property Public Property Get Walidacja() As Boolean: Walidacja = mbWalidacja: End Property 'Obsługa przycisku OK. Private Sub cmdOK_Click() 1 mbOK = True 2 Call SprawdzWalidacje 'Ustal czy dane są ok 3 If mbWalidacja Then 4 Me.Hide 5 Me.lblBlad.Visible = False 6 Else 'Ważne! Jeśli dane nie są ok - nie ukrywaj formularza. 'Zapamiętaj wpisane dane przez użytkownika i pozwól mu je poprawić/uzupełnić. 7 Me.lblBlad.Visible = True 8 End If End Sub ' Obsługa przycisku Anuluj. Private Sub cmdAnuluj_Click() mbOK = False Me.Hide End Sub 'Private Sub txtTelefon_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) ' ' 'Jeżeli jest wciśnięty SHIFT to zablokuj ' If Shift = 1 Then KeyCode = 0 ' ' 'Pozwól tylko na wprowadzenie liczb, tabulatora i backspace ' Select Case KeyCode ' Case 8, 9, 48 To 57, 96 To 105 'OK :-) ' Case Else: KeyCode = 0 ' End Select ' 'End Sub 'Private Sub txtTelefon_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) ' If Not IsNumeric(Me.txtTelefon.Text) Then Cancel = True 'End Sub Private Sub txtTelefon_Change() If IsNumeric(Me.txtTelefon.Text) Then Me.txtTelefon.BackColor = vbWindowBackground Else Me.txtTelefon.BackColor = rgbPink End If End Sub Private Sub txtTelefon_AfterUpdate() If IsNumeric(Me.txtTelefon.Text) Then Me.txtTelefon.BorderColor = vbBlack Else Me.txtTelefon.BorderColor = vbRed End If End Sub Private Sub txtImie_AfterUpdate() If Len(Me.txtImie.Text) <> 0 Then Me.txtImie.BorderColor = vbBlack Else Me.txtImie.BorderColor = vbRed End If End Sub Private Sub txtNazwisko_AfterUpdate() 1 If Len(Me.txtNazwisko.Text) <> 0 Then 2 Me.txtNazwisko.BorderColor = vbBlack 3 Else 4 Me.txtNazwisko.BorderColor = vbRed 5 End If End Sub ' Powoduje, że znak [x] zachowuje się tak samo, jak przycisk Anuluj. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 1 If CloseMode = vbFormControlMenu Then 2 mbOK = False 3 Me.Hide 4 Cancel = True 5 End If End Sub ' Celem procedury jest przede wszystkim ustawienie zmiennej prywatnej mbWalidacja i dzięki temu ' też wyliczenie zmiennej publicznej Walidacja. Oprócz tego, wyróżniamy na czerwono (obramowanie) ' kontrolki wymagane, które nie zostały poprawnie uzupełnione. Private Sub SprawdzWalidacje() Dim ctl As MSForms.Control, txt As MSForms.TextBox 'Ustaw wartość funkcji na True 1 mbWalidacja = True 'Sprawdź czy wymagane pole jest uzupełnione 2 For Each ctl In Me.Controls 3 If TypeOf ctl Is MSForms.TextBox Then 4 Set txt = ctl 5 If txt.Tag = "Wymagane" Then ' Wymagane pola mają tag! 6 If Len(txt.Text) = 0 Then ' Nie mogą być puste! 7 mbWalidacja = False ' bo nie dodamy danych do tabeli 8 txt.BorderColor = vbRed ' Ustaw kolor ramki na czerwony. 9 Else 10 txt.BorderColor = vbBlack ' OK :-) 11 End If 12 If txt.Name = "txtTelefon" Then ' Osobna walidacja dla telefonu 13 If Not IsNumeric(txt.Text) Then 14 mbWalidacja = False 15 txt.BorderColor = vbRed 16 End If 17 End If 18 End If 19 End If 20 Next ctl 21 Set ctl = Nothing: Set txt = Nothing End Sub |
Kod – moduł zwykły
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
'// Autor : Mariusz Jankowski Private Const ms_MODUL As String = "MModulGlowny" Option Explicit Public Sub WyswietlDane() Dim frmRejestracja As URejestracja Dim bCzyOk As Boolean Dim bCzyWalidacja As Boolean Const sPROC As String = "WyswietlDane" 'Aktywuj obsługę błędów na starcie 1 On Error GoTo ObslugaBledu 'Wyświetl formularz 2 Set frmRejestracja = New URejestracja 3 frmRejestracja.Show 'Sprawdź walidację 4 With frmRejestracja 5 bCzyOk = .OK 6 bCzyWalidacja = .Walidacja 7 End With 'Wyloguj formę 8 Unload frmRejestracja 'Jeśli OK to MsgBox 9 If bCzyOk Then 10 If bCzyWalidacja Then 11 MsgBox Prompt:="Dane są OK!", _ Title:="SUKCES!", Buttons:=vbInformation 12 End If 13 End If Wyjscie: 14 Set frmRejestracja = Nothing 15 On Error GoTo 0 16 Exit Sub ObslugaBledu: 17 Application.ScreenUpdating = True 18 MsgBox Title:="Błąd programu!", Buttons:=vbInformation, _ Prompt:="Informacje dotyczące błędu: " & vbCr & vbCr & _ "Numer: " & vbTab & Err.Number & vbCr & _ "Opis: " & vbTab & Err.Description & vbCr & vbCr & _ "Moduł: " & vbTab & ms_MODUL & vbCr & _ "Makro: " & vbTab & sPROC & vbCr & _ "Linia: " & vbTab & Erl() 19 GoTo Wyjscie End Sub |
Podsumowanie
Jak wspomniałem na początku tego wpisu, wybór metody walidacji zależy od Klienta. Osobiście staram się odchodzić od bardziej inwazyjnych technik na rzecz zdarzeń Change i AfterUpdate.
Musimy jednak pamiętać, że rolą tych zdarzeń jest tylko powiadomienie użytkownika (w subtelny sposób!) o istniejących błędach. Rzeczywista kontrola poprawności odbywa się po kliknięciu w przycisk Dodaj. To w tym miejscu należy ustalić czy wszystkie pola zostały uzupełnione prawidłowo i zdecydować czy przepuszczamy użytkownika dalej.
Przycisk Dodaj powinien robić dwie rzeczy. Po pierwsze, musi ustawić wartość zmiennej prywatnej mbOK na True tj. zapamiętać, że użytkownik kliknął Dodaj, a nie Anuluj. Po drugie, powinien dokonać walidacji danych i zapisać wynik w zmiennej typu Boolean np. mbWalidacja. Jeśli dane są w porządku – ukryć formularz.
Zapis informacji z formularza do arkusza powinien się odbyć z poziomu modułu zwykłego, a nie modułu formularza. Dzięki temu utrzymujemy kod w pewnej hermetycznej całości i oddzielamy dane wejściowe (moduł formularza) od analizy biznesowej (moduł zwykły).
Przycisk Dodaj nie powinien zawierać kodu, który zapisuje dane z formularza do arkusza. Robi tak „99% użytkowników”, ale nie jest to sposób właściwy.
Plik XLSM
Pobierz plik i przeanalizuj kod metodą krokową (F8).
