Archive for the ‘Porady excela’ Category

Excel i VBA procedury Sub z argumentami opcjonalnymi

Thursday, March 1st, 2012

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

autopodpowiedz.png

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

callmp_opt1.png

callmp_opt2.png

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
zrzutekranu.png

Excel i VBA - polecenia zapisu i zamknięcia skoroszytu

Friday, February 24th, 2012

Ostatnio pisałem o sposobach na automatyczne otwarcie makra po uruchomieniu skoroszytu. Teraz napiszę jak je zapisać i zamknąć automatycznie po wykonaniu wszystkich poleceń. Nie polecam na początek łączenia obu tych umiejętności w jednym pliku, gdyż może nas to pozbawić kontroli nad działaniem makr :) ale z czasem przyjdzie nam pomysł jak to połączyć i uniknąć problemów.
Aby wobec tego nie przeciągać dalej:

Sub makro()
‘…. jakieś instrukcje
ActiveWorkbook.Save
ActiveWorkbook.Close

‘ a można też krócej
ActiveWorkbook.Close savechanges:=True
‘ tu jednak trzeba zwrócić uwagę na to żeby dany skoroszyt miał już swoją nazwę
‘ jeśli nie ma to trzeba na końcu tej komendy dopisać filename:=”Twojanazwaskoroszytu.xls”
End Sub

makrozamyka1.png

Nasze makro możemy uruchomić dla ułatwienia przyciskiem, który sobie dodamy do arkusza

przyciskexcel2010.png

Gdybyś nie wiedział jak dodać sobie przycisk do menu (wstęgi) to poniżej pokazałem

przyciski.png

Gdy już wszystko gotowe to wciskamy nasz przycisk. Po wciśnięciu skoroszyt się zamknie.

Excel i VBA automatyczne uruchomienie makra przy otwarciu skoroszytu: sposób 2

Tuesday, February 21st, 2012

Jak obiecałem kilka dni temu kontynuuję temat automatycznego uruchamiania makra w Excelu. Ten sposób podobnie jak poprzedni działa we wszystkich wersjach Excela.
Oto co należy zrobić:

1. Po otwarciu skoroszytu naciśnij kombinację klawiszy Prawy Alt + F11 Wówczas pojawi się okno eksploratora oraz edytora kodu VBA
excelaltf11.png

2. W oknie eksploratora projektu wybieramy This Workbook
excelaltf11_1.png

3. Z menu podręcznego wybieramy View code
excelaltf11_5.png

4. W oknie edytora kodu u góry wybrać Workbook zamiast General.

excelaltf11_3.png

5. Wewnątrz automatycznie wstawionego kodu Private Sub workbook_Open () wstawić swoje instrukcje lub odwołania do innych makr

excelaltf11_4.png

6. Zapisać skoroszyt, zamknąć go i uruchomić.

.png

W obydwu sposobach działa odwołanie do nazwy (zwykłego) makra jak i do makra z parametrami więc można stosować dowolnie.

Pobierz ten artykuł w formie pliku pdf

Pobierz gotowy plik excel z makrem

Excel i VBA - odwoływanie do procedury z parametrami

Saturday, February 18th, 2012

Ostatnio napisałem że aby uruchomić kod jednej procedury Sub nazwa () w innej procedurze wystarczy podać tylko jej nazwę.
To wszystko prawda. Jeśli jednak utworzymy procedurę z argumentami to podanie samej jej nazwy (z parametrami oczywiście) nie zadziała. Mam na myśli taką procedurę

Sub mojaprocedurka (argument1, argument2)
‘ różne instrukcje np

Cells(1,1)=(argument1+argument2)/2
‘ w komórce A1 aktywnego arkusza wyświetli się średnia argumentów podanych w nawiasach

End Sub

Jej wywołanie na zasadzie

mojaprocedurka (argument1, argument2)

Nie przyniesie pożądanego rezultatu a jedynie błąd interpretera

Aby ją wywołać należy użyć następującej składni

Call mojaprocedurka(argument1, argument2)

Procedura z argumentami wykonuje jakieś zadanie w oparciu o argumenty podane w nawiasach np wylicza objętość walca w oparciu o promień i wysokość. Argumenty mogą byś różnego typu i może być ich dowolna liczba (oczywiście bez przesady).

Nie będę się na dłużej rozpisywał na temat tworzenia procedur z argumentami, faktem jest że taka konstrukcja nieraz bardzo upraszcza tworzenie aplikacji w VBA i zmniejsza ilość koniecznego kodu, więc warto je stosować tam gdzie nadarza się okazja.

Być może wrócę do tego niebawem

Excel 2010 zagnieżdżenie funkcji jeżeli - jeszcze raz o tym

Saturday, January 7th, 2012

Dość dawno temu pisałem o zagnieżdżonej funkcji JEŻELI w Excelu, a ponieważ doszedłem do wniosku że dałoby się to łatwiej wyjaśnić postanowiłem napisać o tym ponownie.

Funkcja IF lub po polsku JEŻELI ma następującą konstrukcję

JEŻELI (test logiczny; wartość jeśli prawda; wartość jeśli fałsz) - tego możemy się dowiedzieć z pomocy w Excelu.

Jeśli rozumiemy istotę działania tej funkcji to jej zagnieżdżenie nie sprawi nam już żadnego problemu
Z tej prostej definicji wynikają następujące wnioski:
- Argument pierwszy - test logiczny - ma za zadanie porównać coś z czymś

testem logicznym może być na przykład:
A1 > C1
D1 < 20
E2 > SUMA(A1:D1)
itp

- Argument drugi - wartość jeśli prawda - w tym miejscu wpisujemy konkretną wartość jak na przykład
1, 100, “Franek” itp
lub …
właśnie …
Może to być funkcja jak SUMA(A10:A100), PRAWY(tekst,2), JEŻELI(tl;wjp;wjf)

Argument trzeci właściwie może być tym samym pod względem definicji co argument drugi z tą różnicą że obowiązuje dla niespełnionego testu logicznego czyli np A1>C1 jest nieprawdą bo w komórce C1 wpisaliśmy wartość większą niż jest a A1

Argumenty oddzielamy średnikami ( ; ) a tam gdzie trzeba dajemy nawiasy - zresztą ich pomijanie jest najczęstszą przyczyną błędów
Jak widzimy nic nie stoi na przeszkodzie aby zagnieżdżoną funkcję jeżeli zapisać na przykład tak

JEŻELI( test_logiczny_główny;instrukcja_wewnętrzna_JEŻELI_dla_PRAWDY; instrukcja_wewnętrzna_JEŻELI_dla_FAŁSZU)

p> instrukcja_wewnętrzna_JEŻELI jest podobna do tej nadrzędnej

Dla jakiegoś przykładu

Rysunek i postać funkcji.

Funkcja zagnieżdżona jeżeli

Schemat w pliku pdf

Postać funkcji
= IF(A1 > C1; IF(A1 < D1; "Wartosc jest w zadanym zakresie"; "Wartosc przekracza zadany zakres"); IF(A1=C1; "Wartosc minimalna"; "Wartosc ponizej zadanego zakresu"))

Uwaga:
Powyżej skopiowałem regułę, której użyłem w anglojęzycznej wersji Excela aby poprawnie działała należy zamienić słowo IF na JEŻELI w każdym miejscu formuły.

przykłady dla różnych wartości A1

jezeli_przyklady.png