08 — Opcje, które warto zmienić w edytorze VBA

Jest kilka opcji, które warto, żebyś zmienił w opcjach edytora VBA. Rozwijasz menu Tools, a następnie Options. Pojawi się okno, w którym na pierwszej zakładce (Editor), moim zdaniem warto zmienić dwie opcje.
wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-01
Pierwszą jest zaznaczenie pola wyboru Require Variable Declaration. Po jej zaznaczeniu w nowo stworzonych modułach będzie dodawać się na samej górze fraza: Option Explicit. Fraza ta wymusza deklarowanie zmiennych, co oznacza, że nie będziesz mógł przypisać wartości do zmiennej która wcześniej nie została zadeklarowane. Jeśli nie zadeklarujesz zmiennej, do której przypiszesz wartość, to w trakcie kompilacji (próby uruchomienia kodu) pojawi się odpowiedni komunikat:

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-02
Dzięki temu unikniesz problemu z ewentualnymi literówkami oraz oszczędzisz trochę pamięci przez deklarowanie zmiennych konkretnych typów.
Drugą zmianą jest odznaczenie pola wyboru Auto Syntax Check. Ta opcja automatycznie sprawdza składnie kodu i wyświetla odpowiedni komunikat podczas pisania (przykładowy komunikat poniżej).

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-03
Ten komunikat jest całkiem zbędny, ponieważ błędnie składniowo napisany kod jest podświetlany na czerwono i to jest wystarczające wyróżnienie. Natomiast wyskakujące okienko z komunikatem, może irytować i rozpraszać w trakcie pisania kodu.

Na tej zakładce możesz ewentualnie jeszcze zmienić wielkość wcięć (Tab Width).

Domyślną kolorystykę błędów składni (Syntax Error Text) oraz słów kluczowych itp., znajdziesz na drugiej zakładce opcji edytora VBA (Editor Format).

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-04
Tam co najwyżej warto zmienić rozmiar czcionki, a kolory zostawić domyślne, bo właśnie takie znajdziesz w tutorialach i pomocach w sieci.
Dodatkowa możliwość, która może Ci się przydać, to komentowanie (i odkomentowywanie) całych bloków kodu. Możesz to uzyskać dodać sobie pasek narzędzi Edit (Menu View -> Toolbars -> Edit).

wstep-do-makr-08-opcje-ktore-warto-zmienic-w-edytorze-vba-05Po dodaniu możesz go przypiąć z boku lub na górze okna edytora VBA. Tu najbardziej przydatna jest opcja komentowania bloków kodu, bo do pozostałych ważnych narzędzi przypisane są skróty klawiszowe.

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak przypisać liczby do odpowiednich kolumn (losowanie lotto) — widzowie #103

Zakładamy, że mamy przykładowe liczby (wyniki gry losowej) i chcemy je wstawić uporządkowane w kolumny im przypisane. Jest to całkiem proste bo nagłówki kolumn to dokładnie te same numery, które mogą się trafić w losowaniu.
widzowie-103-jak-przypisac-liczby-do-odpowiednich-kolumn-losowanie-lotto-01
W pierwszej kolejności musimy sprawdzić czy dana liczba z nagłówka znajduje się na liście wylosowanych liczb – wystarczy, że skorzystamy z funkcji PODAJ.POZYCJĘ. Jeśli liczba z danego nagłówka kolumny znajduje się na liście wylosowanych liczb, to funkcja PODAJ.POZYCJĘ zwróci tą pozycję (numer pozycji na liście), jeśli jej nie ma, to funkcja PODAJ.POZYCJĘ zwróci błąd (#N/D!).

Na razie nasza formuła (pamiętając o odpowiednich rodzajach odwołań) będzie wyglądać tak:

=PODAJ.POZYCJĘ(B$13;$B3:$AO3;0)

widzowie-103-jak-przypisac-liczby-do-odpowiednich-kolumn-losowanie-lotto-02
Jeśli liczba zostanie odnaleziona, to chcemy zwrócić liczbę z nagłówka, jeśli nie, to chcemy zostawić pustą komórkę. Przyda się nam do tego na pewno funkcja JEŻELI, ale jeszcze będziemy potrzebowali funkcji CZY.LICZBA, która będzie zwracać wartość PRAWDA, gdy liczba zostanie odnaleziona i FAŁSZ, gdy nie zostaje odnaleziona:

=JEŻELI(CZY.LICZBA(PODAJ.POZYCJĘ(B$13;$B3:$AO3;0));B$13;"")

Wystarczy ją przeciągnąć i mamy przyporządkowane liczby do odpowiednich kolumn.

widzowie-103-jak-przypisac-liczby-do-odpowiednich-kolumn-losowanie-lotto-03

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Stop overthinking — wyciąganie danych z arkusza obok — porada #278

Stop overthinking. O co mi chodzi? Systematycznie dostaję pytania o rozwiązanie jakiegoś problemu w Excelu i czasami się zdarza, że czytelnik zaczyna myśleć, że do rozwiązania jego problemu potrzeba jakiś skomplikowanych problemów zapominają o prostych rozwiązaniach.

W tym filmie najmocniejszy przykład overthinkingu, czyli sytuacja, gdzie potrzebujemy w osobnych arkuszach mieć tylko część danych (kolumn) z głównego arkusza. Czyli w najprostszej postaci wystarczy zwykłe odwołanie do komórek z sąsiedniego arkusza. Co prawda pewnie dla każdej kolumny będzie trzeba napisać osobną formułę, żeby pobierał dane z odpowiedniej kolumny, ale da się to zrobić, mając podstawową wiedzę o Excelu. Wystarczy formuła w stylu:

=Arkusz1!A2

Już zaczęłoby działać, robić to o co nam chodzi. To jest najważniejsze. To często powtarzam na kursach programowania VBA najważniejsze, żeby nasz kod działał. Nie musi być idealny, czy optymalny. Pierwsze najważniejsze kryterium ma działać.

Jak mawia człowiek, od którego dużo się uczę: „JAKOŚ, a później JAKOŚĆ”. „Ć” dodajemy przez ćwiczenia 😀

Z powyższym przyrównaniem do danych z innego arkusza może być tylko jeden drobny problem – jeśli komórka będzie pusta, to Excel odczyta ją jako zero. Na szczęście, żeby uniknąć tego problemu wystarczy sprawdzić wartość komórki za pomocą funkcji JEŻELI, i jeśli będzie pusta, to zwrócić pusty ciąg znaków, a jak nie będzie pusta, to wartość komórki:

=JEŻELI(Dane!A2="";"";Dane!A2)

porada-278-stop-overthinking-01

A jakby zależało Ci na pojedynczej formule na całą tabelkę to przykładowa może wyglądać tak 😉

=JEŻELI(INDEKS(Dane!$A$2:$F$14;ILE.WIERSZY($I$2:I2);PODAJ.POZYCJĘ(I$1;Dane!$A$1:$F$1;0))="";"";INDEKS(Dane!$A$2:$F$14;ILE.WIERSZY($I$2:I2);PODAJ.POZYCJĘ(I$1;Dane!$A$1:$F$1;0)))

Dlatego działaj z tym co masz, a naprawdę masz duże szanse, że rozwiążesz swój problem, a pewnie się jeszcze czegoś nowego przy tym nauczysz J

Pozdrawiam
Adam Kopeć
Miłośnik Excela

07 — Tłumaczenie makra formatującego komórki i jego modyfikacja

Na samym dole znajdziesz kod z komentarzami, tutaj zajmiemy się możliwością uruchamiania kodu krok po kroku, czyli linijka po linijce.
Kod krok po kroku możesz uruchomić za pomocą menu Debug i polecenia Step Into (skórt klawiszowy – klawisz F8).

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 01

Inny rozwiązaniem, jeśli masz trudności z odnalezieniem, gdzie został zapisany Twój kod naciśnięcie skrótu klawiszowego Alt + F8, żeby pokazała się lista makr w otwartych plikach wybranie tego, które Cię interesuje, a następnie kliknięcie przycisku Wkrocz.

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 02

Wtedy zaczniesz uruchamiać dany kod krok po kroku, dlatego lepiej upewnij się, że byłeś w odpowiednim miejscu arkusza przed uruchomieniem kodu. Jeśli chcesz zatrzymać makro wystarczy, że naciśniesz przycisk stop pod menu edytora VBA.

Linijka, która ma zostać uruchomiona jest przez edytor VBA podświetlana na żółto.

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 03

Najszybciej uruchamiasz ją naciskając klawisz F8. Czasami możesz zobaczyć, że edytor VBA podświetla kilka linijek kodu, ale wynika to z zastosowania specjalnego połączenia spacji i znaku pokreślenia (" _"), która informuje edytor, że kod ciągnie się dalej w następnej linijce.

W trakcie uruchamiania krok po kroku makra możesz przeskakiwać do Excela i obserwować zmiany jakie wprowadził kod VBA, ale pamiętaj, żeby nie zmieniać zaznaczenia i wartości, bo kolejne linijki kodu VBA mogą zadziałać nieprawidłowo.

Ważną informacją może być to, że w trakcie uruchomienia kodu krok po kroku możesz podglądać wartości zmiennych i stałych wystarczy, że najedziesz na nie myszką.

Wstęp do makr 07 - - Tłumaczenie makra formatującego komórki i jego modyfikacja 04

Sub Formatowanie()
'Nakładanie formatowania walutowego.
'Zwróć uwagę, że jest zapisane w stylu angielskim
'ale ustawienia regionalne odpowiednio je konwertują
Selection.NumberFormat = "#,##0.00 $"
'Zmiana wyrównanie w poziomie na do środka.
'Rejestrator zarejestrował dużo więcej kodu niż potrzeba,
'gdyż zapisał wszystkie właściwości dotyczące wyrównania
'w bloku With Selection...End With
With Selection
'Poniższa linijka to wyśrodkowanie w poziomie

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

End With
'Zmiana czcionki. Znów rejestrator zapisał wszystkie
'właściwości, mimo, że zmieniliśmy tylko rozmiar
'czcionki (.Size = 9) oraz styl na pogrubiony
'(.FontStyle = "Pogrubiony")
With Selection.Font

.Name = "Calibri"
.FontStyle = "Pogrubiony" 'zmieniona przez nas właściwość
.Size = 9 'zmieniona przez nas właściwość
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor

End With
'Największy kawałek kodu dotyczący obramowania (Borders)
'dla każdej strony obramowanie nakładane jest oddzielnie
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDash
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDash
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
'Dodany blok by rysował poziome wewnętrzne obramowanie
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlDash
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
'obramowania wewnętrzne ustawione na nic/brak (xlNone)
Selection.Borders(xlInsideVertical).LineStyle = xlNone
'zakomentowany kod by nie czyścił poziomego wewnętrznego obramowania
'Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'Nadanie wybrane koloru wypełnieniu
'Znów więcej zapisanych właściości niż zmodyfikowaliśmy
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub

Chcesz ze mną poznać możliwości VBA? Zobacz kiedy planowana jest następna edycja 30dniowego kursu online:
http://excelw30dni.pl/kategoria-produktu/szkolenia-vba/

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Ile razy wystąpił dany miesiąc pomiędzy dwoma datami — widzowie #102

Jeśli potrzebujesz policzyć ile dany miesiąc pojawia się pomiędzy dwoma datami, to przede wszystkim musisz odpowiednio sobie ten miesiąc zapisać, bo jeśli byś zapisał miesiąc jako tekst np.: "styczeń", to ciężko byłoby Ci pracować z datami. Dlatego w Excelu zrobimy odwrotną rzecz – najpierw napiszemy datę ze stycznia to może być dowolny rok. Załóżmy, że 2016-01-01.

Teraz masz dwie możliwości, albo przeciągasz datę w dół łapiąc za prawy dolny róg komórki prawym przyciskiem myszy i jak go puścisz, to z podręcznego menu wybierasz opcję wypełnij miesiącami.

https://exceliadam.pl/wp-content/uploads/2016/09/Widzowie-102-Ile-razy-był-dany-miesiąc-pomiędzy-dwoma-datami-01.png

Albo wykorzystujesz funkcję NR.SER.OST.DN.MIES dzięki, której możesz wyznaczyć ostatni dzień miesiąca i wystarczy dodać jedynkę, żeby uzyskać pierwszy dzień kolejnego miesiąca:

=NR.SER.OST.DN.MIES(C2;0)+1

Widzowie 102 - Ile razy był dany miesiąc pomiędzy dwoma datami 02

Kolejnym krokiem będzie zaznaczenie wszystkich dat i przejście do formatowania komórek (skrót klawiszowy Ctrl + 1). Odpowiednio na zakładce Liczby wybierasz kategorię Niestandardowe i jako kod wpisujesz cztery razy "m".

Widzowie 102 - Ile razy był dany miesiąc pomiędzy dwoma datami 03

Teraz mamy przygotowane wszystkie miesiące w postaci dat, które Excel łatwiej zrozumie i możemy zacząć nasze obliczenia. Przede wszystkim wykorzystamy funkcję DATA.RÓŻNICA, by obliczyć ilość pełnych lat pomiędzy wybranymi datami. Excel nie podpowiada tej funkcji, ale jest ona dostępna.

=DATA.RÓŻNICA($A$1;$A$2;"Y")

Widzowie 102 - Ile razy był dany miesiąc pomiędzy dwoma datami 04

Teraz pozostaje sprawdzić, czy miesiąc z naszej ukrytej daty jest większy bądź równy początkowej dacie i mniejszy bądź równy końcowej dacie. Jeśli tak to będziemy chcieli do naszego wyniku dodać jeszcze 1. W sumie sprowadza się to do formuły:

=DATA.RÓŻNICA($A$1;$A$2;"Y")+JEŻELI(ORAZ(MIESIĄC(C2)<=MIESIĄC($A$2);MIESIĄC(C2)>=MIESIĄC($A$1));1;0)

Widzowie 102 - Ile razy był dany miesiąc pomiędzy dwoma datami 05

Pozdrawiam
Adam Kopeć
Miłośnik Excela