zmienne tablicowe
deklaracja zmiennych tablicowych
Tablica jednowymiarowa
Dim nazwa_tablicy(wymiar) As typ_elementów
wymiar:
indeks_początkowy To indeks_końcowy
indeks_końcowy
Elementy tablicy domyślnie numerowane są od 0
Jeśli użytkownik chce, aby wszystkie tablice numerowane były od jedynki, w obszarze declarations powinien użyć instrukcji:
Option Base 1
Uwaga:
Excel kontroluje zakres indeksów. Jeśli w programie następuje przekroczenie zakresu tablicy pojawia się błąd:
Przykłady:
Dim tab(100) As Integer
istnieje element tab(0), tab(100), nie ma elementu tab(101)
Dim tab(1 To 100) As Integer
istnieje element tab(1), tab(100), nie ma elementu tab(101), czy tab(0)
Option Base 1
...........
Dim tab(100) As Integer
Tablice wielowymiarowe
Deklaracja tablicy dwuwymiarowej:
Dim nazwa_tablicy(wymiar1,wymiar2) As typ_elementów
wymiarn:
indeks_początkowyn To indeks_końcowyn
indeks_końcowyn
Przykłady:
Dim tab(5, 4) As Double
Dim tab(1 To 10, 1 To 2) As String * 5
Option Base 1
...........
Dim tab(13, 4) As Integer
Dim tab(5, 6, 7) As Long
wykorzystanie zmiennych tablicowych
Sub num() Dim A(2) As Integer A(0) = 1 A(1) = 3 A(2) = 19 MsgBox "wartości elementów tablicy" & vbCrLf _ & A(0) & "," & A(1)& "," & A(2) End Sub |
|
Sub wek() Dim A(1 To 5) As Integer For i = 1 To 5 A(i) = i Next kom = "tablica=" For i = 1 To 5 kom = kom & A(i) & "," Next MsgBox kom End Sub |
|
Sub naj() Dim A(1 To 5) As Integer For i = 1 To 5 A(i) = Worksheets("Arkusz1").Range("A1").Offset(i - 1, 0) Next Max = A(1): j = 1 For i = 2 To 5 If A(i) > Max Then Max = A(i): j = i Next MsgBox "Największy element zapisany jest w " & j & "-tym wierszu i _ jest równy " & Max End Sub |
|
|
|
Sub iloczyn() 'oblicza iloczyn macierzy Dim A(1 To 3, 1 To 4) As Integer, B(1 To 4, 1 To 2) As Integer Dim C(1 To 3, 1 To 2) As Integer ' wczytanie macierzy A For i = 1 To 3 For j = 1 To 4 A(i, j) = Worksheets("Arkusz1").Range("A1").Offset(i - 1, j - 1) Next j Next i ' wczytanie macierzy B For i = 1 To 4 For j = 1 To 2 B(i, j) = Worksheets("Arkusz1").Range("F1").Offset(i - 1, j - 1) Next j Next i ' mnożenie For i = 1 To 3 For j = 1 To 2 C(i, j) = 0 For k = 1 To 4 C(i, j) = C(i, j) + A(i, k) * B(k, j) Next k Next j Next i ' wyniki kom = "Iloczyn macierzy" & vbCrLf For i = 1 To 3 kom = kom & C(i, 1) & "," & C(i, 2) & vbCrLf Next i MsgBox kom End Sub |
|
|
|
Tablice dynamiczne
Tablica dynamiczna nie ma z góry określonej liczby elementów.
Dim nazwa_tablicy() As typ_elementów
Przed użyciem tablicy musi zostać określony jej rozmiar
ReDim nazwa_tablicy(wymiary) [As typ_elementów]
Sub tabl_dyn()
Dim A() As Integer
i = 2
j = 3
ReDim A(1 To i, 1 To j)
A(2, 2) = 7
MsgBox A(2, 2)
k = 11
ReDim A(1 To k)
A(7) = 8
MsgBox A(7)
End Sub
Rozmiar tabeli może być określony przez użytkownika programu:
Sub tabl_dyn()
Dim A() As Integer
i = InputBox("podaj liczbę wierszy tabeli")
j = InputBox("podaj liczbę kolumn tabeli")
ReDim A(1 To i, 1 To j)
MsgBox "Macierz ma wierszy - " & i & " i kolumn - " & j
End Sub
sortowanie wektora
Sub sort1() Dim x() As Integer, y() As Integer m = 4 ReDim x(1 To m), y(1 To m) For i = 1 To m x(i) = ActiveSheet.Range("a1").Offset(i - 1, 0) Next i For i = 1 To m Min = x(1): k = 1 For j = 2 To m If x(j) < Min Then Min = x(j): k = j Next j x(k) = 1000 y(i) = Min Next i For i = 1 To m ActiveSheet.Range("a1").Offset(i - 1, 1).Value = y(i) Next i End Sub |
|
Sub sort2() Dim x() As Integer m = 5 ReDim x(1 To m) For i = 1 To m x(i) = ActiveSheet.Range("a1").Offset(i - 1, 0) Next i If x(1) > x(2) Then pom = x(1): x(1) = x(2): x(2) = pom For i = 3 To m j = i - 1 b = x(i) Do Until (j = 1 Or b > x(j)) x(j + 1) = x(j) x(j) = b j = j - 1 Loop If x(1) > b Then x(2) = x(1): x(1) = b Next i For i = 1 To m ActiveSheet.Range("a1").Offset(i - 1, 1).Value = x(i) Next i End Sub |
|
obiekty i kolekcje
zmienne obiektowe
Zmienna obiektowa reprezentuje obiekt Microsoft Excel.
upraszczają zapis kodu
skracają czas wykonania kodu
Deklaracja - Dim lub Public
Przykłady
WorkSheets(”Arkusz1”).Range(”A1”).Value = 567
WorkSheets(”Arkusz1”).Range(”A1”).Font.Bold = True
WorkSheets(”Arkusz1”).Range(”A1”).Font.Italic = True
Wykorzystanie zmiennej obiektowej:
Dim Kom As Range
Set Kom = WorkSheets(”Arkusz1”).Range(”A1”)
Kom.Value = 567
Kom.Font.Bold = True
Kom.Font.Italic = True
Dim ark As Worksheet
Set ark = Application.ActiveSheet
ark.Range("A1:C3").Interior.ColorIndex = 2
ark.Range("A1:C3").Borders.ColorIndex = 6
ark.Range("A1:C3").Font.ColorIndex = 7
Przykładowe kody kolorów:
1-black 2-white 3-red 4-green 5-blue 6-yellow
predefiniowane stałe
vbBlack vbWhite
instrukcja with
Składnia:
With obiekt
instrukcje wykonywane na obiekcie
(nazwy metod i właściwości poprzedzone kropką)
End With
With WorkSheets(”Arkusz1”).Range(”A1”)
.Value = 567
.Font.Bold = True
.Font.Italic = True
End With
With Application.ActiveSheet
.Range("A1:C3").Interior.ColorIndex = 2
.Range("A1:C3").Borders.ColorIndex = 6
.Range("A1:C3").Font.ColorIndex = 7
End With
instrukcja for each-next
Składnia:
For Each element In grupa
[instrukcje]
[Exit For]
[instrukcje]
Next [element]
Pętla wykonuje operacje dla wszystkich elementów grupy
Wypełnienie tablicy wartościami losowymi z przedziału 0-1 i wypisanie ich w oknach dialogowych:
Dim Tabl(5) As Single
Randomize
For i = 0 to 5
Tabl(i) = Rnd
Next i
For Each t In Tabl
MsgBox t
Next t
lub
Next
Wypisanie w oknach dialogowych nazw arkuszy w aktywnym skoroszycie:
Dim ark As Worksheet
For Each ark In ActiveWorkbook.Worksheets
MsgBox ark.Name
Next ark
Zamykanie nieaktywnych skoroszytów:
Dim SK As Workbook
For Each SK In Workbooks
If SK.Name <> ActiveWorkbook.Name Then SK.Close
Next SK
Zamiana we wszystkich komórkach zaznaczonego obszaru liter na duże:
Dim kom As Range
For Each kom In Selection
kom.Value = UCase(kom.Value)
Next kom
typy danych użytkownika
Typy użytkownika definiowane są w obszarze declaration
Składnia:
Type nazwa_typu
nazwa_zm1 As type_zm1
nazwa_zm21 As type_zm2
.....
End Type
Odwołanie do elementów
Dim nazwa_zmiennej As nazwa_typu
nazwa_zmiennej.nazwa_zm1
nazwa_zmiennej.nazwa_zm2
Przykład
Type rach
cena As Currency
ilosc As Single
wartosc As Currency
End Type
Sub rachunek()
Dim dane As rach, i As Integer, ob As Range
Set ob = ActiveSheet.Range("a1")
i = 1
Do Until ob.Offset(i, 0).Value = ""
dane.cena = ob.Offset(i, 1).Value
dane.ilosc = ob.Offset(i, 2).Value
ob.Offset(i, 3).Value = dane.cena * dane.ilosc
ob.Offset(i, 3).NumberFormat = "#0.00 zł"
i = i + 1
Loop
End Sub
Inne rozwiązanie:
Range("D2").Value = "=B2*C2"
Range("D2").Copy Destination:=Range("D3:D5")
Range("D2:D5").NumberFormat = "#0.00 zł"
Metoda Copy obiektu Range
zakres_kopiowany.Copy Destination := zakres_docelowy
gdzie:
zakres_kopiowany - zakres, którego zawartość zostanie skopiowana
Destination - parametr zawierający obszar, do którego kopiowany jest zakres_kopiowany
zakres_docelowy - zakres ten musi być zgodny z zakresem kopiowanym
funkcje i procedury wbudowane
Funkcje wbudowane:
mogą być wywoływane bezpośrednio w kodzie VBA
wymagają odwołania do obiektu Application.WorksheetFunction
Lista funkcji wywoływanych bezpośrednio w kodzie VBA:
napisać VBA i kropkę (musi być aktywna opcja Auto List Members (Tools | Options - zakładka Editor)
Lista funkcji obiektu Application.WorksheetFunction:
napisać Application.WorksheetFunction i kropkę (musi być aktywna opcja Auto List Members (Tools | Options - zakładka Editor)
Uwaga:
Pomoc na temat określonej funkcji podświetlić nazwę i F1
W przypadku funkcji obiektu Application.WorksheetFunction po kliknięciu F1 należy kliknąć List of Worksheet Functions Available to Visual Basic i wybrać funkcję z listy.
Przykłady
dzis = VBA.Now
kom = "numer miesiąca -" & VBA.Month(dzis)
MsgBox kom
kom = "rok -" & VBA.Year(dzis)
MsgBox kom
sss = "suma w obszarze jest równa ="
sss = sss & Application.WorksheetFunction.Sum(Range("b7:d9"))
MsgBox sss
sss = "średnia w obszarze jest równa ="
sss = sss & Application.WorksheetFunction.Average(Range("b7:d9"))
MsgBox sss
x = Application.WorksheetFunction.Pi()
MsgBox x
lub
x = Application.WorksheetFunction.Pi
i = Application.WorksheetFunction.CountBlank(Range("a1:b10"))
MsgBox i
procedury użytkownika
Procedura jest ciągiem instrukcji, którym nadawana jest nazwa.
Procedury napisane przez użytkownika mogą być wywoływane z innych procedur (również z poziomu arkusza).
Procedury mogą mieć parametry (argumenty), których wartość musi być określona w chwili wykonywania procedury.
Deklaracja procedury
[Private|Public |Static] Sub nazwa_procedury [(lista_param_form)]
[instrukcje]
[Exit Sub]
[instrukcje]
End Sub
gdzie:
Private - procedura jest dostępna tylko w obrębie danego modułu
Public - procedura jest dostępna we wszystkich otwartych skoroszytach (jeśli moduł zawiera instrukcję Option Private w obszarze Declaration, dostęp zostaje ograniczony do projektu) - Public jest zakresem domyślnym
Static - zmienne lokalne procedury zachowują wartości po jej zakończeniu
nazwa_procedury - nazwa musi spełniać warunki jak nazwy zmiennych. Dodatkowo musi być różna od adresów komórek (np. A3, B11, AA28)
Exit Sub - instrukcja powodująca natychmiastowe wyjście z procedury
lista_param_form - (lista parametrów formalnych) lista zmiennych oddzielonych przecinkami
nazwa_par
nazwa par As typ_parametru
ByVal nazwa_par
ByVal nazwa_par As typ_parametru
Jeśli typ parametru nie został określony, ma on typ Variant
Wywołanie procedury
Wywołanie procedury w innej procedurze
Call nazwa_procedury [(lista_parametrów_aktualnych)]
lub
nazwa_procedury [(lista_parametrów_aktualnych)]
gdzie:
parametr_aktualny - zmienna, tablica, wyrażenie
Wywołanie procedury z innego modułu
Application.Run ("Skoroszyt!Moduł.nazwa_procedury")
Przykład
Skoroszyt Zeszyt2:
Sub wywolanie()
Application.Run ("Zeszyt1.xls!Module1.proc1")
MsgBox "procedura wywołująca"
End Sub
Skoroszyt Zeszyt1, Module1:
Sub proc1()
MsgBox "procedura"
End Sub
Wywołanie procedury z poziomu arkusza
Narzędzia | Makro | Makra
Przekazywanie parametrów do procedury
Parametry mogą być przekazywane do procedury na dwa sposoby:
przez referencje (przez zmienną) - domyślnie
przez wartość - trzeba poprzedzić parametr ByVal
Przykłady
Parametry przekazywane przez referencje i przez wartość
Sub p1(a As Integer, ByVal b As Integer)
MsgBox a & " " & b '1 1
a = 15
b = 20
End Sub
Sub p2()
Dim w As Integer, z As Integer
w = 1
z = 1
Call p1(w, z)
MsgBox w '15
MsgBox z '1
End Sub
Wyrażenie jako parametr aktualny
Sub p1(a As Integer, ByVal b As Integer)
MsgBox a & " " & b '3 4
a = 15
b = 20
End Sub
Sub p2()
Dim w As Integer, z As Integer
w = 1
z = 1
Call p1(w + 2, z + 3)
MsgBox w '1
MsgBox z '1
End Sub
Parametr w postaci tablicy (musi być ostatnim parametrem)
Sub p1(Tabl() As Integer)
Sum = 0
For Each t In Tabl
Sum = Sum + t
Next t
MsgBox "Suma elementów tablicy " & Sum '10
End Sub
Sub p2()
Dim a(1 To 4) As Integer
a(1) = 1: a(2) = 2: a(3) = 3: a(4) = 4
Call p1(a)
End Sub
Tablica jest przekazywana przez zmienną
Sub p1(Tabl() As Integer)
Sum = 0
i = 0
For Each t In Tabl
i = i + 1
Sum = Sum + t
Next t
MsgBox "Suma elementów tablicy " & Sum '10
For j = 1 To i
Tabl(j) = Tabl(j) + 1
Next
End Sub
Sub p2()
Dim a(1 To 4) As Integer
a(1) = 1: a(2) = 2: a(3) = 3: a(4) = 4
Call p1(a)
MsgBox "Tablica " & a(1) & "," & a(2) & "," & a(3) & "," & a(4) '2,3,4,5
End Sub
Wykorzystanie zmiennych globalnych
Dim agl As Integer
Sub p1()
Dim a As Integer
a = 15
MsgBox "procedura p1, zmienna lokalna a=" & a & " zmienna globalna agl=" & agl
agl = 1000
End Sub
Sub p2()
a = 2: agl = 7
Call p1
MsgBox "procedura p2, zmienna lokalna a=" & a & " zmienna globalna agl=" & agl
End Sub
Przykład
Jakie komunikaty pojawią się na ekranie?
Dim glo As Integer
Sub p1(a As Integer, ByVal b As Integer)
a = 1: b = 20: glo = 300
MsgBox "W a=" & a & " b=" & b & " glo=" & glo
End Sub
Sub p2()
Dim i As Integer, j As Integer
i = 12: j = 14: glo = 123
MsgBox "Z i=" & i & " j=" & j & " glo=" & glo
Call p1(i, j)
MsgBox "Z i=" & i & " j=" & j & " glo=" & glo
End Sub
funkcje użytkownika
Funkcje zwracają wartość. Tak jak procedury mogą mieć parametry.
Deklaracja funkcji
[Private|Public |Static] Function nazwa_f [(lista_pf)][As typ]
[instrukcje]
[Exit Function]
[instrukcje]
End Function
gdzie:
nazwa_f - nazwa funkcji musi spełniać takie same warunki jak nazwa procedury
Exit Function - instrukcja powodująca natychmiastowe wyjście z funkcji
lista_pf - (lista parametrów formalnych) lista zmiennych oddzielonych przecinkami
nazwa_par
nazwa par As typ_parametru
ByVal nazwa_par
ByVal nazwa_par As typ_parametru
typ - opcjonalny, typ wartości zwracanych przez funkcję
End Function - koniec funkcji
Wywołanie funkcji
zmienna = nazwa_f [(lista_pa)]
Uwaga: Typ zmiennej musi się zgadzać z typem określonym w deklaracji
zmienna = Skoroszyt!nazwa_f [(lista_pa)]
Przykład
Funkcja zwracająca łańcuch w odwrotnej kolejności
Function odw(a As String) As String
Dim j As Long
n = Len(a)
odw = ""
For i = 0 To n - 1
j = n - i
p = VBA.Mid(a, j, 1)
odw = odw & p
Next i
End Function
Sub zew()
Dim a As String, b As String
a = "zajęcia z VBA"
b = odw(a)
MsgBox b `ABV z aicęjaz
End Sub
Funkcja VBA Mid
Mid (łańcuch, numer_poczatkowy, długość)
Zwraca łańcuch o długości długość, pobrany z łańcucha począwszy od znaku numer_poczatkowy
gdzie:
numer_poczatkowy - musi być typu long
długość - jeśli pominięty wycina znaki do końca łańcucha
1
Wyszukiwarka