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.
Znak (Twardej) Spacji
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?
Dane Pierwotne
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).
Kod Makra
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.
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
Dane Przetworzone
A tak wygląda tabela w Excelu po uruchomieniu makra.
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.