poniedziałek, 17 marca 2014

Funkcja zwracająca symbol kolumny

Mam czasem wrażenie, że to pytanie jest dość powszechne w pracy z Excelem- jak zwrócić symbol określonej kolumny, a konkretnie jej literowy indeks, czyli A, B, Z, AA, AAA, itp? No cóż, w zasobie standardowych funkcji arkuszowych nie dysponujemy odpowiednią formułą, która może wykonać dla nas to zadanie. Dlatego też stworzymy prostą funkcję, która uzupełni ten brak. A właściwie stworzymy dwie proste funkcje, które wykorzystają dwa różne podejścia do zagadnienia.

Wariant 1. Funkcja  zwraca literowy symbol kolumny dla komórki, w której została umieszczona
W rozwiązaniu tym kluczowe będzie wykorzystanie właściwości Application.ThisCell, która to właściwość dostępna jest tylko z poziomu funkcji własnych użytkownika. Jej rolą jest zwrócenie obiektu Range odwołującego się do komórki, do której została wstawiona sama funkcja.
W obu rozwiązaniach sięgniemy natomiast po funkcję Split, która tworzy tablicę z ciągu tekstowego.
Oto pełny kod naszej pierwszej funkcji:

1Function ColumnNameSimple() As String
2 
3    Dim TMP As Variant
4    TMP = Split(Application.ThisCell.Address, "$")
5    ColumnNameSimple = TMP(1)
6    
7End Function

Powyższa funkcja wstawiona do komórki C20 zwróci w wyniku C, w AA10 - AA, w XYZ14 zwróci XYZ.

Wariant 2. Funkcja zwraca literowy symbol kolumny, której numer został wskazany w formie parametru funkcji
W tym przykładzie kluczem będzie stworzenie wirtualnego odwołania do kolumny, której numer jest parametrem funkcji. Następnie dla takiej kolumny odczytamy adres, a dalsze działania są praktycznie zbieżne z wariantem 1. Tak wyglądać więc będzie kolejna z naszych funkcji:

1Function ColumnNameEasy(ColNumber) As String
2 
3    If ColNumber > 0 Then
4        Dim TMP As Variant
5        TMP = Split(Replace(Columns(ColNumber).Address, ":", ""), "$")
6        ColumnNameEasy = TMP(1)
7    End If
8    
9End Function

Funkcję tą możemy wywołać w Excelu na kilka sposobów:
1=ColumnaNameEasy(26)     zwróci Z
2=ColumnaNameEasy(27)     zwróci AA

Możemy także wykorzystać w formie parametru inną funkcję arkuszową: NR.KOLUMNY:
1=ColumnaNameEasy(NR.KOLUMNY)

Powyższe wywołanie będzie tożsame z wywołaniem funkcji z wariantu 1- w komórce, w której wstawimy połączone funkcje zwrócony zostanie symbol kolumny dla tej właśnie komórki, a więc dla komórki C20 otrzymamy C, dla AA10 - AA, a dla XYZ14- XYZ.

Wskazówka! Oba rozwiązania można połączyć w jedną uniwersalną funkcję z opcjonalnym parametrem. Techniki tej uczymy w czasie prowadzonych szkoleń z zakresu VBA dla Excela, szczególnie prowadząc kurs VBA dla analityków i finansistów.

Brak komentarzy:

Prześlij komentarz