Sumowanie liczb (rzeczywistych) w dokumencie Word (2 z 2)

O ile sumowanie liczb całkowitych było proste (co zaprezentowałem w poprzednim poście), o tyle suma liczb rzeczywistych wymaga dodatkowych zabiegów.

Osoby znające zagadnienie związane z wykorzystaniem znaków specjalnych w wyszukiwaniu (wildcards) lub techniki Regular Expresion z pewnością spodziewały się, że wystarczy minimalnie przerobić wzorzec wyszukiwania aby uzyskać oczekiwany efekt. Otóż nic bardziej mylnego, następujący zapis:

view sourceprint?

1	.Text = "[0-9]{1;}[0-9,]{0;}"

niestety nie sprawdza się w aplikacji Word. Po prostu nie jest dopuszczalne aby ilość znaków określić na 'zero lub więcej' co kryje się w zapisie {0;}.

Owszem, jesteśmy blisko rozwiązania tworząc następujący wzorzec:

view sourceprint?

1	.Text = "[0-9,]{1;}"

tyle tylko, że powyższy schemat wyszuka również wszystkie przecinki w dokumencie i spróbuje je zsumować co w efekcie wygeneruje błąd. Pozostaniemy jednak przy tym rozwiązaniu i aby było skuteczne dodamy odpowiedni mechanizm obsługi błędów. Całość rozwiązania prezentuje poniższy kod.

view sourceprint?

01	Sub SearchNumbers_Doubles()

02

03	Dim Counter As Long

04	Dim Total As Double

05

06	With Selection.Find

07	.Text = "[0-9,]{1;}"

08	.Replacement.Text = ""

09	.Forward = True

10	.Wrap = wdFindStop

11	.Format = False

12	.MatchCase = False

13	.MatchWholeWord = False

14	.MatchAllWordForms = False

15	.MatchSoundsLike = False

16	.MatchWildcards = True

17

End With

18

19	Dim dblTmp As Double

20	On Error Resume Next

21	Do While Selection.Find.Execute

22

23	dblTmp = CDbl(Selection)

24	If Err.Number = 0 Then

25	Counter = Counter + 1

26	Total = Total + dblTmb

27

Else

28

Err.Clear

29

End If

30

31	Loop    MsgBox "W dokumencie znajduje się  " & Counter & _

32	" liczb o łącznej wartości równej " & Total

33

34

35

End Sub

Sumowanie liczb (całkowitych) w dokumencie Word (1 z 2)

Tym razem temat wywołany na jednym z forów poświęconych programowaniu w VBA- w jaki sposób zsumować wszystkie wartości liczbowe w dokumencie Word, w określonym zaznaczeniu.
W sytuacji gdyby rzecz dotyczyła sumy w tabeli w dokumencie moglibyśmy sięgnąć po odpowiednie pola sumujące. Tym razem chodzi jednak o dowolny zaznaczony obszar.

Rozwiązanie podzielę na dwie części- sumowanie liczb całkowitych- i ten wariant zostanie zaprezentowany w niniejszym poście- oraz sumowanie liczb rzeczywistych, co jak się okaże jest relatywnie prostym zadaniem.

Aby zsumować liczby rzeczywiste najlepszym rozwiązaniem jest zastosować obiekt Find dla odpowiednio skonstruowanego klucza wyszukiwania z wykorzystaniem znaków specjalnych (wildcards). Poniższy kod realizuje to zadanie wyświetlając na końcu komunikat z podsumowaniem.

view sourceprint?

01	Sub SearchSumIntegers()

02

03	Dim Counter As Long

04	Dim Total As Long

05

06	With Selection.Find

07	.Text = "[0-9]{1;}"

08	.Replacement.Text = ""

09	.Forward = True

10	.Wrap = wdFindStop

11	.Format = False

12	.MatchCase = False

13	.MatchWholeWord = False

14	.MatchAllWordForms = False

15	.MatchSoundsLike = False

16	.MatchWildcards = True

17

End With

18

19	Do While Selection.Find.Execute

20

21	Counter = Counter + 1

22	Total = Total + Selection

23

24

Loop

25	MsgBox "W dokumencie znajduje się  " & Counter & _

26	" liczb o łącznej wartości równej " & Total

27

28

End Sub

Powyższe rozwiązanie w wariancie sumującym liczby rzeczywiste opublikowany zostanie już jutro.

Konwersja tekstu na mowę (2 z 2)

Kontynuując poprzedni wpis dot. konwersji tekstu na mowę zaprezentuję sposób wykonania tej operacji z innej niż Excel aplikacji z pakietu Office. Istnieją dwa sposoby rozwiązania tego problemu.

Wariant A. Odczyt paragrafu z aplikacji MS Word z wykorzystaniem Excela.
W tym wariancie wystarczy z poziomu aplikacji MS Word utworzyć referencję do aplikacji Excel (dla prostoty działania z wykorzystaniem wiązania późnego) a następnie wykorzystać obiekt Speech dostępny w Excelu. Poniższy kod prezentuje takie właśnie działanie. Co ważne, nie musimy wyświetlać aplikacji Excel przez co osoba postronna nie zorientuje się, że odczyt następuje z poziomu innej niż Word aplikacji.

view sourceprint?

01	Sub Czytaj_Text_Z_Pomocą_Excela()

02

03	Dim xlApp As Object

04	Set xlApp = CreateObject("Excel.Application")

05

06	xlApp.speech.Speak ActiveDocument.Paragraphs(1).Range.Text

07

08	xlApp.Quit

09	Set xlApp = Nothing

10

End Sub

Wariant B. Wykorzystanie zewnętrznej biblioteki do odczytu tekstu
W tym wariancie wykorzystamy bibliotekę o nazwie Microsoft Speech Object Library, do której należy ustawić odpowiednią referencję w Menu >> Tools >>References... Biblioteka ta daje nam dostęp do rozszerzonej wersji generatora mowy. Dzięki szeregu właściwości i metod możemy wpłynąć na jakość dźwięku czy też wybrać odpowiedni język i głos (o ile biblioteka posiada odpowiednie dodatkowe zestawy głosów i języków).
Prosty przykład wykorzystania w/w biblioteki w celu odczytania pierwszego paragrafu z dokumentu MS Word przedstawia poniższa procedura:

view sourceprint?

1	Sub Speech_FromFile_Example()

2

3	Dim testVoice As SpVoice

4	Set testVoice = New SpVoice

5

6	testVoice.Speak ActiveDocument.Paragraphs(1).Range.Text

7

8

End Sub

Po dodatkowe informacje odsyłam do strony MSDN poświęconej tej bibliotece.

Konwersja tekstu na mowę (1 z 2)

W dwóch najbliższych wpisach chciałbym krótko nawiązać do dwóch technik umożliwiających konwersję tekstu na mowę, a więc możliwości odczytania tekstu przez generatora mowy dostępnego w środowisku Office.

Zacznijmy od wersji prostej- generatora mowy dołączonego do pakietu MS Office, a konkretnie aplikacji Excel. I tu warto od razu podkreślić- poniższy przykład działa wyłącznie w aplikacji Excel i nie jest dostępny dla innych aplikacji pakietu.

Proponowane rozwiązanie opiera się o wykorzystanie obiektu Speech:

view sourceprint?

1	Application.Speech

oraz jego właściwości Speak:

view sourceprint?

1	Application.Speech.Speak (Text, SpeakAsync, SpeakXML, Purge)

gdzie wśród parametrów szczególnie zainteresują nas:
Text który wskazuje tekst do odczytania oraz  
ApeakAsync  informujący kompilatora o tym, czy wstrzymać dalsze wykonywanie procedury do zakończenia czytania (domyślne działanie, wartość False parametru) czy też kontynuować procedurę bez oczekiwania na zakończenie odczytu (wartość True parametru). Przeanalizujmy poniższy przykład:

view sourceprint?

01	Sub Konwersja_Tekstu_Na_Mowę()

02

03	Dim txtPL As String

04	Dim txtEN As String

05

06	txtPL = "To jest teks do odczytania przez generator mowy."

07	txtEN = "This is text to read by speach generator."

08

09	Application.Speech.Speak txtPL

10	Application.Speech.Speak txtEN

11

12

End Sub

Po wykonaniu powyższej procedury przekonamy się, że tylko jeden z tekstów zostanie odczytany prawidłowo. Zależy to od wersji pakietu Office i częściowo od jego wersji językowej. W aplikacji Excel 2010 PL prawidłowo zostanie odczytany tekst angielski i niepoprawnie tekst polski. W wersji aplikacji Excel 2013 PL efekt będzie odwrotny i uzyskamy prawidłową wymogę dla tekstu w języku polskim.

Interakcja pomiędzy aplikacjami MS Office (4 z ...wielu)

Tym razem kilka słów na temat integracji aplikacji Excel z aplikacją PowerPoint. Zaprezentuję prostą operację polegającą na wkopiowaniu do PowerPointa obiektów pochodzących z arkusza Excel:

• tabeli danych
• wykresu

oraz ustawimy odpowiednie pozycje  i wymiary dla powyższych elementów slajdu.

Warto zwrócić szczególną uwagę w poniższym kodzie na edycję z wykorzystaniem zmiennych obiektowych. Zabieg ten jest przeprowadzony celowo w celu poprawienia czytelności kodu oraz wyraźnego wydzielenia, które operacje-obiekty dotyczą Excela, a które PowerPoint'a. Jedyną wada tego rodzaju zapisu to wydłużenie całej procedury o szereg linii związanych z deklaracją zmiennych i ich ustawieniem (linie Dim i Set).

Cała poniższa procedura działa dla prostego pliku Excela, gdzie w arkuszu Arkusz1 (nazwa kodowa arkusza) znajduje się jeden wykres oraz zakres danych wykresu znajduje się w komórkach A1:C4. Pozostałe ważne informacje i wskazówki znaleźć można w poniższym kodzie w postaci komentarzy.

view sourceprint?

01	Sub Interakcja_z_PowerPoint()

02

03	'stosujemy wczesne wiązanie

04	'uruchamiamy PP

05	Dim appPP As PowerPoint.Application

06	Set appPP = New PowerPoint.Application

07

08	appPP.Visible = True

09

10	'tworzymy nową prezentację, alternatywnie

11	'można otworzyć inną, istniejąca prezentację metodą .Open

12

13	Dim presPP As PowerPoint.Presentation

14	Set presPP = appPP.Presentations.Add

15

16	'dodajemy slajd do prezentacji, pusty

17	Dim sldPP As PowerPoint.Slide

18	Set sldPP = presPP.Slides.AddSlide(1, presPP.SlideMaster.CustomLayouts(7))

19

20	'wkopiowujemy i pozycjonujemy kolejne elementy

21	'z arkusza excel: zakres komórek oraz wykres

22

23	'wariant 1 dla wykresu- wklejanie domyślne obiektu

24	Dim chrEXL As ChartObject

25	Set chrEXL = Arkusz1.ChartObjects(1)

26

27	chrEXL.Copy

28

29	'wklejmy w PP

30	Dim chrPP As PowerPoint.Shape

31	Set chrPP = sldPP.Shapes.Paste(1)

32

33	'ustawiamy pozycję

34	With chrPP

35

.Top = 10

36	.Left = 10

37

End With

38

39	'wariant 2 dla wykresu

40	'wklejamy wykres ponownie ale tym razem jako obrazek

41	sldPP.Shapes.PasteSpecial ppPasteJPG

42	Set chrPP = sldPP.Shapes(sldPP.Shapes.Count)

43	With chrPP

44

.Top = 10

45	.Left = sldPP.Master.Width / 2

46

End With

47

48	'wariant 3- dla zakresu- wklejanie domyślne

49	Dim rngEXL As Range

50	Set rngEXL = Arkusz1.Range("a1:c4")

51	rngEXL.Copy

52

53	'sklejanie

54	Dim rngPP As PowerPoint.Shape

55	Set rngPP = sldPP.Shapes.Paste(1)

56

57	With rngPP

58	.Top = sldPP.Master.Height / 2

59	.Left = 10

60

End With

61

62	'wariant 4- dla zakresu- wklajanie jako obrazek

63	rngEXL.Copy

64	sldPP.Shapes.PasteSpecial ppPasteBitmap

65	Set rngPP = sldPP.Shapes(sldPP.Shapes.Count)

66	With rngPP

67	.Top = sldPP.Master.Height / 2

68	.Left = sldPP.Master.Width / 2

69

End With

70

End Sub

Efektem działania powyższej procedury jest poniższy przykładowy slajd w prezentacji PowerPoint.

Interakcja pomiędzy aplikacjami MS Office (3 z ...wielu)

Jendą z praktycznych technik wykorzystujących interakcję pomiędzy aplikacjami pakietu Office jest wypełnianie dokumentu Word danymi pochodzącymi np. z tabeli znajdującej się w aplikacji Excel. Chodzi o operację zbliżoną do pojęcia 'korespondencji seryjnej' z jedną istotną różnicą- odpowiednie informacje z Excela umieszczane są w zakładkach znajdujących się w dokumencie Word.

Prześledźmy to na następującym przykładzie:

1. Tworzymy dwa pliki - plik Excel'a o dowolnej nazwie oraz plik Word'a, który umownie nazwiemy 'dokument z zakładkami.docx'. Dla prostoty tego przykładu oba pliki proszę zapisać we wspólnym folderze (plik Excela powinien być typu xlsm gdyż zaraz dodamy do niego odpowiednie makro).

2. W treści dokumentu Word'a umieśćmy jakiś tekst, np.

Szanowny Pan Jan Kowalski, 
w nawiązaniu do rozmowy telefonicznej...

i jednocześnie oznaczmy naszego 'Jana Kowalskiego' jako zakładkę o nazwie ImieNazwisko. To właśnie ta zakładka będzie przykładowym fragmentem tekstu, który będziemy następnie podmieniać wykorzystując kod VBA. Zamknijmy utworzony dokument.

3. W pliku Excela musimy przygotować odpowiedni kod, który wykona następujące operacje:

• uruchomi aplikację MS Word (wykorzystując technikę wczesnego wiązania)
• otworzy plik DOCX znajdujący się w tym samym folderze
• wstawi w zakładce ImieNazwisko dane nowej osoby
• i tu kluczowy moment- wstawi ponownie zakładkę ImieNazwisko ze względu na fakt, że poprzedni punkt automatycznie ją usunął
• opcjonalnie można dodać polecenie zapisywania pliku Worda- ten fragment nie został uwzględniony w poniższym kodzie.

Całość obrazuje poniższy zestaw dwóch procedur. Wewnątrz kodu znaleźć można dodatkowe komentarze wyjaśniające kluczowe punkty kodu VBA.

view sourceprint?

01	Sub WordApplicationBookmark()

02

03	Dim appWRD As Word.Application

04	Set appWRD = New Word.Application

05

06	appWRD.Visible = True

07

08	'praca ze zmiennymi obiektowymi jest _

09	jedną z bardziej wydajnych i efektywnych _

10	sposobów tworzenia kodu:

11

12	Dim docWRD As Word.Document

13	Set docWRD = appWRD.Documents.Open(ThisWorkbook.Path & _

14	"\dokument z zakładkami.docx")

15

16	'uruchamiamy procedurę zewnętrzną przekazując parametry: _

17	dokument Word w którym wykonany akcję, _

18	nazwę zakładki- ImieNazwisko, _

19	nową treść zakładki- docelowo pobierana z komórki Excela

20	PodmianaTresciZakladki docWRD, "ImieNazwisko", "Tadeusz Nowak"

21

22

23

End Sub

view sourceprint?

01	Sub PodmianaTresciZakladki(DokumentWord As Word.Document, _

02	Zakladka As String, _

03	Tekst As String)

04

05	With DokumentWord

06	'odnajdujemy zakładkę i

07	'zapamiętujemy jej miejsce początkowe

08	Dim ZakladkaStart As Long

09	ZakladkaStart = .Range.Bookmarks(Zakladka).Start

10

11	'podmieniamy tekst zakładki na nowy co _

12	skutkuje jej automatycznym usunięciem

13	.Bookmarks(Zakladka).Range.Text = Tekst

14

15	'przywracamy zakładkę w celu ponownego, _

16	późniejszego jej wykorzystania

17	.Bookmarks.Add Name:=Zakladka, _

18	Range:=.Range(ZakladkaStart, _

19	ZakladkaStart + Len(Tekst))

20

21

End With

22

23

End Sub

Interakcja pomiędzy aplikacjami MS Office (2 z ...wielu)

Kontynuując wpis z ubiegłego tygodnia chciałby krótko zaprezentować drugi typ wiązania a więc...

Wiązanie późne (late binding)

Wiązanie późne nie będzie wymagać od nas żadnych dodatkowych przygotowań i wskazywania zewnętrznych bibliotek wcześniej, niż dopiero na etapie edycji kodu. I tak, aby uruchomić aplikację MS Word i utworzyć w niej nowy dokument niezbędny kod wygląda następująco:

view sourceprint?

1	Sub UruchomMSWordaLateBinding()

2

3	Dim appWord As Object

4	Set appWord = CreateObject("Word.Application")

5

6	appWord.Visible = True

7	appWord.Documents.Add

8

9

End Sub

Zalety późnego wiązania:

• nie wskazujemy konkretnej wersji zewnętrznej biblioteki tak więc kod napisany w środowisku Office 2013 zadziała także skutecznie w starszych wersjach Office, np. 2007 czy 2010
• biblioteka zostanie podłączona dopiero na etapie kompilacji kodu

Wady późnego wiązania:

• utrudniona edycja przez co nie jesteśmy w stanie skorzystać z systemu IntelliSense edytując kod dla zewnętrznej aplikacji
• zazwyczaj mniejsza wydajność kodu
• brak do 'stałych' języka VBA dla zewnętrznej aplikacji (w miejsce np. WdInformation.wdActiveEndPageNumber niezbędne jest wstawienie numerycznego odpowiednika, tu: 3)

Dla osób zainteresowanych dogłębnie typami wiązań odsyłam do wyszukiwarki Google. Temat ten jest bardzo obszernie przedstawiony w wielu artykułach zarówno w języku polskim jak i językach obcych.

Na koniec chciałbym dodać, że zagadnienia interakcji omawiane są w czasie naszych kursów na poziomie średnio-zaawansowanym. Serdecznie zapraszam na nasze szkolenia z VBA organizowane w Warszawie, Krakowie i Wrocławiu.

Interakcja pomiędzy aplikacjami MS Office (1 z ...wielu)

Tym wpisem chciałbym rozpocząć dłużą serię postów omawiających szereg zagadnień związanych z interakcją pomiędzy aplikacjami pakietu MS Office z wykorzystaniem kodu VBA. Postaram się omówić kilka prostych przykładów dot. aplikacji Excel, Word, PowerPoint i Access.

Rzecz od której nie sposób nie rozpocząć to zagadnienia dot. typów łączenia aplikacji, a więc jak wskazać w kodzie aplikacji Excel inną aplikację, np. Word'a w celu umożliwienia wykonania dalszych operacji w edytorze tekstu z poziomu kodu VBA umieszczonego w Excelu. Zagadnienie to określa się pojęciem 'wiązania' dookreślając możliwość zastosowania dwóch typów wiązań: 'wiązania wczesnego' (early binding) lub 'wiązania późnego' (late binding). W tym i kolejnym wpisie omówię krótko oba te warianty od strony praktycznej.

Wczesne wiązanie (early binding)...
...wymagać będzie od użytkownika wykonania akcji 'przed kompilacją kodu'. Operacja wiąże się z koniecznością ustawienia referencji w edytorze VBA w pozycji Menu >> Tools >> References... gdzie następnie należy wskazać i zaznaczyć odpowiednią bibliotekę lub aplikację. Przykładowo obrazuje to poniższy zrzut ekranu gdzie z poziomu aplikacji Excel wskazano referencję do aplikacji Word

Niezbędny kod VBA tworzący wiązanie wyglądać będzie następująco (tu: uruchomienie i wyświetlenie aplikacji Word oraz utworzenie nowego dokumentu)

view sourceprint?

1	Sub UruchomMSWorda()

2

3	Dim appWord As Word.Application

4	Set appWord = New Word.Application

5

6	appWord.Visible = True

7	appWord.Documents.Add

8

9

End Sub

Zalety wczesnego wiązania:

• ułatwiona edycja z wykorzystaniem IntelliSense. W powyższym przykładzie dla zmiennej appWord będzie podpowiadać się zestaw dostępnych metod i właściwości  
• szybkość wykonania kodu- kompilator wie jakie zewnętrzne biblioteki należy uruchomić już na etapie prekompilacji
• dostęp do 'stałych' języka VBA dla zewnętrznej aplikacji (np. WdInformation.wdActiveEndPageNumber)

Wady wczesnego wiązania:

• ustawiając referencję do wskazanej biblioteki oczekiwana jest dokładnie ta sama wersja aplikacji, z którą wykonywana jest interakcja. Powyższy przykład nie zadziała z aplikacją MS Word w wersji 2010 lub wcześniejszą.

W kolejnym wpisie przedstawię pojęcie wiązania późnego...

Komórka w tabeli dokumentu Word- wstawianie tekstów i obiektów

Myślę, że warto dodać jeszcze jeden wpis dot. Word VBA i pracy z komórkami tabeli w dokumencie Word. Przeglądając ostatnio kilka forów dostrzegłem, że niekiedy problemy sprawia dodawanie zawartości do komórki tabeli.

Przyjmijmy na początek następującą referencję do komórki w tabeli w aktywnym dokumencie:

view sourceprint?

1	Dim Komórka As Cell

2	Set Komórka = ActiveDocument.Tables(1).Cell(1, 1)

Komórka.Range.Text = "Hello World"
Próba dodania prostego tekstu do komórki nie stanowi problemu:

view sourceprint?

1	ActiveDocument.Fields.Add Komórka.Range, WdFieldType.wdFieldData

Problemem jednak okazuje się dodanie do komórki np. pola z datą. Poniższy kod wygeneruje błąd (Run-time error '4605'):

view sourceprint?

1	ActiveDocument.Fields.Add Komórka.Range, WdFieldType.wdFieldData

Powyższy problem wynika z faktu, ze wewnątrz komórki występuje znak końca komórki, który widać po włączeniu wyświetlania znaków specjalnych:

W tego typu sytuacjach rozwiązaniem problemu jest wstawienie obiektu z pominięciem znaku końca komórki. Wykonać to można najprościej przed określenie początku i końca  obszaru wstawiania:

view sourceprint?

1	ActiveDocument.Fields.Add _

2	ActiveDocument.Range( _

3	Komórka.Range.Start, _

4	Komórka.Range.End - 1), _

5	WdFieldType.wdFieldDate

Warto zwrócić na wartość -1 w powyższym kodzie- dzięki tej korekcie pomijamy problematyczny symbol końca komórki.

Usuwanie stron w MS Word z pomocą VBA 2/2

Kontynuując poprzedni wpis dot. stron dokumentu warto dodać, że istnieje jeszcze jeden ze sposobów odnajdywania strony dokumentu- przez wykorzystanie zakładki "\page". Poniższe odwołanie spowoduje, że zaznaczony zostanie obszar strony na której znajduje się bieżące zaznaczenie:

view sourceprint?

1	Selection.Bookmarks("\page").Select

a stąd już blisko do usunięcia całej strony przez minimalna modyfikację powyższego kodu:

view sourceprint?

1	Selection.Bookmarks("\page").Range.Delete

Jak widać wykorzystanie obiektu Bookmarks("\page") domyślnie weryfikuje układ dokumentu przez pryzmat widoku o czym pisałem w poprzednim wpisie.

Przy okazji warto zwrócić uwagę na zakładki specjalne występujące w dokumencie gdyż często okazują się być niezmiernie przydatne. Określane są one mianem zakładek predefiniowanych i pozwalają m.in. na:

• odnalezienie początku dokumentu \StartOfDoc
• odnalezienie końca dokumentu \EndOfDoc
• odnalezienie początku lub końca bieżącego zaznaczenia : \StartOfSel, \EndOfSel

itp. Kompletną listę znaleźć można pod tym linkiem.

Przy okazji serdecznie zapraszam na jesienne szkolenia z VBA dla aplikacji Excel. Nasze kursy organizowane są w Warszawie, Krakowie i Wrocławiu na różnych poziomach zaawansowania.  Szczegółowe informacje na naszej stronie www.SzkoleniaVBA.pl.

Usuwanie stron w MS Word z pomocą VBA 1/2

Każdy kto kiedykolwiek miał styczność z VBA dla Worda ma świadomość, że nie istnieje jednoznacznie definiowany obiekt odpowiadający stronie dokumentu. Od razu wyjaśnię dlaczego tak jest dla tych osób, które są zaskoczone tym faktem- dokument traktowany jest bowiem jako ciąg tekstu o zdefiniowanych początku i końcu. Trudno na etapie obiektowej analizy zawartości dokumentu (sprawdzając ilość słów, paragrafów czy zdań)  jednoznacznie określić ile on zajmie stron skoro użytkownik ostatecznie będzie mógł zastosować czcionkę różnej wielkości, zastosuje inny format papieru, itp., a przez to wpłynie na różną ilość stron dokumentu.

Nie jest jednak tak źle- pojecie strony pojawia się wtedy, gdy zaczynamy się odwoływać do dokumentu przez pryzmat bieżącego widoku. I tak na przykład aby sprawdzić ilość stron dokumentu możemy zastosować następujące zapytanie:

view sourceprint?

1	Debug.Print ActiveWindow.Panes(1).Pages.Count

W powyższym przykładzie jesteśmy w stanie odnaleźć kolekcję Pages odpowiadającą stronie dokumentu. Ale kolekcja ta pojawia się jako element widoku, który reprezentowany jest przez obiekt okna Window (ActiveWindow).

Powyższy sposób nie jest jedynym na określenie ilości stron dokumentu. O wiele bardziej popularny sposób to odwołanie się do właściwości .Information obiektu Range, która zwraca szereg informacji dot. pliku Word:

view sourceprint?

1	Debug.Print ActiveDocument.Range.Information(wdNumberOfPagesInDocument)

W kolejnym wpisie zaprezentuję jeszcze jeden sposób odnajdywania strony, a technikę tą zaprezentuję w kontekście usuwania całej strony pliku.

Podświetlanie wierszy parzystych i nieparzystych w Excelu z wykorzystaniem formatowania warunkowego

Relatywnie często wykorzystywanym rozwiązaniem jest naprzemienne podświetlenie  wierszy parzystych i nieparzystych. Podstawowej sposoby uzyskania tego efektu to:

1. wypełnienie komórek i skopiowanie formatowania do wskazanego obszaru, co wymaga pracy i jednocześnie staje się problematyczne w momencie gdy dodamy kolejny wiersz   
2. zastosowanie obiektu Tabela (ListObject po stronie obiektów VBA), co wiąże się z umiejętnością wykorzystania obiektu Tabela

Do tej listy chciałbym dodać jeszcze relatywnie prostą choć niezbyt popularną technikę formatowania warunkowego. W tym celu:

Krok 1- zaznaczamy obszar, w którym ma funkcjonować podświetlanie wiersza- w naszym przykładzie A1:K20 (ważne, obszar zaznaczamy od komórki A1 w kierunku K20, nie odwrotnie)..

Krok 2- ustawiamy formatowanie warunkowe: Menu >> Narzędzia Główne >>Formatowanie Warunkowe >> Nowa Reguła… >> Użyj formuły do określenia komórek… >> w miejsce formuły wstawiamy =MOD(WIERSZ(A1);2)=0 >> określamy formatowanie po wciśnięciu przycisku >>Formatuj… >> Akceptujemy wszystkie ustawienia.

Warto wspomnieć o dwóch podstawowych korzyściach z zastosowania tej techniki: po pierwsze- nie likwiduje ona formatowania wynikającego z wypełnienia komórek innym kolorem. Należy jednak pamiętać, że formatowanie warunkowe ma przewagę nad zwykłym wypełnieniem obszaru. Po drugie- dodanie nowych wierszy w obszarze formatowania spowoduje zastosowanie tej samej reguły, a przez to automatycznie nowe wiersze będą uwzględniały efekt formatowania.

Podświetlanie aktywnego wiersza w arkuszu Excel

Po dłuższej wakacyjnej przerwie czas na kolejny wpis tym razem wpis z pogranicza VBA i zaawansowanej techniki pracy z Excelem- jak uzyskać efekt podświetlenia dla aktywnego wiersza?

Wariant 1- tylko VBA

Wariant ten ma podstawową wadę polegającą na tym, że dokonujemy ‘podmiany’ wypełnienia w bieżącym wierszu, a przez to nie jesteśmy w stanie efektywnie przywrócić poprzedniego układu kolorów, o ile takie istniały (pomijam możliwość tworzenia zaawansowanych konstrukcji programistycznych). Zaletą jest to, że możemy podświetlić szerszy obszar niż tylko ten odpowiadający pojedynczemu wierszowi.

Niezbędny kod VBA wymaga oprogramowania zdarzenia Selection_Change. W tym celu proszę w wybranym module arkuszowym wkleić poniższy kod. Dodatkowe informacje znajdują się wewnątrz kodu w postaci komentarzy.

view sourceprint?

01	'zmienna publiczna- jej celem jest

02	'zapamiętanie poprzenio pokolorowanego obszaru

03	'musi znajdować się w górnej części modułu

04	Dim prevTarget As Range

05

06	Private Sub Worksheet_SelectionChange(ByVal Target As Range)

07

08	'niezbędna obsługa błędów- przy pierwszym wywołaniu lub

09	'w przypadku awarii nasza zmienna publiczna nie

10	'przechowuje żadnej wartości

11	On Error Resume Next

12	'usuwamy kolorowanie poprzedniego obszaru

13	prevTarget.EntireRow.Interior.Color = xlNone

14

15	'ustawiamy kolorowanie wierszy dla obecnego obszaru

16	Set prevTarget = Target

17	Target.EntireRow.Interior.Color = vbYellow

18

19

End Sub

Wariant 2- VBA + formatowanie warunkowe

Wariant ten jest o wiele bardziej efektywny i wydajny. Co najważniejsze- nie zastępuje innych kolorów i formatowania występującego w arkuszu.

Krok 1- zaznaczamy obszar, w którym ma funkcjonować podświetlanie wiersza- w naszym przykładzie A1:K20 (ważne, obszar zaznaczamy od komórki A1 w kierunku K20, nie odwrotnie)

Krok 2- ustawiamy formatowanie warunkowe: Menu >> Narzędzia Główne >>Formatowanie Warunkowe >> Nowa Reguła… >> Użyj formuły do określenia komórek… >> w miejsce formuły wstawiamy =KOMÓRKA("wiersz")=WIERSZ(A1) >> określamy formatowanie po wciśnięciu przycisku >>Formatuj… >> Akceptujemy wszystkie ustawienia. 

Ważne! Komórkę A1 w podaje formule należy odpowiednio zmienić w przypadku zaznaczenia innego obszaru niż przykładowe A1:K20

Krok 3- ostatni krok to wymuszenie przeliczenia komórek po każdej zmianie zaznaczenia. W tym celu w module arkuszowym po stronie edytora VBA dodajemy prostą obsługę zdarzenia Selection_Change:

view sourceprint?

1	Private Sub Worksheet_SelectionChange(ByVal Target As Range)

2	'tu m.in. wymuszamy przeliczanie formuły

3	'w formatowaniu warunkowym

4	Target.Calculate

5

End Sub

Przy okazji serdecznie zapraszam na jesienne kursy z programowania VBA. Tym razem zaplanowaliśmy kilka terminów w Warszawie, Krakowie i Wrocławiu. Nasze szkolenia jak zawsze przygotowane są na najwyższym poziomie, w oparciu o wieloletnią praktyczną widzę i ciągle zdobywane i poszerzane doświadczenie.

Pasek postępu w komórce Excela

Całkiem dużym zainteresowaniem cieszy się mój zeszłoroczny wpis dot. paska postępu wyświetlanego w pasku stanu (StatusBar) aplikacji Excel, którego treść można znaleźć pod tym linkiem. Pomyślałem więc, że dodam jeszcze jeden wpis prezentujący inny wariant paska postępu. Nadal będę trzymał się koncepcji aby pasek był rozwiązaniem minimalistycznym. Tym razem więc informacje o postępie umieścimy w...dowolnej komórce Excel. Oczekiwany efekt prezentuje poniższa grafika:

I tu należy uczynić ważne zastrzeżenie- rozwiązanie to nie będzie dostępne dla wszystkich wersji MS Excel gdyż oparte jest o zaawansowane techniki formatowania warunkowego, które rozbudowane zostało począwszy od wersji 2007.

Krok 1 (wariant ręczny).
Tworzymy komórkę z formatowaniem warunkowym, która będzie zawierać nasz pasek postępu. Proponowany wariant to: Menu >> Formatowanie warunkowe >> Pasek danych gdzie następnie wartości ustawiamy jako liczby i określamy ich minimalną i maksymalną wartość jako odpowiednio 0 i 1. Wskazane będzie również dodanie do naszej komórki obramowania.

Krok 1 (kod VBA)
Proponuję wariant dynamicznego tworzenia paska, a więc taki, gdzie nie definiujemy stałego miejsca jego lokalizacji, a zależnie od okoliczności wyświetlić go możemy w dowolnej komórce Excela (cały przykład obrazuje działanie dla aktywnej komórki). W tym celu będziemy potrzebowali kod VBA, który utworzy w komórce formatowanie warunkowe. Poniższy kod dostarcza niezbędny minimalny zestaw instrukcji VBA (komentarze wewnątrz kodu dodatkowo wyjaśniają poszczególne sekcje).

view sourceprint?

01	Sub PasekPostępuFormatowanie(rngCell As Range)

02

03	'tworzymy formatownie warunkowe

04	With rngCell

05	.FormatConditions.AddDatabar

06	With .FormatConditions(1)

07	'opcjonalnie false jeżeli nie chcemy

08	'wyświetlać liczb wewnątrz paska

09	.ShowValue = True

10	.SetFirstPriority

11	.MinPoint.Modify _

12	newtype:=xlConditionValueNumber, _

13	newvalue:=0

14	.MaxPoint.Modify _

15	newtype:=xlConditionValueNumber, _

16	newvalue:=1

17	.BarColor.Color = 13012579

18	.BarFillType = xlDataBarFillSolid

19

End With

20

21	'tworzymy obramowanie- sekcja opcjonalna

22	With .Borders()

23	.LineStyle = xlContinuous

24	.ColorIndex = 0

25	.TintAndShade = 0

26	.Weight = xlThin

27

End With

28

29	'tworzymy styl tekstu- o ile .ShowValue

30	'powyżej ustawiono na True

31	.Style = "Percent"

32	.HorizontalAlignment = xlCenter

33	.VerticalAlignment = xlCenter

34

End With

35

End Sub

Krok 2
Nie pozostało nam nic innego jak wypróbować nasz kod w oparciu o testową procedurę.

view sourceprint?

01	Sub Pasek_Postępu_Komórka_Excel()

02

03	Dim iTest As Double, jTest As Long

04

05	'tworzymy formatowanie warunkowe we wskazanej komórce

06	Dim rngProgress As Range

07	Set rngProgress = ActiveCell'lub obszar typu Range("A5")

08	PasekPostępuFormatowanie rngProgress

09

10	'pętla testowa- prezentacyjna

11	For iTest = 0 To 1 Step 0.01

12

13	'poniższa pojedyncza linia powinna być

14	'umieszczona wewnątrz Twojego kodu

15	'a zmienna iTest przekazywać % wykonania

16	rngProgress.Value = iTest

17

18	'pętla spowalniająca do celów prezentacji

19	For jTest = 1 To 20000000

20	Next jTest

21

22	Next iTest

23

24

End Sub

Krok 3. 
Myślę, że po wykonaniu zadania nasz pasek powinien zniknąć. Chyba najproście można to zrobić kopiując pustą komórkę (np. ostatnią komórkę w arkuszu) w miejsce naszego paska postępu. Poniższy kod można dodać po zakończeniu pętli, przed zakończeniem procedury.

view sourceprint?

1	Cells(Rows.Count, Columns.Count).Copy rngProgress

Jeżeli ktoś miałby wątpliwość jak cała koncepcja działa proponuję skopiować do nowego modułu obie powyższe procedury. Następnie proszę uruchomić procedurę z kroku 2 i obserwować zachowania aktywnej komórki.

Arkusz startowy gdy makra zostały wyłączone

Na początek opis problemu:

• skoroszyt posiada dwa arkusze- MacroDisabled i MacroEnabled
• w sytuacji gdy skoroszyt jest otwierany z dostępem do makr automatycznie wyświetlony powinien zostać arkusz MacroEnabled. Ten punkt możemy uznać za prosty i oczywisty gdyż wszystko czego potrzebujemy to prosta procedura zdarzenia Private Sub Workbook_Open()
• w sytuacji gdy skoroszyt otwierany jest bez dostępu do makr zawsze wyświetlony ma być arkusz MacroDisabled. Niestety, nie możemy sięgnąć po procedurę zdarzenia gdyż makra są wyłączone.
• z tego samego skoroszytu korzystamy naprzemiennie w opcji Macro Enabled i Macro Disabled
• lista zakładek arkuszowych jest ukryta (opcjonalnie arkusze mogą być ukryte) dlatego też po otwarciu pliku w opcji MacroDisabled nie jesteśmy w stanie ręcznie przejść do arkusza MacroEnabled.

Jedyna sytuacja, która staje się problematyczna to moment, gdy po otwarciu pliku w opcji MacroEnabled (krok 1) następuje otwarcie pliku w opcji MacroDisabled (krok 2). Krok 1 otwiera arkusz Enabled lecz musi zamknąć skoroszyt w opcji Disabled. Oczywiście możemy wykorzystać zdarzenie Workbook_BeforeSave lecz należy pamiętać, że staje się to problematyczne gdy użytkownik ostatecznie postanawia wrócić do pracy i nie zamyka skoroszytu. W tej sytuacji aktywowany zostałby arkusz Disabled i dalsza praca nie mogła by być kontynuowana.

Poniżej prezentuję proponowane rozwiązanie tej sytuacji wraz z dodatkowymi komentarzami.

Krok 1. otwarcie skoroszytu z dostępem do makr (MacroEnabled). Wykorzystamy zdarzenie Open w celu aktywacji arkusza:

view sourceprint?

1	Private Sub Workbook_Open()

2

3	Sheets("MacroEnabled").Activate

4

5

End Sub

Krok 2. zapisanie arkusza w sytuacji, gdy skoroszyt został otwarty w wariancie MacroEnabled. Wykorzystamy zdarzenie Before_Save oraz dodatkową procedurę pomocniczą:

view sourceprint?

01	Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

02

03	If ActiveSheet Is Sheets("MacroDisabled") Then Exit Sub

04

05	If PoprzedniSheet Is Nothing Then

06

07	Set PoprzedniSheet = ActiveSheet

08	Sheets("MacroDisabled").Activate

09

10	'kluczowa metoda- wywoływana o ile użytkownik

11	'nie zamyka pliku a jedynie go zapisuje lub

12	'rezygnuje z zapisania/zamknięcia

13

14	Application.OnTime Now, "AktywowaniePoprzedniego"

15

End If

16

End Sub

Krok 3. w osobnym module tworzymy zmienną publiczną oraz kod przywracający aktywny arkusz:

view sourceprint?

01	Public PoprzedniSheet As Worksheet

02

03	Public Sub AktywowaniePoprzedniego()

04

05	If Not PoprzedniSheet Is Nothing Then

06	PoprzedniSheet.Activate

07	Set PoprzedniSheet = Nothing

08

End If

09

10

End Sub

Export wybranych obszarów komórek do pliku PDF

Podstawowy sposób zapisania w formacie PDF obszarów komórek Excel opiera się na wykorzystaniu metody .ExportAsFixedFormat. Warto jednak wiedzieć, że metoda ta dostępna jest dla różnych obiektów na różnych poziomach hierarchii:

• komórek- obiekt Range
• arkusza- obiekt Worksheet
• skoroszytu- obiekt Workbook

1. Obiekt Range udostępnia metodę wprost umożliwiając stworzenie instrukcji:

view sourceprint?

1	Range("A1:G10").ExportAsFixedFormat xlTypePDF, "RangeToPDF.pdf"

Warto jednak pamiętać o możliwości wykorzystania innych metod definiowania obiektu Range. Poniższy przykład również jest prawidłowy lecz co ciekawe- każdy z obszarów wewnątrz instrukcji Union zostanie zapisany na osobnej stronie dokumentu PDF:

view sourceprint?

1	Union(Range("A1:E5"), Range("a10:G20")). _

2	ExportAsFixedFormat xlTypePDF, "UnionToPDF.pdf"

Powyższa techniki nie zadziała oczywiście dla obszarów pochodzących z różnych arkuszy. W wariancie tym, co ważne, ignorowane są jednak obszary wydruku które są istotne przy eksporcie opisanym w punkcie 2 i 3 poniżej.

2. Obiekt Worksheet umożliwia wydrukowanie całego arkusza lub jego części ustawionej jako obszar wydruku:

view sourceprint?

1	Sheets(1).ExportAsFixedFormat xlTypePDF, "SheetToPDF.pdf"

3. Obiekt Workbook również posiada metodę .ExportAsFixFormat a jej wykorzystanie spowoduje eksport wszystkich ustawionych obszarów wydruku lub całych arkuszy do pliku PDF:

view sourceprint?

1	ThisWorkbook.ExportAsFixedFormat xlTypePDF, "WorkbookToPDF.pdf"

Co powinniśmy zrobić jeżeli chcemy wyeksportować do PDF różne obszary z różnych arkuszy? W takiej sytuacji musimy w pierwszym kroku ustawić obszary wydruku indywidualnie dla każdego arkusza wykorzystując poniższe dostępne techniki:

view sourceprint?

1	ActiveSheet.PageSetup.PrintArea = "$B$5:$E$14"

2	ActiveSheet.PageSetup.PrintArea = Range("B5:E14").Address

a następnie wywołać metodę eksportu dla skoroszytu.

Blokada edycji zakładek w dokumencie MS Word

Poniższy problem został wywołany niedawno na forum StackOverflow.Com i można go opisać w następujących kilku słowach:

W jaki sposób zablokować dostęp do zakładek dokumentu jednocześnie umożliwiając edycję pozostałych elementów dokumentu?

Całkiem ciekawe rozwiązanie tego problemu opiera się na wykorzystaniu obiektów ContentControl (CC) i zdarzeń powiązanych z tym obiektami. Co ważne, rozwiązanie to dostępne jest począwszy od wersji 2007 aplikacji MS Word.

Krok 1. Każdą zakładkę w dokumencie musimy umieścić wewnątrz obiektu ContentControl. Jeżeli nie chcemy wykonywać tej operacji ręcznie wystarczy uruchomić proste makro:

view sourceprint?

1	Sub Dodawanie_ContentControl_do_Zakladki()

2

3	Dim bookM As Bookmark

4	For Each bookM In ActiveDocument.Bookmarks

5	ActiveDocument.ContentControls.add wdContentControlRichText, _

6	bookM.Range

7

Next

8

9

End Sub

Krok 2. Zakładamy, że naszym celem jest uniemożliwienie edycji bez blokowania obiektu ContentControl. W tym celu wykorzystamy zdarzenie powiązane z tego typu obiektami i wymusimy zmianę zaznaczenia w sytuacji gdy użytkownik kliknie wewnątrz obiektu CC:

view sourceprint?

01	Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)

02

03	If ContentControl.Range.Bookmarks.Count > 0 Then

04	'Opcjonalna informacja dla użytkownika

05	MsgBox "Nie powinieneś zmieniać zawartości " & _

06	"tego obszaru i tej zakładki." & _

07	vbNewLine & "Zostaniesz przeniesiony poza obszar edycji"

08

09	'Opcjonalne przeniesienie poza obszar CC/Zakładki

10	Dim newPos As Long

11	newPos = ContentControl.Range.End + 2

12	ActiveDocument.Range(newPos, newPos).Select

13

14

End If

15

16

End Sub

Alternatywnie krok 1 można rozszerzyć o mechanizm blokowania edycji obiektu CC i jednocześnie zrezygnować z obsługi zdarzenia obiektów CC. W tym wypadku zamiast kroków 1 i 2 zastosujemy poniższy rozszerzony kod z kroku 1:

view sourceprint?

01	Sub Add_Bookmark_CC_Protected()

02

03	Dim bookM As Bookmark

04	Dim CC As ContentControl

05	For Each bookM In ActiveDocument.Bookmarks

06	Set CC = ActiveDocument.ContentControls.Add( _

07	wdContentControlRichText, bookM.Range)

08

09	'kluczowa w tym wariancie blokada dostępu do obiektu CC

10	CC.LockContents = True

11

Next

12

13

End Sub

Imitacja zdarzenia MouseOver (Hover) dla komórki Excela...

...czyli jak wywołać makro pod wpływem najechania wskaźnikiem myszy nad określoną komórkę Excela.

Znawcy VBA z pewnością od razu domyślają się, że nie chodzi o wykorzystanie zdarzenia gdyż do wersji Office 2013 w zasobie dostępnych zdarzeń nie znajdziemy takiego, które wywoła efekt MouseOver dla komórki Excela. Okazuje się jednak, że efekt ten możemy uzyskać połączywszy ze sobą dwa elementy:

• własną funkcję użytkownika (UDF), wewnątrz której zdefiniujemy określoną akcję wywoływaną pod wpływem najechania myszą nad komórkę
• formułę komórkową HIPERŁĄCZE (opcjonalnie połączoną z formułą JEŻELI).

Zanim zaprezentuję określony kod nakreślmy oczekiwany scenariusz:

• komórka A1 zawierać będzie tekst: "Najedź myszą w celu uzyskania dodatkowych informacji"
• komórka B1 zawierać będzie tekst: "Wyłącz dodatkowe informacje"
• najechanie myszą na komórki A1 i B1 będzie odpowiednio odkrywać i ukrywać obiekt Shape zawierający dodatkowe informacje (kształt Shape w naszym przypadku nazywa się "ShapeTip1")

Krok 1. Zacznijmy od stworzenia funkcji UDF, której zadaniem będzie pobranie parametru decydującego o odkryciu/ukryciu kształtu:

view sourceprint?

1	Function MouseOver_ShowHide(boShow As String)

2

3	If boShow = True Then

4	ActiveSheet.Shapes("ShapeTip1").Visible = True

5

Else

6	ActiveSheet.Shapes("ShapeTip1").Visible = False

7

End If

8

9	End Function

Krok 2. W arkuszu tworzymy kształt i ustawiamy jego nazwę na ShapeTip1.

Krok 3. W komórce A1 dodajemy formułę HYPERLINK wg schematu:

view sourceprint?

1	=HIPERŁĄCZE(MouseOver_ShowHide(PRAWDA);

2	"Najedź myszą w celu uzyskania dodatkowych informacji")

Z niewiadomych przyczyn formuła HIPERŁĄCZE zwracać może błąd dla powyższej konstrukcji dlatego też warto od razu obudować ją w formułę JEŻELI(CZY.BŁ(...)) (wskazówka: formuła JEŻELI.BŁĄD() niekoniecznie sprawdza się w tej sytuacji co warto przetestować).
Ostatecznie więc do komórki B1 wpisujemy następującą formułę wyzwalającą ukrywanie:

view sourceprint?

1	=JEŻELI(CZY.BŁ(HIPERŁĄCZE(MouseOver_ShowHide(FAŁSZ)));

2	"Wyłącz dodatkowe informacje";"Wyłącz dodatkowe informacje")

Ostateczny układ arkusza mógłby wyglądać jak na poniższym zrzucie ekranu. Najechanie muszą na komórkę A1 odkrywa prostokąt z komunikatem. Ukrywanie prostokąta wyzwalane jest przez najechanie myszą na komórkę B1.

Wskazówki i uwagi końcowe:

• warto zwrócić uwagę, że zatrzymanie myszy nad komórkami A1 i B1 powodują nieustanne wywoływanie utworzonej funkcji UDF co nie jest zbyt efektywne
• niestety nie udało mi się stworzyć mechanizmu autoukrywania kształtu po opuszczeniu komórki A1 bez najechania na komórkę B1. Funkcja nie wyzwala zdarzeń ani polecenia Application.OnTime.
• Inspiracją do stworzenia niniejszego wpisu były przykłady znalezione w sieci. Tu chciałbym jednak polecić Waszej uwadze doskonały arkusz oparty o podobne rozwiązanie, który prezentuje okresowy układ pierwiastków. Pod tym linkiem znajdziecie zarówno gotowy przykład pliku Excela jak również prosty filmik prezentujący działanie tego narzędzia.

Sprawdzenie warunku zapisanego w zmiennej tekstowej

Wyobraźmy sobie sytuację, w której kryteria i warunki zostały określone w postaci zmiennych tekstowych co obrazuje poniższy przykład:

Dim Warunek As String
Warunek = "1=1"
lub też
Warunek = "1+5=6"
albo
Warunek = "A=A"

Czy istnieje możliwość aby sprawdzić prawdziwość powyższych warunków w kodzie VBA? Otóż i tak i nie. W zakresie warunków oparty o liczby możemy sięgnąć po funkcję Evaluate i zweryfikować prawdziwość powyższych przykładów:

Warunek = "1=1"
Debug.Print Evaluate(Warunek) >> True

Warunek = "1+5=6"
Debug.Print Evaluate(Warunek) >> True

Warunek = "1+5=7"
Debug.Print Evaluate(Warunek) >> False

Problemem jest jednak poniższy zapis dot. zmiennych tekstowych, który zwróci błąd:

Warunek = "A=A"
Debug.Print Evaluate(Warunek) >> Error 2029

Metoda Evaluate zadziała prawidłowo dla poniższego zapisu warunku opartego o parametry tekstowe, a więc wartości ujęte w dodatkowe wewnętrzne cudzysłowy:

Warunek = """A""=""A"""
Debug.Print Evaluate(Warunek) >>True

Warunek = """A""=""a"""
Debug.Print Evaluate(Warunek) >> True

Warunek = """A""=""Z"""
Debug.Print Evaluate(Warunek) >> False

Pobieranie informacji dot. systemu operacyjnego i parametrów komputera- 2/2

Zgodnie z obietnicą zaprezentuję dwa przykłady wykorzystania WMI z poziomu kodu VBA.

Przykład 1. Pobranie informacji dot. parametrów procesora:

view sourceprint?

01	Sub WMI_Processor_Info()

02

03	Dim objWMI As Object

04	Set objWMI = GetObject("winmgmts:\\.\root\cimv2")

05

06	Dim objQuery As Object

07	Set objQuery = objWMI.ExecQuery("Select * from Win32_Processor")

08

09	Dim objParam As Object

10	For Each objParam In objQuery

11	Debug.Print 1, objParam.Name

12	Debug.Print 2, objParam.Caption

13	Debug.Print 3, objParam.Version

14

Next

15

16	Set objParam = Nothing

17	Set objQuery = Nothing

18	Set objWMI = Nothing

19

20

End Sub

Warto zwrócić uwagę, że w powyższym przykładzie wystarczy podmienić zapytanie SQL aby uzyskać informacje dot. innych parametrów naszego systemu lub komputera.

Przykład 2. Pobranie informacji o otwartych procesach i zamknięcie wszystkich dot. aplikacji MS Word.

view sourceprint?

01	Sub WMI_Processes_Info_Close()

02

03	Dim objWMI As Object

04	Set objWMI = GetObject("winmgmts:\\.\root\cimv2")

05

06	Dim objQuery As Object

07	Set objQuery = objWMI.ExecQuery("Select * from Win32_Process")

08

09	Dim objProces As Object

10	For Each objProces In objQuery

11

12	'pobieramy informacje dot. procesów

13	Debug.Print 1, objProces.Name

14	Debug.Print 2, objProces.Caption

15	Debug.Print 3, objProces.ProcessID

16

17	'zamykamy te, które dot. aplikacji MS Word

18	If objProces.Name = "WINWORD.EXE" Then

19	objProces.Terminate

20

End If

21

Next

22

23	Set objProces = Nothing

24	Set objQuery = Nothing

25	Set objWMI = Nothing

26

27

End Sub

Warto pamiętać, że metoda Terminate zamyka aplikację bez dodatkowych pytań o zapisanie dokumentów. Oczywiście powyższe zadanie można wykonać szybciej odpowiednio konstruując zapytanie SQL co prezentuje poniższy kod VBA:

view sourceprint?

01	Sub WMI_Processes_Close_MSWord()

02

03	Dim objWMI As Object

04	Set objWMI = GetObject("winmgmts:\\.\root\cimv2")

05

06	Dim objQuery As Object

07	Set objQuery = objWMI.ExecQuery( _

08	"Select * from Win32_Process Where Name='WINWORD.EXE'")

09

10	Dim objProces As Object

11	For Each objProces In objQuery

12

13	'zamykamy wszystkie aplikacje zwrócone przez zapytanie SQL

14	objProces.Terminate

15

16

Next

17

18	Set objProces = Nothing

19	Set objQuery = Nothing

20	Set objWMI = Nothing

21

22

End Sub

Pobieranie informacji dot. systemu operacyjnego i parametrów komputera- 1/2

WMI (Windows Management Instrumentation) to zagadnienie, na które trafiłem całkiem przypadkiem na stronie www.stackoverflow.com. Po głębszej analizie możliwości implementacji WMI w kodzie VBA uznałem, że warto podzielić się tą wiedzą z czytelnikami.

Czym jest WMI? Zacytuję za Wikipedią: "WMI (ang. Windows Management Instrumentation) – zestaw protokołów i rozszerzeń systemu Windows umożliwiających zarządzanie i dostęp do zasobów komputera, takich jak adaptery sieciowe, aktualnie otwarte programy, lista procesów, odczyty z wbudowanych czujników temperatury, odczytów woltomierzy itp."

Co w praktyce możemy zrobić dzięki WMI? Oto kilka przykładowych możliwości:

• uzyskać informacje o systemie operacyjnym
• uzyskać listę procesów uruchomionych w systemie, w tym zamykać procesy
• uzyskać parametry wykorzystania dysków
• uzyskać informacje dot. procesora komputera
• uzyskać parametry pamięci RAM
• a nawet zamknąć system operacyjny.

Użycie WMI w kodzie VBA wymagać będzie utworzenia referencji do obiektu:

view sourceprint?

1	Dim objWMI As Object

2	Set objWMI = GetObject( _

3	"winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

lub

view sourceprint?

1	Set objWMI = GetObject("winmgmts:\\.\root\cimv2")

Kolejnym krokiem będzie stworzenie właściwego zapytania SQL skierowanego do odpowiedniej tabeli systemowej. Oto przykłady:

• parametry systemu operacyjnego: "Select * from Win32_OperatingSystem"
• parametry procesora: "Select * from Win32_Processor"
• parametry uruchomionych procesów: "Select * from Win32_Process"
  (powyższy mechanizm pozwala wylistować ID procesu, na bazie którego możemy np. zamknąć określony proces z poziomu kodu VBA)
• Listę plików określonego folderu: "Select * From Cim_DataFile " & _
                 "Where Drive = 'C:' And Path = '\\Instalki\\'") 

Dodatkowe przykłady zastosowań bez problemu można znaleźć w sieci choć w szczególności polecam ten link ze strony Microsoft MSDN.  Szerszy opis WMI znajduje się również na stronie Microsoft MSDN.

W kolejnym poście zaprezentuję dwa gotowe przykłady wykorzystania WMI listujące informacje o parametrach procesora oraz zamykające procesy spełniające określone kryteria.

Wydajność i efektywność 2/2- Mikrooptymalizacja

Wracam do tematów związanych z optymalizacją i tym razem skupię się na drobiazgach, a więc mikroelementach, które potrafią mieć znaczący wpływ na szybkość wykonywanego kodu.

1. Instrukcje warunkowe:

• należy stosować wielostopniową instrukcję If...ElseIf...Else...End If w miejsce instrukcji Select Case
• nie należy stosować instrukcji IIf() gdyż każdorazowo sprawdza warunki dla prawy i fałszu
• używać krótką formę porównawczą If CzyPrawda Then... w miejsce If CzyPrawda=True Then...
• w pracy z tekstem nie używać modułowej deklaracji Option Compare Text. Gdy porównujemy teksty używać instrukcji UCase(), LCase() lub parametrów vbCompareText gdzie dostępne
• sprawdzając, czy zmienna przechowuje tekst używać konstrukcji If Len(tekst) =0 Then... zamiast If tekst ="" Then...
• przed ustawieniem właściwości, gdy nie mamy pewności jej bieżącego stanu, warto sprawdzić jej bieżącą wartość. Odczyt wartości właściwości wykonywany jest szybciej niż zmiana właściwości.

2. Obiekty i zewnętrzne biblioteki:

• nie używać ogólnej deklaracji As Object
• używać techniki wczesnego wiązania
• używać dłuższej techniki wczesnego wiązania, zamiast:

     Dim myObject As New ObjectType
        stosować:
  Dim myObject As ObjectType
  Set myObject = New ObjectType

• wykorzystywać instrukcję With...End With

3. Inne zagadnienia:

• co oczywiste lecz warte przypomnienia- zawsze stosujemy właściwy typ zmiennych, typ Variant nie powinien być wykorzystywany
• używać funkcji konwersji typów (CInt, CDbl, CSng, itp) przy przekazywaniu wartości różnych typów pomiędzy zmiennymi
• operacje dzielenia dla licz całkowitych wykonywać z odwrotnym operatorem dzielenia A\B zamiast A/B
• w pracy ze zmiennymi tekstowymi używać funkcji tekstowych: Mid$, Left$, Right$
• wykonując szereg operacji na zakresie warto przenieść zakres do tablicy i dalsze analizy wykonywać na tablicy
• w procesie pobierania i zwracania wartości pomiędzy komórkami arkusza i zmiennymi stosować zmienne typu Double dzięki czemu unikniemy procesu konwersji, formatowania, zaokrąglania, itp.

Na koniec przypomnę najważniejszą z technik, o której wspomniałem w poprzednim poście poświęconym zagadnieniom optymalizacji- nie zapominajmy o A w VBA...

Przy okazji zapraszam na prowadzone przez nas szkolenia z zakresu programowania w VBA, które organizujemy w Warszawie, Krakowie i Wrocławiu. W czasie naszych kursów VBA prezentujemy szereg technik związanych z efektywnym programowaniem.

Wydajność i efektywność 1/2- Makrooptymalizacja

Zagadnienia dot. optymalizacji i efektywności programów w VBA podzielone zostaną na dwie części zgodnie z przyjętą gdzieniegdzie systematyką:

1. makrooptymalizacja dot. zagadnień szerszych, dużych, właściwego doboru narzędzi, techniki, rozwiązania czy algorytmu,
2. mikrooptymalizacja dot. szeregu drobnych detali w kodzie, które w całościowym ujęciu mają istotny wpływ na szybkość wykonywania procedury.

Muszę jednak od razu poczynić zastrzeżenia zanim przejdę do prezentacji krótkich opisów w/w technik. Optymalizacja jest tematem 'żywym', co jakiś czas zdarza się, że pojawiają się nowe sugestie, tak więc nie należy traktować niniejszych postów jako zamkniętą listę działań optymalizacyjnych. I drugie zastrzeżenie- niektóre z technik można uznać za sporne, a więc takie, które nie w każdym przypadku przekładają się na widoczną poprawę efektywności.

Na początek kilka zagadnień dot. makrooptymalizacji:

1. W aspekcie pracy z pętlami, tablicami i kolekcjami:

• unikać zagnieżdżonych pętli, stosować techniki wyjścia z pętli (Exit For, Exit Do)
• w procesach porównywania posortować porównywane kolekcje/tablice
• w pracy z kolekcjami stosować pętle For Each...Next
• w pracy z tablicami stosować pętle For i...Next
• w pracy z tablicami dynamicznymi stosować płynną redefinicję wymiaru

2. W pracy z obiektami:

• unikać aktywowania (.Activate) i zaznaczanie (.Select)
• wyłączać odświeżanie ekranu (Application.ScreenUpdating = False)
• wyłączać (gdy tylko jest to bezpieczne) standardowe okna potwierdzeń (Application.DisplayAllerts = False)
• wyłączać przeliczanie formuł, szczególnie gdy kod VBA ingeruje w strukturę wartości i formuł w skoroszycie (Application.Calculation = xlManual)

3. Inne aspekty:

• unikać przesadnego stosowania zmiennych publicznych
• gdy możliwe i dopuszczalne zerować wartości zmiennych publicznych instrukcją End
• nie tworzyć własnych funkcji jeżeli podobna funkcja istniej w zbiorze funkcji VBA lub WorksheetFunction

A na koniec chyba najważniejsza z technik optymalizacji- nie zapominać o A w VBA... innymi słowy, jeżeli można coś wykonać z wykorzystaniem dostępnych, gotowych narzędzi w aplikacji to zróbmy to w miejsce tworzenia nowego kodu VBA.

VBScript vs. VBA w teorii i przykładach (3/3)

Trzeci wpis poświęcony pracy z językiem skryptowym VBScript dotyczyć będzie analizy konkretnego przykładu. Zacznijmy więc od opisu samego problemu...

W czasie prowadzonych szkoleń z zakresu VBA dla Excela czasem słyszę pytanie dot. możliwości uruchomienia określonego makra o określonej godzinie. Jedną z technik pozwalającą na wykonanie takiej operacji jest wykorzystanie instrukcji Application.OnTime. Warunek- aplikacja Excela musi pozostać uruchomiona do godziny, o której makro ma być uruchomione. Co jednak zrobić w sytuacji gdy Excel jest wyłączony? Co zrobić jeżeli chcemy uruchomić makro VBA dla aplikacji MS Word lub MS PowerPoint w sytuacji, gdy obie te aplikacje nie dysponują instrukcją Application.OnTime? W każdym z tych wariantów możemy sięgnąć po systemowego Menadżera Zadań i skrypt VBS. Prześledźmy kolejne niezbędne działania.

Krok 1. Utwórzmy testowy plik XLSM z dwoma prostymi makrami, które umożliwią nam wykonanie testu także w wariancie z przekazaniem parametru do wywoływanego makra.

view sourceprint?

1	Sub VBS_Test()

2	MsgBox "Wywołanie z poziomu VBS o godzinie: " & Time

3

End Sub

4

5	Sub VBS_Test_Arg(Parametr)

6	MsgBox "Wywołanie z poziomu VBS z parametrem: " & Parametr

7

End Sub

Krok 2. Tworzymy skrypt VBS i zapisujemy go w pliku z takim właśnie rozszerzeniem. Poniżej przykładowy kod wraz z komentarzami wyjaśniającymi poszczególne operację. Proszę zwrócić uwagę na olbrzymie podobieństwo do języka VBA.

view sourceprint?

01	'tworzymy zmienną reprezentującą aplikacje Excela

02

dim EXL

03	'uruchamiamy instancję Ecxcela

04	set EXL = CreateObject("Excel.Application")

05	'opcjonalnie wyświetlamy aplikację-

06	'krok nie jest wymagany jeżeli makro ma działać w tle

07	EXL.Visible = true

08

09	'otwieramy skoroszyt z uprzednio utworzonymi makrami

10	EXL.Workbooks.Open "c:\PROJEKT VBA\SZKOLENIA\VBS_Test_Arg.xlsm"

11	'wywołujemy pierwsze makro bez parametru

12	EXL.Run "VBS_Test"

13	'wywołujemy drugie makro z przekazaniem parametrem

14	EXL.Run "VBS_Test_Arg", "Tekst do przekazania"

15

16	'opcjonalnie wyłączamy aplikację i niszczymy naszą zmienną

17

EXL.Quit

18	Set EXL = Nothing

Krok 3. Pozostało nam już tylko powiązać nasz skrypt z godziną wywołania wykorzystując do tego celu systemowego Menadżera Zadań.

• uruchamiamy Menadżera Zadań z Menu >> Programy >> Akcesoria >>Narzędzia systemowe >> Harmonogram zadań (dla Windows 7)
• tworzymy nowe zadanie wskazując jako akcję do wykonania nasz utworzony plik VBS (poniżej odpowiednie okno prezentujące ten krok) i ustawiamy wszystkie niezbędne parametry zgodnie z kreatorem tworzenia zadań
  (operacja ta jest relatywnie łatwa i intuicyjna więc nie będę poświęcał jej więcej czasu)

O określonej godzinie, zgodnie z parametrami ustawionymi w systemowym Harmonogramie Zadań, uruchomiony zostanie skrypt VBS. W wyniku jego działania otwarta zostanie nowa instancja Excela i wywołane zostaną dwa makra. Po zakończeniu pracy makr zakończony zostanie także skrypt VBA w wyniku czego Excel zostanie zamknięty.

VBScript vs. VBA w teorii i przykładach (2/3)

Kontynuując opis języka VBScript chciałbym spojrzeć na wybrane podobieństwa i różnice pomiędzy tym językiem a VBA.

Zacznijmy może od zasadniczej różnicy- VBS nie działa w zamkniętym środowisku powiązanym z określoną aplikacją. Obszarem jego funkcjonowania jest, w naszym przypadku, system Windows, a podstawowym edytorem będzie zapewne zwykły notatnik. Pliki z kodem VBS są plikami tekstowymi, wykonywalnymi (z rozszerzeniem VBS). Język jest na tyle uniwersalny, że działa w środowisku Windows XP, Windows 7 czy też Windows 8.

Warto też zwrócić uwagę na wybrane cechy języka VBS, a w szczególności: problem ze stosowaniem polskich liter w nazwach własnych, brak profesjonalnego edytora, który ułatwiałby edycję kodu, dowolność stosowania wielkich i małych liter w procesie edycji skryptu.

A teraz zwróćmy uwagę na podobieństwa:

1. Każdy plik VBS tworzy niejako procedurę główną w ramach której mogą funkcjonować opcjonalnie podprogramy (Sub...End Sub) oraz funkcje (Function...End Function). Obie struktury wywołujemy zgodnie z technikami obowiązującymi w VBA.

2. Zmienne deklarujemy tymi samymi instrukcjami jak w języku VBA: Dim, Public, Private. Co ważne, nie deklarujemy typu zmiennej choć w procesie kompilacji każda zmienna jest przypisywana do określonego typu jaki znamy z VBA (Integer, Long, String, itp). Deklaracja zmiennych nie jest obowiązkowa, chyba że zastosujemy polecenie Option Explicit.

3. Możemy tworzyć tablice korzystając z funkcji Array oraz tablice dynamiczne. Funkcja ReDim funkcjonuje wg standardów znanych ze środowiska VBA.

4. Do dyspozycji mamy dziesiątki funkcji matematycznych, konwersji typów zmiennych, konwersji tekstu, daty, itp. W zdecydowanej większości są to funkcje znane ze środowiska VBA.

5. Istnieje możliwość stworzenia referencji do bibliotek innych aplikacji i wykonywanie operacji na strukturze obiektowej danej aplikacji. Do punktu tego zaliczę więc interakcję z aplikacjami pakietu Microsoft Office, możliwość zarządzania plikami dzięki bibliotece Microsoft Scripting Runtime, itp.

6. Do dyspozycji mamy kilka rodzai pętli (For...Next, For Each...Next, Do...Loop), instrukcje warunkowe (If...End If, Select Case...End Select).

7. W zakresie zaawansowanych technik możemy wykorzystać własne klasy, technikę pracy z wyrażeniami regularnymi RegEx czy też słownikiem Dictionary.

Wszystkich zainteresowanych szerszym zastosowaniem języka VBScript, w tym składnią oraz zestawem instrukcji i funkcji odsyłam do szeregu informacji znajdujących się w sieci. W szczególności zaś polecam następujące linki:
VBScript by MSDN
Microsoft Script Center

W następnym artykule zaprezentuję technikę, która pozwala na wywołanie makra stworzonego w VBA z poziomu języka VBScript. Dodatkowo działanie to wyzwolimy z poziomu Menadżera Zadań systemu Windows.

Na koniec chciałbym zademonstrować zestaw przykładowych instrukcji i działań jakie można wykonać z pomocą języka VBScript. Poniższy kod w całości proszę skopiować i zapisać w dowolnym pliku tekstowym z rozszerzeniem VBS. Następnie proszę uruchomić utworzony plik.

view sourceprint?

01	'proszę zapisać cały skrypt w pliku *.vbs

02	'następnie proszę zapisać i uruchomić

03	msgbox "Witam w przykładowym skrypcie VBScript"

04

05

'funkcja

06	function Test(a, b, h)

07	Test = 1/2 * (a+b) * h

08	end function

09

10	'podprogram subprocedura

11	sub Obliczenie_Pole_trapezu

12	'przykłady deklaracji zmiennych

13

dim A

14

dim B

15

dim C

16	A = 2:b = 2

17	'pobranie danych od użytkownika

18	c = inputbox("Podaj wysokość trapeza dla podstawy 2 i 2")

19	msgbox Test(a, b, c)

20	'pobranie informacji o typie zmiennej A

21	msgbox Typename(a)

22

End sub

23

24	'podprogram ze parametrem wejściowym

25	sub Praca_z_tekstem(Tekst)

26	msgbox "Tekst z wielkiej litery: " & ucase(tekst)

27

end sub

28

29	'wywołanie funkcji

30	msgbox Test(2,3,4)

31	'wywołanie sub-procedur

32	obliczenie_pole_trapezu

33	'wywołanie sub-procedury z parametrem

34	praca_z_tekstem "to jest tekst przykładowy"

35	'wywołanie sub-procedury (znajdującej się poniżej)

36	procedura_z_petla

37

38	'tablica dynamiczna

39

dim TBL()

40	Redim TBL(10)

41	TBL(0) = 100

42	TBL(1) = 200

43	'zwrócenie elementów tablicy

44	MsgBox Tbl(0) & " " & Tbl(1)

45

46	sub Procedura_z_Petla

47	dim i, txt

48	'prosta pętla For...Next

49	for i=1 to 10

50	txt = txt & chr(10) & i

51

next

52

53	msgbox txt

54

end sub

VBScript vs. VBA w teorii i przykładach (1/3)

Zacznę od prostego i jednoznacznego określenia: VBScript (VBS) to nie to samo co VBA, to dwa różne języki pomimo tego, iż mają ze sobą bardzo wiele wspólnego. Do wspólnych elementów należy zaliczyć szczególnie szereg elementów składni, instrukcji, pętli, a większość instrukcji VBScript znaleźć można jednocześnie w VBA. Należy wyraźnie jednak rozróżniać oba języki.

Niniejszy wpis poświęcony zostanie teorii i definicji dot. języka VBS oraz zaprezentowany zostanie pierwszy przykładowy kod tego języka. Kolejny post w temacie dotyczyć będzie zagadnień związanych z instrukcjami VBS, podobieństwem i różnicami w relacji do VBA, stworzymy bardziej rozbudowaną procedurę VBS. W trzeciej części zaprezentuję techniki wiązania VBS z VBA- dokładnie w tym, a nie przeciwnym kierunku. Pokażę jak z poziomu VBS można wywołać makro stworzone w Excelu oraz jak uruchomić makro VBA o określonej godzinie wykorzystując systemowego Menadżera Zadań, VBS i VBA.

VBS (Microsoft Visual Basic Scripting Edition) to jezyk dość stary. Został stworzony do współpracy z technologią ASP jako alternatywa dla języka JavaScript i jak wiadomo w tym zakresie nie przyjął się na większą skalę. VBS wykorzystywany jest przez formularze aplikacji Microsoft Outlook także w najnowszych wersjach. Poza tym wspomaga działania na plikach i folderach będąc zintegrowany z biblioteką Windows Script Hosting (dość powszechnie używaną z poziomu języka VBA). Warto pamiętać również, że VBScript nie jest obecnie rozwijany w postaci samodzielnego języka, a jedynie jako element języka ASP.

W jaki sposób tworzymy skrypt języka VBS? Wystarczy kilka zaledwie kroków aby skonstruować samowykonywalny plik wykonujący prosty skrypt i wywoływany z poziomu systemu Windows.

1. Tworzymy plik tekstowy z rozszerzeniem vbs, np. QuickTest.vbs

2. Przechodzimy do edycji pliku i dodajemy następujących kilka linii kodu:

view sourceprint?

01	'wariant 1 okna z komunikatem

02	msgbox "Hello World!"

03

04	'wariant 2 okna z komunikatem

05	msgbox "10 razy 10 to razem " & 10*10

06

07	'wykorzytanie zmiennych i funkcji czasu

08

dim czas

09	czas = Now

10	msgbox "Aktualna data i godzina to: " & czas

3. Zapisujemy i zamykamy utworzony plik VBS

4. Uruchamiamy plik przez dwukrotne kliknięcie lub wpisanie nazwy pliku w pasku poleceń. Poniżej pierwsze dwa okna, które ukarzą się w wyniku działania skryptu.

Na koniec kilka szybkich spostrzeżeń dot. zaprezentowanego kodu. Proszę zwrócić uwagę, że VBScript:

a) pozwala na interakcję w środowisku Windows wykonaną przez wyświetlenie okien
b) umożliwia wykonywanie obliczeń
c) pracuje ze zmiennymi w sposób podobny do VBA
d) posiada zestaw funkcji  znanych ze środowiska VBA.

Mam nadzieję, że to zachęci czytelników do zapoznania się z kolejnym wpisem dot. VBScript, który zostanie opublikowany już za kilka dni.