Zakresy i komórki Excela VBA

Zakresy i komórki w VBA

Arkusze kalkulacyjne Excel przechowują dane w komórkach. Komórki są ułożone w wiersze i kolumny. Każdą komórkę można zidentyfikować na podstawie punktu przecięcia jej wiersza i kolumny (np. B3 lub R3C2).

Zakres Excela odnosi się do jednej lub więcej komórek (np. A3:B4)

Adres komórki

Notacja A1

W notacji A1 komórka jest określana literą kolumny (od A do XFD), po której następuje numer wiersza (od 1 do 1 048 576).

W VBA możesz odwołać się do dowolnej komórki za pomocą Obiekt zakresu.

123456789

'Odwołaj się do komórki B4 w aktualnie aktywnym arkuszuZakres MsgBox("B4")' Odwołaj się do komórki B4 w arkuszu o nazwie 'Dane'Arkusze MsgBox("Dane").Range("B4")' Odwołaj się do komórki B4 w arkuszu o nazwie 'Dane' w innym skoroszycie OPENo nazwie „Moje dane”MsgBox Workbooks("Moje dane").Worksheets("Dane").Range("B4")

Notacja R1C1

W notacji R1C1 komórka jest oznaczona literą R, po której następuje numer wiersza, a następnie litera „C”, a następnie numer kolumny. np. B4 w notacji R1C1 będzie odnosić się do R4C2. W VBA używasz Obiekt komórek używać notacji R1C1:

12	' Odnieś się do komórki R[6]C[4] tj. D6Komórki (6, 4) = "D6"

Zakres komórek

Notacja A1

Aby odwołać się do więcej niż jednej komórki, użyj „:” między początkowym adresem komórki a adresem ostatniej komórki. Poniższe będą dotyczyć wszystkich komórek od A1 do D10:

1	Zakres("A1:D10")

Notacja R1C1

Aby odwołać się do więcej niż jednej komórki, użyj „,” między początkowym adresem komórki a adresem ostatniej komórki. Poniższe będą dotyczyć wszystkich komórek od A1 do D10:

1	Zakres (komórki (1, 1), komórki (10, 4))

Pisanie do komórek

Aby zapisać wartości w komórce lub ciągłej grupie komórek, po prostu odwołaj się do zakresu, umieść znak =, a następnie wpisz wartość, która ma być przechowywana:

12345678910

'Przechowuj F5 w komórce z adresem F6Zakres("F6") = "F6"' Przechowuj E6 w komórce z adresem R[6]C[5], czyli E6Komórki(6, 5) = "E6""Przechowuj A1: D10 w zakresie A1: D10"Zakres("A1:D10") = "A1:D10"' lubZakres(Komórki(1, 1), Komórki(10, 4)) = "A1:D10"

Czytanie z komórek

Aby odczytać wartości z komórek, po prostu odwołaj się do zmiennej, aby przechowywać wartości, umieść znak =, a następnie odnieś się do zakresu do odczytania:

1234567891011

Wart ściemniania1Wartość ściemniania2' Odczytaj z komórki F6wart1 = Zakres("F6")'Odczytaj z komórki E6wart2 = komórki(6, 5)MsgBox wart1Msgbox Val2

Uwaga: Aby przechowywać wartości z zakresu komórek, musisz użyć tablicy zamiast prostej zmiennej.

Komórki nieciągłe

Aby odwołać się do nieciągłych komórek, użyj przecinka między adresami komórek:

123456

' Przechowuj 10 w komórkach A1, A3 i A5Zakres("A1,A3,A5") = 10' Przechowuj 10 w komórkach A1:A3 i D1:D3)Zakres("A1:A3, D1:D3") = 10

Przecięcie komórek

Aby odwołać się do nieciągłych komórek, użyj spacji między adresami komórek:

123	' Przechowuj 'Col D' w D1:D10' co jest wspólne dla A1:D10 i D1:F10Zakres("A1:D10 D1:G10") = "Kol D"

Odsunięcie od komórki lub zakresu

Korzystając z funkcji Offset, możesz przenieść odwołanie z danego Zakresu (komórki lub grupy komórek) o określoną liczbę_wierszy i liczbę_kolumn.

Składnia przesunięcia

Zakres.Przesunięcie(liczba_wierszy, liczba_kolumn)

Przesunięcie od komórki

12345678910111213141516

' PRZESUNIĘCIE z komórki A1'Odnieś się do samej komórki' Przenieś 0 wierszy i 0 kolumnZakres("A1").Przesunięcie(0,0) = "A1"' Przenieś 1 wiersze i 0 kolumnZakres("A1").Przesunięcie(1,0) = "A2"' Przenieś 0 wierszy i 1 kolumnZakres("A1").Przesunięcie(0, 1) = "B1"' Przenieś 1 wiersz i 1 kolumnęZakres("A1").Przesunięcie(1, 1) = "B2"' Przenieś 10 wierszy i 5 kolumnZakres("A1").Przesunięcie(10, 5) = "F11"

Odsunięcie od zakresu

123	' Przenieś odniesienie do zakresu A1:D4 o 4 wiersze i 4 kolumny'Nowe odniesienie to E5:H8Zakres("A1:D4").Przesunięcie(4,4) = "E5:H8"

Ustawianie odniesienia do zakresu

Aby przypisać zakres do zmiennej zakresu: zadeklaruj zmienną typu Range, a następnie użyj polecenia Set, aby ustawić ją na zakres. Pamiętaj, że musisz użyć polecenia SET, ponieważ RANGE jest obiektem:

12345678

' Zadeklaruj zmienną zakresuPrzyciemnij mój zakres jako zakres' Ustaw zmienną na zakres A1:D4Ustaw mójZakres = Zakres("A1:D4")' Drukuje $A$1:$D$4MsgBox mójZakres.Adres

Zmień rozmiar zakresu

Metoda zmiany rozmiaru obiektu Range zmienia wymiar zakresu odniesienia:

1234567

Przyciemnij mój zakres jako zakres'Zakres do zmiany rozmiaruUstaw mójZakres = Zakres("A1:F4")' Drukuje $A$1:$E$10Debug.Print myRange.Resize(10, 5).Adres

Lewa górna komórka zakresu zmienionego rozmiaru jest taka sama jak lewa górna komórka oryginalnego zakresu

Zmień rozmiar składni

Range.Resize(liczba_wierszy, liczba_kolumn)

PRZESUNIĘCIE a zmiana rozmiaru

Przesunięcie nie zmienia wymiarów zakresu, ale przesuwa go o określoną liczbę wierszy i kolumn. Zmiana rozmiaru nie zmienia pozycji oryginalnego zakresu, ale zmienia wymiary na określoną liczbę wierszy i kolumn.

Wszystkie komórki w arkuszu

Obiekt Cells odnosi się do wszystkich komórek w arkuszu (1048576 wierszy i 16384 kolumn).

12	' Wyczyść wszystkie komórki w arkuszach roboczychKomórki.Wyczyść

Używany zakres

Właściwość UsedRange podaje prostokątny zakres od lewej górnej używanej komórki do prawej dolnej używanej komórki aktywnego arkusza.

1234567

Dim ws As WorksheetUstaw ws = Aktywny arkusz' $B$2:$L$14, jeśli L2 jest pierwszą komórką z dowolną wartością' i L14 to ostatnia komórka z dowolną wartością na' aktywny arkuszDebug.Print ws.UsedRange.Address

Bieżący region

Właściwość CurrentRegion zapewnia ciągły prostokątny zakres od lewej górnej komórki do prawej dolnej używanej komórki zawierającej komórkę/zakres, do którego istnieje odwołanie.

1234567891011

Przyciemnij mój zakres jako zakresUstaw mójZakres = Zakres("D4:F6")'Wydruki $B$2:$L$14' Jeśli jest wypełniona ścieżka z D4:F16 do B2 I L14Debug.Print myRange.CurrentRegion.Address' Możesz również odwołać się do pojedynczej komórki początkowejUstaw mójZakres = Zakres("D4") ' Drukuje $B$2:$L$14

Właściwości zakresu

Możesz uzyskać adres, numer wiersza / kolumny komórki i liczbę wierszy / kolumn w zakresie, jak podano poniżej:

123456789101112131415161718192021

Przyciemnij mój zakres jako zakresUstaw mójZakres = Zakres("A1:F10")' Drukuje $A$1:$F$10Debug.Drukuj mójZakres.AdresUstaw mójZakres = Zakres("F10")' Drukuje 10 dla rzędu 10Debug.Print myRange.Row' Drukuje 6 dla kolumny FDebug.Print myRange.ColumnUstaw mójZakres = Zakres("E1:F5")'Wypisuje 5 dla liczby wierszy w zakresieDebug.Print myRange.Rows.Count'Wypisuje 2 dla liczby kolumn w zakresieDebug.Print myRange.Columns.Count

Ostatnia komórka w arkuszu

Możesz użyć Liczba wierszy oraz Kolumny.Liczba właściwości z Komórki obiekt, aby uzyskać ostatnią komórkę w arkuszu:

1234567891011

' Wydrukuj numer ostatniego wiersza'Wydruki 1048576Debug.Print "Wiersze w arkuszu: " & Rows.Count' Wydrukuj ostatni numer kolumny„Odbitki 16384”Debug.Print "Kolumny w arkuszu: " & Columns.Count' Wydrukuj adres ostatniej komórki' Wydruki $XFD$1048576Debug.Print "Adres ostatniej komórki w arkuszu: " & Cells(Rows.Count, Columns.Count)

Ostatni używany numer wiersza w kolumnie

Właściwość END przenosi nas do ostatniej komórki w zakresie, a End(xlUp) przenosi do pierwszej użytej komórki z tej komórki.

123	Dim lastRow As LonglastRow = Cells(Rows.Count, "A").End(xlUp).Row

Numer ostatniej używanej kolumny w rzędzie

123	Dim lastCol As LonglastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Właściwość END przenosi nas do ostatniej komórki w zakresie, a End(xlToLeft) przenosi nas do pierwszej użytej komórki z tej komórki.

Możesz również użyć właściwości xlDown i xlToRight, aby przejść do pierwszej używanej dolnej lub prawej komórki bieżącej komórki.

Właściwości komórki

Wspólne właściwości

Oto kod do wyświetlania powszechnie używanych właściwości komórki

12345678910111213141516171819202122

Przyciemnij komórkę jako zakresUstaw komórkę = Zakres("A1")komórka.AktywujDebug.Drukuj komórkę.Adres' Drukuj $A$1Debug.Print cell.Value'Odbitki 456' AdresDebug.Drukuj komórkę.Formuła' Drukuje = SUMA(C2:C3)' KomentarzDebug.Drukuj komórkę.Komentarz.Tekst„Styl”Debug.Print cell.Style' Format komórkiDebug.Print cell.DisplayFormat.NumberFormat

Czcionka komórki

Obiekt Cell.Font zawiera właściwości czcionki komórki:

1234567891011121314151617181920

Przyciemnij komórkę jako zakresUstaw komórkę = Zakres("A1")' Regularna, kursywa, pogrubiona i pogrubiona kursywacell.Font.FontStyle = "Pogrubiona kursywa"' Taki sam jakcell.Font.Pogrubienie = Prawdacell.Font.Italic = Prawda' Ustaw czcionkę na Couriercell.Font.FontStyle = "Kurier"' Ustaw kolor czcionkikomórka.Czcionka.Kolor = vbBlue' lubkomórka.Czcionka.Kolor = RGB(255, 0, 0)' Ustaw rozmiar czcionkikomórka.Czcionka.Rozmiar = 20

Kopiuj i wklej

Wklej wszystko

Zakresy/komórki można kopiować i wklejać z jednej lokalizacji do drugiej. Poniższy kod kopiuje wszystkie właściwości zakresu źródłowego do zakresu docelowego (odpowiednik CTRL-C i CTRL-V)

1234567

„Prosta kopia”Zakres ("A1:D20").KopiujArkusze("Arkusz2").Zakres("B10").Wklej'lub' Kopiuj z bieżącego arkusza do arkusza o nazwie 'Arkusz2'Range("A1:D20").Miejsce docelowe kopiowania:=Worksheets("Arkusz2").Range("B10")

Wklej specjalnie

Wybrane właściwości zakresu źródłowego można skopiować do miejsca docelowego za pomocą opcji WKLEJ SPECJALNE:

123	' Wklej zakres tylko jako wartościZakres ("A1:D20").KopiujArkusze("Arkusz2").Range("B10").PasteSpecial Paste:=xlPasteValues

Oto możliwe opcje opcji Wklej:

12345678910111213

' Wklej typy specjalnexlWklej wszystkoxlWklej wszystkie z wyjątkiem obramowańxlWklej wszystkie scalające formaty warunkowexlPasteAllUsingSource ThemexlPasteColumnSzerokościxlWklejKomentarzexlPasteFormatyxlPasteFormułyxlPasteFormulasAndNumberFormatsxlWklejWalidacjaxlWklejWartościxlPasteValuesAndNumberFormats

Zawartość autodopasowania

Rozmiar wierszy i kolumn można zmienić, aby dopasować zawartość za pomocą poniższego kodu:

12345

' Zmień rozmiar wierszy od 1 do 5, aby dopasować zawartośćWiersze("1:5").Autodopasowanie' Zmień rozmiar kolumn od A do B, aby dopasować zawartośćKolumny("A:B").AutoFit

Więcej przykładów zakresu

Zaleca się korzystanie z Rejestratora makr podczas wykonywania wymaganej czynności za pośrednictwem GUI. Pomoże Ci zrozumieć różne dostępne opcje i jak z nich korzystać.

Dla każdego

Łatwiej jest przejść przez zakres za pomocą Dla każdego skonstruuj jak pokazano poniżej:

123	Dla każdej komórki w zakresie ("A1:B100")'Zrób coś z komórkąNastępna komórka

W każdej iteracji pętli jedna komórka z zakresu jest przypisywana do zmiennej c, a instrukcje w pętli For są wykonywane dla tej komórki. Pętla kończy się, gdy wszystkie komórki są przetwarzane.

Sortować

Sort to metoda obiektu Range. Zakres można posortować, określając opcje sortowania do Range.Sort. Poniższy kod posortuje kolumny A: C na podstawie klucza w komórce C2. Sort Order może być xlAscending lub xlDescending. Header:= xlYes należy stosować, jeśli pierwszy wiersz jest wierszem nagłówka.

12	Kolumny("A:C").Sort key1:=Range("C2"), _order1:=xlRosnąco, Nagłówek:=xlYes

Odnaleźć

Find jest również metodą Range Object. Znajduje pierwszą komórkę mającą zawartość pasującą do kryteriów wyszukiwania i zwraca komórkę jako obiekt Range. Wraca Nic jeśli nie ma dopasowania.

Posługiwać się Znajdź następny metoda (lub FindPrevious), aby znaleźć następne (poprzednie) wystąpienie.

Poniższy kod zmieni czcionkę na „Arial Black” dla wszystkich komórek w zakresie zaczynającym się od „John”:

12345

Dla każdego c W zakresie("A1:A100")Jeśli c Jak „Jan*” Wtedyc.Font.Name = "Arial Black"Zakończ, jeśliNastępny c

Poniższy kod zastąpi wszystkie wystąpienia „Do przetestowania” na „Zaliczone” w określonym zakresie:

12345678910

Z zakresem("a1:a500")Ustaw c = .Find("Do przetestowania", LookIn:=xlValues)Jeśli nie c, to nic, topierwszy adres = c.AdresRobićc.Wartość = "Zdane"Ustaw c = .Znajdźnastępny(c)Loop While Not c Is Nothing And c.Adres pierwszy adresZakończ, jeśliKończyć z

Ważne jest, aby pamiętać, że musisz określić zakres, aby użyć FindNext. Musisz także podać warunek zatrzymania, w przeciwnym razie pętla będzie wykonywana bez końca. Zwykle adres pierwszej znalezionej komórki jest przechowywany w zmiennej i pętla jest zatrzymywana, gdy ponownie dotrzesz do tej komórki. Musisz również sprawdzić przypadek, w którym nie znaleziono nic, co mogłoby zatrzymać pętlę.

Adres zakresu

Użyj Range.Address, aby uzyskać adres w stylu A1

123	Zakres MsgBox("A1:D10").Adres' lubDebug.Print Range("A1:D10").Adres

Użyj xlReferenceStyle (domyślnie xlA1), aby uzyskać adresy w stylu R1C1

123	MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)' lubDebug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1)

Jest to przydatne, gdy masz do czynienia z zakresami przechowywanymi w zmiennych i chcesz przetwarzać tylko określone adresy.

Zakres do tablicy

Szybciej i łatwiej jest przenieść zakres do tablicy, a następnie przetworzyć wartości. Tablicę należy zadeklarować jako Wariant, aby uniknąć obliczania rozmiaru wymaganego do wypełnienia zakresu w tablicy. Wymiary tablicy są ustawione tak, aby odpowiadały liczbie wartości w zakresie.

123456789

Dim DirArray jako wariant'Przechowuj wartości z zakresu do tablicyDirArray = Zakres("a1:a5").Value' Pętla do przetwarzania wartościDla każdego c In DirArrayDebug.Drukuj cNastępny

Tablica do zakresu

Po przetworzeniu możesz zapisać tablicę z powrotem do zakresu. Aby zapisać Array w powyższym przykładzie w Range, musisz określić Range, którego rozmiar odpowiada liczbie elementów w Array.

Użyj poniższego kodu, aby zapisać tablicę w zakresie D1:D5:

123	Zakres("D1:D5").Value = DirArrayZakres("D1:H1").Value = Application.Transpose(DirArray)

Pamiętaj, że musisz transponować tablicę, jeśli zapisujesz ją w wierszu.

Zakres sumy

12	SumOfRange = Application.WorksheetFunction.Sum(Range("A1:A10"))Debug.Print SumOfRange

Możesz użyć wielu funkcji dostępnych w programie Excel w kodzie VBA, określając Application.WorkSheetFunction. przed nazwą funkcji, jak w powyższym przykładzie.

Zakres zliczania

1234567

' Policz liczbę komórek z liczbami w zakresieCountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))Debug.Print CountOfCells' Policz liczbę niepustych komórek w zakresieCountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))Debug.Print CountOfNonBlankCells

Autor: Vinamra Chandra