Archive for the ‘makra VBA’ Category

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 i VBA automatyczne uruchomienie makra przy otwarciu skoroszytu: sposób 1

Thursday, February 16th, 2012

Czasami przydaje się takie uruchomienie skoroszytu, które pociąga za sobą automatyczne uruchomienie makra. Przykładem może być skoroszyt, który pobiera dane ze strony www lub zdalnej bazy danych odpowiednio je przetwarza i wylicza na przykład ceny artykułów w e-sklepie.
Taki arkusz może być uruchomiony automatycznie o określonej godzinie przez oprogramowanie do planowania zadań wbudowane w Windows.

Aby makro uruchomiło się automatycznie należy je odpowiednio nazwać:
co jednak zrobić aby więcej makr uruchomiło się z automatu, nie jest to zapewne problem jeśli znasz VBA ale jeśli nie wiesz to powiem:

Umieszczasz listę makr wewnątrz makra startującego automatycznie w sposób jak niżej

Sub auto_open()
jakieś instrukcje
….
makro1
makro2
makro3

jakieś instrukcje
End Sub

Przy czym makro1, makro2, makro3 to makra zdefiniowane w innym miejscu w postaci

Sub makro1()
jakieś instrukcje…
End Sub

nazewnictwo makr jest oczywiście dowolne, ale konsekwencja odwoływania się do nich powinna być zachowana zgodnie z powyższym przykładem.
Na razie tyle, o następnym sposobie wkrótce.

Excel i VBA deklarowanie tablicy statycznej i dynamicznej

Sunday, February 5th, 2012

Tablice przydają się do tymczasowego przechowywania ciągów danych określonego typu i do szybszych obliczeń. Typ zmiennej przechowywanej w tablicy może być różnoraki w zależności od potrzeb, może to być ciąg znaków alfanumerycznych (String), byte, integer.

Podam jeden z wielu sposobów deklarowania przykładowej tablicy dwuwymiarowej (100 wierszy i 3 kolumny) przechowującej dane typu string

Niech nasza tablica nazywa się po prostu Tablica

Dim Tablica (100,3) as String

Jest to definicja tablicy statycznej kiedy wiemy ile wierszy i kolumn będzie zawierać.

Jeżeli nasza tablica ma za zadanie przechowywać dane, których ilości nie możemy na razie przewidzieć wówczas piszemy

Dim Tablica () as String

Ma to na celu zapobieganie zbędnemu przewymiarowaniu tablicy i zabezpiecza nas przed jej niedoszacowaniem
Weźmy na przykład określenie maksymalnej liczby wierszy i kolumn w danym arkuszu excela o czym pisałem w kilku poprzedzających wpisach a głównie we wpisie Excel 2010 - określenie maks. używanej ilości wierszy i kolumn za pomocą kodu VBA.

jeśli te zmienne (ilość wierszy i ilość kolumn) nazwiemy odpowiednio

wiersze, kolumny to aby dookreślić tablicę piszemy

Redim Tab(wiersze, kolumny)

i nasza tablica będzie ich mieć tyle ile trzeba :)

Zwykle jest jednak tak że pobieramy do tablicy tylko niektóre konkretne kolumny z arkusza ich liczbę (kolumn) jesteśmy w stanie przewidzieć.

Teraz zagadnienie innego typu: jak zapisać dane do odpowiedniej kolumny w tablicy ?

Niech za przykład posłuży nam taka tabelka

zapisdotablicy1.png

Pętla For … Next która będzie pomocna przy wczytywaniu będzie wyglądać mniej więcej tak:

For i =1 to 3
Tablica(i,1)=Cells(i,1)
Tablica(i,2)=Cells(i,2)
Tablica(i,3)=Cells(i,3)
Next

dla każdego i (w sensie wiersza) będą zapisane kolejno dane do trzech kolumn tabeli

Gdybyśmy chcieli dla sprawdzenia pobrać z tablicy element Tablica(3,3) to byłaby to wartość “30″ piszę w cudzysłowiu gdyż byłyby to znaki 3 i 0 dlatego że jest to tablica z elementami typu String

UWAGA na koniec:
Jeśli dopiero zaczynasz przygodę z makrami w VBA to powyższe przykłady zapisuj wewnątrz procedury Sub Twoja_nazwa ()
np.

Sub ZapisDoTablicy()

Dim Tablica (3,3) as String
Sheets(”Arkusz1″).select
‘ Wskazujemy z którego arkusza pobieramy dane do tablicy
for i =1 to 3
Tablica(i,1)=Cells(i,1)
Tablica(i,2)=Cells(i,2)
Tablica(i,3)=Cells(i,3)
Next
‘ sprawdzamy element Tablica(3,3)
MsgBox Tablica(3, 3)
End sub

Taką procedurę zapisz w module (nie w kodzie arkusza)

Na górze modułu napisz jeszcze taki wiersz

Option base 1

Daje to informację dla VBA że twoje tablice są liczone od 1-go elementu a nie domyślnie od zerowego. W stosunku do tablic jest to ułatwienie.

Jak to wygląda w VBA w anglojęzycznej wersji Excel 2010

zapisdotablicy2.png

Jak to działa

zapisdotablicy3.png

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

Pobierz fragment

Excel i VBA - formatowanie warunkowe inaczej

Saturday, December 31st, 2011

Kiedyś już pisałem jak można formatować komórki w excelu za pomocą funkcji formatowania warunkowego. W większości przypadków jest to zupełnie wystarczająca umiejętność.
Jest jednak sposób który zabezpieczy dodatkowo nasze arkusze przed nieumyślnym skasowaniem lub nadpisaniem reguł formatowania, tym bardziej że do edytora Visual Basic osoby z nim nieobeznane bardzo niechętnie zaglądają :) nawet gdybyśmy nie zabezpieczyli dostępu do odczytania samego makra.

W dalszym ciągu będziemy korzystać z przykładu omówionego poprzednio nieco go tylko poszerzając.

vba_fw__r_dane.png

Wiemy już jak określić ostatnią kolumnę i ostatni wiersz.

vba_fw__r_granice.png

Tym razem chcę wprowadzić coś nowszego polegającego na użyciu pętli for…next wewnątrz której będzie kilka instrukcji warunkowych if i w zależności od wartości w komórce wykona się instrukcja zmieniająca kolor tła komórki

Cells(wiersz, kolumna).Interior.ColorIndex = indeks_koloru

indeks_koloru - to wartość powinna być w przedziale od 1 do 56

Ponieważ nie wiemy jakie kolory są dostępne pod jakim indeksem to sobie trochę pomożemy generując tabelę kolorów. Zrobimy to w kolumnie ósmej. Po wklejeniu poniższego kodu do okna Visual Basic. Czyli w Excelu za pomocą Lewy Alt + F11 uruchamiamy VBA wybieramy Arkusz1 i wklejamy ten kod

Sub kolory ()
For w = 1 To 56
Cells(w, 7).Value = w
Cells(w, 8).Interior.ColorIndex = w
Next
End Sub

W liniach wewnątrz pętli for znajdują się instrukcje, które kolejno:
1 wstawiają wartość w kolumnę 7
2 zmieniają kolor tła komórki w koluminie 8

Wszystko po to by można było zidentyfikować jaki indeks odpowiada danemu kolorowi.

Będzie to wyglądać mniej więcej tak

kolor-index.png

Kiedy już wiemy jaki kod ma dany kolor możemy przejść do sedna

Wpiszmy następujący przykładowy kod.


Sub kolory()

For w = 1 To 16
If Cells(w, 2).Value < 5 Then
Cells(w, 1).Interior.ColorIndex = 33
End If
If Cells(w, 2).Value > 5 And Cells(w, 2).Value < 10 Then
Cells(w, 1).Interior.ColorIndex = 26
End If
If Cells(w, 2).Value > 10 Then
Cells(w, 1).Interior.ColorIndex = 3
End If
If Cells(w, 2).Value = 5 Then
Cells(w, 1).Interior.ColorIndex = 3
End If
If Cells(w, 2).Value = 10 Then
Cells(w, 1).Interior.ColorIndex = 44
End If
Next

Po uruchomieniu tego makra dostaniemy coś takiego

makro_kolory.png

Aby pogrubić tekst w danej komórce stosujemy polecenie


Cells(w, 1).Font.Bold = True

To na razie tyle :)

Nieco więcej przeczytasz klikając w ten link Excel i VBA - formatowanie warunkowe