Automatyczne czyszczenie danych: VBA, które usuwa spacje i poprawia formaty

Dlaczego automatyczne czyszczenie danych w Excelu ratuje projekty

Cel osoby sięgającej po automatyczne czyszczenie danych w Excelu jest zwykle prosty: pozbyć się ręcznego poprawiania spacji, formatów i błędnych zapisów, tak aby raporty dało się aktualizować jednym kliknięciem zamiast wielogodzinnego „przepychania” arkuszy. VBA świetnie nadaje się do tego zadania, pod warunkiem że proces czyszczenia jest dobrze zaplanowany i konsekwentnie stosowany.

Źródła brudnych danych w codziennej pracy

Większość problemów nie bierze się z samego Excela, ale z tego, skąd dane trafiają do arkusza. Typowe scenariusze:

• Kopiowanie z systemów zewnętrznych – CRM, ERP, systemy księgowe. Często dane zawierają dodatkowe spacje, znaki sterujące, apostrofy wymuszające tekst lub niestandardowe kody znaków.
• Import plików CSV – w zależności od separatorów i ustawień regionalnych daty i liczby lądują jako tekst, a polskie znaki bywają popsute przez nieodpowiednie kodowanie.
• Ręczne wpisy użytkowników – każdy ma trochę inny styl: „Warszawa”, „warszawa”, „WARSZAWA ” z dodatkową spacją; „Tak”, „TAK”, „yes”; skróty wpisywane na różne sposoby.

W małej tabelce takie różnice da się „przeskoczyć” ręcznie. W dziesiątkach tysięcy wierszy zamieniają się jednak w poważny problem, który później wylezie w tabelach przestawnych, raportach Power BI czy przy eksporcie danych do innych systemów.

Ręczne poprawki, formuły arkuszowe czy VBA – co sprawdza się najlepiej

Do czyszczenia brudnych danych w Excelu można podejść na kilka sposobów. Każde podejście ma swoje miejsce i ograniczenia.

Formuły są świetne jako etap przejściowy: pomagają „odkryć” i przetestować logikę czyszczenia. Gdy proces jest już ustalony, VBA pozwala tę logikę zamknąć w jednym, powtarzalnym makrze, które można uruchomić przed eksportem czy zapisaniem pliku.

Najczęstsze objawy bałaganu i ich wpływ na analizy

Dane rzadko krzyczą wprost „jestem błędna”. Problem ujawnia się dopiero na etapie analiz. Typowe objawy:

• Zbędne spacje – wartości wyglądają identycznie, ale Excel traktuje „Nazwa” i „Nazwa ” jako różne; w tabeli przestawnej pojawiają się dwa podobne wiersze.
• Apostrof na początku – wszystko jest tekstem, nawet daty i liczby; sortowanie zachowuje się dziwnie, filtry nie działają jak trzeba.
• Daty jako tekst – nie można obliczyć różnicy dni, miesiąca, kwartału; w dodatku różne formaty: „2023-01-02”, „01.01.2023”, „1/1/23”.
• Mieszane formaty liczb – część liczb z przecinkiem, część z kropką; część jako tekst, część jako liczby; sumy nie zgadzają się z oczekiwaniami.

Automatyczne czyszczenie danych w VBA działa jak filtr bezpieczeństwa między źródłem a raportem. Zanim dane trafią do tabel przestawnych, Power Query lub raportów, przechodzą przez sekwencję procedur, które usuwają spacje, normalizują formaty i zamieniają chaos w przewidywalną strukturę.

Podstawy pracy z tekstem i formatem w VBA (w kontekście sprzątania)

Wartość, format i typ danych – trzy różne światy

Podczas czyszczenia danych w VBA trzeba odróżniać trzy rzeczy: wartość komórki, jej format i typ danych w VBA. To częsta pułapka początkujących.

• Value / Value2 – faktyczna wartość, na której operuje kod. Jeśli w komórce jest data, VBA widzi liczbę (np. 45000 odpowiadające dacie), a nie tekst „2023-02-01”.
• Text – to, co jest widoczne na ekranie, już po nałożeniu formatowania; przy czyszczeniu rzadziej przydatne, chyba że trzeba analizować konkretny zapis.
• NumberFormat – sposób prezentacji wartości, np. „dd.mm.yyyy” lub „0,00 zł”. Zmiana formatu bez zmiany wartości nie naprawia daty zapisanej jako tekst.

Po stronie VBA dochodzi jeszcze kwestia typu danych:

• String – tekst, np. nazwy klientów, kody, niektóre daty zapisane błędnie.
• Double – liczby z częścią dziesiętną, np. kwoty, stawki VAT.
• Date – typ daty; wygodny do obliczeń, ale Excel przechowuje go jako liczbę.
• Variant – uniwersalny „pojemnik”, który przyjmuje wszystko, ale łatwo w nim ukryć błędy konwersji.

Przy automatycznym czyszczeniu danych w VBA wyraźne określanie typów (np. Dim d As Date, Dim s As String) pozwala szybciej wychwycić problemy: kompilator wymusi konwersję, co może zakończyć się błędem w kontrolowanym miejscu zamiast cichego zepsucia danych.

Kluczowe funkcje tekstowe w VBA wykorzystywane do czyszczenia

Czyszczenie tekstu w VBA opiera się głównie na kilku funkcjach, które można łączyć w łańcuchy.

• Trim, LTrim, RTrim – usuwają spacje odpowiednio z obu stron, lewej lub prawej. Działają tylko na zwykłe spacje (kod 32), nie usuwają spacji niełamliwych.
• Replace – zastępuje fragment tekstu innym, np. kropkę przecinkiem, spację niełamliwą zwykłą spacją, ciąg „ ” (dwie spacje) pojedynczą.
• UCase, LCase – zamiana tekstu na wielkie lub małe litery; przydają się przy ujednolicaniu kodów i znaczników typu „TAK/NIE”.
• StrConv – bardziej zaawansowane przekształcenia, np. vbProperCase do formatu „Pierwsza Litera Duża”, z zachowaniem polskich znaków.

Przykładowe połączenie przy prostym czyszczeniu:

Dim s As String
s = rng.Value
s = Replace(s, Chr(160), " ") ' zamiana spacji niełamliwych na zwykłe
s = Trim(s)                    ' usunięcie spacji na początku i na końcu
rng.Value = s

Łącząc te proste funkcje, można zbudować własną procedurę typu „super Trim”, która usuwa nie tylko zwykłe spacje, ale też ukryte znaki i podwójne odstępy wewnątrz tekstu.

WorksheetFunction kontra funkcje VBA – które wykorzystać

Wiele funkcji arkuszowych ma swoje odpowiedniki w VBA. Np. arkuszowe USUŃ.ZBĘDNE.ODSTĘPY (CLEAN/TRIM) można wywołać jako WorksheetFunction.Trim. Pojawia się pytanie, co jest wygodniejsze:

• Funkcje VBA (Trim, Replace, UCase) – szybsze, prostsze w użyciu, nie wymagają odwołania do obiektu WorksheetFunction, dobrze znane programistom.
• WorksheetFunction – pozwalają wykorzystać bardziej złożone funkcje arkuszowe bez budowania ich od zera w VBA (np. TEXT, PROPER itp.).

Przy typowych zadaniach związanych z automatycznym czyszczeniem danych w VBA lepiej sprawdzają się wbudowane funkcje języka. Są wydajne i przewidywalne. Po WorksheetFunction warto sięgnąć głównie wtedy, gdy:

• chodzi o specyficzną funkcję arkuszową, której brak w VBA,
• mierzy się czasem wykonania i wiadomo, że dana funkcja arkuszowa jest zaskakująco szybka dla dużych zakresów.

Pętle po komórkach – jak czyścić szybko i bez zamulania Excela

Klasyczny błąd przy pisaniu makr czyszczących to iterowanie po każdej komórce z osobna i częste odświeżanie ekranu. Przy dużych zakresach arkusz zaczyna się „dusić”.

Praktyczny kompromis to:

• ograniczyć zakres do używanych komórek (UsedRange lub konkretne kolumny),
• wyłączyć odświeżanie ekranu i automatyczne przeliczanie na czas działania makra,
• czytać i zapisywać dane blokami (tablica VBA) zamiast pojedynczych komórek, gdy zakres jest bardzo duży.

Sub CleanRangeSpaces(rng As Range)
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In rng.Cells
        If Not IsEmpty(c.Value) Then
            c.Value = Trim(CStr(c.Value))
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Dla kilku tysięcy wierszy taka pętla wykona się w rozsądnym czasie. Przy setkach tysięcy warto przejść na pracę na tablicach (Variant = rng.Value, pętla w pamięci, a na końcu zrzut przetworzonych danych z powrotem do zakresu).

Usuwanie zbędnych spacji – od Trim do inteligentnego czyszczenia

Różne rodzaje spacji i znaków „niewidzialnych”

Usuwanie spacji w Excelu z poziomu VBA wydaje się banalne, dopóki nie trafi się na dane kopiowane z systemów webowych lub PDF-ów. Wtedy pojawiają się „spacje”, które wcale nie są zwykłymi spacjami.

• Standardowa spacja – kod 32 (Chr(32)); Trim radzi sobie z nią bez problemu.
• Spacja niełamliwa (non-breaking space) – kod 160 (Chr(160)); często pojawia się przy kopiowaniu z przeglądarki, wygląda identycznie, ale Trim jej nie usuwa.
• Znaki sterujące – np. znaki końca linii, tabulacje, których nie widać, ale wpływają na sortowanie i porównania.

Jeżeli makro korzysta wyłącznie z Trim, łatwo dojść do sytuacji, w której użytkownik widzi „czyste” dane, a VBA nadal postrzega je jako różne ciągi znaków. To klasyczna przyczyna „mnożenia się” bardzo podobnych pozycji w tabelach przestawnych.

Porównanie Trim, USUŃ.ZBĘDNE.ODSTĘPY i własnego „super Trim”

Do walki ze spacjami można użyć kilku narzędzi. Każde zachowuje się odrobinę inaczej.

• VBA Trim – usuwa tylko standardowe spacje z początku i końca tekstu. Nie dotyka spacji wewnątrz ciągu ani spacji niełamliwych.
• Arkuszowa funkcja USUŃ.ZBĘDNE.ODSTĘPY – oprócz przycięcia z brzegów usuwa też nadmiarowe spacje wewnętrzne (zostawia maksymalnie jedną). Może jednak naruszyć teksty, w których podwójne spacje są istotne.
• Własna funkcja „super Trim” w VBA – pozwala krok po kroku zdefiniować, co jest uważane za zbędne: zamienić nietypowe spacje na zwykłe, usunąć znaki sterujące, ograniczyć liczbę spacji między wyrazami do jednej.

Przykład prostej funkcji „super Trim” dla celów czyszczenia danych biznesowych:

Przykładowa funkcja SuperTrim w VBA

Rozsądny kompromis między agresywnym a bezpiecznym czyszczeniem to funkcja, która:

• zamienia nietypowe spacje na zwykłe,
• wyrzuca znaki sterujące z końców tekstu,
• redukuje ciągi wielu spacji wewnątrz do pojedynczej,
• zostawia spacje, jeśli są częścią „środka” tekstu (np. w nazwach firm).

Function SuperTrim(ByVal s As String) As String
    Dim i As Long
    Dim ch As String
    Dim tmp As String
    
    ' 1. Zamiana nietypowych spacji na zwykłe
    s = Replace(s, Chr(160), " ")      ' NBSP
    s = Replace(s, vbTab, " ")         ' tabulacje na spacje
    s = Replace(s, Chr(13), " ")       ' CR
    s = Replace(s, Chr(10), " ")       ' LF
    
    ' 2. Przycięcie spacji z brzegów
    s = Trim(s)
    
    ' 3. Redukcja wielokrotnych spacji wewnętrznych
    For i = 1 To Len(s)
        ch = Mid$(s, i, 1)
        If Not (ch = " " And Right$(tmp, 1) = " ") Then
            tmp = tmp & ch
        End If
    Next i
    
    SuperTrim = tmp
End Function

Taka funkcja nadaje się jako „filtr wejściowy” praktycznie do każdego projektu raportowego. Jeśli w jakiejś kolumnie podwójne spacje są rzeczywiście znaczące (np. w specjalistycznych kodach), można ją pominąć dla tej kolumny albo dodać parametr sterujący sposobem działania.

Czyszczenie całych kolumn – kiedy wystarczy Trim, a kiedy SuperTrim

W typowym projekcie pojawiają się przynajmniej trzy klasy kolumn, które wymagają innego traktowania:

• kody i identyfikatory – NIP, numer klienta, numer faktury, kody produktów,
• nazwy i opisy – nazwy kontrahentów, miejscowości, opisy pozycji,
• pola „techniczne” – flagi TAK/NIE, kategorie, krótkie etykiety.

Dla kodów różnica między „ABC123” a „ABC123 ” oznacza inne rekordy, ale wewnętrzne spacje z reguły nie występują. Dlatego wystarczy przycięcie brzegów:

Sub CleanCodes(rng As Range)
    Dim dataArr As Variant
    Dim i As Long
    
    dataArr = rng.Value
    For i = 1 To UBound(dataArr, 1)
        If Not IsEmpty(dataArr(i, 1)) Then
            dataArr(i, 1) = Trim(CStr(dataArr(i, 1)))
        End If
    Next i
    rng.Value = dataArr
End Sub

Przy nazwach i opisach częściej pojawia się mieszanka formatów, „łamania” linii, wklejonych tabulacji. Tu lepszy będzie SuperTrim albo wariant rozszerzony o usuwanie podwójnych spacji wewnątrz:

Sub CleanDescriptions(rng As Range)
    Dim dataArr As Variant
    Dim i As Long
    
    dataArr = rng.Value
    For i = 1 To UBound(dataArr, 1)
        If Not IsEmpty(dataArr(i, 1)) Then
            dataArr(i, 1) = SuperTrim(CStr(dataArr(i, 1)))
        End If
    Next i
    rng.Value = dataArr
End Sub

Flagom i etykietom zwykle bliżej do kodów niż do opisów – tu nadmiar spacji niemal zawsze jest błędem. Przy nich można dodatkowo narzucić wielkość liter i słownik dozwolonych wartości (o tym niżej).

Spacje wiodące, końcowe i środkowe – różne ryzyka

Trzy typy spacji mają odmienne skutki:

• wiodące – wpływają na sortowanie, porównania i dopasowania (np. VLOOKUP/XLOOKUP nie znajduje danych),
• końcowe – mniej widoczne, ale psują porównania i filtry tekstowe („równa się”),
• środkowe – potrafią rozbić grupowania (np. „Warszawa Śródmieście” vs „Warszawa Śródmieście”).

Makra „hurtowe” często wycinają wszystkie nadmiarowe spacje środkowe, co jest wygodne, ale bywa destrukcyjne. Bezpieczniejsze podejście to:

1. Najpierw przyciąć wiodące i końcowe spacje (Trim/SuperTrim).
2. Później zastosować redukcję spacji środkowych tylko w wybranych kolumnach, gdzie jest to jednoznacznie pożądane (np. w adresach, nazwach miast).

Przykład selektywnej redukcji spacji środkowych:

Function ReduceInnerSpaces(ByVal s As String) As String
    Dim parts() As String
    parts = Split(SuperTrim(s), " ")
    ReduceInnerSpaces = Join(parts, " ")
End Function

Taką funkcję można zastosować np. tylko na kolumnie „Miasto”, natomiast kolumnę „Opis towaru” pozostawić po samym SuperTrim, jeśli istnieje ryzyko, że wewnętrzne podwójne spacje są znaczące (np. w tekstach formatowanych pod druk).

Poprawianie formatów tekstu: wielkość liter, polskie znaki, jednolite zapisy

Wielkość liter – trzy dominujące standardy

Przy wczytywaniu danych z wielu systemów spotykają się różne konwencje zapisu:

• CAŁY TEKST WIELKIMI LITERAMI (systemy legacy / hurtownie),
• tekst małymi literami, bez akcentów (import z API, logi),
• „format biurowy” – pierwsze litery wyrazów duże, reszta małe.

Najczęściej stosuje się trzy transformacje:

• UCase – zamiana wszystkiego na wielkie litery; przydatna dla kodów, flag, oznaczeń „TAK/NIE”,
• LCase – pełne małe litery; bywa używana do tymczasowej normalizacji przed porównywaniem tekstów,
• StrConv(…, vbProperCase) – „pierwsza litera duża”, dobre dla nazw i opisów, choć ma ograniczenia w skrótach.

Function NormalizeName(ByVal s As String) As String
    s = SuperTrim(s)
    s = StrConv(s, vbProperCase)
    NormalizeName = s
End Function

Na danych typu „JAN KOWALSKI”, „jan kowalski” i „ Jan KOWALSKI ” taka funkcja da zbliżony, spokojny efekt: „Jan Kowalski”. Problematyczne będą skróty („PKP”, „IT”, „VAT”), które ProperCase zamieni na „Pkp”, „It”, „Vat”. Dlatego dla pól zawierających dużo skrótów lepiej rozdzielić logikę:

• dla nazw klientów – ProperCase,
• dla działów/skrótów – UCase,
• dla etykiet technicznych (np. „aktywne/nieaktywne”) – LCase lub jednorodne UCase.

Polskie znaki – naprawianie „krzaków” i tekstów bez ogonków

W raportach łączonych z różnych źródeł pojawia się klasyczna mieszanka: raz „Łódź”, raz „Lodz”, a czasem „Lódź”. Trzy scenariusze wymagają innych narzędzi:

1. Brak polskich znaków („Lodz”) – zwykle da się zaakceptować bez korekty, ale jeśli raport ma iść „na zewnątrz”, przydaje się mapowanie bezogonkowych zapisów na poprawne (np. słownik nazw miast).
2. Nieprawidłowe kodowanie („Lódź”) – efekty mieszania UTF-8, Windows-1250 itp.; naprawa w samym VBA jest trudna i często wymaga ponownego wczytania danych z odpowiednim kodowaniem.
3. Mieszane wpisy („Lódz”, „Lodz”) – tutaj lepiej działa normalizacja do formy bezpolskiej (Lodz), a dopiero później ewentualne mapowanie.

Do szybkiego ujednolicenia pod kątem analitycznym (np. grupowanie miast w raportach) można pozbyć się wszystkich polskich znaków i zamienić je na odpowiedniki łacińskie. Przykładowa funkcja:

Function StripPolish(ByVal s As String) As String
    Dim fromChars As String
    Dim toChars As String
    Dim i As Long
    
    fromChars = "ĄĆĘŁŃÓŚŹŻąćęłńóśźż"
    toChars   = "ACELNOSZZacelnoszz"
    
    For i = 1 To Len(fromChars)
        s = Replace(s, Mid$(fromChars, i, 1), Mid$(toChars, i, 1))
    Next i
    
    StripPolish = s
End Function

Można ją połączyć z UCase i SuperTrim, aby uzyskać formę „techniczno-porównawczą”:

Function NormalizeForCompare(ByVal s As String) As String
    s = SuperTrim(s)
    s = StripPolish(s)
    s = UCase$(s)
    NormalizeForCompare = s
End Function

Taki wariant stosuje się głównie przed dopasowywaniem rekordów (np. porównanie kontrahentów z CRM i systemu księgowego), a niekoniecznie jako finalny widok dla użytkownika.

Jednolite zapisy flag i kategorii – słownik zamiast domysłów

Pola typu „Tak/Nie”, „Aktywny/Nieaktywny” czy „M”/„K” potrafią przyjść w kilkunastu wariantach:

• „TAK”, „Tak”, „tak”, „T”, „1”
• „NIE”, „Nie”, „nie”, „N”, „0”, pusty ciąg

Zamiast każdorazowo budować serię zagnieżdżonych If-ów, efektywniejsze jest podejście słownikowe – osobna funkcja, która z dowolnego brudnego wariantu tworzy ustalony standard (np. „TAK” lub „NIE”).

Function NormalizeYesNo(ByVal s As Variant) As String
    Dim n As String
    If IsNull(s) Or IsEmpty(s) Then
        NormalizeYesNo = ""
        Exit Function
    End If
    
    n = NormalizeForCompare(CStr(s)) ' SuperTrim + bez ogonków + UCase
    
    Select Case n
        Case "TAK", "T", "1", "Y", "YES"
            NormalizeYesNo = "TAK"
        Case "NIE", "N", "0", "NO"
            NormalizeYesNo = "NIE"
        Case Else
            NormalizeYesNo = "" ' nieznana wartość, do weryfikacji
    End Select
End Function

Na danych z różnych działów efekt jest namacalny: zamiast 5–6 różnych wersji odpowiedzi w filtrach pojawiają się tylko dwie wartości. Taka funkcja może też zapisywać nieznane kody do osobnego logu do przeglądu przez analityka.

Standardy formatowania nazw firm i osób – dwa różne światy

Ten sam mechanizm ProperCase inaczej sprawdzi się przy nazwach osób, a inaczej przy nazwach firm. Dla osób oczekiwany jest zapis „Jan Kowalski”, dla firm już niekoniecznie („PKO BP SA”, „XYZ Sp. z o.o.”).

Można zbudować dwie wyspecjalizowane funkcje:

Function NormalizePersonName(ByVal s As String) As String
    s = SuperTrim(s)
    s = LCase$(s)
    s = StrConv(s, vbProperCase)
    NormalizePersonName = s
End Function

Function NormalizeCompanyName(ByVal s As String) As String
    s = SuperTrim(s)
    ' Najpierw ProperCase dla "normalnych" części
    s = StrConv(s, vbProperCase)
    ' Następnie poprawka typowych skrótów
    s = Replace(s, " Sp. Z O.o.", " Sp. z o.o.")
    s = Replace(s, " S.a.", " S.A.")
    s = Replace(s, " S.c.", " S.C.")
    s = Replace(s, " Sp. J.", " Sp. j.")
    NormalizeCompanyName = s
End Function

W praktyce listę poprawek dla firm rozwija się stopniowo, obserwując rzeczywiste dane. Różnica między „Firma X Sp. Z O.O.” a „Firma X Sp. z o.o.” nie ma znaczenia w obliczeniach, ale w prezentacji i w wyszukiwaniu po nazwie już tak.

Daty, liczby i kody – różne typy, różne problemy

Daty jako tekst vs daty jako liczby

Najwięcej kłopotów sprawiają daty przechowywane jako tekst, szczególnie gdy w jednym pliku mieszają się różne formaty regionalne. Typowe warianty:

• „2023-01-02” (ISO, często tekst),
• „01.02.2023” (europejski),
• „02/01/2023” (anglosaski),
• „2023/01/02 00:00:00” (z komponentem czasu).

Jeżeli Excel nie rozpozna wartości jako daty, w VBA pojawi się String. Pierwszy krok to próba konwersji „miękkimi” metodami, ale z kontrolą regionalną:

Function SafeCDate(ByVal s As Variant) As Variant
    Dim d As Date
    
    If IsDate(s) Then
        SafeCDate = CDate(s)
        Exit Function
    End If
    
    ' Ręczna obsługa formatu ISO RRRR-MM-DD
    Dim parts() As String
    If InStr(1, s, "-") > 0 Then
        parts = Split(CStr(s), "-")
        If UBound(parts) = 2 Then
            On Error Resume Next
            d = DateSerial(CInt(parts(0)), CInt(parts(1)), CInt(parts(2)))
            If Err.Number = 0 Then
                SafeCDate = d
                Exit Function
            End If
            On Error GoTo 0
        End If
    End If
    
    SafeCDate = CVErr(xlErrValue) ' nie udało się
End Function

Taka funkcja zwraca albo poprawną datę (typ Date), albo błąd arkuszowy. Makro czyszczące może później zaznaczyć wszystkie błędy innym kolorem, aby operator szybko zauważył problematyczne wiersze.

Separatory dziesiętne – przecinek kontra kropka

Separatory dziesiętne – przecinek kontra kropka w praktyce VBA

Pliki z systemów zagranicznych często przychodzą z kropką dziesiętną („1234.56”), podczas gdy Excel w polskich ustawieniach regionalnych oczekuje przecinka („1234,56”). Efekt: wartości są traktowane jak tekst, nie biorą udziału w sumach, filtrach liczbowych ani wykresach.

Można podejść do tematu na dwa sposoby:

• globalna zmiana separatorów – manipulacja ustawieniami aplikacji lub systemu (ryzykowne dla innych plików i użytkowników),
• lokalna normalizacja tekstu na liczbę – dokładnie na tych zakresach, które trzeba naprawić.

W kontekście „makra sprzątającego” sprawdza się podejście lokalne: najpierw rozpoznanie, czy dany tekst wygląda na liczbę z kropką, potem kontrolowana zamiana na wartość numeryczną:

Function SafeCDbl(ByVal v As Variant) As Variant
    Dim s As String
    Dim d As Double
    
    If IsNull(v) Or IsEmpty(v) Then
        SafeCDbl = CVErr(xlErrNA)
        Exit Function
    End If
    
    If IsNumeric(v) Then
        SafeCDbl = CDbl(v)
        Exit Function
    End If
    
    s = CStr(v)
    s = SuperTrim(s)
    
    ' Zamiana kropki na przecinek tylko, gdy występuje typowy wzorzec liczby
    If s Like "*#.#*" And InStr(s, ",") = 0 Then
        s = Replace$(s, ".", Application.International(xlDecimalSeparator))
    End If
    
    On Error Resume Next
    d = CDbl(s)
    If Err.Number = 0 Then
        SafeCDbl = d
    Else
        SafeCDbl = CVErr(xlErrValue)
    End If
    On Error GoTo 0
End Function

Ten wariant nie narusza poprawnych zapisów z przecinkiem i ostrożnie obchodzi się z danymi typu kod (np. „1.2.3” – wersje oprogramowania), które i tak nie przejdą pozytywnie konwersji i trafią do błędów.

Kody, które wyglądają jak liczby – jak ich nie „zepsuć”

Numery klienta, kody produktów, identyfikatory przesyłek – często są przechowywane jako liczby, choć w logice biznesowej są tekstem. Dwie sytuacje powodują problemy:

• utrata zer wiodących („00123” staje się „123”),
• automatyczne zamiany formatu („1E+05” przy długich numerach).

Można wyróżnić dwa podejścia do czyszczenia takich pól:

1. traktowanie wszystkiego jako tekstu – bezpieczniejsze dla identyfikatorów, ale wymaga konsekwentnego formatowania kolumn,
2. mieszane – wartości „liczbowe” konwertowane na Double, „podejrzane” ciągi (z wiodącymi zerami, myślnikami) zostawiane jako tekst.

Przy danych operacyjnych (kody zamówień, numery przesyłek) przeważnie lepiej działa pierwsza strategia: normalizacja do tekstu, ale z kontrolowanym formatem:

Function NormalizeCode(ByVal v As Variant) As String
    Dim s As String
    s = CStr(v)
    s = SuperTrim(s)
    
    ' Zabezpieczenie przed formatem naukowym
    If InStr(1, s, "E+", vbTextCompare) > 0 Then
        s = Format$(CDbl(s), "0") ' pełna liczba, bez notacji naukowej
    End If
    
    ' Wszystko traktujemy jako tekst, bez lokalnych separatorów tysięcy
    s = Replace$(s, " ", "")
    s = Replace$(s, ".", "")
    s = Replace$(s, ",", "")
    
    NormalizeCode = s
End Function

Takie podejście jest konserwatywne, ale przewidywalne: kod „00123-AB” nie będzie nigdy przypadkowo zinterpretowany jako liczba. Dla czysto liczbowych identyfikatorów można dodatkowo pilnować stałej długości, np. uzupełniać do 8 znaków z przodu zerami.

Formatowanie kolumn po konwersji – liczby, daty, kody

Samo przepisanie wartości na odpowiedni typ to dopiero połowa efektu wizualnego. W arkuszu końcowym przydaje się spójne formatowanie:

• daty – jeden wzorzec (np. „rrrr-mm-dd”),
• liczby – stała liczba miejsc po przecinku, separator tysięcy,
• kody – format „tekstowy”, żeby Excel nie „pomagał” za bardzo.

Makro czyszczące może po konwersji przelecieć przez wybrane kolumny i nadać im jednolite formaty:

Sub FormatCleanedColumns(ByVal ws As Worksheet)
    With ws
        .Range("B:B").NumberFormat = "yyyy-mm-dd"  ' daty
        .Range("C:C").NumberFormat = "# ##0,00"    ' kwoty
        .Range("D:D").NumberFormat = "@"           ' kody/identyfikatory
    End With
End Sub

Różnica między arkuszem „surowym” a po takim zabiegu jest natychmiast widoczna: filtry działają przewidywalnie, sortowanie po datach nie miesza kolejności, a kody nie zmieniają długości.

Projekt makra „jednym kliknięciem”: plan czyszczenia arkusza

Dwa style architektury: procedura monolityczna vs zestaw funkcji

Makro sprzątające można zbudować na dwa główne sposoby:

• jeden duży Sub – cała logika w jednej procedurze, szybka do napisania, trudniejsza do utrzymania,
• moduł funkcji narzędziowych + cienka warstwa sterująca – początkowo więcej pisania, za to łatwiejsza rozbudowa i ponowne użycie.

Przy jednorazowych, jednorazowo używanych plikach monolit może wystarczyć. W raportach cyklicznych wyraźnie wygrywa podejście modułowe: identyczne funkcje „SuperTrim”, „NormalizeYesNo”, „SafeCDate” można wykorzystać w kilku projektach bez przepisywania.

Mapa kolumn – serce konfigurowalnego makra

Zamiast na sztywno zaszywać „kolumna B to daty, kolumna C to kwoty”, wygodniej jest zbudować małą „mapę kolumn” – np. w osobnym arkuszu lub w tablicy w kodzie. Pozwala to porównać dwa warianty:

• konfiguracja w kodzie – szybsza, ale wymaga wejścia do edytora VBA przy zmianach,
• konfiguracja w arkuszu – ciut wolniejsza, za to dostępna dla kogoś, kto nie programuje.

Prosty przykład mapy zakodowanej w VBA (wariant „szybki”):

Enum CleanType
    ctText = 0
    ctPersonName = 1
    ctCompanyName = 2
    ctDate = 3
    ctNumber = 4
    ctYesNo = 5
    ctCode = 6
End Enum

Private Type ColumnRule
    Col As Long
    Rule As CleanType
End Type

Function GetRules() As Variant
    Dim rules(1 To 5) As ColumnRule
    
    rules(1).Col = 1: rules(1).Rule = ctCode          ' A: kod klienta
    rules(2).Col = 2: rules(2).Rule = ctPersonName    ' B: imię i nazwisko
    rules(3).Col = 3: rules(3).Rule = ctCompanyName   ' C: nazwa firmy
    rules(4).Col = 4: rules(4).Rule = ctDate          ' D: data
    rules(5).Col = 5: rules(5).Rule = ctYesNo         ' E: flaga aktywny
    
    GetRules = rules
End Function

Taki zestaw reguł może być użyty przez jedno makro sterujące, które dla każdej komórki wybiera właściwą funkcję czyszczącą.

Centralna funkcja „CleanValue” – jeden punkt decyzyjny

Zamiast w pętli po arkuszu pisać serię If-ów na typ kolumny, wygodniej jest wprowadzić centralną funkcję routera:

Function CleanValue(ByVal v As Variant, ByVal rule As CleanType) As Variant
    Select Case rule
        Case ctText
            CleanValue = SuperTrim(CStr(v))
        Case ctPersonName
            CleanValue = NormalizePersonName(CStr(v))
        Case ctCompanyName
            CleanValue = NormalizeCompanyName(CStr(v))
        Case ctDate
            CleanValue = SafeCDate(v)
        Case ctNumber
            CleanValue = SafeCDbl(v)
        Case ctYesNo
            CleanValue = NormalizeYesNo(v)
        Case ctCode
            CleanValue = NormalizeCode(v)
        Case Else
            CleanValue = v
    End Select
End Function

Jeżeli za pół roku trzeba będzie dodać nowy typ pola (np. NIP z walidacją sumy kontrolnej), wystarczy rozbudować ten Select Case i dopisać nową funkcję pomocniczą.

Pętla czyszcząca – iteracja po wierszach i kolumnach

Na bazie mapy kolumn i centralnej funkcji czyszczącej można zbudować główne makro. Najprostsza wersja używa bezpośredniego dostępu do komórek; wydajniejsza – tablic w pamięci. Porównanie:

• bezpośrednio po komórkach – prostszy kod, wolniejszy na dużych tablicach (tysiące wierszy),
• tablice Variant – szybsze dla większych danych, ale mniej intuicyjne na początku.

Przykład wariantu „pamięciowego” dla wydajności:

Sub CleanSheet(ByVal ws As Worksheet)
    Dim rules As Variant
    Dim r As Long, c As Long, i As Long
    Dim lastRow As Long
    Dim arr As Variant
    
    rules = GetRules()
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub ' brak danych
    
    ' Zakres danych (1. wiersz nagłówki)
    arr = ws.Range("A2:E" & lastRow).Value
    
    For i = LBound(rules) To UBound(rules)
        c = rules(i).Col
        For r = 1 To UBound(arr, 1)
            arr(r, c) = CleanValue(arr(r, c), rules(i).Rule)
        Next r
    Next i
    
    ws.Range("A2:E" & lastRow).Value = arr
End Sub

Przy arkuszu z kilkudziesięcioma tysiącami wierszy taka wersja zwykle działa zauważalnie szybciej niż iteracja po każdej komórce z osobna.

Warstwa „user-friendly”: przycisk, skrót klawiaturowy, etykiety

Z technicznego punktu widzenia makro jest gotowe, ale dla końcowych użytkowników liczy się sposób wywołania. Można porównać trzy możliwości:

• Alt+F8 i wybór makra z listy – najprostsze, ale mało przyjazne dla mniej technicznych osób,
• przycisk formularza w arkuszu – intuicyjny, nadaje się do pliku, który krąży po firmie,
• dedykowana karta na wstążce – wymaga więcej pracy na początku (szablon .xlam, konfiguracja wstążki), ale daje wrażenie „prawdziwej aplikacji”.

Najczęściej wybiera się przycisk w arkuszu połączony z krótkim opisem, co dokładnie robi makro i na których kolumnach. Użytkownik widzi: „Krok 1 – wklej dane z systemu. Krok 2 – kliknij Wyczyść dane”. Od strony VBA wystarczy zwykła procedura wywołująca algorytm czyszczący:

Sub ButtonCleanData_Click()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    CleanSheet ThisWorkbook.Worksheets("Dane_surowe")
    FormatCleanedColumns ThisWorkbook.Worksheets("Dane_surowe")
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Przy projektach, w których kilka działów korzysta z tego samego pliku, taki „jednym kliknięciem” workflow ogranicza liczbę ręcznych manipulacji i zmniejsza rozjazdy pomiędzy raportami.

Sygnalizowanie problemów – błędy jako element procesu

Nawet najlepiej zaprojektowane makro nie rozwiąże wszystkich niejednoznaczności. Różnica polega na tym, w jaki sposób zgłasza problemy. Z praktyki wyłaniają się dwa podejścia:

• twarde zatrzymanie – MsgBox z komunikatem o błędzie, użytkownik musi reagować od razu,
• miękki log w arkuszu – wszystkie nieprzetworzone wartości są zaznaczane lub wypisywane w osobnym raporcie.

W kontekście czyszczenia danych zwykle lepiej działa podejście drugie: makro przechodzi przez całość, a sporne rekordy są oznaczone do późniejszej weryfikacji. Prostym mechanizmem jest kolorowanie komórek, które zwróciły błąd arkuszowy:

Sub HighlightErrors(ByVal ws As Worksheet, ByVal rng As Range)
    Dim c As Range
    For Each c In rng.Cells
        If IsError(c.Value) Then
            c.Interior.Color = vbYellow
        End If
    Next c
End Sub

Po wywołaniu „CleanSheet” można zaznaczyć zakres danych i uruchomić „HighlightErrors”, dzięki czemu analityk jednym rzutem oka widzi, które daty lub kwoty trzeba sprawdzić ręcznie. Taki kompromis między automatem a kontrolą ludzką zwykle sprawdza się lepiej niż poleganie wyłącznie na jednym z tych biegunów.