W Excelu często przetwarzamy dane, które pochodzą z różnych systemów IT. Po zaczytaniu ich do arkusza, nierzadko okazuje się, że Excel traktuje je w sposób inny od oczekiwanego. Jednym z popularnych tematów jest obecność tzw. znaku „twardej spacji”. Jest to znak, który wygląda jak zwykła spacja, ale nim nie jest. Wywołanie skrótu Ctrl+H i próba zamiany znaku zwykłej spacji na pusty ciąg, nie daje żadnego efektu, kod tego znaku to bowiem 160, zaś kod zwykłej spacji to 32. Konieczne jest skopiowanie znaku twardej spacji i wklejenie go do pierwszego pola na karcie Zamień okienka Znajdowanie i zamienianie. Wtedy wszystko zadziała. A jak to zrobić za pomocą makra?
Twarda spacja w arkuszu jest znakiem niepożądanym i przeszkadza w wyliczeniach, czego dowodem może być poniższy przykład. Screen pierwszy pokazuje dane pierwotne. Widzimy, że niektóre kwoty po zaczytaniu do Excela zostały zinterpretowane jako tekst (wyrównanie do lewej strony komórki).
Użycie makra, które kasuje znak twardej spacji pozwala nam na konwersję tych kwot wyglądających jak liczby (a będących w rzeczywistości tekstem) do prawdziwych liczb.
Poniżej makro główne wywołujące podprocedurę KasujTwardeSpacje realizującą to zadanie. Analizę kodu zostawiam pod rozwagę Czytelnikom.
'// Autor : Mariusz Jankowski Private Const msMODUL As String = "MMakroGlowne" Option Explicit Public Sub MakroGlowne() 'Wywołaj funkcję dla arkusza wksDane KasujTwardeSpacje wksDane.UsedRange End Sub Public Sub KasujTwardeSpacje(ByRef rngObszar As Range) Dim rngCelka As Range 'Pojedyncza komórka Dim sWartoscCelki As String 'Wartość komórki (pobrana) Dim bCzyTwardaSpacja As Boolean 'Sprawdza czy jest twarda spacja w wartości Dim sWartoscBezSpacji As String 'Wartość komórki (po usunięciu spacji) Dim dNowaWartosc As Double 'Nowa wartość Dim lLicznik As Long 'Zliczamy komórki z twardymi spacjami Const sPROC As String = "KasujTwardeSpacje" Const sFORMAT_KS As String = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" 'Aktywuj obsługę błędów 1 On Error GoTo ObslugaBledu 'Pętla po wszystkich komórkach w zakresie 2 For Each rngCelka In rngObszar.Cells 'Zaczytaj bieżącą wartość komórki 3 sWartoscCelki = rngCelka.Value 'Bierz pod uwagę tylko niepuste komórki 4 If Len(sWartoscCelki) <> 0 Then 'Sprawdź czy w komórce znajduje się twarda spacja 5 bCzyTwardaSpacja = InStr(1, sWartoscCelki, Chr(160)) > 0 'Działaj tylko na komórkach, które zawierają twardą spację 6 If bCzyTwardaSpacja Then 'Zwiększ licznik 7 lLicznik = lLicznik + 1 'Usuń twardą spację 8 sWartoscBezSpacji = Trim(WorksheetFunction.Substitute( _ sWartoscCelki, Chr(160), "")) 'Sprawdzamy czy wartość bez spacji jest liczbą 9 If IsNumeric(sWartoscBezSpacji) Then 10 dNowaWartosc = CDbl(sWartoscBezSpacji) 11 rngCelka.Value = dNowaWartosc 12 rngCelka.NumberFormat = sFORMAT_KS 13 End If '// If IsNumeric(sWartoscBezSpacji) Then 14 End If '// If bCzyTwardaSpacja Then 15 End If '// If Len(sWartoscCelki) <> 0 Then 16 Next rngCelka '// For Each rngCelka In rngObszar.Cells 'Wyświetl info 17 MsgBox "Operacja zakończona." & vbCr & _ "Liczba komórek, w których usunięto twarde spacje: " & lLicznik Wyjscie: 18 Set rngCelka = Nothing 19 On Error GoTo 0 20 Exit Sub ObslugaBledu: 21 Application.ScreenUpdating = True 22 MsgBox "Wystąpił błąd nr " & Err.Number & " (" & Err.Description & ")." & _ vbCr & vbCr & "Linia kodu nr " & Erl & " w procedurze " & _ "'" & sPROC & "' modułu '" & msMODUL & "'.", vbInformation, "BŁĄD!" 23 Resume Wyjscie End Sub
Podane makro może wydawać się nieco nadmiarowe ponieważ tak naprawdę wystarczy skorzystać z metody REPLACE obiektu RANGE. W takiej sytuacji obszarem może być zakres roboczy (UsedRange) lub wszystkie komórki arkusza (Cells). W parametrze WHAT wpisujemy Chr(160) zaś w REPLACEMENT podajemy pusty ciąg vbNullString. Makro skasuje nam twarde spacje z komórek jednak nie wyrówna otrzymanych w wyniku liczb do prawej strony. Estetom polecam więc uniwersalne makro zawarte w tej notce, natomiast warto pamiętać, że istnieje rozwiązanie prostsze i szybsze, nie bazujące na pętli.