drukuj
anuluj
Gdy forma jest tre
ś
ci
ą
2001-09-6
Grzegorz D
ą
browski
Tworzenie prostych formularzy w Excelu
Wi
ę
kszo
ść
u
Ŝ
ytkowników pakietu biurowego Microsoftu nie stara si
ę
nawet korzysta
ć
z mo
Ŝ
liwo
ś
ci oferowanych
przez formularze i VBA. Tymczasem nie jest to wcale takie trudne, a cz
ę
sto ułatwia
Ŝ
ycie przeci
ę
tnego zjadacza
cyfrowego chleba.
Wyobra
ź
my sobie nast
ę
puj
ą
c
ą
sytuacj
ę
: na skutek skrajnie niekorzystnego systemu ubezpiecze
ń
zdrowotnych jeste
ś
my zmuszeni do samodzielnego rozliczania si
ę
z pobliskim urz
ę
dem skarbowym. Nasza
rado
ść
z szansy usamodzielnienia si
ę
i perspektyw na
ś
wietlan
ą
przyszło
ść
nie trwa jednak długo.
U
ś
wiadamiamy sobie bowiem, i
Ŝ
comiesi
ę
czne wystawianie faktur wi
ąŜ
e si
ę
co najmniej z jednym popołudniem
w miesi
ą
cu, sp
ę
dzonym nad bloczkiem formularzy i kalkulatorem. Owocuje to desperack
ą
ide
ą
zaprz
ę
gni
ę
cia do
Ŝ
mudnego procesu fakturowania zakurzonego peceta młodszego brata. Poniewa
Ŝ
nie chcemy niepotrzebnie
mno
Ŝ
y
ć
kosztów, postanawiamy wykorzysta
ć
do pracy wysłu
Ŝ
ony pakiet biurowy, mog
ą
cy w wielu przypadkach
zast
ą
pi
ć
wyspecjalizowane narz
ę
dzia ksi
ę
gowe.
Jest to mo
Ŝ
liwe dzi
ę
ki gotowym komponentom, umo
Ŝ
liwiaj
ą
cym budowanie formularzy. Dodatkowym atutem
w przypadku MS Office (i innych pakietów tego typu) jest mo
Ŝ
liwo
ść
skojarzenia takich elementów z fragmentami
programów napisanych w odpowiednim j
ę
zyku programowania. Microsoft zaadaptował do tego celu Visual
Basica, tworz
ą
c jego odmian
ę
o nazwie VBA (Visual Basic for Applications).
Zalet VBA mo
Ŝ
na wyliczy
ć
co najmniej kilka. Najwa
Ŝ
niejsze z nich to:
- prosta składnia j
ę
zyka;
- obiektowo
ść
VB, umo
Ŝ
liwiaj
ą
ca współprac
ę
makr z dokumentami Office'a;
- mo
Ŝ
liwo
ść
wykorzystania podczas pracy do
ś
wiadcze
ń
wyniesionych z programowania w j
ę
zyku Visual
Basic lub VBScript.
--
Info
Visual Basic w Excelu
http://vba.profit.pl
Na płycie CD w katalogu Porady | Formularze w Excelu znajdują się pliki XLS omawiane w artykule.
Faktura od podstaw
Na ka
Ŝ
dym rachunku VAT musz
ą
znale
źć
si
ę
okre
ś
lone informacje. S
ą
to przede wszystkim dane sprzedawcy i
nabywcy wyszczególnionych towarów i usług, data i miejsce wystawienia faktury oraz kwota transakcji. Szablon
prostej faktury powinni
ś
my wykona
ć
w Excelu w ci
ą
gu kilku minut - jako wzorca mo
Ŝ
emy u
Ŝ
y
ć
rachunku, który
dostali
ś
my w dowolnym sklepie. Przykładowy arkusz, stanowi
ą
cy podstaw
ę
do dalszej pracy, mo
Ŝ
na znale
źć
na
płycie CD doł
ą
czonej do bie
Ŝą
cego numeru CHIP-a (patrz: ramka Info), w pliku Faktura1.xls.
Rozwijamy skrzydła
Tworzenie faktury rozpoczniemy od jej najprostszych elementów. MS Office udost
ę
pnia gotowe komponenty do
budowy formularzy, tzw. formanty. Jednym z nich jest lista rozwijalna, pozwalaj
ą
ca na wybieranie spo
ś
ród
Page 1 of 5
Tworzenie prostych formularzy w Excelu
2008-10-27
http://www.chip.pl/archiwum/printversion/printversion_12903.html
wcze
ś
niej okre
ś
lonych warto
ś
ci. U
Ŝ
yjemy jej m.in. do zaznaczania na fakturze u
Ŝ
ywanych stawek VAT i
jednostek.
Najpierw musimy wy
ś
wietli
ć
na ekranie stosown
ą
palet
ę
. Słu
Ŝ
y do tego polecenie Widok | Paski narz
ę
dzi |
Formularze. Z paska wybieramy formant Pole kombi i "rysujemy" pole w komórce okre
ś
laj
ą
cej jednostk
ę
sprzedawanego produktu (rys. u góry s
ą
siedniej strony). Zawarto
ść
listy mo
Ŝ
emy okre
ś
li
ć
, wpisuj
ą
c jej kolejne
pozycje w s
ą
siaduj
ą
cych ze sob
ą
komórkach arkusza i wskazuj
ą
c je Excelowi. Umówmy si
ę
,
Ŝ
e na dodatkowe,
nie drukowane dane przeznaczymy osobny arkusz skoroszytu. W przykładowym pliku arkusz ten nosi nazw
ę
pomocniczy. Klikamy nasz
ą
list
ę
prawym przyciskiem myszy, wybieramy z menu podr
ę
cznego opcj
ę
Formatuj
for- mant i zaznaczamy kart
ę
Formant. Naciskamy teraz przycisk obok pola Zakres wej
ś
ciowy, przechodzimy do
arkusza pomocniczego i zaznaczamy komórki z pozycjami listy (w przykładowym skoroszycie b
ę
dzie to zakres
komórek A2-A4 - patrz rys. obok). Po naci
ś
ni
ę
ciu klawisza [Enter] odwołanie do nich zostanie wpisane do
wspomnianego pola Zakres wej
ś
ciowy. Nast
ę
pnie klikamy przycisk OK. Na naszej li
ś
cie pojawi
ą
si
ę
pozycje "---",
"szt.", "kg". Mo
Ŝ
emy teraz skopiowa
ć
pole listy (poleceniami Kopiuj i Wklej z menu podr
ę
cznego) do pozostałych
komórek kolumny okre
ś
laj
ą
cej jednostki towarów.
Lista li
ś
cie nierówna
W MS Office spotkamy si
ę
z dwoma rodzajami komponentów, u
Ŝ
ywanych do tworzenia formularzy. Do pierwszej
grupy nale
Ŝ
y lista u
Ŝ
yta przed momentem. Istniej
ą
jednak bardziej zaawansowane formanty (tzw. kontrolki
ActiveX), których zastosowanie wyja
ś
ni
ę
w dalszej cz
ęś
ci tekstu.
Wy
ś
wietlamy na ekranie pasek narz
ę
dzi Przybornik formantów. Na pozór jest on bardzo podobny do palety
Formularze, zawiera jednak formanty ActiveX. Umie
ść
my Pole kombi z tego paska w pierwszej od góry komórce
kolumny Stawka VAT naszego arkusza. Zauwa
Ŝ
my,
Ŝ
e operacji tej towarzyszy pojawienie si
ę
dodatkowego
paska, na którym umieszczony jest tylko jeden przycisk. Pozwala on na wł
ą
czanie i wył
ą
czanie tzw. trybu
projektowania formularza. Po wył
ą
czeniu tego przycisku b
ę
dziemy mogli jedynie wprowadza
ć
dane do arkusza,
bez mo
Ŝ
liwo
ś
ci modyfikacji wła
ś
ciwo
ś
ci formantów. Identyczny przycisk znajdziemy na pasku Przybornik
formantów.
Gdy wybierzemy z menu podr
ę
cznego nowego pola kombi polecenie Formatuj formant, przekonamy si
ę
,
Ŝ
e
w oknie o tej samej nazwie brakuje karty Formant. Jej bardzo rozbudowany odpowiednik stanowi okienko
Properties (rys. u góry strony), wy
ś
wietlane po u
Ŝ
yciu opcji Wła
ś
ciwo
ś
ci z menu kontekstowego formantu. Jest
ono znane wszystkim programistom korzystaj
ą
cych z narz
ę
dzi RAD (Visual Basic, Delphi itd.). Pozwala na
ś
cisłe
okre
ś
lenie nie tylko wygl
ą
du, ale i zachowania komponentu w ró
Ŝ
nych sytuacjach. Jedn
ą
z wa
Ŝ
niejszych dla nas
cech, okre
ś
lanych za pomoc
ą
okna Properties, jest nazwa kontrolki. Dzi
ę
ki niej b
ę
dziemy mogli si
ę
odwoła
ć
np.
do zawarto
ś
ci listy, u
Ŝ
ywaj
ą
c kodu VBA.
Własny interfejs: tworzenie formularzy w aplikacjach
MS Office nie jest trudne, a moŜe zaoszczędzić nam
nieco czasu.
Zaczynamy kodowa
ć
Nazwa komponentu przechowywana jest w polu (Name) - ujrzymy je u góry listy zawartej w oknie Properties. Je
ś
li
skopiujemy utworzony przed chwil
ą
komponent, zauwa
Ŝ
ymy,
Ŝ
e Excel automatycznie zmienił nazw
ę
nowej kontrolki.
Dzieje si
ę
tak, gdy
Ŝ
nazwa komponentu musi by
ć
unikatowa - w obr
ę
bie skoroszytu nie mo
Ŝ
emy umie
ś
ci
ć
dwóch
obiektów o tych samych nazwach.
Page 2 of 5
Tworzenie prostych formularzy w Excelu
2008-10-27
http://www.chip.pl/archiwum/printversion/printversion_12903.html
Utwórzmy zatem odpowiedni
ą
liczb
ę
pól kombi (w naszym przykładowym arkuszu b
ę
dzie to dziesi
ęć
obiektów o
nazwach od ComboBox1 do ComboBox10). Posłu
Ŝą
one do wyboru stawki VAT dla danego produtku. Mo
Ŝ
emy
skorzysta
ć
z pliku Faktura2.xls z CHIP- -CD. Umieszczeniem na listach odpowiednich warto
ś
ci zajmie si
ę
program,
który za chwil
ę
napiszemy.
Przechodzimy do edytora VBA (Narz
ę
dzia | Makro | Edytor Visual Basic lub skrót [Alt]+[F11]). W oknie
projektu, standardowo umieszczonym po lewej stronie ekranu, klikamy dwa razy gał
ąź
VBAProjectMicrosoft Excel
Objects ThisWorkbook. Spowoduje to otwarcie okienka edycyjnego. Jest ono wyposa
Ŝ
one w dwie listy rozwijalne (w
miejscu menu). Wybieramy z lewej pozycj
ę
Workbook, z prawej - Open. Dzi
ę
ki temu b
ę
dziemy mogli utworzy
ć
procedur
ę
o nazwie Workbook_open, wykonywan
ą
automatycznie podczas otwierania skoroszytu. Dzi
ę
ki niej dodamy
kolejne pozycje do list ComboBox.
Aby wstawi
ć
ła
ń
cuch znakowy do listy, musimy wskaza
ć
pełn
ą
nazw
ę
obiektu i odpowiedniej, skojarzonej z nim
procedury (tzw. metody). Mo
Ŝ
emy to zrobi
ć
np. tak:
Worksheets("faktura"). _
ComboBox1.AddItem "22%"
Instrukcja taka oznacza to,
Ŝ
e w arkuszu o nazwie faktura znajduje si
ę
obiekt ComboBox1 (pierwszy z
utworzonych przez nas komponentów typu ActiveX). Obiekt typu ComboBox oferuje z kolei metod
ę
AddItem,
dodaj
ą
c
ą
do listy dowolny tekst (w naszym przypadku "22%").
Uwaga: znak podkre
ś
lenia "_" oznacza w j
ę
zyku Visual Basic przeniesienie wiersza. W podawanych w
niniejszym artykule listingach mo
Ŝ
na go usuwa
ć
, wpisuj
ą
c w zamian odpowiednie polecenia w jednej linii.
W podobny sposób musimy doda
ć
pozostałe pozycje odnosz
ą
ce si
ę
do podatku VAT, a wi
ę
c "7%", "0%" i
"zwolniony". Operacj
ę
powinni
ś
my powtórzy
ć
dla wszystkich list. Daje to co najmniej 40 linii kodu. Postaramy si
ę
nieco zredukowa
ć
rozmiar programu.
Po pierwsze, zastosujemy powtórzon
ą
10 razy (dla ka
Ŝ
dego formantu ComboBox) p
ę
tl
ę
For... Next. Pozwala
ona na cykliczne wykonywanie umieszczonych wewn
ą
trz niej instrukcji. Poza tym nieco inaczej ni
Ŝ
we
wcze
ś
niejszym przykładzie "dobierzemy si
ę
" do naszych list rozwijalnych. Zrobimy to mianowicie, korzystaj
ą
c z
obiektu OLEObjects, reprezentuj
ą
cego zbiór wszystkich formantów ActiveX umieszczonych w danym arkuszu. Aby
uzyska
ć
dost
ę
p np. do kontrolki ComboBox5, wystarczy u
Ŝ
y
ć
polecenia OLEObjects("ComboBox5"). Nasz program
mo
Ŝ
e wygl
ą
da
ć
tak, jak na poni
Ŝ
szym wydruku:
Private Sub Workbook_Open()
Dim i
With Worksheets("faktura")
For i = 1 To.OLEObjects.Count
.OLEObjects("ComboBox" + _
CStr(i)).Object.AddItem "22%"
.OLEObjects("ComboBox" + _
Zaczynamy programować: widok okna Properties,
ułatwiającego kontrolę nad formantami, ucieszy
zapewne kaŜdego programistę.
Page 3 of 5
Tworzenie prostych formularzy w Excelu
2008-10-27
http://www.chip.pl/archiwum/printversion/printversion_12903.html
CStr(i)).Object.AddItem '7%'
.OLEObjects("ComboBox" + _
CStr(i)).Object.AddItem "0%"
.OLEObjects("ComboBox" + _
CStr(i)).Object.AddItem "zw."
Next i
End With
End Sub
Po uzupełnieniu tre
ś
ci procedury mo
Ŝ
emy zapisa
ć
i zamkn
ąć
nasz arkusz (na tym etapie mo
Ŝ
na skorzysta
ć
z
pliku formularz3.xls z płyty CD). Po ponownym otwarciu dokumentu wł
ą
czamy obsług
ę
makropolece
ń
klikni
ę
ciem
przycisku Wł
ą
cz makra w wy
ś
wietlonym przez Excel oknie dialogowym. Przekonamy si
ę
,
Ŝ
e wszystkie listy
rozwijalne w kolumnie arkusza o nazwie Stawka VAT zawieraj
ą
odpowiednie wpisy.
Sumowanie i zliczanie
Zajmiemy si
ę
teraz obliczaniem cen, stawek VAT i wyliczeniem warto
ś
ci, na jak
ą
b
ę
dzie opiewa
ć
faktura. Na
pocz
ą
tek wró
ć
my do trybu projektu formularza (przycisk Tryb projektowania na pasku Przybornik formantów).
Liczby w kolumnie Warto
ść
netto faktury wyliczymy, posługuj
ą
c si
ę
prost
ą
formuł
ą
. W naszym przykładowym
arkuszu wpisujemy do komórki G17 formuł
ę
: =E17*F17 i kopiujemy j
ą
do pozostałych komórek kolumny Warto
ść
netto.
Nieco inaczej wyliczymy warto
ś
ci w kolumnie Podatek. B
ę
d
ą
one zale
Ŝ
ały od tego, co wybierzemy z list
rozwijalnych w szpalcie Stawka VAT. Warto
ś
ci podatku mo
Ŝ
emy wyliczy
ć
na kilka sposobów. Jeden z łatwiejszych
to utworzenie procedury obsługi zdarzenia Change ka
Ŝ
dej z list. Brzmi to nieco gro
ź
nie, postaram si
ę
jednak
wyja
ś
ni
ć
to w miar
ę
przyst
ę
pnie.
Ka
Ŝ
dy komponent typu ActiveX generuje zestaw tzw. zdarze
ń
. Maj
ą
one miejsce, gdy np. wybierzemy co
ś
z
listy, wci
ś
niemy lub pu
ś
cimy przycisk myszy w obr
ę
bie kontrolki. Mo
Ŝ
emy obsłu
Ŝ
y
ć
ka
Ŝ
d
ą
z takich typowych sytuacji
w wybrany przez siebie sposób. Robimy to, tworz
ą
c procedur
ę
o odpowiedniej nazwie. Podprogram ten zostanie
wykonany w momencie, gdy wyst
ą
pi dane zdarzenie.
W naszym przypadku chodzi o proste zdarzenie Change, generowane w momencie wyboru pozycji z listy
wybieralnej lub wpisania do niej tekstu.
Poniewa
Ŝ
nie zawsze wiemy, czy u
Ŝ
ytkownik arkusza najpierw wpisze cen
ę
produktu czy te
Ŝ
wybierze stawk
ę
VAT, nie mo
Ŝ
emy wyliczy
ć
ceny brutto w momencie wyboru pozycji z listy rozwijalnej. Zamiast tego zapiszemy
informacj
ę
o wybranej z listy warto
ś
ci w ukrytej komórce arkusza. Aby utworzy
ć
stosown
ą
procedur
ę
, klikamy
dwukrotnie (b
ę
d
ą
c w trybie projektu) pierwsz
ą
z naszych list. Spowoduje to automatyczne przej
ś
cie do edytora VBA
i wygenerowanie nagłówków procedury ComboBox1_Change. Wpisujemy w niej tekst:
Select Case ComboBox1.Value
Case "22%"
Gotowy formularz faktury moŜemy wzbogacić
dowolnymi elementami. Dobrym pomysłem będzie
np. dodanie list z oferowanym asortymentem.
Page 4 of 5
Tworzenie prostych formularzy w Excelu
2008-10-27
http://www.chip.pl/archiwum/printversion/printversion_12903.html
Copyright © 2001-2007
Range("K17").Value = 0.22
Case "7%"
Range("K17").Value = 0.07
Case "%", "zw."
Range("K17").Value = 0
End Select
Opis instrukcji Case mo
Ŝ
na znale
źć
w Pomocy edytora VBA. Mówi
ą
c w skrócie, pozwala ona na podj
ę
cie
okre
ś
lonych działa
ń
w zale
Ŝ
no
ś
ci od warto
ś
ci parametru (w naszym przypadku zawarto
ś
ci listy). Poszczególne
fragmenty procedury skutkuj
ą
wpisaniem odpowiedniej stawki podatku do pomocniczej komórki K17 (Range
("K17"). Value =...). Podobn
ą
operacj
ę
wykonujemy w odniesieniu do pozostałych list ComboBox. Kolejn
ą
czynno
ś
ci
ą
jest umieszczenie w kolumnie Podatek formuł typu: =G17*K17. Arkusz po tych zmianach mo
Ŝ
na
skopiowa
ć
z płyty CD (faktura4.xls).
Co jeszcze mo
Ŝ
na zrobi
ć
?
Nast
ę
pnie kroki tworzenia arkusza s
ą
ju
Ŝ
banalne - musimy wyliczy
ć
warto
ś
ci brutto poszczególnych pozycji i
całkowit
ą
kwot
ę
faktury. Jedynie od naszej inwencji zale
Ŝą
jednak ewentualne usprawnienia w funkcjonowaniu
skoroszytu. Warto pomy
ś
le
ć
np. o wyborze towaru z listy i automatycznym wstawianiu cen czy te
Ŝ
o
generowaniu osobnego, pomocniczego arkusza do wydruku. Wszystko to mo
Ŝ
na zrobi
ć
, opieraj
ą
c si
ę
na
wskazówkach zawartych w niniejszym artykule oraz wykorzystuj
ą
c pomoc Excela i edytora VBA. Cz
ęść
dodatkowych własno
ś
ci zaimplementowano w przykładowym arkuszu faktura5.xls. n
drukuj
anuluj
Page 5 of 5
Tworzenie prostych formularzy w Excelu
2008-10-27
http://www.chip.pl/archiwum/printversion/printversion_12903.html