Wiêcej ni¿ Excel 2007.
166 gotowych rozwi¹zañ
i trików w jêzyku VBA
Autor: Miros³aw Lewandowski
ISBN: 978-83-246-1907-8
Format: 158×235, stron: 224
Dopasuj Excela do swoich wymagañ!
• Narzêdzia i zasady u¿ywania VBA – okno edytora, certyfikaty, zabezpieczenia
• Makra i kod VBA – tworzenie, uruchamianie, przyk³ady konkretnych dzia³añ
• Podstawy VBA – typy danych, operacje na ³añcuchach, interakcje z u¿ytkownikiem
Bez umiejêtnoœci pos³ugiwania siê arkuszem kalkulacyjnym Excela trudno dziœ
wyobraziæ sobie jak¹kolwiek pracê biurow¹. Jego wielofunkcyjnoœæ i elastycznoœæ s¹
imponuj¹ce, ale wci¹¿ jeszcze mo¿na natkn¹æ siê na obszary nie do koñca odpowiadaj¹ce
specyficznym potrzebom danej firmy, stanowiska czy pracownika. Owszem, ogólnie
wiadomo, ¿e wbudowany w program jêzyk VBA oferuje mo¿liwoœæ znacznego
zmodyfikowania ustawieñ i zapisania dodatkowych funkcji albo sekwencji dzia³añ,
jednak nie wszyscy potrafi¹ sprawnie wykorzystaæ to narzêdzie.
Ksi¹¿ka „VBA dla Excela 2007. 166 praktycznych przyk³adów” to cenny poradnik dla
tych, którym nie wystarcza znajomoœæ podstawowych funkcji Excela. Znajdziesz w niej
ca³¹ masê przyk³adów kodu napisanego w jêzyku VBA – zastosowanie któregokolwiek
z nich pozwala poczuæ ró¿nicê w dzia³aniu programu. Jeœli wiêc chcia³byœ zarejestrowaæ
i uruchomiæ w³asne makro, zdefiniowaæ funkcjê, dodaæ przycisk, jednym ruchem zamkn¹æ
wszystkie skoroszyty, wykonaæ jak¹œ operacjê na tablicach albo wyœwietliæ okreœlony
komunikat, mo¿esz zrobiæ to bez koniecznoœci zag³êbiania siê w tajniki programowania.
Prosto, szybko, skutecznie.
• Narzêdzie VBA w Excelu 2007
• Zabezpieczenia Excela i Windows, certyfikaty cyfrowe
• Rejestrator makr – uruchamianie makr i funkcji
• Definiowanie w³asnej funkcji i okreœlanie w³aœciwoœci makra/funkcji
• Wymuszanie zezwolenia na makra przy uruchomieniu skoroszytu
• Dodawanie przycisku lub ikon do wst¹¿ki i komentarzy do komórki
• Ochrona przed zmian¹ nazwy arkusza i dynamiczne ukrywanie wierszy
• Hiper³¹cze w formularzu i oknie komunikatu
• Odczytywanie danych o systemie
• Blokowanie wydruków i dostêpu do makr
• Arkusz ofert
• Typy danych, zmienne, sta³e i tablice
• Typy zmiennych i sta³ych, funkcje i operatory matematyczne
• Interakcja z u¿ytkownikiem i operacje na ³añcuchach
• Dodatki
Rozszerz potêgê Excela — wykorzystaj gotowe kody VBA
Spis tre
Ĉci
Wst
öp ................................................................................... 5
Rozdzia
ä 1. Excel 2007 — nowe przyzwyczajenie ...................................... 7
Rozdzia
ä 2. Narzödzie VBA w Excelu 2007 ................................................ 9
Okno edytora VBA ............................................................................................9
Rozdzia
ä 3. Zanim zaczniemy ................................................................. 15
S
áowo o zabezpieczeniach ...............................................................................15
Zabezpieczenia Excela ...............................................................................17
Certyfikaty cyfrowe ....................................................................................20
Zabezpieczenia Windows ...........................................................................21
Rozdzia
ä 4. O VBA bez VBA .................................................................... 25
Rejestrator makr ...............................................................................................25
Przyk
áad 1. Rejestrowanie makr .................................................................26
Przyk
áad 2. Modyfikacja kodu z rejestratora ..............................................27
Uruchamianie makr i funkcji ...........................................................................30
Przyk
áad 3. Uruchamianie makr za pomocą okna dialogowego Makro .....30
Przyk
áad 4. Uruchamianie makr za pomocą kombinacji klawiszy .............31
Przyk
áad 5. Wstawianie przycisku makra na pasku narzĊdzi Szybki dostĊp .... 32
Przyk
áad 6. Uruchamianie makra za pomocą przycisku makra
wewn
ątrz arkusza ....................................................................34
Przyk
áad 7. Uruchamianie makra za pomocą zdarzenia .............................34
Przyk
áad 8. Zakáadka Dodatki na wstąĪce ..................................................35
Przyk
áad 9. Uruchamianie makra na podstawie analizy komórki ...............38
Przyk
áad 10. Uruchamianie makr za pomocą formantu .............................39
Przyk
áad 11. Uruchamianie makra na podstawie analizy komórki.
Sposób drugi ..........................................................................39
Przyk
áad 12. Uruchamianie makra na podstawie analizy wartoĞci komórki.
Sposób trzeci .........................................................................41
Rozdzia
ä 5. Przykäady ............................................................................. 43
Przyk
áad 13. Definiowanie wáasnej funkcji. Konwertuj .............................43
Przyk
áad 14. Definiowanie wáasnej funkcji. DzieĔ tygodnia .....................44
Przyk
áad 15. DzieĔ tygodnia duĪo áatwiej ..................................................45
4
Wi
öcej niĔ Excel 2007. 166 gotowych rozwiñzaþ i trików w jözyku VBA
Przyk
áad 16. Funkcja Sáownie ....................................................................45
Przyk
áad 17. Funkcja Sáownie — wersja 2 .................................................51
Przyk
áad 18. Funkcja Sáownie z podprogramem ........................................54
Przyk
áad 19. OkreĞlenie wáaĞciwoĞci makra/funkcji za pomocą edytora ......58
Przyk
áad 20. OkreĞlenie wáaĞciwoĞci makra/funkcji za pomocą VBA ......60
Przyk
áad 21. Wstawianie funkcji arkuszowej do arkusza za pomocą VBA ...61
Przyk
áad 22. Wymuszenie wáączenia dodatku ...........................................62
Przyk
áad 23. ZamkniĊcie dodatku ..............................................................62
Przyk
áad 24. ZamkniĊcie dodatku uruchomionego przez nasz skoroszyt .......62
Przyk
áad 25. Rozwiązanie drugie — stabilniejsze ......................................63
Przyk
áad 26. Formatowanie warunkowe w zaleĪnoĞci od wartoĞci
z innej komórki .....................................................................65
Przyk
áad 27. Ile mamy otwartych skoroszytów? ........................................65
Przyk
áad 28. Program w wersji trial ...........................................................66
Przyk
áad 29. Wymuszanie zezwolenia na makra
przy uruchomieniu skoroszytu ..............................................67
Przyk
áad 30. Dodanie przycisku do wstąĪki w zakáadce Dodatki ..............68
Przyk
áad 31. Dodawanie komentarzy do komórki w zaleĪnoĞci
od warunków .........................................................................70
Przyk
áad 32. Poszukiwanie ostatniego wiersza ..........................................71
Przyk
áad 33. Ochrona przed zmianą nazwy arkusza ..................................72
Przyk
áad 34. Dynamiczne ukrywanie wierszy ............................................72
Przyk
áad 35. Jednorazowe losowanie — sposób 1 .....................................74
Przyk
áad 36. Jednorazowe losowanie — sposób 2 .....................................75
Przyk
áad 37. Automatyczne uruchamianie ostatnich skoroszytów .............76
Przyk
áad 38. Uruchamianie skoroszytów przy starcie programu ...............76
Przyk
áad 39. Uruchamianie skoroszytów przy starcie programu
— wersja non VBA ...............................................................77
Przyk
áad 40. Uruchamianie wielu skoroszytów jednoczeĞnie ....................78
Przyk
áad 41. „Pisanie” formularza .............................................................79
Przyk
áad 42. Hiperáącze w oknie komunikatu ............................................82
Przyk
áad 43. Hiperáącze w formularzu .......................................................84
Przyk
áad 44. Najprostszy komunikat ..........................................................86
Przyk
áad 45. Komunikat bardziej skomplikowany .....................................86
Przyk
áad 46. Okno dialogowe ....................................................................87
Przyk
áad 47. Porada dnia ............................................................................87
Przyk
áad 48. Porada dnia z opcjami w User form — czĊĞü I .....................88
Przyk
áad 49. Porada dnia z opcjami w User form — czĊĞü II ....................89
Przyk
áad 50. Czyszczenie formatowania
w komórkach niezawieraj
ących danych ................................91
Przyk
áad 51. WyĞwietlanie postĊpu obliczeĔ na pasku stanu .....................92
Przyk
áad 52. WyĞwietlanie postĊpu obliczeĔ na formularzu ......................93
Przyk
áad 53. Jeszcze trochĊ zabawy ...........................................................95
Przyk
áad 54. Odczytywanie danych o systemie — nazwa komputera .......95
Przyk
áad 55. Odczytywanie danych o systemie — nazwa uĪytkownika ....96
Przyk
áad 56. Odczytywanie danych o systemie
— rozdzielczo
Ğü karty graficznej ..........................................96
Przyk
áad 57. Odczytywanie danych o systemie — wielkoĞü okna aplikacji .....97
Spis tre
Ĉci
5
Przyk
áad 58. Odczytywanie danych o systemie
— pro
Ğba o zmianĊ wielkoĞci okna ......................................98
Przyk
áad 59. WyĞwietlanie okna w peánym rozmiarze bez pytania ...........99
Przyk
áad 60. Zamykanie wszystkich skoroszytów .....................................99
Przyk
áad 61. Blokowanie wydruków ........................................................100
Przyk
áad 62. Blokada dostĊpu do makr ....................................................100
Przyk
áad 63. Import tekstu z zewnĊtrznego pliku tekstowego .................100
Przyk
áad 64. Pobieranie danych z zamkniĊtego skoroszytu Excela .........101
Przyk
áad 65. Pobieranie danych z innego skoroszytu — wersja 2 ...........102
Przyk
áad 66. Dynamiczne tworzenie makra .............................................102
Przyk
áad 67. Usuwanie kodu z moduáu ....................................................103
Przyk
áad 68. ZastĊpowanie moduáu innym moduáem ..............................103
Przyk
áad 69. Przekazywanie moduáu miĊdzy plikami ..............................104
Przyk
áad 70. Sprawdzanie ustawieĔ dostĊpu do VBProject .....................105
Przyk
áad 71. Uruchamianie innych aplikacji Windows ............................105
Przyk
áad 72. Uruchamianie okien panelu sterowania ...............................105
Przyk
áad 73. Zapis kopii zapasowej pliku z hasáem .................................105
Przyk
áad 74. Ostatnie zapisanie arkusza ...................................................107
Przyk
áad 75. Przygotowanie wydruku z danymi
z arkusza innego ni
Ī widoczny ...........................................107
Przyk
áad 76. Nie wszystko, co automatyczne, to VBA ............................108
Przyk
áad 77. Automatyka formantów .......................................................110
Przyk
áad 78. WstąĪka w Office 2007. To takĪe nie VBA, ale... ...............110
Przyk
áad 79. Dodawanie ikon do wstąĪki .................................................113
Przyk
áad 80. Arkusz ofert — przykáad,
o który najcz
ĊĞciej pytają czytelnicy ................................... 114
Przyk
áad 81. Arkusz ofert — otwieranie bazy ..........................................114
Przyk
áad 82. Arkusz ofert — czĊĞü 2 .......................................................115
Przyk
áad 83. Arkusz ofert — czĊĞü 3 .......................................................116
Przyk
áad 84. Arkusz ofert — czĊĞü 4 .......................................................117
Przyk
áad 85. Arkusz ofert — czĊĞü 5 .......................................................118
Przyk
áad 86. Arkusz ofert — czynnoĞci koĔcowe ....................................118
Rozdzia
ä 6. Trochö podstaw o VBA ....................................................... 119
Typy zmiennych i sta
áych ..............................................................................119
Przyk
áad 87. Typy zmiennych ..................................................................120
Przyk
áad 88. Deklarowanie zmiennych ....................................................121
W
áasny typ zmiennej ......................................................................................121
Przyk
áad 89. Deklaracja wáasnego typu zmiennej ....................................121
Przyk
áad 90. Deklarowanie zmiennych za pomocą Const, Dim i Static ...123
Przyk
áad 91. Sáowa kluczowe Public i Private .........................................124
Przyk
áad 92. Opcje moduáu ......................................................................124
Przyk
áad 93. Opcja Option Compare ........................................................125
Przyk
áad 94. Konwersja typów danych ....................................................125
Przyk
áad 95. Funkcje FormatNumber, FormatCurrency, FormatPercent ......128
Przyk
áad 96. Funkcja FormatDateTime ....................................................129
Przyk
áad 97. Sáowo kluczowe ReDim ......................................................129
Przyk
áad 98. Funkcje LBound i UBound .................................................130
Przyk
áad 99. Operacje na tablicach ..........................................................130
6
Wi
öcej niĔ Excel 2007. 166 gotowych rozwiñzaþ i trików w jözyku VBA
Funkcje i operatory matematyczne ................................................................131
Przyk
áad 100. Funkcje trygonometryczne ................................................131
Przyk
áad 101. Funkcje Exp i Log .............................................................131
Przyk
áad 102. Funkcja Sqr .......................................................................132
Przyk
áad 103. Funkcje Randomize i Rnd .................................................132
Przyk
áad 104. Funkcje Abs i Sgn .............................................................133
Przyk
áad 105. Funkcje Fix, Int i Round ....................................................133
Przyk
áad 106. Funkcje Hour, Minute, Second ..........................................134
Przyk
áad 107. Funkcje Day, Month, Year ................................................134
Przyk
áad 108. Weekday ............................................................................135
Przyk
áad 109. TimeSerial i DateSerial .....................................................135
Przyk
áad 110. TimeValue i DateValue .....................................................136
Przyk
áad 111. Funkcja DateAdd ...............................................................136
Przyk
áad 112. Funkcja DateDiff ...............................................................138
Przyk
áad 113. Funkcja DatePart ...............................................................138
Przyk
áad 114. Funkcje Date, Now i Time ................................................139
Przyk
áad 115. Funkcja Timer ...................................................................139
Przyk
áad 116. Funkcja MonthName .........................................................139
Przyk
áad 117. Funkcja WeekdayName .....................................................140
Interakcja z u
Īytkownikiem ...........................................................................140
Przyk
áad 118. Okno komunikatu MsgBox ...............................................140
Przyk
áad 119. Okno dialogowe InputBox ................................................141
Przyk
áad 120. Funkcje logiczne ................................................................142
Operacje na
áaĔcuchach .................................................................................143
Przyk
áad 121. Funkcja StrComp ...............................................................143
Przyk
áad 122. Funkcja StrConv ................................................................143
Przyk
áad 123. Funkcje Lcase i Ucase .......................................................144
Przyk
áad 124. Funkcje Space, String i Len ...............................................144
Przyk
áad 125. Funkcja Format ..................................................................144
Przyk
áad 126. LSet, Rset ..........................................................................146
Przyk
áad 127. InStr, InStrRev ..................................................................146
Przyk
áad 128. Left, Right .........................................................................146
Przyk
áad 129. LTrim, RTrim, Trim ..........................................................147
Przyk
áad 130. Funkcja Replace ................................................................147
Przyk
áad 131. StrReverse .........................................................................148
Przyk
áad 132. Funkcja Mid ......................................................................148
Przyk
áad 133. Skok do podprogramu GoSub...Return, ............................148
Przyk
áad 134. Skok bezwarunkowy GoTo ...............................................148
Przyk
áad 135. Wykonuj aĪ... Do...Loop ...................................................149
Przyk
áad 136. PĊtla For...Step...Next, Exit For ........................................149
Przyk
áad 137. PĊtla For Each...Next .........................................................150
Przyk
áad 138. Wykonuj dopóki: While...Wend ........................................150
Przyk
áad 139. Reaguj na sytuacjĊ On...GoSub, On...GoTo ......................151
Przyk
áad 140. Funkcja Choose .................................................................152
Przyk
áad 141. Instrukcje warunkowe If...Then...Else ...............................152
Przyk
áad 142. Select Case...End Select ....................................................153
Przyk
áad 143. Przeáącznik Switch ............................................................153
Przyk
áad 144. Struktura With...End With .................................................154
Spis tre
Ĉci
7
Przyk
áad 145. Przerwanie programu: End i Stop ......................................154
Przyk
áad 146. Przerwanie programu: Exit ................................................155
Przyk
áad 147. Funkcja IsArray .................................................................155
Przyk
áad 148. Funkcja IsDate ...................................................................155
Przyk
áad 149. Funkcja IsEmpty ................................................................156
Przyk
áad 150. Funkcja IsError ..................................................................156
Przyk
áad 151. Funkcja IsMissing .............................................................156
Przyk
áad 152. Funkcja IsNull ...................................................................156
Przyk
áad 153. Funkcja IsNumeric ............................................................157
Przyk
áad 154. Funkcja IsObject ................................................................157
Przyk
áad 155. Funkcje TypeName, VarType ...........................................157
Przyk
áad 156. Obiekty Err i Error ............................................................158
Przyk
áad 157. Sam wygeneruj báąd! Raise ...............................................159
Przyk
áad 158. Czujka báĊdu logicznego. On Error ...................................159
Przyk
áad 159. Wznowienie po báĊdzie. Resume .......................................160
Przyk
áad 160. Przydatne gadĪety. Application.Volatile ...........................160
Przyk
áad 161. Przydatne gadĪety. Application.DisplayAlerts ..................160
Przyk
áad 162. Przydatne gadĪety. Application.ScreenUpdating ..............161
Przyk
áad 163. Przydatne gadĪety. Application.EnableEvents ..................161
Przyk
áad 164. Przydatne gadĪety. Application.DoubleClick ....................162
Przyk
áad 165. Przydatne gadĪety. Application.WorksheetFunction .........162
Przyk
áad 166. Przydatne gadĪety. Application.Calculate .........................162
Rozdzia
ä 7. Dodatki ............................................................................. 163
Dodatek 1. Obiekt Application.WorksheetFunction ......................................163
Dodatek 2. Skróty klawiaturowe u
Īywane w Excelu ........................................171
Dodatek 3. Skróty klawiaturowe u
Īywane w edytorze VBA .........................174
Dodatek 4. Procedury zdarzeniowe ................................................................177
Skorowidz .......................................................................... 215
Rozdzia
ä 5.
Przyk
äady
W tym rozdziale zaprezentuj
Ċ przykáady zastosowania VBA w róĪnych dziedzi-
nach
Īycia. Mam nadziejĊ, Īe znajdziesz tu inspiracjĊ do tworzenia wáasnych
projektów. J
Ċzyk programowania ma bowiem to do siebie, Īe jego ograniczeniem
jest tylko Twoja wyobra
Ĩnia. I umiejĊtnoĞci. Do dzieáa zatem.
Przyk
äad 13. Definiowanie wäasnej funkcji. Konwertuj
Poni
Īsza funkcja dokonuje konwersji jednostek temperatury pomiĊdzy stopniami
Celsjusza, Fahrenheita i kelwinami. Algorytm jest nast
Ċpujący:
Pobranie danych i weryfikacja. W przypadku temperatury ni
Īszej niĪ 0 K wyni-
kiem b
Ċdzie komunikat o báĊdzie.
Przeliczenie wed
áug zaleĪnoĞci:
1 st C = (1*1,8)+32 st F = 1+273,15 K
1 K = 1-273,15 st C = ((1-273,15)*1,8)+32 st F
1 st F = (1-32)/1,8 st C = ((1-32)/1,8)+273,15 K
Je
Īeli nastąpiáo báĊdne wpisanie jednostki wejĞciowej lub wyjĞciowej, wynikiem
b
Ċdzie komunikat o báĊdzie.
Przyjmij obliczon
ą wartoĞü.
Funkcji mo
Īna uĪywaü jak kaĪdej funkcji arkuszowej (rysunek 5.1).
Rysunek 5.1.
Mimo wielkiego bogactwa funkcji
wbudowanych ka
Īdy z nas moĪe dodaü
do palety podstawowej gar
Ğü wáasnych
gad
Īetów. Zanim jednak opracujesz wáasną
funkcj
Ċ, dobrze poszukaj — prawdopodobnie
kto
Ğ juĪ przygotowaá ją za Ciebie
48
Rozdzia
ä 5.
i Przykäady
Function konwertuj(liczba As Single, jed_wejħciowa, jed_wyjħciowa As String)
´
As Variant
Dim c, k, f, wynik As Variant, bđæd As String
'konwersja na wielkie litery
jed_wejħciowa = UCase(jed_wejħciowa)
jed_wyjħciowa = UCase(jed_wyjħciowa)
'czy nie jest ni
Īsza od zera bezwzglĊdnego
If jed_wejħciowa = "K" And liczba < 0 Then _
bđæd = "Temperatura poniľej 0 bezwzglúdnego"
If jed_wejħciowa = "C" And liczba < -273.15 Then _
bđæd = "Temperatura poniľej 0 bezwzglúdnego"
If jed_wejħciowa = "F" And liczba < -459.67 Then _
bđæd = "Temperatura poniľej 0 bezwzglúdnego"
'zamiana danej wej
Ğciowej na stopnie Celsjusza
Select Case jed_wejħciowa
Case "C"
c = liczba
Case "K"
c = liczba - 273.15
Case "F"
c = (liczba - 32) / 1.8
Case Else
bđæd = "Bđúdna jednostka wejħciowa"
End Select
' zamiana stopni Celsjusza na jednostk
Ċ wyjĞciową
Select Case jed_wyjħciowa
Case "C"
wynik = c
Case "K"
wynik = c + 273.15
Case "F"
wynik = (c * 1.8) + 32
Case Else
bđæd = "Bđúdna jednostka wyjħciowa"
End Select
If bđæd <> "" Then wynik = bđæd
konwertuj = wynik
End Function
Przyk
äad 14. Definiowanie wäasnej funkcji. Dzieþ tygodnia
Poni
Īsza funkcja wyĞwietla nazwĊ dnia tygodnia z podanej daty. Dodatkowo za-
wiera argument opcjonalny. Je
Īeli go nie pominiemy, wynikiem funkcji bĊdzie
trzyliterowy skrót dnia tygodnia — rysunek 5.2.
Rysunek 5.2.
Okre
Ğlanie nazwy
dnia tygodnia
za pomoc
ą VBA
Przyk
äad 15. Dzieþ tygodnia duĔo äatwiej
49
Nast
Ċpny przykáad pokazuje, Īe niekiedy warto poszukaü, zanim stworzysz kod,
bo rozwi
ązanie moĪe byü bliĪej, niĪ myĞlisz.
Function Dz_tygodnia(dzieē As Single, Optional skrócona)
Dim tabela(1 To 7) As String
tabela(7) = "Niedziela"
tabela(1) = "Poniedziađek"
tabela(2) = "Wtorek"
tabela(3) = "Ħroda"
tabela(4) = "Czwartek"
tabela(5) = "Piætek"
tabela(6) = "Sobota"
Dz_tygodnia = tabela(dzieē)
If IsMissing(skrócona) = False Then Dz_tygodnia = Left(Dz_tygodnia, 3)
End Function
Funkcja nie jest skomplikowana. Jej dzia
áanie polega na przypisaniu pozycjom
tabeli jednowymiarowej nazw kolejnych dni tygodnia, a nast
Ċpnie wybraniu
odpowiedniej warto
Ğci z obliczonej w komórce. Dodatkowo, jeĪeli podasz drugi
argument (mo
Īe to byü cokolwiek, bowiem kod sprawdza tylko jego obecnoĞü lub
brak —
If IsMissing(skrócona) = False Then...
),
funkcja zwróci tylko trzy
pierwsze litery nazwy dnia tygodnia (
Left(Dz_tygodnia, 3)
).
Bardzo wa
Īne jest, aby argument opcjonalny badany funkcją
IsMissing
mia
á
okre
Ğlony typ
Variant
. Nasza funkcja nie definiuje typu argumentu, zatem
Variant
zostaje do niego przyporz
ądkowany automatycznie. Zadeklarowanie innego typu
argumentu, na przyk
áad:
Function Dz_tygodnia(dzieē As Single, Optional skrócona As String)
spowoduje nieprawid
áowe dziaáanie.
Przyk
äad 15. Dzieþ tygodnia duĔo äatwiej
By
ü moĪe miaáeĞ juĪ okazjĊ przekonaü siĊ o tym, Īe narzĊdzia wbudowane w Excel
bywaj
ą zaskakujące. Niekiedy przemyĞlane ich uĪycie moĪe prowadziü do takich
samych efektów jak u
Īywanie VBA. Rysunek 5.3 pokazuje, jak moĪemy otrzymaü
wynik z przyk
áadu 14 bez uĪywania VBA.
Przyk
äad 16. Funkcja Säownie
To pewnie najbardziej po
Īądana z funkcji, które trzeba samemu napisaü. Nie
wiadomo, dlaczego Microsoft w kolejnych edycjach pakietu Office nie do
áącza
tej funkcji w standardzie. Niewa
Īne. Mamy przynajmniej moĪliwoĞü, aby roz-
win
ąü skrzydáa. Ten przykáad jest doĞü rozbudowany, bowiem zachowuje polską
gramatyk
Ċ sáów. Przykáad 17 rezygnuje z niej, dając w zamian moĪliwoĞü
wyboru waluty.
50
Rozdzia
ä 5.
i Przykäady
Rysunek 5.3. Po prostu skopiuj dat
Ċ do komórek B3 i B4, a nastĊpnie z okna Formatowanie
komórek (uruchamianego kombinacj
ą Ctrl+1) wybierz niestandardowe formaty komórek:
dddd dla B3 i ddd dla B4
Function SLOWNIE(x)
Dim Setki(10) As String
Dim dziesiatki(10) As String
Dim jednostki(20) As String
Setki(0) = ""
Setki(1) = "sto"
Setki(2) = "dwieħcie"
Setki(3) = "trzysta"
Setki(4) = "czterysta"
Setki(5) = "piúèset"
Setki(6) = "szeħèset"
Setki(7) = "siedemset"
Setki(8) = "osiemset"
Setki(9) = "dziewiúèset"
dziesiatki(0) = ""
dziesiatki(1) = "dziesiúè"
dziesiatki(2) = "dwadzieħcia"
dziesiatki(3) = "trzydzieħci"
dziesiatki(4) = "czterdzieħci"
dziesiatki(5) = "piúèdziesiæt"
dziesiatki(6) = "szeħèdziesiæt"
dziesiatki(7) = "siedemdziesiæt"
dziesiatki(8) = "osiemdziesiæt"
dziesiatki(9) = "dziewiúèdziesiæt"
Przyk
äad 16. Funkcja Säownie
51
jednostki(0) = ""
jednostki(1) = "jeden"
jednostki(2) = "dwa"
jednostki(3) = "trzy"
jednostki(4) = "cztery"
jednostki(5) = "piúè"
jednostki(6) = "szeħè"
jednostki(7) = "siedem"
jednostki(8) = "osiem"
jednostki(9) = "dziewiúè"
jednostki(10) = "dziesiúè"
jednostki(11) = "jedenaħcie"
jednostki(12) = "dwanaħcie"
jednostki(13) = "trzynaħcie"
jednostki(14) = "czternaħcie"
jednostki(15) = "piútnaħcie"
jednostki(16) = "szesnaħcie"
jednostki(17) = "siedemnaħcie"
jednostki(18) = "osiemnaħcie"
jednostki(19) = "dziewiútnaħcie"
' je
Īeli moduá liczby jest wiĊkszy od podanej wartoĞci to zakoĔcz i wyĞwietl komunikat
If Abs(x) >= 1000000000000# Then
SLOWNIE = "Przekroczony zakres od -1.000.000.000.000
´
do 1.000.000.000.000"
Exit Function
End If
If x < 0 Then minus = "Minus " Else minus = ""
x = Abs(x)
liczba = Int(x)
L = (x - liczba) * 100
liczbagr = Int(L)
If (L - liczbagr) * 10 >= 5 Then liczbagr = liczbagr + 1
'okre
Ğla ile mamy miliardów
liczbamld = Int(liczba / 1000000000)
'okre
Ğla ile mamy milionów
liczbamil = Int((liczba - liczbamld * 1000000000) / 1000000)
'okre
Ğla ile mamy tysiĊcy
liczbatys = Int((liczba - liczbamld * 1000000000 - liczbamil *
´
1000000) / 1000)
'okre
Ğla ile mamy setek
liczbaset = Int(liczba - liczbamld * 1000000000 - liczbamil * 1000000
´
- liczbatys * 1000)
miliardy:
liczba = liczbamld
If liczba = 0 Then GoTo miliony
L = Int(liczba / 100)
slowniezl = slowniezl + Setki(L)
52
Rozdzia
ä 5.
i Przykäady
liczba = liczba - (L * 100)
If liczba < 20 Then slowniezl = slowniezl + " " + jednostki(liczba):
´
GoTo piszmiliardy
L = Int(liczba / 10)
slowniezl = slowniezl + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slowniezl = slowniezl + " " + jednostki(liczba)
piszmiliardy:
If liczbamld = 1 Then slowniezl = slowniezl + " miliard ": GoTo miliony
aa = Str(liczba)
bb = Right(aa, 1)
If aa >= 5 And aa < 20 Then slowniezl = slowniezl + " miliardów ":
´
GoTo miliony
If bb > 1 And bb < 5 Then slowniezl = slowniezl + " miliardy ":
´
GoTo miliony
If liczbamld >= 5 Then slowniezl = slowniezl + " miliardów "
miliony:
liczba = liczbamil
If liczba = 0 Then GoTo tysiace
L = Int(liczba / 100)
slowniezl = slowniezl + Setki(L)
liczba = liczba - (L * 100)
If liczba < 20 Then slowniezl = slowniezl + " " + jednostki(liczba):
´
GoTo piszmiliony
L = Int(liczba / 10)
slowniezl = slowniezl + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slowniezl = slowniezl + " " + jednostki(liczba)
piszmiliony:
If liczbamil = 1 Then slowniezl = slowniezl + " milion ": GoTo tysiace
aa = Str(liczba)
bb = Right(aa, 1)
If aa >= 5 And aa < 20 Then slowniezl = slowniezl + " milionów ":
´
GoTo tysiace
If bb > 1 And bb < 5 Then slowniezl = slowniezl + " miliony ":
´
GoTo tysiace
If liczbamil >= 5 Then slowniezl = slowniezl + " milionów "
Przyk
äad 16. Funkcja Säownie
53
tysiace:
liczba = liczbatys
If liczba = 0 Then GoTo jednostki
L = Int(liczba / 100)
slowniezl = slowniezl + Setki(L)
liczba = liczba - (L * 100)
If liczba < 20 Then slowniezl = slowniezl + " " + jednostki(liczba):
´
GoTo pisztysiace
L = Int(liczba / 10)
slowniezl = slowniezl + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slowniezl = slowniezl + " " + jednostki(liczba)
pisztysiace:
If liczbatys = 1 Then slowniezl = slowniezl + " tysiæc ":
´
GoTo jednostki
aa = Str(liczba)
bb = Right(aa, 1)
If aa >= 5 And aa < 20 Then slowniezl = slowniezl + " tysiúcy ":
´
GoTo jednostki
If bb > 1 And bb < 5 Then slowniezl = slowniezl + " tysiæce ":
´
GoTo jednostki
If liczbatys >= 5 Then slowniezl = slowniezl + " tysiúcy "
jednostki:
liczba = liczbaset
If Int(x) = 0 Then slowniezl = slowniezl + " zero zđotych":
´
GoTo grosze
If liczba = 0 Then slowniezl = slowniezl + " zđotych": GoTo grosze
L = Int(liczba / 100)
slowniezl = slowniezl + Setki(L)
liczba = liczba - (L * 100)
If liczba < 20 Then slowniezl = slowniezl + " " + jednostki(liczba):
´
GoTo piszjednostki
L = Int(liczba / 10)
slowniezl = slowniezl + " " + dziesiatki(L)
liczbaa = liczba - (L * 10)
slowniezl = slowniezl + " " + jednostki(liczbaa)
54
Rozdzia
ä 5.
i Przykäady
piszjednostki:
aa = Str(liczba)
bb = Right(aa, 1)
If aa >= 5 And aa < 20 Then slowniezl = slowniezl + " zđotych": GoTo grosze
If bb >= 2 And bb < 5 Then slowniezl = slowniezl + " zđote": GoTo grosze
If aa = 1 Then slowniezl = slowniezl + " zđoty"
If liczbaset >= 5 Then slowniezl = slowniezl + " zđotych"
grosze:
liczba = liczbagr
If liczba = 0 Then slowniegr = "zero groszy": GoTo wynik
If liczba < 20 Then slowniegr = jednostki(liczba): GoTo piszgrosze
L = Int(liczba / 10)
slowniegr = slowniegr + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slowniegr = slowniegr + " " + jednostki(liczba)
piszgrosze:
aa = Str(liczbagr)
bb = Right(aa, 1)
If aa >= 5 And aa < 20 Then slowniegr = slowniegr + " groszy": GoTo wynik
If bb >= 2 And bb < 5 Then slowniegr = slowniegr + " grosze": GoTo wynik
If aa = 1 Then slowniegr = slowniegr + " grosz": GoTo wynik
If aa >= 5 Then slowniegr = slowniegr + " groszy"
wynik:
' obcina zb
Ċdne spacje na początku i koĔcu sáowa
bez_spacji = Trim(minus + slowniezl)
'pierwsza litera wyra
Īenia
wielka_litera = UCase(Left(bez_spacji, 1))
'liczy ile liter jest w wyra
Īeniu
ile_mamy_liter = Len(bez_spacji)
'wstawia wielk
ą literĊ na początku wyraĪenia
wstaw_wielka = wielka_litera + Right(bez_spacji, ile_mamy_liter - 1)
'przypisuje warto
Ğü do funkcji
SLOWNIE = Application.WorksheetFunction.Trim(wstaw_wielka + " " + slowniegr)
End Function
Przyk
äad 17. Funkcja Säownie — wersja 2
55
Wyja
Ğnienia do dziaáania tej funkcji zawarte są w komentarzach w kodzie (czyli
w tek
Ğcie po apostrofie — taki tekst nie jest analizowany przez VBA). Funkcja
jest sekwencj
ą operacji powtarzających siĊ dla kaĪdego z rzĊdów wielkoĞci:
miliardów, milionów, tysi
Ċcy itd. Wynikiem kolejnej sekwencji (nazwijmy ją
podprogramem) jest zmienna „
slowniezl
”, która jest wyd
áuĪana (czyli doklejane
s
ą do niej kolejne sáowa) w miarĊ schodzenia kodu coraz niĪej. W sekcji „wynik”
dodawana jest jeszcze wielka litera na pocz
ątku wyraĪenia, znak minus, jeĪeli
liczba jest ujemna, oraz usuwane s
ą zbĊdne spacje miĊdzy kolejnymi sáowami.
Do usuni
Ċcia zbĊdnych spacji postanowiáem uĪyü funkcji arkuszowej
Trim
(w polskim Excelu jest to
USUĒ.ZBùDNE.ODSTùPY
). Co prawda VBA tak
Īe oferuje
funkcj
Ċ
Trim
, jednak podczas prób okaza
áo siĊ, Īe odcina ona tylko spacje na po-
cz
ątku i koĔcu wyniku, nie ingerując w odstĊpy pomiĊdzy sáowami.
Przyk
äad 17. Funkcja Säownie — wersja 2
Poni
Īsza funkcja jest prostsza od poprzedniej, bowiem nie uwzglĊdnia polskiej
gramatyki. Mamy za to do wyboru zapis w j
Ċzyku polskim lub angielskim i wa-
luty: euro, dolary i z
áote.
Function SĐOWNIE(ByVal liczba, Optional waluta, Optional júzyk)
On Error Resume Next
'deklaracja tablicy
Dim sđowa(9) As String
'deklaracja zmiennych
Dim przecinek, a, x, cađkowita As Single, po_przecinku As Boolean
Dim wynik, gr, zđ, znak As String, jednoħè
'zmiana liter w zmiennych na du
Īe
If waluta = Null Then waluta = " "
If júzyk = Null Then júzyk = " "
waluta = UCase(waluta)
júzyk = UCase(júzyk)
'Nadanie warto
Ğci początkowych zmiennym
wynik = ""
po_przecinku = False
Select Case waluta
Case "E"
gr = "Cent"
zđ = "Euro"
Case "D"
gr = "Cent"
zđ = "Dolar"
Case Else
gr = "gr"
zđ = "zđ"
End Select
Select Case júzyk
Case "ANG"
56
Rozdzia
ä 5.
i Przykäady
sđowa(0) = "zero"
sđowa(1) = "one"
sđowa(2) = "two"
sđowa(3) = "three"
sđowa(4) = "four"
sđowa(5) = "five"
sđowa(6) = "six"
sđowa(7) = "seven"
sđowa(8) = "eight"
sđowa(9) = "nine"
Case Else
sđowa(0) = "zero"
sđowa(1) = "jeden"
sđowa(2) = "dwa"
sđowa(3) = "trzy"
sđowa(4) = "cztery"
sđowa(5) = "piúè"
sđowa(6) = "szeħè"
sđowa(7) = "siedem"
sđowa(8) = "osiem"
sđowa(9) = "dziewiúè"
End Select
' ustalenie znaku plus/minus
znak = ""
If liczba < 0 Then
znak = "minus "
End If
liczba = Abs(liczba)
cađkowita = Int(liczba)
liczba = Trim(Str(liczba))
'szukanie przecinka
przecinek = InStr(liczba, ".")
If przecinek <> 0 Then a = przecinek - 1: po_przecinku = True Else a =
´
Len(liczba)
'rozpatrywanie liczb z lewej strony przecinka
If cađkowita > 0 Then
For x = 1 To a
jednoħè = Mid(liczba, x, 1)
jednoħè = Val(jednoħè)
wynik = wynik & sđowa(jednoħè) & "-"
Next x
Else
wynik = sđowa(0) & "-"
End If
wynik = wynik & zđ
Przyk
äad 17. Funkcja Säownie — wersja 2
57
'rozpatrywanie dwóch liczb z prawej strony przecinka (je
Īeli wystĊpują)
If po_przecinku Then
wynik = wynik & "-"
For x = 2 To 3
jednoħè = Mid(liczba, a + x, 1)
jednoħè = Val(jednoħè)
wynik = wynik & sđowa(jednoħè) & "-"
Next x
wynik = wynik & gr
Else
wynik = wynik & "-" & sđowa(0) & "-" & gr
End If
'zwrócenie warto
Ğci funkcji, obciĊcie niepotrzebnych spacji
SĐOWNIE = Trim(znak & wynik)
End Function
Kilka s
áów wyjaĞnienia, jak dziaáa powyĪszy kod:
1.
Rezerwuje tablic
Ċ jednowymiarową na jednostki.
2.
Pobiera informacje na temat j
Ċzyka i waluty. Odpowiednie wartoĞci
zostaj
ą przypisane zmiennym
waluta
i
júzyk
.
3.
Wprowadza do tablicy s
áowa po polsku lub angielsku, w zaleĪnoĞci
od wskazanego j
Ċzyka, oraz przypisuje zmiennym
zđ
i
gr
odpowiednie
warto
Ğci, stosownie do wybranej waluty.
4.
W przypadku liczby ujemnej nadaje zmiennej
znak
warto
Ğü
minus
.
5.
W analizowanej liczbie znajduje przecinek.
6.
Obcina cz
ĊĞü uáamkową w przypadku, gdy są wiĊcej niĪ dwie cyfry
po przecinku.
7.
Analizuje kolejno cyfry z lewej strony przecinka, przyporz
ądkowuje
im w
áaĞciwe sáowa, rozdziela je myĞlnikiem.
8.
Na zako
Ĕczenie wstawiona zostaje nazwa jednostki gáównej waluty.
9.
Analizuje dwie pierwsze cyfry z prawej strony przecinka i post
Ċpuje
jak wy
Īej.
10.
Ostatni
ą czynnoĞcią jest przypisanie funkcji
SĐOWNIE
obliczonej
warto
Ğci ciągu.
W przeciwie
Ĕstwie do poprzedniej funkcji tutaj nie mamy ograniczenia dotyczą-
cego wielko
Ğci wprowadzanej liczby. Nie jest ono potrzebne, bowiem nie uĪy-
wamy tu s
áów „miliardy”, „miliony” itp.
58
Rozdzia
ä 5.
i Przykäady
Przyk
äad 18. Funkcja Säownie z podprogramem
Jak zauwa
ĪyáeĞ, w przykáadzie 16 czĊĞü kodu jest powtarzana. Te same czynnoĞci
s
ą wykonywane dla kaĪdej grupy cyfr. JeĪeli tworzysz duĪe programy, warto
zamyka
ü powtarzalne elementy kodu w osobne procedury. Zmniejszają one
obj
ĊtoĞü kodu i tylko minimalnie spowalniają jego dziaáanie.
Function SLOWNIE_Z_PODPROGRAMEM(x)
Dim Setki(10) As String
Dim dziesiatki(10) As String
Dim jednostki(20) As String
Setki(0) = ""
Setki(1) = "sto"
Setki(2) = "dwieħcie"
Setki(3) = "trzysta"
Setki(4) = "czterysta"
Setki(5) = "piúèset"
Setki(6) = "szeħèset"
Setki(7) = "siedemset"
Setki(8) = "osiemset"
Setki(9) = "dziewiúèset"
dziesiatki(0) = ""
dziesiatki(1) = "dziesiúè"
dziesiatki(2) = "dwadzieħcia"
dziesiatki(3) = "trzydzieħci"
dziesiatki(4) = "czterdzieħci"
dziesiatki(5) = "piúèdziesiæt"
dziesiatki(6) = "szeħèdziesiæt"
dziesiatki(7) = "siedemdziesiæt"
dziesiatki(8) = "osiemdziesiæt"
dziesiatki(9) = "dziewiúèdziesiæt"
jednostki(0) = ""
jednostki(1) = "jeden"
jednostki(2) = "dwa"
jednostki(3) = "trzy"
jednostki(4) = "cztery"
jednostki(5) = "piúè"
jednostki(6) = "szeħè"
jednostki(7) = "siedem"
jednostki(8) = "osiem"
jednostki(9) = "dziewiúè"
jednostki(10) = "dziesiúè"
jednostki(11) = "jedenaħcie"
jednostki(12) = "dwanaħcie"
jednostki(13) = "trzynaħcie"
jednostki(14) = "czternaħcie"
jednostki(15) = "piútnaħcie"
jednostki(16) = "szesnaħcie"
Przyk
äad 18. Funkcja Säownie z podprogramem
59
jednostki(17) = "siedemnaħcie"
jednostki(18) = "osiemnaħcie"
jednostki(19) = "dziewiútnaħcie"
' je
Īeli moduá liczby jest wiĊkszy od podanej wartoĞci to zakoĔcz i wyĞwietl komunikat
If Abs(x) >= 1000000000000# Then
SLOWNIE_Z_PODPROGRAMEM = "Przekroczony zakres od -1.000.000.000.000
´
do 1.000.000.000.000"
Exit Function
End If
If x < 0 Then minus = "Minus " Else minus = ""
x = Abs(x)
liczba = Int(x)
L = (x - liczba) * 100
liczbagr = Int(L)
If (L - liczbagr) * 10 >= 5 Then liczbagr = liczbagr + 1
'okre
Ğla ile mamy miliardów
liczbamld = Int(liczba / 1000000000)
'okre
Ğla ile mamy milionów
liczbamil = Int((liczba - liczbamld * 1000000000) / 1000000)
'okre
Ğla ile mamy tysiĊcy
liczbatys = Int((liczba - liczbamld * 1000000000 - liczbamil *
´
1000000) / 1000)
'okre
Ğla ile mamy setek
liczbaset = Int(liczba - liczbamld * 1000000000 - liczbamil *
´
1000000 - liczbatys * 1000)
miliardy:
liczba = liczbamld
If liczba = 0 Then GoTo miliony
L = Int(liczba / 100)
slownie_zlotych = slownie_zlotych + Setki(L)
liczba = liczba - (L * 100)
If liczba < 20 Then slownie_zlotych = slownie_zlotych + " " +
´
jednostki(liczba): GoTo piszmiliardy
L = Int(liczba / 10)
slownie_zlotych = slownie_zlotych + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slownie_zlotych = slownie_zlotych + " " + jednostki(liczba)
piszmiliardy:
If liczbamld = 1 Then slownie_zlotych = slownie_zlotych + " miliard ":
´
GoTo miliony
aa = Str(liczba)
bb = Right(aa, 1)
slownie_zlotych = slownie_zlotych + przypisz(aa, bb, " miliardów ",
´
" miliardy ", liczbamld)
60
Rozdzia
ä 5.
i Przykäady
miliony:
liczba = liczbamil
If liczba = 0 Then GoTo tysiace
L = Int(liczba / 100)
slownie_zlotych = slownie_zlotych + Setki(L)
liczba = liczba - (L * 100)
If liczba < 20 Then slownie_zlotych = slownie_zlotych + " " +
´
jednostki(liczba): GoTo piszmiliony
L = Int(liczba / 10)
slownie_zlotych = slownie_zlotych + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slownie_zlotych = slownie_zlotych + " " + jednostki(liczba)
piszmiliony:
If liczbamil = 1 Then slownie_zlotych = slownie_zlotych + " milion ":
´
GoTo tysiace
aa = Str(liczba)
bb = Right(aa, 1)
slownie_zlotych = slownie_zlotych + przypisz(aa, bb, " milionów ",
´
" miliony ", liczbamil)
tysiace:
liczba = liczbatys
If liczba = 0 Then GoTo jednostki
L = Int(liczba / 100)
slownie_zlotych = slownie_zlotych + Setki(L)
liczba = liczba - (L * 100)
If liczba < 20 Then slownie_zlotych = slownie_zlotych + " " +
´
jednostki(liczba): GoTo pisztysiace
L = Int(liczba / 10)
slownie_zlotych = slownie_zlotych + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slownie_zlotych = slownie_zlotych + " " + jednostki(liczba)
pisztysiace:
If liczbatys = 1 Then slownie_zlotych = slownie_zlotych + " tysiæc ":
´
GoTo jednostki
aa = Str(liczba)
bb = Right(aa, 1)
Przyk
äad 18. Funkcja Säownie z podprogramem
61
slownie_zlotych = slownie_zlotych + przypisz(aa, bb, " tysiúcy ", " tysiæce ",
´
liczbatys)
jednostki:
liczba = liczbaset
If Int(x) = 0 Then slownie_zlotych = slownie_zlotych + " zero zđotych":
´
GoTo grosze
If liczba = 0 Then slownie_zlotych = slownie_zlotych + " zđotych":
´
GoTo grosze
L = Int(liczba / 100)
slownie_zlotych = slownie_zlotych + Setki(L)
liczba = liczba - (L * 100)
If liczba < 20 Then slownie_zlotych = slownie_zlotych + " " +
´
jednostki(liczba): GoTo piszjednostki
L = Int(liczba / 10)
slownie_zlotych = slownie_zlotych + " " + dziesiatki(L)
liczbaa = liczba - (L * 10)
slownie_zlotych = slownie_zlotych + " " + jednostki(liczbaa)
piszjednostki:
aa = Str(liczba)
bb = Right(aa, 1)
If aa >= 5 And aa < 20 Then slownie_zlotych = slownie_zlotych + "
´
zđotych": GoTo grosze
If bb >= 2 And bb < 5 Then slownie_zlotych = slownie_zlotych + " zđote":
´
GoTo grosze
If aa = 1 Then slownie_zlotych = slownie_zlotych + " zđoty"
If liczbaset >= 5 Then slownie_zlotych = slownie_zlotych + " zđotych"
grosze:
liczba = liczbagr
If liczba = 0 Then slowniegr = "zero groszy": GoTo wynik
If liczba < 20 Then slowniegr = jednostki(liczba): GoTo piszgrosze
L = Int(liczba / 10)
slowniegr = slowniegr + " " + dziesiatki(L)
liczba = liczba - (L * 10)
slowniegr = slowniegr + " " + jednostki(liczba)
piszgrosze:
aa = Str(liczbagr)
bb = Right(aa, 1)
62
Rozdzia
ä 5.
i Przykäady
If aa >= 5 And aa < 20 Then slowniegr = slowniegr + " groszy": GoTo wynik
If bb >= 2 And bb < 5 Then slowniegr = slowniegr + " grosze": GoTo wynik
If aa = 1 Then slowniegr = slowniegr + " grosz": GoTo wynik
If aa >= 5 Then slowniegr = slowniegr + " groszy"
wynik:
' obcina zb
Ċdne spacje na początku i koĔcu sáowa
bez_spacji = Trim(minus + slownie_zlotych)
'pierwsza litera wyra
Īenia
wielka_litera = UCase(Left(bez_spacji, 1))
'liczy ile liter jest w wyra
Īeniu
ile_mamy_liter = Len(bez_spacji)
'wstawia wielk
ą literĊ na początku wyraĪenia
wstaw_wielka = wielka_litera + Right(bez_spacji, ile_mamy_liter - 1)
'przypisuje warto
Ğü do funkcji
SLOWNIE_Z_PODPROGRAMEM = Application.WorksheetFunction.Trim(wstaw_wielka +
´
" " + slowniegr)
End Function
W powtarzalnych miejscach programu wywo
áywana jest funkcja przypisz, której
wynik doklejany jest do naszej zmiennej
slownie_zlotych
. Kluczem dzia
áania
funkcji przypisz s
ą wartoĞci przekazywane przy jej wywoáywaniu jako argumenty.
Poni
Īej przedstawiona jest jej treĞü:
Function przypisz(aa, bb, cc, dd, ee)
If aa >= 5 And aa < 20 Then przypisz = cc: Exit Function
If bb > 1 And bb < 5 Then przypisz = dd: Exit Function
If ee >= 5 Then przypisz = cc
End Function
Przyk
äad 19. OkreĈlenie wäaĈciwoĈci makra/funkcji za pomocñ edytora
Pisz
ąc funkcjĊ uĪytkownika, moĪesz zadbaü o jej opis i inne wáaĞciwoĞci. To
bardzo u
áatwia korzystanie z niej. Przykáad 13 bĊdzie tu dobrą ilustracją. Funkcja
Konwertuj posiada trzy parametry. Jak
ą jednak powinny mieü postaü? Wie o tym
tylko jej twórca i mo
Īe jeszcze ten, kto bĊdzie miaá doĞü czasu i umiejĊtnoĞci, aby
rzuci
ü okiem na jej kod. Marna to informacja dla potencjalnego uĪytkownika.
Oczekuje on bowiem od Ciebie u
Īytecznego, áatwego w obsáudze narzĊdzia
i informacji podanych na talerzu (rysunek 5.4), a nie kolejnej
áamigáówki. Gdyby
szuka
á áamigáówki, pewnie siĊgnąáby po odpowiednie czasopismo. My nie zakáa-
damy,
Īe tworzysz makra w VBA do czasopisma z zagadkami, zatem spróbu-
jemy do utworzonej w przyk
áadzie 13 funkcji dodaü informacje umoĪliwiają-
ce u
Īytkownikowi prawidáowe jej zredagowanie.
Przyk
äad 19. OkreĈlenie wäaĈciwoĈci makra/funkcji za pomocñ edytora
63
Rysunek 5.4.
Oprócz przyjaznych
nazw parametrów
mo
Īesz dodaü trzy
linijki informacji
dla u
Īytkownika,
aby zach
Ċciü go
do korzystania
z Twojej funkcji
W tym przyk
áadzie nie bĊdziemy pisaü kodów VBA, mimo Īe edytor bĊdzie nam
potrzebny.
1.
W oknie edytora VBA otwórz okno przegl
ądarki obiektu
(F2, lub View/Object Browser).
2.
Z listy (patrz rysunek 5.5) wybierz kategori
Ċ VBA Project.
Rysunek 5.5.
Przegl
ądarka
obiektów. U
Īyteczne
narz
Ċdzie dla
deweloperów VBA.
Naprawd
Ċ warto siĊ
z nim zaprzyja
Ĩniü
3.
W polu Classes (rysunek 5.6) wska
Ī moduá, w którym znajduje siĊ
opisywana funkcja, kliknij prawym przyciskiem myszy jej nazw
Ċ
w przegl
ądarce i z podrĊcznego menu wybierz polecenie Properties….
4.
W oknie dialogowym Member Options (rysunek 5.7) wpisz opis
funkcji. Oczywi
Ğcie! MoĪesz tam wpisaü dowcip z cyklu „przychodzi
baba do lekarza”, lecz je
Īeli bĊdzie dáuĪszy niĪ trzy linijki, nie wyĞwietli
si
Ċ w caáoĞci, a jeĪeli dodatkowo nie bĊdzie Ğmieszny, to gwarantujĊ
ci,
Īe mocno zapadniesz uĪytkownikom w pamiĊü. Tym báyskotliwym
ruchem by
ü moĪe przysáuĪysz siĊ czyimĞ badaniom na temat wpáywu
inteligencji krzemowej na bia
ákową.
64
Rozdzia
ä 5.
i Przykäady
Rysunek 5.6.
Dodanie opisu do
funkcji u
Īytkownika
nie jest trudne.
Zaufaj mi
Rysunek 5.7.
Kto powiedzia
á,
Īe w okno Member
Options nale
Īy wpisaü
pomoc do funkcji
u
Īytkownika? MoĪemy
co najwy
Īej zaáoĪyü,
Īe jest to zalecane
Przyk
äad 20. OkreĈlenie wäaĈciwoĈci makra/funkcji za pomocñ VBA
Wró
ümy juĪ do VBA. Nareszcie.
Skorzystaj z metody
MacroOptions
. Dla obiektu
Application
ma ona wiele argu-
mentów. Cz
ĊĞü z nich jest pomijana. NajwaĪniejsze dla nas argumenty są uĪyte
w poni
Īszej procedurze:
Macro
— podaje nazw
Ċ funkcji (makra), do którego odnosi siĊ Twoje dziaáanie.
Description
— wy
Ğwietla tekst wprowadzony przez Ciebie w oknie Member
Options.
Category
— przypisuje Twoj
ą funkcjĊ do odpowiedniej kategorii funkcji, zgodnie
z tabel
ą 5.1.
Sub definiuj_funkcjú()
tekst = "Konwertuje stopnie wg skal Celsjusza, Fahrenheita i Kelvina."
tekst = tekst & vbCrLf & "Jako argumentu jed_wejħciowa i jed_wyjħciowa
´
uľyj:"
tekst = tekst & vbCrLf & "'C' dla stopni Celsjusza,"
tekst = tekst & " 'K' dla stopni Kelvina oraz"
tekst = tekst & " 'F' dla stopni Fahrenheita."
Application.MacroOptions Macro:="konwertuj", _
Description:=tekst, Category:=3
End Sub
Przyk
äad 21. Wstawianie funkcji arkuszowej do arkusza za pomocñ VBA
65
Tabela 5.1. Kategorie funkcji i warto
Ğci argumentu Category metody MacroOptions
Kategoria funkcji
Warto
Ĉè argumentu Category
Finansowe
1
Daty i czasu
2
Matematyczne
3
Statystyczne
4
Wyszukiwania i adresu
5
Bazy danych
6
Tekstowe
7
Logiczne
8
Informacyjne
9
U
Īytkownika
Argument nale
Īy pominąü
Przyk
äad 21. Wstawianie funkcji arkuszowej do arkusza za pomocñ VBA
Poni
Īszy przykáad spowoduje wstawienie funkcji
ZĐäCZ.TEKSTY
do komórki C7.
Range("C7").Formula = "=CONCATENATE(A1,Arkusz2!A1,Arkusz3!B3)"
Jak wida
ü, nie moĪemy wprowadzaü tu polskich nazw funkcji. Angielskie odpo-
wiedniki znajdziesz w pliku funcs.xls, który jest instalowany w komputerze razem
z Excelem. W dalszej cz
ĊĞci ksiąĪki zamieĞciáem tabelĊ metod zawierających
si
Ċ w obiekcie
Application.Worksheetfunction
. Tabela ta zawiera polskie nazwy
funkcji i odpowiadaj
ące im metody bĊdące w zbiorze tego obiektu. Nazwy metod
odpowiadaj
ą angielskim nazwom funkcji. Niestety, funkcja
ZĐäCZ.TEKSTY
— jak
i kilkana
Ğcie innych — nie jest reprezentowana w tym zbiorze i nie moĪna uĪyü
jej w po
áączeniu z obiektem
Worksheetfunction
. Jedynym wi
Ċc sposobem na
odnalezienie jej angielskiego odpowiednika jest wspomniany wy
Īej plik.
Mo
Īesz takĪe uĪyü rejestratora makr w celu wygenerowania kodu wpisującego
formu
áĊ do komórki. To najpewniejszy sposób, aby nie popeániü báĊdu, choü reje-
strator u
Īywa sáowa kluczowego
FormulaR1C1
, które wymusza nieco inny sposób
adresowania komórek. Poza tym rejestrator wstawia osobny wiersz kodu mówi
ący
o zaznaczeniu komórki.
Range("C7").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-14]C[-2],Arkusz2!R[-14]C[-2],Arkusz3!R[-12]C[-1])"
Poza tymi sprawami technicznymi kod ma takie samo dzia
áanie, wiĊc jeĪeli nie
musisz w niego ingerowa
ü, skorzystaj z rejestratora i ciesz siĊ szybko osiągniĊ-
tym wynikiem.
66
Rozdzia
ä 5.
i Przykäady
Maj
ñc powyĔszñ wiedzö, pomyĈl, jak äatwo byäoby stworzyè „samopiszñcy siö”
arkusz Excela. Mo
Ĕesz w taki sposób wymusiè na uĔytkowniku zgodö na
uruchomienie makr. Je
Ĕeli nie bödzie zgody, nie bödzie w nim nawet zwykäych
excelowskich formu
ä!
Przyk
äad 22. Wymuszenie wäñczenia dodatku
Je
Īeli Twój skoroszyt korzysta z formuá lub funkcjonalnoĞci któregoĞ ze stan-
dardowych dodatków, na przyk
áad AnalysisToolPak zawierającego szereg funkcji
in
Īynierskich, moĪesz spowodowaü jego zaáadowanie przy kaĪdym otwarciu
Twojego pliku. Poni
Īsza procedura zostanie wykonana po kaĪdym otwarciu sko-
roszytu. Nale
Īy ją umieĞciü w jego module.
Private Sub Workbook_Open()
AddIns("Analysis ToolPak").Installed = True
End Sub
Przyk
äad 23. Zamkniöcie dodatku
Poni
Īsza procedura zostaáa przypisana do zdarzenia wykonywanego przed za-
mkni
Ċciem skoroszytu
Workbook_BeforeClose
. U
Īyáem tego zdarzenia, niejako
kontynuuj
ąc poprzedni przykáad. Jednak oczywiĞcie nie jest to konieczne. Byü
mo
Īe Twój skoroszyt nie powinien pracowaü, gdy jakiĞ dodatek jest zaáadowany,
i musisz umie
Ğciü procedurĊ, która go wyáączy zaraz na początku. W tym przy-
padku powiniene
Ğ uĪyü zdarzenia
Workbook_Open
. A mo
Īe dodatek powinien
áadowaü siĊ po wejĞciu/wyjĞciu z okreĞlonego arkusza? Twój wybór! Na koĔcu
ksi
ąĪki zamieĞciáem listĊ zdarzeĔ, z których moĪesz do woli korzystaü.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Analysis ToolPak").Installed = False
End Sub
Przyk
äad 24. Zamkniöcie dodatku uruchomionego przez nasz skoroszyt
Praktyka mówi,
Īe choü 90 procent uĪytkowników nigdy nie dokonaáo wiĊkszej
modyfikacji narz
Ċdzi uĪywanych w swoim pakiecie Office, to wáaĞnie z tymi
10 procentami, które czego
Ğ dokonaáy, programiĞci mają najwiĊkszy káopot.
Trudno bowiem przewidzie
ü, na jaki grunt trafi Twój produkt. Na etapie projek-
towania musisz zatem za
áoĪyü róĪne sytuacje. Im wiĊcej ich przewidzisz, tym
stabilniejszy b
Ċdzie Twój program. Jednym z oczywistych zaáoĪeĔ wydaje siĊ,
Īe uĪytkownik mógá samodzielnie zaáadowaü jakiĞ dodatek (pozostaĔmy juĪ przy
naszym przyk
áadowym AnalysisToolPak), i nie ma potrzeby, aby po zamkniĊciu
naszego skoroszytu dodatek ten by
á dezaktywowany. Na początku musimy tylko
odczyta
ü, czy dodatek jest juĪ otwarty, i informacjĊ o jego stanie przechowaü
w zmiennej, która zostanie odczytana przy zamykaniu skoroszytu.
Przyk
äad 25. Rozwiñzanie drugie — stabilniejsze
67
Na poziomie modu
áu wpisz wiĊc deklaracjĊ zmiennej.
Dim dodatek As Boolean
To deklaracja zmiennej logicznej, która mo
Īe przyjmowaü wartoĞci Prawda lub
Fa
ász. Zadeklarowanie jej na poziomie moduáu (przed pierwszą procedurą
sub
lub
function
) zapewnia nam,
Īe jej wartoĞü bĊdzie przechowywana do za-
mkni
Ċcia skoroszytu, pod warunkiem Īe VBA nie bĊdzie wymagaá zresetowania
(rysunek 5.8).
Rysunek 5.8.
Gdy nast
ąpi zawieszenie wykonywania makr
(mimo do
áoĪenia wszelkich staraĔ zdarza siĊ
to cz
ĊĞciej, niĪ przypuszczasz), konieczne jest
zresetowanie VBA. Powoduje to, niestety,
wyzerowanie zmiennych
W module skoroszytu wpisz zdarzenie przy otwarciu.
Private Sub Workbook_Open()
dodatek = False
If AddIns("Analysis ToolPak").Installed = True Then
‘ je
Īeli zachodzi powyĪszy warunek to zmiana wartoĞci zmiennej dodatek...
dodatek = True
‘ ... i wyj
Ğcie z procedury
Exit Sub
‘ w przeciwnym wypadku za
áadowanie dodatku
Else
AddIns("Analysis ToolPak").Installed = True
End If
End Sub
... i przy zamkni
Ċciu.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘ wyjd
Ĩ jeĪeli zmienna dodatek ma wartoĞü True
If dodatek = True Then Exit Sub
AddIns("Analysis ToolPak").Installed = False
End Sub
Przyk
äad 25. Rozwiñzanie drugie — stabilniejsze
Jak ju
Ī wiesz z üwiczenia powyĪej, zdarzają siĊ sytuacje, gdy wartoĞü prze-
chowywana przez zmienn
ą w VBA moĪe zostaü — z róĪnych przyczyn —
zresetowana. Doprowadzi to do z
áego dziaáania procedury uruchamianej przy
zamykaniu skoroszytu. Nie wiadomo bowiem, czy informacja pobrana na po-
cz
ątku pracy jest jeszcze przechowywana.
Przyjmij sobie jeden arkusz do przechowywania danych. Daj mu trudn
ą nazwĊ,
której u
Īytkownik siĊ nie spodziewa, i nadaj mu wáaĞciwoĞü
Visible = 2
(rysunek 5.9).