Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Jak odwoływać się do tabel Excela (ListObjects) w VBA?

Maciej Gonet

Dostęp do tabel Excela z poziomu VBA, to zawsze było dla mnie wyzwanie.
Trudno mi było zaakceptować, że tabela jako taka (ListObject), a także jej elementy (ListRows, ListColumns) nie są wprost zakresami komórek, tylko trzeba je do tego adaptować.

Po kilku latach zdecydowałem się zebrać najważniejsze informacje o użyciu tabel w VBA w jednym miejscu, bo M$ jak zwykle skąpi przykładów. Pozbierałem opisy dostępne w sieci i to co sam zweryfikowałem i załączam tutaj tekst, który może komuś też pomoże uporządkować tę wiedzę.

Miłej lektury! Oczywiście uzupełnienia i komentarze też będą mile widziane.

Tabele Excela z perspektywy języka VBA.docx

Pobierz Plik ściągnięto 142 raz(y) 39.23 KB

Rafał B. · Wysłany: 08-02-2024, 19:51

Taka małe uwagi/wskazówki z perspektywy bardziej praktycznej niż teoretycznej:

Cytat:

Podstawową zaletą tabel Excela jest ich elastyczność i automatyzm działania. Dodawanie wierszy w obrębie tabeli lub w strefie przyległej jak też dodawanie danych w wierszu pod tabelą powoduje automatyczne włączenie ich do tabeli.

Przy "ręcznej" interakcji ze skoroszytem tak - zawsze tabela się sama "rozszerza". Natomiast w przypadku VBA niekoniecznie. Nie wiem czy to jakiś sporadyczny bug, albo koincydencja jakichś przypadków; w każdym razie u dwóch różnych klientów na dwóch różnych projektach zaobserwowałem to niepożądane (dość rzadkie) zachowanie na przestrzeni kilku lat mojej pracy z VBA.

Nie wnikałem w przyczyny (może jakieś wyłączenie automatycznych kalkulacji, eventów, nie mam pojęcia), po prostu dla świętego spokoju zawsze dopisuję linijkę sprawdzającą Range tabeli i robię .Resize jeśli coś się nie zgadza, co też sugeruję tutaj deweloperom, szczególnie tym, którzy udostępniają swoja aplikację Excel+VBA na wiele stanowisk.

A i dla jasności: nie mówię tutaj o wykorzystaniu `ListRows.Add`, ale dopisaniu przez VBA danych tuż poniżej tabeli na zasadzie `Range("A5:A10").Value = mojaTablicaWartości`. Bo to ListRows.Add jest ładne i pożądane, ale jednak dość wolne w pętli.

---

Druga sprawa dość prosta, ale trzeba czasem uważać. Pamiętajmy, że nazwy nagłówków w tabeli są po pierwsze unikalne, po drugie nawet przez moment nie mogą być puste (pusty string). Niestety zamiast rzucania wyjątkami Excel automatycznie sobie nazywa po swojemu te nagłówki w sytuacji konfliktowej, co może rodzić problemy. Nazewnictwo to zależy od wersji językowej i dla naszej jest na zasadzie "Kolumna1", "Kolumna2", czasem dodaje sobie jakieś indeksy-sufiksy na koniec.

Przykład: metoda ListObject.ListColums.Add zwraca obiekt ListColumn. Rodzi to więc pokusę by w jednej linii dołączyć coś jeszcze do niej, najczęściej po prostu nazwę nowego nagłówka:

Kod:

ListObject.ListColums.Add(3).Name = "AAA"

' Tym kodem wykonujemy dwie akcje:
' (1). ListObject.ListColums.Add(3) dodaje kolumnę na trzecią pozycję w tabeli, otrzymujemy obiekt ListColumn. Excel automatycznie nazywa sobie na tym etapie kolumnę po swojemu np. `Kolumna1`: `.Name = "AAA"` jeszcze się nie uruchomiło (!!!) zawsze ten krok pośredni zachodzi

' (2). Dopiero w kolejnym kroku do tego obiektu chcemy przypisać właściwości "Name" = "AAA"
' jeśli jednak już istnieje kolumna "AAA" - nic się nie dzieje, zostaje tymczasowa nazwa kolumny z punktu 1, czyli np. `Kolumna1`

'
'... tutaj jakaś logika biznesowa
'

ListObject.ListColums("AAA").DataBodyRange.Value = tablicaWartosci

' jesli kolumna "AAA" już istniała wcześniej to zostanie wypełniona ta stara kolumna wartościami, a nie nowa kolumna dodana na początku tego kodu (bo nowa ma nazwę `Kolumna1`)

Wydaje się to dość banalne i oczywiste. Ale gdy przykładowo gromadzimy jakieś dane (np. zbieramy ceny produktów), a nagłówek jest wyznaczany dynamicznie jakąś funkcją, formułą, to może zrodzić dość ciężki do zdebugowania problem. A co gorsza- dane, które miały być archiwizowane byłyby w przypadku takich duplikatów nadpisywane, co przy braku jakiegoś częstego backupu byłoby ogromnym problemem spowodowanym przez tak trywialną przyczynę.

Taki bardziej realny przykład: klient mówi, ze będzie wgrywał stany magazynowe raz dziennie do tabeli w naszej aplikacji. Nasuwa się: nagłówkiem będzie bieżąca data. Ale oczywiście kiedyś z jakichś przyczyn klient się pomyli i wgra dane 2x w ciągu tego samego dnia błędnie myśląc, że dodadzą się oba zestawy danych do naszej tabeli, nie rozumiejąc mechanizmów VBA. Dlatego albo dodajmy sprawdzenie + ostrzeżenie czy nadpisać, albo po prostu zamiast daty dajmy coś na pewno unikalnego jak np. datę z czasem jako nagłówek.

TLTR
przed dodaniem kolumny dobrze upewnić się, czy już taki nagłówek nie istnieje, żeby tę sytuację świadomie obsłużyć. Oczywiście w tych przypadkach gdy coś tam dynamicznie w tych kolumnach mieszamy.

Maciej Gonet · Wysłany: 08-02-2024, 20:20

Bardzo dziękuję za cenny komentarz z punktu widzenia praktyki użycia ListObjects. Ja jestem w tej dziedzinie raczej teoretykiem, moje doświadczenia praktyczne niezbyt współgrają z najbardziej popularnymi zastosowaniami arkuszy kalkulacyjnych.

Artik · Wysłany: 20-02-2024, 03:35

Maciej Gonet · Wysłany: 20-02-2024, 09:20

Dziękuję za kolejne ciekawe spostrzeżenie! :danke