Ostatni biznesowy dzień miesiąca (funkcja)

Dziś krótka odpowiedź na pytanie- jak pobrać datę odpowiadającą ostatniemu dniu miesiąca z pominięciem weekendu? Jeżeli ostatni dzień miesiąca wypada w sobotę lub niedzielę naszym celem będzie otrzymanie daty odpowiadającej piątkowi poprzedzającemu ten weekend. Poniżej gotowe rozwiązanie w postaci funkcji UDF. Proszę też zwrócić uwagę na ten zapis: którego celem jest zwrócenie daty ostatniego dnia miesiąca (poprzedniego).
Warto przy okazji też podkreślić, że funkcja DateSerial() jest funkcją bardzo elastyczną, która umożliwia wprowadzenie ilości miesięcy spoza przedziału 1-12 co powoduje automatyczną konwersję na odpowiedni miesiąc dokonując przejścia przez kolejne lata (mniejsze od 0- lata wcześniejsze, większe od 12- lata późniejsze). Podobnie kwestia wygląda w zakresie dni, gdzie dopuszczalne są wartości spoza przedziału 1-31, a czego przykład widać w powyższej funkcji.

Funkcja sprawdzająca istnienie arkusza w zewnętrznym pliku Excela? (kontynuacja)

Kontynuując zagadnienie prezentowane przed kilkoma dniami chciałbym zaprezentować podobne rozwiązanie do tam opisanego lecz tym razem oparte o funkcję użytkownika UDF. Cel- funkcja ma zwracać wartości True/False w odpowiedzi na pytanie czy we wskazanym skoroszycie (tu podamy pełną ścieżkę do pliku) istnieje określony arkusz (tu podamy jego nazwę). Poniżej kod funkcji wraz z dodatkowymi komentarzami. Funkcja tej postaci działać będzie zarówno w środowisku VBA jak również w dowolnej komórce Excela.

Zaokrąglanie liczb- funkcja Round vs. UDF

W dobie operacji finansowych wykonywanych dla jednostek wyrażanych w czterech, pięciu miejscach po przecinku zaokrąglanie wyników może sprawiać pewien problem. Szczególny problem pojawia się w momencie, gdy sięgamy po standardową, wbudowaną funkcję VBA Round. Okazuje się bowiem, że funkcja ta nie zaokrągla w sposób jaki oczekujemy. Zobrazuje to poniższy przykładowy kod: Jak widać zwracane wynik są w niektórych sytuacjach lekko zaskakujące. Teoretycznie bowiem obowiązuje tu zasada, iż w sytuacji, gdy zaokrąglana wartość wynosi 5 (jak w każdym powyższym przypadku) to zaokrąglenie w górę nastąpi o ile poprzednia liczba jest nieparzysta, oraz w dół- gdy poprzednia liczba jest parzysta. Jak widać nie w każdym przypadku jest to prawdziwe.

Jeżeli więc potrzebujemy klasycznej techniki zaokrąglania to niezbędne jest stworzenie własnej funkcji zaokrąglającej. Poniżej przykład takie funkcji:


Wyniki działania naszej funkcji zaokrąglającej prezentuje poniższy przykład:

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:


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

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


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:


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.

Funkcja zwracająca symbol kolumny

Mam czasem wrażenie, że to pytanie jest dość powszechne w pracy z Excelem- jak zwrócić symbol określonej kolumny, a konkretnie jej literowy indeks, czyli A, B, Z, AA, AAA, itp? No cóż, w zasobie standardowych funkcji arkuszowych nie dysponujemy odpowiednią formułą, która może wykonać dla nas to zadanie. Dlatego też stworzymy prostą funkcję, która uzupełni ten brak. A właściwie stworzymy dwie proste funkcje, które wykorzystają dwa różne podejścia do zagadnienia.

Wariant 1. Funkcja  zwraca literowy symbol kolumny dla komórki, w której została umieszczona
W rozwiązaniu tym kluczowe będzie wykorzystanie właściwości Application.ThisCell, która to właściwość dostępna jest tylko z poziomu funkcji własnych użytkownika. Jej rolą jest zwrócenie obiektu Range odwołującego się do komórki, do której została wstawiona sama funkcja.
W obu rozwiązaniach sięgniemy natomiast po funkcję Split, która tworzy tablicę z ciągu tekstowego.
Oto pełny kod naszej pierwszej funkcji:


Powyższa funkcja wstawiona do komórki C20 zwróci w wyniku C, w AA10 - AA, w XYZ14 zwróci XYZ.

Wariant 2. Funkcja zwraca literowy symbol kolumny, której numer został wskazany w formie parametru funkcji
W tym przykładzie kluczem będzie stworzenie wirtualnego odwołania do kolumny, której numer jest parametrem funkcji. Następnie dla takiej kolumny odczytamy adres, a dalsze działania są praktycznie zbieżne z wariantem 1. Tak wyglądać więc będzie kolejna z naszych funkcji:


Funkcję tą możemy wywołać w Excelu na kilka sposobów:

Możemy także wykorzystać w formie parametru inną funkcję arkuszową: NR.KOLUMNY:

Powyższe wywołanie będzie tożsame z wywołaniem funkcji z wariantu 1- w komórce, w której wstawimy połączone funkcje zwrócony zostanie symbol kolumny dla tej właśnie komórki, a więc dla komórki C20 otrzymamy C, dla AA10 - AA, a dla XYZ14- XYZ.

Wskazówka! Oba rozwiązania można połączyć w jedną uniwersalną funkcję z opcjonalnym parametrem. Techniki tej uczymy w czasie prowadzonych szkoleń z zakresu VBA dla Excela, szczególnie prowadząc kurs VBA dla analityków i finansistów.

Co w rzeczywistości zawiera komórka w Excelu?

Z pytaniem umieszczonym w tytule niniejszego posta spotkałem się kilkukrotnie, zarówno przeglądając różne wpisy i pytania na forach internetowych, jak również prowadząc szkolenie z zakresu VBA dla Excela. Chodzi bowiem o sytuację, w której chcemy określić rodzaj informacji zawartej w komórce z uwzględnieniem rodzaju formatowania jaki został zastosowany w danym zakresie arkusza.

Gdzie jednak znajduje się problem? Z punktu widzenia VBA liczba, data, czas i procent- wszystkie te elementy są liczbami. Także wartość Prawda/Fałsz w praktyce jest liczbą odpowiadającą 1 lub 0. W naszej sytuacji określić rzeczywisty typ danych znajdujących się w komórce.

W celu rozwiązania tego problemu wystarczy skonstruować prostą funkcję, której pełną postać znajdziecie Państwo poniżej. Kluczowa w tej funkcji pozostaje kolejność sprawdzania poszczególnych typów. Prześledźmy to na przykładzie typu liczbowego, który sprawdzany jest jako ostatni. Musimy się najpierw upewnić, że podane wartości nie są żadnym z typów: datą, wartością czasu, procentem lub wartością Prawda/Fałsz. Każdy z tych typów będąc domyślnie numerycznym zostałby więc rozpoznany jako liczba. Tymczasem nasza funkcja wydaje się działać prawidłowo co prezentuje poniższy zrzut ekranu.

Wyrażenia regularne RegExp raz jeszcze

Postanowiłem raz jeszcze wrócić do zagadnienia związanego z wyrażeniami regularnymi. Tym razem poszerzę temat o dwa obszary- pobieranie określonego fragmentu n-tego elementu spełniającego kryterium wyszukiwania oraz włączenie RegExp do własnej funkcji użytkownika (UDF).

Spójrzmy na początek na poniższy przykładowy tekst:

    Questionnaire results from company web.
    Name: John Smith
    Phone: 1234567
    Name: Jan Kowalski
    Phone: 9876545321
    Name: Johan Schmitt
    Phone: 00112233

Z pomocą RegExp i VBA spróbujemy przygotować rozwiązanie, które umożliwi pobranie np. drugiego imienia  i nazwiska (Jan Kowalski) czy też trzeciego numeru telefonu (00112233) z naszego przykładowego tekstu.

Trudność pierwsza- musimy odnaleźć fragment tekstu, który zaczyna się od Name. W tym celu nasz wzorzec będzie miał postać:

W wyniku czego jesteśmy w stanie otrzymać kolekcję składającą się z elementów:

    Name: John Smith
    Name: Jan Kowalski 
    Name: Johan Schmitt 

Trudność druga- jak pobrać samo imię i nazwisko i pominąć początkowy fragment z wyników wyszukiwania? W tym celu będziemy musieli sięgnąć głębiej w metodę .Execute. Samo wywołanie tej metody tworzy kolekcję zawierającą wszystkie wystąpienia spełniające kryterium .Pattern. Istnieje jednak możliwość pobrania określonego fragmentu n-tego elementu sięgając do kolekcji .SubMatches. Elementami należącymi do tej kolekcji będą wszystkie fragmenty, które zostały ujęte w nawiasach w naszym wzorcu .Pattern.
Ogólna składnia metody .Execute wyglądałaby następująco:


Proponuję zebrać w całość nasz kod. Na początek testowa procedura wywołująca z dodatkowymi komentarzami wewnątrz kodu:


A teraz funkcja właściwa uwzględniająca przedstawione powyżej istotne elementy metody .Execute z dodatkowym komentarzem:

Po wywołaniu procedury Pobieranie_nTego_elementu() uzyskamy w oknie immediate dokładnie to czego szukaliśmy, a więc odpowiednio:

    Jan Kowalski
    00112233

Blokada własnej funkcji po stronie Excela

Pytanie z jednego z forum wydało mi się na tyle ciekawe, że postanowiłem przedstawić swoją odpowiedź także i w tym miejscu.

Pytanie owo brzmiało: czy da się stworzyć funkcję własną użytkownika tak, aby była dostępna po stronie środowiska VBA/IDE (możliwość wywoływania z okna Immediate lub innych procedur i funkcji) i aby nie była dostępna po stronie komórek w arkuszu. Ponieważ nie da się wykluczyć dostępności funkcji po stronie Excela możemy jedynie zwrócić inny jej wynik jeżeli wywołana zostanie właśnie w dowolnej komórce arkusza. Do tego zmierzać będzie więc rozwiązanie.

Pierwsza myśl to zastosowanie słowa kluczowego Private przed deklaracją funkcji. Otóż słowo to sprawia, że funkcja nie jest wyświetlana w podpowiedzi natomiast nie sprawia, że funkcja nie jest dostępna. Innymi słowy- jeżeli ktoś zna nazwę naszej funkcji może z niej skorzystać w dowolnej komórce Excela.

Rozwiązanie, które zaproponuję opiera się o sprawdzenie skąd pochodzi wywołanie funkcji (Application.Caller). Jeżeli wynik zwraca wartość Range to znaczy to, że funkcja wywołana jest z aplikacji Excel. Rozwiązanie to nie jest jednak wystarczające. Okazuje się, że jeżeli nasza funkcja jest wykorzystywana w obliczeniach pośrednich innej funkcji wywołanej z Excela to również otrzymamy informację jakoby wywołanie pochodziło z komórkek Excela. Dlatego też niezbędna jest dodatkowa weryfikacja, czy w wywołaniu użyto nazwy naszej funkcji.

Aby nie przedłużać teorii przedstawię kod, który powinien wyjaśnić wszystkie wątpliwości: