Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: zdarzenia arkusza

wtorek, 21 lutego 2017

Wyobraźmy sobie arkusz Excela z kodem reagującym na zdarzenia arkusza. W zależności od tego, jaki jest to kod, jego wykonywanie bardzo łatwo może generować kolejne i zupełnie niepotrzebne zdarzenia arkusza.
Załóżmy, że mamy oprogramowane zdarzenie arkusza, które po zmianie wartości w komórce kolumny A w sąsiadującej z nią komórce kolumny B wstawia datę. Przykładowy kod takiej funkcji wygląda tak:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(0, 1) = Date()
    End If
End Sub


 

 

 

W trakcie wykonywania kodu, po wstawieniu wartości w kolumnie B następuje wykrycie zdarzenia "przy zmianie" i następuje ponowne uruchomienie makra (mimo, że pierwsze nie doszło jeszcze do końca). W tym przypadku akurat może być praktycznie niezauważalne, ale jeżeli takich zmian jest więcej - owszem. W ekstremalnym przypadku może to doprowadzić do zapętlenia kodu.

Chcąc tego uniknąć musimy na czas wykonywania kodu po prostu wyłączyć obsługę wykrywania zdarzeń. Wykorzystujemy do tego właściwość obiektu Application - EnableEvants. Jest to właściwość typu Boolean czyli może przybierać wartość Prawda lub Fałsz. Ustawiamy ją tak:

Application.EnableEvents=False          'wyłączenie obsługi zdarzeń

Application.EnableEvents=True            'włączenie obsługi zdarzeń

Wykorzystując w kodzie makra jw. mamy więc:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
   Application.EnableEvents = False
   Target.Offset(0, 1) = Date()
   Application.EnableEvents = True
 End If

End Sub


 

 

 

 

 

I już. Na czas wykonywania kodu obsługa zdarzeń jest wyłączona - wstawienie daty w komórce sąsiadującej nie wywoła w tym przypadku ponownej reakcji na zdarzenie.



poniedziałek, 14 lutego 2011

Formatowanie warunkowe jest jednym z ciekawszych narzędzi własnych Excela. Pisałam o tym tu:
Formatowanie warunkowe
Formatowanie warunkowe nieco bardziej zaawansowane
Przydaje się w praktycznym wykorzystaniu, gdy wyróżnienie okreśłonych danych może pomóc w analizie czy choćby uatrakcyjnić wygląd arkusza.
Problem pojawia się wtedy, gdy potrzebujemy więcej warunków formatowania niż ograniczona liczba dostępnych warunków systemowych. W takiej sytuacji możemy wykorzystać swoje własne formatowanie za pomocą kodu VBA.

Dobrym przykładem może być tu miesięczny grafik dyżurów dla 15 osób.

formatowanie arkusza Excel 

W legendzie - czyli komórkach F3 do G17 oznaczeniom L1 do L15 przypisujemy kolory wypełnienia komórki. Formatowanie to automatycznie przenosi się do samego grafika czyli komórek A1 do D32.

Zdefiniujmy najpierw konieczne funkcje i procedury


Public Function Kolorek(Symbol)

Dim Przeglad As Range 
For Each Przeglad In Range("F3:F20")
    If Przeglad.Value = Symbol Then
       Kolorek = Przeglad.Interior.Color
       Exit Function
    End If
Next
Kolorek = 0

End Function

Funkcja Kolorek wyszukuje Symbol (czyli wartość komórki będącej argumentem funkcji) w obszarze Range (w tym przypadku zdefiniowanej komórkami Legendy w arkuszu Excela). Obszar jest przeszukiwany w ramach pętli For Each...Next (zob. Pętla For Each ... Next) i gdy wynik poszukiwania jest pozytywny - funkcja Kolorek przyjmuje wartość numeru koloru tła.

Kolor mamy ustalony, potrzebna jest jeszcze procedura zmieniająca kolory w samym arkuszu.


Public Sub Pokoloruj()

Dim JakiKolor
Dim Komorka As Range
Dim JakiSymbol
 For Each Komorka In Range("B2:F40")
       JakiSymbol = Komorka.Value
       If JakiSymbol <> "" Then
              JakiKolor = Kolorek(JakiSymbol)
              If JakiKolor = 0 Then Komorka.Interior.Pattern = xlNone Else Komorka.Interior.Color = JakiKolor
             
        Else
         Komorka.Interior.Pattern = xlNone
        End If
 Next
 End Sub

Procedura Pokoloruj przechodząc przez poszczególne komórki grafika (czyli zakres B2 do F40), na podstawie ich wartości przypisanej do zmiennej Symbol w fukcji Kolorek, sprawdza i zmienia kolor komórek w grafiku.

Teraz wystarczy tylko stworzyć przycisk uruchamiający makro Pokoloruj. Prosciej jednak byłoby przypisać je do któregoś ze zdarzeń arkusza lub zdarzeń skoroszytu.  
Na przykład tak:

Private Sub Worksheet_Activate()
Pokoloruj
End Sub

 

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column < 8 And Target.Row < 33 Then Pokoloruj

End Sub

Makro działa w tym przypadku przy otwarciu arkusza oraz przy zmianie wartości komórek z określonego zakresu.  

poniedziałek, 17 stycznia 2011

Worksheet_SelectionChange to kolejne makro przypisane do zdarzeń arkusza (inne najłatwiej wyszukać korzystając z tagu zdarzenia arkusza ).
Makro to działa po zmianie zaznaczenia komórki (myszką lub klawiaturą). Aktualnie zaznaczona komóka jest odczytywana przez parametr wejściowy Target (typu Range).
W praktyce może wyglądać to tak:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'ciąg instrukcji  
End Sub

Procedura wydaje sie z pozoru mało atrakcyjna, ale w połączeniu z inymi funkcjami, jako fragment więszej całości - może sie przydać.

poniedziałek, 27 grudnia 2010

Worksheet_PivotTableUpdate jest makrem przypisanym do zdarzeń arkusza (inne tego typu makra można wyszukać korzystając z tagu zdarzenia arkusza ).
Makro to działa automatycznie po odświeżeniu tabeli przestawnej. Parametrem wejściowym jest tu tabela przestawna - jest to istotne jeżeli w arkuszu jest kilka roznych tabel przestawnych.
W praktyce zapis koduwygląda tak:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'ciąg instrukcji
End Sub

W ten sposób można np. sformatować komórki, czy odczytując komórki przed i po aktualizacji tabeli - porównać dane.

poniedziałek, 20 grudnia 2010

Worksheet_FoloowHyperlink to kolejne makro przypisane do zdarzeń arkusza (inne najłatwiej wyszukać korzystając z tagu zdarzenia arkusza ).
Makro to działa po kliknięciu łącza w arkuszu. Wybrane łącze odczytywane przez parametr wejściowy Target (typu Hyperlink).
W praktyce może wyglądać to tak:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'ciąg instrukcji
End Sub

Można w ten sposób np.śledzić kliknięte łącza.

niedziela, 12 grudnia 2010

Worksheet_Deactivate to makro automatyczne związane z zdarzeniem arkusza. Zasada jego działania jest identyczna jak makra Worksheet_Activate, ale działą przy wychodzeniu (deaktywacji) danego arkusza. Zapis makra w kodzie VBA wygląda tak:

Private Sub Worksheet_Deactivate()

' odwołanie do makra lub bezpśredni ciąg instrukcji

End Sub

Ciąg poleceń zostanie wykonany automatycznie po opuszczeniu arkusza, do którego makro jest przypisane.
Inne makra związane ze zdarzeniami arkusza - można znaleźć pod tagiem:
zdarzenia arkusza.

niedziela, 05 grudnia 2010

Worksheet_Change to kolejne makro przypisane do zdarzeń arkusza (inne najłatwiej wyszukać korzystając z tagu zdarzenia arkusza ).
Makro to działa po zmianie wartości którejś z komórek. Komórka, w której dokonano zmiany jest odczytywana przez parametr wejściowy Target typu Range.
W praktyce może wyglądać to tak:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = RGB(255, 0, 0)
End Sub

W tym przypadku - komórka, w której dokonano zmiany zmienia swój kolor na czerwony. Może się przydać, prawda?

niedziela, 28 listopada 2010

Makro Worksheet_Calculate() jest makrem uruchamianym automatycznie. Przypisane jest do zdarzenia arkusza - działa po przeliczeniu arkusza. Samo wpisywanie danych nie wywołuje reakcji - momemntem kluczowym jest przeliczenie wartości w funkcji powiązanej z tymi danymi.

Private Sub Worksheet_Calculate()
Columns("A:C").EntireColumn.AutoFit
End Sub

W tym przypadku - po przeliczeniu arkusza następuje automatyczne dopasowanie szerokości kolumn A, B i C. Przydaje się czasami.

sobota, 20 listopada 2010

Makro Worksheet_BeforeRightClick (ByVal Target as Range, Cancel as Bolean) jest procedurą związaną ze zdarzeniem arkusza. Działa automatycznie po kliknięciu prawym przyciskiem myszy i jest bardzo podobne do makra wywoływanego zdarzeniem Worksheet_BeforeDoubleClick. Zmienna Target określa obszar wykrywania zdarzenia czyli komórkę, na której kliknięto prawym przyciskiem myszy. Cancel oznacza rezygnację.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value = 5 Then Makro2 Else Target.Interior.Color = RGB(255, 0, 0)
Cancel=True

End Sub

W tym przypadku - kliknięcie na dowolną komórkę powoduje sprawdzenie jej wartości. Jeżeli jest równa 5 - wykonywane jest Makro2. W przeciwnym przypadku - kolor wypełnienia komórki zmienia się na czerwony.
Jest jeszcze jedna sprawa istotna - zmienna Cancel. Standardowo - kliknięcie prawym przyciskiem myszy wywołuje otwarcie menu podręcznego, we wszystkich aplikacjach, nie tylko w Excelu. W przypadku tego zdarzenia - też tak się dzieje. Najpierw wykonywane są kolejne kroki makra, a potem - otwiera się menu podręczne. Przypisanie zmiennej Cancel wartości True - powoduje wyłączenie tej opcji. W wersji skróconej - żeby uniemożliwić w danym arkuszu możliwość włączenia menu podręcznego wystarczy przypisac procedurę:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel=True

End Sub

 

czwartek, 18 listopada 2010

Makro Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel as Bolean) jest procedurą związaną ze zdarzeniem arkusza. Działa automatycznie po dwukrotnym kliknięciu lewym przyciskiem myszy. Zmienna Target określa obszar wykrywania zdarzenia czyli komórkę, na której dwukrotnie kliknięto. Cancel oznacza rezygnację.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value = 5 Then Makro2 Else Target.Interior.Color = RGB(255, 0, 0)
End Sub

W tym przypadku - kliknięcie na dowolną komórkę powoduje sprawdzenie jej wartości. Jeżeli jest równa 5 - wykonywane jest Makro2. W przeciwnym przypadku - kolor wypełnienia komórki zmienia się na czerwony.

 
1 , 2
| < Lipiec 2017 > |
Pn Wt Śr Cz Pt So N
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!