Archive for the ‘howto’ Category

Odwoływanie się do komórek w innych arkuszach

Friday, June 22nd, 2012

Pierwotnie artykuł miał mieć inny tytuł, ale chciałem wrócić jeszcze do tego co ostatnio napisałem o nazywaniu komórek i zakresów.

Otóż jeśli chcemy przykładowo w komórce C9 arkusza Arkusz2 odnieść się do komórki AX11 w arkuszu Arkusz1 to musimy napisać w komórce C9 coś takiego:

=Arkusz1!AX11

Z kolei jeśli zastosujemy nazwę dla komórki to piszemy po prostu tę nazwę. Przykładowo komórkę AX11 w arkuszu Arkusz1 nazwiemy PRĘDKOŚĆ (wielkość liter nie ma znaczenia) to w komórce C9 arkusza Arkusz2 piszemy

=PRĘDKOŚĆ

i wyświetla nam się wartość tej komórki

Ponieważ temat odwoływania do komórek jest dość szerokim tematem to szkoda się ograniczać tyllko do tego przypadku.
Niegdyś omawiałem na tej stronie temat tworzenia listy rozwijanej na podstawie wartości w zaznaczonej grupie komórek.

sprpopr1.png

Formalnie w Excelu nosi to nazwę sprawdzanie poprawności

Jeśli chodzi o najnowszą wersję Excela (2010) to nie ma żadnych ograniczeń aby dane do listy pochodziły z innego arkusza. Po prostu przy wyborze źródła danych zaznaczamy inny arkusz i na nim zakres komórek. W wersji 2003 i starszych ograniczenie jest takie że dane do listy można pobrać wyłącznie z aktywnego w danym momencie arkusza.

chyba że …
No właśnie

…zastosujemy nazwę dla pożądanego zakresu komórek i do tej nazwy w okienku sprawdzania poprawności się odniesiemy pisząc:

=MOJA_NAZWA

oczywiście MOJA_NAZWA zastąp tą właściwą

Abyś to zrozumiał i mógł zastosować wystarczy że przeglądniesz poprzedni post oraz ten o sprawdzaniu poprawności

Aby sobie utrwalić zrób to najlepiej teraz

I jeszcze ostatni temat

To że nazwę przypisujemy do komórki pewnie Cię już nie dziwi, ale czy wiedziałeś(aś) że tą samą metodą możemy nazwę przypisać nie do komórki tylko do wartości.

nazwadostalej1.png

Można powiedzieć że to takie definiowanie stałej, która może mieć zasięg dla całego skoroszytu. Przykładem może być jakiś współczynnik do obliczeń matematycznych (dzięki czemu reguła ze wzorem staje się bardziej czytelna) lub stopa procentowa, może to być nawet stała tekstowa np nazwa ZUS przypisane do stałej “Zakład Ubezpieczeń Społecznych”
Jeśli to zdefiniujesz zobaczysz że w miejsce napisanego słowa ZUS podstawi się automatycznie Zakład Ubezpieczeń Społecznych

Spróbuj i przekonaj się :)

Word 2010 - jak włączyć lub wyłączyć ograniczniki tekstu

Wednesday, June 13th, 2012

Word 2010 podobnie jak w poprzednich wersjach ma możliwość wizualizacji ograniczników tekstu i znaczników przycięcia. W poprzednich wersjach były one włączane za pomocą jednej tylko opcji w Narzędziach obecnie włącza się je rozdzielnie.

w2010_ograniczniki1.png

Aby włączyć ograniczniki wchodzimy w menu Plik następnie Opcje

w2010_ograniczniki2.png

Klikamy zakładkę zaawansowane

i zaznaczamy pole wyboru “Pokaż ograniczniki tekstu”

w2010_ograniczniki3.png

bezpośrednio pod tym polem wyboru znajdziemy pole odpowiadające znacznikom przycięcia (z ang Crop marks)

Jak utworzyć własną formułę w Excelu

Tuesday, May 1st, 2012

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

ownformula2.png

ownformula3.png

ownformula4.png

ownformula5.png

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

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 2010 - określenie maks. używanej ilości wierszy i kolumn za pomocą kodu VBA

Monday, December 19th, 2011

Chciałem omówić małą ale przydatną rzecz, która mi ostatnio bardzo pomogła w pracy a mianowicie określenie za pomocą komend VBA ilości użytych wierszy i kolumn w pliku. Jest to o tyle ułatwieniem że na tej podstawie mogę zadać granicę dla pętli for (lub innej) w celu dalszej analizy danych.

Aby sprawdzić jak to działa warto otworzyć jakiś plik z danymi lub samemu je przygotować :)

dane do sprawdzenia działania

Warto zwrócić uwagę przy otwieraniu pliku Excela 2010 z makrami (jeżeli mamy już plik z napisanymi makrami) że trzeba niestety kliknąć przycisk “Enable content” (zezwalaj na treść) bez tego nasze makra nie będą działać.

makro_finalrowand-finalcol-1a.png

Druga sprawa, od której należało może zacząć to ta że plik excela 2010 “utrudni” nam bezmyślne zapisanie go jako zwykłego pliku excela i zasugeruje by zapisać go w postaci pliku z zezwoleniem dla makr

macro_save-as-2.png

i ten właśnie plik będzie wymagał wciśnięcia przycisku enable content aby je uruchamiać. oznaczony jest on specjalną ikoną

ikonka

Teraz do rzeczy
1 mamy już dane
2 Po otwarciu pliku Excela naciskamy Lewy Alt +F11 i otwiera nam się okno VBA (Visual Basic for Applications) i zaznaczamy klikając dwukrotnie arkusz z naszymi danymi (w moim przypadku Arkusz1)

makro_finalrowand-finalcol-2.png

3. Wpisujemy kod jak niżej


Sub granice()

ostatniwiersz = ActiveSheet.UsedRange.Rows.Count
ostatniakolumna = ActiveSheet.UsedRange.Columns.Count
MsgBox “W arkuszu jest ” & ostatniwiersz & ” wierszy”
MsgBox “W arkuszu jest ” & ostatniakolumna & ” kolumn”
End Sub

Funkcje Msgbox służą mi jedynie do upewnienia się że wszystko jest ok i po ich użyciu mogę na przedzie każdej takiej lini dodać pojedynczy apostrof aby interpretator VB widział te linie jako komentarz i nic z nimi nie robił.

4. Uruchamiamy makro używając F5 lub menu

runmacro.png

Możemy widzieć jeszcze coś takiego

runmacro2.png

5. Klikamy przycisk Run i oglądamy wyniki :)

macro_result1.png

macro_result2.png

To na razie tyle. Makro można zmodyfikować do swoich potrzeb. Dodam jeszcze że testowałem je w wersjach Excel 2000 i Excel 2003 a skoro w niezmienionej formie treść makra działa również w tej wersji (2010) to śmiało można powiedzieć że nie powinno być przeszkód aby działało również w wersji 2007