Prezentowane materiały są przeznaczone dla uczniów szkół ponadgimnazjalnych. Autor artykułu: mgr Jerzy Wałaszek,  wersja1.0

©2010 mgr Jerzy Wałaszek I LO w Tarnowie

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:

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ń:

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:

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łę:

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:

---

I Liceum Ogólnokształcące    im. Kazimierza Brodzińskiego w Tarnowie ©2024 mgr Jerzy Wałaszek

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

Pytania proszę przesyłać na adres email: i-lo@eduinf.waw.pl

W artykułach serwisu są używane cookies. Jeśli nie chcesz ich otrzymywać,
zablokuj je w swojej przeglądarce.
Informacje dodatkowe