VBA jest częścią pakietu Microsoft Office od wielu lat. Chociaż nie ma pełnej funkcjonalności i mocy pełnej aplikacji VB, VBA zapewnia użytkownikom Office elastyczność w integracji produktów Office i automatyzacji wykonywanej w nich pracy.
Jedno z najpotężniejszych narzędzi dostępna w VBA jest możliwość załadowania całego zakresu danych do jednej zmiennej zwanej tablicą. Ładując dane w ten sposób, możesz manipulować lub wykonywać obliczenia na tym zakresie danych na różne sposoby.
Czym jest tablica VBA? W tym artykule odpowiemy na to pytanie i pokażemy, jak używać jednego z własny skrypt VBA.
Co to jest tablica VBA?
Używanie VBA Tablica w programie Excel jest bardzo prosta, ale zrozumienie pojęcia tablic może być nieco skomplikowane, jeśli nigdy nie byłeś używany.
Pomyśl o tablicy jak o polu zawierającym sekcje. Tablica jednowymiarowa to pudełko z jedną linią przekrojów. Dwuwymiarowa tablica to pudełko z dwoma liniami sekcji.
Możesz umieścić dane w każdej sekcji tego „pudełka” w dowolnej kolejności.
In_content_1 all: [300x250] / dfp: [640x360]->Na początku skryptu VBA musisz zdefiniować to „pudełko” poprzez zdefiniowanie tablicy VBA. Aby utworzyć tablicę, która może pomieścić jeden zestaw danych (tablicę jednowymiarową), należy napisać następujący wiersz.
Dim arrMyArray(1 To 6) As String
Później w programie można umieścić dane do dowolnej sekcji tej tablicy, odwołując się do sekcji w nawiasach.
arrMyArray(1) = "Ryan Dube"
Możesz utworzyć tablicę dwuwymiarową za pomocą następującego wiersza.
Dim arrMyArray(1 To 6,1 to 2) As Integer
Pierwsza liczba reprezentuje wiersz, a druga kolumna. Tak więc powyższa tablica może zawierać zakres z 6 wierszami i 2 kolumnami.
Możesz załadować dowolny element tej tablicy z danymi w następujący sposób.
arrMyArray(1,2) = 3
Spowoduje to załadowanie 3 do komórki B1.
Tablica może przechowywać dowolny typ danych jako zwykłą zmienną, taką jak łańcuchy, wartość logiczna, liczby całkowite, zmiennoprzecinkowe i inne.
liczba w nawiasie może być również zmienną. Programiści często używają pętli For do liczenia wszystkich sekcji tablicy i ładowania komórek danych z arkusza kalkulacyjnego do tablicy. Zobaczymy, jak to zrobić w dalszej części tego artykułu.
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 wielowymiarowej tablicy.
Na przykład spójrzmy na arkusz sprzedaży produktu, w którym chcesz wyciągnąć nazwę przedstawiciela handlowego, pozycję i całkowitą sprzedaż z arkusza kalkulacyjnego.
Zauważ, że w VBA, kiedy odwołujesz się do wierszy lub kolumn, liczysz wiersze i kolumny, zaczynając od lewego górnego rogu na 1. Więc kolumna powtórzeń to 3, kolumna pozycji to 4, a całkowita kolumna to 7.
Aby załadować te trzy kolumny we wszystkich 11 wierszach, musisz napisać następujący skrypt.
Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer
For i = 2 To 12
For j = 1 To 3
arrMyArray(i-1, j) = Cells(i, j).Value
Next j
Next i
Zauważysz, że aby pominąć wiersz nagłówka, numer pierwszego wiersza Dla look musi zaczynać się od 2, a nie 1. Oznacza to, że musisz odjąć 1 dla wartości wiersza tablicy podczas ładowania wartość komórki do tablicy za pomocą Cells (i, j) .Value.
Gdzie wstawić skrypt VBA Array
Aby umieścić program VBA w Excelu, musisz użyć Edytor VBA. Możesz uzyskać do niego dostęp, wybierając menu Deweloperi wybierając Wyświetl kodw sekcji Kontroli wstążki.
Jeśli nie widzisz Dewelopera w menu, musisz dodaj to. Aby to zrobić, wybierz Pliki Opcje, aby otworzyć okno Opcje programu Excel.
Zmień polecenia wyboru z menu rozwijanego na Wszystkie polecenia. Wybierz Deweloperz menu po lewej stronie i wybierz przycisk Dodaj, aby przenieść go do panelu po prawej stronie. Zaznacz pole wyboru, aby je włączyć, i wybierz OK, aby zakończyć.
Po otwarciu okna Edytora kodu upewnij się, że arkusz, w którym znajdują się Twoje dane, jest zaznaczony w lewym okienku. Wybierz Arkuszw lewym menu rozwijanym i Aktywujw prawym. Spowoduje to utworzenie nowego podprogramu o nazwie Worksheet_Activate ().
Ta funkcja będzie działać przy każdym otwarciu pliku arkusza kalkulacyjnego. Wklej kod do panelu skryptu 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 oraz całkowitą sprzedaż z kolumny 7.
Po zakończeniu obu pętli For, dwuwymiarowa tablica arrMyArray zawiera wszystkie dane określone z oryginalnego arkusza.
Manipulowanie tablicami w Excel VBA
Załóżmy, że chcesz zastosować 5% podatek od sprzedaży do wszystkich ostatecznych cen sprzedaży , a następnie zapisz wszystkie dane na nowym arkuszu.
Możesz to zrobić, dodając kolejną pętlę For po pierwszej, z poleceniem zapisania wyników na nowym arkuszu.
For k = 2 To 12
Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k
Spowoduje to „rozładowanie” całej tablicy do arkusza 2, z dodatkowym wierszem zawierającym sumę pomnożoną przez 5% kwoty podatku.
wynikowy arkusz będzie wyglądał następująco.
Jak widać, tablice VBA w programie Excel są niezwykle przydatne i tak wszechstronne, jak każda inna sztuczka z Excela.
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 względem przechowywanych w nich danych.
Jeśli chcesz być naprawdę kreatywny, możesz nawet utworzyć dwie tablice zawierający zakres komórek z dwóch różnych arkuszy i wykonuj obliczenia między elementami każdej tablicy.
Aplikacje są ograniczone tylko twoją wyobraźnią.