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…

Formatka

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:

1. Zaznacz komórkę E3
2. W menu Deweloper kliknij przycisk Zarejestruj makro (pokazanie karty Deweloper na wstążce omawiałam tutaj)
3. Wejdź do edycji komórki E3 (czyli np. klawisz F2)
4. Naciśnij Enter
5. 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: