Excel Programowanie dla profesjonalistów


Excel. Programowanie dla
profesjonalistów. Wydanie II
Autorzy: Rob Bovey, Dennis Wallentin,
Stephen Bullen, John Green
Tłumaczenie: Robert Górczyński
ISBN: 978-83-246-2544-4
Tytuł oryginału: Professional Excel Development:
The Definitive Guide to Developing Applications Using
Microsoft Excel, VBA, and .NET (2nd Edition)
Format: 172×245, stron: 1096
Kompendium wiedzy dla programistów Excela!
" Jak wykorzystać nowy interfejs Excela 2007?
" Jak sterować innymi aplikacjami z pakietu Microsoft Office?
" Jak używać Windows API?
Dzięki książce  Excel. Programowanie dla profesjonalistów. Wydanie II poznasz tajniki
tworzenia aplikacji opartych o Microsoft Excel. NaukÄ™ rozpoczniesz od poznania architektury
takich aplikacji. Poznasz też zestaw najlepszych praktyk w programowaniu w VBA.
W kolejnych rozdziałach przyswoisz wiedzę na temat projektowania arkuszy, weryfikacji
danych, sterowania paskami poleceń oraz współpracy z interfejsem typu wstążka (ang.
Ribbon). Ponadto zdobędziesz informacje na temat wykorzystania Windows API, obsługi
błędów, programowania baz danych oraz optymalizacji aplikacji. WSród wielu innych
zagadnień omawianych w tej książce warto także zwrócić uwagę na takie, jak współpraca
z usługami sieciowymi, sterowanie innymi aplikacjami z pakietu Microsoft Office oraz
techniki przetwarzania danych. Książka stanowi kompendium wiedzy na temat programowania
w Srodowisku Microsoft Excel, a o jej jakoSci merytorycznej Swiadczy fakt, iż trójka jej
autorów została wyróżniona przez firmę Microsoft tytułem MVP (Most Valuable Professional).
" Architektura aplikacji opartych o Microsoft Excel
" Najlepsze praktyki tworzenia aplikacji w Excelu i VBA
" Zasady projektowania arkuszy
" Używanie stylów, nazw zdefiniowanych, formatowań warunkowych
" Wykorzystanie kontrolek i zastosowanie dodatków funkcyjnych
" Użycie modułów klas do tworzenia obiektów
" Zarządzanie paskiem poleceń
" Praca z plikami XML
" Wykorzystanie możliwoSci nowego interfejsu Excela 2007
" Użycie Windows API
" Tworzenie formularzy
" Zastosowanie interfejsów oraz ponowne wykorzystanie kodu
" Sytuacje wyjątkowe  obsługa błędów
" Debugowanie i optymalizacja kodu VBA
" Zastosowanie asercji
" Współpraca z bazami danych i techniki przetwarzania danych
" Tworzenie zaawansowanych wykresów
" Sterowanie innymi aplikacjami z pakietu Microsoft Office
" Wykorzystanie języka VB.NET
" MożliwoSci narzędzia Visual Studio Tools for Office System
Wykorzystaj Excel jako fundament efektywnych rozwiązań!
SPIS TRE CI
Podzi kowania 14
O autorach 13
Rozdzia 1. Wst p
O ksi ce 15
Dla kogo przeznaczona jest ta ksi ka? 15
Twórca programowania excelowego 16
Excel jako platforma do tworzenia aplikacji 18
Struktura 21
Przyk ady 22
Wersje obs ugiwane 23
Rodzaje kroju pisma 24
Na p ycie CD 24
Pomoc i wsparcie 25
Witryna internetowa 26
Komentarze czytelników 26
Rozdzia 2. Architektura aplikacji
Koncepcje 27
Wnioski 38
Rozdzia 3. Najlepsze praktyki programowania w Excelu i VBA
Konwencje nazw 41
Najlepsze praktyki organizacji i tworzenia struktury aplikacji 53
Najlepsze praktyki okre laj ce ogólne zasady
tworzenia oprogramowania 58
Wnioski 78
Rozdzia 4. Projektowanie arkusza
Zasady projektowania dobrego interfejsu u ytkownika 79
Wiersze i kolumny programu: podstawowe techniki
tworzenia interfejsu u ytkownika 80
Nazwy definiowane 81
Style 88
Techniki kre lenia interfejsów u ytkownika 94
Weryfikacja danych 99
6 SPIS TRE CI
Formatowanie warunkowe 103
U ywanie kontrolek w arkuszu 108
Przyk ad praktyczny 111
Wnioski 116
Rozdzia 5. Dodatki funkcyjne, ogólne i specjalizowane
dla aplikacji
Cztery etapy rozwoju i dzia ania aplikacji 117
Dodatki b d ce bibliotekami funkcji 120
Dodatki ogólne 127
Dodatki specjalizowane dla aplikacji 127
Przyk ad praktyczny 134
Wnioski 146
Rozdzia 6. Aplikacja dyktatorska
Struktura aplikacji dyktatorskiej 147
Przyk ad praktyczny 161
Wnioski 168
Rozdzia 7. U ywanie modu ów klas do tworzenia obiektów
Tworzenie obiektów 169
Tworzenie kolekcji 173
Wychwytywanie zdarze 179
Generowanie zdarze 182
Przyk ad praktyczny 189
Wnioski 195
Rozdzia 8. Zaawansowane sterowanie paskami polece
Projektowanie paska polece 198
Tablicowe sterowanie paskami polece 199
Zbieranie wszystkiego razem 219
adowanie niestandardowych ikon z plików 227
Podczepianie obs ugi zdarze do kontrolek paska polece 231
Przyk ad praktyczny 240
Wnioski 246
Rozdzia 9. Wprowadzenie do formatu XML
XML 249
Wnioski 271
Rozdzia 10. Wst ka w Office 2007
Podstawowe za o enia RibbonX 273
Wprowadzenie do formatu pliku Open XML w Office 2007 274
Projektowanie Wst ki oraz najlepsze praktyki programowania 278
SPIS TRE CI 7
Dostosowanie opcji Wst ki bazuj cej na tabeli 288
Zaawansowane rozwi zywanie problemów 289
Dalsze pozycje do czytania 297
Portale internetowe 298
Wnioski 299
Rozdzia 11. Tworzenie aplikacji niezale nych od wersji Excela
Pasek polece i Wst ka w pojedynczej aplikacji 301
Inne kwestie dotycz ce programowania w Excelu 2007 315
Bezpiecze stwo w Windows Vista a struktura katalogów 322
Wnioski 326
Rozdzia 12. Zrozumienie i u ywanie wywo a Windows API
Ogólny opis 327
Praca z ekranem 333
Praca z oknami 336
Praca z klawiatur 344
Praca z systemem plików i sieci 349
Przyk ad praktyczny 361
Wnioski 365
Rozdzia 13. Projektowanie formularzy UserForm
i najlepsze praktyki
Zasady 367
Podstawy kontrolek 375
Efekty wizualne 382
Pozycjonowanie i rozmiary formularzy UserForm 390
Kreatory 396
Dynamiczne formularze UserForm 400
Niemodalne formularze UserForm 407
Wyszczególnienie kontrolek 412
Przyk ad praktyczny 418
Wnioski 419
Rozdzia 14. Interfejsy
Co to jest interfejs? 421
Ponowne u ycie kodu 422
Definiowanie w asnych interfejsów 425
Implementacja w asnego interfejsu 426
U ywanie w asnych interfejsów 427
Klasy polimorficzne 429
Polepszanie solidno ci 433
Upraszczanie rozwoju 434
8 SPIS TRE CI
Architektura modu ów rozszerzaj cych 443
Przyk ad praktyczny 445
Wnioski 446
Rozdzia 15. Obs uga b dów VBA
Poj cia obs ugi b dów 447
Zasada pojedynczego punktu wyj cia 456
Prosta obs uga b dów 457
Z o one projekty obs ugi b dów 458
Centralna obs uga b dów 462
Obs uga b dów w klasach i formularzach UserForm 469
Zbieranie wszystkiego razem 470
Przyk ad praktyczny 476
Wnioski 484
Rozdzia 16. Debugowanie kodów VBA
Podstawowe techniki debugowania kodów VBA 485
Okno Immediate (Ctrl+G) 495
Call Stack  stos wywo a (Ctrl+L) 498
Okno Watch 500
Okno Locals 510
Object Browser  przegl darka obiektowa (F2) 511
Tworzenie dzia aj cego otoczenia testowego 514
Stosowanie asercji 517
Debugerskie skróty klawiaturowe,
które powinien zna ka dy programista 518
Wnioski 520
Rozdzia 17. Optymalizacja wydajno ci VBA
Mierzenie wydajno ci 523
Program narz dziowy PerfMon 524
My lenie kreatywne 528
Makrooptymalizacja 534
Mikrooptymalizacja 543
Wnioski 550
Rozdzia 18. Programowanie i bazy danych
Wprowadzenie do baz danych 551
Wprowadzenie do SQL 568
Dost p do danych za pomoc ADO 572
Dalsze pozycje do czytania 586
Wnioski 587
SPIS TRE CI 9
Rozdzia 19. Programowanie z u yciem bazy danych Access
i SQL Server
Uwaga dotycz ca przyk adowej bazy danych Northwind 589
Projektowanie warstwy dost pu do danych 590
Praca z bazami danych Microsoft Access 594
Praca z bazami danych Microsoft SQL Server 603
Rozbudowa bazy danych Access do SQL Server 613
Dalsze pozycje do czytania 618
Przyk ad praktyczny 619
Wnioski 629
Rozdzia 20. Techniki przetwarzania danych
Struktury danych Excela 631
Funkcje przetwarzania danych 637
Zaawansowane funkcje 648
Wnioski 656
Rozdzia 21. Zaawansowane techniki tworzenia wykresów
Podstawowe techniki 657
Techniki VBA 674
Wnioski 679
Rozdzia 22. Sterowanie innymi aplikacjami Office
Podstawy 681
Modele obiektowe g ównych aplikacji Office 695
Dalsze pozycje do czytania 707
Przyk ad praktyczny 707
Wnioski 708
Rozdzia 23. Po czenie Excela i Visual Basica 6
Witaj wiecie ActiveX DLL 710
Dlaczego u ywa VB6 ActiveX DLL w projektach Excel VBA? 725
In-process kontra out-of-process 739
Automatyzacja Excela z VB6 EXE 740
Dodatki COM 748
Dodatek COM Witaj wiecie 748
Projektant dodatków (Add-in Designer) 752
Instalacja 755
Zdarzenia AddinInstance 757
Obs uga paska polece 759
Dlaczego warto u ywa dodatku COM? 762
Automatyzacja dodatków 764
Przyk ady praktyczne 767
Wnioski 779
10 SPIS TRE CI
Rozdzia 24. Excel i VB.NET
Podstawy platformy .NET 782
Visual Basic.NET 783
Usuwanie b dów 807
U yteczne narz dzia programistyczne 816
Automatyzacja Excela 819
Zasoby w rozwi zaniach .NET 826
Pobieranie danych za pomoc ADO.NET 827
Dalsze pozycje do czytania 832
Dodatkowe narz dzia programistyczne 833
Fora 834
Przyk ad praktyczny  narz dzie raportuj ce PETRAS
w technologii .NET 834
Wnioski 848
Rozdzia 25. Tworzenie zarz dzanych dodatków COM
za pomoc VB.NET
Wybór narz dzi programistycznych 852
Tworzenie dodatku zarz dzanego COM 853
Budowanie interfejsu u ytkownika 870
Tworzenie zarz dzanych dodatków automatyzacji 888
R czne rejestrowanie i wyrejestrowanie dodatków COM 899
U ywanie klas w VB.NET 900
U ywanie klasycznego ADO w celu eksportu danych do Excela 906
Opakowywanie dodatków COM 910
Blogi zwi zane z tematem 919
Dodatkowe narz dzia programistyczne 920
Przyk ad praktyczny  narz dzie raportuj ce PETRAS
w technologii .NET 921
Wnioski 929
Rozdzia 26. Tworzenie rozwi za Excela za pomoc Visual Studio
Tools for Office System (VSTO)
Co to jest VSTO? 932
Kiedy powinni my u ywa VSTO? 939
Praca z dodatkami VSTO 941
Praca z szablonami VSTO i rozwi zaniami w postaci skoroszytu 961
Implementacja i bezpiecze stwo 970
Inne pozycje do czytania 979
Ró ne portale i blogi 979
Dodatkowe narz dzia programistyczne 980
Wnioski 980
SPIS TRE CI 11
Rozdzia 27. XLL i API C
Dlaczego warto tworzy funkcje arkusza na bazie XLL? 983
Tworzenie projektu XLL w Visual Studio 984
Struktura XLL 989
Typy danych XLOPER i OPER 997
Funkcja Excel4 1002
Powszechnie u ywane funkcje API C 1004
XLOPER i zarz dzanie pami ci 1005
Rejestrowanie i wyrejestrowywanie w asnych funkcji arkusza 1006
Przyk adowa funkcja aplikacji 1009
Debugowanie funkcji arkusza 1011
Ró ne tematy 1012
Dodatkowe ród a informacji 1013
Wnioski 1014
Rozdzia 28. Excel i us ugi sieciowe
Us ugi sieciowe 1015
Przyk ad praktyczny 1022
Wnioski 1031
Rozdzia 29. Zapewnianie pomocy, bezpiecze stwa,
pakowanie i rozpowszechnianie
Zapewnianie pomocy 1033
Bezpiecze stwo 1042
Pakowanie 1046
Rozpowszechnianie 1051
Wnioski 1052
Skorowidz 1055
R OZ DZ I A 1 0 .
WST KA W OFFICE 2007
Od czasu wydania pakietu Office 2007 RibbonX i jego interfejs u ytkownika
(Wst ka) sta y si tematem cz stych dyskusji w spo eczno ci programi-
stów Office. Jak mo na si spodziewa po technologii w wersji 1.0, Rib-
bonX nak ada pewne ograniczenia, które uniemo liwiaj wykorzystanie
w pe ni mo liwo ci drzemi cych w tym interfejsie u ytkownika. Jednak
nawet na tak wczesnym etapie powinni my zrozumie podstawowe za o enia
nowego interfejsu, aby budowa w asne, doskonale zaprojektowane interfejsy
u ytkownika.
Innym wyzwaniem jest równoczesna codzienna praca z paskiem polece
oraz technologiami interfejsu u ytkownika RibbonX. Zagadnienie to obej-
muje tworzenie rozwi za , które b d dzia a y z obydwoma interfejsami
u ytkownika, czyli tzw. aplikacji niezale nych od wersji Excela. W tym roz-
dziale zostan przedstawione najlepsze praktyki w zakresie projektowania
Wst ki i programowania RibbonX, natomiast aplikacje dzia aj ce nieza-
le nie od wersji Excela omówimy w rozdziale 11. Tu zajmiemy si tak e
pewnymi nieco bardziej skomplikowanymi problemami aplikacji dyktator-
skich. Bazuj cy na tabelach proces budowania interfejsu u ytkownika paska
polece jest szeroko akceptowany i uznawany za standard. Oznacza to, e do
tego celu powinni my równie u ywa technologii RibbonX i ten temat
te b dzie poruszony w rozdziale.
W pakiecie Office 2007 wprowadzono nowy format zapisu plików o naz-
wie Office Open XML (OOXML lub po prostu Open XML). Oferuje on
struktur szkieletow dla RibbonX  format OOXML i RibbonX s ze sob
ci le powi zane. Nowy format zapisu plików pozwala tak e na tworzenie
dokumentów Office i manipulowanie nimi bez u ywania któregokolwiek
z programów wchodz cych w sk ad pakietu Office. W rozdziale przedstawimy
tak e krótkie wprowadzenie do formatu plików Open XML.
Podstawowe za o enia RibbonX
Przez ponad dekad paski polece by y jedyn technologi interfejsu u yt-
kownika, której obs ug musieli my si zajmowa . Wraz z wprowadzeniem
RibbonX otrzymujemy now technologi oraz nowe za o enia stanowi ce
podstaw interfejsu u ytkownika, które cznie maj ogromny wp yw na
274 ROZDZIA 10. WST KA W OFFICE 2007
nasz sposób pracy. Na pierwszy rzut oka nauka i manipulowanie RibbonX
mo e wydawa si wyzwaniem. Jednak z naszego do wiadczenia wynika, e
prawdziwym wyzwaniem jest równoczesna codzienna praca zarówno z pas-
kami polece , jak i RibbonX.
RibbonX, w porównaniu do pasków polece , ma wady i zalety. Chocia jest
w swojej pierwszej wersji, jego podstawy zosta y ju ustalone i cechy charak-
terystyczne technologii RibbonX mo na podsumowa w nast puj cy sposób.
Opcje pozwalaj ce na dostosowanie do w asnych potrzeb okre lamy
w trakcie projektowania interfejsu. S zdefiniowane za pomoc XML
i przechowywane jako oddzielna cz formatu pliku XML. Jednak wi k-
szo atrybutów kontrolek mo e by zmodyfikowana w trakcie dzia ania
aplikacji przy u yciu VBA (np. w czony/wy czony, etykieta, widocz-
no itd.).
Po otworzeniu skoroszytu zawieraj cego dostosowane do w asnych potrzeb
opcje Wst ki Excel automatycznie odczytuje definicj XML Wst ki,
a nast pnie przyst puje do tworzenia Wst ki okre lonej przez wy-
mienione opcje. Do zainicjalizowania tego procesu nie jest wymagany a-
den kod VBA. W rzeczywisto ci nawet nie ma sposobu, aby uniemo liwi
inicjalizacj tego procesu.
Kiedy skoroszyt zostanie otwarty i jest aktywny, dostosowane do w a-
snych potrzeb opcje Wst ki zostaj zastosowane i s widoczne. Po za-
mkni ciu skoroszytu dostosowane do w asnych potrzeb opcje Wst ki
s automatycznie usuwane.
Po wczytaniu dodatku dowolnego rodzaju jego dostosowane do w asnych
potrzeb opcje Wst ki zostaj zastosowane i widoczne s we wszystkich
otwartych skoroszytach.
Na w asnej Wst ce wszystkie wbudowane kontrolki Wst ki mog by
w czone, nadpisane, wykonane lub zapytane o nag ówek, grafik itd.
Wprowadzenie do formatu pliku Open XML w Office 2007
XML po raz pierwszy wprowadzony zosta w pakiecie Office 2002. Od tej
chwili Microsoft nieustannie rozbudowuje i ulepsza modu XML b d cy
cz ci pakietu Office. Wraz z wydaniem Office 2007 otrzymujemy nowy
format pliku ca kowicie bazuj cy na XML, czyli format Open XML. Format
ten zapewnia mo liwo pracy z dokumentami Office oraz ich tre ci bez
konieczno ci u ywania którejkolwiek z aplikacji wchodz cych w sk ad pakietu
Office. Dzi ki temu zwi ksza si liczba rozwi za alternatywnych s u cych
do generowania dokumentów Office po stronie serwera. Znacznemu u atwie-
niu ulega równie proces wymiany danych mi dzy ró nymi systemami oraz
dokumentami Office.
WPROWADZENIE DO FORMATU PLIKU OPEN XML W OFFICE 2007 275
Ka dy dokument Office bazuj cy na Open XML jest przechowywany
w archiwum ZIP. Wspomniane archiwum stanowi rodzaj pojemnika zawie-
raj cego zarówno dane u ytkownika w formacie XML, jak i inne pliki wraz
z informacjami o stylu, obrazami itd. Kiedy dokument jest zapisany w forma-
cie Open XML, jego zawarto zostaje skompresowana, co skutkuje mniejsz
wielko ci pliku, w porównaniu do pliku w formacie binarnym. Microsoft
twierdzi, e wielko pliku mo na zredukowa nawet o 75%, cho wed ug
naszych obserwacji typowa wielko redukcji mie ci si w okolicach 60  65%.
Poniewa dokument Open XML sk ada si z kilku elementów umiesz-
czonych razem w archiwum ZIP, dokumenty Office w tym formacie s
znacznie odporniejsze na uszkodzenia ni zapisane w formacie binarnym.
W ten sposób zmniejsza si ryzyko utraty informacji ze wzgl du na zniszczone
b d uszkodzone pliki.
Powszechnie spotykanym wymaganiem jest, aby tworzone rozwi zania
wspó dzia a y z ró nymi wersjami Excela. Rodzi to pytania o format pliku,
który powinien zosta zastosowany. Rozwi zaniem zapewniaj cym najwi ksz
elastyczno podczas wymiany danych pozostaje format .xls (w Excelu 2007
nosi nazw Skoroszyt programu Excel 97  2003). Jednak mo liwe jest u ycie
formatu pliku Open XML w starszych wersjach pakietu Office, o ile kompu-
tery docelowe maj zainstalowany Pakiet zgodno ci formatu plików pakietu
Microsoft Office dla programów Word, Excel i PowerPoint 2007. Pakiet jest
dost pny bezp atnie na witrynie internetowej firmy Microsoft1. Warto zwró-
ci uwag , e Office 2000 mo e konwertowa pliki Open XML na format
binarny tylko za po rednictwem Eksploratora Windows.
Struktura formatu pliku Open XML
Format pliku Open XML mo e zosta opisany jako struktura sk adaj ca si
z bloków stanowi cych elementy sk adowe (czyli poszczególne cz ci) oraz
po cze (zwi zków) wykorzystywanych do skomponowania, zapakowania,
rozpowszechniania i generowania zawarto ci dokumentu. J drem formatu
pliku s schematy XML oraz archiwum ZIP. Podczas zapisu dokumentu Office
w formacie Open XML nast puje utworzenie archiwum ZIP zawieraj cego
nast puj ce komponenty.
Cz ci  wi kszo elementów  cz ci to pliki XML opisuj ce dane apli-
kacji i metadane. Modu owo pozostaje najwa niejsz cech charakte-
rystyczn formatu pliku, poniewa pozwala na zlokalizowanie okre lonej
cz ci i bezpo redni prac z dan cz ci .
1
Pakiet mo na pobra ze strony http://www.microsoft.com/downloads/details.aspx?FamilyID=
941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=pl  przyp. t um.
276 ROZDZIA 10. WST KA W OFFICE 2007
Elementy rodzaju tre ci  opisuj , jakiego rodzaju pliki s przechowy-
wane w dokumencie. Przyk adowo image/png oznacza plik graficzny
w formacie PNG. Informacja ta pozwala aplikacjom na okre lenie zawar-
to ci dowolnej cz ci pakietu i prawid owe przetworzenie jej zawarto ci.
Zwi zki  opisuj , jak zbiór elementów dokumentu razem tworzy posta
dokumentu. Podczas gdy  cz ci tworz tre pliku, zwi zki opisuj spo-
sób, w jaki cz ci ze sob wspó pracuj .
Przeanalizujmy samodzielnie zawarto pliku Excela. Do tej operacji
potrzebny b dzie program obs uguj cy archiwa ZIP, np. WinZip b d 7-Zip.
Pierwszym krokiem jest utworzenie pliku Excela, umieszczenie w nim
wykresu, a nast pnie zapisanie pliku pod nazw PED.xlsm. Do kolejnego
kroku konieczne b dzie u ycie narz dzia Office 2007 Custom UI Editor,
które mo na pobra bezp atnie z witryny Microsoft2. Otwórz plik w narz dziu
Custom UI Editor i do skoroszytu dodaj w asne opcje RibbonX przedsta-
wione w listingu 10.1.
LISTING 10.1. Proste dostosowanie opcji w RibbonX