VBA #23 – Jak wykorzystać funkcje makr 4.0 w VBA

W poradzie 305 wspominałem o funkcjach makr 4.0. Pomogły mi wyciągnąć listę plików z katalogu, ale był z nimi jeden problem dla Polaków. Ciężko było znaleźć ich nazwę po Polsku.

Mi przyszedł do głowy jeden pomysł na poprawne tłumaczenie ich nazw za pomocą króciutkiego kodu VBA. Zadaniem tego kodu jest po prostu dodanie nazwy, która będzie odwoływać się do przykładowej funkcji makr 4.0.

Ponieważ VBA jest angielsko języczny, więc funkcję makr 4.0 wpisujemy w nim po angielsku (listę funkcji makr 4.0 z dokładnym opisem po angielsku możesz pobrać na stronie:
https://www.myonlinetraininghub.com/excel-4-macro-functions).

Po uruchomieniu tego makra do nazw Excela (patrz Menadżer nazw na karcie Formuły) dodadzą się dwie nazwy korzystające już z polskich nazw funkcji makr 4.0, gdyż mój Excel ‘mówi’ po polsku 😉 Po prostu między VBA, a Excelem funkcje zostały przetłumaczone. Dzięki temu będziemy już mogli korzystać z potrzebnych nam funkcji makr 4.0 już bez pomocy VBA.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Odświeżanie zapytań PowerQuery za pomocą VBA #22

W tym wpisie chcemy napisać kod VBA, który odświeża wszystkie zapytania albo tylko pojedyncze zapytanie PowerQuery. Odświeżanie wszystkich zapytań jest prostsze, ponieważ możesz nagrać ten kod sam i później podpiąć go do przycisku.
Najpierw musimy włączyć rejestrowanie makr – polecenie Zarejestruj makro z karty Deweloper.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 01

W oknie, które się otworzy

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 02

nadajesz nazwę makru (np.: Refresh), ewentualnie podpinasz skrót klawiszowy. To trochę bez sensu ponieważ polecenie odśwież wszystko z karty Dane (rys. 3) ma już przypisany do siebie skrót klawiszowy (Ctrl + Alt + F5). Upewniasz się, że makro zostanie zapisane do tego skoroszytu i naciskasz klawisz OK. Teraz ważne, żebyś nie klikał nigdzie w komórki arkusza, tylko od razu przeszedł do karty Dane i kliknął polecenie odśwież wszystko.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 03

Teraz możesz już wyłączyć rejestrację makra – polecenie Zatrzymaj rejestrowanie z karty Deweloper.
VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 04

Teraz nawet bez zaglądania do kodu makra możesz wstawić dowolny kształt lub obraz i kliknąć na niego prawym przyciskiem myszy, by z podręcznego menu wybrać opcję przypisz makro.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 05

Następnie z listy dostępnych makr wybierasz to, które zarejestrowałeś.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 06

I już możesz odświeżać wszystkie zapytania PowerQuery (oraz tabele przestawne i inne połączenia) za pomocą kliknięcia w ‘przycisk’ (kształt/obraz).

Cały kod makra wygląda tak:

Jeśli chciałbyś odświeżać tylko pojedyncze zapytanie to trudniejsze zadanie i rejestrator makr Ci raczej w tym nie pomoże (patrz film), za to możesz skorzystać z kodu poniżej.

Ważne, że do nazwy zapytania dodajesz prefiks „Zapytanie — ” (w innych język ten prefiks jest inny), a i myślnik nie jest standardowy, więc najlepiej, żebyś skopiować cały kod VBA (w Excelu ten myślnik ma KOD = 151).

Na koniec jeszcze pętla (jaką możesz znaleźć na różnych portalach w internecie), która pomoże Ci odświeżyć wszystkie zapytania PowerQuery w pliku (zmieniłem tylko prefiks na polski):

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Kwota słownie liczba słownie – odcinek #21

Chyba każdy program księgowy ma taką funkcjonalności, że potrafi zamienić liczbową reprezentację kwoty na jej reprezentację słowną, czyli przykładowo:

123, 45zł -> sto dwadzieścia trzy złote i czterdzieści pięć groszy

Jak to zrobić w Excelu? Potrzeba odpowiedni kod. Niestety nie jest znany mi jego autor, bo na żadnej stronie, gdzie go widziałem nikt nie wiedział kto go napisał. Mi on się bardzo podoba, bo jak się go odpowiednio podzieli jest naprawdę prosty do zrozumienia.
Ja żeby lepiej go zrozumieć dołożyłem do niego jeszcze część odpowiedzialną za przekształcenie miliardów.

Kod składa się z dwóch funkcji, które z komentarzami zamieszczam poniżej:

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak cofnąć makro – odcinek #20

Ogólnie jeśli chcesz Twoje makro ruszy arkusz, czy też dane w komórkach, to również wyczyści bufor cofania i nie będziesz mógł cofnąć, ani czynności, które wykonało makro, ani czynności, które wcześniej wykonywałeś – Ctrl + Z nie zadziała.

Jeśli Twoje makro nie rusza arkusza, np: wyświetla komunikat za pomocą funkcji Msgbox, to bufor cofania nie jest czyszczony i dalej możesz cofnąć swoje wcześniejsze czynności za pomocą skrótu klawiszowe.

Niektórzy chcieliby móc cofać swoje makra, dlatego został stworzony kod, który wykorzystuje pewną sztuczkę, a mianowicie w swoim makrze na samym jego początku dodajesz linijkę kodu, która zapisuje Twój plik:
ActiveWorkbook.Save

Następnie jeśli Twoje makro zadziałało niepoprawnie uruchamiasz makro, które powinieneś przechowywać w innym pliku np: skoroszycie makr osobistych, które pobiera nazwę Twojego pliku (aktywnego pliku), zamyka go bez zapisywania, a następnie ponownie uruchamia:

Czyli symuluje to co musiałbyś zrobić ręcznie, żeby odzyskać swoje dane 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak zmienić kolor słowa w zaznaczonych komórkach – odcinek #19

Krótki opis problemu

Potrzebujemy zmienić kolor tylko wybranych słów/fraz w zaznaczonych komórkach Excela. Sam Excel sobie z tym nie poradzi dlatego musimy napisać odpowiedni kod VBA.

VBA 19 - Jak zmienić kolor słowa w zaznaczonych komórkach (Walentynki) 01

Kod

Sub ZmieńKolor()

Dim MojeSłowo As String, Długość As Integer, i As Integer
Dim r As Range, SF As Integer
‚Jakie słowo frazę chcesz zmienić
MojeSłowo = InputBox(„Podaj słowo jakie chcesz pomalować na czerwono”)
‚Długość słowa/frazy, którą chcesz zmienić
Długość = Len(MojeSłowo)
‚Pętla po wszystkich komórkach (r) w zaznaczonym obszarze (Selection)
For Each r In Selection

‚Pętla po znakach w aktualnej komórce
For i = 1 To Len(r.Value)

‚Ponieważ może nie znaleźć słowa w komórce potrzebujemy obsługiwać
‚błąd wystarczy, że każemy VBA przejść dalej
On Error Resume Next
SF = 0
‚rozróżnia wielkość liter
SF = WorksheetFunction.Find(MojeSłowo, r.Value, i)
‚Nie zwraca uwagi na małe wielkie
‚SF = WorksheetFunction.Find(UCase(MojeSłowo), UCase(r.Value), i)
‚Wracamy do normalnej obsługi błędu
On Error GoTo 0
‚Jeśli zmienna SF większa od zera to znaczy, że tekst został
‚odnaleziony, czyli zmieniamy kolor części tekstu
If SF > 0 Then
r.Characters(Start:=SF, Length:=Długość).Font.Color = RGB(255, 0, 0)
i = SF + 1
‚jeśli nie to kończymy pętlę i przechodzimy do sprawdzania następnej komórki
Else
Exit For
End If

Next i

Next r

End Sub

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak pobrać dane przycisku, który uruchomił makro – odcinek #18

Dziś chcemy poznać kod, który umożliwi nam odczytanie danych przycisku, który uruchomił makro, dzięki czemu będziemy mogli odczytać informacje bezpośrednio np: z tekstu przechowywanym w przycisku, a nie zamieszczać tych informacji w kodzie VBA.
Dodatkowo będziemy mogli podpiąć to samo makro pod wiele przycisków/kształtów i w zależności od tego, który przycisk klikniesz wpiszą się inne informacje.

Zrobimy to na uproszczonym przykładzie akcji, jakie mogą wykonywać koszykarze z kosmicznego meczu. Mamy 3 przyciski akcji i 5 przycisków zawodników. Odpowiednio przyciski akcji są podpięte pod makro Akcja, a przyciski zawodników pod makro Zawodnik.

VBA 18 - Jak pobrać dane przycisku, który uruchomił makro 01

Dwie najważniejsze dla nas linijki kodu z tych dwóch makr to:

Najpierw pobieramy „wewnętrzną” nazwę przycisku (obiektu) który uruchomił makro, a później wykorzystujemy tą nazwę, żeby wyciągnąć dokładną informację o tekście przechowywanym w kształcie/przycisku. Ot i cała filozofia. Reszta kodu służy temu, żeby informacje wstawiały się w odpowiednich miejscach.

Przy tym kodzie ważna jest jeszcze informacja, że warto, żeby tekst w kształtach nie był wpisywany ręcznie tylko pobierany z komórek Excela.

VBA 18 - Jak pobrać dane przycisku, który uruchomił makro 02

Dzięki temu szybko możemy zmienić np: drużynę i jej wszystkich zawodników po prostu przekopiowując w komórki powiązane zawodników z drugiej drużyny.

If Range(„C2”).Value = „” Then
Range(„C2”).Value = NazwaPrzycisku
Else
Range(„C1”).End(xlDown).Offset(1, 0).Value = NazwaPrzycisku
End If
End Sub

Pozdrawiam
Adam Kopeć
Miłośnik Excela