fbpx

Jak zamienić kropki na przecinki za pomocą makra (VBA)?

16.12.2014 | ECP3, Makra VBA, Techniczne

Nie wiem czy wiecie, ale artykuł o zamianie kropek na przecinki jest najczęściej czytanym artykułem na moim blogu. Szczerze mówiąc, jak go pisałam, to nawet do głowy mi wtedy nie przyszło, że tak będzie! Niewątpliwie jest to super trik, a wręcz niezbędny dla użytkowników np. SAPa (pozdrawiam! ;)), gdzie jako separator dziesiętny używana jest kropka, a nie przecinek. Oczywiście Excel liczby zapisane w takiej postaci: 1,234.56 traktuje jak tekst, co doprowadza nas – użytkowników – do szewskiej pasji 🙂

W artykule Jak zamienić kropki na przecinki pokazuję doskonałą (łatwą i szybką) metodę na pozbycie się tych nieszczęsnych kropek i zamianę ich na przecinki.

Co jednak w sytuacji, gdy po kilka razy dziennie musimy dokonywać takiej zamiany? Mimo, że metoda jest genialna, to przydałoby się jeszcze szybsze rozwiązanie. No i tutaj przychodzi nam z pomocą Norbert (pozdrawiam cię 🙂 ), który zapytał mnie jak to zrobić za pomocą… makra! Tadam! Super pytanie, więc postanowiłam, że napiszę o tym artykuł, aby więcej osób mogło poznać ten wspaniały sposób 🙂

A rozwiązanie nie jest bynajmniej takie oczywiste, o czym się za chwilę przekonacie…

Zacznijmy od danych, które należy zamienić. U mnie są one już w Excelu w kolumnie A. Tak to wygląda:

MalinowyExcel Jak zamienić kropki na przecinki za pomocą makra VBA - dane

Dane

Zamieńmy najpierw przecinki na nic, a potem kropki na przecinki. Żeby zrobić to ręcznie (dokładny opis znajdziesz tutaj), najpierw należy zaznaczyć zakres, w którym chcemy dokonać zmiany (A1:A9). Następnie wywołać okno zamieniania i dokonać odpowiednich zmian.

Za pomocą VBA zrobimy to trochę na skróty. Zanim uruchomimy makro – zaznaczymy dane, a makro podmieni odpowiednie znaki. Tyle. Oczywiście jest pewien haczyk, ale o tym za chwilę 🙂

Jak już wspomniałam, zanim uruchomimy makro – należy zaznaczyć zakres, w którym chcemy dokonać zmian. Makro będzie działało jedynie na tym zakresie. Jest to o tyle dobre, że dzięki temu jest uniwersalne – zamieni tam, gdzie zaznaczymy.

Ok. Aby stworzyć makro, trzeba otworzyć edytor Visual Basic. Możesz to zrobić za pomocą przycisku Visual Basic na karcie Deweloper na wstążce (tutaj znajdziesz opis, w jaki sposób dodać do wstążki kartę Deweloper). Pokaże nam się okno VBE (Visual Basic Editor), w którym na razie są 2 okienka: Project Explorer i Properties Window. W pierwszym z nich – Project Explorer – widzimy wszystkie otwarte pliki Excela i dodatki. My chcemy teraz dodać nowy moduł VBA, w którym umieścimy nasze makro. Aby to zrobić należy kliknąć prawym przyciskiem myszy na nazwę projektu, do którego chcemy tę procedurę dodać. W naszym przypadku będzie to po prostu nazwa naszego pliku. Z menu kontekstowego następnie należy wybrać opcję Insert i dalej Module. O tak:

MalinowyExcel Jak zamienić kropki na przecinki za pomocą makra VBA - wstawianie modułu

Wstawianie modułu

Pod nazwą naszego projektu (pliku) pojawi się katalog Modules, a w nim nasz moduł o nazwie Module1. Żeby zmienić tę cudną nazwę należy kliknąć prawym na tę nazwę na liście modułów i w okienku poniżej – Properties – zmienić nazwę (Name). Zatwierdzić Enterem oczywiście. Ja zmieniłam nazwę na Przydatne:

MalinowyExcel Jak zamienić kropki na przecinki za pomocą makra VBA zmiana nazwy modułu

Zmiana nazwy modułu

Ok. Kosmetyka zrobiona, to teraz do dzieła. Tworzymy nowe makro. Można je albo nagrać i potem edytować, albo od razu wpisać do nowego modułu to:

Sub Zamiana()

   Selection.Replace What:=",", Replacement:=""
   Selection.Replace What:=".", Replacement:="."
 
End Sub

Żeby wpisać kod do modułu, trzeba ten moduł otworzyć, czyli kliknąć na niego dwukrotnie lewym przyciskiem myszy.

Pierwsza linijka kodu w ramce w zaznaczonym zakresie (Selection) usuwa przecinki (zamienia je na nic). Druga linijka robi najtrudniejsze – zamienia kropki na przecinki. Tak, wiem, jest napisane, że zamienia kropki na kropki. Bez sensu? No właśnie niekoniecznie. Tutaj bowiem jest ten haczyk, o którym pisałam wcześniej 🙂

Jak to mawia Darek Kolasa – specjalista od VBA (znajdziesz go tutaj: http://akademia-vba.pl/) „Visual Basic Amerykaninem jest„. No właśnie… a po amerykańsku separatorem tysięcy jest przecinek, a separatorem dziesiętnym – kropka. Przecinki już wcześniej zamieniliśmy na nic, więc nie ma z tym problemu. Problem jest przy naszych kochanych kropkach…

Na razie, Excel (Polak ;)) traktuje kropki jak tekst. Za to VB (Amerykanin), jak każe mu się zamienić coś (u nas są to kropki) na kropki, to w liczbach uzyskamy efekt zamiany na separator dziesiętny (dla VB jest nim kropka!). Excel sobie to przetłumaczy na polski i voila! Mamy efekt, który chcieliśmy uzyskać. Trochę to głupie zamieniać kropkę na kropkę, ale działa – spróbujcie sami i dajcie znać w komentarzach 🙂

Efekt jest taki:

MalinowyExcel Jak zamienić kropki na przecinki za pomocą makra VBA efekt po zamianie

Efekt działania makra

Czyli z tekstu (pierwszy obrazek w tym artykule) zrobiliśmy liczby – o to chodziło.

No dobrze, ale jak teraz wywołać to makro ponownie? Skrótem klawiszowym Alt + F8. Tylko pamiętajcie koniecznie, że trzeba najpierw zaznaczyć zakres, w którym chcemy dokonać zmian.

Ostatnią rzeczą jest kwestia zapisania pliku. Pamiętaj, aby zapisać go z rozszerzeniem .xlsm (inaczej makro nie zadziała). Jest to format plików obsługujący makra. Warto też wiedzieć, że napisane makro będzie działało tylko w tym pliku. W innych – jedynie, gdy ten plik będzie otwarty.

To wszystko. Jestem ciekawa waszych opinii i tego, jak często będziecie tego używali i ile czasu dzięki temu zaoszczędzicie 🙂 Mam nadzieję, że sporo! Napiszcie śmiało w komentarzach pod tym artykułem.

A tutaj dowiecie się jak zamienić znak gwiazdki lub znak zapytania w komórce. Metoda prosta, a wcale nie taka oczywista…

Jeżeli chcesz lepiej poznać makra i VBA zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 3! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

13 komentarzy

  1. pięknie działa a jak zrobić na odwrót żeby zamieniało przecinki na kropki ?
    Pozdrawiam i dziekuje za odpowiedź

    Reply
    • A do czego tego potrzebujesz? Widzę kilka opcji rozwiązania 🙂

      Reply
      • ja potrzebuję zamieniać przecinki na kropki, żeby zaczytać plik do SAPa. Niestety, sekwencja:

        Selection.Replace What:=”,”, Replacement:=”.”, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

        użyta w makro nie działa 🙁

        Reply
        • Hej,
          obawiam się, że tą metodą niestety nie zadziała ;( Właśnie dlatego, że „Visual Basic” Amerykaninem jest”. Jedyną metodą, którą tutaj widzę, jest przejechanie każdej wartości pętlą, która sprawdzi, czy w liczbie jest przecinek i zamieni go na kropkę.
          A BTW: musisz koniecznie to makrem robić?:> Ctrl+h działa bez takiego kombinowania 😉
          Pozdrawiam
          Malina

          Reply
          • Albo SU3 w SAPie i zmiana formatu wyswietlania liczb dziesietnych 🙂 pewnie przez admina

            Mnie ciekawi ten kod z przykladu uzyty dla formatu liczb 1.234.567,89, ktos cos? 🙂

          • Pewnie przez admina…
            A o co dokładnie chodzi z tym formatem liczb?

  2. Panel sterowania …. ustawienia regionalne …. separator dziesiętny.

    W Windows domyślnie jest przecinek. Zmieniamy na kropkę i gotowe

    Reply
  3. ok a ja potrzebuję zamienić w przypadku jak jest liczba np. 1.256,33 zł. Zrobiłam przycisk i wszystko fajnie, przy czym wyszukuje tego typu liczby i usuwa tą kropkę i tu ok, ale zmienia też liczby typu 164,00 na 16400,00. Gdzie popełniłam błąd?

    Reply
    • A może po prostu spróbuj zamienić tylko kropki na puste?

      Reply
  4. Witam, dziękuję za przydatny artykuł.
    Czy ja dobrze rozumiem, że za każdym razem jak wkleję liczbę z kropką, to by zmienił ją na przecinek muszę wybrać Alt + F8 i potwierdzić?
    Czy za każdym razem przy otwieraniu i zamykaniu (z zachowaniem zmian) danego pliku ma się pokazywać ostrzeżenie dotyczące prywatności? Czy można je na stałe usunąć, tzn. by się nie pokazywało?
    Ostatnie pytanie czy jest jakaś metoda by zamienić kropki na przecinki i dodatkowo usunąć litery (np. „123.2587 ABCD” na „123,2587”, lub zrobić to w dwóch krokach.
    Z góry dziękuję za odpowiedź.

    Reply
    • Tak, tak napisane makro trzeba odpalać każdorazowo.
      Ostrzeżenie – można ustawić, aby się nie pokazywało (Deweloper/Bezpieczeństwo makr)
      Odnośnie usuwania liter – myślę, że tutaj najlepiej skorzystać z Power Query (podobnie, jak z tymi kropkami). Tylko to już uzyskamy specyficzny efekt (zaimportowanych danych), a nie wiem, czy takie rozwiązanie będzie akceptowalne? Można też oczywiście modyfikować makro.

      Reply
  5. Witam, dziękuję za przydatny artykuł.

    Reply
    • Bardzo proszę 🙂

      Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest