Niemodalne okienko MsgBox

Standardowe okienko MsgBox występujące w języku VBA ma tą (niekiedy uciążliwą) cechę, że jest okienkiem modalnym. Modalnym czyli takim, które należy zamknąć aby móc powrócić do pracy z aplikacją znajdującą się w tle. Jak więc wywołać proste okienko z przyciskami Tak/Nie/Anuluj w trybie niemodalnym? Jest kilka możliwych wariantów spośród których skupię się szczegółowo tylko na jednym.

Wariant A. Należy stworzyć okno UserForm, a następnie wyświetlić go w trybie niemodalnym:

view sourceprint?

1	UserForm1.Show vbModeLess

Wariant B. Wykorzystać okienko znajdujące się w zewnętrznej bibliotece Windows Script Hosting. Poniżej prezentuję kompletny kod, który wyświetla okienko z trzema przyciskami oraz wykonuje akcję zależnie od wybranego przycisku.

view sourceprint?

01	Sub Okno_Popup()

02

03	Dim wsMsgBox As Object

04	Set wsMsgBox = CreateObject("WScript.Shell")

05

06	Dim mbResult As Integer

07

08	'czas oczekiwania, opcjonalny, _

09	niestety nie zawsze działa!

10	Dim iTime As Integer

11

iTime = 2

12

13	'wyświetlamy okienko określając jego parametry

14	mbResult = wsMsgBox.Popup("Okienko by Windows Scripting.", _

15	iTime, "Okienko niemodalne MsgBox!", 3)

16

17	Select Case mbResult

18

Case 6

19	Debug.Print "klawisz yes/tak"

20

Case 7

21	Debug.Print "klawisz no/nie"

22

Case Else

23	Debug.Print "pozostałe"

24	End Select

25

26

End Sub

Rozwiązanie to posiada dodatkową opcję, która pozwala określić czas, przez jaki okienko będzie widoczne. Niestety, parametr ten nie działa stabilnie. W praktyce tylko ustawienie czasu oczekiwania na 1 sekundę gwarantuje ukrycie okna. W przypadku dłuższego czasu oczekiwania okienko i tak oczekuje na akcję za strony użytkownika. Być może błąd ten zależny jest od wersji oprogramowania nie mniej nie udało mi się odnaleźć informacji na temat rozwiązania tego problemu.

Dodatkowe informacje na temat okna Popup z biblioteki Windows Scripting znajdziecie Państwo pod tym linkiem.

Losowy dokument w Wordzie

Tworząc szereg rozwiązań w VBA dla aplikacji MS Word często testuję określone techniki korzystając z przykładowego 'losowego' dokumentu. Dokument taki można stworzyć w bardzo prosty sposób wykorzystując ukryte polecenia aplikacji MS Word. W tym celu proszę utworzyć nowy pusty dokument w Word'zie i wykorzystać jedną z poniższych technik.

Wariant 1. Aby stworzyć losowy tekst 'Lorem ipsum' sięgamy po polecenie:

=Lorem()
które należy wpisać w pierwszym linii dokumentu i wcisnąć enter. W wyniku otrzymamy trzy paragrafy losowego tekstu.

Rozwiązanie to posiada możliwość parametryzowania:

=Lorem(10) 
utworzy 10 paragrafów z losową ilością zdań

=Lorem(10,3)
utworzy 10 paragrafów i po trzy zdania w każdym paragrafie.

Wariant 2. Jeżeli tekst, z których chcemy pracować ma być w języku polskim sięgamy wtedy po polecenie:

=Rand()
które działa podobnie jak prezentowane powyżej polecenie Lorem. Dostępne więc będą wiarianty:

=Rand(10)
=Rand(10,3)
które odpowiednio definiują ilość paragrafów i zdań w każdym paragrafie.

Technika ta przyda się w jednym z kolejnych wpisów, który wkrótce pojawi się w sekcji Strefa Wiedzy. Strefa Wiedzy prowadzona jest jako rozszerzenie dla kursów VBA organizowanych przez naszą firmę w Krakowie, Warszawie i Wrocławiu.

Metoda Find w aplikacji MS Word- Przykład 1

Metoda .Find to jedno z bardziej przydatnych narzędzi nie tylko w Wordzie, ale także w Excelu (o czym wkrótce napiszę kilka postów). Ogólna zasada działania metody jest prosta- Znajdź >> Zaznacz. O wiele częściej sięgamy po to rozwiązanie w konfiguracji Znajdź >> Zamień. Tak czy inaczej w pracy z metodą .Find warto zawsze:

a) skorzystać z rejestratora makr
b) wykorzystać umiejętnie znaki specjalne (Wildcards) dostępne w pracy z tą metodą.

Od czasu do czasu przedstawię proste przykłady pracy z techniką Znajdź >> Zamień. Na początek następujące zadanie:

Dokument składa się z szeregu 12-to cyfrowych liczb umieszczonych w kolejnych paragrafach. Zadanie polega na wstawieniu pojedynczych cudzysłowów na początku i końcu każdego ciągu oraz na umieszczeniu wszystkich wpisów w jednej linii. Poniższy zrzut ekran pokazuje sytuację przed i po. Nieprzypadkowo na zrzucie ekranu dostrzec można również znaki podziału paragrafu, które będą kluczowe w rozwiązaniu problemu.

Podejście A. Teoretycznie możemy wykorzystać pętle i przeszukać cały dokument na okoliczność ciągów znaku uprzednio częściowo dokonawszy zamiany znaku paragrafu na przecinek. Rozwiązanie choć skuteczne to nieefektywne  i nie będzie tu prezentowane.

Podejście B. Wykorzystując dwukrotnie metodę .Find >> .Replace osiągniemy nasz cel. Proszę zwrócić uwagę na parametr .Wildcards ustawiony odmiennie w pierwszej i drugiej części kodu.

view sourceprint?

01	Sub Podejście_B()

02

03	With Selection.Find

04	.Text = "^p"

05	.Replacement.Text = ","

06	.MatchWildcards = False

07	.Execute Replace:=wdReplaceAll

08

09	.Text = "([0-9]{12})"

10	.MatchWildcards = True

11	.Replacement.Text = "'\1'"

12	.Execute Replace:=wdReplaceAll

13

14

End With

15

16

End Sub

Podejście C. Kluczowe w tym wariancie jest zastąpienie symbolu znaku paragrafu. W miejsce '^p' wstawiamy inny odpowiednik nowego paragrafu wyrażony jako '^13', który nie powoduje kolizji z ustawionym naprzemiennie parametrem .Wildcards w powyższym przykładzie. Dzięki temu nasze ostateczne rozwiązanie staje się jeszcze bardziej kompaktowe.

view sourceprint?

01	Sub Podejście_C()

02

03	With Selection.Find

04	.Text = "([0-9]{10})(^13)"

05	.Replacement.Text = "'\1',"

06	.MatchWildcards = True

07	.Execute Replace:=wdReplaceAll

08

End With

09

10

End Sub

Prowadząc szkolenie z VBA zawsze staram się prezentować rozwiązania optymalne, choć często wychodzę od rozwiązań naturalnych-intuicyjnych by następnie móc zaprezentować ewolucję rozwiązania podobnie jak ma to miejsce powyżej.

Podręczna paleta kolorów wypełnienia

Wyobraźmy sobie sytuację, w której chcemy ułatwić użytkownikowi nanoszenie kolorów w określonym obszarze arkusza. Odpowiednie kolory mogą oznaczać status danego produktu, zamówienia, tworzyć kolorystyczną informacje o postępie zdarzenia, itp. Ot np. spójrzmy na poniższy układ prostej tabeli wraz z wyświetlonym szybkim menu podręcznym przyspieszającym kolorowanie.

Poniżej w kilku krokach prezentuję technikę tworzenia niniejszego rozwiązania.

Krok 1. Procedura tworząca i wyświetlająca menu podręczne z paletą kolorystyczną. Procedura ta wykorzystuje tradycyjne techniki tworzenia menu oparte o obiekty/kolekcje CommandBar/CommandBars.

view sourceprint?

01	Sub RightClickMenu_Utwórz()

02

03	Dim tmpCB As CommandBar

04	Dim tmpCBpop As CommandBarPopup

05

06	'na wszelki wypadek

07	RightClickMenu_Usuń

08

09	Set tmpCB = Application.CommandBars.Add("KolorMenu", msoBarPopup, , True)

10	Set tmpCBpop = tmpCB.Controls.Add(ID:=1691)

11	tmpCB.ShowPopup

12

13	'usuwamy po wykonaniu operacji

14	RightClickMenu_Usuń

15

End Sub

Krok 2. Jak łatwo zauważyć w powyższej procedurze tworzone menu jest tymczasowe. Niezbędna jest więc procedura usuwania paska zaraz po tym gdy zostanie wykorzystany.

view sourceprint?

1	Sub RightClickMenu_Usuń()

2	On Error Resume Next

3	Application.CommandBars("KolorMenu").Delete

4

End Sub

Krok 3. Wykorzystamy obsługę zdarzenia BeforeRightClick w celu wyświetlenia menu tylko w kolumnie C odpowiedniego arkusza. Co ważne, poniższe rozwiązanie wyświetli nasze nowe menu po kliknięciu prawym klawiszem myszy. Nadal pozostawimy użytkownikowi dostęp do standardowego menu podręcznego o ile wybierze na klawiaturze konfigurację Shift + F10.

view sourceprint?

1	Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

2	If Target.Column = 3 Then

3	Cancel = True

4	RightClickMenu_Utwórz

5

End If

6

End Sub

I na koniec mała wskazówka- wykorzystana w kroku 1 metoda .ShowPopup posiada dodatkowe, opcjonalne parametry, które umożliwiają wskazanie miejsca, w którym ma zostać wyświetlone nasze menu podręczne.
Tworzenie interakcji i rozwiązań ułatwiających pracę z Excelem (i innymi aplikacjami Office) jest ważną umiejętnością każdego programisty co staram się często podkreślać prowadząc kurs z programowania w VBA.

Przechowywanie wartości zmiennych po zamknięciu pliku

Szukając odpowiedni na pytanie jak przechować zmienne po zamknięciu pliku przychodzi mi do głowy szereg pomysłów. Na początek wymienię kilka z nich- wykorzystując komórkę w arkuszu (tylko Excel), korzystając z właściwości dokumentu, korzystając z kolekcji Variables (tylko Word), korzystając z rejestru komputera (tylko lokalnie, nie przenoszone z plikiem).

Relatywnie ciekawą techniką jest jednak wykorzystanie instrukcji Put (dla zapisu) i Get (dla odczytu) z zapisem do pliku tekstowego. Rozwiązanie to mam kilka dodatkowych zalet:
1.  można szybko i skutecznie przechować wartości tablic, co nie jest możliwe w rozwiązaniach alternatywnych,
2. utworzony plik tekstowy sprawia wrażenie zaszyfrowanego, nie każdy będzie w stanie odczytać jego zawartość.

Skoro są zalety to i zazwyczaj istnieją wady rozwiązania. Otóż i owszem- ograniczona wielkość zmiennej- dla tablicy będzie to niewiele ponad 3000 rekordów.

Jak działa taki mechanizm? Jak wygląda kod zapisu i odczytu?

Wyobraźmy sobie taką oto sytuację, że obszar tabeli Excela A1:E7 chcemy zapisać w postaci zmiennej tablicowej i przesłać w postaci pliku tekstowego do innej osoby.

W celu zapisu posłużymy się takim poniższym przykładowym kodem. Dodatkowe parametry zostały opisane w postaci komentarzy wewnątrz kodu.

view sourceprint?

01	Sub Write_Array_To_File()

02

03	Dim Plik As String

04

05	Dim fileNum As Integer

06

07	Dim tblRange() As Variant

08	tblRange = Range("A1:E7").Value

09

10	'nasz plik wynikowy

11	Plik = "Array.To.File.VBA.bin"

12	'gdy plik istnieje to uprzednio go skasujemy _

13	wymagana obsługa błędów na okoliczność gdy plik nie istnieje

14	On Error Resume Next

15

Kill Plik

16	On Error GoTo 0

17

18	'procedura tworzenia pliku

19	fileNum = FreeFile

20

21	Dim arrLen As Long

22	'parametr długości wg pomocy z MSDN: _

23	=2 + ilość wymiarów * 8 + _

24	wielkość wymiaru 1 * wielkość wymiaru 2 * ilość wymiarów

25	'w praktyce wartość ta jest za mała i zwraca błąd, dlatego _

26	eksperymentalnie została dodana dodatkowa wartość

27	'opcjonalnie można ustawić wartość na poziom max = 32767

28	arrLen = (2 + 2 * 8) + _

29	(UBound(tblRange, 1) * UBound(tblRange, 2) * 2) + 255

30

31	'otwieramy plik do zapisu i wywołujemy instrukcję Put

32	Open Plik For Random As #fileNum Len = arrLen

33	Put #fileNum, 1, tblRange

34	Close #fileNum

35

36

End Sub

Jeżeli operacja powiodła się w domyślnej lokalizacji znajdziemy plik o nazwie "Array.To.File.VBA.bin". Próba otwarcia tego pliku w prostych edytorach tekstu da nam następujący lub podobny efekt:

Plik jest niewielki i jednocześnie sprawia wrażenie jakoby zawartość była zaszyfrowana. Teraz możemy przesłać plik do odbiorcy.

W jaki sposób możemy odczytać nasz plik. Wystarczy uruchomić poniższą procedurę aby wartość naszej tabeli została wprowadzona do arkusza Excel począwszy od aktywnie zaznaczonej komórki. (Dodatkowe uwagi dot. kodu w postaci komentarzy poniżej)

view sourceprint?

01	Sub Read_Array_From_File()

02

03	Dim Plik As String

04	Dim fileNum As Integer

05	Dim tblRange() As Variant

06

07	'plik musi znajdować się w domyślnej lokalizacji

08	Plik = "Array.To.File.VBA.bin"

09	fileNum = FreeFile

10

11	Dim lenAAA As Integer

12	'nie znamy wielkości tabeli- ustawiamy max wartość parametru

13	lenAAA = 32767

14

15	'otwarcie pliku i odczyt zwaratości do zmiennej

16	Open Plik For Random As #fileNum Len = lenAAA

17	Get #fileNum, 1, tblRange

18	Close #fileNum

19

20	'przeniesienie tabeli do arkusza względem aktywnej komórki

21	ActiveCell.Resize(UBound(tblRange, 1), UBound(tblRange, 2)) = tblRange

22

End Sub

Przydatne linki referencyjne do strony Microsoft MSDN:

Put Statement
Get Statement