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ę:
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.
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;
- włączenie ochrony arkusza
A tu możesz mi postawić kawę:
