POWRÓT

SEMESTR VI

Części składowe języka VBA


Spis treści rozdziału - tutaj kliknij

Procedury SUB i Function
Zmienne
Instrukcje przypisania
Podstawowe instrukcje warunkowe i sterujące VBA
Wbudowane funkcje VBA
Zmienne obiektowe - praca z obiektami i kolekcjami
Sterowanie sposobem wykonywania procedur
Zadanie 2

Źródła - Excel 2013 Pl, Programowanie VBA - John Walkenbach, VBA dla Excela 2010 Pl - Witold Wrotek, Excel 2013 Pl, programowanie w VBA dla Bystrzaków.


 

Procedury Sub, Function

     
   

  Program w VBA jest złożony z instrukcji, które pogrupowane są w procedury, moduły i projekty.

  • Procedura to najmniejsza część kodu programu posiadająca nazwę, którą można uruchomić niezależnie.
  • Moduł - zawiera z jedną lub więcej procedur oraz sekcje deklaracji w której umieszczamy instrukcje wspólne dla w wszystkich procedur tego modułu. Możemy wyróżnić dwa rodzaje modułów: moduł standardowy i moduł klasy.
  • Projekt - obejmuje wszystkie moduły, formularze, obiekty aplikacji macierzystej dokumentu oraz sam dokument.

  Natomiast instrukcje dzielą się na;

  • deklaracje,
  • instrukcje przypisania,
  • instrukcje wykonujące konkretne zadania,
  • instrukcje z informacjami dla kompilatora.

Deklaracja - służy do poinformowania kompiltora VBA o zamiarze użycia w programie zmiennej określonego typu. Każda zmienna powinna być zadeklarowana. Mówimy w ten sposób Excelowi, żeby zarezerwował w pamięci kawałek miejsca i od teraz do tej części pamięci będziemy się odwoływać przez wskazaną przez nas nazwę.
Instrukcja przypisania - przypisuje wartość do zmiennej lub właściwości obiektu.
Pozostałe dwa typy instrukcji - wykonują kontretne zadania wywoływania podprogramów oraz informowania kompilatora.


Rodzaje procedur

Są dwa rodzaje procedur, tj. procedury Sub oraz procedury Function

Procedury Sub - są to podprogramy, które wykonują pewne działania w Excelu i służą do automatyzacji określonych, powtarzalnych działań. Cechą charakterystyczną jest to, że przyjmują argumenty ale nie zwracają wartości, dlatego nie mogą być wykorzystywane w formułach. Składnia funkcji jest następująca.

[Public|Private] [Static] Sub NazwaProcedury(lista argumentów)
   Instrukcja 1 'komentarze
   Exit Sub
   Instrukcja 2 'komentarze
  ..........
End Sub

    Deklaracja funkcji składa się z następujacych elementów;

  • Public - wskazuje, że procedura Sub będzie dostępna dla wszystkich innych procedur we wszystkich modułach skoroszytu,
  • Private - wskazuje, że procedura Sub będzie dostępna tylko dla innych procedur tego samego modułu,
  • Static - wskazuje, że wartości zmiennych zadeklarowanych w procedurze Sub nie zostaną usunięte po zakończeniu działania procedury, ale będą przechowywane w pamięci do jej kolejnego wywołania,
  • Sub - wskazuje początek procedury,
  • NazwaProcedury - dowolna poprawna nazwa procedury,
  • Lista argumentów - reprezentuje listę zmiennych zawartych w nawiasach okrągłych, pobierających argumenty przekazywane procedurze,
  • Instrukcje 1, 2 - zestaw poprawnych instrukcji języka VBA,
  • Exit Sub - wymusza natychmiastowe zakończenie procedury jeszcze przed osiągnięciem jej formalnego końca,
  • End Sub - wskazuje koniec procedury.

Przykład 1

Sub MistrzostwaEuropy()
   MsgBox "Czy Polska zostanie Mistrzem Europy?"
End Sub

Przykład 2

Sub kopiowanie()
   Range("A1:A3").Select
   Selection.Copy
   Range("D1").Select
   ActiveSheet.Paste
End Sub

Przykład 3 - z klawiszem skrótu

Sub kopia()
   ' Klawisz skrótu: Ctrl+Shift+W
   Range("A2:A6").Select
   Selection.Copy
   Range("C2:C6").Select
   ActiveSheet.Paste
End Sub

    Procedurę Sub wywołuje się zazwyczaj klikając jakiś przycisk albo są skojarzone z określonymi zdarzeniami typu zmiana bieżącego arkusza, zmiana aktywnego zakresu, zmiana zawartości arkusza.

Procedura Function - inaczej funkcja, to zbiór instrukcji VBA, które wykonują obliczenia i w odróżnieniu procedury Sub zwracają pojedyńczą wartość (lub tablicę). Procedury Function, podobnie jak funkcje wbudowane, mogą używać argumentów.
Mogą być użyte w dwóch sytuacjach;

  • jako część wyrażenia zawartego w procedurze języka VBA,
  • w formułach tworzonych w arkuszu.

Procedurę deklarujemy za pomocą słowa kluczowego Function, instrukcja End Function kończy procedurę. Składnia funkcji jest następująca.

[Public|Private][Static]Function NazwaFunkcji(lista argumentów) As typ
   instrukcje
   NazwaFunkcji = wyrażenie
   Exit Function
   instrukcje
   NazwaFunkcji = wyrażenie
End Function

    Deklaracja funkcji składa się z następujacych elementów;

  • Public - wskazuje, że procedura Function będzie dostępna dla wszystkich innych procedur we wszystkich pozostałych modułach wszystkich aktywnych projektów Excela,
  • Private - wskazuje, że procedura Function będzie dostępna tylko dla innych procedur tego samego modułu,
  • Static - wskazuje, że wartości zmiennych zadeklarowanych w procedurze Function są zachowane pomiędzy kolejnymi wywołaniami,
  • Function - wskazuje początek procedury zwracającej wartość lub dane innego typu,
  • NazwaFunkcji - reprezentuje dowolną poprawną nazwę procedury Function. Nazwa musi zostać nadana zgodnie z zasadami stosowanymi przy nadawaniu nazw zmiennym.
  • Lista argumentów - lista składająca się z jednej lub kilku zmiennych bedących argumentami przekazywanymi procedurze. Argumenty umieszczane są w nawiasach okrągłych. W celu oddzielenia od siebie kolejnych argumentów stosuje się przecinek,
  • Typ - identyfikuje typ danych wartości zwracanej przez funkcję
  • Instrukcje -dowolna liczba poprawnych instrukcji języka VBA,
  • Exit Function - wymusza natychmiastowe zakończenie procedury Function,
  • End Function - wskazuje koniec procedury.

Przykład funkcji pobierającej jedną liczbę i podającej wynik potęgowania - przykład

  • Tutaj do pobrania kody przykładowych procedur - kody »»» 
  • Do góry


     

    Zmienne

         
       

        Podstawowym zadaniem języka VBA jest przetwarzanie danych. Niektóre znajdują się w obiektach, takich jak zakresy arkusza. Inne są przechowywane w utworzonych przez programistę zmiennych.
      Zmiennym języka VBA można przypisać różne wartości i należy ją traktować jako nazwę służącą do przechowywania określonej wartości. Zmienne stosuje się aby uzyskać; oszczędność pamięci, porządek dla programisty i możliwość łatwiejszych zmian i poprawek, przyspieszenie wykonywania programu, prawidłowe przechowywanie i operacje na danych.
    VBA udostępnia nam poniższe typy zmiennych;

    • Byte - wartości dodatnie,
    • Boolean - przechowuje dane binarne zwracając wartości True lub False,
    • Integer - przechowuje dane numeryczne od -32,768 do +32,767,
    • Long - przechowuje dane numeryczne z zakresu -2,147,483,648 do +2,147,483,647,
    • Single - liczby rzeczywiste od -3.4e38 do +3.4e38,
    • Double - przechowuje liczby zmiennoprzecinkowe,
    • Currency - liczby rzeczywiste o stałej ilości miejsc po przecinku,
    • Date - typ przeznaczony dla dat 1 styczeń 100 to 31 grudzień 9999,
    • Object - przechowuje bieżące obiekty z modelu obiektów Excela,
    • String - przechowuje dane tekstowe,
    • Variant - przechowuje każdy rodzaj danych.

      Typ zmiennej oraz zasięg jej działania (jej dostępność) zależy od miejsca oraz sposobu jej zadeklarowania. Zmienne mogą być deklarowane w sekcji deklaracji modułu, w sekcji deklaracji formularza, albo na początku procedury. Ich zasięg działania określa użyte słowo kluczowe. Poniżej zestawienie słów kluczowyh.

    • Dim - ograniczony do procedury, w którym zmienna ta została zadeklarowana,
    • Public - zmienna dostępna w dowolnym miejscu w programie, w różnych modułach i formularzach.
    • Private - deklaruje zmienne, które będą widoczne tylko w odrębie modułu lub formularza, w którym zostały zadeklarowane,
    • Static - pozwala zadeklarować zmienne, które nie są usunięte z pamięci komputera po zakończeniu działania procedury, lecz ich ostatnia wartość będzie dostępna tylko w następnym wywołaniu procedury.

      Tworzenie zmiennych w makrze nosi nazwę deklaracji. Aby utworzyć deklaracje należy - w kodzie wprowadzić nazwę zmiennej oraz jej typ, zgodnie z poniższym przykładem.

    Dim nazwa_zmiennej As typ_zmiennej
    Przykład

    Dim delta As Double
    Długość łańcucha znaków przechowywanego w zmiennej typu String można ograniczać:
    Dim tekst As String * 25
    Definiuje zmienną tekst typu String o długości do 25 znaków

    Po utworzeniu zmiennej można wprowadzić do niej dane. Poniżej przykład jak to można zrobić.

    Dim MójText as String
    MójText = Range("A1").Value

    Dim MojaLiczba as Integer
    MojaLiczba = Range("B1").Value * 25

    Dim MójArkusz as Worksheet
    Set MójArkusz = Sheets("Arkusz1")

    gdzie; Range - oznacza obszar, zakres komórek w arkuszu Excela. Może też być pojedyńczą komórką. Ma on swoje charakterystyczne właściwości i metody. Najczęściej wykorzystywane Właściwości to;

    • Value - wartość poszczególnych komórek w obszarze,
    • Font - ustawienia czcionki. Ta właściwość jest jednocześnie sama obiektem, definiowanym przez takie atrybuty jak Bold (pogrubienie czcionki), Color (kolor czcionki), Underline (podkreślenie), Italic (kursywa), Size (wielkość) czy FontStyle (rodzaj czcionki),
    • Interior - wypełnienie tła,
    • Formula - formuła obliczająca wartość,
    • Column - właściwości kolumny,
    • Row - właściwości wiersza.

    Najczęśćiej wykorzystywane Metody to;

    • Clear - czyści wszystkie właściwości komórki - wartość, ustawienia czcionki, kolor itp. W ramach tej metody można ograniczyć się do usuwania tylko niektóych właściwości np. ClearComments (czyszczenie komentarzy), ClearFomats (czyszczenie formatowania komórki) itd.
    • Activate - w odniesieniu do pojedynczej komórki - aktywują ją
    • AddComment - dodaj komentarz
    • Copy - kopiowanie obszaru,
    • Paste - wklejanie obszaru,
    • Select - zaznaczanie obszaru.

      Ćwiczenie 1 - pokazuje, że ten sam efekt realizacji programu można osiągnąć używając zmiennych, jak i również nie korzystając ze zmiennych. W celu sprawdzenia, należy w oknie Code uruchomionego Excela i VBA umieścić programy umieszczone w notatnku.

    Tutaj do pobrania kody przykładowych procedur zapisane w notatniku - kody »»» 

      Uwaga - nie używając zmiennych wynik realizacji programu zapisany jest w Arkuszu 1. Natomiast używając zmiennych wynik zapisywany jest w Arkuszu 2.

    Do góry


     

    Instrukcje przypisania

         
       

      Podstawowym typem instrukcji jest instrukcja przypisania wartości zmiennej

    nazwa_zmiennej = wyrażenie

      Składnikiem wyrażenia mogą być; stałe, zmienne, operatory (np. +, -, *, /, ^), nawiasy (tylko okrągłe!) i funkcje (zarówno funkcje języka VBA, Excela, jak i własne).
    Funkcje VBA i własne wprowadzamy poprzez samą nazwę, ale VBA można wprowadzić też poprzez kod VBA.nazwa_funkcji. Funkcje Excelowskie wprowadzamy przez kod WorksheetFunction.nazwa_funkcji. Zalecane jest używanie raczej funkcji VBA.
      Instrukcja przypisania jest instrukcją wykonującą obliczenia matematyczne i przypisująca wynik zmiennej lub obiektowi.
    Poniżej podano kilka przykładowych instrukcji przypisania (wyrażenia znajdują się po prawej stronie znaku równości).


    suma = 0
    i = i + 1
    pole = 3.14 * r^2

      Ćwiczenie 2 - wpływ zastosowanej deklaracji na przebieg realizacji programu. W celu sprawdzenia, jaki ma wpływ zastosowana deklaracja na przebieg programu, należy w oknie Code uruchomionego Excela i VBA umieścić programy z różnymi deklaracjami pobranymi z ramki umieszczonej niżej, za każdym razem uruchamiając program

  • Tutaj do pobrania skoroszyt do ćwiczenia - zmiana kodu w instrukcji przypisania »»» 
  • Tutaj do pobrania kody do makra - kody »»» 
  •   Ćwiczenie wykonujemy dla deklaracji Dim x As String, Dim x As Variant, Dim x As Integer, Dim x As Boolean, Dim x As Date.

    Do góry


     

    Podstawowe instrukcje warunkowe i sterujące VBA

         
       

        Podstawowe instrukcje stosowane w VBA pozwalające kontrolować przepływ programu to;

    • Instrukcja If... Then... Else - najczęściej stosowana instrukcja warunkowa. Jest używana do sprawdzenia wartości warunku, czy jest równa True lub False, i wykonania, w zależności od tej wartości, określonego bloku instrukcji.
    • Instrukcja Select Case - jest alternatywą dla konstrukcji if...then...elseif, w przypadku wyboru pomiędzy kilkoma możliwymi drogami wykonania skryptu.
    • Pętle warunkowe Do...Loop - służą one do wielokrotnego wykonywania danego bloku kodu. Instrukcji Do...Loop użyjemy jeżeli nie wiemy ile razy pętla ma być wykonana. Jest to pętla warunkowa, w której kluczową cechą jest warunek.
    • Instrukcja For... Next - pętla For... Next - powtarza blok instrukcji określoną liczbę razy, stosujemy ją jeżeli z góry wiadomo ile razy pętla ma być wykonana.
    • Instrukcja For Each... Next - pętla służąca do wykonywania operacji na obiektach kolekcji.

    Szczegółowy opis wykorzystania instrukcji w części ćwiczeniowej.

    Do góry


     

    Wbudowane funkcje VBA

         
       

      Język VBA, podobnie jak większość języków programowania, posiada szereg wbudowanych funkcji upraszczających obliczenia i wykonywanie wielu operacji. Wiele funkcji języka VBA jest podobnych do funkcji arkuszowych Excela, a czasami są one identyczne.
    Funkcje wbudowane zostały podzielone na następujące kategorie.

    • Funkcje tekstowe,
    • Funkcje liczbowe,
    • Funkcje daty i czasu,
    • Funkcje konwersji i informacyjne,
    • Funkcje arkuszowe.

    Funkcje VBA i własne wprowadzamy poprzez samą nazwę, ale VBA można wprowadzić też poprzez kod VBA.nazwa_funkcji. Funkcje Excelowskie wprowadzamy przez kod WorksheetFunction.nazwa_funkcji. Zalecane jest używanie raczej funkcji VBA.

    Wybrane funkcje liczbowe VBA

    • Abs(x) - wartość bezwzględna,
    • Atn(x) - arctg x,
    • Cos(x) - trygonometryczna cos,
    • Exp(x) - funkcja wykładnicza o podstawie stałej Eulera - ex,
    • Log(x) - logarytm naturalny - ln x,
    • Sin(x) - trygonometryczna sin,
    • Sqr(x) - pierwiastek kwadratowy,
    • Tan(x) - tg x

    Ćwiczenie 3 - Przykład procedury dla funkcji wbudowanej - obliczanie pierwiastka. W celu sprawdzenia należy w oknie Code uruchomionego Excela i VBA umieścić program skopiowany z notatnika.

  • Tutaj do pobrania kody przykładowych procedur - kody »»» 
  • Do góry


     

    Zmienne obiektowe - praca z obiektami i kolekcjami

         
       

    Zmienna obiektowa

      Zmienna obiektowa jest zmienną reprezentującą cały obiekt, np. arkusz. Zmienne obiektowe, podobnie jak zwykłe zmienne, są deklarowane przy użyciu instrukcji Dim lub Public. Na przykład poniższe polecenie deklaruje zmienną InputArea jako obiekt klasy Range;

    Dim InputArea as Range

      Aby przypisać obiekt do zmiennej, należy użyć słowa kluczowego Set, jak w poniższym przykładzie.

    Set Dim InputArea("C16:E16")


  • Tutaj do pobrania skoroszyt do ćwiczenia - zmiana kodu »»» 
  • Tutaj do pobrania kod do makra - zmiana kodu »»» 
  •   Podczas codziennej pracy w Excelu mamy do czynienia ze skoroszytami, arkuszami i zakresami, które składają się na hierachiczny model obiektowy Excela. Dlatego też pracując z językiem VBA, konieczna jest znajomość koncepcji wykorzystania obiektów i ich hierarchicznej struktury. Na szczycie modelu znajduje się obiekt Application, który zawiera inne obiekty, takie jak;

    • Workbooks - kolekcja wszystkich obiektów Workbook, tj. skoroszyty,
    • Windows - kolekcja wszystkich obiektów Window, tj. okna,
    • AddIns - kolekcja wszystkich obiektów AddIn, tj. dodatki.

      Niektóre obiekty sa kontenerami dla innych obiektów. Przykładowo zbiór Workbooks składa się z wszystkich otwartych obiektów Workbook. Z kolei obiekt Workbook zawiera inne obiekty, min. takie jak;

    • Worksheets - kolekcja obiektów Worksheet, tj. arkusze,
    • Charts - kolekcja obiektów Chart, tj. wykresy,
    • Names - kolekcja obiektów Name, tj. nazwy.

    Z kolei każdy z powyższych obiektów przechowuje inne obiekty. Oto niektóre z nich;

    • ChartObjects
    • Range
    • PageSetup
    • Pivot Tables

      Większość pracy z Excelem polega na modyfikowaniu komóre, co w efekcie przyczynia się do wykonywania dużej części pracy w VBA przy pomocy zakresów (obiekt Range). Range może reprezentować pojedynczą komórkę, wiersz, kolumnę, grupę komórek z zakresu ciągłego lub nie albo nawet zakres z kilku skoroszytów jednocześnie.
      Praca na obiektach to również adresowanie, np. Application.ActiveWorkbook.Sheets("Arkusz1"),Range("A1"),Select

    Przykład funkcji, która oblicza pierwiastek kwadratowy dla zmiennej - przykład

  • Tutaj do pobrania kod funkcji, która oblicza pierwiastek kwadratowy - kody »»» 
  • Do góry


     

    Sterowanie sposobem wykonywania procedur

         
       

      W niektórych procedurach instrukcje są przetwarzane po kolei - od pierwszej do ostatniej. Jednak często konieczne jest sterowanie wykonywaniem procedur poprzez pominięcia w instrukcji, wielokrotne przetworzenie niektórych z nich i przetestowanie warunków określających, jaką czynność zrealizuje następną. Czynność tę zabezpieczają nam dodatkowe metody sterowania wykonywaniem procedur języka VBA. Są nimi

    • polecenie GoTo,
    • konstrukcja If...Then,
    • konstrukcja Select Case,
    • pętla For... Next,
    • pętla Do While,
    • Pętla Do Until.

    Do góry


     

    Zadanie 2

         
       

      W oparciu o załączone dane zawarte w skoroszycie aplikacja_cw, opracuj aplikację z obsługą makr do obliczania kosztów podgrzania wody w lokalach.

    • Tutaj do pobrania skoroszyt - aplikacja_cw »»» ,
    • Należy obliczyć wielkość wpłacanej do firmy zarządzającej zaliczki na pokrycie kosztów podgrzania wody.,
    • Należy obliczyć wielkość kosztów na podgrzanie wody przypadająca na lokal,
    • Dokonać oceny wielkość zaliczki - koszty,

    Do góry


     

     (C) 2011 - 2018 Wydział Przyrodniczo - Techniczny KPSW. All Rights Reserved