Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00038 007598 18991659 na godz. na dobę w sumie
Zrozumieć Excela. VBA - makra i funkcje - książka
Zrozumieć Excela. VBA - makra i funkcje - książka
Autor: Liczba stron: 600
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-5694-8 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> aplikacje biurowe >> excel
Porównaj ceny (książka, ebook (-35%), audiobook).

Programowanie i makra w Excelu? Nic strasznego!

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!

Dowiedz się, jak wykorzystać Excela do zautomatyzowania swojej pracy!

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji. Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli. Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce. Redaktor prowadzący: Małgorzata Kulik Projekt okładki: Studio Gravite / Olsztyn Obarek, Pokoński, Pazdrijowski, Zaprucki Grafika na okładce została wykorzystana za zgodą Shutterstock.com Wydawnictwo HELION ul. Kościuszki 1c, 44-100 GLIWICE tel. 32 231 22 19, 32 230 98 63 e-mail: helion@helion.pl WWW: http://helion.pl (księgarnia internetowa, katalog książek) Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie/zrexvb Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję. ISBN: 978-83-283-5694-8 Copyright © Helion 2019 Printed in Poland. • Kup książkę • Poleć książkę • Oceń książkę • Księgarnia internetowa • Lubię to! » Nasza społeczność Spis treści 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 Poleć książkęKup książkę 4 Zrozumieć Excela. VBA — makra i funkcje 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 Poleć książkęKup książkę Spis treści 5 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 Poleć książkęKup książkę 6 Zrozumieć Excela. VBA — makra i funkcje 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 Poleć książkęKup książkę Spis treści 7 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 Poleć książkęKup książkę 8 Zrozumieć Excela. VBA — makra i funkcje 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 Poleć książkęKup książkę Spis treści 9 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 Poleć książkęKup książkę 10 Zrozumieć Excela. VBA — makra i funkcje 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 Poleć książkęKup książkę Spis treści 11 Łą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 Poleć książkęKup książkę 12 Zrozumieć Excela. VBA — makra i funkcje 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 Poleć książkęKup książkę Spis treści 13 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 Poleć książkęKup książkę 14 Zrozumieć Excela. VBA — makra i funkcje 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 Poleć książkęKup książkę Rozdział 5. Funkcje definiowane przez użytkownika W zastosowaniach naukowych najczęściej wykorzystuje się Visual Basic do definiowania funk- cji, które mają uzupełnić dostępny standardowo zestaw o funkcje realizujące specyficzne po- trzeby obliczeniowe. Wstępne informacje o funkcjach podano w rozdziale 1., w podrozdziale „Makra i funkcje”, dalej w punkcie „Definicje podprogramów”, a prosty przykład funkcji zamiesz- czono w tym samym rozdziale, w podrozdziale „Prosta funkcja zdefiniowana w VBA”. Teraz deklarowanie i definiowanie funkcji zostanie omówione bardziej szczegółowo. Sposoby przekazywania argumentów funkcji Jeśli argument funkcji (lub procedury Sub) jest zmienną, to może być przekazany do funkcji wywołującej dwoma sposobami: przez odwołanie (używa się również określeń referencja lub wskazanie) lub przez wartość. Pierwszy sposób jest w VBA domyślny, gdyż jest szybszy i bardziej efektywny, jeśli chodzi o gospodarkę pamięcią, ale nie zawsze może być użyty. Jeśli chcemy przeka- zać argument przez wartość, należy poprzedzić jego nazwę w deklaracji funkcji słowem kluczowym ByVal. Jeśli natomiast chcemy zaakcentować chęć posłużenia się odwołaniem, możemy analo- gicznie użyć słowa ByRef, jednak nie jest to konieczne, gdyż jest to ustawienie domyślne. W przy- padku odwołania, do funkcji wywołującej przekazywany jest faktycznie adres zmiennej i w związku z tym wszystkie zmiany dokonane na tej zmiennej będą widoczne również po wyjściu z funkcji (ogólniej: procedury). W przeciwieństwie do tego, gdy argument jest przekazywany przez war- tość, tworzona jest kopia argumentu o tej samej wartości; procedura operuje na tej kopii, a ory- ginalna zmienna zachowuje swoją niezmienioną wartość. Przeanalizujmy to na prostym przykładzie. Sub Sumuj() Dim a a = 5 Debug.Print Pomnóż(3, a) + a z ByVal 20, bez ByVal 30 Debug.Print Pomnóż(5, a) + a z ByVal 30, bez ByVal 150 End Sub Poleć książkęKup książkę 144 Zrozumieć Excela. VBA — makra i funkcje Function Pomnóż(x, ByVal y) y = x * y Pomnóż = y End Function Ze względu na utrudnioną analizę kodu należy unikać sytuacji, gdy wywołanie funkcji powo- duje „skutki uboczne”, jak w powyższym przykładzie zmianę wartości zmiennej a w razie po- minięcia słowa ByVal. Przy przekazywaniu argumentów przez odwołanie należy upewnić się, że typy powiązanych zmiennych są identyczne, w przeciwnym razie wystąpi błąd kompilacji ByRef Argument Type Mismatch. Jeżeli argumenty są przekazywane przez wartość, typ zmiennej zostanie automatycznie dopasowany, jeśli to będzie możliwe. Należy zwrócić uwagę na specyfikę przekazywania argumentów, gdy są nimi odwołania do obiek- tów, najczęściej zakresów. Jeśli parametr procedury jest zadeklarowany jako Range lub Object, to nie ma znaczenia, czy użyjemy wcześniej ByVal lub ByRef. Nie można utworzyć lokalnej kopii obiektu w procedurze i w związku z tym operacje odnoszące się do obiektu są od razu widoczne w tym obiekcie. Natomiast jeśli parametr procedury jest zadeklarowany jako Variant lub inny typ standardowy, a jako argument przekażemy odwołanie do zakresu (lub innego obiektu), to sytuacja jest bardziej złożona. Jeśli użyjemy słowa ByVal, to jest tworzona lokalna zmienna o warto- ści pobranej z zakresu, a sam zakres nie jest modyfikowany. Jeśli jawnie lub przez domniemanie użyjemy słowa ByRef, a argument zostanie przekazany w formie — na przykład — Range( C5 ) lub [C5], to wartość zakresu również tym razem zostanie tylko skopiowana do zmiennej lokal- nej. Jedynie w przypadku, gdy parametr procedury jest zadeklarowany jako Variant, a argu- ment zostanie przekazany w formie zmiennej obiektowej (przez nazwę), to wówczas faktycznie ta zmienna obiektowa będzie modyfikowana (czyli zachowanie będzie takie, jak przy użyciu deklaracji As Range albo As Object). Poniższy listing ilustruje ten najbardziej nietypowy przypadek. Private Sub ChangeValue(ByRef a) As Variant Jeśli zmienna a jest zadeklarowana jako wariant, to na ogół przekazywana jest wartość a obiekt tylko wtedy, gdy argumentem będzie nazwana zmienna obiektowa Debug.Print a a = 35 Debug.Print a Debug.Print [A6] wartość komórki zmieniona, jeśli argumentem jest obiekt End Sub Sub TestChV() Dim b As Range Set b = [A6] ChangeValue b b - argumentem jest obiekt, [A6], Range( A6 ) - argumentem jest wartość End Sub Następna komplikacja dotyczy tablic. Całe tablice (deklarowane po słowach Dim, ReDim lub Static) muszą być przekazywane przez referencję, natomiast pojedyncze elementy tablicy można prze- kazywać przez wartość. Jeżeli chcemy przekazać taką tablicę do innej funkcji przez wartość (aby uniknąć modyfikacji oryginalnej tablicy), to mamy do wyboru dwa sposoby: albo w wywołaniu Poleć książkęKup książkę Rozdział 5.  Funkcje definiowane przez użytkownika 145 nazwę tablicy umieścimy w nawiasach, albo utworzymy kopię oryginalnej tablicy w zmiennej typu Variant i tę kopię przekażemy jako argument do dalszego przetwarzania. Sytuację ilustruje poniższy listing: Sub TestDim() Dim a(1 To 3) As Long a(1) = 1: a(2) = 4: a(3) = 9 Wariant 1 Modyfikuj a tablica a zostanie zmodyfikowana Wariant 2 Modyfikuj (a) tablica a nie zostanie zmodyfikowana Wariant 3 Dim kopia kopia = a Modyfikuj kopia tablica a nie zostanie zmodyfikowana Debug.Print a(1); a(2); a(3) End Sub Private Sub Modyfikuj(t) Dim u, v, i For i = LBound(t) To UBound(t) v = t(i) t(i) = v + u u = v Debug.Print t(i); Next i Debug.Print End Sub Słowa ByVal lub ByRef mogą być używane wraz ze słowem Optional, natomiast nie mogą być używane do tablic ParamArray (patrz podrozdział „Użycie tablicy parametrów”). Opcjonalne parametry funkcji W deklaracji funkcji można określić zarówno typy parametrów, jak i typ wyniku. Jak w przy- padku deklaracji zmiennych, służy do tego słowo kluczowe As lub znak deklaratora. Brak dekla- racji typu oznacza typ Variant. Niektóre — lub nawet wszystkie — parametry funkcji można określić jako opcjonalne (nieobo- wiązkowe). Muszą one wystąpić jako ostatnie na liście parametrów. Nazwę takiego parametru w deklaracji poprzedzić trzeba słowem Optional. Jeżeli jest więcej niż jeden taki parametr, sło- wo to powinno się znaleźć przed każdym z nich. W deklaracji funkcji można podać jego war- tość domyślną, która będzie użyta w razie braku argumentu w wywołaniu funkcji. Typ para- metru opcjonalnego może być określony jawnie, może też pozostać nieokreślony (wtedy jest to typ Variant). Może to wyglądać na przykład tak: Optional wsp As Double = 1 lub Optional wsp# = 1 Poleć książkęKup książkę 146 Zrozumieć Excela. VBA — makra i funkcje Jednak deklaracja Optional wsp = 1# ma inne znaczenie. W tym drugim przypadku parametr wsp jest typu Variant, a tylko jest inicjowa- ny stałą typu Double. W wywołaniu lub treści funkcji można mu nadawać wartości innych typów. Jeśli wartość domyślna parametru opcjonalnego nie została określona, a w wywołaniu wartość odpowiedniego argumentu nie została podana, to jeśli parametr był typu Variant, przyjmuje on specjalną wartość Missing, która nie może być użyta do obliczeń (ale może być zidentyfikowana — patrz niżej), a jeśli typ parametru był określony, przyjmuje on wartość, jaką domyślnie przyjmują zmienne określonego typu. Parametr opcjonalny m
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Zrozumieć Excela. VBA - makra i funkcje
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: