VBA nie posiada kontrolki MSForms z kalendarzem, która działałaby dla każdej wersji programu Excel. Niestandardowy DTPicker potrzebuje wersji 32-bitowej – w wersji 64-bitowej kontrolka zniknie z UserForma. Jak zatem stworzyć kalendarz na formularzu, który będzie działać niezależnie od wersji Excela? Jak wybrać datę i przekazać ją do arkusza? W tym tekście opiszę pewien sposób.
Funkcja DATA – pewniak dla Excela
W Excelu daty są liczbami. Możemy więc dokonywać na nich pewnych obliczeń np.
- Określić liczbę dni roboczych jakie upłynęły od daty A do daty B.
- Sprawdzić numer dnia tygodnia lub całego roku.
- Wyciągnąć szczegółowe informacje z daty urodzenia.
Wprowadzenie daty do komórki może sprawić pewien kłopot. Dlaczego? Ponieważ wymaga od nas znajomości tego, jak Excel rozpoznaje daty. Musimy wiedzieć jak wpisać datę, aby Excel potraktował ją właśnie jak datę, a nie tekst.
Problem staje się większy, gdy korzystamy z różnych wersji językowych Excela. Wpisując ręcznie np. 3-05-2020r. jedna wersja językowa może zinterpretować ten wpis jako 3-maja, natomiast inna jako 5-marca.
Antidotum na te bolączki to podanie daty za pomocą wbudowanej funkcji arkuszowej DATA. Posiada ona trzy argumenty liczbowe (rok, miesiąc, dzień) i korzystając z tego rozwiązania mamy pewność, że nic złego się nie stanie.
Czepialscy mogą powiedzieć, że korzystając z funkcji DATA wykonujemy nadmiarową robotę. Musimy bowiem specjalnie wywołać funkcję, aby wskazać interesującą nas datę. Rozwiązaniem bardziej przyjaznym z pewnością byłoby wyskakujące okienko z kalendarzem.
Problem kalendarza w VBA
Jak wygląda sytuacja w przypadku VBA? Są dwie wiadomości: dobra i zła.
Dobra jest taka, że istnieją kontrolki, które pozwalają taki zgrabny kalendarz zamieścić na formularzu np. Microsoft Calendar Control czy Month View.
Zła wiadomość jest natomiast taka, że są to kontrolki niestandardowe i wymagają dodatkowej instalacji i rejestracji w systemie.
W praktyce ich stosowanie jest dosyć kłopotliwe, ponieważ przekazując aplikację Klientowi – nie wiemy czy będzie ona zawsze u niego działać. Nie możemy dać mu takiej gwarancji. Jest to uzależnione od wersji bitowej pakietu Office. Migracja z wersji 32-bit do wersji 64-bit spowoduje, że kontrolka zniknie z formularza i aplikacja przestanie działać.
Z reguły nie ma tu miejsca na kompromisy i szansa na to, że Klient powróci do wersji 32-bit jest niewielka – w przypadku korporacji praktycznie zerowa.
Dla świętego spokoju – swojego i Klienta – musimy szukać rozwiązań pewnych i bezpiecznych. Mam tu na myśli takie, które będą działały bez względu na wersję Excela.
W ten sposób powstają mniej lub bardziej udane protezy – oparte na standardowych kontrolkach MsForms.
Moja proteza kalendarza
Postanowiłem zmierzyć się z tym wyzwaniem tworząc trzy pola typu ComboBox. Takie rozwiązanie widziałem już wcześniej w rewelacyjnym dodatku ASAP-Utilities.
Trzy pola typu ComboBox
Mój kalendarz na formularzu wygląda w ten sposób.

Kod VBA
Obszerny kod, który odpowiada za ten formularz wygląda tak.
Moduł formularza
1 2 3 4 5 6 7 8 |
Private Const msMODUL As String = "UKalendarz" Dim m_bOK As Boolean ' Zapamiętuje, czy użytkownik nacisnął OK lub Anuluj. Dim m_iDzien As Integer Dim m_iMiesiac As Integer Dim m_iRok As Integer |
1 2 3 4 5 6 7 8 |
Property Get Dzien() As Integer: Dzien = m_iDzien: End Property Property Get Miesiac() As Integer: Miesiac = m_iMiesiac: End Property Property Get Rok() As Integer: Rok = m_iRok: End Property Property Get DataWprowadzenia() As Date: DataWprowadzenia = DateSerial(Rok, Miesiac, Dzien) End Property |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Private Sub UserForm_Initialize() 'Zaczytaj do pól kombi dane z kolumn wksListy 1 Me.cmbRok.List = wksListy.Range("Rok").Value 2 Me.cmbMiesiac.List = wksListy.Range("Miesiac").Value 'Zaczytaj dane do zmiennych poziomu modułu - na podstawie dzisiejszej daty 3 m_iRok = VBA.Year(VBA.Now) 4 m_iMiesiac = VBA.Month(VBA.Now) 5 m_iDzien = VBA.Day(VBA.Now) '----- Ustaw domyślne wartości pól kombi 6 Me.cmbRok.ListIndex = WorksheetFunction.Match( _ m_iRok, wksListy.Range("Rok"), 0) - 1 'cmbRok_Click() 7 Me.cmbMiesiac.ListIndex = m_iMiesiac - 1 'cmbMiesiac_Click) End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Private Sub cmbRok_Click() 1 m_iRok = Val(Me.cmbRok.Text) 2 ZaladujListeDni m_iMiesiac, m_iRok End Sub Private Sub cmbMiesiac_Click() 1 m_iMiesiac = Me.cmbMiesiac.ListIndex + 1 2 ZaladujListeDni m_iMiesiac, m_iRok End Sub Private Sub cmbDzien_Click() m_iDzien = Val(Me.cmbDzien.Text) End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Private Sub ZaladujListeDni(ByVal iMojmc As Integer, ByVal iMojrok As Integer) Dim iOstDzien As Integer 'Ostatni dzień danego miesiąca Dim x As Integer 'Licznik pętli 'Sprawdź ostatni dzień miesiąca 1 iOstDzien = Day(DateSerial(iMojrok, iMojmc + 1, 1) - 1) 'Aktualizuj dane dla cmbDzien 2 Me.cmbDzien.Clear ' Nie uruchamia zdarzenia Click(), uruchamia Change() 3 For x = 1 To iOstDzien 4 Me.cmbDzien.AddItem x 5 Next x 'Uruchamia cmbDzien_Click() 6 Me.cmbDzien.ListIndex = m_iDzien - 1 End Sub |
1 2 3 4 5 6 7 8 9 10 |
' Powoduje, że znak [x] zachowuje się tak samo, jak przycisk Anuluj. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 1 If CloseMode = vbFormControlMenu Then 2 m_bOK = False 3 Me.Hide 4 Cancel = True 5 End If End Sub |
1 2 3 4 5 6 7 |
' Obsługa przycisku OK. Private Sub cmdOK_Click() 1 m_bOK = True 2 Me.Hide End Sub |
1 2 3 4 5 6 7 |
' Obsługa przycisku Zamknij Private Sub cmdZamknij_Click() 1 m_bOK = False 2 Me.Hide End Sub |
Kod dodający datę do komórki
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Option Explicit Public Sub DodajDateDoKomorki() Dim clsKalendarz As UKalendarz Dim bCzyOk As Boolean Dim dtMojaData As Date ' Zdefiniuj obiekt 1 Set clsKalendarz = New UKalendarz ' Wyświetl formularz 2 clsKalendarz.Show vbModal ' Pobierz dane 3 With clsKalendarz 4 bCzyOk = .OK 5 dtMojaData = .DataWprowadzenia 6 End With ' Wyloguj 7 Unload clsKalendarz 'Dodaj do aktywnej komórki 8 If bCzyOk Then 9 If IsDate(dtMojaData) Then 10 ActiveCell.Value = dtMojaData 11 End If 12 End If End Sub |
Dopasowanie liczby dni
Pierwszym założeniem było to, aby po wyświetleniu formularza została wybrana dzisiejsza data.
- Problemu nie było z listą miesięcy. ComboBox cmbMiesiac zawiera stałą listę dwunastu miesięcy.
- Problemu nie było z listą lat. ComboBox cmbRok zawiera odwołanie do dynamicznego zakresu komórek.
Wyzwaniem było to, aby lista dni dopasowała się do bieżącego roku i miesiąca. Tutaj nie mogliśmy wyświetlić stałej listy np. od 1 do 31, ponieważ użytkownik nie powinien mieć możliwości wybrania daty, która nie istnieje np. 30-lutego.
Lista dni powinna się aktualizować na bieżąco i zależeć nie tylko od miesiąca, ale właśnie od kombinacji miesiąc-rok. Dlaczego? Ze względu na lata przestępne. Wybierając luty 2020r. użytkownik powinien zobaczyć listę od 1 do 29, wybierając 2021 lub 2022 lista powinna zawierać 28 dni.
Rozwiązaniem tego problemu jest wywołanie zdarzenia cmbRok_Click() lub cmbMiesiac_Click(). Dzieje się tak, gdy chcemy zaznaczyć na polach kombi miesiąc z dzisiejszą datą (np. grudzień) i rok z dzisiejszą datą (np. 2020). A chcemy to zrobić przy starcie formularza.
W takiej sytuacji wywoływane jest makro, które sprawdza ostatni dzień miesiąca (dla grudnia 2020) i ładuje do pola kombi odpowiednią liczbę dni.
Zdarzenie Click vs. Change
Dlaczego wybrałem zdarzenie Click, a nie Change?
Powód jest taki, że zdarzenie Change jest wyzwalane w sytuacji, gdy korzystamy z metody Clear. A dzieje się to wtedy, gdy chcemy wyczyścić listę na polu kombi. Ja nie chciałem tego robić ponieważ w tym zdarzeniu pobieram informację do zmiennej m_iRok na temat wybranego roku. Czyszcząc listę wyczyściłaby mi się również wartość tej zmiennej, a to byłby błąd.
Podsumowanie
Reasumując. Otrzymaliśmy quasi-kalendarz, który dostosowuje ilość wyświetlanych dni zależnie od wybranego miesiąca i roku. Jest to rozwiązanie bezpieczne, ponieważ pole kombi jest standardową kontrolką i nie musimy jej doinstalowywać.
Plik XLSM
