Czyli formuła, której nie da się skasować…
Załóżmy, że tworzymy szablon oferty, taki jak na obrazku poniżej. Chcemy wybierać z listy rozwijanej model produktu i na tej podstawie ma się podpowiedzieć cena netto. Cena ta jest pobierana z cennika, który znajduje się w innym arkuszu. To wszystko mamy już gotowe, natomiast chcemy mieć możliwość ręcznego wpisywania cen jednostkowych. TO oczywiście jest możliwe, natomiast jak to zrobimy – bezpowrotnie tracimy formułę, która wcześniej tę cenę podpowiadała.
I w tym zadaniu chodzi o to, aby po skasowaniu tej ręcznie wpisanej wartości, automatycznie wpisywała się formuła, która tam była…
Bez VBA się nie obejdzie 🙂
Czyli chodzi o to, aby w jakiś sposób wyłapać, że użytkownik skasował wartość w komórce z ceną i wstawić w nią określoną formułę. Musimy zatem znów skorzystać ze zdarzeń arkusza, a konkretnie ze zdarzenia Change, ponieważ będziemy sprawdzać zmiany po tym, jak user coś zmieni (change) w arkuszu.
Określanie komórek do sprawdzania
W tym celu najpierw określimy, jakie komórki będą nas interesowały, czyli do jakich komórek mamy ewentualnie wstawiać formułę. Zakres ten sobie najpierw nazwiemy.
Czyli zaznaczamy komórki z ceną netto, czyli E3:E9, klikamy w polu nazwy i wprowadzamy tam nazwę np. CenaNetto. Wpis zatwierdzamy Enterem.
Pobranie istniejącej formuły
Kolejny krok to przetłumaczenie na angielski formuły, która znajduje się w komórkach z cenami netto. Ta formuła to u mnie:
=JEŻELI.BŁĄD((WYSZUKAJ.PIONOWO(D3;Cennik!$B$3:$D$25;3;0));0)
Na angielski trzeba ją przetłumaczyć dlatego, że VBA potrzebuje jej w takim właśnie języku. Można to zrobić oczywiście “ręcznie”, ale ja polecam prostszą metodę: nagranie makra z wpisywaniem tej formuły do komórki. Skorzystamy z faktu, że ona już tam jest i jedyne co zrobimy podczas nagrywania tego makra, to wejście do edycji tej komórki i zatwierdzenie wpisu w niej. Prościutkie 🙂
Czyli poszczególne koki to:
- Zaznacz komórkę E3
- W menu Deweloper kliknij przycisk Zarejestruj makro (pokazanie karty Deweloper na wstążce omawiałam tutaj)
- Wejdź do edycji komórki E3 (czyli np. klawisz F2)
- Naciśnij Enter
- Zatrzymaj rejestrowanie makra (Deweloper/ Zatrzymaj rejestrowanie)
Tyle. Teraz w oknie VBE (Alt + F11) powinien pokazać się nowy moduł (np. Module1), a w nim procedura z naszym nagranym makrem:
Sub Makro1() ' ' Makro1 Makro ' ' Selection.FormulaR1C1 = _ "=IFERROR((VLOOKUP(RC[-1],Cennik!R3C2:R25C4,3,0))*R6C7*R7C7,0)" End Sub
To sobie teraz zostawiamy na później i idziemy do zdarzenia arkusza…
Zdarzenie arkusza
To bowiem jest miejsce, które powie Excelowi, że ma coś wykonać, gdy user zmienić wartość komórki. Oczywiście Excel będzie sprawdzał każdą komórkę w określonym przez nas arkuszu, ale z tym też sobie poradzimy :).
Najpierw określmy, w którym arkuszu w ogóle ma sprawdzać, czyli działać makro. U mnie jest to arkusz o nazwie kalkulator, więc po wejściu do VBE (Visial Basic Editor), w okienku Project Explorer, dwa razy klikam w taki właśnie arkusz. Następnie z list rozwijanych w prawym oknie wybieramy:
(1) Worksheet, i
(2) Change
Oba te kroki dokładnie opisałam tutaj.
Po wyborze Change pojawi się następujący kod:
Private Sub Worksheet_Change(ByVal Target As Range) End Sub
Nasz kod wpiszemy dokładnie pośrodku tych linijek.
Kod VBA
A zaczniemy od deklaracji zmiennej, oznaczającej nasz zakres do sprawdzenia:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Zakres As Range End Sub
Od razu możemy dorzucić sobie linijki obsługujące błędy i zabijające zmienną obiektową Zakres:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Zakres As Range On Error GoTo Obsluga Obsluga: Set Zakres = Nothing End Sub
Błędy bowiem mogą się pojawić, gdy np. user skasuje więcej niż jedną komórkę. A błędów, objawiających się brzydką, szarą tabliczką nie chcemy… :). Powyższe linijki więc mówią, że jeśli jakiś błąd się pojawi – idziemy na koniec procedury, gdzie następuje jedynie zabicie zmiennej obiektowej i zakończenie makra. Nie chcę tutaj rozmyślać, co ma się stać gdy wyskoczy błąd taki, albo śmaki – wszystkiego nie przewidzę i w tej sytuacji nawet nie chcę. To ma być proste i działać tylko wtedy, gdy user usunie wartość z komórki z ceną netto. Kropka 🙂
Czyli krótko mówiąc: gdy makro napotka błąd, z punktu widzenia użytkownika nic się nie dzieje. I tak ma być :).
Ok, to teraz co, jeśli wszystko odbędzie się zgodnie z planem.
No po pierwsze user może skasować/zmienić dowolną komórkę arkusza. Ale na dowolną nie chcemy reagować, tylko na taką, która znajduje się we wcześniej nazwanym zakresie CenaNetto i dodatkowo właśnie stała się pusta.
Czyli potrzebujemy przypisać do zmiennej obiektowej Zakres – arkuszowy zakres CenaNetto.
Set Zakres = Me.Range("CenaNetto")
Słówko me oznacza w tym wypadku arkusz, w którym tworzymy zdarzenie.
Kolejny krok to zbadanie, czy zmieniona komórka (Target) znajduje się w naszym Zakresie i czy jest pusta. Zrobi to ten if i obiekt Intersect:
If Not Intersect(Zakres, Target) Is Nothing And Target.Value = "" Then End If
A w środku to już prosto: jeśli oba powyższe warunki są spełnione, to formułą w zmienionej komórce ma być ta formuła, dla której wcześniej nagrywaliśmy makro, aby ją przetłumaczyć. Teraz wystarczy sobie ją tylko przekopiować między modułami, aby uzyskać to:
If Not Intersect(Zakres, Target) Is Nothing And Target.Value = "" Then Target.FormulaR1C1 = "=IFERROR((VLOOKUP(RC[-1],Cennik!R3C2:R25C4,3,0)),0)" End If
Wszystko do kupy wygląda tak:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Zakres As Range On Error GoTo Obsluga Set Zakres = Me.Range("CenaNetto") If Not Intersect(Zakres, Target) Is Nothing And Target.Value = "" Then Target.FormulaR1C1 = "=IFERROR((VLOOKUP(RC[-1],Cennik!R3C2:R25C4,3,0)),0)" End If Obsluga: Set Zakres = Nothing End Sub
Śmiga pięknie! Uzyskujemy też śmieszny efekt, że formuły nie da się skasować 🙂
Na koniec pamiętaj tylko o zapisaniu pliku z obsługą makr, czyli jako *.xlsm. Bez tego ani rusz!
A jeśli wolisz omówione kroki obejrzeć na filmie, to zachęcam do obejrzenia poniżej:
Załączam też plik z gotowcem:
MalinowyExcel Odzyskiwanie formuły po nadpisaniu jej wartością dw.xlsm
Jutro sprawdzę w pracy 🙂
Pewnie 🙂
Bardzo przydatne, choć jak dla mnie trochę skomplikowane. Ale postaram się ogarnąć, z ciekawości.
Beata, wystarczy, że wkleisz kod i będzie działało 🙂
Cześć!
Co oznacza/czym jest “Obsluga” w ‘On Error GoTo Obsluga;?
Hej, Obsluga to miejsce w kodzie, gdzie ma “przeskoczyć”, gdy napotka błąd.