W tym artykule pokażemy, jak utworzyć zakres dynamiczny w Excel VBA.
Zadeklarowanie określonego zakresu komórek jako zmiennej w Excel VBA ogranicza nas do pracy tylko z tymi konkretnymi komórkami. Deklarując zakresy dynamiczne w programie Excel, zyskujemy znacznie większą elastyczność w zakresie naszego kodu i funkcjonalności, które może on wykonywać.
Zakresy i komórki odniesienia
Kiedy odwołujemy się do obiektu Range lub Cell w programie Excel, zwykle odwołujemy się do nich, umieszczając na stałe w żądanym wierszu i kolumnach.
Zakres nieruchomości
Korzystając z właściwości zakresu, w przykładowych wierszach kodu poniżej, możemy wykonać działania na tym zakresie, takie jak zmiana koloru komórek lub pogrubienie komórek.
| 12 | Zakres("A1:A5").Kolor czcionki = vbRedZakres("A1:A5").Czcionka.Pogrubienie = Prawda |
Właściwość komórek
Podobnie możemy użyć właściwości Cells, aby odnieść się do zakresu komórek, bezpośrednio odwołując się do wiersza i kolumny we właściwości cell. Wiersz musi być zawsze liczbą, ale kolumna może być liczbą lub literą ujętą w cudzysłów.
Na przykład adres komórki A1 może mieć postać:
| 1 | Komórki(1,1) |
Lub
| 1 | Komórki(1, "A") |
Aby użyć właściwości Cells do odwoływania się do zakresu komórek, musimy wskazać początek zakresu i koniec zakresu.
Na przykład do zakresu odniesienia A1: A6 możemy użyć poniższej składni:
| 1 | Zakres (komórki (1,1), komórki (1,6) |
Następnie możemy użyć właściwości Cells, aby wykonać akcje na zakresie zgodnie z przykładowymi wierszami kodu poniżej:
| 12 | Range(Cells(2, 2), Cells(6, 2)).Font.Color = vbRedRange(Cells(2, 2), Cells(6, 2)).Font.Bold = True |
Zakresy dynamiczne ze zmiennymi
Ponieważ rozmiar naszych danych zmienia się w Excelu (tzn. używamy więcej wierszy i kolumn niż zakresy, które zakodowaliśmy), dobrze byłoby, gdyby zmieniły się również zakresy, do których odwołujemy się w naszym kodzie. Korzystając z powyższego obiektu Range, możemy tworzyć zmienne do przechowywania maksymalnej liczby wierszy i kolumn w obszarze arkusza programu Excel, którego używamy, i używać tych zmiennych do dynamicznego dostosowywania obiektu Range podczas działania kodu.
Na przykład
| 1234 | Dim lRow jako liczba całkowitaDim lCol jako liczba całkowitalRow = Zakres("A1048576").End(xlUp).RowlCol = Zakres("XFD1").End(xlToLeft).Column |
Ostatni wiersz w kolumnie
Ponieważ w arkuszu jest 1048576 wierszy, zmienna lRow przejdzie na dół arkusza, a następnie użyje specjalnej kombinacji klawisza End i klawisza strzałki w górę, aby przejść do ostatniego wiersza użytego w arkuszu - to da nam numer rzędu, który potrzebujemy w naszym asortymencie.
Ostatnia kolumna w rzędzie
Podobnie, lCol przejdzie do kolumny XFD, która jest ostatnią kolumną w arkuszu, a następnie użyj specjalnej kombinacji klawisza End i klawisza strzałki w lewo, aby przejść do ostatniej kolumny użytej w arkuszu - to da nam numer kolumny, której potrzebujemy w naszym asortymencie.
Dlatego, aby uzyskać cały zakres, który jest używany w arkuszu, możemy uruchomić następujący kod:
| 1234567891011 | Sub GetRange()Dim lRow jako liczba całkowitaDim lCol jako liczba całkowitaDim rng As RangelRow = Zakres("A1048576").End(xlUp).Row'użyj lRow, aby znaleźć ostatnią kolumnę w zakresielCol = Zakres("XFD" & lRow).End(xlToLeft).ColumnUstaw rng = Zakres(Komórki(1, 1), Komórki(lRow, lCol))'msgbox, aby pokazać nam zakresMsgBox "Zakres to " i rng.AddressNapis końcowy |
SpecialCells - LastCell
Możemy również użyć metody SpecialCells obiektu Range, aby uzyskać ostatni wiersz i kolumnę użyte w arkuszu.
| 123456789101112 | Dodatkowe użycieKomórekspecjalnych()Dim lRow jako liczba całkowitaDim lCol jako liczba całkowitaDim rng As RangeDim rngRozpocznij jako zakresUstaw rngBegin = Zakres("A1")lRow = rngBegin.SpecialCells(xlCellTypeLastCell).RowlCol = rngBegin.SpecialCells(xlCellTypeLastCell).KolumnaUstaw rng = Zakres(Komórki(1, 1), Komórki(lRow, lCol))'msgbox, aby pokazać nam zakresMsgBox "Zakres to " i rng.AddressNapis końcowy |
Używany zakres
Metoda używanego zakresu obejmuje wszystkie komórki, które zawierają wartości w bieżącym arkuszu.
| 123456 | Podrzędny przykład zakresu używanego()Dim rng As RangeUstaw rng = ActiveSheet.UsedRange'msgbox, aby pokazać nam zakresMsgBox "Zakres to " i rng.AddressNapis końcowy |
Bieżący region
Bieżący region różni się od UsedRange tym, że patrzy na komórki otaczające komórkę, którą zadeklarowaliśmy jako zakres początkowy (tzn. zmienną rngBegin w poniższym przykładzie), a następnie przegląda wszystkie komórki, które są „dołączone” lub powiązane do tej zadeklarowanej komórki. Jeśli pojawi się pusta komórka w wierszu lub kolumnie, CurrentRegion przestanie szukać dalszych komórek.
| 12345678 | Sub bieżący region()Dim rng As RangeDim rngRozpocznij jako zakresUstaw rngBegin = Zakres("A1")Ustaw rng = rngBegin.CurrentRegion'msgbox, aby pokazać nam zakresMsgBox "Zakres to " i rng.AddressNapis końcowy |
Jeśli użyjemy tej metody, musimy upewnić się, że wszystkie komórki w wymaganym zakresie są połączone bez pustych wierszy lub kolumn.
Nazwany zakres
Możemy również odwoływać się do nazwanych zakresów w naszym kodzie. Nazwane zakresy mogą być dynamiczne, o ile po aktualizacji lub wstawieniu danych nazwa zakresu może się zmienić, aby uwzględnić nowe dane.
Ten przykład zmieni czcionkę na pogrubioną dla nazwy zakresu „Styczeń”
| 12345 | Podzakres NazwaPrzykład()Dim rng jako zakresUstaw rng = Zakres("Styczeń")rng.Font.Pogrubienie = = PrawdaNapis końcowy |
Jak widać na poniższym obrazku, jeśli do nazwy zakresu zostanie dodany wiersz, nazwa zakresu zostanie automatycznie zaktualizowana, aby uwzględnić ten wiersz.
Jeśli następnie ponownie uruchomimy przykładowy kod, zakres, na który ma wpływ kod, będzie C5:C9, podczas gdy w pierwszym przypadku byłby to C5:C8.
Stoły
Możemy odwoływać się do tabel (kliknij, aby uzyskać więcej informacji o tworzeniu i manipulowaniu tabelami w VBA) w naszym kodzie. Gdy dane tabeli w programie Excel zostaną zaktualizowane lub zmienione, kod odnoszący się do tabeli będzie odnosić się do zaktualizowanych danych tabeli. Jest to szczególnie przydatne w przypadku odwoływania się do tabel przestawnych, które są połączone z zewnętrznym źródłem danych.
Używając tej tabeli w naszym kodzie, możemy odwoływać się do kolumn tabeli według nagłówków w każdej kolumnie i wykonywać akcje na kolumnie zgodnie z ich nazwą. Gdy wiersze w tabeli rosną lub maleją zgodnie z danymi, zakres tabeli odpowiednio się dostosuje, a nasz kod będzie nadal działał dla całej kolumny w tabeli.
Na przykład:
| 123 | Sub UsuńTabelaKolumna()ActiveWorkbook.Worksheets("Arkusz1").ListObjects("Tabela1").ListColumns("Dostawca").DeleteNapis końcowy |
