Co to jest błąd niezgodności typów?
Błąd niezgodności może często wystąpić podczas uruchamiania kodu VBA. Błąd spowoduje całkowite zatrzymanie kodu i oznaczenie za pomocą okna komunikatu, że ten błąd wymaga uporządkowania
Należy pamiętać, że jeśli kod nie został w pełni przetestowany przed udostępnieniem użytkownikom, ten komunikat o błędzie będzie widoczny dla użytkowników i spowoduje dużą utratę zaufania do aplikacji programu Excel. Niestety, użytkownicy często robią bardzo specyficzne rzeczy w aplikacji i często są to rzeczy, których Ty jako programista nigdy nie brałeś pod uwagę.
Błąd niezgodności typu występuje, ponieważ zdefiniowałeś zmienną za pomocą instrukcji Dim jako określonego typu, np. liczba całkowita, data, a Twój kod próbuje przypisać wartość zmiennej, która jest niedopuszczalna, np. ciąg tekstowy przypisany do zmiennej całkowitej, jak w tym przykładzie:
Oto przykład:
Kliknij Debug, a obraźliwy wiersz kodu zostanie podświetlony na żółto. W wyskakującym okienku błędu nie ma opcji kontynuowania, ponieważ jest to poważny błąd i nie ma możliwości dalszego uruchomienia kodu.
W tym konkretnym przypadku rozwiązaniem jest zmiana instrukcji Dim na typ zmiennej, który działa z wartością przypisaną do zmiennej. Kod zadziała, jeśli zmienisz typ zmiennej na „String” i prawdopodobnie będziesz chciał również zmienić nazwę zmiennej.
Jednak zmiana typu zmiennej będzie wymagała zresetowania projektu i będziesz musiał ponownie uruchomić kod od samego początku, co może być bardzo denerwujące, jeśli w grę wchodzi długa procedura
Błąd niezgodności spowodowany obliczeniem arkusza roboczego
Powyższy przykład jest bardzo prostym przykładem, w jaki sposób może powstać błąd niezgodności i w tym przypadku można go łatwo naprawić
Jednak przyczyna błędów niezgodności jest zwykle znacznie głębsza i nie jest tak oczywista, gdy próbujesz debugować swój kod.
Jako przykład załóżmy, że napisałeś kod, aby pobrać wartość w określonej pozycji w arkuszu i zawiera on zależne od obliczeń inne komórki w skoroszycie (w tym przykładzie B1).
Arkusz roboczy wygląda tak, jak w tym przykładzie, z formułą umożliwiającą znalezienie określonego znaku w ciągu tekstu
Z punktu widzenia użytkownika komórka A1 ma dowolny format i może wprowadzić dowolną wartość. Jednak formuła szuka wystąpienia znaku „B” i w tym przypadku nie zostanie znaleziony, więc komórka B1 ma wartość błędu.
Poniższy kod testowy spowoduje błąd niezgodności, ponieważ w komórce A1 wprowadzono nieprawidłową wartość
| 1234 | Niezgodność podtestu()Dim MyNumber As IntegerMójNumer = Arkusze("Arkusz1").Zakres("B1").WartośćNapis końcowy |
Wartość w komórce B1 spowodowała błąd, ponieważ użytkownik wprowadził tekst do komórki A1, który nie jest zgodny z oczekiwanym i nie zawiera znaku „B”
Kod próbuje przypisać wartość do zmiennej „MyNumber”, która została zdefiniowana tak, aby oczekiwała liczby całkowitej, więc otrzymujesz błąd niezgodności.
To jeden z tych przykładów, w których skrupulatne sprawdzenie kodu nie da odpowiedzi. Musisz również zajrzeć do arkusza, z którego pochodzi wartość, aby dowiedzieć się, dlaczego tak się dzieje.
Problem jest w rzeczywistości w arkuszu, a formuła w B1 wymaga zmiany, aby poradzić sobie z wartościami błędów. Możesz to zrobić, używając formuły „JEŻELI.BŁĄD”, aby podać domyślną wartość 0, jeśli znak wyszukiwania nie zostanie znaleziony
Następnie możesz dołączyć kod, aby sprawdzić wartość zerową i wyświetlić komunikat ostrzegawczy dla użytkownika, że wartość w komórce A1 jest nieprawidłowa
| 12345678 | Niezgodność podtestu()Dim MyNumber As IntegerMójNumer = Arkusze("Arkusz1").Range("B1").TekstJeśli MójNumer = 0 WtedyMsgBox "Wartość w komórce A1 jest nieprawidłowa", vbCriticalWyjście SubZakończ, jeśliNapis końcowy |
Możesz również użyć sprawdzania poprawności danych (grupa Narzędzia danych na karcie Dane na wstążce) w arkuszu kalkulacyjnym, aby uniemożliwić użytkownikowi robienie tego, co mu się podoba, i przede wszystkim powodowanie błędów w arkuszu. Zezwalaj im tylko na wprowadzanie wartości, które nie spowodują błędów w arkuszu.
Możesz napisać kod VBA na podstawie zdarzenia Zmiana w arkuszu, aby sprawdzić, co zostało wprowadzone.
Również blokada i hasło chronią arkusz roboczy, aby nie można było wprowadzić nieprawidłowych danych
Błąd niezgodności spowodowany przez wprowadzone wartości komórek
Błędy niezgodności mogą być spowodowane w kodzie poprzez wprowadzenie normalnych wartości z arkusza roboczego (bez błędów), ale gdy użytkownik wprowadził nieoczekiwaną wartość, np. wartość tekstowa, gdy oczekiwałeś liczby. Być może zdecydowali się na wstawienie wiersza w zakresie liczb, aby móc umieścić w komórce notatkę wyjaśniającą coś na temat liczby. W końcu użytkownik nie ma pojęcia, jak działa twój kod i że właśnie wyrzucił wszystko z bałaganu, wpisując swoją notatkę.
Poniższy przykładowy kod tworzy prostą tablicę o nazwie „MyNumber” zdefiniowaną za pomocą wartości całkowitych
Następnie kod przechodzi przez zakres komórek od A1 do A7, przypisując wartości komórek do tablicy, używając zmiennej „Coun” do indeksowania każdej wartości
Gdy kod osiągnie wartość tekstową, powoduje to błąd niezgodności i wszystko się zatrzymuje
Klikając „Debuguj” w wyskakującym okienku błędu, zobaczysz wiersz kodu, w którym problem jest podświetlony na żółto. Najeżdżając kursorem na dowolne wystąpienie zmiennej „Coun” w kodzie, zobaczysz wartość „Coun” w miejscu, w którym kod się nie powiódł, co w tym przypadku wynosi 5
Patrząc na arkusz, zobaczysz, że 5NS komórka w dół ma wartość tekstową, co spowodowało awarię kodu
Możesz zmienić swój kod, umieszczając warunek, który najpierw sprawdza wartość liczbową przed dodaniem wartości komórki do tablicy
| 12345678910111213 | Niezgodność podtestu()Dim MyNumber(10) jako liczba całkowita, coun jako liczba całkowitaLiczba = 1RobićJeśli liczba = 11, to zakończ WykonajIf IsNumeric(Sheets("sheet1").Cells(Coun, 1).Value) ThenMyNumber(Coun) = Sheets("sheet1").Komórki(Coun, 1).ValueW przeciwnym razieMójNumer (liczba) = 0Zakończ, jeśliHrabia = Hrabia + 1PętlaNapis końcowy |
Kod używa funkcji „IsNumeric”, aby sprawdzić, czy wartość jest rzeczywiście liczbą, a jeśli tak, to wpisuje ją do tablicy. Jeśli nie jest liczbą, to wpisuje wartość zero.
Dzięki temu indeks tablicy jest zgodny z numerami wierszy komórek w arkuszu kalkulacyjnym.
Możesz również dodać kod, który kopiuje oryginalną wartość błędu i szczegóły lokalizacji do arkusza „Błędy”, aby użytkownik mógł zobaczyć, co zrobił źle, gdy Twój kod jest uruchamiany.
Test numeryczny wykorzystuje pełny kod komórki oraz kod do przypisania wartości do tablicy. Możesz argumentować, że powinno to być przypisane do zmiennej, aby nie powtarzać tego samego kodu, ale problem polega na tym, że musiałbyś zdefiniować zmienną jako „Wariant”, co nie jest najlepszym rozwiązaniem.
Potrzebna jest również walidacja danych w arkuszu i zabezpieczenie arkusza hasłem. Uniemożliwi to użytkownikowi wstawianie wierszy i wprowadzanie nieoczekiwanych danych.
Błąd niezgodności spowodowany wywołaniem funkcji lub podprogramu przy użyciu parametrów
Gdy funkcja jest wywoływana, zwykle przekazujesz parametry do funkcji przy użyciu typów danych już zdefiniowanych przez funkcję. Funkcja może być już zdefiniowana w VBA lub może być funkcją zdefiniowaną przez użytkownika, którą sam zbudowałeś. Podprogram może czasami wymagać parametrów
Jeśli nie będziesz trzymać się konwencji przekazywania parametrów do funkcji, otrzymasz błąd niezgodności
| 12345678 | Podrzędna Funkcja Wywołania()Dim Ret jako liczba całkowitaRet = MojaFunkcja(3, "test")Napis końcowyFunkcja MyFunction (N jako liczba całkowita, T jako ciąg) jako ciągMojaFunkcja = TKoniec funkcji |
Istnieje kilka możliwości uzyskania błędu niedopasowania
Zmienna zwracana (Ret) jest zdefiniowana jako liczba całkowita, ale funkcja zwraca ciąg. Jak tylko uruchomisz kod, zakończy się niepowodzeniem, ponieważ funkcja zwróci łańcuch, a ten nie może przejść do zmiennej całkowitej. Co ciekawe, uruchomienie Debug na tym kodzie nie wychwytuje tego błędu.
Jeśli umieścisz cudzysłów wokół pierwszego przekazywanego parametru (3), jest on interpretowany jako ciąg znaków, który nie pasuje do definicji pierwszego parametru w funkcji (liczba całkowita)
Jeśli zmienisz drugi parametr w wywołaniu funkcji na wartość liczbową, zakończy się niepowodzeniem z niezgodnością, ponieważ drugi parametr w ciągu jest zdefiniowany jako ciąg (tekst)
Błąd niezgodności spowodowany nieprawidłowym użyciem funkcji konwersji w VBA
Istnieje wiele funkcji konwersji, z których możesz skorzystać w VBA, aby przekonwertować wartości na różne typy danych. Przykładem jest „CInt”, który konwertuje ciąg zawierający liczbę na wartość całkowitą.
Jeśli konwertowany ciąg zawiera jakiekolwiek znaki alfa, otrzymasz błąd niezgodności, nawet jeśli pierwsza część ciągu zawiera znaki numeryczne, a reszta to znaki alfa, np. „123abc”
Ogólne zapobieganie błędom niezgodności
W powyższych przykładach widzieliśmy kilka sposobów radzenia sobie z potencjalnymi błędami niezgodności w kodzie, ale istnieje wiele innych sposobów, chociaż mogą nie być najlepszymi opcjami:
Zdefiniuj swoje zmienne jako typ wariantu
Typ wariantu jest domyślnym typem zmiennej w VBA. Jeśli nie użyjesz instrukcji Dim dla zmiennej i po prostu zaczniesz używać jej w swoim kodzie, automatycznie zostanie jej nadany typ Variant.
Zmienna Variant akceptuje dowolny typ danych, niezależnie od tego, czy jest to liczba całkowita, długa liczba całkowita, liczba o podwójnej precyzji, wartość logiczna czy wartość tekstowa. Brzmi to jak wspaniały pomysł i zastanawiasz się, dlaczego wszyscy nie ustawiają po prostu wszystkich swoich zmiennych na wariant.
Wariant typu danych ma jednak kilka wad. Po pierwsze, zajmuje znacznie więcej pamięci niż inne typy danych. Jeśli zdefiniujesz bardzo dużą tablicę jako wariant, pochłonie ona ogromną ilość pamięci podczas działania kodu VBA i może łatwo spowodować problemy z wydajnością
Po drugie, ogólnie działa wolniej niż w przypadku korzystania z określonych typów danych. Na przykład, jeśli wykonujesz złożone obliczenia przy użyciu liczb zmiennoprzecinkowych, obliczenia będą znacznie wolniejsze, jeśli liczby będą przechowywane jako warianty, a nie liczby podwójnej precyzji
Używanie typu wariantu jest uważane za niedbałe programowanie, chyba że istnieje absolutna konieczność.
Użyj polecenia OnError do obsługi błędów
Polecenie OnError może być zawarte w kodzie, aby poradzić sobie z wychwytywaniem błędów, dzięki czemu jeśli wystąpi błąd, użytkownik zobaczy zrozumiały komunikat zamiast standardowego wyskakującego okienka z błędem VBA
| 1234567 | Podrzędna pułapka błędów()Dim MyNumber As IntegerW przypadku błędu Przejdź do Err_HandlerMójNumer = "test"Błąd_obsługi:MsgBox "Wystąpił błąd " & Err.Description & " wystąpił"Napis końcowy |
Skutecznie zapobiega to zatrzymaniu przez błąd płynnego działania kodu i pozwala użytkownikowi odzyskać sprawność po wystąpieniu błędu.
Procedura Err_Handler może pokazać dalsze informacje o błędzie i z kim się skontaktować w tej sprawie.
Z punktu widzenia programowania, gdy używasz procedury obsługi błędów, dość trudno jest zlokalizować wiersz kodu, w którym znajduje się błąd. Jeśli przechodzisz przez kod za pomocą klawisza F8, zaraz po uruchomieniu niewłaściwej linii kodu przeskakuje on do procedury obsługi błędów i nie możesz sprawdzić, gdzie dzieje się źle.
Sposobem na obejście tego jest ustawienie globalnej stałej, która ma wartość True lub False (wartość logiczna) i użycie jej do włączania lub wyłączania procedury obsługi błędów za pomocą instrukcji „If”. Gdy chcesz przetestować błąd, wszystko, co musisz zrobić, to ustawić stałą globalną na False, a program obsługi błędów przestanie działać.
| 1 | Globalna obsługa błędów const = Fałsz |
| 1234567 | Podrzędna pułapka błędów()Dim MyNumber As IntegerJeśli ErrHandling = True, to w przypadku błędu Przejdź do Err_HandlerMójNumer = "test"Obsługa błędów:MsgBox "Wystąpił błąd " & Err.Description & " wystąpił"Napis końcowy |
Jedynym problemem z tym jest to, że pozwala użytkownikowi naprawić błąd, ale reszta kodu w ramach podprogramu nie jest uruchamiana, co może mieć ogromne reperkusje w późniejszej aplikacji
Korzystając z wcześniejszego przykładu przechodzenia przez zakres komórek, kod trafiałby do komórki A5 i powodował błąd niedopasowania. Użytkownik zobaczy okno komunikatu z informacją o błędzie, ale nic z tej komórki w zakresie nie zostanie przetworzone.
Użyj polecenia OnError, aby wyeliminować błędy
Wykorzystuje to polecenie „Po błędzie wznowienia dalej”. Jest to bardzo niebezpieczne, aby umieścić go w kodzie, ponieważ zapobiega wyświetlaniu kolejnych błędów. Zasadniczo oznacza to, że podczas wykonywania kodu, jeśli wystąpi błąd w wierszu kodu, wykonanie po prostu przejdzie do następnego dostępnego wiersza bez wykonywania wiersza błędu i będzie kontynuowane jak zwykle.
Może to rozwiązać potencjalną sytuację błędu, ale nadal będzie miało wpływ na każdy przyszły błąd w kodzie. Możesz wtedy pomyśleć, że twój kod jest wolny od błędów, ale w rzeczywistości tak nie jest, a części twojego kodu nie robią tego, co myślisz, że powinny robić.
Zdarzają się sytuacje, w których konieczne jest użycie tego polecenia, na przykład, jeśli usuwasz plik za pomocą polecenia „Zabij” (jeśli plik nie jest obecny, wystąpi błąd), ale wychwytywanie błędów powinno zawsze zostać przełączone z powrotem natychmiast po tym, gdy potencjalny błąd może wystąpić za pomocą:
| 1 | W przypadku błędu Przejdź do 0 |
We wcześniejszym przykładzie pętli przez zakres komórek przy użyciu opcji „Po błędzie wznowienia dalej” umożliwiłoby to kontynuowanie pętli, ale komórka powodująca błąd nie zostałaby przeniesiona do tablicy, a element tablicy dla tego konkretnego indeksu miałby wartość null.
Konwertowanie danych na typ danych w celu dopasowania do deklaracji
Za pomocą funkcji VBA można zmienić typ danych przychodzących danych, tak aby był zgodny z typem danych zmiennej odbierającej.
Możesz to zrobić podczas przekazywania parametrów do funkcji. Na przykład, jeśli masz liczbę, która jest przechowywana w zmiennej łańcuchowej i chcesz przekazać ją jako liczbę do funkcji, możesz użyć CInt
Istnieje wiele funkcji konwersji, których można użyć, ale oto najważniejsze z nich:
CInt - konwertuje ciąg, który ma wartość liczbową (poniżej + lub -32 768) na wartość całkowitą. Należy pamiętać, że powoduje to obcięcie wszelkich miejsc dziesiętnych
CLng - konwertuje ciąg, który ma dużą wartość liczbową na długą liczbę całkowitą. Punkty dziesiętne są obcinane.
CDbl - konwertuje ciąg znaków zawierający liczbę zmiennoprzecinkową na liczbę o podwójnej precyzji. Zawiera kropki dziesiętne
CDate - konwertuje ciąg, który przechowuje datę na zmienną daty. Częściowo zależy od ustawień w Panelu sterowania systemu Windows i ustawień regionalnych od interpretacji daty
CStr - Konwertuje wartość liczbową lub datę na ciąg
Podczas konwersji z ciągu na liczbę lub datę ciąg nie może zawierać niczego poza liczbami lub datą. Jeśli znaki alfa są obecne, spowoduje to błąd niezgodności. Oto przykład, który spowoduje błąd niezgodności:
| 123 | Podtest()MsgBox CInt("123abc")Napis końcowy |
Testowanie zmiennych w kodzie
Możesz przetestować zmienną, aby dowiedzieć się, jaki to typ danych, zanim przypiszesz ją do zmiennej określonego typu.
Na przykład możesz sprawdzić ciąg, aby sprawdzić, czy jest liczbowy, używając funkcji „IsNumeric” w VBA
| 1 | MsgBox IsNumeric("123test") |
Ten kod zwróci False, ponieważ chociaż ciąg zaczyna się od znaków numerycznych, zawiera również tekst, więc test nie przejdzie testu.
| 1 | MsgBox jest numeryczny("123") |
Ten kod zwróci True, ponieważ zawiera wszystkie znaki numeryczne
W VBA istnieje wiele funkcji do testowania dla różnych typów danych, ale są to główne:
IsNumeric - sprawdza, czy wyrażenie jest liczbą, czy nie
IsDate - sprawdza, czy wyrażenie jest datą, czy nie
IsNull - sprawdza, czy wyrażenie ma wartość null, czy nie. Wartość null można umieścić tylko w obiekcie wariantu, w przeciwnym razie pojawi się błąd „Nieprawidłowe użycie wartości null”. Okno komunikatu zwraca wartość null, jeśli używasz go do zadawania pytania, więc zmienna zwracana musi być wariantem. Pamiętaj, że każde obliczenie przy użyciu wartości null zawsze zwróci wynik null.
IsArray - sprawdza, czy wyrażenie reprezentuje tablicę, czy nie
IsEmpty - sprawdza, czy wyrażenie jest puste, czy nie. Zauważ, że pusty to nie to samo co null. Zmienna jest pusta, gdy jest definiowana po raz pierwszy, ale nie jest wartością pustą
Co zaskakujące, nie ma funkcji dla IsText lub IsString, która byłaby naprawdę przydatna
Obiekty i błędy niedopasowania
Jeśli używasz obiektów, takich jak zakres lub arkusz, otrzymasz błąd niezgodności w czasie kompilacji, a nie w czasie wykonywania, co daje ostrzeżenie, że Twój kod nie będzie działał
| 123456 | Podzakres testowy()Dim MyRange jako zakres, I tak długoUstaw MójZakres = Zakres("A1:A2")ja = 10x = UżyjMójZakres(I)Napis końcowy |
| 12 | Funkcja UseMyRange(R jako zakres)Koniec funkcji |
Ten kod zawiera funkcję o nazwie „UseMyRange” i parametr przekazywany jako obiekt zakresu. Jednak przekazywany parametr jest liczbą całkowitą Long Integer, która nie jest zgodna z typem danych.
Po uruchomieniu kodu VBA jest on natychmiast kompilowany i zobaczysz następujący komunikat o błędzie:
Obrażający parametr zostanie podświetlony na niebieskim tle
Ogólnie rzecz biorąc, jeśli popełnisz błędy w kodzie VBA przy użyciu obiektów, zobaczysz ten komunikat o błędzie, a nie komunikat o niezgodności typu:
