Visual Basic for Applications (VBA) w Excel - wstep do programowania
Visual Basic for Applications
VBA - uproszczona wersja języka Visual Basic przygotowana głównie do automatyzacji czynności ale również rozszerzająca możliwości aplikacji w których została zaimplementowana. Obecnie VBA możesz spotkać w programach Microsoft Office, AutoCAD, WordPerfect i wielu innych.
Visual Basic for Application pojawił się w Excelu (od wersji Excel 5) w 1994, wcześniej (do wersji 4) makra pozwalały jedynie automatyzować te czynności, które można było wykonać za pomocą klawiatury. Teraz oprócz możliwości rejestrowania makr możemy też samodzielnie pisać własne procedury.
Trzeba też w tym miejscu zaznaczyć że pojęcie "makro" w przypadku programów pisanych w VBA jest anachronizmem nie odzwierciedlającym rzeczywiście wykonywanych czynności.
Podstawy programowania
Moduły
Moduł jest zbiorem deklaracji i procedur języka Visual Basic for Applications przechowywanych razem jako jedna całość – przechowuje kod źródłowy VBA.
Ważne opcje modułu:
Option Explicit
Option Explicit: opcja ta wymusza jawną deklaracje zmiennych w module.
Automatyczne wstawianie tej opcji w każdym nowo wstawianym module:
wybierz z menu Tools -> Options -> zaznacz Require Variable Declaration
Procedury
Istnieją dwa rodzaje procedur:
- procedury typu Sub
wykonują operację lub serie operacji, lecz nie zwracają żadnej wartości
Składnie procedury typu Sub
[Private | Public | Friend] [Static] Sub name [(arglist)] [statements] [Exit Sub] [statements] End Sub
przykład:
Sub say_hello()
MsgBox "Hello world!"
End Sub
- procedury typu Function (funkcje)
procedury zwracające pojedynczą wartość lub tablicę
składnie procedury typu Function
[Public | Private | Friend] [Static] Function name [(arglist)] [As type] [statements] [name = expression] [Exit Function] [statements] [name = expression] End Function
przykład:
Function poleProstokata(a As Double, b As Double) As Double
poleProstokata = a * b
End Function
Stała
Wartość tekstowa, liczbowa itp, raz ustanowiona nie może ulec zmianie
Składnia:
[Public | Private] Const constname [As type] = expression
przykład:
Const VAT As Double = 0.23
Jeżeli nie określisz jawnie zasięgu stałej (Public/Private) domyślnie zostanie ustawiona jako prywatna.
Zmienna
Do zbierania i przechowywania informacji w komputerze używamy pamięci, kolejny krok to zaadresowanie tej informacji czyli nadanie jej nazwy. Teraz ta część pamięci posiadająca swoją nazwę oraz określony rozmiar (typ danych) nazywamy zmienną. Inaczej mówiąc zmienna jest to miejsce w pamięci, w którym jest przechowywana wartość, do której mamy dostęp poprzez nazwę.
Zasady nazewnictwa zmiennych:
- Mogą zawierać litery, cyfry oraz znak podkreślenia
- Powinny zaczynać się od litery
- Zmienne nie mogą zawierać spacji
- Nie mogą zawierać słów kluczowych dla Visual Basic
- Maksymalna ilość znaków w zmiennej to 255
Typy danych (VBA w Excel 2010)
|
Data type |
Storage size |
Range |
|
Byte |
1 byte |
0 to 255 |
|
Boolean |
2 bytes |
True or False |
|
Integer |
2 bytes |
-32,768 to 32,767 |
|
Long |
4 bytes |
-2,147,483,648 to 2,147,483,647 |
|
(long integer) |
||
|
LongLong |
8 bytes |
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (Valid on 64-bit platforms only.) |
|
(LongLong integer) |
||
|
LongPtr |
4 bytes on 32-bit systems, 8 bytes on 64-bit systems |
-2,147,483,648 to 2,147,483,647 on 32-bit systems, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems |
|
(Long integer on 32-bit systems, LongLong integer on 64-bit systems) |
||
|
Single |
4 bytes |
-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values |
|
(single-precision floating-point) |
||
|
Double |
8 bytes |
-1.79769313486231E308 to |
|
(double-precision floating-point) |
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values |
|
|
Currency |
8 bytes |
-922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
|
(scaled integer) |
||
|
Decimal |
14 bytes |
+/-79,228,162,514,264,337,593,543,950,335 with no decimal point; |
|
+/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is |
||
|
+/-0.0000000000000000000000000001 |
||
|
Date |
8 bytes |
January 1, 100 to December 31, 9999 |
|
Object |
4 bytes |
Any Object reference |
|
String |
10 bytes + string length |
0 to approximately 2 billion |
|
(variable-length) |
||
|
String |
Length of string |
1 to approximately 65,400 |
|
(fixed-length) |
||
|
Variant |
16 bytes |
Any numeric value up to the range of a Double |
|
(with numbers) |
||
|
Variant |
22 bytes + string length (24 bytes on 64-bit systems) |
Same range as for variable-length String |
|
(with characters) |
||
|
User-defined |
Number required by elements |
The range of each element is the same as the range of its data type. |
|
(using Type) |
Deklarowanie zmiennych
Deklarowanie niejawne
zmienne zadeklarowane w sposób niejawny domyślnie otrzymują typ danych VARIANT
przykład niejawnej deklaracji
a = 3
Jeżeli została włączona opcja modułu Option Explicit - deklarowanie niejawne zmiennych nie będzie możliwe.
Deklaracja za pomocą instrukcji Dim
Dim nazwa_zmiennej As Typ Zmiennej
Deklarowanie kilku zmiennych na raz
Dim a, b, c as Integer
Jakiego typu są zmienne a oraz b?
Poprawne deklaracje
Dim a as Integer, b as Integer, c as Integer
lub każda oddzielnie
Dim a as Integer Dim b as Integer Dim c as Integer
Zasięg zmiennych i stałych
DIM - lokalny zasięg zmiennej, ograniczony do procedury lub modułu, w którym została zadeklarowana.
PUBLIC - zmienna dostępna w dowolnym miejscu w programie, w różnych modułach i formularzach.
PRIVATE - zmienna, która będzie widoczne tylko w obrębie modułu lub formularza, w którym została zadeklarowana.
STATIC - pozwala zadeklarować zmienne, któe nie są usunięte z pamięci komputera po zakończeniu działania procedury, ich ostatnia wartość będzie dostępna tylko w następnym wywołaniu procedury w której została zadeklarowana. Po zakończeniu działania całego programu zmienne te zostają usunięte z pamięci.
np.:
Static zmienna as Integer
Public PrzykladowaZmienna As String
Public Const PrzykladowaStala = "tekst umieszczony wewnątrz stałej"
Zmiennej lub stałej, której nadane zostały atrybuty Public muszą być zadeklarowane w sekcji Declarations modułu (czyli na samej górze modułu ponad wszystkimi procedurami)
Czas życia zmiennych
Jest to okres w jakim dana zmienna jest dostępna (tzn. można z niej korzystać).
a) zmienne na poziomie procedur – istnieją od momentu uruchomienia procedury do wykonania polecenia exit lub end (czyli końca procedury)
b) zmienne na poziomie modułu – istnieją od momentu otwarcia zeszytu zawierającego kod do momentu jego zamknięcia.
W celu zadeklarowania zmiennej na poziomie modułu należy umieścić jej deklarację przed pierwszą procedurą w module.
Badanie typów danych
TypeName
Sub jakiToTypDanych()
Dim a, b, c, d, e, f
a = "tekst"
b = 23
c = 4565768
d = 45.67
e = True
f = #1/1/1999#
'wynik Debug.Print zwracany jest do okienka Immediate (ctrl+g)
Debug.Print TypeName(a) 'String
Debug.Print TypeName(b) 'Integer
Debug.Print TypeName(c) 'Long
Debug.Print TypeName(d) 'Double
Debug.Print TypeName(e) 'Boolean
Debug.Print TypeName(f) 'Date
End Sub
IsNumeric
If IsNumeric(x) = True Then MsgBox "liczba" Else MsgBox "Tekst"
Operatory
Wykonują operacje arytmetyczne, logiczne, łańcuchowe, podstawienia lub porównania.
Operatory arytmetyczne
^ Operator potęgowania
- Operator negacji
* Operator mnożenia
/ Operator dzielenia
\ Operator dzielenia całkowitego
Mod Operator modulo (reszta z dzielenia)
+ Operator dodawania
- Operator odejmowania
Operatory porównania
< Operator mniejszości
<= Operator mniejszości lub równości
> Operator większości
>= Operator większości lub równości
= Operator równości
<> Operator różności
Operatory łańcuchowe
+ Operator dodawania
& Operator konkatenacji (łączenia znaków)
Like Operator porównania wartości tekstowych
Operatory logiczne
Not Operator negacji
And Operator iloczynu logicznego, koniunkcji
Or Operator sumy logicznej, alternatywy
Xor Operator wyłączenia
Priorytet operatorów
Operatory te podlegają zasadom pierwszeństwa. Jeżeli w wyrażeniu wystąpi kilka operatorów, każda część jest obliczana i sprawdzana w oparciu o kolejność priorytetów operatorów. Jeżeli wyrażenie zawiera operatory należące do różnych kategorii, najpierw wykonywane są działania operatorów arytmetycznych, potem operatorów porównania i operatorów logicznych.
|
Operatory arytmetyczne |
Operatory porównania |
Operatory logiczne |
Potęgowanie (^) |
Równe (=) |
Not |
Negacja (-) |
Różne (<>) |
And |
Mnożenie, dzielenie (*, /) |
Mniejsze niż(<) |
Or |
Dzielenie całkowite (\) |
Większe niż (>) |
Xor |
Modulo (Mod) |
Mniejsze niż bądź równe (<=) |
Eqv |
Dodawania i odejmowanie (+, –) |
Większe niż bądź równe (>=) |
Imp |
Konkatenacja stringów (&) |
Like |
Wyrażenia warunkowe
Wyrażenie pozwalające rozgałęzić działanie programu. W zależności od spełnionych warunków wykonywane są odpowiednie opcje.
instrukcja If Else
Na początku wykonywany jest test warunku podanego w postaci wyrażenia logicznego. Jeśli wynikiem jest true, wykonywany jest właściwy blok kodu, a jeśli false - alternatywny. Następnie program kontynuuje od pierwszej komendy po end if.
Składnia:
If condition Then [statement] [Else elseStatement]
Można również użyć bardziej rozbudowanej składni:
If condition Then
[statements]
[ElseIf condition-n Then
[elseIfStatements] ...
[Else
[elseStatements]]
End If
Instrukcja If...Else nazywana jest wyrażeniem wzajemnie wykluczającym się, ponieważ może zostać wykonany tylko jeden z zawartych w nim bloków rozkazów.
Przykład:
Sub testIfElse
Dim liczba as Integer
liczba = 53
If liczba <= 10 Then
MsgBox "do 10"
ElseIf liczba <= 100 Then
MsgBox "liczba w przedziale między 11 a 100"
Else
MsgBox "liczba powyżej 100 "
End If
End Sub
instrukcja Select Case
składnia:
Select Case testExpression
[Case expressionList-n
[statements-n]] ...
[Case Else
[elseStatements]]
End Select
Przykład:
Sub testSelectCase
Dim liczba as Integer
liczba = 8
Select Case liczba
Case 1 To 5
Debug.Print "między 1 i 5"
Case 6, 7, 8
Debug.Print " 6 lub 7 lub 8"
Case 9 To 10
Debug.Print "między 9 a 10"
Case Else
Debug.Print "nie między 1 i 10"
End Select
End Sub
Ćwiczenia 1
- Napisz program, który będzie wyświetlał wprowadzone dane.
- Napisz program, który sprawdzi czy podana liczba jest większa, mniejsza czy też równa 2, program powinien wyświetlić wprowadzaną przez użytkownika liczbę oraz stosowny komunikat.
- Napisz program sprawdzający czy podana liczba jest liczbą parzystą, program powinien wyświetlać wprowadzoną liczbę oraz stosowny komunikat.
- Napisz program, który będzie żądał podania 2 liczb, po czym wyświetli większą z nich.
- Napisz program, który będzie żądał podania 3 liczb, po czym wyświetli największą z nich.
- Napisz program, oceniający testy uczniów. Program powinien w zależności od wpisanej przez Ciebie ilości punktów wyświetlać ocenę:
- punkty 91 do 100 - cel
- punkty 81 do 90 - bdb
- punkty 71 do 80 - db
- punkty 61 do 70 - dst
- punkty 51 do 60 - dop
- punkty 0 do 50 - ndst
- > 100 lub < 0 - błąd
Pętle
Pętla - czyli instrukcja wykonująca daną czynność określoną ilość razy bądź aż zostanie spełniony warunek zakończenia pętli.
Pętla For Next
Zawartość pętli For wykonuje się ustaloną liczbę razy. Na początku pętli zmienna sterująca (licznikowa) jest ustawiana na wartość początkową, następnie przy każdym obiegu pętli jej wartość jest zwiększana o jeden (chyba że została ustalona inna wartość kroku), aż do osiągnięcia górnego limitu. Pętla For może odliczać w dół lub zmienna może być modyfikowana o wartość inną niż 1
składnia:
For counter = start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]
Przykład:
Sub testPetliFor()
For licznik = 1 to 5
MsgBox licznik
Next
End Sub
Pętla For Each Next
Pętla for each to odmiana pętli for, która wykonuje się dla każdego elementu kolekcji. Nie musimy wiedzieć, ile jest elementów w kolekcji - zadziała dobrze dla kolekcji 1-elementowej jak i wieloelementowej. Licznikiem petli może być zmienna typu Variant (tak jest najwygodniej)
Składnia
Dim zmienna As Variant 'licznik pętli For Each zmienna In kolekcja 'dzialanie Next zmienna
Przykład
Dim arkusz As Variant For Each arkusz In Sheets arkusz.Visible = True Next arkusz
Pętla Do While/Until
Pętla Do While wymaga zastosowania wyrażenia porównania. Pętla te jest ograniczona słowami kluczowymi Do i Loop. W ciele pętli może znajdować się jedna instrukcja lub cały blok instrukcji VBA. Blok ten jest wykonywany tak długo jak długo podany warunek jest prawdziwy. Ważne jest więc, aby w ciele pętli umieścić instrukcje, które doprowadzą do tego, że podany warunek kiedyś stanie się fałszywy w przeciwnym razie pętla się nie zakończy.
Pętla Do Until wykonuje zawarty w niej blok rozkazów tak długo, jak długo podany warunek jest fałszywy. W ciele pętli może znajdować się jedna instrukcja lub cały blok instrukcji VBA. Blok ten jest wykonywany tak długo jak długo podany warunek jest fałszywy. Ważne jest więc, aby w ciele pętli umieścić instrukcje, które doprowadzą do tego, że podany warunek
kiedyś stanie się prawdziwy w przeciwnym razie pętla się nie zakończy.
Składnia:
Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop
Możesz też użyć poniższej składni:
Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]
Przykład
Sub testPetliDoUntil()
Dim X
Do
X = InputBox("")
Debug.Print X
Loop Until X = ""
End Sub
Exit For/Do
Może się zdarzyć, że konieczne jest opuszczenie pętli przed zakończeniem jej działania. Najczęściej chcemy przerwać pętle gdy dalsze jej działanie doprowadza do błędu. Instrukcją pozwalającą na przerwanie działania pętli jest Exit Do w przypadku pętli Do While|Until bądź Exit For w przypadku pętli For Next. Powoduje ona na natychmiastowe przerwanie działania pętli i przejście do kolejnej linii kodu pod pętlą. Instrukcja Exit przeważnie poprzedzona jest instrukcją warunkową (najczęściej If)
Ćwiczenia 2
1. Jaki będzie wynik działania następujących pętli.
A.
i = 10 Do While i < 10 MsgBox (i) i = i + 1 Loop
B.
i = 10 Do Until i >= 10 MsgBox i i = i + 1 Loop
C.
i = 10 Do MsgBox (i) i = i + 1 Loop While i < 10
D.
i = 1 Do MsgBox (i) i = i + 1 Loop Until i >= 10
E.
i = 13 Do MsgBox (i) i = i - 2 Loop Until i < 2
F.
Dim i As Double For i = 0.9 To 0 Step -0.3 Debug.Print (i) Next
G.
Dim i As Integer For i = 1 To 0 Step -0.3 Debug.Print (i) Next
H.
Dim i As Integer For i = 30 To 0 Step -0.6 Debug.Print (i) Next
2. Napisz program, który będzie wykonywał odliczanie od 10 do 0, po czym wyświetli komunikat "koniec odliczania".
3. Napisz program obliczający sumę 5 kolejnych liczb naturalnych, począwszy od 1.
4. Wypisz tylko liczby parzyste od 2-100, a następnie tylko nieparzyste od 101-200.
Wprowadzanie danych do komórek arkusza
VBA pozwala nam wprowadzać jak i pobierać dane z dowolnych komórek Excela:
Range
Range("A1").Value = "wartość w komórce A1"
Range("A3:B4").Value = "Zakres komórek"
Range("B5,B7,B9").Value = "Wybrane komórki"
Range("D:D 6:6").Value = "wartość na przecięciu kolumny D i wiersza 6"
Range("RangeName").Value = "nazwany zakres"
Range("czary").Font.Bold = True 'ustawienie pogrubienia czcionki dla nazwanego zakresu
Cells
Cells(2,4).Value = "wiersz 2 kolumna 4"
Workbooks("test.xls").Worksheets("Arkusz1").Cells(5, 6).Value = "test"
ActiveCell
ActiveCell.Value = "Aktywna komórka" ActiveCell.Offset(1,0).Value = "pod aktywną komórką" ActiveCell.Offset(-1,0).Value = "nad" ActiveCell.Offset(0,1).Value = "prawa" ActiveCell.Offset(0,-1).Value = "lewa"
Sortowanie danych
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A3") _
, SortOn:=xlSortOnValues, Order:=xlDescending
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:A3")
.Apply
End With
Range("F1:F3").Sort key1:=Range("F1:F3"), _
order1:=xlAscending
Ćwiczenia 3
- Napisz program, który wprowadzi do komórek od A1 do A20 kolejne liczby od 1 do 20.
- Napisz program, który wypełni kolejne 56 komórek w kolumnie B kolorami od 1 do 56. Wykorzystaj kolekcję ColorIndex. (cells.interior.colorindex = 12)
- Napisz program, który począwszy od aktywnej komórki wprowadzi 20 kolejnych liczb naturalnych (zaczynając od 1) a następnie poniżej wprowadzi sumę tych 20 liczb.
- Napisz program, który wygeneruje tabliczkę mnożenia do 100.
Tablice
Tablica jest to zbiór indeksowanych elementów mających ten sam typ danych. Każdy element tablicy posiada unikatowy numer indeksu. Zmiana jednego elementu tablicy nie wpływa na pozostałe elementy tablicy.
Deklarowanie Tablic statycznych
Dim TablicaStatyczna(10) As Integer lub i = Array(1, 2, "eer", 5, 6)
Tablica statyczna przy deklaracji otrzymuje wymiar i rozmiar, którego nie możemy zmodyfikować w kolejnych krokach programu. Wartość 10 umieszczona w nawiasach tablicy informuje nas o wymiarze oraz rozmiarze tablicy. W tym przykładzie mamy do czynienia z tablicą składającą się z 11 elementów oraz 1 wymiaru. Tablice domyślnie są indeksowane od 0, jeżeli chcesz zmienić indeks początkowy możesz to zrobić zgodnie z poniższym przykładem.
Dim TablicaStatyczna(1 to 10) as Integer
Aby zmienić domyślną dolną granicę tablicy należy na szczycie modułu umieścić instrukcji Option Base
Option Base 1
Dim Tablica(10) 'teraz pierwszy numer indeksu wynosi 1
Deklarowanie Tablic dynamicznych
Tablica dynamiczna przy deklaracji jest nie określona, nie posiada ani rozmiaru ani wymiaru.
Dim TablicaDynamiczna() as String
W trakcie trwania programu za pomocą polecenia ReDim zostaje określony wymiar i rozmiar.
Redim TablicaDynamiczna(1 to 3)
Każda kolejna zmiana rozmiaru tablicy domyślnie powoduje usunięcie jej zawartości. Chcąc uniknąć czyszczenia tablicy należy użyć polecenia Preserve.
ReDim Preserve TablicaDynamiczna(1 to 4)
Deklaracja tablicy wielowymiarowej
Dim Tablica(-10 to 0, 1 to 100, 200 to 250) as Integer 'Tablica 3 wymiarowa. Pierwszy wymiar indeksowany od -10 do 0, drugi od 1 do 100 a trzeci od 200 do 250.
Tablica w VBA może otrzymać maksymalnie 60 wymiarów.
Przykład użycia tablicy
Sub TestTablicy
Dim licznik as Integer
Dim Tablica(1 to 3) as string
Tablica(1) = "jeden"
Tablica(2) = "dwa"
Tablica(3) = "trzy"
For licznik = LBound(tablica) To UBound(tablica)
Debug.Print Tablica(licznik)
Next
End Sub
Excel - model obiektów
Właściwości obiektów
Właściwości Application
ActiveCell ' za pomocą ActiveCell możesz odwołać się do aktywnej komórki w arkuszu
ActiveCell.Value = 1 'Do aktywnej komórki wprowadzamy wartość -1 ActiveCell.Font.Bold = True 'Pogrubienie czcionki w aktywnej komórce
ActiveSheet - aktywny arkusz
MsgBox ActiveSheet.Name 'w oknie MsgBox zostanie wyświetlona nazwa aktywnego arkusza
ActiveWindow - Aktywne okno
ActiveWindow.Zoom = 200 'powiększenie zostało ustawione na 200%
ActiveWorkbook - Aktywny skoroszyt
Debug.Print ActiveWorkbook.Path 'Do okna Immediate zwracamy ścieżkę w której jest zapisany aktywny skoroszyt
RangeSelection - Zaznaczony obszar komórek
Debug.Print ActiveWindow.RangeSelection.Address 'Adres zaznaczonego obszaru komórek zostanie wypisany w oknie Immediate
Selection - zaznaczony obiekt lub zbiór obiektów
Selection.Delete 'usuwa zaznaczony obiekt
ThisWorkbook – skoroszyt zawierający wykonywaną procedurę
ThisWorkbook.Save 'zapisuje plik
Metody obiektów
Metoda to inaczej operacja, (funkcja) którą można wykonać na obiekcie.
np.: obiekt Columns posiada metodę AutoFit
Worksheets("Sheet2").Columns.AutoFit
Powyższe polecenie dopasowuje szerokość wszystkich kolumn do ich zawartości w arkuszu Sheet2
Kolejnym przydatnym narzędziem jest metoda copy obiektu Range
np.: polecenie
Worksheets("Sheet1").Range("A1:B2").Copy Worksheets("Sheet2").Range("A2:B4")
kopiuje zawartość zakresu A1:B2 z arkusza Sheet1 do zakresu A2:B4 w arkuszu Sheet2
Zaznaczenie całej kolumny:
ActiveSheet.Columns(1).Select
Zaznaczenie całej kolumny aktywnej komórki:
ActiveCell.EntireColumn.Select
Sprawdzenie, czy istnieje arkusz oraz utworzenie go, gdy nie istnieje
Dim sh As Worksheet
'sprawdzenie czy arkusz istnieje
czujnik = 0
For Each sh In ActiveWorkbook.Sheets
If sh.Name = "nazwy" Then
sh.Activate: czujnik = 1: Exit For
End If
Next sh
'tworzenie nowego arkusza jeśli on nie istnieje
If czujnik = 0 Then
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "nazwy"
End If
Ćwiczenia 4
1 Napisz program zwracający do aktywnej komórki nazwę aktywnego arkusza
2 Napisz program, który zaznaczy komórki w zakresie od C3 do F16 oraz wyświetli komunikat "ilość komórek w zaznaczeniu to"
3 Napisz program, który zaznaczy komórki w zakresie od A1 do B10 a następnie wprowadzi w nie "tekst"
4 Napisz program, który dla zaznaczonych komórek (A1:B10) ustawi formatowanie:
- kolor tekstu na czerwony (Font.Color = vbRed)
- czcionka pogrubiona (Font.Bold = True)
5 Napisz program, który przekopiuje komórki z zakresu A1:B5 w arkuszu "Arkusz1" do arkusza "Arkusz2" i zakresu A6:B10
6 Napisz program, który dla całej kolumny (litera kolumny podana jako parametr) ustawi formatowanie:
- tekst na czerwono
- czcionka pogrubiona
7. W zakresie A1:J10 stwórz szachownice odpowiednio kolorując pola.
Łańcuchy znaków - strings
String - typ danych służący do przechowywania ciągu znaków (zmiennych łańcuchowych).
Sub Przyklad()
Dim łańcuch As String
łańcuch = "VBA"
łańcuch = łańcuch & " - Visual Basic for Applications"
MsgBox łańcuch
End Sub
Wybrane funkcje tekstowe
Mid
Mid(string, start[, length])
zwraca wybrany fragment tekstu (określoną ilość znaków) z tekstu
Sub Przyklad()
Dim tekst As String
tekst = "żwawa żyrafa żłopie"
MsgBox Mid(tekst, 7, 6)
End Sub
Left, Right
Left(string, length) Right(string, length)
zwraca określoną ilość znaków z tekstu licząc od lewej/prawej strony
Sub Przyklad()
Dim tekst As String
tekst = "żwawa żyrafa żłopie"
MsgBox Right(tekst, 6)
End Sub
LTrim, RTrim, Trim
LTrim(string) RTrim(string) Trim(string)
usuwa spacje z lewej, prawej lub obu stron stringu
Sub Przyklad()
Dim tekst As String
tekst = " OOOOO OOOOOO "
MsgBox LTrim(tekst) & vbCrLf & RTrim(tekst) & vbCrLf & Trim(tekst)
End Sub
Split
Split(expression[, delimiter[, limit[, compare]]])
dzieli ciąg znaków na tablicę której elementami są poszczególne wyrazy stringu
Sub Przyklad()
Dim tekst As String, i As Integer
Dim tablica() As String
tekst = "NobleProg Training how YOU want it!"
tablica = Split(tekst, " ")
For i = 0 To UBound(tablica)
MsgBox tablica(i)
Next i
End Sub
Len
Len(string)
zwraca długość stringu (ilość znaków)
Sub Przyklad()
Dim tekst As String
tekst = "Ala ma kota"
MsgBox Len(Tekst)
End Sub
UCase, LCase
UCase(String) LCase(String)
zamienia wielkość znaków na duże lub małe
Sub Przyklad()
Dim tekst As String
tekst = "Ala ma kota"
MsgBox UCase(tekst)
MsgBox LCase(tekst)
End Sub
Replace
Replace(expression, find, replace[, start[, count[, compare]]])
zamienia część stringu na inną
Sub Przyklad()
Dim tekst As String
tekst = "Ala ma kota"
MsgBox Replace(tekst, "ma", "nie ma")
End Sub
Ćwiczenia 5
1 Użytkownik wpisuje imię. Napisz program, który zamieni jego imię w kod składający się z trzech pierwszych liter i ilości znaków np. Jasiu -> Jas5
2. Zamień w podanym przez użytkownika ciągu znaków litery "a" na "x". Następnie zapisz zmiany w aktywnym arkuszu.
3. Stwórz program do pobierania imienia z komórki "A1", a następnie zapisania go dużymi literami w drugim arkuszu
4. Pozwól użytkownikowi wpisać imię. Na jego podstawie określ płeć zakładając, że imię żeńskie kończy się na literę "a"
5. Użytkownik wpisuje trzy wyrazy. Wypisz w arkuszu jakie wyrazy wpisał, jaka jest ich długość i w osobnej komórce napisz, który z tych wyrazów jest najdłuższy.
Pliki
wybrane funkcje
ChDir
ChDir path
zmienia katalog, ścieżkę
chDir "C:\"
CurDir
wyświetla nazwę bieżącego katalogu
ChDrive
ChDrive drive
zmienia dysk
ChDrive ("D")
Name
Name oldpathname As newpathname
zmienia nazwę katalogu
Name "c:\kurs\staranazwa.txt" As "c:\kurs\nowanazwa.txt”
FileCopy
FileCopy source, destination
kopiuje pliki
FileCopy "plik.txt", "b.txt" FileCopy "c:\a.txt", "c:\b.txt"
Kill
Kill pathname
kasuje pliki
Kill "c:\b.txt"
Mkdir
MkDir path
tworzy katalog
MkDir "c:\kurs"
Rmdir
RmDir path
usuwa katalog (nie uda Ci się usunąć katalogu jeżeli będzie zawierał wewnątrz pliki)
RmDir "C:\kurs"
FileDateTime
FileDateTime(pathname)
wyświetla datę i czas, kiedy plik był modyfikowany
FileDateTime("C:\plik.txt")
Dir
Dir[(pathname[, attributes])]
wyświetla pliki z katalogu
MsgBox Dir("") 'wyświetla nazwę pierwszego pliku z bieżącego katalogu
MsgBox Dir ' kolejna nazwa pliku z bieżącego katalogu
FileLen
wyświetla ile znaków zawiera dany plik
1 znak = 1 bajt tak więc dzięki tej funkcji można "zważyć" wielkość pliku
FileLen("c:\a.txt")
GetAttr,SetAttr
GetAttr - pobiera atrybuty plików
SetAttr - ustawia atrybuty dla plików
Dim attr As String
attr = GetAttr("c:\a.txt")
If attr = vbReadOnly Then
Debug.Print "tylko do odczytu"
End If
SetAttr "c:\a.txt", vbNormal + vbReadOnly SetAttr "c:\a.txt", (32 + 1)
te dwie linie powyżej wykonują to samo zadanie – ustawiają parametry plików
Tryby otwarcia pliku - output -> do zapisu, jeśli istnieje to nadpisze dane - append -> do zapisu, jeśli istnieje to doda dane(nie usunie poprzednich) - input -> do odczytu
Zapis do pliku
Open "C:\wpisy.txt" For Append As #1 Write #1, 4563 Close
Odczytanie z pliku
Odczyt wierszami
Dim NumerWiersza Dim OdczytywanyWiersz NumerWiersza = 1 Open "C:\wpisy.txt" For Input As #1 Do While Not EOF(1) Line Input #1, OdczytywanyWiersz Cells(NumerWiersza, 1).Value = OdczytywanyWiersz NumerWiersza = NumerWiersza + 1 Loop Close #1
Ćwiczenia 6
30. Utwórz (za pomocą VBA) na dysku C katalog kurs
31. Napisz program, który po przejściu do katalogu kurs, wyświetli za pomocą funkcji msgbox nazwę katalogu. Stwórz w notatniku plik a.txt o dowolnej treści i zapisz go w katalogu kurs.
32. Napisz program, który skopiuje plik a.txt do katalogu kurs pod nazwą b.txt.
33. Napisz program, który wyświetli rozmiar oraz datę ostatniej modyfikacji pliku b.txt
34. Napisz program, który skasuje plik b.txt z katalogu kurs.
35. Napisz program, który wyświetli nazwę dowolnego pliku z katalogu kurs.
36. Napisz program, który sprawdzi czy plik a.txt ma atrybut tylko do odczytu i jeżeli tak to wyświetli komunikat ”plik jest tylko od odczytu”.
37. Napisz program, który wpisze do arkusza wszystkie nazwy plików z katalogu kurs. (Wcześniej stwórz kilka plików w katalogu kurs)
38. Popraw powyższy program tak, żeby zliczał ilość wypisanych do arkusza plików (tzn oprócz nazw plików niech program pod spodem napisze „liczba plików w katalogu to”.
39. Napisz program który skopiuje z katalogu kurs tylko pliki z rozszerzeniem .txt do dowolnego innego katalogu stworzonego przez program.
40. Napisz program, który zamieni nazwy raportów (z katalogu raporty) tak, żeby między rokiem, miesiącem i dniem nie było żadnych separatorów (np. 20120101.xls).
Błędy
Sub bledy()
Dim x
On Error GoTo kodObslugBledow 'jeżeli wystąpi błąd przejdź pod etykietę kodObslugBledow
' wnetrze procedury generujace bledy
'
Exit Sub 'jezeli nie wystąpi błąd polecenie Exit sub zakończy program
kodObslugBledow:
MsgBox Err.Description, vbCritical, Err.Number 'komunikat o błędzie
'tu umieszczam instrukcje naprawiającą błąd
Resume 'wracam do miejsca wystąpienia błędu
End Sub
Formularze
konwencja nazewnicza dla kontrolek formularza:
Pre Object Example --- ------ ------- cbo Combobox & dropdown Listbox cboChoice chk Checkbox chkEnable cls Class Module clsMultimedia cmd Command Button cmdQuit dat Data datInformation dbc Data-bound combo box dbcEnglish dbl Data-bound list box dblPolicy des Designer desCustom dir Directory List Box dirSource dlg Common Dialog dlgOpen drv Drive List Box drvTarget fil File List Box filSource fra Frame fraLanguage frm Form frmSplash hsb Horizontal Scroll Bar hsbVolume img Image Control imgLogo imgcbo ImageCombo imgcboControl** ils ImageList ilsProgramIcons lbl Label lblComment lin Line linUnderline lst Listbox lstItems mdi MDI Child Form mdiChild mnu Menu mnuFileOpen mod Module modTestProcedures ole OLE oleObject1 opt Option Button optEnglish pic Picture picProduct pnl Panel pnlGroup1 prg ProgressBar prgFileDownload prp Property Page prpUserControl res Resource File resMessages shp Shape shpBlock sld Slider sldVolumeLevel sta StatusBar staConnectionStatus tab TabStrip tabUserChoices tre TreeView treDriveContents txt TextBox txtInfoBlock tmr Timer tmrUpdateClock tlb ToolBar tlbMainControls usr User Control usrMyNoteControl vsb Vertical Scroll Bar vsbTemperature
Skróty klawiaturowe
w edytorze VBA
- ctrl + space
- ctrl + y
- tab
w Excelu
- alt + F11 --> przejście do edytora VBA
- alt + F8 --> lista dostęnych makr
Różne
- Resize
- Union
- Intersect
- Areas
- ThisWorkbook.FullName
