tupaj.com

Kiedy czasem pracujemy na skoroszycie z dużą liczbą arkuszy zdarza się że w celu skopiowania zawartości komórki musimy przewijać ekran w prawo lub lewo aby dostać się do określonego arkusza. Gorzej jest jeszcze kiedy przyjdzie nam przełączać się co chwilę między tymi arkuszami. W excelu zawarta została funkcja, która znacząco ułatwia życie w tym zakresie. Mianowicie kliknięcie w jedną ze strzałek w lewym dolnym rogu ekranu excela wyświetla listę wszystkich arkuszy i możemy szybko przejść do pożądanego arkusza. Funkcja dostępna jest też w poprzednich wersjach Excela.

Do tej pory często poruszałem temat procedur Sub, które wykonywały określone zadanie po czym ich rola zwykle się kończyła lub ograniczała się do wielokrotnego wykonania sekwencji identycznych czynności. Teraz omówię sposób utworzenia własnej formuły do zastosowania w komórkach Excela. Zakładam że masz problem ze znalezieniem tej właściwej formuły lub chcesz się po prostu dowiedzieć jak to zrobić.

Istotą działania formuły jest to że zwraca ona jakąś wartość w zależności od argumentów lecz niczego nie robi z formatem komórki.
Do utworzenia formuły potrzebujemy więc utworzenia funkcji w kodzie VBA. Robi się to podobnie jak w przypadku procedur Sub z tą różnicą że zamiast wyrażenia Sub nazwa () piszemy Function nazwa () funkcję zamykamy wyrażeniem End function

Mniej więcej tak

Function mojafunkcja () as Type
kod funkcji
End Function

oczywiście zamiast słowa type wpisujemy prawidłowy typ danych jaki ma zwracać nasza funkcja np Byte albo String albo Variant itp

i jeszcze coś ważnego:
zawsze przed słowami End Function przekazujemy wynik obliczeń lub działania formuły ze zmiennej do nazwy funkcji

wygląda to tak

Function mojafunkcja (jakaśzmienna1) as Type
…
‘coś tam się dzieje w naszej funkcji ze zmienną jakaśzmienna1
…
mojafunkcja=jakaśzmienna1
End Function

Przykładowa banalnie prosta funkcja zamieniająca mile na kilometry wyglądałaby tak

Function milenakm (mile as integer) as single
milenakm=mile*1,6
End function

Kod funkcji podobnie jak kod procedur Sub umieszczamy w Module VBE

W trakcie pisania artykułu posiłkowałem się tą książką
Vademecum Walkenbacha

W artykule Dodawanie przycisku do uruchamiania makra w Excelu 2003 opisałem metodę przypisania przycisku (takiego jak w większości aplikacji windows) do uruchomienia makra czyli tego typu

Można jednak utworzyć dużo prościej taki przycisk w oparciu o dowolny element graficzny w excelu jak pokazane jest niżej

Wybieramy kształt (ja wybrałem prostokąt i prostokąt z zaokrąglonymi rogami , ale można nawet te najbardziej udziwnione - jak chmurki czy gwiazdki)

Przypisujemy makro (ja miałem napisane jedno dla przykładu)

Pomijam oczywiście etap tworzenia makra, gdyż był on omawiany wielokrotnie w poprzednich wpisach
Zaletą tego rozwiązania jest większa estetyka i większa dowolność formatowania kształtu i koloru tła przycisku
O ile w zwykłych “windowsowych” przyciskach nie można było zmienić koloru tła a jedynie tekstu o tyle tutaj jest wielkie pole do popisu i przede wszystkim jest to rozwiązanie o wiele szybsze i bardziej intuicyjne w realizacji.

Polecam spróbować.

Książka, która może pomóc w lepszym zrozumieniu tego tematu:

Pobierz fragment

Skoroszyt Excela po otwarciu ma zwykle dostępne 3 arkusze. Liczbę tę możemy zmieniać przez tworzenie nowych lub usuwanie istniejących.

Aby zmienić domyślną ilość arkuszy po uruchomieniu Excela należy ustawić odpowiednią opcję w menu Narzędzia -> Opcje lub użyć odpowiedniego polecenia w języku VBA

Ustalanie domyślnej liczby arkuszy za pomocą menu

W Excelu 97 - 2003

W Excelu 2010 (prawdopodobnie także w wersji 2007)

Taka zmiana pociąga za sobą konsekwencje że wszystkie nowo utworzone skoroszyty będą mieć tyle arkuszy ile ustawimy w menu aż dokonamy ponownie zmiany. Czasem jest potrzeba aby tylko jeden nowy skoroszyt miał nietypową ilość arkuszy. Jeśli jest to różnica rzędu 1-2 arkuszy to szkoda pisać w tym celu makro ale jeśli chcemy utworzyć dla odmiany skoroszyt o liczbie arkuszy o 20 większej niż normalnie to warto napisać takie makro tym bardziej że zajmie to jedynie 3 może 4 linijki kodu.

Dostęp do pełnej treści artykułu wymaga zapisania się na listę adresową (zapisanie na listę jest bezpłatne)

Chcę mieć dostęp

Niedawno napisałem o tworzeniu procedur z parametrami.
Samo w sobie jest to ciekawe i daje duże możliwości, zwłaszcza jeśli piszemy kod, w którym niektóre grupy poleceń często się powtarzają. Wyższość procedur z parametrami nad tymi bez polega na tym że:
- może wykonywać różne zadania w zależności od argumentów, co w przypadku zwykłej procedury jest trudne - musiałaby się odnosić do zawartości określonych komórek arkusza
- jest bardziej zwięzła i w większości przypadków szybsza w stosunku do procedury bezargumentowej
- da się ją wywoływać kaskadowo (można ją łatwiej zagnieździć bo jest niezależna od miejsca w którym została wywołana)

Dobrze, ale jak się mają zalety stosowania argumentów opcjonalnych w stosunku do normalnie deklarowanych argumentów.
- argumenty opcjonalne nie muszą być podawane
- obecność argumentów opcjonalnych nie zmusza nas do pisania kilku odrębnych procedur dla różnych zestawów argumentów
A teraz konsekwencje:
- w deklaracji takiej procedury argumenty nieopcjonalne jeśli występują to muszą być deklarowane w pierwszej kolejności
jeśli procedura zawiera więcej niż jeden argument, to wywołując ją argumenty rozdzielamy przecinkami, nawet jeśli ich wartości są pomijane, na przykład jeśli mamy procedurę z 5 argumentami z czego 2 są opcjonalne to piszemy tak:
Call procedurka(42, 3, 4, , )
Ułatwieniem jest podpowiedź interpretera VBA w trakcie wywoływania procedury

W miejsce argumentów opcjonalnych zostaną wstawione wartości domyślne jeśli zadeklarujemy je w procedurze, jeśli ich nie zadeklarujemy to wstawione zostaną zera.
Deklaracja wartości domyślnej została pokazana poniżej. Przykładowej zmiennej velocity została domyślnie nadana wartość 50 jeśli argument jest jedynym argumentem i na dodatek opcjonalnym to taką procedurę można wywołać podając samą jej nazwę niezapominając jednak o poprzedzającym słowie Call

Sub mojaproc1(Optional velocity As Byte = 50)
MsgBox “Twoja prędkość: ” & velocity
End Sub

Sub wywolaj()
Call mojaproc1
Call mojaproc1(120)
End Sub

Poniżej sposób i efekt wywołania kolejno tej samej procedury:
raz z pominięciem argumentu i drugi raz z podaniem argumentu innego niż domyślny

Poniżej jeszcze przykład procedury 5 argumentowej której zadaniem jest wyświetlenie objętości bryły w zależności od typu podstawy. Wykorzystałem tutaj instrukcję Select Case

Sub proc5arg(podstawa As String, wysokosc As Byte, Optional bok1 As Byte = 1, Optional bok2 As Byte = 1, Optional promien As Integer = 1)
Dim objetosc As Single
Const liczbaPi = 3.1415
Select Case podstawa
Case “kwadrat”
objetosc = bok1 * bok1 * wysokosc
Case “prostokat”
objetosc = bok1 * bok2 * wysokosc
Case “kolo”
objetosc = promien * promien * liczbaPi * wysokosc
End Select
MsgBox objetosc
End Sub

i zrzut ekranu