ZROZUMIEĆ EXCELA VBA MAKRA I FUNKCJE GONET M. Księgarnia

Zrozumieć Excela

VBA makra i funkcje

Poznaj Visual Basic for Applications (VBA)

Naucz się tworzyć makra i własne funkcje

Zrozum Excela

Na rynku nie brakuje książek opisujących obsługę i zastosowania arkusza kalkulacyjnego MS Excel, żadna jednak nie wprowadzi Cię w tę tematykę tak skutecznie jak ta! Omiń rafy i białe plamy dokumentacji, skorzystaj z doświadczenia autora i śmiało wkrocz w świat niesamowitych możliwości Excela.

Poznaj konstrukcje języka VBA i naucz się przeprowadzać obliczenia za jego pomocą. Odkryj zastosowania formantów, zapanuj nad danymi opisującymi datę i czas, dowiedz się, jak radzić sobie z tekstami i wykorzystaj funkcje definiowane przez użytkownika. Przekonaj się też, do czego mogą Ci się przydać makra!

Struktura i konstrukcje VBA
Obiekty, właściwości i metody
Instrukcje warunkowe i komunikacja
efiniowanie i używanie tablic oraz funkcji
Obsługa wyjątków i zdarzeń
Zaawansowane struktury danych
Operacje na datach, czasach i tekstach
Operowanie kolorami i formatowanie warunkowe
Graficzne elementy sterujące
Funkcje makr programu Excel

Wstęp 15

Rozdział 1. Struktura i podstawowe konstrukcje języka VBA 23

Podstawowe informacje o języku programowania VBA 23
Edytor VBA 24
Uruchamianie i testowanie kodu 26
Makra i funkcje 28
Struktura modułów w VBA 29
Polecenia opcji 29
Deklaracje zmiennych i stałych globalnych 30
Definicje podprogramów 31
Makropolecenie utworzone w wyniku rejestracji 32
Skoroszyt makr osobistych i folder AddIns 34
Uruchamianie makropoleceń 34
Wprowadzanie kodu VBA w edytorze 35
Stosowanie nazw w kodzie VBA 35
Prosta funkcja zdefiniowana w VBA 37
Stosowanie komentarzy 38
Zmienne - typy i deklaracje 39
Określanie typu zmiennej w czasie wykonania 41
Deklaracje stałych 43
Wyrażenia 44
Podwójne znaczenie znaku równości 44
Lista operatorów w Visual Basicu 44
Specyfika użycia operatorów relacji do argumentów różnych typów 47
Użycie operatora dodawania do danych tekstowych 48
Interpretacja tekstu pustego i wartości pustej w VBA i w Excelu 49
Specyfika dzielenia całkowitego i operacji modulo 51
Użycie funkcji w wyrażeniach 51

Rozdział 2. Obiekty, ich właściwości i metody 57

Obiekt Range i jego właściwości 57
Właściwość Value 57
Właściwość Formula i właściwości pokrewne 58
Właściwość NumberFormat 59
Właściwość Text 60
Właściwość Count 62
Uproszczony sposób zapisu odwołania do zakresu 62
Metody 62
Hierarchia obiektów i nazwy kodowe 64
Wymiana informacji między arkuszem a kodem VBA 65
Komórka aktywna i komórki wybrane 65
Użycie instrukcji wiążącej With 66
Użycie adresów bez kwalifikatora arkusza 67
Sposoby odwołania do zakresu komórek przez adres 68
Ogólna postać definicji zakresu 68
Wykorzystanie nazw 69
Wykorzystanie właściwości Cells 69
Tworzenie odwołań pośrednich 70
Wykorzystanie właściwości Offset i Resize 71
Odwołania do sąsiednich komórek 72
Zmienne reprezentujące obiekty 72
Unia zakresów i zakresy złożone 73
Odwołania do wskazanych wierszy i kolumn 74
Kopiowanie zakresu nieciągłego z zachowaniem jego struktury 75
Metoda Find obiektu Range - wyszukiwanie adresu komórki o określonej zawartości 76
Obiekt zakresu a jego wartość 79
Jawne odwołanie do wartości zakresu 79
Pośrednie sposoby odwołania do wartości zakresu 80
Specyfika zakresów złożonych 81
Użycie autofiltra 82
Składnia i znaczenie parametrów 82
Filtr wykluczający trzy i więcej wartości 84
Odczyt ustawień filtra 85
Użycie filtra zaawansowanego 86
Kontrola procesu modyfikacji danych w arkuszu 88

Rozdział 3. Sterowanie wykonaniem kodu i komunikacja w języku VBA 91

Sterowanie wykonaniem kodu 91
Instrukcje warunkowe i instrukcje wyboru 91
Sterowanie przebiegiem kompilacji - kompilacja warunkowa 96
Instrukcje pętli 98
Awaryjne przerwanie wykonywania funkcji lub procedury 103
Instrukcje skoku 103
Komunikacja programu w Visual Basicu z użytkownikiem 107
Wyświetlanie okienka komunikatów 107
Wyświetlanie komunikatów na pasku stanu 110
Pobieranie informacji od użytkownika 112

Rozdział 4. Użycie tablic w Visual Basicu 117

Deklaracje tablic 117
Tablice statyczne 117
Tablice dynamiczne 118
Sprawdzanie zakresu indeksów tablicy 119
Tablice w zmiennych typu Variant 119
Odwzorowanie zakresu komórek w tablicy 119
Funkcja Array 121
Funkcja Split 122
Specyfika deklaracji zmiennych typu Variant i ich użycia jako tablic 123
Teksty w tablicach dynamicznych 124
Podstawowe operacje 125
Nadawanie wartości elementom tablicy 125
Kasowanie zawartości tablicy 127
Użycie operatora Not w odniesieniu do tablicy 128
Zmiana struktury tablic 129
Rozbudowa tablicy jednowymiarowej o drugi wymiar 129
Modyfikacja pierwszego wymiaru tablicy dwuwymiarowej 129
Tablice ułatwiają wymianę informacji z arkuszem 130
Tablice i zakresy jako alternatywne argumenty funkcji 130
Funkcje generujące tablice bazowe do użycia w arkuszu 131
Wykorzystanie arkuszowej funkcji Index w VBA 132
Wypełnianie zakresu zawartością tablicy 132
Odwzorowanie zakresu w zmiennej obiektowej 133
Stałe tablicowe Excela w VBA 134
Odczyt wartości stałych tablicowych w kodzie VBA 134
Zagnieżdżanie metody Evaluate 135
Zamiana zakresu na stałą tablicową 135
Zamiana tablicy VBA na stałą tablicową 138
Porównywanie tablic w VBA 139

Rozdział 5. Funkcje definiowane przez użytkownika 143

Sposoby przekazywania argumentów funkcji 143
Opcjonalne parametry funkcji 145
Użycie tablicy parametrów 147
Problem pustych argumentów 147
Problem tablic wśród argumentów ParamArray 148
Deklaracja nagłówka funkcji o zmiennej liczbie parametrów 149
Przekazywanie zmiennej liczby parametrów pomiędzy funkcjami 151
Przekazywanie parametrów przez wartość 151
Przekazywanie parametrów przez referencję 153
Funkcja zwracająca wynik w postaci tablicy 156
Przeliczanie wartości funkcji 157
Wywołanie makroinstrukcji z kodu VBA 158
Funkcje użytkownika podobne do funkcji standardowych 159

Rozdział 6. Wybrane zastosowania tablic 163

Przekształcenie tablicy dwuwymiarowej w jednowymiarową 163
Wyszukiwanie danych w strukturach dwuwymiarowych 166
Wyszukiwanie w zakresie 166
Wyszukiwanie w tablicy 168
Funkcje składające dowolne dane w tablice 169
Połączenie danych w tablicę jednowymiarową przez kopiowanie elementów 169
Połączenie danych w tablicę jednowymiarową za pośrednictwem tekstu 170
Ustalenie orientacji tablicy lub przekształcenie w tablicę dwuwymiarową 172
Usuwanie wybranego wiersza lub kolumny z tablicy 173
Sortowanie danych w obszarach i tablicach 175
Czyszczenie danych pochodzących z arkusza 177
Grupowanie arkuszy w skoroszycie 178

Rozdział 7. Metoda Evaluate i nazwy arkuszowe 181

Metoda Evaluate 181
Użycie funkcji OBLICZ z odwołaniem 182
Specyfika i ograniczenia metody Evaluate 184
Warianty użycia metody Evaluate 186
Kwalifikowane wywołanie funkcji używających metody Evaluate 186
Zagnieżdżona metoda Evaluate 187
Dane grupowe jako argumenty funkcji OBLICZ 188
Szacowanie formuł z polskimi nazwami funkcji 189
Użycie funkcji OBLICZ w obliczeniach iteracyjnych 190
Metoda Evaluate rozszerza możliwości funkcji ADR.POŚR 191
Użycie metody Evaluate w kodzie VBA 192
Alternatywa dla funkcji LICZ.JEŻELI i SUMA.JEŻELI 193
Uproszczenie zapisu wyrażeń wykorzystujących metodę Evaluate 195
Funkcja Eval do szacowania wyrażeń w VBA 196
Obiekt Names 198
Definiowanie nazw arkuszowych w kodzie VBA 198
Nazwy odnoszące się do zamkniętych skoroszytów 205
Stosowanie metody Evaluate do nazw arkuszowych 206
Nadawanie nazw stałym i formułom w arkuszu 207

Rozdział 8. Obsługa wyjątków i zdarzeń, kontrola poprawności danych 209

Obsługa błędów wykonania w kodzie VBA 209
Procedury niestandardowej obsługi błędów 209
Kody błędów w arkuszu 215
Funkcje walidujące wartość wyrażenia 217
Śledzenie wykonania kodu VBA - obiekt Debug 218
Identyfikacja miejsca, z którego wywołano makro lub funkcję 219
Identyfikacja komórki, z której wywołano funkcję UDF 220
Procedury obsługi zdarzeń 221
Koncepcja zdarzeń 221
Włączanie zgody na iteracje przed otwarciem skoroszytu 222
Operacje na zakresie wskazanym myszką 223
Poprzednia zawartość komórek 224
Dziedzictwo przeszłości - właściwości OnDoubleClick oraz OnEntry obiektu Application 231
Konsolidacja z automatyczną aktualizacją 232
Kontrola poprawności danych 233
Obiekt Range.Validation, jego metody i właściwości 233
Ograniczenia źródła listy rozwijanej 236
Ustalanie listy poprawności danych za pomocą formuły 237
Powiązanie listy poprawności danych z formatowaniem warunkowym 238
Wyświetlanie komunikatu o konieczności nowego wyboru w przypadku list zależnych 240
Modyfikacja sposobu wyświetlania listy rozwijanej 241
Ochrona listy rozwijanej przed nadpisaniem 243
Wprowadzanie danych z podpowiedzią 244

Rozdział 9. Adaptacja wybranych funkcji i metod VBA do użycia jako funkcji arkuszowych 247

Funkcja Val 247
Metoda InputBox 249
Przekazywanie wartości argumentów do formuł nazwanych 252
Metoda ConvertFormula 253
Funkcja CallByName - wywoływanie metod przez tekst ich nazwy 254
Dostęp do stałych predefiniowanych w VBA 258
Odczyt nazwy lub numeru arkusza 260
Wyświetlanie tekstów formuł w arkuszu 261
Dodawanie i edycja komentarzy w komórkach arkusza 262
Metoda NoteText (notatka tekstowa) 262
Właściwość Comment obiektu Range 263
Metoda AddComment obiektu Range 263
Metody ClearNotes i ClearComments z obiektu Range 264
Obiekt Comment i jego komponenty 264
Sposób wyświetlania komentarzy i znaczników komentarza 266
Kolekcja Comments 267
Metoda SpecialCells obiektu Range 267
Wstawianie i edycja komentarzy przez funkcje UDF 269
Wartości w komórkach scalonych 270
Określanie formatu komórek w VBA 272
Odczyt i zapis kodu formatu 272
Wykorzystanie informacji udostępnianych przez funkcję Format 273
Inne funkcje do formatowania w VBA 274

Rozdział 10. Wybrane problemy obliczeniowe 277

Zaokrąglanie liczb 277
Zaokrąglanie liczb z uwzględnieniem cyfr znaczących 277
Uwzględnienie zasady cyfry parzystej przy zaokrąglaniu 278
Obliczanie wartości wielomianu 278
Obliczanie pierwiastków równania kwadratowego 279
Rozwiązywanie równań nieliniowych z wykorzystaniem metody GoalSeek 280
Całkowanie numeryczne 282
Wspomaganie obliczania szeregów 285
Iloczyn pierwszych elementów tablicy 285
Rozwiązanie równania różniczkowego metodą Rungego-Kutty 287
Wspomaganie wykonywania wykresów 288
Wykresy funkcji opisanych wzorem 288
Korekta danych do wykresów funkcji nieciągłych 289
Wykresy funkcji nieciągłych - wykrywanie nieciągłości 291
Ukrywanie zawartości komórek w komentarzach 293
Generowanie liczb pseudolosowych w VBA 294
Wybrane zagadnienia kombinatoryki 295
Generowanie permutacji 295
Generowanie permutacji z powtórzeniami 297
Generowanie kombinacji 300
Planowanie serii rozgrywek sportowych 301
Zwiększona dokładność obliczeń 303
Zamiana odwołań w wyrażeniu na wartości 307

Rozdział 11. Przykłady zastosowań makroinstrukcji w chemii 309

Obliczanie masy molowej 309
Przeliczanie stężeń roztworów 312
Modyfikacja wyglądu i zawartości obiektów na poziomie znaków 319
Łączenie tekstów sformatowanych 321
Formatowanie wzorów chemicznych 323
Formatowanie wzorów chemicznych - inaczej 325
Formatowanie tekstu za pomocą polecenia SendKeys 328
Polecenie SendKeys 328
Wybrane skróty klawiaturowe, które działają w trybie edycji 330
Zastosowania polecenia SendKeys do formatowania komórki z tekstem 332

Rozdział 12. Alternatywne struktury danych: kolekcje i słowniki 335

Kolekcje 335
Tworzenie kolekcji i usuwanie jej elementów 335
Odczyt elementów kolekcji 337
Specyfika kolekcji w porównaniu z tablicami 338
Kolekcje jako argumenty procedur i funkcji oraz wynik funkcji 339
Ograniczenia kolekcji i sposoby ich obejścia 340
Słowniki 341
Tworzenie słownika 341
Odczyt i modyfikacja zapisów 343
Klucze mogą być obiektami zakresów (komórkami) 345
Różnice między kolekcją a słownikiem 345
Wykorzystanie słownika w praktyce 346
Kopiowanie słownika 348

Rozdział 13. Operacje na danych oznaczających datę i czas 351

Podstawy operowania datami w VBA 352
Rozpoznawanie dat w arkuszu za pomocą VBA 353
Interpretacja tekstu w arkuszu jako daty 354
Funkcja UDF do konwersji dat w formie tekstu 355
Przegląd funkcji VBA do operacji na datach 356
Funkcje Date, Time i Now 356
Funkcje DateValue i TimeValue 356
Funkcje DateSerial i TimeSerial 356
Funkcja IsDate 356
Funkcja DateAdd 357
Funkcja DatePart 358
Funkcja DateDiff 359
Funkcja FormatDateTime 359
Funkcja Format 360
Funkcja MonthName 364
Funkcja WeekdayName 364
Funkcja Timer 364
Metoda Wait 365
Metoda OnTime 365
Dokładny pomiar czasu 366
Ciągłe wyświetlanie czasu - stoper sekundowy 369
Ciągłe wyświetlanie czasu - inne rozwiązanie stopera 371
Niestandardowe minuty w indeksie górnym (VBA) 374
Właściwości Value i Value2 obiektu Range w rozpoznawaniu dat 374
Data i czas systemowy w kodach formatu 375

Rozdział 14. Operacje z udziałem tekstów 377

Funkcje do operacji na tekstach w VBA 377
Funkcje LTrim, RTrim i Trim ($) 377
Funkcje Chr i ChrW ($) 377
Funkcje Asc i AscW 378
Funkcje Hex i Oct ($) 378
Funkcje LCase i UCase ($) 378
Funkcje Left i Right ($) 378
Funkcja Len 379
Funkcja Mid ($) 379
Instrukcja Mid 379
Funkcje Space i String ($) 380
Funkcja Format ($) 380
Instrukcje LSet i RSet 383
Funkcje InStr i InStrRev 383
Funkcja StrComp 384
Funkcja StrConv 385
Funkcja StrReverse 386
Funkcja Replace 386
Funkcja Filter 387
Funkcja Join 388
Łączenie tekstów z użyciem VBA 388
Wykorzystanie operatora złączenia tekstów 388
Inny wariant z operatorem złączenia tekstów 389
Wykorzystanie funkcji Join 390
Funkcja Join w wersji minimum 390
Operator Like - porównywanie tekstów 392
Położenie pierwszej i ostatniej cyfry w tekście 393
Podział tekstu w kolumnie 394
Metoda TextToColumns 394
Metoda Parse 397

Rozdział 15. Operowanie kolorami i formatowanie warunkowe 399

Sposób przedstawiania kolorów 399
Odczyt koloru tła lub czcionki bez użycia VBA 402
Wykorzystanie kolorów do oznaczania komórek w arkuszu 403
Rozjaśnianie i ściemnianie kolorów 404
Operacje na komórkach sformatowanych w określony sposób 405
Sumowanie komórek wykorzystujących ten sam kolor czcionki 405
Bezpośrednie oznaczanie komórek numerami kolorów tła 406
Przyspieszenie reakcji na zmiany kolorów przez wykorzystanie niestandardowego zdarzenia 409
Symulowana lista rozwijana 411
Formatowanie warunkowe w VBA 412
Dostęp do definicji formatów warunkowych z poziomu VBA 412
Przeliczanie reguł formatowania warunkowego 418
Definiowanie reguł formatowania warunkowego za pomocą funkcji UDF 419
Kopiowanie formatowania warunkowego z zamianą na formatowanie stałe 425
Symulacja skali barw za pomocą VBA 427
Selektywne kopiowanie reguł formatowania warunkowego 428

Rozdział 16. Ograniczenia i możliwości funkcji UDF 429

Funkcje UDF wywoływane bezpośrednio 429
Modyfikacja parametrów wykresu 431
Funkcje UDF wywoływane w sposób pośredni 432
Ogólne wskazówki co do użycia metody Evaluate 432
Definiowanie nazw za pomocą funkcji UDF 434
Zmiana elementów formatowania innych komórek 439
Zmiana zawartości i koloru czcionki w innych komórkach 441
Usunięcie zawartości komórek 443
Wpis wartości do innej komórki 444
Tworzenie list rozwijanych 445
Uruchamianie funkcji UDF za pomocą funkcji HIPERŁĄCZE 446
Specyfika działania funkcji HIPERŁĄCZE 446
Użycie funkcji UDF w hiperłączu 447
Wykonanie kodu VBA przy uaktywnieniu listy rozwijanej 449
Współdziałanie funkcji UDF z procedurami obsługi zdarzeń 450
Kopiowanie komórek z pełnym formatowaniem 451
Funkcja wyszukiwania zwracająca wynik sformatowany 453
Rzeczywista długość tekstu 455
Formatowanie fragmentu tekstu 458
Funkcja łącząca teksty sformatowane 460

Rozdział 17. Ciekawe pomysły z użyciem VBA 465

Monitorowanie zmian w komórkach 465
Działanie jednorazowe 465
Działanie wielokrotne 466
Odczyt danych z zamkniętego skoroszytu 466
Rozwiązanie klasyczne 467
Rozwiązanie z wykorzystaniem ADO 467
Konstrukcja odwołań zewnętrznych 469
Wykorzystanie nazw arkuszowych 472
Nadmierna objętość skoroszytu po usunięciu części danych 473
Wyznaczenie różnicy zakresów 475
Wyraźne zaznaczenie komórki aktywnej lub zakresu selekcji 476
Konwersja stylu liczb za pomocą narzędzi Visual Basica 477
Metoda Range.Replace 477
Metoda Range.TextToColumns 478
Funkcja Replace 479
Funkcja WorksheetFunction.Substitute 480
Funkcja Val 481
Funkcja CDbl 481
Funkcje Str i CStr 481
Funkcja Format 482
Obliczenia uwzględniające ukryte kolumny 483
Rozwiązanie wykorzystujące właściwość Width 483
Przeliczanie formuł 484
Ustalanie absolutnej wielkości komórki 485
Problemy z użyciem metody AutoFit 488
Przełączanie między alternatywnymi wynikami w komórce za pomocą klawiatury 489
Ochrona komórek przed przypadkową edycją 491
Powiększenie zaznaczonych komórek 492
Użycie schowka systemowego z poziomu VBA 494
Obiekt pośredniczący DataObject 494
Wykorzystanie samego obiektu DataObject bez schowka 497
Zapis do schowka w Windows 8 i 10 498
Opróżnianie schowka 500
Użycie funkcji UDF 501

Rozdział 18. Graficzne elementy sterujące (kontrolki ekranowe, formanty) 503

Rodzaje graficznych elementów sterujących i ich przeznaczenie 503
Dodawanie kontrolek (formantów) do arkusza 504
Formanty formularza 507
Formanty ActiveX 510
Niestandardowe wykorzystanie pola listy 516
Zamienniki formantów pola tekstowego 517
Obiekty graficzne w kodzie VBA 520
Pola tekstowe i formanty formularza 521
Formanty ActiveX 522
Obiekty graficzne w funkcjach UDF 523
Makroinstrukcje przypisywane do formantów w arkuszu 524
Formanty formularza 525
Formanty ActiveX 525

Rozdział 19. Funkcje makr Excela w wersji 4.0 529

Geneza makr XLM 529
Podstawy użycia makr XLM w arkuszu 530
Wstawianie arkuszy makr do skoroszytu 530
Dostęp do funkcji makr XLM 530
Wykorzystanie Międzynarodowego arkusza makr 531
Przeliczanie formuł w arkuszach makr 532
Wywołanie funkcji makr XLM w kodzie VBA 533
Bezpieczeństwo użycia makr XLM 534
Odwołania do zakresów wielokomórkowych w funkcjach XLM 534
Przekazywanie parametrów w wywołaniu formuły nazwanej 536
Jeden parametr 536
Dwa parametry 537
Przykłady użycia funkcji makr XLM w arkuszu i w formułach nazwanych 538
Ukryta przestrzeń nazw 546

Rozdział 20. Przegląd funkcji makr XLM 547

Składnia funkcji makr Excela 4.0 i opis znaczenia parametrów 547
ADR.TEKST = REFTEXT Kategoria: Wyszukiwania i adresu 547
DOKUMENTY = DOCUMENTS Kategoria: Informacyjne 548
FORMUŁA.TRYB.ADR = FORMULA.CONVERT Kategoria: Wyszukiwania i adresu 549
KOM.AKT = ACTIVE.CELL Kategoria: Informacyjne 549
NAZWY = NAMES Kategoria: Informacyjne 550
O.APLIKACJI = GET.WORKSPACE Kategoria: Informacyjne 551
O.DEFINICJI = GET.DEF Kategoria: Informacyjne 558
O.DOKUMENCIE = GET.DOCUMENT Kategoria: Informacyjne 559
O.FORMULE = GET.FORMULA Kategoria: Informacyjne 566
O.KOMÓRCE = GET.CELL Kategoria: Informacyjne 567
O.NAZWIE = GET.NAME Kategoria: Informacyjne 572
O.NOTATCE = GET.NOTE Kategoria: Informacyjne 574
O.OBIEKCIE = GET.OBJECT Kategoria: Informacyjne 574
O.OPCJACH.LISTY = OPTIONS.LISTS.GET Kategoria: Informacyjne 582
O.SKOROSZYCIE = GET.WORKBOOK Kategoria: Informacyjne 582
OKNA = WINDOWS Kategoria: Informacyjne 585
PLIKI = FILES Kategoria: Informacyjne 586
SZACUJ = EVALUATE Kategoria: Wyszukiwania i adresu 586
ZAZNACZENIE = SELECTION Kategoria: Informacyjne 588
Literatura cytowana i uzupełniająca 589

Skorowidz 590

600 stron, Format: 16.0x24.0cm, oprawa miękka

Księgarnia nie działa. Nie odpowiadamy na pytania i nie realizujemy zamówien. Do odwolania !.