Microsoft Excel - przykłady programowania w VBA

Biurowe aplikacje Miscrosoft Office (Word, Excel, Access, Frontpage, Outlook, PowerPoint) są wyposażone w język programowania Visal BASIC for Aplications, w skrócie VBA. Nazwa BASIC pochodzi od angielskich słów:

 

Beginner's All-purpose Symbolic Instruction Code
Symboliczny kod instrukcji ogólnego przeznaczenia dla początkujących

 

Język BASIC powstał w latach 60-tych ubiegłego wieku. Zgodnie z nazwą, przeznaczony był od samego początku dla niefachowców. Zasady programowania w języku BASIC są takie, iż może je opanować każdy użytkownik komputera - wcale nie musi być informatykiem, wystarczy, że potrafi logicznie myśleć (wiem, to jest dla niektórych przeszkoda nie do pokonania).

Na lekcjach informatyki w liceum nie ma wystarczającej liczby godzin, aby nauczyć kogokolwiek wszystkich zasad programowania w tym języku. Dlatego pokażemy tylko jego najprostsze zastosowania, nie wgłębiając się w szczegóły. Jeśli kogoś zainteresuje ten temat, może samodzielnie zdobyć potrzebną wiedzę (polecam szczególnie pozycje z cyklu "... dla opornych") - wymaga to jednak dziesiątek (a nawet setek) godzin pracy z komputerem. Ja posiadam do języka BASIC pewien szczególny sentyment - to na nim w latach 80-tych nauczyłem się programowania komputerów. Wbrew obiegowym opiniom dzisiejszy język BASIC jest w pełni funkcjonalnym narzędziem programowania komputerów, posiadając wszelkie niezbędne ku temu struktury danych oraz konstrukcje programowe. Opanowanie VBA pozwoli użytkownikowi zrobić dosłownie wszystko z każdą aplikacją MS-Office.

 

Przygotowanie środowiska programowania w VBA

Uruchom MS-Excel. Z menu wybierz opcję Narzędzia / Makro / Edytor Visual Basic. Otworzy się środowisko programowania w VBA, które jest jakby osobną aplikacją, współpracującą ściśle z MS-Excel.

 

 

Aby rozpocząć programowanie na potrzeby dzisiejszej lekcji, musimy do projektu VBA wstawić nowy moduł. Moduł VBA będzie zawierał funkcje, które użytkownik może wykorzystać w swoich arkuszach kalkulacyjnych. W tym celu z menu wybierz opcję Insert / Module. W oknie pojawi się edytor tekstu programu, a do projektu VBA zostanie dołączony moduł o nazwie Module1:

 

 

W edytorze zapisujemy treści funkcji i procedur. Funkcja jest fragmentem programu, który może wykonać obliczenia i zwrócić wartość. Procedura wykonuje najczęściej jakieś operacje, lecz nie zwraca wartości. Dlatego w programach funkcje wykorzystuje się do wykonania obliczeń, a procedury do wykonania określonych działań. W języku VBA funkcję (procedurami tutaj nie będziemy się zajmowali) tworzymy wg schematu:

 

function nazwa_funkcji(lista parametrów) as typ

  ...

end function

 

Lista parametrów to dane, które funkcja otrzymuje od wywołującego ją programu. Ma ona następującą postać:

 

nazwa_parametru as typ, nazwa_parametru as typ ...

 

Typ określa rodzaj informacji, którą funkcja otrzymuje w parametrze lub którą funkcja zwraca jako swój wynik. Oto niektóre typy danych języka VBA:

 

Boolean  - typ logiczny, przyjmuje tylko dwie wartości: True dla prawdy i False dla fałszu
Integer  - typ całkowity ze znakiem, 16 bitowy, zakres od -32768 do 32767
Long  - typ całkowity ze znakiem, 32 bitowy, zakres od -2147483648 do 2147483647
Double  - typ zmiennoprzecinkowy, dokładność 15 cyfr
String  - typ łańcuchowy, pozwala przetwarzać teksty

 

Na początek utwórzmy funkcję bez parametrów, która będzie zwracać wartość 2π. W edytorze wpisz poniższy kod:

 

function dwa_pi() as double
    dwa_pi = 6.28318530717958
end function

 

Zwróć uwagę na sposób określania wartości funkcji - nadajemy wartość jej nazwie.

 

Przejdź do arkusza kalkulacyjnego i w komórce A1 wpisz formułę:

 

 

W wyniku otrzymasz wartość:

 

 

Rozszerzyłeś zestaw dostępnych funkcji MS-Excel o nową funkcję dwa_pi() i możesz z niej korzystać w tym arkuszu jak z każdej innej funkcji wbudowanej. Bardziej użyteczna będzie funkcja, która otrzymuje parametr i na jego podstawie wylicza jakiś wynik. Dopisz w edytorze VBA następujący kod (pomiędzy arkuszem a edytorem VBA możesz szybko się przełączać za pomocą klawiszy Alt+F11):

 

function dziel_2(liczba as double) as double
  dziel_2 = liczba / 2
end function

 

Przejdź do arkusza. W komórce A1 umieść liczbę 5, a do A2 wpisz formułę:

 

 

Jako wynik w A2 otrzymasz 2,5. Zwróć uwagę, iż do funkcji przekazaliśmy zawartość komórki A1 zgodnie z konwencją arkusza kalkulacyjnego.

Kolejna funkcja będzie wykorzystywała instrukcję warunkową, która w języku VBA posiada następujące formy:

 

if warunek then instrukcja
if warunek then instrukcja, gdy warunek prawdziwy else instrukcja, gdy warunek fałszywy
if warunek then
  instrukcje, gdy warunek prawdziwy
  ...
else
  instrukcje, gdy warunek fałszywy
  ...
end if
if warunek1 then
  instrukcje, gdy warunek1 prawdziwy
  ...
elseif warunek2 then
  instrukcje, gdy warunek2 prawdziwy
  ...
dalsze bloki elseif
  ...
else
  instrukcje, gdy żaden z powyższych warunków nie był prawdziwy
end if

 

Warunek jest wyrażeniem logicznym, które daje w wyniku wartość True lub False. W warunkach można stosować operatory porównań:

 

Operator Opis
= równe, np. a = 5
<> różne, np. a <> b
> większe, np. a > 10
>= większe lub równe, np. a >= b - 1
< mniejsze, np. a < 10
<= mniejsze lub równe, np. a + b <= 10

 

W warunkach mogą również być stosowane dwie funkcje logiczne:

 

w1 AND w2 - wynik jest prawdziwy, gdy oba warunki są prawdziwe, inaczej wynik jest fałszem
w1 OR w2 - wynik jest fałszywy, gdy oba warunki są fałszywe, inaczej wynik jest prawdą

 

Napiszmy prostą funkcję, która będzie przetwarzała ocenę liczbową na ocenę słowną. Obowiązują następujące przedziały:

 

Przedział Ocena
5,5...6 cel
4,4...5,5 bdb
3,5...4,5 db
2,5...3,5 dst
1,5...2,5 dop
< 1,5 ndst

 

W oknie edytora VBA dopisz poniższy kod:

 

function ocena(w as double) as string
  if w > 5.5 then
    ocena = "cel"
  elseif w > 4.5 then
    ocena = "bdb"
  elseif w > 3.5 then
    ocena = "db"
  elseif w > 2.5 then
    ocena = "dst"
  elseif w > 1.5 then
    ocena = "dop"
  else
    ocena = "ndst"
  end if
end function

 

Przejdź do arkusza, w komórkach od A1 do A10 umieść oceny liczbowe:

 

 

Przejdź do komórki B1 i wpisz w niej formułę:

 

 

Skopiuj komórkę formułę z B1 na pozostałe komórki od B2 do B10. Arkusz wypisze oceny słowne:

 

 

Wg podobnych zasad możesz utworzyć również podobne funkcje, które muszą zamieniać liczby na słowa.

Język VBA posiada kilka instrukcji do tworzenia pętli warunkowych. Oto jedna z nich:

 

while warunek
  powtarzane instrukcje
wend

 

Pętla ta sprawdza na początku warunek, i jeśli jest on prawdziwy, to wykonuje obieg. Pętla jest przerywana, jeśli warunek nie jest prawdziwy. Kolejna funkcja będzie wyliczała największy wspólny dzielnik dwóch liczb za pomocą algorytmu Euklidesa. W algorytmie tym potrzebujemy zmiennej do przechowania wartości reszty z dzielenia. Zmienne w języku VBA tworzymy następująco:

 

dim zmienna as typ

 

W edytorze VBA dopisz kolejną funkcję:

 

function nwd(a as long, b as long) as long
  dim r as long
  while b <> 0
    r = a mod b
    a = b
    b = r
  wend
  nwd = a
end function

 

Przejdź do arkusza, usuń poprzednią zawartość, do komórek A1 i A2 wpisz dwie liczby naturalne:

 

 

W komórce A4 umieść formułę:

 

 

Uwaga:

Zwróć uwagę, iż w arkuszu argumenty funkcji oddzielamy średnikiem, a nie przecinkiem. Jest to konsekwencją lokalizacji - w języku polskim przecinek służy do rozdzielania części całkowitej od ułamkowej w liczbie dziesiętnej. Excel przyjmuje te ustawienia, jednakże wtedy przecinka nie można stosować do oddzielania elementów listy, np. 5,6 - czy to są dwie liczby 5 i 6, czy jedna? Dlatego elementy listy w arkuszu oddziela się od siebie za pomocą średnika - w VBA natomiast przecinki mają swoją zwykłą rolę, ponieważ język ten stosuje kropkę, do oddzielania części całkowitej od ułamkowej w liczbie - wg systemu anglosaskiego.

 

Teraz dopiszemy funkcję, która będzie wyliczała najmniejszą wspólną wielokrotność. Funkcja ta wykorzystuje funkcję nwd(). W edytorze VBA dopisz:

 

function nww(a as long, b as long) as long
  nww = a * b \ nwd(a, b)
end function

 

Operator \ oznacza dzielenie całkowitoliczbowe. Nie myl go z operatorem /, który dzieli liczby zmiennoprzecinkowe.

Przejdź do komórki A5 i wpisz w niej następującą formułę:

 

 

Od tej chwili arkusz wylicza nwd i nww liczb w komórkach A1 i A2.

Pętla iteracyjna (wykonująca się zadaną liczbę razy) ma w języku VBA następującą postać:

 

for zmienna = początek to koniec step krok
  powtarzane instrukcje
  ...
next

 

zmienna przyjmuje w kolejnych obiegach pętli wartości od wartości początkowej do wartości końcowej z podanym krokiem. Jeśli krok wynosi 1, to człon step może zostać pominięty. Ostatnia funkcja na dzisiejszej lekcji sprawdza, czy liczba przekazana jej jako argument jest liczbą pierwszą. Jeśli tak, to wynikiem funkcji jest tekst "pierwsza". Inaczej funkcja zwraca tekst pusty. W edytorze VBA wpisz:

 

function czy_pierwsza(liczba as long) as string
  dim i, g as long
  if liczba <> 2 and liczba mod 2 = 0 then
    czy_pierwsza = ""
  else
    czy_pierwsza = "pierwsza"
    g = int(sqr(liczba))
    for i = 3 to g step 2
      if liczba mod i = 0 then
        czy_pierwsza = ""
        exit for
      end if
    next
  end if
end function

 

W programie mamy dwie nowe funkcje:

 

Int(x) - zwraca część całkowitą z x
Sqr(x) - zwraca pierwiastek kwadratowy z x

 

Występuje również nowa instrukcja:

 

Exit For

 

Instrukcja ta powoduje wyjście z pętli typu For. Jest niezbędna, ponieważ, stwierdziwszy podzielność liczby, nie ma sensu dalej wykonywać tej pętli - liczba nie jest pierwsza.

W arkuszu w komórkach od A1 do A100 umieść kolejne liczby naturalne: 2, 3, 4...

 

 

W komórce B1 umieść formułę:

 

 

Skopiuj tę formułę do komórek B2...B100. Arkusz Excel wskaże ci liczby pierwsze:

 

 



List do administratora Serwisu Edukacyjnego Nauczycieli I LO

Twój email: (jeśli chcesz otrzymać odpowiedź)
Temat:
Uwaga: ← tutaj wpisz wyraz  ilo , inaczej list zostanie zignorowany

Poniżej wpisz swoje uwagi lub pytania dotyczące tego rozdziału (max. 2048 znaków).

Liczba znaków do wykorzystania: 2048

 

W związku z dużą liczbą listów do naszego serwisu edukacyjnego nie będziemy udzielać odpowiedzi na prośby rozwiązywania zadań, pisania programów zaliczeniowych, przesyłania materiałów czy też tłumaczenia zagadnień szeroko opisywanych w podręcznikach.



   I Liceum Ogólnokształcące   
im. Kazimierza Brodzińskiego
w Tarnowie

©2017 mgr Jerzy Wałaszek

Dokument ten rozpowszechniany jest zgodnie z zasadami licencji
GNU Free Documentation License.