Świat Excela

Blog nie tylko dla programistów VBA

Kasowanie znaku tzw. „twardej spacji”

przez 1 komentarz

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

Komentarze

  1. 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.

     

Dodaj komentarz