Visual Basic for Applications (VBA) w Excel - wstep do programowania

From Training Material
Jump to navigation Jump to search

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

Option Explicit.JPG

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
Is

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

  1. Napisz program, który będzie wyświetlał wprowadzone dane.
  2. 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.
  3. Napisz program sprawdzający czy podana liczba jest liczbą parzystą, program powinien wyświetlać wprowadzoną liczbę oraz stosowny komunikat.
  4. Napisz program, który będzie żądał podania 2 liczb, po czym wyświetli większą z nich.
  5. Napisz program, który będzie żądał podania 3 liczb, po czym wyświetli największą z nich.
  6. 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

  1. Napisz program, który wprowadzi do komórek od A1 do A20 kolejne liczby od 1 do 20.
  2. 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)
  3. 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.
  4. 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

  1. Resize
  2. Union
  3. Intersect
  4. Areas
  5. ThisWorkbook.FullName