Co to jest tablica VBA w programie Excel i jak zaprogramować

VBA jest częścią Microsoft Office Suite od wielu lat. Chociaż nie ma pełnej funkcjonalności i mocy pełnej aplikacji VB, VBA zapewnia użytkownikom biurowym elastyczność w integracji produktów biurowych i automatyzacji pracy, którą wykonujesz.

Jednym z najpotężniejszych narzędzi dostępnych w VBA jest możliwość załadowania całego zakresu danych do jednej zmiennej o nazwie tablica. Ładując dane w ten sposób, możesz manipulować lub wykonywać obliczenia na tym zakresie danych na różne sposoby.

Możesz umieścić dane w każdej sekcji tego „pudełka” w dowolnej kolejności.

Na początku skryptu VBA musisz zdefiniować to „pudełko”, definiując swoją tablicę VBA. Aby utworzyć tablicę, która może pomieścić jeden zestaw danych (tablica jednowymiarowa), napiszesz następujący wiersz.

Dim ArmyArray (1 do 6) jako ciąg

W dalszej części programu możesz umieścić dane w dowolnej sekcji niniejszej tablicy, odwołując się do sekcji wewnątrz nawiasów.

Arrmyarray (1) = „Ryan Dube”

Możesz utworzyć dwuwymiarową tablicę za pomocą następującej wiersza.

Dim ArrMyarray (1 do 6,1 do 2) jako liczba całkowita

Pierwsza liczba reprezentuje wiersz, a drugi to kolumna. Więc powyższa tablica mogła pomieścić zakres z 6 rzędami i 2 kolumnami.

Możesz załadować dowolny element tej tablicy z danymi w następujący sposób.

ArmyArray (1,2) = 3

To załaduje 3 do komórki B1.

Tablica może pomieścić dowolny rodzaj danych jako zmienna zwykła, taka jak struny, logika, liczby całkowite, pływaki i inne.

Liczba wewnątrz nawiasu może być również zmienną. Programiści często używają pętli do liczenia przez wszystkie sekcje tablicy i ładowania komórek danych w arkuszu kalkulacyjnym do tablicy. Zobaczysz, jak to zrobić później w tym artykule.

Jak zaprogramować tablicę VBA w programie Excel

Rzućmy okiem na prosty program, w którym możesz załadować informacje z arkusza kalkulacyjnego do tablicy wielowymiarowej.

Na przykład spójrzmy na arkusz kalkulacyjny Sprzedaż produktów, w którym chcesz wyciągnąć nazwę, przedmiot i całkowitą sprzedaż z arkusza kalkulacyjnego.

Zwróć uwagę, że w VBA, gdy odwołujesz się do wierszy lub kolumn, liczysz wiersze i kolumny od lewej górnej części 1. Więc kolumna reprezentacji to 3, kolumna elementu to 4, a łączna kolumna to 7.

Aby załadować te trzy kolumny na wszystkich 11 wierszy, musisz napisać następujący skrypt.

Dim Armyarray (1 do 11, 1 do 3) jako ciąg
Dim ja jako liczba całkowita, j jako liczba całkowita

Dla i = 2 do 12
Dla j = 1 do 3
Arrmyarray (I-1, J) = Komórki (I, J).Wartość
Następny J
Następnie ja

Zauważysz, że aby pominąć wiersz nagłówka, numer wiersza w pierwszym dla wyglądu musi zacząć od 2 niż 1. Oznacza to, że musisz odjąć 1 dla wartości wiersza tablicy, gdy ładujesz wartość komórki do tablicy za pomocą komórek (i, j).Wartość.

Gdzie wstawić skrypt tablicy VBA

Aby umieścić program VBA w programie Excel, musisz użyć edytora VBA. Możesz uzyskać do tego dostęp, wybierając Deweloper menu i wybór Zobacz kod w części kontroli wstążki.

Jeśli nie widzisz programisty w menu, musisz go dodać. Aby to zrobić, wybierz Plik I Opcje Aby otworzyć okno opcji Excel.

Zmień polecenia wybierz z rozwijania na Wszystkie polecenia. Wybierać Deweloper z lewego menu i wybierz Dodać przycisk, aby przenieść go do panelu po prawej stronie. Wybierz pole wyboru, aby go włączyć i wybierz OK skończyć.

Po otwarciu okna edytora kodu upewnij się, że arkusz są wybierane w lewym panelu. Wybierać Arkusz roboczy w lewym rozwijaniu i Aktywuj po prawej. To stworzy nowy podprogram o nazwie WorkSheet_activate ().

Ta funkcja będzie działać za każdym razem, gdy plik arkusza kalkulacyjnego zostanie otwarty. Wklej kod do panelu skryptowego wewnątrz tego podprogramu.

Ten skrypt będzie działał przez 12 wierszy i załaduje nazwę przedstawiciela z kolumny 3, element z kolumny 4 i całkowitej sprzedaży z kolumny 7.

Po zakończeniu obu pętli, dwukierunkowe Array ArrmyArray zawiera wszystkie dane określone z oryginalnego arkusza.

Manipulowanie tablic w Excel VBA

Załóżmy, że chcesz zastosować 5% podatku od sprzedaży do wszystkich ostatecznych cen sprzedaży, a następnie napisać wszystkie dane do nowego arkusza.

Możesz to zrobić, dodając kolejną pętlę po pierwszym, z poleceniem do napisania wyników do nowego arkusza.

Dla k = 2 do 12
Arkusze („arkusz 2”).Komórki (K, 1).Wartość = ArrMyarray (K - 1, 1)
Arkusze („arkusz 2”).Komórki (K, 2).Wartość = arrMyarray (k - 1, 2)
Arkusze („arkusz 2”).Komórki (K, 3).Wartość = arrMyarray (k - 1, 3)
Arkusze („arkusz 2”).Komórki (K, 4).Wartość = ArrMyarray (K - 1, 3) * 0.05
Następnie k

To „rozładuje” całą tablicę na arkusz2, z dodatkowym wierszem zawierającym całkowitą mnożenie przez 5% dla kwoty podatkowej.

Powstały arkusz będzie wyglądał tak.

Jak widać, tablice VBA w programie Excel są niezwykle przydatne i tak wszechstronne jak każda inna sztuczka Excel. 

Powyższy przykład jest bardzo prostym zastosowaniem tablicy. Możesz tworzyć znacznie większe tablice i wykonywać sortowanie, uśrednianie lub wiele innych funkcji w stosunku do danych, które w nich przechowujesz.

Jeśli chcesz stać się prawdziwym kreatywnością, możesz nawet stworzyć dwie tablice zawierające szereg komórek z dwóch różnych arkuszy i wykonać obliczenia między elementami każdej tablicy.

Aplikacje są ograniczone tylko przez twoją wyobraźnię.