tworzenie prostych formularzy w excelu

drukuj

anuluj

Gdy forma jest tre

2001-09-6

Grzegorz D

browski

Tworzenie prostych formularzy w Excelu

kszo

ść

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

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.

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

kosztów, postanawiamy wykorzysta

do pracy wysłu

ony pakiet biurowy, mog

cy w wielu przypadkach

zast

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

źć

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

rachunku, który

dostali

my w dowolnym sklepie. Przykładowy arkusz, stanowi

cy podstaw

do dalszej pracy, mo

na znale

źć

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

, 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

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

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

w dalszej cz

ęś

ci tekstu.

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

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

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

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

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

For... Next. Pozwala

ona na cykliczne wykonywanie umieszczonych wewn

trz niej instrukcji. Poza tym nieco inaczej ni

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

polecenia OLEObjects("ComboBox5"). Nasz program

e wygl

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

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

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

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

to w miar

przyst

pnie.

dy komponent typu ActiveX generuje zestaw tzw. zdarze

. Maj

one miejsce, gdy np. wybierzemy co

listy, wci

niemy lub pu

cimy przycisk myszy w obr

bie kontrolki. Mo

emy obsłu

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

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

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

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

jest umieszczenie w kolumnie Podatek formuł typu: =G17*K17. Arkusz po tych zmianach mo

skopiowa

z płyty CD (faktura4.xls).

Co jeszcze mo

na zrobi

Nast

pnie kroki tworzenia arkusza s

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

np. o wyborze towaru z listy i automatycznym wstawianiu cen czy te

generowaniu osobnego, pomocniczego arkusza do wydruku. Wszystko to mo

na zrobi

, opieraj

c si

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

Wyszukiwarka

Podobne podstrony:
Formularz w Excelu z formatowaniem warunkowym
Ochrona formuł w Excelu
Tworzenie i modyfikacja formularza
Tworzenie złożonych formuł warunkowych
Tworzenie złożonych formuł warunkowych, Dokumenty(1)
10 Tworzenie poprawnych formuł1
Formaty niestandardowe w Excelu przydatne w budowaniu formularzy
HTTPD, J FORMS, Tworzenie formularzy HTML, mechanizm CGI
Tworzenie formularzy - Access, ściągi
Wykorzystanie Formularzy w tworzeniu arkuszy dynamicznych, Różne Dokumenty, MARKETING EKONOMIA ZARZĄ
Lekcja 5 Tworzenie formuł
Przygotowanie formularza z formantami w Excelu
Tworzenie formularzy do wypełniania lub drukowania przez użytkowników w programie Word
Cw3 Tworzenie formularzy
#9 OpenOffice Math – tworzenie formuł
Tworzenie formuł

więcej podobnych podstron