Makra, nazywane też makropoleceniami są małymi programami zapisanymi w plikach Excela. Najprościej można powiedzieć, iż służą automatyzacji często powtarzanych czynności w programie Excel. Ich niewątpliwą zaletą jest fakt, iż nawet użytkownik, który nie zna języka programowania VBA (Visual Basic for Applications) jest w stanie stworzyć mini-program, który może potem wykorzystywać w codziennej pracy, dzięki wykorzystaniu rejestratora makr wbudowanego w niektóre programy pakietu MS Office.
Przykładem może być np. polecenie, które ma za zadanie wkleić formaty i wartości - można stworzyć makro, które zrealizuje te dwie czynności od razu po sobie, zamiast używać dwukrotnie poleceń z grupy "Wklej specjalnie".

W sytuacji, kiedy poznamy choćby elementy wspomnianego języka i podstawy programowania, możliwości jakie uzyskamy stają się nieporównywalnie większe i wówczas można by mówić o nadużyciu w kontekście tylko "automatyzacji". Wykorzystując środowisko programistyczne VBA, jesteśmy w stanie tworzyć nawet bardzo rozbudowane aplikacje, które nie korzystają jedynie z programów pakietu MS Office.

Od wersji Office 2007, w przypadku konieczności użycia nowego formatu pliku, są one rozróżniane: *.xlsm - służy przechowywaniu pliku w nowym formacie przy zachowaniu ogromnej liczby wierszy i kolumn wraz z obsługą makr, *.xlsx - - nie umożliwia zachowania makr w pliku. W naszych przykładach będziemy pracować we wspomnianym w kursie podstawowym "Trybie zgodności", zapisując pliki w poprzednim formacie *.xls, który obsługuje makra.

W tym kursie przedstawimy sobie dwa zasadnicze aspekty makr:

• rejestrator makr wraz z niewielką modyfikacją kodu
• pisanie makr/programów od zupełnego początku.

Jak już wspomniano na wstępie, dzięki makrom możliwa jest automatyzacja często wykorzystywanych poleceń, które nie są łatwo dostępne np. na wstążce programu. Podobnie można też wykorzystać je w przypadku, kiedy polecenia są łatwo dostępne, ale jest ich więcej - innymi słowy do uruchomienia jednym przyciskiem lub skrótem klawiaturowym sekwencji poleceń.
Nie będziemy w tym miejscu rozważać możliwości jakie daje tworzenie makr od początku - czyli całych projektów VBA. Tym zagadnieniem zajmiemy się w dalszej części tego kursu.

Aby można było rejestrować i używać makr muszą być spełnione następujące warunki:

• plik musi być w formacie obsługującym makra
• dobrze jest mieć włączoną kartę Deweloper na wstążce programu Excel (w Excel 2010 jest włączona domyślnie).
• trzeba ustawić obsługę makr w programie Excel

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Najważniejszą rzeczą, którą musimy sobie uświadomić przed rejestracją makra jest fakt, iż rejestrator zapisze dokładnie każdą czynność, którą wykonamy - łącznie z przewijaniem ekranu, przełączaniem się pomiędzy arkuszami itd. - nawet jeśli czynności te nie mają wpływu na końcowy efekt poleceń, które są istotą zadania. Spróbujemy zobrazować ten fakt, rejestrując wcześniej przytoczony przykład: nagramy makro, które formatuje tą komórkę (kolorując ją na żółto) oraz wpisuje do dodanej komórki wartość 1 na dwa sposoby:
- zarejestrujemy czynność wpisania formatowania i wpisania wartości po wybraniu określonej komórki,
- zarejestrujemy samą czynność formatowania i wpisania wartości.
Następnie skupimy się na różnicach w działaniu nagranych makr.

Uruchamiamy rejestratora makr:

Pomijając jedną dość istotną opcję podczas rejestracji makra w poprzednim rozdziale spowodowaliśmy, iż nasze makro było dostępne jedynie w ramach danego skoroszytu (pliku) programu Excel. Tymczasem możemy zapisywać makra w innych lokalizacjach, dzięki czemu będą one jeszcze bardziej uniwersalne. Spójrzmy ponownie na ekran pojawiający się na początku rejestracji makra:

Moment, w którym uruchamialiśmy makro (odpowiednie okno), dawał nam jeszcze jedną możliwość: umożliwiał przypisanie wykonania makra do skrótu klawiaturowego. Innymi słowy po użyciu odpowiedniej kombinacji klawisza "Ctrl" i któregoś z innych określone makro będzie się wykonywało, bez wcześniejszego wyświetlania listy makr.
W trakcie rejestrowania makra, możliwość przypisania skrótu, daje nam polecenie umieszczone na oknie dialogowym:

Definiowanie różnego rodzaju obiektów, jako te, które mogą uruchamiać makropolecenia jest kolejną z bardzo wygodnych metod ich późniejszego wykorzystania. W tym miejscu krótko omówimy dwa dodatkowe elementy: umieszczanie dodatkowego przycisku na wstążce oraz przypisywanie makra do formantów formularza, które możemy osadzać bezpośrednio w arkuszu.

W przypadku pierwszej opcji - musimy wyświetlić "Opcje programu Excel":

W ostatnim rozdziale tej części kursu, zajmiemy się chwilę nagranym makrem od strony jego kodu. Nie będziemy jeszcze tworzyć/pisać programu od początku, ale spróbuję zainteresować czytelnika na tyle, aby miał ochotę poznać bliżej język VBA omówiony w dalszych rozdziałach.
Pamiętamy okno, które uruchamiane było poleceniem Makra na kacie Deweloper wstążki:

Uruchomienie środowiska programowania VBA, czyli okna, w którym będziemy mogli tworzyć nasze programy odbyć się może na parę sposobów.

Pierwszy z nich (i chyba najszybszy i najprostszy zarazem) to skrót klawiaturowy Alt+F11. Po jego użyciu od razu pojawi się okno "Microsoft Visual Basic":

Przypomnijmy sobie jak wygląda uruchamiane w poprzednim rozdziale okno środowiska VBA:

Pod pojęciem lokalizacji kodu rozumiemy miejsce, w którym umieszczamy kod wraz z konsekwencjami, jakie to za sobą niesie. I tak:

• jeśli kod umieścimy w skoroszycie makr osobistych (wspomnieliśmy o nim podczas omawiania zapisywania) - klikając dwukrotnie w pokazany obiekt, wyświetlimy kod w oknie z prawej strony (na naszym obrazie jest ono białe):
  
  
  
  wówczas zapisany kod będzie dostępny w każdym pliku (skoroszycie) programu Excel. Innymi słowy możemy go uruchomić z dowolnego miejsca programu Excel na danym komputerze. Trzeba jednak zadbać o odpowiednią "uniwersalność" takich poleceń
• w przypadku, kiedy kod zostanie umieszczony w arkuszu:
  
  
  
  wtedy, kod będzie dostępny w ramach danego pliku Excel. Dodatkowo domyślnie odwołania i polecenia dotyczą arkusza, w którym są zapisane - wykorzystanie ich dla innego arkusza wymaga jawnej deklaracji. O szczegółach odwołań powiemy sobie za chwilę.
• kod umieszczony w arkuszu ThisWorkbook:
  
  
  
  pozwoli nam na definiowanie zdarzeń/poleceń, które mają być np. uruchamiane podczas włączania danego skoroszytu - dzięki użyciu procedury: "Workbook_open()" - przykład podamy także za chwilę, w rozdziale omawiającym procedury.
• kod może być też umieszczony w module:
  
  
  
  wówczas jest dostępny w każdym miejscu danego skoroszytu/pliku. O ile nie użyliśmy jawnym odwołań do poszczególnych arkuszy, określona procedura/funkcja będzie wykonywać sekwencje poleceń w odniesieniu do bieżącego arkusza.
• pomijając klasy, kod może być umieszczony jeszcze w formularzach UserForm, o których też sobie jeszcze powiemy. Wówczas dostęp do tego kodu możliwy jest po użyciu polecenia View Code, wyświetlanego po kliknięcia w obiekt prawym klawiszem myszy:
  
  
  
  W tym przypadku także możliwe są oczywiście odwołania do arkuszy (jawnie zdefiniowane), natomiast co do zasady umieszcza się tam procedury, które określają polecenia w reakcji na użycie formantów umieszczonych na danym formularzu. Czyli jeśli np. formularz UserForm będzie zawierał przycisk, to kod, który ma być wykonany po jego naciśnięciu będzie umieszczony właśnie tu.

O referencjach wspominaliśmy już we wcześniejszej części tego kursu - omawiając menu środowiska VBA. Teraz doprecyzujemy to zagadnienie bliżej.
Jak już napisane było poprzednio, wykorzystanie referencji zwiększa funkcjonalności standardowego zestawu poleceń języka VBA. Wiele niestandardowych bibliotek, do których odwołujemy się poprzez referencje, jest dołączonych do instalacji Office. Bardzo często jest też tak, że producenci różnego rodzaju oprogramowania - niekoniecznie związanego z MS Excel - dołączają biblioteki, które udostępniają w ramach języka VBA dodatkowe funkcjonalności związane z określonym oprogramowaniem. Mogą to być np. polecenia umożliwiające pobieranie danych z określonych baz danych.

Dodanie określonej referencji odbywa się poprzez użycie polecenia References w menu Tools programu:

Bardzo krótko należy odnieść się także do kwestii nazewnictwa w naszym projekcie VBA pod Excel. Tak jak w przypadku każdego innego środowiska programistycznego, w projekcie będziemy używać różnego rodzaju obiektów, formularzy, formantów, zmiennych, stałych itd. Pomijając fakt, iż nazwy własne tych obiektów nie powinny zawierać żadnych znaków specjalnych, a jedynie litery arabskie bez polskich znaków wraz z podkreśleniami i cyframi (ale nie zaczynające się od cyfr) warto przemyśleć jak będziemy rozróżniać typy obiektów.

Jako przykład możemy podać, że chcemy rozróżniać w każdym miejscu kodu przycisk od okna tekstowego. Podczas omawiania interfejsu, wspominaliśmy o oknie Properties, które wyświetla właściwości danego obiektu. W tym właśnie oknie mamy też zawsze właściwość nazwaną "Name", która dotyczy nazwy tego obiektu. Aby zobrazować przykład, musimy wybiec trochę wprzód i dodać do arkusza dwa formanty ActiveX (przycisk oraz pole tekstowe):

W tym rozdziale omówimy sobie sposób deklaracji procedur oraz wynikające z odpowiednich sformułowań następstwa. Procedura zawiera ciąg poleceń jakie mają być wykonane przez program. Jakiekolwiek polecenia (poza deklaracją zmiennych i poleceniami określającymi zachowanie się programu) formułowane w języku VBA muszą być ujęte w procedurach lub funkcjach (pomijam klasy). W formie procedur zapisywanych w modułach są rejestrowane makra, omówione na początku tego kursu. Generalna zasada definiowania procedur mówi o bezwzględnie wymaganych elementach, z których musi się ona składać:

Sub procedura_testowa()

End Sub

Sub procedura_testowa(parametr As Integer)

End Sub

procedura_testowa 1

Private Sub procedura_testowa(parametr As Integer)

End Sub

Public Sub procedura_testowa(parametr As Integer)

End Sub

Deklaracja funkcji jest bardzo zbliżona do deklaracji procedury. Poza użyciem sformułowania Function zamiast Sub ma jeszcze jedną zasadniczą różnicę. Funkcja zwraca wynik, a odbywa się to poprzez przypisanie określonej wartości do jej samej wewnątrz funkcji. Aby to zobrazować, stwórzmy prostą funkcję, która przyjmuje parametr w postaci liczby całkowitej i zwraca wynik w postaci kwadratu podanego argumentu:

Function kwadrat(liczba As Integer)

kwadrat = liczba * liczba

End Function

Ostatnia część związana z deklarowaniem obiektów dotyczyć będzie zmiennych. Zmienne można określić jako ten element języka, w którym przechowywane są informacje (dane). W zależności od sposobu deklaracji oraz typu zmiennej dane mogą przyjmować różny format oraz być dostępne z różnego miejsca.

Deklaracja zmiennej wygląda następująco:

Sub test()

Dim licznik As Integer

End Sub

• Byte - najprostszy (i zajmujący najmniej miejsca w pamięci komputera) typ danych, który pozwala przechowywać liczby całkowite z przedziału od 0 do 255 - świetnie się nadaje do wykorzystania jako wszelkiego rodzaju licznik w pętlach, których liczba powtórzeń nie przekracza wspomnianej przez chwilą wartości.
• Integer - przechowuje również liczby całkowite, ale z przedziału -32 768 do 32 767.
• Long - typ przechowujący największe liczby całkowite: od -2 147 483 648 do 2 147 483 647.
• Date - typ do przechowywania danych w formacie daty i czasu. Ważne jest, iż data musi być podawana pomiędzy znakami "#" i jeśli jest podana opisowo, to musi być to język angielski - czyli 1 stycznia 2010 r. - #1 January 2010#. Zakres dat jaki jest obsługiwany to od 1 stycznia 100 do 31 grudnia 9999.
• String - typ zmiennej przechowujący łańcuch znaków (czyli tekst). Opcjonalnie podana gwiazdka z liczbą za słowem "String" pozwoli na ograniczenie długości łańcucha znaków (czyli jak napiszemy "As String * 10" - to zmienna będzie przechowywać maksymalnie 10 znaków). UWAGA: próba zapisania dłuższego tekstu do zmiennej ze stałą długością nie skończy się ostrzeżeniem - program zapisze po prostu "obcięty" łańcuch nie informując nas o tym.
• Variant - ostatni z najważniejszych typów danych - jest najbardziej ogólny, w zmiennej tego typu możemy zapisać niemal wszystko. Warto wspomnieć, iż jeśli używamy zmiennych bez ich wcześniejszego deklarowania, to przyjmują one taki właśnie typ.
  
  Drugim elementem, który wymaga krótkiego komentarza jest miejsce deklaracji zmiennych oraz to co z tego wynika. Najczęściej deklaracja zmiennych ma miejsce wewnątrz określonej procedury lub funkcji. Takie zmienne są deklarowane na samym początku, przed napisaniem pierwszej instrukcji - aby uniknąć sytuacji, że do zmiennej przypisujemy np. najpierw wartość, a potem dopiero ją deklarujemy - w takiej sytuacji program zwróci błąd i powie, że zmienna o takiej, a takiej nazwie już istnieje i jest wykorzystywana. Aby tego uniknąć, na samej górze modułu, w którym umieszczamy procedury, możemy użyć polecenia:
  

  
  Option Explicit
  
  Sub test()
  
  End Sub
  

  Option Explicit spowoduje, że program nie pozwoli na użycie zmiennych, które nie zostały wcześniej zdefiniowane. Dodatkową korzyścią jest unikanie literówek w używanych zmiennych - jeśli się pomylimy, przy próbie uruchomienia zostaniemy poinformowani o błędzie stosownym komunikatem:
  
  
  
  Warto pamiętać, iż zmienne zdefiniowane w ramach danej procedury są dostępne dopóty, dopóki program nie wyjdzie z tej procedury - po jej zakończeniu pamięć zostaje zwolniona.
  Jeśli zaszła by potrzeba zdefiniowania zmiennej o szerszym zasięgu, należy uczynić to poza modułem na jego górze (np. po poleceniu "Option Explicit"):
  

  
  Option Explicit
  Dim licznik As Byte
  
  Sub test()
  
  End Sub
  

  taka zmienna (nazywana "globalną") będzie przechowywała wartość podczas całego czasu korzystania ze stworzonej aplikacji, można z niej też korzystać w każdym module i innym obiekcie danego projektu. Tego typu zmiennych nie można tworzyć w module klas.
  Jeśli z jakiegokolwiek powodu chcielibyśmy utworzyć "stałą" - czyli obiekt przechowujący określoną informację, musimy użyć zamiast sformułowania Dim polecenia Const i przypisać do niego określoną wartość:
  

  
  Option Explicit
  Dim licznik As Byte
  Public Const licznik2 as Byte = 123
  
  Sub test()
  
  End Sub
  

  Nasz przykład dotyczy stałej globalnej, natomiast równie dobrze można deklarować stałe wewnątrz procedur z konsekwencjami podobnymi do tych, które dotyczą zmiennych (bez użycia "Public").

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

W tym rozdziale zaczniemy tworzyć proste procedury, które będą miały za zadanie pokazanie czytelnikowi jak można odwoływać się do różnego rodzaju obiektów. Będziemy wywoływać procedury, przypisywać dane do zmiennych, odwoływać się do skoroszytów, arkuszy, kolumn, wierszy, obszarów i komórek. Sprawdzimy też jak wykorzystać niektóre z właściwości wspomnianych obiektów i jak je zmieniać. W tym celu utwórzmy moduł w pustym pliku Excela:

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Zwykłe operacje na obiektach arkusza, przypisywanie danych i tym podobne czynności są potrzebne, lecz tak naprawdę nie wnoszą nic poza zakres poleceń programu Excel dostępnych bez użycia języka programowania VBA.
Aby tworzony program realizował zadania wykraczające poza zwykłe czynności, musimy nauczyć się przekazywać do niego pewnego rodzaju logikę, czyli elementy przyczynowo-skutkowe i reakcje na komendy wydawane przez użytkownika.
Ten rozdział będzie prezentował instrukcję If - Then - Else, która wzbogaca kod, umożliwiając wykonanie sekwencji poleceń w zależności od wypełnienia warunku w niej postawionego. Załóżmy, że podobnie jak w poprzednim rozdziale, przygotujemy procedurę, która będzie miała za zadanie przenieść odpowiednie wartości pomiędzy komórkami (dla ułatwienia przygotujemy ją w ramach jednego arkusza), jednak lokalizacja docelowa będzie uwarunkowana od załóżmy wartości. Komórka A1 będzie zawierać jedną wartość, B1 drugą, natomiast w zależności od tego która z nich jest większa, będzie ona wstawiana do komórki C1:

Sub wklej()

If Cells(1, 1).Value > Cells(1, 2).Value Then

	Cells(1, 3).Value = Cells(1, 1).Value

	Else

		Cells(1, 3).Value = Cells(1, 2).Value

End If

End Sub

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Poprzednio omawialiśmy instrukcję warunkową If, która świetnie sprawdza się w sytuacji, kiedy mamy jednoznaczne rozstrzygnięcie postawionego warunku: albo jest spełniony, albo nie. Zdarzają się jednak sytuacje, w których możliwych rozstrzygnięć jest więcej - kiedy np. określone wyrażenie lub warunek może przyjąć więcej rozwiązań. Oczywiście, czasami wystarczy (i jest zasadne) zagnieżdżenie kolejnej instrukcji If - natomiast najczęściej w takich sytuacjach z pomocą przychodzi instrukcja Case.

Option Explicit

Sub case_in()

Select Case Cells(1, 1)

Case 1
Cells(1, 2) = "jedynka"

Case 2
Cells(1, 2) = "dwójka"

Case 3 To 4
Cells(1, 2) = "liczba od 3 do 4"

Case Is > 4
Cells(1, 2) = "więcej niż 4"

Case Else
Cells(1, 2) = "coś innego"

End Select

End Sub

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Poprzednie dwa rozdziały omawiały krótko instrukcje warunkowe, pozwalające na uzależnienie poleceń zawartych w procedurze/funkcji od spełnienia określonych warunków. W tym miejscu omówimy sobie kolejny element: pętlę.
Zaczniemy od pętli, w przypadku której z góry określamy liczbowo ilość powtórzeń. Załóżmy, że chcielibyśmy za pomocą języka VBA napisać procedurę, która wylosuje nam 100 kolejnych liczb z przedziału 0 do 1000 i umieści je w kolumnie, począwszy od aktywnej komórki. W tym celu musielibyśmy użyć sekwencji np. takich poleceń:

Option Explicit

Sub petla_losowa()

Dim licznik As Byte

For licznik = 1 To 100

	ActiveCell = Round(Rnd() * 1000, 0)
	Cells(ActiveCell.Row + 1, ActiveCell.Column).Select

Next licznik

End Sub

For licznik = 1 To 100 Step 2

For licznik = 100 To 1 Step -1

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Tym razem omówimy krótko pętlę, która dodatkowo zawiera w sobie warunek sprawdzający. Innymi słowy, liczba powtórzeń poleceń w niej zawartej zależy od tego, jakie są efekty wykonywanych sekwencyjnie poleceń. Pętla ta może być użyta na dwa sposoby: może wykonywać sekwencję poleceń dopóki warunek sprawdzany na jej początku lub końcu jest spełniony, lub też wykonywać sekwencję poleceń, do momentu aż warunek ten zostanie spełniony.

Pętla z wyrażeniem "While"

Pętla, która powtarzać będzie określone polecenia, dopóki warunek jest spełniony. Załóżmy, że chcemy wypełniać komórki liczbami losowymi od 0 do 1000 w kolejnych komórkach kolumny, do momentu kiedy losowane liczby są mniejsze lub równe od 750. Użyjemy tych samych poleceń, jak w przykładzie z poprzedniego rozdziału, jednak umieścimy je w innej pętli:

Sub petla_do_while()

Dim zmienna_losowa As Integer

zmienna_losowa = Round(Rnd() * 1000, 0)

If zmienna_losowa > 750 Then
	Exit Sub
	Else

		Do While zmienna_losowa <= 750
			ActiveCell = zmienna_losowa
			Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
			zmienna_losowa = Round(Rnd() * 1000, 0)
		Loop

End If

End Sub

Sub petla_do_until()

Dim zmienna_losowa As Integer

zmienna_losowa = Round(Rnd() * 1000, 0)

If zmienna_losowa > 750 Then
	Exit Sub
	Else

		Do Until zmienna_losowa > 750
			ActiveCell = zmienna_losowa
			Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
			zmienna_losowa = Round(Rnd() * 1000, 0)
		Loop

End If

End Sub

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Przedostatni rozdział w tej części kursu będzie miał za zadanie przybliżyć czytelnikowi podstawowe elementy obsługi błędów i sytuacji nieprzewidywalnych.
Niemal zawsze zdarza się, że w bardziej skomplikowanych aplikacjach trudno przewidzieć wszystko to, co późniejszym użytkownikom naszego programu "przyjdzie na myśl" z nim zrobić. Takie sytuacje są praktycznie nie do wyeliminowania, ale możemy spróbować zabezpieczyć nasz program przed ich skutkami lub spowodować skłonić użytkownika do określonych czynności - pytając go o pewne rzeczy lub informując go.

Najpierw o obsłudze błędów. Aby program potrafił poradzić sobie z każdym nieprzewidzianym zdarzeniem, powinniśmy użyć polecenia "On Error"" i dalej jego opcji:

• On Error GoTo - i podać etykietę wiersza (opcjonalnie może być to też numer linii). Etykieta wiersza jest tekstem, który musi zaczynać się od pierwszej kolumny kodu i zaraz po nim następuje dwukropek, a dopiero po nim właściwy kod programu
• On Error GoTo 0 - wyłącza zupełnie obsługę błędów w ramach danej procedury/funkcji
• On Error Resume Next - w razie błędu przejdź do kolejnego polecenia.

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

W ostatnim rozdziale tej części kursu, omówimy najważniejsze elementy związane z obiektem nazwanym Application. Właściwości oraz metody odnoszące się do tego obiektu mają odzwierciedlenie w sposobie działania całej aplikacji MS Excel. Elementy, o których należy wspomnieć to:

• Application.ScreenUpdating - który przyjmuje wartość "True" lub "False". Ustawiając właściwość Application.ScreenUpdating=False, wyłączamy zupełnie odświeżanie ekranu. Przydatna funkcjonalność w sytuacji, kiedy z określonego powodu musimy przełączać się pomiędzy oknami lub arkuszami pliku Excel, natomiast niekoniecznie chcemy oglądać "migający ekran" i wszelkie inne zmiany zachodzące w trakcie działania programu. Aby przywrócić normalne działanie, ustawiamy właściwość na "True".
• Application.Calculation - kolejna ciekawa właściwość tego obiektu, pozwalająca na wyłączenie automatycznego obliczania (kiedy równa się "xlCalculationManual") lub ponowne jego włączenie ("xlCalculationAutomatic"). Dodatkowo jeśli przeliczanie jest wyłączone, a w trakcie działania programu potrzebujemy go jednokrotnie użyć, możemy zastosować metodę Application.Calculate (dla całego skoroszytu) lub Application.ActiveSheet.Calculate dla przeliczenia aktualnego arkusza, bez włączania automatycznego przeliczania całości.
• Application.GetOpenFilename - jest to metoda, powodująca otwarcie okna dialogowego typu "Otwórz plik". Po niej, w nawiasie możemy podać odpowiedni komunikat oraz zdefiniować filtr tego okna, np.: ("Pliki Excel (*.xls), *.xls") - dla wyświetlenia tylko plików Excel. Metoda zwraca pełną ścieżkę do pliku, który wybierze użytkownik.
• Application.Intersect - potrafi wskazać obszar będący częścią wspólną wielu innych zakresów.

W jednym z rozdziałów kursu zaawansowanego Excel omawialiśmy formanty formularza. Powiedzieliśmy, że pozwalają one wzbogacić arkusz o różnego rodzaju interaktywne elementy takie jak: przycisk, etykieta, pokrętło itp. W tej chwili będziemy omawiać bardzo podobne obiekty, dostępne w tym samym miejscu wstążki:

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Jak wspomniano wcześniej, formanty ActiveX można dodać do danego arkusza używając odpowiednich poleceń karty Deweloper na wstążce:

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

W tym miejscu omówimy sobie tzw. formularz "UserForm". Wstawiamy go do projektu odpowiednim poleceniem w aktywnym środowisku VBA:3

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Kolejne rozdziały będą opisywać najważniejsze i najczęściej używane funkcje wbudowane w język programowania VBA. Zaczniemy od bardzo często używanych funkcji tekstowych, służących do różnego rodzaju operacji na łańcuchach znaków.

W tym celu przygotujemy krótką procedurą, która wykonuje następujące czynności dla tekstu umieszczonego w komórce A1 Arkusza1:

• obliczy długość tego tekstu,
• zwróci pierwszych 10 znaków od lewej,
• zwróci pierwsze 10 znaków od prawej,
• zwróci 10 znaków od lewej, począwszy od 5-go,
• zamieni myślnik na średnik (o ile istnieje),
• podzieli tekst na fragmenty rozdzielone myślnikiem i wklei te elementy w kolejne kolumny arkusza

Bardzo przydatne oraz często używane funkcje, które mają za zadanie konwertować odpowiednie wyrażenia na pożądany format. Przydatne np. w sytuacji kiedy określone wyrażenie chcemy poddać czynnościom (użyciu funkcji lub operatorów), których argumentem może być tylko dany typ zmiennej lub wyrażenia (tak jest np. w przypadku stosowania operatorów matematycznych, gdzie wynik może się różnić w zależności od typu danych). Dla zobrazowana tematu spójrzmy na następujący przykład:

Dim test As String
test = "10"
Cells(3,3) = test + test

• CByte - konwersja do typu "Byte",
• CDate - do typu "Date",
• CDbl - do typu "Double",
• CInt - do typu "Integer",
• CLng - do typu "Long",
• CStr- do typu "String".

Osobiście rzadko używam funkcji matematycznych w kodzie języka VBA - gdyż do wykonywania obliczeń preferuję stosowanie funkcjonalności arkusza kalkulacyjnego - do czego zresztą jest on w pierwszej kolejności przeznaczony.

O wiele większe znaczenie mają zwykłe operatory, które można wykorzystywać budując odpowiednie wyrażenia lub obliczenia. W języku VBA możemy stosować najbardziej oczywiste operatory takie jak: "+" dla dodawania (łączenia przypadku łańcuchów), "-" dla odejmowania, "/" dla dzielenia oraz "*" dla mnożenia. Zastosowanie nawiasów "()" w kombinacji z operatorami pozwala na jednoznaczne zdefiniowanie pożądanej kolejności obliczeń.

Jeśli natomiast chodzi o same funkcje wbudowane, to należy wspomnieć przede wszystkim o:

• Abs - wartość bezwzględna wyrażenia będącego jej argumentem,
• Fix i Int - dwie funkcje pozwalające na uzyskanie części całkowitej z podanego argumentu. Różnią się tym, że Fix przy argumentach ujemnych, daje w wyniku wartość większą lub równą (czyli jak mamy np. -8,5 to wynik będzie -8), natomiast Int w tym przypadku zwraca wartość mniejszą lub równą (czyli dla -8,5 wynik wyniesie -9). Obie funkcje działają identycznie dla argumentów pozytywnych.
• Rnd - zwraca losową liczbę z przedziału 0 - 1,
• Sgn - w zależności od tego czy argument jest dodatni, równy zeru lub ujemny, zwraca 1, 0 lub -1 (czyli pozwala uzyskać znak argumentu),
• Round - funkcja, która pozwala na zaokrąglanie wartości będących argumentem, do miejsc podanych po przecinku, określonych w drugim argumencie (jeśli nie podamy go, wynik będzie po prostu liczbą całkowitą).

Ostatnią grupą funkcji, jaką chciałbym krótko omówić są funkcje daty i czasu. Pierwszą z nich, która jest dosyć często wykorzystywana jest Now. Funkcja ta nie zawiera argumentów: Now(). Jej wynikiem jest po prostu aktualna systemowa data i godzina. Kolejnymi funkcjami są te, które z istniejących dat pozwalają wyjąć odpowiednio rok, miesiąc, dzień, godzinę, minutę i sekundę - są do odpowiednio funkcje: Year, Month, Date, Hour, Minute i Second . Ich argumentami są wyrażenia w formacie dat/godzin.

Dodatkowe funkcje, o których trzeba wspomnieć to te, które pozwalają dokonywać obliczeń na datach:

• DateAdd - pozwala na dodanie dowolnego interwału do podanej daty. Posiada trzy argumenty: interval, number i date. W pierwszym podajemy jakiego rodzaju cechę dodajemy (dostępne są "yyyy" dla lat, "m" dla miesięcy, "y" dla dni, "d" dla dni w roku, "w" dla dnia tygodnia, "ww" dla tygodni, "h", "n" i "s" dla godzin, minut i sekund), w drugim wpisujemy konkretną liczbę, w trzecim argumencie umieszczamy datę do której dodajemy/odejmujemy poprzedni argument. Czyli jeśli byśmy chcieli obliczyć jaka to jest data 90 dni od daty #2010-01-30#, wpisalibyśmy: DateAdd("y", 90, #2010-01-30#)
• DateDiff - druga z funkcji, która pozwala obliczyć różnicę pomiędzy dwiema datami, podanymi jako argumenty. Kolejno podajemy interwał (jak w poprzedniej funkcji) oraz dwie daty. Czyli gdyby chcieć obliczyć ile dni jest pomiędzy 30 września 2010 r. oraz 24 marca 2010 r. wpisalibyśmy: DateDiff("y", #2010-03-24#, #2010-09-30#) - aby uzyskać wartość dodatnią.

Funkcje użytkownika zostały zasygnalizowane już we wcześniejszych częściach niniejszego kursu. W tym miejscu jednak temat ten spróbujemy trochę rozwinąć.

Deklarując funkcję w skoroszycie makr osobistych danego użytkownika zyskujemy przede wszystkim możliwość rozszerzenia standardowych funkcji Excela o własne. Oczywiście podstawowym warunkiem jest to, aby budowane funkcje miały wystarczająco częste i uniwersalne zastosowanie, dzięki czemu umieścimy je właśnie w tym miejscu (pliku personal.xlsb).
W przypadku natomiast jeśli chcielibyśmy zautomatyzować powtarzalne czynności w ramach danego projektu (pliku), wystarczy że umieścimy je w module makr tego pliku (wobec czego będzie ona dostępna tylko w ramach tego skoroszytu).

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Jak już wspomnieliśmy, deklarowanie funkcji odbywa się poprzez użycie słowa kluczowego "Function" i następnie nazwania jej, po czy w nawiasie można podać argumenty takiej funkcji.

Załóżmy, że chcielibyśmy zadeklarować funkcję, wynikiem której będzie maksymalna wartość w podanym zakresie, przy spełnieniu określonego warunku. Innymi słowy funkcja taka będzie działać podobnie jak wbudowana funkcja Excela SUMA.JEŻELI, tylko będzie szukać maksymalnej wartości dla danego warunku. Definicja mogłaby być np. taka:

Function MAX_JEZELI(kryteria As Range, warunek As String, zakres As Range)

Dim licznik As Long

licznik = 1

For licznik = 1 To kryteria.Count
	If kryteria.Item(licznik) = warunek Then
		MAX_JEZELI = zakres.Item(licznik)
		GoTo sprawdz
		Else
			MAX_JEZELI = "Brak podanego warunku"
	End If
Next licznik

sprawdz:
For licznik = licznik + 1 To kryteria.Count
	If kryteria.Item(licznik) = warunek And zakres.Item(licznik) > MAX_JEZELI Then
		MAX_JEZELI = zakres.Item(licznik)
	End If
Next licznik

End Function

• kryteria - podane jako zakres w którym będzie szukany warunek,
• warunek - warunek, o ile zostanie spełniony - będzie brany pod uwagę dany wiersz lub kolumna,
• zakres - zakres danych, z których będzie szukane maksimum dla tych elementów na poziomie których jest spełniony warunek.

Przykład użycia funkcji użytkownika przedstawiono już pośrednio w poprzednim rozdziale. W tym tylko możemy go uzupełnić w taki sposób, iż po zadeklarowaniu danej funkcji w module pliku, używając polecenia wstaw funkcję i następnie Funkcje użytkownika powinna ona być odstępna:

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Kolejne części tego kursu traktować będą nt. obsługi danych zewnętrznych z poziomu języka VBA. W pierwszym rozdziale temu poświęconym skupimy się na plikach tekstowych. Zobaczymy zarówno jak zapisywane są w kodzie języka VBA kwerendy pobierające dane - używając zwykłego rejestratora makr i analizując potem kod. Następnie spojrzymy też na typowe polecenia odczytujące dane z plików - bez użycia kwerend i rejestratora. Sprawdzimy jak otwierać i zamykać pliki, jak śledzić ich zawartość oraz jak zapisywać dane w takim pliku.

Jako pierwszy element tej części zarejestrujemy makro, które pobierze zawartość pliku, który jest drugim z powyższych załączników. Co ważne, poniższe właściwości można stosować także dla innych źródeł danych (łatwo to sprawdzić) takich jak chociażby baza danych czy sieć internet - my jednak skupimy się na plikach tekstowych.
Włączamy rejestrator makr zostawiając domyślne ustawienia nazwy oraz jego lokalizacji:

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

W tym rozdziale zajmiemy się obsługą plików Excel z poziomu języka VBA. Nauczymy się jak tworzyć, otwierać, zapisywać dane w innym pliku niż ten, w którym zawarty jest kod programu.

W tego typu operacjach kluczowymi obiektami są obiekty nazwane Workbooks oraz ActiveWorkbook. Dzięki ich właściwościom, a przede wszystkim metodom możemy dokonywać praktycznie wszystkich operacji na zewnętrznych plikach programu Excel. W paru słowach omówimy najważniejsze z nich:

• Workbooks.Add - otwiera nowy, czysty skoroszyt (plik programu Excel).
• ActiveWorkbook.Save - zapisuje aktywny skoroszyt. Polecenie to można rozszerzyć do postaci ActiveWorkbook.SaveAs (czyli "Zapisz jako") - wówczas należy podać szereg argumentów po przecinku. Są to kolejno: FileName - ścieżka pliku do zapisania, FileFormat - format pliku (w przypadku *.xls przyjmuje wartość "xlExcel8"), Password - hasło zabezpieczające plik przed otwarciem (opcjonalnie, w cudzysłowiu), WriteResPassword - hasło zabezpieczające przed zapisem. Istnieje jeszcze kilka parametrów możliwych do skonfigurowania w tym miejscu, jednak nie będziemy się nad nimi skupiać, ze względu na fakt, iż są rzadziej używane. Zainteresowanych czytelników chciałbym odesłać do pomocy (wystarczy wpisać tam w oknie wyszukiwania: "Workbook.SaveAs Method").
• Workbooks.Open - kolejna metoda, która pozwala na otwarcie dowolnego pliku Excel. Najważniejszym parametrem jest FileName, po którym podajemy ścieżkę do plik, który chcemy otworzyć.
• ActiveWorkbook.Save - zapisuje aktywny skoroszyt.
• ActiveWindow.Close - zamyka aktywne okno.

ADODB - czyli ActiveX Data Objects DataBase, umożliwia nam dostęp do baz danych z poziomu własnoręcznie napisanej aplikacji w języku VBA. Dzięki bibliotece, która dodaje odpowiedni zestaw poleceń do naszego projektu, będziemy w stanie połączyć się z bazą danych, dodawać, odczytywać, modyfikować i usuwać rekordy w niej zawarte. Dodatkowo, jeśli korzystamy np. z baz danych MS SQL Server, możemy uruchamiać z poziomu programu VBA tzw. procedury składowane, czyli podprogramy realizujące szereg zadań, ale już po stronie bazy danych.
W kolejnych krótkich rozdziałach przedstawimy sobie najważniejsze polecenia i obiekty związane z obsługą baz danych za pomocą ADODB. Na początek jednak musimy włączyć odpowiednią referencję w naszym projekcie, aby polecenia te stały się dla nas dostępne. Lokalizacja referencji jest przedstawiona na poniższym obrazie:

Do połączenia się z bazą danych użyjemy obiektu typu ADODB.Connection. W zależności od typu bazy danych, sam łańcuch połączenia będzie przyjmował trochę inny kształt. My, w tym miejscu omówimy jedynie połączenie do bazy programu MS Access oraz MS SQL Server, co nie oznacza, że w podobny sposób nie można połączyć się z innymi typami baz przy wykorzystaniu zainstalowanego odpowiedniego sterownika.

Zacznijmy więc od definicji połączenia do bazy danych programu MS Access. Jeśli nasz plik bazy danych będzie umieszczony np. na dysku "C:" komputera i będzie miał nazwę "Northwind.mdb" (czyli będzie to baza testowa załączana z programem MS Access), to definicja będzie wyglądać następująco:

Sub access_link()

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "C:\Northwind.mdb"

'-----

'polecenia wykorzystujące bazę danych

'-----

cn.Close

End Sub

Sub access_link()

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "C:\Northwind.mdb", "admin", ""

'-----

'polecenia wykorzystujące bazę danych

'-----

cn.Close

End Sub

Sub access_link()

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB;Data Source='10.10.10.1';Initial Catalog='moja_baza';User ID='admin';Password='haslo'"

'-----

'polecenia wykorzystujące bazę danych

'-----

cn.Close

End Sub

Obiekt typu Recordset przechowuje rezultaty poleceń realizowanych na bazie danych, z którą się łączymy. Co do zasady, w języku VBA, wszystkie operacje wykonywane na bazie danych są wykonywane "online" w trakcie trwania połączenia z bazą (czyli mówiąc wprost: po użyciu metody .Open i przed .Close). Nie mamy do dyspozycji obiektów, które potrafią np. odczytać i przechować zawartość, w celu jej edycji i późniejszego zapisania z powrotem do bazy: wszelkie operacje odbywają się w czasie rzeczywistym.
Obiektu Recordset używamy najczęściej wówczas, kiedy musimy z bazy danych odczytać określone informacje.

Najczęstszym przypadkiem wykorzystania tego obiektu będzie odczyt rekordów pobranych z bazy danych w wyniku użycia instrukcji SQL typu SELECT. Załóżmy, że chcielibyśmy z serwera SQL Server z bazy danych o nazwie "dane_import" wygenerować zawartość tabeli o tej samej nazwie. Polecenie SQL miałoby taką postać:

Poprzednia część dotyczyła głównie wybierania/czytania danych z bazy. W tym miejscu, który kończy nasze rozważania na temat obsługi baz danych przy pomocy ADODB w VBA, zatrzymamy się chwilę nad poleceniami, które umożliwiają realizowanie innych operacji na bazach danych. W tym celu przypomnijmy sobie, jak wygląda tabela służąca nam za środowisko testowe: