Tablice VBA w programie Excel: typy i sposób użycia z przykładem

Przez: Charlotte Miller Charlotte Miller
Hours Zaktualizowano

⚡ Inteligentne podsumowanie

Tablice VBA w Excelu przechowują grupę powiązanych wartości tego samego typu danych pod jedną nazwą, do której dostęp uzyskuje się za pomocą indeksu. Ta strona wyjaśnia tablice statyczne i dynamiczne, tablice jedno-, dwu- i wielowymiarowe, składnię deklaracji oraz przykład ładowania napojów.

  • 📦 Koncepcja tablicy: Pojedyncza nazwana lokalizacja w pamięci, która przechowuje wiele wartości tego samego typu danych, oddzielonych indeksem.
  • 🎯 Zalety: Tablice grupują powiązane dane, redukują liczbę zmiennych i przyspieszają pobieranie i sortowanie danych.
  • 📌 Tablica statyczna: Deklarowane ze stałym rozmiarem, na przykład Dim ArrayMonth(12) As String, gdy znana jest liczba elementów.
  • 🔄 Tablica dynamiczna: Zadeklarowano jako puste za pomocą Dim ArrayMonth(), a następnie zmieniono rozmiar za pomocą ReDim, gdy liczba elementów jest nieznana.
  • 📐 Wymiary: Tablica używa jednego, dwóch lub więcej indeksów do modelowania pojedynczych list, tabel lub danych wielopoziomowych.
  • 🧪 Przykład rozwiązania: Przycisk polecenia ładuje cztery nazwy napojów z tablicy do komórek arkusza kalkulacyjnego przy użyciu Sheet1.Cells.
Czytaj więcej

Tablice Excela VBA

Co to jest tablica VBA?

Tablicę definiuje się jako miejsce w pamięci, w którym można przechowywać więcej niż jedną wartość. Wszystkie wartości muszą być tego samego typu danych. Powiedzmy, że chcesz przechowywać listę swoich ulubionych napojów w jednej zmiennej, możesz do tego użyć tablicy VBA.

Używając tablicy, możesz odwoływać się do powiązanych wartości o tej samej nazwie. Aby je rozróżnić, możesz użyć indeksu lub indeksu dolnego. Poszczególne wartości nazywane są elementami tablicy Excel VBA. Są one ciągłe od indeksu 0 do najwyższej wartości indeksu.

W tym samouczku założono, że używasz Microsoft przewyższać wersja 2013. Wiedza ta nadal dotyczy pozostałych wersji Microsoft Excela również.

Jakie są zalety tablic?

Zanim napiszesz kod tablicowy, warto zrozumieć, dlaczego warto z nich korzystać. Poniżej przedstawiono niektóre korzyści oferowane przez funkcję tablicową VBA.

  1. Grupuj logicznie powiązane dane — powiedzmy, że chcesz przechowywać listę uczniów. Możesz użyć pojedynczej zmiennej tablicowej, która ma osobne lokalizacje dla kategorii uczniów, np. przedszkole, szkoła podstawowa, średnia, średnia itp.
  2. Tablice ułatwiają pisanie łatwego w utrzymaniu kodu. Dla tych samych logicznie powiązanych danych pozwala zdefiniować jedną zmienną, zamiast definiować więcej niż jedną zmienną.
  3. Lepsza wydajność – po zdefiniowaniu tablicy można szybciej pobierać, sortować i modyfikować dane.

Rodzaje tablic w VBA

VBA obsługuje dwa typy tablic, a mianowicie;

  • Statyczny – Tego typu tablice mają stałą, z góry określoną liczbę elementów, które można przechowywać. Nie można zmienić rozmiaru typu danych tablicy statycznej. Są one przydatne, gdy chcesz pracować ze znanymi jednostkami, takimi jak liczba dni w tygodniu, płeć itp.Na przykład: Dim ArrayMonth(12) jako ciąg znaków
  • Dynamiczny – Tablice tego typu nie mają ustalonej, z góry określonej liczby elementów, które można przechowywać. Są one przydatne podczas pracy z elementami, których liczby nie można z góry określić.Na przykład: Dim ArrayMonth() jako wariant

Gdy już wiesz, który typ pasuje do Twoich danych, następnym krokiem jest składnia deklaracji dla każdego z nich.

Składnia deklarowania tablic

Tablice statyczne

Składnia deklarowania STATYCZNY tablice wygląda następująco:

Dim arrayName (n) as datatype

TUTAJ,

Code Działania
Dim arrayName (n) typ danych
  1. Deklaruje zmienną tablicową o nazwie arrayName o rozmiarze n i typie danych. Rozmiar odnosi się do liczby elementów, które tablica może przechowywać.

Tablice dynamiczne

Składnia deklarowania DYNAMICZNY tablice wygląda następująco:

Dim arrayName() as datatype
ReDim arrayName(4)

TUTAJ,

Code Działania
Dim arrayName () typ danych
  1. Deklaruje zmienną tablicową o nazwie arrayName bez określania liczby elementów
Nazwa tablicy ReDim(4)
  1. Określa rozmiar tablicy po zdefiniowaniu tablicy.

Wymiary tablicy

Tablica może być jednowymiarowa, dwuwymiarowa lub wielowymiarowa.

  • Jeden wymiar: W tym wymiarze tablica używa tylko jednego indeksu. Na przykład pewna liczba osób w każdym wieku.
  • Dwa wymiary: W tym wymiarze tablica używa dwóch indeksów. Na przykład liczba uczniów w każdej klasie. Wymagana jest liczba klas i liczba uczniów w każdej klasie
  • Wielowymiarowy: W tym wymiarze tablica używa więcej niż dwóch indeksów. Na przykład temperatury w ciągu dnia. (30, 40, 20).
Nie przegap:

Jak korzystać z tablicy w Excel VBA

Po zapoznaniu się z teorią możemy wykorzystać tablicę w prawdziwym skoroszycie. Stworzymy prostą aplikację. Ta aplikacja wypełnia arkusz Excela danymi ze zmiennej tablicowej. W tym przykładzie z tablicą w VBA wykonamy następujące czynności.

  • Stwórz nowy Microsoft Skoroszyt programu Excel i zapisz go jako skoroszyt programu Excel z obsługą makr (*.xlsm)
  • Dodaj przycisk polecenia do skoroszytu
  • Ustaw nazwę i właściwości podpisu przycisku polecenia
  • Programowanie VBA wypełniającego arkusz Excela

Zróbmy to ćwiczenie krok po kroku,

Krok 1 – Utwórz nowy skoroszyt

  1. Otwarte Microsoft przewyższać
  2. Zapisz nowy skoroszyt jako VBA Arrays.xlsm

Krok 2 – Dodaj przycisk poleceń

Uwaga: W tej sekcji założono, że znasz proces tworzenia interfejsu w programie Excel. Jeśli nie jesteś zaznajomiony, przeczytaj tutorial Kontrola formularzy VBA Excel i kontrola ActiveX. Pokaże Ci jak stworzyć interfejs

  1. Dodaj przycisk polecenia do arkusza

Użyj tablicy w Excel VBA

  1. Ustaw właściwość name na cmdLoadBeverages
  2. Ustaw właściwość podpisu na Załaduj napoje

Twój GUI powinien teraz wyglądać następująco

Użyj tablicy w Excel VBA

Krok 3 – Zapisz plik

  1. Kliknij przycisk Zapisz jako
  2. Wybierz Skoroszyt programu Excel z obsługą makr (*.xlsm), jak pokazano na obrazku poniżej

Użyj tablicy w Excel VBA

Krok 4 – Napisz kod

Napiszemy teraz kod naszej aplikacji

  1. Kliknij prawym przyciskiem myszy przycisk Załaduj napoje i wybierz opcję Wyświetl kod
  2. Dodaj następujący kod do zdarzenia kliknięcia cmdLoadBeverages
Private Sub cmdLoadBeverages_Click()
    Dim Drinks(1 To 4) As String

    Drinks(1) = "Pepsi"
    Drinks(2) = "Coke"
    Drinks(3) = "Fanta"
    Drinks(4) = "Juice"

    Sheet1.Cells(1, 1).Value = "My Favorite Beverages"
    Sheet1.Cells(2, 1).Value = Drinks(1)
    Sheet1.Cells(3, 1).Value = Drinks(2)
    Sheet1.Cells(4, 1).Value = Drinks(3)
    Sheet1.Cells(5, 1).Value = Drinks(4)
End Sub

TUTAJ,

Code Działania
Ciemne napoje (1 do 4) jako ciąg
  • Deklaruje zmienną tablicową o nazwie Drinks. Pierwszy indeks tablicy to 1, a ostatni indeks tablicy to 4.
Napoje(1) = „Pepsi”
  • Przypisuje wartość Pepsi do pierwszego elementu tablicy. Drugi podobny kod robi to samo dla pozostałych elementów tablicy.
Sheet1.Cells(1, 1).Value = „Moje ulubione napoje”
  • Zapisuje wartość Moje ulubione napoje w adresie komórki A1. Arkusz1 odwołuje się do arkusza, a Cells(1,1) odwołuje się do wiersza numer 1 i kolumny 1 (B)
Arkusz1.Komórki(2, 1).Wartość = Napoje(1)
  • Zapisuje wartość elementu tablicy o indeksie 1 w drugim wierszu kolumny 1

Testowanie naszej aplikacji

Po wstawieniu kodu, ostatnim krokiem jest uruchomienie przycisku i potwierdzenie, że tablica wypełnia arkusz. Wybierz kartę Deweloper i upewnij się, że przycisk Tryb projektowania jest wyłączony. Wskaźnik wskazuje, że będzie miał białe tło, a nie kolorowe (zielonkawe). (Zobacz poniższy obrazek)

Testowanie aplikacji w Excel VBA

Kliknij przycisk Załaduj napoje

Otrzymasz następujące wyniki

Testowanie aplikacji w Excel VBA

Pobierz Excel zawierający powyższy kod

Pobierz powyższy plik Excel Code

Nie przegap:

FAQ

Tablica statyczna ma stały rozmiar ustawiony podczas deklaracji, na przykład Dim Arr(12). Tablica dynamiczna jest deklarowana jako pusta za pomocą Dim Arr(), a jej rozmiar jest później zmieniany za pomocą ReDim, gdy liczba elementów nie jest znana z góry.

ReDim ustawia lub zmienia rozmiar tablicy dynamicznej po jej zadeklarowaniu. Dodanie słowa kluczowego Preserve, jak w ReDim Preserve Arr(10), zachowuje istniejące wartości podczas zmiany rozmiaru; bez Preserve wszystkie zapisane wartości są czyszczone.

Użyj funkcji LBound i UBound. LBound zwraca najniższy indeks, a UBound zwraca najwyższy. Liczba elementów równa się UBound(Arr) minus LBound(Arr) plus jeden, co działa zarówno dla tablic statycznych, jak i dynamicznych.

Tak. Asystenci AI, tacy jak Copilot, generują deklaracje tablicowe i pętle na podstawie prostego żądania, na przykład ładują dziesięć nazw produktów do kolumny. Programista wkleja kod do edytora i testuje go na kopii skoroszytu.

Tak. Asystenci AI odczytują błąd „Subscript out of range” oraz kod, a następnie wskazują indeks, który przekracza granice tablicy i sugerują rozwiązanie, takie jak dostosowanie rozmiaru ReDim lub licznika pętli. Programista stosuje to i ponownie testuje.

Podsumuj ten post następująco: