VBA-podstawy cz. 1
1. Uwagi historyczne
Nazwa BASIC pochodzi od słów "Beginner s All-purposes Symbolic Instruction", oznacza nazwę
języka programowania, który powstał na początku lat 60-tych. Pierwotnie był to język
interpretowany. Visual Basic for Windows powstał w 1991 r.
Nazwa VBA jest skrótem od słów "Visual Basic for Applications" i oznacza język skryptowy do
pisania aplikacji. Pierwsza wersja VBA pojawiła się wraz z Excel 5. VBA jest językiem obiektowym,
nawiązującym do wymagań stawianym tego typu językom. Jego poprzednikiem jest XLM.
VBA jest wersją języka Visual Basic wchodzącą min. w skład Microsoft Excel. Zarówno VBA jak i
samodzielna wersja języka Visual Basic korzystają z identycznej składni, tych samych narzędzi
pomocniczych i takiego samego edytora.
We współpracy arkusza z VBA, arkusz udostępnia swoje możliwości za pomocą biblioteki
obiektów. Do tworzenia aplikacji VBA, zwanych makrami, można użyć nie tylko VBA dla Excela,
Worda czy Accessa, ale także samodzielnej wersji VB. Istnieje możliwość wsparcia aplikacji pisanych
w VBA (makr) za pomocą innych języków, np. C++.
PoczÄ…wszy od wersji Office 2010 skoroszyty Excel wsparte makrami VB majÄ… rozszerzenie .xlsm
2. Obiekty VBA
Excel udostępnia ponad 100 klas obiektów. Ważniejsze z nich, to:
obiekt Application  reprezentuje Excel
obiekt Workbook  reprezentuje skoroszyt
Workbooks  reprezentuje kolekcję skoroszytów
obiekt WorkSheet  reprezentuje arkusz
WorkSheets  reprezentuje kolekcjÄ™ arkuszy
ActiveSheet  reprezentuje arkusz aktywny
obiekt Range  reprezentuje zakres komórek lub komórkę pojedynczą
Selection  reprezentuje aktywny obszar arkusza
Cells  reprezentuje komórkę (nie ma obiektu Cell)
ActiveCell  reprezentuje komórkę aktywną arkusza
obiekt CommandBar  reprezentuje pasek narzędzi
CommandBars  reprezentuje wszystkie obiekty CommandBar
obiekt Chart  reprezentuje wykres
obiekt PivotTable  reprezentuje tabelÄ™ przestawnÄ…
...........
Obiekty tworzą pewną hierarchię, tj. składowymi obiektów nadrzędnych są obiekty podrzędne.
Poza obiektami składowymi obiekty mają swoje metody i właściwości. Niektóre obiekty (np.
Range, Cells) występują w roli właściwości. I tak:
Właściwościami obiektu klasy Range są Value, Name
Właściwość Offset obiektu klasy Range, zwraca przesunięty względem niego obiekt Range
MetodÄ… obiektu klasy Range jest ClearContents
Właściwościami obiektu Chart są HasTitle i Type
W odwołaniach do składowych obiektu używamy operatora "."
Na przykład, wyrażenie
Application.Workbooks ("Zeszyt1.xls") _
.WorkSheets("Arkusz1").Range("A1")
oznacza odwołanie do komórki A1 arkusza "Arkusz1" w skoroszycie "Zeszyt1.xls".
Jeżeli "Arkusz1" jest arkuszem aktywnym, odwołanie to można skrócić do
Range("A1")
Podobnie odwołujemy się do metod i właściwości:
Range("A1").Value odwołanie do właściwości Value komórki A1
Range("A1").ClearContents wywołanie metody ClearContents czyszczenia, tutaj
komórki A1
Range("A1").Select Metoda Select czyni A1 komórką aktywną (ActiveCell)
W Excelu każdy skoroszyt jest projektem! Projekt można traktować jako kolekcję obiektów
składowych. Najważniejsze z nich, to:
Microsoft Excel Objects - pojemnik wszystkich arkuszy i wykresów skoroszytu
ThisWorkBook - reprezentuje obiekt ActiveWorkBook
Modules - reprezentuje wszystkie istniejące moduły makr
Forms - zawiera obiekty UserForm, tj. formularze użytkownika.
Każdy element projektu ma okno kodu do przechowywania własnego modułu VBA. Nad
modułami możesz wykonywać operacje wstawiania, usuwania, eksportowania i importowania:
polecenie InsertÒ!Module - dodaje w zaznaczonym projekcje nowy moduÅ‚,
polecenie FileÒ!Remove xxx - usuwa zaznaczony moduÅ‚ xxx
polecenie FileÒ! Export File - eksportuje zaznaczony obiekt
polecenie FileÒ! Import File - importuje do zaznaczonego projektu
Kod modułu VBA (Procedury Sub, Function i Property oraz Deklaracje) możesz wprowadzić na
trzy sposoby:
- użyć edytora VBE i wpisać kod ręcznie, klucz ,
- zarejestrować ciąg czynności nad arkuszem, jako makro za pomocą rejestratora makr, (co
zawsze daje procedurÄ™ Sub znacznie uproszczonÄ… strukturalnie),
- skopiować z innego modułu.
3. Praca z makrami
Kilka przydatnych kluczy:
- otwieranie makr VBA
- otwieranie edytora VBA
- wykonywanie makra
- wykonywanie makra krok po kroku
Zestaw dwóch znaków: spacji i znaku podkreślenia ( _) to znak kontynuacji wiersza
Makra występują pod postacią procedur lub funkcji VBA:
Procedura VBA:
Sub nazwa()
&
End Sub
Funkcja VBA:
Function nazwaFunkcji(var As Integer) As Integer
&
nazwaFunkcji = 5
End Function
Wiersz otwarcia procedury/funkcji może mieć wiele argumentów , np.:
Sub nazwaProcedury(arg1 As Typ1,& , argN As TypN)
&
End Sub
Podanie zmiennej, jako argumentu w linii otwarcia procedury jest równoznaczne z jej
zadeklarowaniem, jako zmiennej lokalnej w danej procedurze
Jeżeli jakaś zmienna zostaje przekazana w formie argumentu do wywoływanej procedury i tam
zostanie zmieniona, to po powrocie do bazowej procedury/funkcji też będzie zmieniona!
Do wywoływania procedury/funkcji możemy użyć słowa kluczowego Call. Gdy procedura nie
posiada żadnych argumentów wejściowych piszemy:
Call wywolywanaProcedura ' OK!
wywolywanaProcedura ' OK!
Gdy procedura posiada argumenty i stosujesz Call to używaj nawiasów:
Call odliczaniePodatku(pensja, stawka) ' OK!
odliczaniePodatku pensja, stawka ' OK!
Aby sprawdzić aktualną wartość zmiennych w procedurze możesz ustawiać tzw. punkty
zatrzymania (toogle breakpoints). Procedura zawsze zawiesza swoje działanie w miejscu, w którym
ustawiony jest taki punkt.
Punktów zatrzymania nie można ustawiać w pustych wierszach oraz w wierszach, w których
znajduje siÄ™ tylko deklaracja zmiennych.
SÅ‚owo kluczowe Optional
Zdarzają się sytuacje, kiedy funkcja lub procedura wymaga podania argumentu tylko w niektórych
sytuacjach, podczas gdy w innych argument ten nie jest konieczny.
Przykład funkcji z argumentem wymaganym i argumentem opcjonalnym:
Function pensjaNetto(podstawa As Long, _
Optional kwotaWolnaOdPodatku As Single = 0) As Long
Dim kwotaDoOpodatkowania As Single
kwotaDoOpodatkowania = podstawa - kwotaWolnaOdPodatku
pensjaNetto = podstawa - (kwotaDoOpodatkowania * 0.18)
End Function
Argumenty opcjonalne mogą mieć wskazaną wartość domyślną (tutaj 0). Argument domyślny nie
może poprzedzać argumentu wymaganego. W odwołaniach do procedur/funkcji z argumentami
domyślnymi argumenty te mogą być pomijane.
Publiczne funkcje użytkownika można wybierać i uruchamiać tak, jak funkcje arkuszowe!
W nagłówkach modułów zawierających makra spotyka się opcje
Option Explicite - deklaruj wszystkie zmienne
Option Base 1 - indeksuj tablice od 1 (od jedynki, nie od 0)
4. Komunikacja z użytkownikiem
Procedury i funkcje mogą zwracać się do użytkownika po potrzebne dane za pomocą funkcji
InputBox i przekazywać mu komunikaty za pomocą funkcji MsgBox
InputBox
Funkcja InputBox służy do wprowadzania danych. Przykładowa procedura wprowadzania daty
może wyglądać następująco:
Sub WprowadzanieDaty()
Dim Data As String
Data = InputBox("Wprowadz
miesiÄ…c w formacie MMM-RRRR")
MsgBox "OK., kontynuacja"
End Sub
Gdy napisane makro uruchomimy najpierw pojawi się zaproszenie do podania daty. Możesz wtedy
wpisać np. "sty-2005" i potwierdzić przyciskiem OK. Zaraz pojawi się kolejny komunikat: "OK.,
kontynuacja" i to niezależnie od tego, czy datę wprowadzono poprawnie czy też nie. Stosunkowo
łatwo zabezpieczyć się przed wprowadzaniem daty pustej, co zrobimy w sposób następujący:
Sub WprowadzanieDaty()
Dim Data As String
Data = InputBox("Wprowadz
miesiÄ…c w formacie MMM-RRRR")
If Data <> "" Then
MsgBox "OK., kontynuacja"
End If
End Sub
Dokładniejsze sprawdzenie daty umożliwia funkcja IsDate. Kolejna modyfikacja naszej
procedury z wykorzystaniem tej funkcji może wyglądać następująco:
Sub WprowadzanieDaty()
Dim Data As String
Data = InputBox("Wprowadz
miesiÄ…c w formacie MMM-RRRR")
If IsDate(Data) Then
MsgBox "OK., kontynuacja"
Else
MsgBox "Niepoprawna data"
End If
End Sub
Przetestuj tak zdefiniowane makro. Gdybyś chciał w jakiejś aktywnej komórce wyświetlać
(poprawnÄ… bÄ…dz
niepoprawnÄ…) datÄ™ dodaj za If lub Else instrukcjÄ™
ActiveCell.Formula = Data
Dojdziesz do wniosku, że VB potrafi rozpoznać kilka formatów dat i że nie ma tu pełnej
zgodności z polskimi nazwami funkcji arkuszowych (VB posługuje się tylko językiem angielskim!)
MsgBox
Funkcję MsgBox można tak skonfigurować, że można będzie zadawać pytania i odpowiadać na
nie słowami "Tak" lub "Nie" korzystając z jej argumentów.
" Pierwszy argument Prompt jest argumentem wymaganym i zawiera tekst komunikatu, który
ma być wyświetlany.
" Drugi argument Buttons - pozwala poprzez wybór stałych dobrać tak ilość, jak i znaczenie
przycisków sterujących.
" Trzeci argument Title może zawierać tytuł okna komunikatów MsgBox().
" Do wykorzystania sÄ… jeszcze dwa inne argumenty tej funkcji.
Kolejna wersja procedury WprowadzanieDaty()wykorzysta dwa pierwsze argumenty i
pomocniczÄ… zmiennÄ… Odp typu VbMsgBoxResult:
Sub WprowadzanieDaty()
Dim Data As String
Dim Odp As VbMsgBoxResult
Data = InputBox("Wprowadz
miesiÄ…c w formacie MMM-RRRR")
If IsDate(Data) Then
MsgBox "OK., kontynuacja"
Else
Odp=MsgBox("Niepoprawna data, kontynuować?", _
vbYesNo)
If Odp = vbNo Then Exit Sub
End If
End Sub
Uwaga. W przypadku, gdy zmiennej przypisuje się wartość zwracaną przez funkcję, wszystkie
argumenty funkcji winny znalez
ć się w nawiasach. Jeżeli nie wykorzystujemy zwracanych wartości,
nawiasów nie należy używać.
5. Przykłady
Przykład 1  kod modułu, który wyświetla nazwę użytkownika
Sub Witaj()
' wyświetla nazwę u\
ytkownika
Msg = "czy nazywasz siÄ™ " & _
Application.UserName & " ?"
Odp = MsgBox(Msg, vbYesNo)
If Odp = vbNo Then
MsgBox " szkoda, \
e siÄ™ nie zgadza "
Else
MsgBox " cieszÄ™ siÄ™, \
e siÄ™ zgadza "
End If
End Sub
Przykład 2  kod modułu, który wyświetla nazwy dwóch kolejnych plików znajdujących się w folderze
aktywnym
Sub DwaPierwszeNaLiscie()
Dim Wiersz As Integer
Dim Plik As String
Wiersz = 1
Plik = Dir("*.xls") ' pierwsze u\
ycie funkcji Dir
Cells(Wiersz, 1) = Plik
Wiersz = 2
Plik = Dir ' drugie u\
ycie Dir!
Cells(Wiersz, 1) = Plik
End Sub
6. Załączniki
Strona internetowa
http://www.mielk.pl/pl/kursy/kursy.php
W załączeniu jest też skoroszyt pod nazwą
VBA_wybraneMakra_.xlsm
gdzie zestawiono i pokrótce skomentowano wybrane makra ze wskazanej strony internetowej.
H.P.