Rozsahy a buňky aplikace Excel VBA

Rozsahy a buňky ve VBA

Tabulky Excelu ukládají data do buněk. Buňky jsou uspořádány do řádků a sloupců. Každou buňku lze identifikovat průsečíkem jejího řádku a sloupce (např. B3 nebo R3C2).

Rozsah Excel označuje jednu nebo více buněk (např. A3: B4)

Adresa buňky

Zápis A1

V zápisu A1 je buňka označena písmenem ve sloupci (od A do XFD), za nímž následuje číslo řádku (od 1 do 1 048 576).

Ve VBA můžete odkazovat na libovolnou buňku pomocí Objekt rozsahu.

123456789

'Viz buňka B4 na aktuálně aktivním listuRozsah MsgBox ("B4")'Viz buňka B4 na listu s názvem' Data 'Pracovní listy MsgBox („Data“). Rozsah („B4“)'Viz buňka B4 na listu s názvem' Data 'v jiném OPEN sešitu's názvem' Moje data 'Sešity MsgBox („Moje data“). Pracovní listy („Data“). Rozsah („B4“)

Zápis R1C1

V zápisu R1C1 je buňka označena písmenem R následovaným číslem řádku, potom písmenem „C“ následovaným číslem sloupce. např. B4 v zápisu R1C1 bude odkazováno pomocí R4C2. Ve VBA používáte Objekt buněk použít zápis R1C1:

12	„Viz buňka R [6] C [4], tj. D6Buňky (6, 4) = "D6"

Rozsah buněk

Zápis A1

Chcete -li odkazovat na více než jednu buňku, použijte „:“ mezi počáteční adresou buňky a adresou poslední buňky. Následující text bude odkazovat na všechny buňky od A1 do D10:

1	Rozsah ("A1: D10")

Zápis R1C1

Chcete -li odkazovat na více než jednu buňku, použijte mezi počáteční adresou buňky a adresou poslední buňky znak „“. Následující text bude odkazovat na všechny buňky od A1 do D10:

1	Rozsah (buňky (1, 1), buňky (10, 4))

Zápis do buněk

Chcete -li zapsat hodnoty do buňky nebo souvislé skupiny buněk, jednoduše se podívejte na rozsah, vložte znaménko = a poté napište hodnotu, která má být uložena:

12345678910

„Uložte F5 do buňky s adresou F6Rozsah ("F6") = "F6"„Uložte E6 do buňky s adresou R [6] C [5] tj. E6Buňky (6, 5) = "E6"„Uchovávejte A1: D10 v rozsahu A1: D10Rozsah ("A1: D10") = "A1: D10"“neboRozsah (buňky (1, 1), buňky (10, 4)) = "A1: D10"

Čtení z buněk

Chcete -li číst hodnoty z buněk, prostým odkazem na proměnnou uložte hodnoty, vložte znaménko = a poté odkazujte na rozsah, který chcete přečíst:

1234567891011

Dim val1Dim val2„Čtěte z buňky F6val1 = rozsah ("F6")„Čtěte z buňky E6val2 = buňky (6, 5)MsgBox val1Msgbox val2

Poznámka: Chcete -li ukládat hodnoty z řady buněk, musíte místo jednoduché proměnné použít pole.

Nesousedící buňky

Chcete -li odkazovat na nesouvislé buňky, použijte mezi adresami buněk čárku:

123456

„Uložte 10 do buněk A1, A3 a A5Rozsah ("A1, A3, A5") = 10„Uložte 10 do buněk A1: A3 a D1: D3)Rozsah ("A1: A3, D1: D3") = 10

Průnik buněk

Chcete -li odkazovat na nesouvislé buňky, použijte mezeru mezi adresami buněk:

123	Uložte „Col D“ do D1: D10“což je běžné mezi A1: D10 a D1: F10Rozsah ("A1: D10 D1: G10") = "Col D"

Odsazení od buňky nebo dosahu

Pomocí funkce Odsazení můžete přesunout referenci z daného Rozsahu (buňky nebo skupiny buněk) o zadaný počet_prostorů a počet_ sloupců.

Ofsetová syntaxe

Rozsah.Offset (number_of_rows, number_of_columns)

Odsazení z buňky

12345678910111213141516

'OFFSET z buňky A1„Podívejte se na samotnou buňku'Přesuňte 0 řádků a 0 sloupcůRozsah ("A1"). Offset (0, 0) = "A1"„Přesuňte 1 řádek a 0 sloupcůRozsah ("A1"). Offset (1, 0) = "A2"'Přesuňte 0 řádků a 1 sloupcůRozsah ("A1"). Offset (0, 1) = "B1"'Přesuňte 1 řádky a 1 sloupceRozsah ("A1"). Offset (1, 1) = "B2"'Přesuňte 10 řádků a 5 sloupcůRozsah ("A1"). Ofset (10, 5) = "F11"

Offset od rozsahu

123	'Přesunout odkaz na rozsah A1: D4 o 4 řádky a 4 sloupce„Nová reference je E5: H8Rozsah ("A1: D4"). Offset (4,4) = "E5: H8"

Nastavení odkazu na rozsah

Přiřazení rozsahu proměnné rozsahu: deklarujte proměnnou typu Range a poté ji pomocí příkazu Set nastavte na rozsah. Vezměte prosím na vědomí, že musíte použít příkaz SET, protože RANGE je objekt:

12345678

"Deklarujte proměnnou rozsahu."Dim myRange as Range'Nastavte proměnnou na rozsah A1: D4Nastavit myRange = rozsah ("A1: D4")'Vytiskne $ A $ 1: $ D $ 4MsgBox myRange.Address

Změnit velikost rozsahu

Metoda změny velikosti objektu Range mění rozměr referenčního rozsahu:

1234567

Dim myRange As Range„Rozsah změny velikostiNastavit myRange = rozsah ("A1: F4")'Vytiskne $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Address

Buňka vlevo nahoře v rozsahu se změnou velikosti je stejná jako buňka vlevo nahoře v původním rozsahu

Změnit velikost syntaxe

Range.Resize (number_of_rows, number_of_columns)

OFFSET vs Změna velikosti

Offset nemění rozměry rozsahu, ale přesouvá jej o zadaný počet řádků a sloupců. Změnit velikost nemění pozici původního rozsahu, ale mění rozměry na zadaný počet řádků a sloupců.

Všechny buňky v listu

Objekt Cells odkazuje na všechny buňky v listu (1048576 řádků a 16384 sloupců).

12	„Vymažte všechny buňky v pracovních listechBuňky. Vymazat

Použitý rozsah

Vlastnost UsedRange vám poskytuje obdélníkový rozsah od použité buňky vlevo nahoře po použitou buňku aktivního listu vpravo a dole.

1234567

Dim ws As WorksheetNastavit ws = ActiveSheet'$ B $ 2: $ L $ 14, pokud L2 je první buňka s jakoukoli hodnotou'a L14 je poslední buňka s libovolnou hodnotou na'aktivní listDebug.Print ws.UsedRange.Address

Aktuální region

Vlastnost CurrentRegion vám poskytuje souvislý obdélníkový rozsah od buňky vlevo nahoře k použité buňce vpravo dole obsahující referenční buňku/rozsah.

1234567891011

Dim myRange As RangeNastavit myRange = rozsah ("D4: F6")'Vytiskne $ B $ 2: $ L $ 14„Pokud existuje vyplněná cesta od D4: F16 do B2 A L14Debug.Print myRange.CurrentRegion.Address`` Můžete také odkazovat na jednu počáteční buňkuNastavit myRange = Range ("D4") 'Tisk $ B $ 2: $ L $ 14

Vlastnosti rozsahu

Můžete získat adresu, číslo řádku/sloupce buňky a počet řádků/sloupců v rozsahu, jak je uvedeno níže:

123456789101112131415161718192021

Dim myRange As RangeNastavit myRange = rozsah ("A1: F10")'Vytiskne $ A $ 1: $ F $ 10Debug.Print myRange.AddressNastavit myRange = rozsah ("F10")`` Vytiskne 10 pro řádek 10Debug.Print myRange.Row`` Vytiskne 6 pro sloupec FDebug.Print myRange.ColumnNastavit myRange = rozsah ("E1: F5")'Vytiskne 5 pro počet řádků v rozsahu.'Debug.Print myRange.Rows.Count'Vytiskne 2 pro počet sloupců v rozsahu.'Debug.Print myRange.Columns.Count

Poslední buňka v listu

Můžeš použít Řádky a Sloupce. Počet vlastnosti s Buňky objekt pro získání poslední buňky na listu:

1234567891011

„Vytiskněte číslo posledního řádku`` Vytiskne 1048576Debug.Print "Řádky v listu:" & Rows.Count„Vytiskněte číslo posledního sloupce`` Tiskne 16384Debug.Print "Sloupce v listu:" & Columns.Count„Vytiskněte adresu poslední buňky'Vytiskne $ XFD $ 1048576Debug.Print "Adresa poslední buňky v listu:" & buňky (řádky. Počet, sloupce. Počet)

Poslední použité číslo řádku ve sloupci

Vlastnost END vám vezme poslední buňku v rozsahu a End (xlUp) vás dovede k první použité buňce z této buňky.

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

Číslo naposledy použitého sloupce v řadě

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

Vlastnost END vám vezme poslední buňku v rozsahu a End (xlToLeft) vás přenese doleva k první použité buňce z této buňky.

Vlastnosti xlDown a xlToRight můžete také použít k navigaci do prvních použitých nebo spodních buněk aktuální buňky.

Vlastnosti buňky

Společné vlastnosti

Zde je kód pro zobrazení běžně používaných vlastností buňky

12345678910111213141516171819202122

Dim buňka jako rozsahNastavit buňku = rozsah ("A1")buňka. AktivovatBuňka ladění. Tisk. Adresa'Vytisknout $ A $ 1Buňka ladění. Tisk. Hodnota`` Tiskne 456' AdresaBuňka ladění. Tisk buňky. Vzorec'Výtisky = SUM (C2: C3)' KomentářBuňka ladění.tisk.Comment.Text"Styl."Buňka ladění. Tisk. Styl„Formát buňkyBuňka Debug.Print.DisplayFormat.NumberFormat

Písmo buňky

Objekt Cell.Font obsahuje vlastnosti písma buňky:

1234567891011121314151617181920

Dim buňka jako rozsahNastavit buňku = rozsah ("A1")„Pravidelné, kurzíva, tučné písmo a tučné písmocell.Font.FontStyle = "Tučné kurzíva"' Stejný jakocell.Font.Bold = Truecell.Font.Italic = True„Nastavit písmo na Couriercell.Font.FontStyle = "Courier"'Nastavit barvu písmacell.Font.Color = vbBlue“nebocell.Font.Color = RGB (255, 0, 0)'Nastavit velikost písmacell.Font.Size = 20

Kopírování a vkládání

Vložit vše

Rozsahy/buňky lze kopírovat a vkládat z jednoho místa na druhé. Následující kód zkopíruje všechny vlastnosti zdrojového rozsahu do cílového rozsahu (ekvivalent CTRL-C a CTRL-V)

1234567

„Jednoduchá kopieRozsah („A1: D20“). ZkopírujtePracovní listy („List2“). Rozsah („B10“). Vložit'nebo'Kopírovat z aktuálního listu na list s názvem' List2 'Rozsah ("A1: D20"). Cíl kopírování: = Pracovní listy ("List2"). Rozsah ("B10")

Vložit speciální

Vybrané vlastnosti zdrojového rozsahu lze zkopírovat do cíle pomocí možnosti PASTESPECIAL:

123	„Rozsah vložte pouze jako hodnotyRozsah („A1: D20“). ZkopírujtePracovní listy („List2“). Rozsah („B10“). PasteSpecial Paste: = xlPasteValues

Zde jsou možné možnosti pro možnost Vložit:

12345678910111213

„Vložte speciální typyxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats

Obsah AutoFit

Velikost řádků a sloupců lze změnit tak, aby odpovídala obsahu, pomocí níže uvedeného kódu:

12345

'Změňte velikost řádků 1 na 5, aby odpovídaly obsahuŘádky („1: 5“). Automatické přizpůsobení„Změňte velikost sloupců A na B, aby odpovídaly obsahuSloupce („A: B“). Automatické přizpůsobení

Další příklady rozsahu

Při provádění požadované akce prostřednictvím grafického uživatelského rozhraní doporučujeme používat záznam makra. Pomůže vám to pochopit různé dostupné možnosti a jejich použití.

Pro každého

Je snazší procházet rozsah pomocí Pro každého sestrojte podle obrázku níže:

123	Pro každou buňku v dosahu („A1: B100“)„Udělejte něco s celouDalší buňka

Při každé iteraci smyčky je jedné buňce v rozsahu přiřazena proměnná c a pro tuto buňku jsou provedeny příkazy ve smyčce For. Po zpracování všech buněk se smyčka ukončí.

Seřadit

Sort je metoda objektu Range. Rozsah můžete seřadit zadáním možností pro řazení do Range.Sort. Níže uvedený kód seřadí sloupce A: C podle klíče v buňce C2. Pořadí řazení může být xlAscending nebo xlDescending. Záhlaví: = xlAno by mělo být použito, pokud je prvním řádkem řádek záhlaví.

12	Sloupce ("A: C"). Klíč řazení1: = Rozsah ("C2"), _order1: = xlAscending, Header: = xlYes

Nalézt

Find je také metoda Range Object. Najde první buňku s obsahem odpovídajícím kritériím vyhledávání a vrátí buňku jako objekt Range. Vrací se to Nic pokud neexistuje shoda.

Použití Najdi další metoda (nebo FindPrevious) k nalezení dalšího (předchozího) výskytu.

Následující kód změní písmo na „Arial Black“ pro všechny buňky v rozsahu, které začínají na „John“:

12345

Pro každé c v rozsahu („A1: A100“)Pokud c jako „John*“ Pakc.Font.Name = "Arial Black"Konec IfDalší c

Následující kód nahradí všechny výskyty „Testovat“ na „Prošel“ v uvedeném rozsahu:

12345678910

S rozsahem ("a1: a500")Nastavit c = .Find ("Testovat", LookIn: = xlValues)Pokud ne c není nic, pakfirstaddress = c. AdresaDělatc.Value = "Prošlo"Nastavit c = .FindNext (c)Smyčka, i když ne c není nic, a c. Adresa první adresaKonec IfKonec s

Je důležité si uvědomit, že pro použití FindNext musíte určit rozsah. Také musíte zadat podmínku zastavení, jinak se smyčka spustí navždy. Normálně je adresa první buňky, která byla nalezena, uložena v proměnné a smyčka se zastaví, když znovu dosáhnete této buňky. Musíte také zkontrolovat případ, kdy není nalezeno nic, co by smyčku zastavilo.

Adresa rozsahu

K získání adresy ve stylu A1 použijte Range.Address

123	Rozsah MsgBox ("A1: D10"). Adresa“neboRozsah ladění.tisku ("A1: D10"). Adresa

K získání adres ve stylu R1C1 použijte xlReferenceStyle (výchozí je xlA1)

123	Rozsah MsgBox ("A1: D10"). Adresa (ReferenceStyle: = xlR1C1)“neboRozsah Debug.Print ("A1: D10"). Adresa (ReferenceStyle: = xlR1C1)

To je užitečné, když pracujete s rozsahy uloženými v proměnných a chcete zpracovávat pouze pro určité adresy.

Rozsah do pole

Je rychlejší a snazší přenést rozsah do pole a poté zpracovat hodnoty. Pole byste měli deklarovat jako Variant, abyste se vyhnuli výpočtu velikosti potřebné k naplnění rozsahu v poli. Rozměry pole jsou nastaveny tak, aby odpovídaly počtu hodnot v rozsahu.

123456789

Dim DirArray jako varianta'Uložte hodnoty v rozsahu do poleDirArray = Rozsah ("a1: a5"). Hodnota„Smyčka ke zpracování hodnotZa každé c In DirArrayDebug.Print cdalší

Pole na dosah

Po zpracování můžete pole zapsat zpět do rozsahu. Chcete -li zapsat pole ve výše uvedeném příkladu do rozsahu, musíte zadat rozsah, jehož velikost odpovídá počtu prvků v poli.

Pomocí níže uvedeného kódu zapište pole do rozsahu D1: D5:

123	Rozsah ("D1: D5"). Hodnota = DirArrayRozsah ("D1: H1"). Hodnota = Application.Transpose (DirArray)

Pokud pole zapisujete do řádku, musíte ho transponovat.

Rozsah součtů

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

V kódu VBA můžete použít mnoho funkcí dostupných v Excelu zadáním Application.WorkSheetFunction. před názvem funkce jako v příkladu výše.

Rozsah počítání

1234567

'Spočítejte počet buněk s čísly v rozsahuCountOfCells = Application.WorksheetFunction.Count (rozsah ("A1: A10"))Debug.Print CountOfCells'Spočítejte počet neprázdných buněk v rozsahuCountOfNonBlankCells = Application.WorksheetFunction.CountA (rozsah ("A1: A10"))Debug.Print CountOfNonBlankCells

Autor: Vinamra Chandra