Excel i VBA

Poprawność danych

Poprawność danych to kolejne, bardzo przydatne narzędzie Excela. Zabezpiecza przed wstawieniem błędnych wartości w komórce, w wyniku których pojawią się błędy w formułach. Najczęściej jest wykorzystywane tam, gdzie powinna być data, a nie tekst przypominający datę czy wartości z określonego zakresu liczb. Umożliwia także wstawienie list rozwijalnych bezpośrednio w komórce.

Poprawność danych znajduje się na karcie Dane w grupie opcji Narzędzia danych.

Zaznaczamy komórkę lub zakres komórek, klikamy w przycisk
i wybieramy regułę poprawności:

Jest tu kilka możliwości ustawienia kryterium:

- dowolna wartość
  – brak kryterium, można wpisać dowolną wartość czy to liczbową czy tekstową
- pełna liczba
  możliwe do wstawienia tylko liczby całkowite z zadeklarowanego zakresu:

Konieczne jest także ustawienie zakresu precyzującego zakres, w którym muszą się mieścić liczby:

Mogą być tu nie tylko liczby, ale także odwołania do adresów komórek z liczbami

- - dziesiętne
    zasady jak w przypadku liczb całkowitych, ale możliwe wstawianie również ułamków
  - Lista
    
    kliknij, aby powiększyć
    
    Jako źródło danych można wpisać konkretne wartości oddzielone średnikami np.
    
    kliknij, aby powiększyć
    
    zakres komórek lub zdefiniowaną w menedżerze nazw listę:
    
    kliknij, aby powiększyć
    
    Efekt końcowy wygląda tak:
    
    kliknij, aby powiększyć
    
    Bardzo przydatne rozwiązanie, często stosuję.
  - Data
    
    kliknij, aby powiększyć
    
    Podobnie jak w przypadku liczb – konieczne jest wstawienie zakresu dat.
    Biorąc pod uwagę, że różni użytkownicy mają różne pomysły do zapisywania dat (co później skutkuje błędami w formułach) – warto wprowadzić to zabezpieczenie, jest naprawdę przydatne
  - Godzina
    – zasada działania jak w przypadku daty
  - Długość tekstu
    
    kliknij, aby powiększyć
    
    W tym przypadku sprawdzana jest ilość znaków wprowadzona do komórki
  - Niestandardowe
    kliknij, aby powiększyć
    
    tu możemy wpisać własną regułę – opartą na formule

Niezależnie od rodzaju wprowadzonej reguły, Poprawność danych ma też 2 dodatkowe zakładki:

- - Komunikat wejściowy
    
    kliknij, aby powiększyć
    
    tu możemy zdefiniować komunikat wyświetlany po wejściu do komórki. Nie ma tu znaczenia, jaka reguła poprawności jest ustawiona, działa także przy dowolnej wartości
  - Alert o błędzie
    kliknij, aby powiększyć
    
    Ta akcja jest widoczna jeżeli wartość w komórce nie spełnia zasady wprowadzonej do niej reguły poprawności. Warto tu wpisać treść komunikatu informującego jakie wartości są tu dopuszczalne.
    
    Możliwe są tu 3 warianty:
    - Stop (domyślny)
      
      kliknij, aby powiększyć
      
      Wprowadzanie danych zostaje zatrzymane, użytkownik musi wpisać prawidłową wartość.
    - Ostrzeżenie
      
      kliknij, aby powiększyć
      
      Wyświetla się ostrzeżenie, choć jeżeli naciśniemy przycisk Tak – Excel pozwoli zostawić w komórce błędną wartość.
    - Informacje
      
      kliknij, aby powiększyć
      
      Działanie podobne jak w przypadku Ostrzeżeń.

Zarówno Ostrzeżenia jak i Informacje pozwalają na wprowadzenie wartości niezgodnych w regułą poprawności. Chcąc sprawdzić, gdzie taka sytuacja występuje – wystarczy zaznaczyć opcję:

Błędne dane są zaznaczone. W tym miejscu można też usunąć zakreślenia błędnych danych.
Choć oczywiście te zaznaczenia znikają również po wprowadzeniu prawidłowych danych.

Często korzystam z tego narzędzia Excela – naprawdę warto jet stosować.

A tu możesz mi postawić kawę:

buycoffee.to/marzatela

Menedżer nazw

Menedżer nazw to jedno z narzędzi Excela, bardzo przydatne w codziennej pracy. Służy głównie do nadawania własnych nazw wyznaczonym zakresom komórek, obiektom, wartościom czy formułom.

Menedżer nazw znajduje się na karcie Formuły w grupie opcji Nazwy zdefiniowane.

Najszybszym i najprostszym sposobem na zdefiniowanie zakresu jest zaznaczenie obszaru, naciśnięcie przycisku Utwórz z zaznaczenia.

Nadana w ten sposób nazwa jest widoczna na liście nazw Menedżera nazw.

Na screenie widoczna jest także nazwa Tabela1 – wszystkie obiekty tabel od razu też są tu widoczne, automatycznie wstawiane przez Excela.
Zadeklarowana nazwa Kurs_Euro jest dostępna w każdym arkuszu tego skoroszytu i może być wykorzystana w każdej formule np.: =A2*Kurs_Euro

Można też przypisać nazwę do dynamicznego zakresu poprzez funkcję np.
=PRZESUNIĘCIE($E$2;;;ILE.NIEPUSTYCH($E$2:$E$100);1)

W tym przypadku wysokość zakresu jest uzależniona od ilości niepustych komórek w zakresie. Idealnie nadaje się do tworzenia list w Poprawności danych.

Przy tworzeniu nazw warto zwrócić uwagę na to, że dana nazwa może obowiązywać zarówno w całym skoroszycie jak i pojedynczym arkuszu. Domyślnym ustawieniem jest Skoroszyt:

Wszystkie nazwy muszą zaczynać się od znaku _ lub litery. Nie mogą też zawierać spacji ani niedozwolonych znaków. No i oczywiście – muszą być unikalne, nie mogą być takie same jak inne nazwy (np.oznaczenia komórek typu A1, b10 itp.)

Zdefiniowane w Excelu nazwy są też dostępne w edytorze VBA:
Range(„Kurs_Euro”)
lub
[Kurs_Euro]

A tu możesz mi postawić kawę:

buycoffee.to/marzatela

Zdarzenie OnKey

Application.OnKey to metoda obiektu Application tworząca własne skróty klawiaturowe:

Argumenty metody to:

- - Key – sekwencja klawiszy, która ma uruchomić
    argument obowiązkowy
  - Procedure – nazwa procedury, która ma zostać uruchomiona
    argument opcjonalny

Argument Key występuje w sekwencji z klawiszami:

- - Shift: znak plus (+)
  - Ctrl: znak (^)
  - Alt: znak procentu (%).

Można tu używać zarówno liter jak i klawiszy funkcyjnych (w nawiasach klamrowych):

Klawisz	Kod
Backspace	{BACKSPACE} lub {BS}
Break	{BREAK}
Caps Lock	{CAPSLOCK}
Delete lub Del	{DELETE} lub {DEL}
End	(END}
Enter	˜ (tylda)
Enter (na klawiaturze numerycznej)	{ENTER}
Escape	{ESCAPE} lub {esc]
Home	{HOME}
Ins	{INSERT}
NumLock	{NUMLOCK}
Page Down (PgDn)	{PGDN}
Page Up (PgUp)	{PGUP}
Scroll Lock	{SCROLLLOCK}
Strzałka w dół	{DOWN}
Strzałka w górę	{UP}
Strzałka w lewo	{LEFT}
Strzałka w prawo	{RIGHT}
Tab	{TAB}
F1 do F15	{F1} do {F15}

Wyłączenie tak wprowadzonego skrótu klawiszowego jest możliwe z zastosowaniem również metody Application.OnKey, ale bez drugiego argumentu czyli Proceudre

W praktyce wygląda to tak:

’ ============================
’ TEST Application.OnKey
’ ============================
’ Ustawienie skrótów klawiszowych
Public Sub SETUP_OnKey()
’ Ctrl + Shift + A – kolorowanie komórki
Application.OnKey „^+A”, „KolorujNaCzerwono”

’ Ctrl + Shift + B – wpisanie tekstu
Application.OnKey „^+B”, „WpiszTekst”

’ Ctrl + Shift + C – czyszczenie komórki
Application.OnKey „^+C”, „WyczyscKomorke”

’ Ctrl + Shift + D – komunikat
Application.OnKey „^+D”, „PokazKomunikat”

MsgBox „Skróty zarejestrowane.” & vbCrLf & _
„A = kolor” & vbCrLf & _
„B = wpisz tekst” & vbCrLf & _
„C = wyczyść komórkę” & vbCrLf & _
„D = komunikat”, vbInformation
End Sub
’ ============================
’ AKCJE POD SKRÓTY
’ ============================
Public Sub KolorujNaCzerwono()
ActiveCell.Interior.Color = RGB(255, 0, 0)
End Sub
Public Sub WpiszTekst()
ActiveCell.Value = „Test OnKey”
End Sub
Public Sub WyczyscKomorke()
ActiveCell.Clear
End Sub
Public Sub PokazKomunikat()
MsgBox „Działa!”, vbInformation
End Sub
’ ============================
’ WYŁĄCZENIE SKRÓTÓW
’ ============================
Public Sub Off_OnKey()
Application.OnKey „^+A”
Application.OnKey „^+B”
Application.OnKey „^+C”
Application.OnKey „^+D”
MsgBox „Skróty wyłączone.”, vbInformation
End Sub

Generalnie – przed zastosowaniem konkretnych przykładów – trzeba sprawdzić, czy na pewno zadziała. W niektórych przypadkach – niestety nie – tak jakby Excel miał wbudowaną blokadę własnych skrótów.

A tu możesz mi postawić kawę:

buycoffee.to/marzatela

Skróty klawiaturowe makr

Każde makro (również to nagrane) czy zdefiniowaną w kodzie VBA procedurę publiczną możemy uruchomić na kilka sposobów.
Najczęściej stosowane metody to:

- - wybranie makra z listy makr na karcie Developer

i dalej:

Wybieramy makro/procedurę i naciskamy przycisk Uruchom.

- - przypisanie makra/procedury do kliknięcia w przycisk lub kształt

Po przypisaniu makra – potem wystarczy kliknąć w kształt i makro zostanie uruchomione.

- - skrót klawiaturowy
    czyli kombinacja klawiszy uruchamiająca dane makro.
    W praktyce chyba coraz rzadziej używany, choć chyba niesłusznie. Przydaje się, gdy np. mamy skoroszyt z dużą ilością arkuszy, przeglądamy któryś z końcowych i chcemy wrócić szybko do pierwszego arkusza – w takim przypadku akurat skrót klawiaturowy sprawdzi się jako najszybsze rozwiązanie.

Chcąc przypisać skrót klawiaturowy do własnego makra zaznaczamy je na liście makr i przechodzimy do Opcji.

W nowym oknie przypisujemy skrót

Wielkość liter (mała/wielka) nie ma znaczenia, ale należy się poważnie zastanowić nad tym, czy nie nadpiszemy w ten sposób jakiegoś standardowego skrótu Excela. Tu niestety nie ma żadnych zabezpieczeń ani ostrzeżeń i w efekcie możemy stracić jakąś wbudowaną przydatną procedurę.

W VBA skrót klawiaturowy możemy przypisać za pomocą metody: Aplication.OnKey.

Standardowe, najczęściej używane skróty klawiaturowe Excela to:

CTRL + N	nowy skoroszyt
CTRL + O	otwórz plik
CTRL + S	zapisz
CTRL + P	drukuj
CTRL + W / CTRL + F4	zamknij skoroszyt
CTRL + Z	cofnięcie ostatniej operacji
CTRL + Y	ponowienie operacji
CTRL + A	zaznacz wszystko
CTRL + Strzałka	przejście do końca danych w kolumnie/wierszu
CTRL + Home	przejście do komórki A1
CTRL + End	przejście do ostatniej używanej komórki
CTRL + C	kopiuj
CTRL + X	wytnij
CTRL + V	wklej
CTRL + D	wypełnij w dół
CTRL + R	wypełnij w prawo
CTRL + F	znajdź
CTRL + H	zamień
CTRL + T	utwórz tabelę
CTRL + K	wstaw hiperłącze
CTRL + ;	wstaw dzisiejszą datę
CTRL + SHIFT + :	wstaw aktualną godzinę
CTRL + SHIFT + L	włącz/wyłącz filtry
CTRL + Page Up / Page Down	przełączanie między arkuszami
CTRL + Tab	przełączanie między otwartymi skoroszytami

Tych skrótów lepiej jednak nie używać do własnych procedur.

A tu możesz mi postawić kawę:

buycoffee.to/marzatela

Zdarzenie OnTime

Application.OnTime to metoda obiektu Application wywoływana zdarzeniem związanym z określonym czasem systemowym.

Argumenty metody to:

- - EarliestTime – czas, kiedy procedura ma zostać uruchomiona
    argument obowiązkowy
  - Procedure – nazwa procedury, która ma zostać uruchomiona
    argument obowiązkowy
  - LatestTime- ostatni czas, kiedy procedura ma zostać uruchomona
    argument nieobowiązkowy
  - Schedule – powtarzalność. Argument decydujący, czy procedura ma być ponownie uruchomiona (wartość True) czy nie (wartość False).
    Argument opcjonalny, wartość domyślna True.

Przykład procedury z wykorzystaniem metody OnTime – przez 20 minut, co minute jest wpisywany bieżący czas do kolejnych komórek kolumny K.

’Zmienne globalne:
Public NastCzas As Date
Public CzasKoniec As Date
’ Uruchomienie cyklicznego wpisywania
Public Sub StartCzas()
CzasKoniec = Now + TimeValue(„00:20:00”)
WpiszCzas
End Sub
’ Procedura wpisująca czas i planująca kolejne wywołanie
Public Sub WpiszCzas()
Dim i As Long
i = Range(„K” & Rows.Count).End(xlUp).Row + 1
Range(„K” & i).Value = Now()
’ zaplanuj kolejne wywołanie za minutę
NastCzas = Now + TimeValue(„00:01:00”)
Application.OnTime EarliestTime:=NastCzas, Procedure:=”WpiszCzas”, LatestTime:=CzasKoniec, Schedule:=True
End Sub

Excel i VBA

Poprawność danych

Dodaj do ulubionych:

Menedżer nazw

Dodaj do ulubionych:

Zdarzenie OnKey

Dodaj do ulubionych:

Skróty klawiaturowe makr

Dodaj do ulubionych:

Zdarzenie OnTime

Dodaj do ulubionych:

Udostępnij:

Dodaj do ulubionych:

Udostępnij:

Dodaj do ulubionych:

Udostępnij:

Dodaj do ulubionych:

Udostępnij:

Dodaj do ulubionych:

Udostępnij:

Dodaj do ulubionych: