niedziela, 19 sierpnia 2007

Procedury i funkcje w VBA

Wygląd - kod procedury

sub moja_nazwa()
  ' ... komentarz
end sub

private - procedura prywatna
public - procedura publiczna
static - procedura statyczna
exit sub - wyjście z procedury
optional - argument opcjonalny np: (optional i as integer = 1)

Gdy na początku umieścimy wpis
Option Private Module - wszystkie procedury w module będą domyślnie typu private
Uruchamianie procedur
  • W edytorze VBA: F5 lub Run | Run Macro
  • Klawiszem skótu
  • Poleceniem menu (należy uprzednio dostosować wygląd menu)
  • Z innej procedury - call procedura lubprocedura
  • Z innego modułu - call module.procedura
  • Z innego skoroszytu - Application.Run "'plik.xls'!procedura"Argumenty procedury
sub procedura ( argumenty)
end sub

Domyślnie argumenty przekazywane są przez referencje - ByRef - czyli poprzez adresy do miejsca w pamięci komputera. Aby przekazywać argumenty przez wartość dopisujemy przed nimi ByVal.

Tu problem pojawi się u programistów C, C++ dla których poprzedzanie argumentów dodatkowym słówkiem (var od variable) kojarzy się dokładnie z czymś odwrotnym czyli przekazywaniem przez referencje. Cóż...
Na szczęście roztrzygnięcie, kto jest winny takiemu bajzlowi nie trzeba się długo zajmować wystarczy sprawdzić co było pierwsze C czy MS :-)

Możemy też obywać się bez komunikatów o błędach

Wykonanie skryptu przeskakuje do następnej operacji po napotkaniu błędu
On Error Resume Next

lub Typowe i nielubiane GOTO
On Error GoTo bledy
' ...
' mięso procedury / funkcji
' ...
GoTo koniec
bledy:
if Err == X then ' (Err <> 0)
  MsgBox Error(Err)
end if
koniec:

a pod koniec procedury sprawdzamy wartość zmiennej systemowej Err tworząc swoje własne, bardziej zrozumiałe komunikaty.

Przy budowie własnych funkcji i procedur przydają się także:

' bez odświeżania po różnych operacjach
Applicatoin.ScreenUpdate = False

' wielkość znaków staje się nierozróżnialna
Option Text Compare

' brak możliwości ręcznego zastopowania makra
Application.EnableCancelKey = xlDisabled

Funkcje nie różnią się w zasadzie od procedur niczym oprócz tego, że zwracają jakąś wartość czyli używamy np:

function moja_funkcja(argument as integer) as integer
  moja_funkcja = -argument
end function

Najciekawszą opcją przy tworzeniu funkcji i procedur jest jednak lista argumentów o zmiennej długości. W zasadzie większość wbudowanych funkcji typu suma, średnia itp na co dzień wykorzystywanych w Excelu bazuje na tym rozwiązaniu.

Aby zbudować taką funkcję należy jako argument podać:
ParamArray MojaLista() as Variant

W ten sposób można zbudować prostą funkcję do liczenia średniej

Function Srednia(ParamArray Lista() as Variant) as Double
dim ile_elementow as integer
 ile_elementow = 0
 For Each element In Lista
   Srednia = Srednia + element
   ile_elementow = ile_elementow + 1
 Next ' Next element
 Srednia = Srednia / ile_elementow
End Function

Do przechodzenia po liście można też stosować LBound(Lista) lub UBound(Lista) - początek i koniec listy.

Tworząc funkcje bazujące na zmiennych pochodzących wprost z arkusza należy pamiętać o typach danych jakie arkusz może przechowywać. Poza tym bardzo istotne jest to, czy faktycznie w danej komórce znajduje się jakaś wartość.
Dobrze jest więc przed pobraniem (wykorzystaniem) danych sprawdzić co takiego faktycznie pobieramy...

Można to zrobić za pomocą funkcji
TypeName()
czyli nazwa typu. Można ją zastosować np:
TypeName(lista(argument))
Funkcja zwraca np Range, Null, Error lub inne.

Aby na czas obliczeń Excel nie przetwarzał zbędnie tego co się zmienia na ekranie można wyłączyć aktualizację ekranu:

Application.ScreenUpdating = False
by później włączyć ją ponownie
Application.ScreenUpdating = True

Aby włączyć przeliczanie funkcji / formuł za każdym razem, gdy cokolwiek w arkuszu ulegnie zmianie:

Function Delikatna()
  Application.Volatile = True
  '...
End Function

W Excel 2007 piszę się jak zwykle nico inaczej:

Function DelikatnaExcel2007()
  Application.Volatile
  '...
End Function

3 komentarze:

  1. Proszę o pomoc.
    Potrzebuję formuły na następujące działanie w arkuszu Exel:
    Suma kolumn od B do AF pomnożona przez kolumnę AM daje AG
    Kolumna AG pomniejszona o kolumnę AH daje AI
    AI + AJ = AL
    Proszę o kontakt pod adresem: markucper@wp.pl

    OdpowiedzUsuń
  2. tak na szybko:

    w kolumnie AG wpisujesz =suma(A2:AF2)*AM2 i kopiujesz w dół w zależności ile masz wierszy

    w polu AI2 wpisujesz =AG2-AH2 i kopiujesz w dół jak poprzednio (można do tego wykorzystać) taki kwadracik który pokazuje się w prawym dolny rogu komórki

    potem w AL2 wpisujesz =AI2+AJ2 kopiowanie jako poprzednio

    OdpowiedzUsuń
  3. Jak ma wyglądać makro, które będzie wykonywać te same czynności dla wszystkich nowotworzonych (o nazwach pobieranych z wierszy wskazywanej kolumny)arkuszy? Mam jedno makro, które tworzy mi arkusze, oraz 3 inne makra, które:
    a/ powinny nadawać nagłówki kolumnom kopiowanym z arkusza źródłowego
    b/ ustawiać szerokość kolumn zgodnie z zawartością
    b/ z kolumny G8 pobierać tytuł tabeli zcalając wiersze B4:K4
    d/ ukrywać kolumnę G
    e/ zaznaczać obszar wydruku (od B6 do ostatniego wiersza z danymi w kolumnie K)
    f/ kończyć pracę w komórce B6.

    Mam problem z napisaniem pętli, która wykonywałaby czynności opisane w podpunktach od a-f w KAŻDYM (o znanej nazwie) utworzonym arkuszu

    OdpowiedzUsuń