Skrypt całkowicie blokuje interfejs aplikacji. Co zrobić?

Jeżeli napisałeś skrypt w VBA przetwarzający duże ilości danych i zauważyłeś, że w trakcie wykonywania blokuje główną aplikację, powinieneś wprowadzić do swojego kodu instrukcje przerywające jego wykonywanie. Poszukaj pętli która wykonuje się najdłużej i dodaj do niej instrukcję DoEvents. Przerywa ona wykonywanie programu i pozwala na wykonanie innych zadań oczekujących w kolejce. Potem wykonywana jest dalsza część skryptu.
Oczywiście, instrukcja DoEvents nie musi być wykonywana w każdej pętli. Wygodnie jest użyć następującego kodu:

If i Mod 200 = 0 Then
DoEvents
End If

Spowoduje on wykonanie instrukcji DoEvents co 200 pętli iteratora i.
Najczęściej użycia tej konstrukcji wymagają skrypty wykonujące się długo ze względu na dużą ilość tablic do przeszukania oraz skrypty generujące duże ilości nowych obiektów. Przykładem może być generowanie wielu stron dokumentacji w Visio - taki proces może wymagać nawet 8 godzin pracy skryptu przy generowaniu dokumentacji wielkości 2000 stron :)

Jak w Excel skopiować komórkę wraz z formatowaniem?

Najbardziej oczywistą metodą kopiowania zawartości komórek arkusza kalkulacyjnego jest przepisanie wartości z jednej do drugiej komórki:

Workbooks("Dokument").Worksheets("Arkusz").Cells(1, 1).Value = _
Workbooks("Dokument").Worksheets("Arkusz").Cells(1, 2).Value

Powyższa metoda nie kopiuje formatowania komórek a jedynie przenosi wartości.
Użytkownicy pracując z arkuszem Excel często wprowadzają poprawki uzupełniając tekst w komórce, wykreślając poszczególne słowa, i wyróżniając inne. Często naniesione formatowanie jest równie istotne jak sama treść komórki.
Aby skopiować tak sformatowane dane wykorzystajmy poniższy kod:

Workbooks("Dokument").Worksheets("Arkusz").Range("A1").Copy
Workbooks("Dokument").Worksheets("Arkusz").Range("A2").PasteSpecial _
Paste:= xlPasteAllExceptBorders

Za pomocą wyrażenia Paste:= xlPasteAllExceptBorders określamy sposób wklejenia skopiowanych danych - w naszym przypadku wklejone zostaną dane z komórki wraz z formatowaniem ale bez obramowań. Możemy korzystać z następujących form parametru Paste:

xlPasteAll	Wklejone zostaną wszystkie dane
xlPasteAllExceptBorders	Wklejone zostaną dane bez obramowań komórek
xlPasteAllUsingSourceTheme	Wszystko zostanie wklejone wykorzystując źródłowy schemat kolorów
xlPasteColumnWidths	Wklejone zostaną tylko szerokości kolumn
xlPasteComments	Wklejone zostaną tylko komentarze
xlPasteFormats	Wkleone zostanie wyłącznie formatowanie
xlPasteFormulas	Wklejone zostaną formuły
xlPasteFormulasAndNumberFormats	Wklejone zostaną formuły oraz format liczb
xlPasteValidation	Wklejone zostaną walidacje
xlPasteValues	Wklejone zostaną wyłącznie wartości
xlPasteValuesAndNumberFormats	Wklejone zostaną wartości oraz format liczb

I jeszcze jedno: powyższa metoda jest znacznie wolniejsza niż kopiowanie wyłącznie samych wartości komórek!

Jak policzyć arkusze w skoroszycie Excel?

Tworząc skrypty dla Excel często pracujemy na wielu różnych arkuszach. Arkusze mogą być dodawane ręcznie przez użytkownika jak również przez kod VBA. Często więc nie wiemy ile jest arkuszy zebranych w skoroszycie ani jakie są ich nazwy.
Proponuję przeanalizowanie poniższego kodu, który powie nam ile jest arkuszy w skoroszycie a dodatkowo policzy te, których nazwa rozpoczyna się od "A":

Public Sub Policz_Arkusze()

Dim I, ileArkuszy, ileArkuszyOdA As Integer

' zmienna pokazuje ilość arkuszy których nazwa zaczyna się od A
ileArkuszyOdA = 0
' Sheets.Count wskazuje ilość arkuszy w skoroszycie
ileArkuszy = Sheets.Count
For I = 1 To ileArkuszy
'Dla każdego arkusza sprawdź czy jego nazwa
'zaczyna się od "A"
If InStr(Sheets(I).Name, "A") = 1 Then
ileArkuszyOdA = ileArkuszyOdA + 1
End If
Next

MsgBox ("Ilość arkuszy w skoroszycie: " & ileArkuszy _
& vbCrLf & "Nazwa " & ileArkuszyOdA & " arkuszy zaczyna się od A")
End Sub

Widzimy, że zmienna Sheets.Count przechowuje ilość arkuszy w skoroszycie.
W pętli For - Next możemy wykonać kod dla każdego z arkuszy. W naszym przypadku odczytujemy nazwy arkuszy ze zmiennej Sheets(I).Name, sprawdzamy czy rozpoczyna się od A i zliczamy.

Jak za pomocą VBA formatować komórki w Excel?

Formatowanie arkusza Excel za pomocą kodu VBA jest niezbędne jeżeli chcemy:

• ujednolicić wygląd generowanych dokumentów
• odciążyć pracowników od pracochłonnych czynności związanych z wyglądem dokumentów

Formatowanie tekstu
Podstawowe właściwości tekstu wpisanego w komórkę arkusza, przechowywane są w obiekcie Cells.Font.
Aby zmienić rozmiar tekstu wpisujemy wielkość w punktach:
Cells(1, 1).Font.Size = 15
Pogrubienie tekstu:
Cells(1, 1).Font.Bold = True
Pochylenie tekstu:
Cells(1, 1).Font.Italic = True
Podkreślenie tekstu:
Cells(1, 1).Font.Underline = True
Przekreślenie tekstu:
Cells(1, 1).Font.Strikethrough = True
Indeks dolny:
Cells(1, 2).Font.Subscript = True
Indeks górny:
Cells(1, 3).Font.Superscript = True
Ponadto możliwe jest określenie kierunku tekstu w komórce:
Cells(1, 3).Orientation = 90
Powyższy kod spowoduje, że tekst w komórce C1 wpisywany będzie w kierunku od dołu do góry. Wartość 0 oznacza standardowy tekst poziomy.
W celu wyrównania tekstu w poziomie użyjemy składni:
Cells(1, 3).HorizontalAlignment = xlCenter
Parametry w formatowaniu poziomym tekstu:

• xlCenter - tekst wyśrodkowany
• xlDistributed - tekst zawsze zaczyna się przy lewej a kończy przy prawej krawędzi komórki
• xlJustify - tekst wyjustowany
• xlLeft - tekst z lewej
• xlRight - tekst z prawej

Wyrównanie tekstu w pionie:
Cells(1, 3).VerticalAlignment = xlCenter
i parametry wyrównania pionowego:

• xlBottom - tekst na dole komórki
• xlCenter - tekst w środku komórki
• xlDistributed - tekst zaczyna się i kończy przy krawędziach komórki
• xlJustify - tekst wyjustowany w pionie
• xlTop - tekst na górze komórki

Czasami chcemy w jednej komórce użyć tekst o różnym formacie np. chcemy wpisać symbol H₂O:
Cells(5, 1).Value = "H2O"
Cells(5, 1).Characters(2, 1).Font.Subscript = True
Powyższy kod wpisze do komórki A5 symbol H₂O. W pierwszej linijce wpisany został tekst a w drugiej za pomocą składni Characters.Font możemy sformatować tylko wybrane znaki. We właściwości Characters(2, 1) 2 oznacza numer znaku od którego rozpoczynamy formatowanie natomiast 1 to ilość znaków do formatowania.
Przy okazji, aby sprawdzić ilość znaków w komórce możemy użyć polecenia:
n = Cells(5, 1).Characters.Count
Formatując komórki w arkuszu Excel nie możemy zapominać o sposobie wyświetlania wartości przez program. Aby ustawić specyficzny sposób wyświetlania wykorzystamy właściwość NumberFormat:
Cells(5, 1).NumberFormat = "0.00"
Kod spowoduje, że wartości w komórce A5 wyświetlane będą z precyzją 2 miejsc po przecinku.
Możemy użyć następujących sposobów wyświetlania wartości:

• NumberFormat = "General" - formatowanie ogólne
• NumberFormat = "0" - Zapis liczbowy - odpowiednia ilość miejsc po przecinku
• NumberFormat = "0.0"
• NumberFormat = "0.00"
• NumberFormat = "0.000"
• NumberFormat = "0.0000"
• NumberFormat = "#,##0.00 $" - Zapis walutowy
• NumberFormat = "m/d/yyyy" - data krótka
• NumberFormat = "[$-F800]dddd, mmmm dd, yyyy" - data długa
• NumberFormat = "[$-F400]h:mm:ss AM/PM" - Godzina
• NumberFormat = "0.00%" - zapis procentowy
• NumberFormat = "# ?/?" - zapis liczby w postaci ułamka
• NumberFormat = "0.00E+00" - format naukowy liczb - zapis wykładniczy
• NumberFormat = "@" - zapis tekstowy

Kolorowanie tekstu oraz komórek
Aby zmienić kolor tekstu komórki używamy składni:
Cells(1, 1).Font.Color = rgbRed
gdzie rgbRed oznacza stałą koloru czerwonego.
Jeżeli nie możemy znaleźć specyficznego koloru możemy zapisać go za pomocą składni RGB:
Cells(1, 1).Font.Color =RGB(255,0,0)
W przykładzie zapisany został kod koloru czerwonego. Szczegółowe informacje na temat kolorowania tekstu znajdziemy w artykule Jak w Excel zmienić kolor tekstu komórki?
Zmiana koloru wypełnienia komórki:
Cells(1, 1).Interior.Color = rgbYellow
Kod spowoduje pokolorowanie komórki na kolor żółty.
Obramowania komórek
Aby rysować obramowania komórek użyjmy kodu:
With Cells(2, 1).Borders
.LineStyle = xlContinuous
.ColorIndex = xlColorIndexAutomatic
.Weight = 2
End With
Kod spowoduje obramowanie komórki A2.
Parametr LineStyle określa styl linii obramowania i może przyjąć wartość:

• xlAutomatic - styl automatyczny
• xlContinuous - linia ciągła
• xlDash - linia przerywana
• xlDashDot - kreska - kropka
• xlDashDotDot - kreska i dwie kropki
• xlDot - linia z kropek
• xlDouble - podwójna linia
• xlLineStyleNone - bez linii
• xlSlantDashDot
• xlGray25 - kolor siwy 25%
• xlGray50 - kolor siwy 50%
• xlGray75 - kolor siwy 75%

Parametr ColorIndex określa dodatkowe właściwości ramek:

• xlColorIndexNone - brak obramowania
• xlColorIndexAutomatic - kolor automatyczny

Parametr Weight określa grubość linii. Może przyjmować wartości liczbowe z przedziału od 1 do 4, i tak:

• 1 - linia kropkowana
• 2 - linia cienka - lub xlThin
• 3 - linia gruba - lub xlThick
• 4 - linia bardzo gruba

Ponadto możemy określić kolor obramowania:
.Color = rgbRed
Kod zmieni kolor ramki na czerwony. Ustalając kolor obramowania nie wykorzystujemy parametru ColorIndex.
Ramki można rysować nad/pod/z prawej/z lewej strony komórki. I tak kod:
Cells(2, 3).Borders(xlEdgeRight).LineStyle = xlContinuous
zmieni tylko prawe obramowanie komórki C2.
Możemy formatować krawędzie komórek za pomocą parametrów:

• xlEdgeTop - górna krawędź komórki
• xlEdgeBottom - dolna krawędź komórki
• xlEdgeLeft - lewa krawędź komórki
• xlEdgeRight - prawa krawędź komórki
• xlDiagonalUp - komórka przekreślona w górę
• xlDiagonalDown - komórka przekreślona w dół

Dodatkowe informacje
Aby formatować nie jedną komórkę ale cały zakres komórek musimy użyć obiekt Range:
Range("E2:G4").Borders.LineStyle = xlContinuous
Kod narysuje tabelkę (ramki) w zakresie od E2 do G4
Zakres komórek można zapisać również tak:
Range(Cells(2, 1), Cells(4, 3)).Interior.Color = rgbYellow
Komórka A2 (2,1) jest początkiem obszaru natomiast C4 (4,3) końcem.

Jak w Excel zmienić kolor tekstu komórki?

Aby zmienić kolor czcionki w Excel odwołujemy się do zmiennej Cells.Font.Color i przypisujemy jej wartość koloru. Najłatwiej posłużyć się zdefiniowanymi stałymi koloru np.: rgbBlue, rgbRed itp:
Cells(12, 1).Font.Color = rgbBlue
Powyższy kod zmieni kolor czcionki w komórce A12 na niebieski.

Wszystkie stałe koloru rozpoczynają się od przedrostka rgb.. Poniżej zamieszczam pełną listę stałych koloru:


Oczywiście, możemy też sami zdefiniować dowolny kolor tekstu za pomocą funkcji RGB(). W jej parametrach podajemy nasycenie poszczególnych składników kolorów: czerwonego (R), zielonego (G) i niebieskiego (B). Podawane są wartości nasycenia koloru w zakresie 0-255 gdzie 255 oznacza kolor pełny.
Dla przykładu:
RGB(0,0,0) - kolor czarny
RGB(255,0,0) - kolor czerwony
RGB(0,255,0) - kolor zielony
RGB(0,0,255) - kolor niebieski
RGB(255,255,255) - kolor biały
Wypróbujmy poniższy kod:
Public Sub Zmien_kolory()
Dim i, j, R, G, B As Integer

For i = 1 To 10
For j = 1 To 10
R = 255 - (i * 25)
G = i * 25
B = j * 25
Cells(i, j).Value = "ABC"
Cells(i, j).Font.Color = RGB(R, G, B)
Next
Next
End Sub
Wynikiem będzie próbka możliwości Excel w zakresie kolorowania tekstu:

Jak za pomocą VBA czytać i zapisywać wartości komórek w Excel?

Wpisywanie danych

Za pomocą języka Visual Basic for Applications możemy wpisywać oraz czytać dane z dowolnej komórki arkusza Excel.

Możliwe jest wprowadzenie dowolnego tekstu, daty czy wartości liczbowej. W tym celu stosujemy składnię:

Cells(2, 3).Value = "ABC"

Powyższy kod wpisze tekst ABC w komórce C2 (wiersz 2, kolumna 3) w aktywnym arkuszu. Jeżeli pracujemy na kilku arkuszach, powinniśmy podać nazwę arkusza do którego chcemy wpisać dane:

Worksheets("Arkusz1").Cells(2, 3).Value = "ABC"

Tekst ABC zostanie wpisany w arkuszu o nazwie Arkusz1. Oczywiście możemy pracować na kilku otwartych skoroszytach - wtedy odwołujemy się do ściśle określonego skoroszytu:

Workbooks("test.xlsm").Worksheets("Arkusz1").Cells(2, 3).Value = "ABC"

W powyższym przykładzie test.xlsm jest nazwą skoroszytu, natomiast Arkusz1 to nazwa arkusza z danymi.

Jasne jest, że ostatni przykład jest najbardziej uniwersalny i niezawodny ponieważ użytkownik arkusza może mieć kilka otwartych skoroszytów a co gorsza może próbować przełączyć się między nimi w trakcie pracy skryptu. Ze względu na przejrzystość artykułów przykłady podaję w formie skróconej, jednak nie zapominajmy o niezawodności kodu!

Wpisywanie formuł

VBA pozwala nam też wprowadzać formuły do arkusza kalkulacyjnego za pomocą składni:

Cells(3, 1).Formula = "=A1+A2"

Powyższy kod przypisze komórce A3 formułę A3=A1+A2. Nie zapominajmy o znaku równości w formule!

Możliwości języka VBA

Możliwości automatyzacji pakietu MS Office z wykorzystaniem VBA są olbrzymie, ale co to jest język VBA? Według Wikipedii:

Visual Basic for Applications (VBA) to oparty na Visual Basicu (VB) język programowania zaimplementowany w aplikacjach pakietu Microsoft Office oraz kilku innych, jak na przykład AutoCAD i WordPerfect. Ta uproszczona wersja Visual Basica służy przede wszystkim do automatyzacji pracy z dokumentami, na przykład poprzez makropolecenia.

Chodzi o to, że aplikacje MS Office posiadają wbudowany język programowania, o czym wielu z nas nie pamięta a wielu nawet nie wie.

Jakie są możliwości tego języka? Dokładnie takie same jak każdego użytkownika aplikacji. Za pomocą skryptów VBA możemy wprowadzać tekst, wartości i formuły do obliczeń, możemy formatować dokumenty, tworzyć wykresy i tabele, wyszukiwać dane a nawet rysować schematy. Wszystkie funkcje dostępne dla użytkownika aplikacji mogą być wywołane przez skrypt VBA.

Kiedy więc stosować skrypty i makra VBA? Przede wszystkim, gdy prace wykonywane na dokumentach są powtarzalne. Jeżeli dokument który tworzymy jest kolejnym dokumentem tego samego typu, o identycznej formie a różni się tylko danymi.

Aby można było zautomatyzować dokument musi być możliwość opisania wszystkich czynności tak, aby powstał precyzyjny algorytm tworzenia dokumentu - czyli schemat wszystkich kroków niezbędnych do jego wygenerowania.

Skrypty VBA mogą być nam przydatne również przy dokumentach tworzonych tylko jeden raz, do ściśle określonego celu, które wymagają przeanalizowania dużych ilości danych - co mogło by być niezwykle czasochłonne przy ręcznej analizie.

Decydując się na zautomatyzowanie określonego dokumentu musimy oszacować czy zysk czasu związany z szybszym tworzeniem dokumentów i usprawnieniem pracy pracowników przewyższa koszty związane z napisaniem aplikacji. Musimy pamiętać o tym, że aplikacja pisana jest jednorazowo, natomiast możemy z niej korzystać ciągle na wielu różnych stanowiskach.

Podsumowując, zalety stosowania automatyzacji dokumentów:

•     zwiększenie funkcjonalności aplikacji MS Office
•     ujednolicenie generowanych dokumentów
•     skrócenie czasu przygotowania dokumentów
•     możliwość przetwarzania danych w ilościach niemożliwych do przetworzenia ręcznie
•     odciążenie pracowników od żmudnych i jednostajnych zadań
•     skrypty mogą służyć do wstępnego przygotowania dokumentów i danych do dalszej analizy
•     niski koszt gdyż nie trzeba dokupować dodatkowych aplikacji

Kiedy stosować skrypty i makra VBA:

•     gdy pracujemy z dokumentami powtarzalnymi
•     gdy potrafimy opisać tworzenie dokumentu precyzyjnym algorytmem
•     gdy mamy duże ilości danych do przetworzenia
•     chcemy wymieniać dane pomiędzy dokumentami MS Office i innymi aplikacjami

Powitanie

Witam w Skrypcikach,
Celem bloga jest zebranie luźnych informacji i fragmentów kodu VBA przydatnych przy przygotowaniu skryptów dla Excel.
Początkowe wpisy będą zawierały materiał przeniesiony ze starej strony www.skryptyexcel.pl.