Dlaczego VBA ?

Dlaczego VBA ?

• Jest wiele języków programowania, niektóre o wiele lepsze od VBA, ale

prawie każdy z nas korzysta z programów zawartych w MS Office i
prawie każdy nie jest programistą. Czy warto uczyć się języka
programowania ? TAK !

• Ale można też inaczej. Zamiast znać po łepkach Visual C++, Delphi

czy jeszcze cokolwiek innego może lepiej poznać dokładniej narzędzie
z którego korzystamy codziennie. MS Excel używamy prawie wszyscy.
99% zadań przeciętnego pracownika mającego jako narzędzie pracy
komputer można rozwiązać przy pomocy Excela.

• Może warto poznać trochę bardziej to narzędzie, zautomatyzować

często wykonywane czynności itp. Wystarczy sięgnąć do Visual Basic
dla Aplikacji a dzięki temu możemy w łatwy sposób programować
również wykorzystując ASP, VBS itp. Narzędzie to zadowoli zarówno
początkującego jak i wytrawnego użytkownika komputera, tym
bardziej, że pojawiają się nowe programy wyposażone w moduł VBA

Projektowanie interfejsu użytkownika

Projektowanie interfejsu użytkownika

• Ważnym elementem jest interface użytkownika. Każda udostępniona

aplikacja powinna posiadać interface, który w atrakcyjny, czytelny i dobrze
zorganizowany sposób pozwoli użytkownikowi niemal intuicyjnie
posługiwać się naszym "dziełem". Poniżej przedstawiam kilka uwag, które
mogą być pomocne przy projektowaniu interface użytkownika

• Zachowaj spójny wygląd i sposób korzystania - konsekwentnie stosuj

kolory, czcionki i formatowanie.

• Staraj się nie przeładowywać okna - niech zawiera tylko niezbędne

elementy, które prowadzą do dalszych szczegółów.

• Dbaj o łatwą nawigację - odpowiedni system menu i pasków narzędzi
• Pamiętaj aby Twoje menu i paski były dostępne tylko w twojej aplikacji -

zadbaj o ich usunięcie przy zakańczaniu aplikacji

• Pamiętaj aby można było używać klawiatury jak i myszy
• Zastanów się nad kolejnością działania klawisza tabulacji (Tab)

Projektowanie interfejsu użytkownika

Projektowanie interfejsu użytkownika

• Pamiętaj, ze niektórzy użytkownicy mogą mieć problem z widzeniem

ekranu, posługiwania się myszką czy klawiaturą

• Wszędzie gdzie możliwe zamieszczaj odpowiednie wskazówki - w postaci

tipsów lub odpowiednich odwołań do pomocy

• Pamiętaj, że nic tak nie denerwuje jak "zawieszenie" aplikacji - jeżeli

występują długie operacje użyj czynników uspokajających - klepsydra na
myszy, pasek postępu itp

• Zawsze sprawdzaj czy dane wprowadzane przez użytkownika są w

dozwolonym zakresie i natychmiast reaguj na niewłaściwe dając
możliwość poprawienia np. oczekiwano liczby a użytkownik wprowadził
napis

• Żadna aplikacja nie jest doskonała - przewiduj błędy, zapewnij ich obsługę,

zadbaj o odpowiednie zwięzłe komunikaty

Pisanie kodu

Pisanie kodu

• Każde zadanie można rozwiązać na wiele sposobów, na wiele

sposobów można napisać kod aplikacji. Nie ma też takiego
kodu którego nie potrzeba poprawić, ulepszyć czy rozbudować.
Każda aplikacja z czasem zostaje rozbudowana o nowe
elementy. To tylko kilka powodów dla których po raz kolejny
przyglądamy się liniom kodu. Może kod nadaje się do
wykorzystania w innych zadaniach. Poniżej kilka praktycznych
uwag dzięki którym kod będzie kodem wielokrotnego użytku

• Staraj się pisać procedury jednozadaniowe tzn. niech procedura

wykonuje tylko jedno zadanie. Procedur będzie więcej, ale będą
bardziej uniwersalne możliwe do zastosowania w wielu
aplikacjach.

• Ustal swój sposób nazywania zmiennych, stałych obiektów itp. i

konsekwentnie się go trzymaj

• Korzystaj z dobrodziejstwa komentarza - komentarz to połowa

sukcesu gdy po np. latach będziesz musiał dokonać modyfikacji
kodu

Pisanie kodu

Pisanie kodu

• Aplikacja może mieć kilka modułów - korzystaj z tego. Niech

każdy moduł zawiera tylko te procedury, które są w jakiś
logiczny sposób ze sobą związane. Takie rozwiązanie zwiększa
też wydajność aplikacji gdyż VBA kompiluje tylko te moduły
które w danym momencie są niezbędne - im mniej
kompilowanych modułów, im ich mniejsza wielkość tym kod
jest wydajniejszy

• Twórz własną bibliotekę procedur - zamiast pisać za każdym

razem korzystaj z już wykonanej pracy odpowiednio
wprowadzając modyfikacje.

• Twórz własne obiekty korzystając z modułu klas. Z czasem

będziesz miał gotowe "czarne skrzynki" z których będziesz
składał aplikację. Nie musisz modyfikować kodu w wielu
miejscach, a tylko w jednym - module klasy

Pisanie kodu

Pisanie kodu

• Deklaruj zmienne, stałe itp. zawsze tak aby miały conajmniej

jedną wielką literę w nazwie pozwoli to uniknąć problemów z
tzw. literówkami w kodzie.

• Zawsze pisz małymi literami, jeżeli po opuszczeniu wiersza

edytor nie zamienił wielkości liter szukaj błędu - literówka,
niezadeklarowana zmienna itp.

• Nie usuwaj elementów związanych z testowaniem aplikacji a

tylko zrób z nich komentarz, który zawsze możesz wykorzystać
kasując jeden znak '

Bezpieczeństwo aplikacji

Bezpieczeństwo aplikacji

• VBA dla Excela jak i dla całego Office nie zawiera dobrych mechanizmów

bezpieczeństwa. Nie ma żadnej metody zabezpieczenia napisanego kodu.
Istnieje możliwość zablokowania kodu hasłem ale w Internecie jest cała masa
lepszych lub gorszych, amatorskich czy profesjonalnych, darmowych i płatnych
programów do wydobywania hasła. Nie każdy jednak będzie łamał
wprowadzone hasło, nie każdy jest zainteresowany jak to zrobiłeś, nie każdy
chciałby podglądnąć Twoje sztuczki i sposoby więc w 90% użytkowników hasło
jest wystarczającym zabezpieczeniem

• Pamiętaj i bądź świadomy - hasło w projekcie VBA nie jest barierą do pokonania

i wcale nie trzeba wyrafinowanych metod, wiedzy, superkomputerów itp. aby je
poznać.

• Jeżeli jednak Twój kod jest tak cenny, że powyższe  Ciebie niezadowala to:
• Rób dodatki kompilowane do postaci xll
• Twórz biblioteki dll
• Pisz i kompiluj aplikację do poziomu exe
• Ale potrzebujesz do tego specjalistycznego oprogramowania, wiedzy i

świadomości że są ludzie którzy i do takich programów potrafią się włamać.

Okno edytora VBA

Okno edytora VBA

Uruchomienie edytora daje nam dostęp do następujących narzędzi związanych z

tworzeniem kodu VBA

Znaczenie wybranych ikon:

• przejście do okna Excela
• wstaw: User Form, Moduł, Moduł Klasy, Procedurę
• Uruchom, Uruchom krokowo, Zatrzymaj

• Przełącz w tryb projektowania
• Pokaż okno Exploratora Obiektu lub F4
• Pokaż Okno właściwości

lub F2

• Pokaż okno Przeglądarki Obiektów
• Pokaż okno Przybornika

VBA - język obiektowy

VBA - język obiektowy

• Aby w pełni wykorzystać możliwości, jakie daje użytkownikowi język VBA, należy

przede wszystkim uświadomić sobie, że jest to język zorientowany obiektowo.
Oznacza to, że kod programu posługuje się obiektami, z których każdy ma
określone właściwości.

• Obiekt jest bytem kontrolowanym przez VBA. Excel posiada wiele obiektów, które

można w różny sposób kontrolować. Obiekty są zorganizowane hierarchicznie, przy
czym niektóre spośród nich zawierają w sobie inne obiekty. Na przykład Excel jest
aplikacją i zawiera w sobie takie obiekty, jak np. skoroszyty czy paski narzędzi.
Przeglądając model obiektowy Excela, widzimy że można w nim wyróżnić takie
obiekty jak Range ( Zakres). Window ( Okno), Worksheet (Arkusz roboczy), Workbook
( Skoroszyt) i Application ( Aplikacja). Jednym z najniższych w hierarchii jest Range
( Zakres).

• Zbiór podobnych obiektów stanowi kolekcję, na przykład zbiór arkuszy Excela w

skoroszycie – to kolekcja arkuszy, zbiór pasków narzędzi etc. Najczęściej stosowane
w praktyce kolekcje to Sheets ( zawierająca arkusze i wykresy , Workbook,
Worksheets i Window. Aby zróżnicować elementy kolekcji, należy podać nazwę
elementu i numer indeksu.

• Wszystkie obiekty określonego typu tworzą klasę. Zatem, aby określić odwołanie do

obiektu w VBA, należy podać całą hierarchię i dokładnie określić obiekty.

• Oprócz właściwości obiekt posiada metody. Metody to nic innego jak czynności,

które obiekt może wykonać. Każdy obiekt Excela może posługiwać się różnymi
metodami.

Struktura obiektów Microsoft Excel

Struktura obiektów Microsoft Excel

Aby przejrzeć strukturę obiektów
MS Excel należy:

• W oknie edytora VB wybrać menu

Help

• Wybrać temat Microsoft Excel

Objects.

Wyświetlone

zostanie

okno

prezentujące

strukturę obiektów.

Hierarchia obiektów Excela jest
podobna

do

drzewa

katalogów.

Najwyższy

poziom

zajmuje

pojedynczy

obiekt

Application,

reprezentujący

program Excel.

Jeśli klikniemy na rysunku strzałkę
obok

obiektu

Worksheets,

spowoduje

to

wyświetlenie

obiektów

występujących

w

arkuszach.

Zasady pisania podstawowych makr

w VBA 1/3

Zasady pisania podstawowych makr

w VBA 1/3

Przy odwołaniu się do składni należy używać składni

Object.Property

Aby odwołać się do wartości wpisanej w komórce

arkusza, należy zapisać

Range(”adres komórki”).Value

Innym przykładem może być aktywacja
wybranego skoroszytu

Workbooks.Activate

Przyjmujemy
następujące opisy:

• Object – nazwa
obiektu

• Property – nazwa
właściwości

• Value – wartość
właściwości

• Method - metoda

Znając podstawowe pojęcia dotyczące struktury modelu obiektowego Excela,
należy nauczyć się zasad wiązania jej elementów w instrukcje języka VB. Ułatwia
to nam składnia języka.

Należy zaznaczyć tu, że nazwa obiektu i właściwość oddzielone są kropką Jeśli

chcemy odwoływać się do właściwości obiektu, a ten obiekt znajduje się
wewnątrz innych obiektów, to należy wymienić po kolei wszystkie
obiekty,oddzielając ich nazwy kropkami. Np..

Workbooks(1).Worksheets(”Sheet1”).Activate

W celu zmiany wartości danej właściwości na inną stosujemy następująca

składnię:

Object.Property = Value

gdzie Value to wartość, którą zamierzamy użyć

Zasady pisania podstawowych

makr w VBA 2/3

Zasady pisania podstawowych

makr w VBA 2/3

Nowa wartość może być

• wartością numeryczną

Worksheets(”Sheet1”).Range(”A4”).Value=30

• tekstem

ActiveWorkbook.Author = ”Piotr”

• wartością logiczną

ActiveCell.Font.Bold = True

3. Aby program zwrócił bieżącą wartość ( ustawienie) danej właściwości należy

zapisać:

Variable = Object.Property

Aby zapisać, że wartość znajdująca się ( na przykład) w komórce A3 jest przechowywana w zmiennej o
nazwie

cellValue,

stosujemy zapis:

cellValue = Worksheets(”Sheet1”).Range(”A3”).Value

4. W

przypadku metod, odwołanie do metody zależy od tego, czy zawiera ona argumenty

• jeżeli metoda nie ma argumentów, zapis ma postać:

Object.Method

Na przykład, metoda Add, dodająca nowy skoroszyt, ma postać:

Workbook.Add

Przyjmujemy następujące
opisy:

•

Object

– nazwa obiektu

•

Property

– nazwa

właściwości

• Value

– wartość

właściwości

•

Method

- metoda

Zasady pisania podstawowych

makr w VBA 3/3

Zasady pisania podstawowych

makr w VBA 3/3

• W

przypadku

metod

wymagających

kilku

argumentów, składnia jest następująca:

Object.Method(

argument1,

argument2,

...,

argumentN)

Przykładem może być metoda Offset, wyświetlająca
komórkę o położeniu wyznaczonym względem
bieżącej komórki. Wymaga ona podania dwóch
argumentów indeksu wiersza (rowOffset) i kolumny
(columnOffset)

ActiveCell.Offset(3,2)

co oznacza komórkę przesuniętą o 3 wiersze w dół i
2 kolumny w prawo od komórki aktywnej. Można
zastosować również inny, bardziej czytelny zapis:

ActiveCell.Offset(rowOffset:=3,columnOffset:=2)

Przyjmujemy następujące
opisy:

•

Object

– nazwa obiektu

•

Property

– nazwa

właściwości

• Value

– wartość

właściwości

•

Method

- metoda

Typy danych

Typy danych

•

Boolean - wartość logiczna True

("Tak")
lub False ("Nie")

• Byte - od 0 do 255

• Integer - liczba całkowita z zakresu

-32768 do 32767

• Single

-

zmiennoprzecinkowy

pojedynczej precyzji

• Double

-

zmiennoprzecinkowy

pojedynczej precyzji

• Currency - Od -922 337 203 685

477,5808 to 922 337 203 685
477,5807

• Decimal

• Date - od 1 stycznia 100r. do 31

grudnia 9999r.

• Object - dowolne odesłanie do

wartości typu Object

•

String (zmiennej długości)  - od 0 do

około 2 miliardów.

• String (ustalonej długości)  - od 1 do

około 65 400 znaków (64 kB)

• Variant (z liczbami)  - każda wartość

numeryczna w zakresie typu Double

• Variant (ze znakami) - taki sam zakres

co dla zmiennej typu String zmiennej
długości

• Definiowany przez użytkownika (przy

użyciu Type) 

• Tablice - 20 bajtów pamięci + 4 bajty na

każdy wymiar tablicy + liczba bajtów
potrzebnych na jej dane (każdy wymiar *
liczba bajtów dla danego typu danych w
wymiarze)

Stałe

Stałe

• Stałe - sa to wartości zadeklarowane na stałe, i nie zmieniające się w

programie. Przykładem stałej jest liczba pi = 3,1416...Liczba ta nie ulega
zmianie i można ja zdeklarować jako stałą.

• Dobrym nawykiem jest deklarowanie stałych dla wartości, która się nie zmienia.

zapobiega to przypadkowym zmianom tej wartości. Stałej przypisujemy nazwę
dzięki której mamy dostęp do niej np. zamiast za każdym razem pisać 3,1416
możemy zadeklarować stałą o nazwie pi której przypiszemy wartość 3,1416
( lub dowolną inną wg naszego uznania). Przypisanie stałej następuje w
następujący sposób:( na przykładzie definiowania stałej pi) Const pi = 3,1416

• Natomiast użycie stałej wygląda następująco ( na przykładzie obwodu koła)

ObwódKoła = 2 * pi * Promień

• Nazwy stałych podlegają tym samum zasadom, które stosujemy do nazywania

zmiennych.

1.    

Przeglądanie zdefiniowanych stałych

Przeglądanie zdefiniowanych stałych

           Przejdź do edytora Visual Basic Narzedzia/ Makro/ Edytor Visual Basic

lub Alt - F11/

1.           Wbierz F2, lub Widok - Przeglądarka Obiektów

2.           W spisie Bibliotek wybierz Excel

3.           W oknie Klasy Przeglądarki obiektów znajdź Constans

4.           W oknie Składowa 'Constans' poszukaj nazwy odpowiedniej stałej

5.           Zaznaczając nazwę stałej w dolnej części Przeglądarki obiektów

otrzymasz informacje o tej stałej. Zauważ, że wszystkie stałe programu Excel
zaczynają się od liter xl   

Zmienne

Zmienne

•

Zmienna jest to symboliczna nazwa wskazująca na pewien obszar pamięci.

O wiele łatwiej panować nad pisaniem programu jeżeli używamy nazw,
które w jakiś sposób kojarzą się ze zmienną przechowywaną w danym
zakresie pamięci niż posługiwać się nic nie mówiącymi adresami komórek
(zakresów) pamięci komputera. Najlepiej używać prostych, krótkich nazw
dla zmiennych. Wyrazy w wielowyrazowych  nazwach   zmiennych
rozdzielać znakiem _ lub każdy wyraz zaczynać z dużej litery. Pamiętać
należy o zasadach obowiązujących przy nadawaniu nazw zmiennym.
Sposób deklaracji zmiennych:     Dim zmienna As typ gdzie zmienna jest
nazwą zmiennej, a typ określa typ danych przechowywany przez zmienną

• Uwaga: Pominięcie słowa kluczowego As i typu zmiennej spowoduje, że

zmienna będzie typu Variant ( ze względów łatwiejszego diagnozowania
błędów w pisanych programach należy unikać tego typu deklaracji
zmiennej ).

• Przykłady deklaracji zmiennych:                 

                

  Dim nazwaPliku As String

                                  Dim liczbaPi As Double

Zasięg stałych i zmiennych

Zasięg stałych i zmiennych

Zasięg stałych i zmiennych ściśle związany jest ze sposobem ich
deklaracji. Zmienna zdeklarowana w procedurze jest lokalna dla tej
procedury. Oznacz to że w innych procedurach może istnieć zmienna o
takiej samej nazwie ale przechowująca odmienne wartości. Aby zmienna
była dostępna dla innych procedur należy zdeklarować ją na poziomie
modułu na początku przed jakąkolwiek funkcją lub procedurą. Zmienne
mogą być definiowane automatycznie ( w momencie pierwszego użycia
nazwy) lub możemy wymusić konieczność deklaracji zmiennych.

Wskazane jest deklarowanie zmiennych
bowiem dzięki temu unikniemy wielu
problemów w błędach nazw zmiennych.
Wyłączenie automatycznego deklarowanie
zmiennych

następuje

poprzez

umieszczenie w Module instrukcji: Option
Explicit.
Można

ustawić

tą

właściwość

automatycznie korzystając z Narzędzia /
Opcje

/

Edytor

i

zaznaczyć

pole

"Wymagane

deklaracje

zmiennych":

Całość

wykonujemy

z

poziomu

Edytora VBA

Zasady nazywania zmiennych i

stałych

Zasady nazywania zmiennych i

stałych

•

Zasady nazywania zmiennych i stałych:

• Nazwy składają się z liter, cyfr i niektórych znaków przystankowych.

• Nazwa musi zaczynać się od litery.

• Litery duże i małe nie są rozróżniane, ale ich wielkość jest zachowywana.

• Nie należy stosować w nazwie spacji, kropek i następujących znaków

!, #, $, %, &.

• Maksymalna liczba znaków w nazwie nie powinna przekroczyć 255.

• Nie można powtarzać nazw w ramach tego samego zakresu ( zasięgu zmiennej lub

stałej).

• Nazwa nie może być tzw. słowem zarezerwowanym w Visual Basic dla aplikacji

(użycie powoduje generację błędu)

• Przykłady nazw: moje_nazwisko; MojeNazwisko; KolorNiebieskiDlaTła\

kolor_niebieski_dla_tła

• Znak "_" użyty do rozdzielenia dwóch wyrazów powoduje większą czytelność

nazwy .Innym sposobem zwiększenia czytelności nazwy jest zastosowanie dużych
liter na początku wyrazu w nazwie (przykład 2 i 3).

• Nie należy stosować zbyt długich nazw gdyż tylko zaciemniają kod programu oraz

pomyśl ile czasu i pomyłek jest potrzebnych do wprowadzenia długiej nazwy.

Tworzenie procedury

Tworzenie procedury

• Rozpoczęcie pracy nad nową procedurą

(makrodefinicją), bez względu na jej typ,
wymaga

wybrania

polecenia Insert / Procedure, a następnie
wskazania odpowiednich opcji w oknie
dialogowy Add Procedure.

• W zależności od naszych planów

wpisujemy nazwę procedury
w polu Name , a następnie w obszarze
Type wybieramy jedną
z trzech dostępnych opcji:

• Sub - dla podprogramu
• Function - dla funkcji
• Property – opcja używana w przypadku

własnych projektów w modułach klas

Wprowadzenie tekstu do tytułu

i linii statusu arkusza

Wprowadzenie tekstu do tytułu

i linii statusu arkusza

Tworząc aplikację na potrzeby konkretnego użytkownika, warto zadbać o
zmianę „znaczących drobiazgów” opisujących arkusz

Ćwiczenie:

• W oknie edytora VB wybierz Insert / Procedure

(przy otwartym oknie z kodem). Zdefiniuj nazwę
procedury „tytul”

• Zamiast „Microsoft Excel” wstaw np. Moja aplikacja”

            Application.Caption=”Moja aplikacja”

• Ponownie wybierz Insert / Procedure

Zdefiniuj nazwę procedury „status” i wpisz

Application.StatusBar = "Wersja 1.0”

• Przetestuj działanie wybierając polecenie

Run Sub / User Form lub pop prostu naciskając [F5]

• Powinieneś otrzymać następujące efekty.

Tworzenie ekranu powitalnego

Tworzenie ekranu powitalnego

W ramach tego ćwiczenia tworzymy właściwie
swój pierwszy formularz, który nie co prawda
służyć do wpisywania danych lecz do „wywołania
pierwszego miłego wrażenia”

Ćwiczenie:

• W oknie edytora VB wstaw formularz wybierz

Insert / Procedure

•Dokonaj aranżacji plastycznej formy. Koniecznie

wstaw
CommandButton - OK. Aby uzyskać to musisz
mieć odpowiednio
ustawione właściwości dla: Formy, Przycisku OK, 
Napisu „Moja..."
i danych autora Przykładowe zagospodarowanie
formy:

•Kliknij na przycisk OK i dopisz kod

Tworzenie ekranu powitalnego

Tworzenie ekranu powitalnego

Ćwiczenie c.d.:

• Przejdź do ThisWorkbook i dopisz kod

aby uzyskać to co na rysunku

• Zapisz plik excela. Przy każdym otwarciu

pliku otrzymasz okienko powitalne
na tle arkusza.  

Kod zaprezentowany nie jest kodem optymalnym, bowiem w pamięci
operacyjnej istnieje UserForm i niepotrzebnie zajmuje miejsce. Aby to miejsce
zwolnić należy wykonać metodę Unload dla opisanej formy. Zostanie to
pokazane w następnym ćwiczeniu.

Tworzenie formularza użytkownika –

definiowanie formy

Tworzenie formularza użytkownika –

definiowanie formy

Przedstawione w przykładzie formularz pobiera od użytkownika dwie
informacje: imię i płeć. Do pobrania nazwiska używane jest pole tekstowe, a do
ustalenia płci trzy przyciski. Informacje zebrane w oknie dialogowym są
następnie zapisywane w pustym wierszu arkusza.

Ćwiczenie :

• Utwórz skoroszyt z jednym tylko arkuszem
• Uaktywnij Edytor VB ( [Alt]+[F11])
• Dodaj pusty formularz użytkownika Insert / User Form.
• Jeśli nie widać okna Properties, naciśnij klawisz [F4]
• Używając okna Properties zmień właściwość Caption

formularza użytkownika na Podaj imię i płeć

• Dodaj formant Label (Etykieta ) i następująco

dostosuj jego właściwości

• Dodaj formant TextBox (Pole tekstowe) i następująco

dostosuj jego właściwości

WŁAŚCIWOŚĆ

WARTOŚĆ

Accelerator

I

Caption

Imię:

TabIndex

0

WŁAŚCIWOŚĆ

WARTOŚĆ

Name

TekstImię

TabIndex

1

Tworzenie formularza użytkownika –

- określanie właściwości formantów

1/2

Tworzenie formularza użytkownika –

- określanie właściwości formantów

1/2

Ćwiczenie c.d :

• Dodaj formant Frame (Pole

grupy) i następująco
dostosuj jego właściwości

• Na obszarze Frame dodaj

formant OptionButton (Przycisk
Opcji)
i następująco dostosuj jego
właściwości.

• Na obszarze Frame dodaj

formant OptionButton (Przycisk
Opcji)
i następująco dostosuj jego
właściwości

• Na obszarze Frame dodaj

formant OptionButton (Przycisk
Opcji)
i następująco dostosuj jego
właściwości

WŁAŚCIWOŚ

Ć

WARTOŚĆ

Caption

Płeć

TabIndex

2

WŁAŚCIWOŚĆ

WARTOŚĆ

Accelerator

M

Caption

Mężczyzna

Name

OpcjaMężczyzn

a

TabIndex

0

WŁAŚCIWOŚĆ

WARTOŚĆ

Accelerator

M

Caption

Mężczyzna

Name

OpcjaMężczyzn

a

TabIndex

0

WŁAŚCIWOŚĆ

WARTOŚĆ

Accelerator

K

Caption

Kobieta

Name

OpcjaKobieta

TabIndex

1

WŁAŚCIWOŚĆ

WARTOŚĆ

Accelerator

N

Caption

Nieznana

Name

OpcjaNieznana

TabIndex

2

Value

True

Tworzenie formularza użytkownika –

- określanie właściwości formantów

2/2

Tworzenie formularza użytkownika –

- określanie właściwości formantów

2/2

Ćwiczenie c.d :

• Dodaj formant CommandButton ( Przycisk

Polecenia) i następująco
dostosuj jego właściwości.

• Dodaj formant CommandButton ( Przycisk

Polecenia) i następująco
dostosuj jego właściwości.

• Rozstaw formanty mniej więcej tak

WŁAŚCIWOŚĆ

WARTOŚĆ

Caption

OK

Default

True

Name

PrzyciskOK

TabIndex

3

WŁAŚCIWOŚĆ

WARTOŚĆ

Caption

Anuluj

Cancel

True

Name

PrzyciskAnuluj

TabIndex

4

Tworzenie formularza użytkownika –

- dodawanie procedur obsługi

zdarzeń

Tworzenie formularza użytkownika –

- dodawanie procedur obsługi

zdarzeń

Ćwiczenie c.d :

• Kliknij dwukrotnie przycisk Anuluj. W odpowiedzi pojawi się okno Code formularza z pustą

procedurą o nazwie PrzyciskAnuluj_Click().

• Doprowadź tę procedurę do postaci:

Rezultatem wykonanie tej procedury jest po prostu usunięcie okna dialogowego.

• Naciśnij kombinację klawiszy [Shift] + [F7], aby ponownie wyświetlić formularz UserForm1

Kliknij dwukrotnie przycisk OK. i wprowadź podstawową procedurę.

• Po pierwsze uaktywnia ona na wszelki wypadek Arkusz1.

• Następnie używając excelowej funkcji COUNTA, znajduje pierwszą wolną komórkę w

kolumnie A. Kolumna A jest reprezentowana przez liczbę 1 w drugim parametrze kolekcji
Cells.

• Dalej procedura przenosi tekst z pola tekstowego do kolumny A i używając kilku instrukcji

If sprawdza, który przycisk został wybrany, po czym zapisuj odpowiedni tekst w kolumnie
B

• Na koniec wartości wszystkich formantów są resetowane w celu przygotowania okna

dialogowego do kolejnego wpisu.

Tworzenie formularza użytkownika –

- dodawanie procedur obsługi

zdarzeń

Tworzenie formularza użytkownika –

- dodawanie procedur obsługi

zdarzeń

Private Sub PrzyciskOK_Click()

' Uaktywnienie na wszelki wypadek Arkusza1

Sheets("Arkusz1").Activate

' Znalezienie następnego pustego wiersza

NastWiersz = Application.WorksheetFunction.CountA(Range("A:A")) + 1

'Przeniesienie imienia

Cells(NastWiersz, 1) = TekstImię.Text

'Przeniesienie informacji o płci

If OpcjaMężczyzna Then Cells(NastWiersz, 2) = "Mężczyzna"

If OpcjaKobieta Then Cells(NastWiersz, 2) = "Kobieta"

If OpcjaNieznana Then Cells(NastWiersz, 2) = "Płeć nieznana"

'Przywrócenie formantów do oryginalnej postaci

TekstImię.Text = ""

OpcjaNieznana = True

TekstImię.SetFocus

End Sub

Ćwiczenie c.d
:

Kod

procedury

Tworzenie formularza użytkownika –

- sprawdzanie poprawności danych

Tworzenie formularza użytkownika –

- sprawdzanie poprawności danych

Ćwiczenie c.d :

• Jeśli wykonasz kilka prób z pokazanym oknem dialogowym, przekonasz się, że

ma ono jeden mankament – nie gwarantuje wprowadzenia przez użytkownika
imienia do pola tekstowego. Aby uniknąć tej sytuacji, musisz dodać fragment
kodu, który spowoduje w razie braku imienia ( a przynajmniej jakiegoś tekstu) ,
pojawienie się pola komunikatu.

'Sprawdzenie, czy zostało wprowadzone imię

If TekstImię.Text = "" Then

MsgBox "Musisz wpisać imię !"

Exit Sub

End If

• Sprawdź, czy faktycznie sygnalizowane jest błąd wpisu.

Tworzenie formularza użytkownika –

- pisanie kodu wyświetlającego okno

Tworzenie formularza użytkownika –

- pisanie kodu wyświetlającego okno

Ćwiczenie c.d :

• Uaktywnij Excela
• Kliknij prawym przyciskiem myszy dowolny pasek narzędzi i wybierz

z menu podręcznego Przybornik formantów.

• Używając przybornika umieść w arkuszu przycisk polecenia. Zmień tytuł klikając

go prawym
przyciskiem myszy i wybierając Obiekt CommandButton / Edit.

• Uaktywnij VBA, klikając dwukrotnie dodany przycisk i wpisz

Ta krótka instrukcja wyświetla okno dialogowe za pomocą metody Show obiektu

UserForm.

Po dokonaniu ostatniej modyfikacji okno dialogowe powinno już łatwo się
pojawiać i działać poprawnie. Oczywiście w naturalnych warunkach trzeba
zbierać więcej informacji, niż za pomoca tego okna. Ogólne zasady pozostają
jednak zawsze takie same. Co najwyżej trzeba uwzględnić więcej formantów.

Ustawianie czcionek

Ustawianie czcionek

• Ustawia podkreślenie w aktywnej komórce

ActiveCell.Font.Underline =

xlUnderlineStyleSingle

• Ustawia podkreślenie w  komórce A2

Range("A2").Font.Underline =

xlUnderlineStyleDouble

• Usuwa podkreślenie w aktywnej komórce

ActiveCell.Font.Underline =

xlUnderlineStyleNone

• Ustawia kursywę w aktywnej komórce

ActiveCell.Font.Italic = True

• Ustawia czcionkę Arial CE  w aktywnej komórce

ActiveCell.Font.Name = "Arial CE"

• Ustawia wielkość czcionki w aktywnej komórce

ActiveCell.Font.Size =   10

• Ustawia indeks górny czcionki  w aktywnej komórce

ActiveCell.Font.Superscript =

True

• Ustawia kolor  czcionki  w aktywnej komórce

ActiveCell.Font.ColorIndex = 45

TABELA INDEKSÓW DLA

POSZCZEGÓLNYCH KOLORÓW

Ustawianie ramek 1/2

Ustawianie ramek 1/2

 Ustawia ramkę dla komórki A1

 

Range("A1")Borders(

KtóraLinia

).LineSt

yle =

TypLinii

• xlEdgeBottom

 

dolna

• xlEdgeTop

 

górna

• xlEdgeRight

 

prawa

• xlEdgeLeft

 

lewa

• xlDiagonalDown

 

przekątna w dół

• xlDiagonalUp

 

przekątna w górę

• xlInsideHorizontal

 

wewnętrzne

poziome

• xlInsideVertical

 

wewnętrzne pionowe

Która Linia 

 

xlContinuous

 

ciągła

xlDash

 

przerywana

xlDashDot

 

przerywano-kropkowa

xlDashDotDot

 

przerywano-

dwukropkowa

XlDot

 

kropkowa

xlDouble

 

podwójna

xlSlantDashDot

 

ukośnie-przerywano-

kropkowa

xlLineStyleNone

 

bez linii

Typ linii 

Ustawianie ramek 2/2

Ustawianie ramek 2/2

Ustawia kolor ramki  w aktywnej komórce

 

ActiveCell.Borders(xlDiagonalDown).Color= RGB(0,0,255)

powoduje ustawienie koloru wg tablicy RGB

ActiveCell.Borders(xlDiagonalDown).ColorIndex=4

•

Uwaga: Ustawienie właściwości ColorIndex= xlAutomatic

powoduje, ustawienie koloru domyślnego [typowo czarny (ColorIndex=1) o
ile nie dokonano zmian w konfiguracji]

• Ustawia grubość  ramki  w aktywnej komórce

           

ActiveCell.Borders(xlDiagonalDown).Weight=xlMedium

                         gdzie:   

xlThin      - 

linia cieńka

                                     xlThick     - 

linia gruba

   

                                  xlMedium -

linia średniej grubości

Definiowanie wypełnień

Definiowanie wypełnień

Obiekt Interior może być użyty do ustawienia koloru oraz wzorca wypełnienia dla
komórki lub zakresu komórek

Ustawia wypełnienie dla komórki A1

 

Range("A1").Interior.Pattern =

wzór

      

Uwaga: 
Ustawienie właściwości 

Pattern= xlNone

 powoduje wykasowanie wypełnienia 

Ustawia kolor w aktywnej komórce

ActiveCell.Interior.Color= RGB(0,0,255) p

owoduje ustawienie koloru

wg tablicy RGB

ActiveCell.Interior.ColorIndex=4

Uwaga: Ustawienie właściwości

ColorIndex= xlAutomatic

powoduje, ustawienie koloru

domyślnego [typowo czarny

(ColorIndex=1)

 o ile nie dokonano zmian w konfiguracji]

Ustawia kolor wypełnienia - Pattern w aktywnej komórce

          ActiveCell.Interior.PatternColor= RGB(0,0,255)

powoduje ustawienie koloru wg

tablicy RGB

        

ActiveCell.Interior.PatternColorIndex=4

Zliczanie elementów arkusza

Zliczanie elementów arkusza

• Liczbę wszystkich komórek w arkuszu

zmienna  = Cells.Count

• Liczbę wszystkich wierszy w arkuszu

zmienna  = Rows.Count

• Liczbę wszystkich kolumn w arkuszu

zmienna  = Columns.Count

• Liczbę otwartych skoroszytów

zmienna  = Workbooks.Count

• Liczbę arkuszy w skoroszycie

zmienna  = Worksheets.Count

• Liczbę Modułów w skoroszycie

zmienna  = Modules.Count

• Liczbę wszystkich kart w skoroszycie

zmienna  = Sheets.Count

• Liczbę arkuszy typu Wykres w skoroszycie

zmienna  = Charts.Count

Wstawianie danych do komórek

Wstawianie danych do komórek

• Własnego tekstu w komórce

(na przykładzie A1 10 różnych sposobów)

            Range("A1").Formula = "mój
tekst"
            Cells(1).Formula ="mój tekst"
            Cells.Item(1).Formula ="mój
tekst"
            Cels(1,1).Formula ="mój tekst"
            Cells(1,"A").Formula ="mój tekst"
            Range("A1").Value = "mój tekst"
            Cells(1).Value ="mój tekst"
            Cells.Item(1).Value ="mój tekst"
            Cels(1,1).Value ="mój tekst"
           Cells(1,"A").Value ="mój tekst„

• L

iczby

            Range("A1").Formula = 13
            Cells(1).Formula =13
            Cells.Item(1).Formula =13
            Cels(1,1).Formula =13
            Cells(1,"A").Formula =13
            Range("A1").Value = 13
            Cells(1).Value =13
            Cells.Item(1).Value =13
            Cels(1,1).Value =13
            Cells(1,"A").Value =13

 

•   Formuły =B1+C4 do komórki A1

            (5 różnych sposobów)

            Range("A1").Formula =
"=B1+C4"
            Cells(1).Formula = "=B1+C4"
            Cells.Item(1).Formula =
"=B1+C4"
            Cels(1,1).Formula = "=B1+C4"
            Cells(1,"A").Formula = "=B1+C4"

• Formatu komórki w postaci np. ##.##0

         Range("A1").NumberFormat =
"##.##0"

Jak wybrać ? 1/2

Jak wybrać ? 1/2

• pojedyńczą komórkę (na przykładzie A1 - 5 różnych

sposobów)

            Range("A1").Select
            Cells(1).Select
            Cells.Item(1).Select
            Cels(1,1).Select
            Cells(1,"A").Select

• zakres komórek   (2 różne sposoby)

            Range("A1:A5").Select
            Range(Cells(1,1),Cells(5,1)).Select

• kilka komórek (różnych)

            Range("A1","A5","Z320").Select

• kilka zakresów komórek (różnych)

            Range("A1:A5","A15:D15","Z320").Select

• komórkę aktywną

            Application.ActiveCell

• komórkę przesuniętą o 2 wiersze i 1 kolumnę

od komórki aktywnej

            Activecell.Offset(2,1).Activate

• komórkę przesuniętą o 2 wiersze i 1 kolumnę od komórki

A2
                 (2 różne sposoby)

            Range("A2").Offset(2, 1).Activate
            Cells(2, 1).Offset(2, 1).Activate

• pierwszy arkusz w aktywnym zeszycie

            Sheets(1).Activate

• .arkusz o nazwie "Arkusz1"

            Sheets("Arkusz1").Activate

• spośród otwartych zeszytów zeszyt o nazwie "Zeszyt1"

            Windows("Zeszyt1").Activate

• wiersz z aktywną komórką  (2 różne sposoby)

            Selection.EntireRow.Select
            ActiveCell.EntireRow.Select

• kolumnę z aktywną komórką (2 różne sposoby)

            Selection.EntireColumn.Select
            ActiveCell.EntireColumn.Select

• kilka kolumn w ciągłym obszarze

            Columns("A:C").Select

Jak wybrać ? 2/2

Jak wybrać ? 2/2

• kilka wierszy w ciągłym obszarze

            Rows("1:5").Select

• kilka kolumn w nieciągłym obszarze

            Columns("A:C","E:E").Select

• kilka wierszy w nieciągłym obszarze

            Rows("1:5","6:6").Select

• ostatnią komórke w danym wierszu

            ActiveCell.End(xlRight).Select

• ostatnią komórke w danej kolumnie

            ActiveCell.End(xlDown).Select

• pierwszą komórke w danym wierszu

            ActiveCell.End(xlLeft).Select

• pierwszą komórke w danej kolumnie

            ActiveCell.End(xlUp).Select

Jak wyświetlić ?

Jak wyświetlić ?

• zawartość komórki  (na przykładzie A1)      (5 różnych

sposobów)

            Range("A1").Value 
            Cells(1).Value 
            Cells.Item(1).Value 
            Cels(1,1).Value 
            Cells(1,"A").Value 

• formułę z  komórki  (na przykładzie A1)      (5 różnych

sposobów)

            Range("A1").Formula 
            Cells(1).Formula 
            Cells.Item(1).Formula 
            Cels(1,1).Formula 
            Cells(1,"A").Formula 

• zatrzymać wyświetlanie okien informacyjnych

            Application.DisplayAlerts = False

• uaktualnić wyświetlanie okien informacyjnych

            Application.DisplayAlerts = True

Jak to zrobić ?

Jak to zrobić ?

• Zatrzymać aktualizację zawartości ekranu

Application.ScreenUpdating = False           

• Wznowić aktualizację zawartości ekranu

Application.ScreenUpdating = True           

• Wyczyścić zawartość schowka po operacji Copy/Paste

Application.CutCopyMode = False           

• Przywrócić Excelowi prawo do pisania w pasku statusu

Application.StatusBar = False

• Zmienić sposób przeliczania formuł w arkuszu

Application.Calculation = xlManual    

'przeliczenie ręczne po np przyciśnięciu

przyciski F9

Application.Calculation = xlAutomatic    

'przeliczenie automatyczna

• Wywołać otwarcie strony www z poziomu VBA - przykład w formie pliku

xls

Sterowanie wykonywaniem instrukcji

Sterowanie wykonywaniem instrukcji

VBA udostępnia zestawione w poniższej tabeli metody sterowania
wykonywaniem instrukcji programu (np. Poprzez tworzenie pętli ).
Służą do tego instrukcje sterujące.

Typ sterowania

Instrukcja VB

Powtarzanie (pętle)

Do ... Loop

While ... Wend

For ... Next
For ... Each

Podejmowanie decyzji

If ... Then ... Else

Select Case ... End Select

Przerywanie i

zatrzymywanie

DoEvents

Exit
End

Stop

Przejścia i skoki

Call

Go Sub ... Return

GoTo

Przykład podejmowania decyzji - instrukcje If

...then

Przykład podejmowania decyzji - instrukcje If

...then

• Instrukcja sterująca If.....Then oraz If.....Then ... Else umożliwiają wykonanie określonych

instrukcji w zależności od prawdziwości warunku. Postać instrukcji jest następująca

If warunek Then instrukcja1 [ Else instrukcja2 ]

Używana jest również postać blokowa instrukcji pozwalająca na uruchomienie większej ilości
linii kodu, przy czym kod jest czytelniejszy

If warunek1 Then

wybór1: jedna lub więcej instrukcji VB

[ ElseIf warunek2

wybór2: jedna lub więcej instrukcji VB

]

[ Else

wybór3: jedna lub więcej instrukcji VB ]

End If

Działanie instrukcji

If.....Then ... Else jest następujące.

• VB sprawdza wartość pierwszego warunku. Jeżeli wartość jest różna od zera, wykonywane są

kolejne instrukcje kodu.

• Jeżeli wartość jest równa zero, VB przenosi sterowanie do ElseIf i sprawdza wartość drugiego

warunku. Jak poprzednio, jeżeli wartość warunku jest różna od zera, wykonywane są instrukcje
następujące po ElseIf .

• W przeciwnym wypadku sterowanie przechodzi do Else i wykonywane są znajdujące się tam

instrukcje.

Co zrobić jak zapomnimy hasła chroniącego

arkusz ? 1/2

Co zrobić jak zapomnimy hasła chroniącego

arkusz ? 1/2

• Procedura znajduje hasło i odbezpiecza chroniony arkusz w skoroszycie. Należy ją skopiować do

skoroszytu w którym sa chronione arkusze i uruchomić z poziomu chronionego arkusza

Sub PasswordBreaker()

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _

Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Co zrobić jak zapomnimy hasła chroniącego

arkusz ? 2/2

Co zrobić jak zapomnimy hasła chroniącego

arkusz ? 2/2

Dokończenie procedury ...

If ActiveSheet.ProtectContents = False Then

MsgBox "One usable password is " & Chr(i) & Chr(j) & _ Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

ActiveWorkbook.Sheets(1).Select

Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _ Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) &

Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

End Sub

Jak przyśpieszyć makro? 1/3

Jak przyśpieszyć makro? 1/3

W celu przyspieszenia wykonywania kodu VBA warto zastosować:

1. Dobrać odpowiednio typy zmiennych i zawsze je zadeklarować.Najlepiej użyć:

Option Explicit

ustawionego

na stałe

 

2. Używać adresowania w postaci W1K1 ( np. A1B1)

3. Zatrzymać aktualizację zawartości ekranu

Application.ScreenUpdating = False           

 

4. Zatrzymać przeliczanie arkusza 

    

Application.

Calculation = xlManual    

'przeliczenie ręczne po np przyciśnięciu przyciski F9

5. Zatrzymać  obsługę zdarzeń     

Application.EnableEvents = False 

6. Używać With...End With przy obiektach

    

With Range("A1") 

                   

      .Value = 100 

                      

   .Font.Bold = True 

      

End With

7.   Używać VbNullString zamiast ""

       PustyText= VbNullString

Jak przyśpieszyć makro? 2/3

Jak przyśpieszyć makro? 2/3

9. Używać instrukcji przypisania zamiast Copy

zamiast:

      

Sheet1.Range("A1:A200").Copy 

       Sheet1.Range("A1:A200").PasteSpecial xlPasteValues 

stosuj: 

      

Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value

10.

Zwalniać nieużywane przypisania używając Nothing

deklaracja:

    

Dim mojObiekt as Worksheet

     Set mojObiekt = Sheet1

zwolnienie zarezerwowanej pamięci

      

Set mojObiekt = Nothing

  11.Upraszczać warunki w if..else..end if

zamiast
    

If i = 5 Then 

             

wynik = True 

        

Else 

            

wynik = False 

      

End If

stosuj
    

Dim wynik As Boolean 

    

Dim i As Integer

 

    

wynik = (i = 25)

 

Jak przyśpieszyć makro? 3/3

Jak przyśpieszyć makro? 3/3

12. Używać Not  zamiast przypisania

zamiast
    

If wynik <> True  Then 

             

wynik = True 

      

End If

stosuj
    

Dim wynik As Boolean 

     

wynik

=

Not

wynik 

13. Unikać pętli.

14. Unikać rozbudowanych instrukcji Select case

Źródła materiałów

Źródła materiałów

•

http://www.vba.matrix.pl/

• „Excel w praktyce” - B.Zieliński Wyd. Translator s.c.
• „Programowanie Excel 2000” John Walkenbach RM 2000