Błąd ten pojawia się w sytuacji odwołania do nieistniejącego obiektu (np.arkusza, tabeli) lub do wartości spoza przypisanego zakresu (np.5 kolumna w 4-kolumnowym zakresie).
Jak się przed tym zabezpieczyć? Oprócz ogólnej obsługi błędów na pewno trzeba pilnować się przed „literówkami” w kodzie. Warto też uchronić się przed ingerencją użytkowników końcowych. Ja często stosuję nazwy kodowe arkuszy uodparniające na zmianę nazwy arkusza.
Ostatnio kilka razy zdarzyło mi się korygować kod VBA w skoroszytach użytkowników, w których nagle coś przestawało działać. Przyczyna błędu była prozaiczna, choć niekiedy bywa trudna do zlokalizowania. O co chodzi? O adresy komórek w kodzie VBA.
Załóżmy, że mamy prostą tabelkę:
kliknij, aby powiększyć
W kolumnie D jest prosta formuła sumująca wartości z komórek
B i C tego samego wiersza. Kolumna E to też suma tych komórek, ale wprowadzona jako wartość kodem VBA:
Public Sub Suma() With Worksheets(„Arkusz8”)
.Range(„E2”) = .Range(„B2”) + .Range(„C2”)
.Range(„E3”) = .Range(„B3”) + .Range(„C3”)
.Range(„E4”) = .Range(„B4”) + .Range(„C4”)
.Range(„E5”) = .Range(„B5”) + .Range(„C5”)
.Range(„E6”) = .Range(„B6”) + .Range(„C6”) End With End Sub
Problem zaczyna się wtedy, gdy użytkownik końcowy zmieni układ arkusza np. wstawi kolumny lub wiersze. Wszystkie formuły odwołujące się do komórek (także w innych arkuszach) – bez problemu same się zaktualizują, nawet gdy są wpisane jako adresy bezwzględne.
kliknij, aby powiększyć
W kodzie VBA jednak nic się nie zmieniło, odwołania do poszczególnych komórek zostały takie same. Po wykonaniu makra efekt wygląda tak:
Procedura nadał wpisuje obliczone kodem wartości i wstawia je w kolumnę E, likwidując znajdującą się tam po przesunięciu formułę. W dodatku są tam dziwne wartości – sumowana jest liczba początkowa LP oraz wartość X.
Problem może się pojawić także przy zmianie nazwy arkusza – w kodzie będzie zapisana stara nazwa i przy uruchomienie procedury pojawi się błąd wykonania 9.
Niestety, w takim przypadku trzeba ręcznie przerobić odwołania w kodzie VBA, dostosowując go do nowego układu danych.
Ten przykład jest prosty, w bardziej skomplikowanych projektach znalezienie i poprawienie takiego błędu może być zdecydowanie bardziej skomplikowane.
Jak zabezpieczyć się przed nieprzewidzianą ingerencją użytkownika?
Najprostsze sposoby to:
włączenie ochrony arkusza
uniemożliwiające wstawienie wierszy/kolumn;
zastosowanie w kodzie VBA odwołań do nazw kodowych arkuszy
uniezależniające odwołania do konkretnych arkuszy;
Funkcja Trim to jedna z funkcji tekstowych VBA. W wyniku jej działania zwracana jest wartość tekstowa oczyszczona z niepotrzebnych spacji na początku i na końcu.
kliknij, aby powiększyć
Funkcja ma jeden argument:
String – ciąg tekstowy
Funkcja Trim() może występować też w ograniczonych wariantach tzn.
LTrim() – usuwa spacje tylko na początku wprowadzonego tekstu
RTrim() – usuwa spacje tylko na końcu wprowadzonego tekstu
Funkcja USUŃ.ZBĘDNE.ODSTĘPY() to jedna z funkcji tekstowych Excela. Zwraca wartość tekstową z usuniętymi niepotrzebnymi spacjami (na początku, na końcu i podwójne spacje w środku).
Funkcja ma jeden argument:
Tekst– zmienna typu tekstowego
Odpowiednikiem funkcji w VBA jest funkcja arkuszowa Trim().
W ograniczonym zakresie – w VBA funkcjonuje także funkcja VBA Trim().
