Rozsahy a bunky programu Excel VBA

Rozsahy a bunky vo VBA

Tabuľky programu Excel ukladajú údaje do buniek. Bunky sú usporiadané do riadkov a stĺpcov. Každú bunku je možné identifikovať podľa priesečníka jej riadka a stĺpca (napr. B3 alebo R3C2).

Rozsah Excel označuje jednu alebo viac buniek (napr. A3: B4)

Adresa bunky

Zápis A1

V zápise A1 je bunka označená písmenom stĺpca (od A do XFD), za ktorým nasleduje číslo riadka (od 1 do 1 048 576).

Vo VBA môžete odkazovať na ľubovoľnú bunku pomocou Objekt dosahu.

123456789

„Pozrite si bunku B4 na aktuálne aktívnom hárkuRozsah MsgBox ("B4")“Pozrite si bunku B4 na hárku s názvom„ Údaje “Pracovné listy MsgBox („údaje“). Rozsah („B4“)“Pozrite si bunku B4 na hárku s názvom„ Údaje “v inom OTVORENOM zošite„s názvom„ Moje údaje “Pracovné zošity MsgBox („Moje údaje“). Pracovné listy („Údaje“). Rozsah („B4“)

Označenie R1C1

V zápise R1C1 je bunka označená písmenom R, číslom riadka, potom písmenom „C“ a číslom stĺpca. napr. B4 v zápise R1C1 bude odkazovať na R4C2. Vo VBA používate Bunky Objekt použiť zápis R1C1:

12	„Pozri bunku R [6] C [4] t.j. D6Bunky (6, 4) = "D6"

Rozsah buniek

Zápis A1

Na odkazovanie na viac ako jednu bunku použite znak „:“ medzi počiatočnou adresou bunky a poslednou adresou bunky. Nasledujúci text sa bude vzťahovať na všetky bunky od A1 do D10:

1	Rozsah („A1: D10“)

Označenie R1C1

Ak chcete odkazovať na viac ako jednu bunku, použite medzi začiatočnou adresou bunky a poslednou adresou bunky znak „“. Nasledujúci text sa bude vzťahovať na všetky bunky od A1 do D10:

1	Rozsah (bunky (1, 1), bunky (10, 4))

Písanie do buniek

Ak chcete zapísať hodnoty do bunky alebo súvislej skupiny buniek, jednoducho sa obráťte na rozsah, zadajte znamienko = a potom napíšte hodnotu, ktorá sa má uložiť:

12345678910

„Uložte F5 do bunky s adresou F6Rozsah ("F6") = "F6"„Uchovávajte E6 v bunke s adresou R [6] C [5] t.j. E6Bunky (6, 5) = "E6"„Uchovávajte A1: D10 v rozmedzí A1: D10Rozsah ("A1: D10") = "A1: D10"“aleboRozsah (bunky (1, 1), bunky (10, 4)) = "A1: D10"

Čítanie z buniek

Ak chcete čítať hodnoty z buniek, jednoducho sa obráťte na premennú, aby ste hodnoty uložili, zadajte znamienko = a potom sa pozrite na rozsah, ktorý sa má prečítať:

1234567891011

Dim val1Dim val2„Prečítajte si z bunky F6val1 = rozsah ("F6")„Prečítajte si z bunky E6val2 = bunky (6, 5)MsgBox val1Msgbox val2

Poznámka: Na ukladanie hodnôt z radu buniek musíte namiesto jednoduchej premennej použiť pole.

Nesusediace bunky

Ak chcete odkazovať na nesúvislé bunky, použite medzi adresami buniek čiarku:

123456

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

Priesečník buniek

Ak chcete odkazovať na nesúvislé bunky, použite medzeru medzi adresami buniek:

123	Uložte „Col D“ v D1: D10“, ktorý je bežný medzi A1: D10 a D1: F10Rozsah ("A1: D10 D1: G10") = "stĺpček D"

Odsadenie od bunky alebo rozsahu

Pomocou funkcie Ofset môžete posunúť referenciu z daného rozsahu (bunka alebo skupina buniek) o zadaný počet_priemerov a počet_ stĺpcov.

Ofsetová syntax

Rozsah. Odsadenie (počet_rowov, počet_ stĺpcov)

Odsadenie od bunky

12345678910111213141516

„OFFSET z bunky A1„Pozrite sa na samotnú bunku„Presuňte 0 riadkov a 0 stĺpcovRozsah ("A1"). Ofset (0, 0) = "A1"„Presuňte 1 riadok a 0 stĺpcovRozsah ("A1"). Ofset (1, 0) = "A2"„Presuňte 0 riadkov a 1 stĺpcovRozsah ("A1"). Ofset (0, 1) = "B1"„Presuňte 1 riadok a 1 stĺpecRozsah ("A1"). Ofset (1, 1) = "B2"„Presuňte 10 riadkov a 5 stĺpcovRozsah ("A1"). Ofset (10, 5) = "F11"

Odsadenie od rozsahu

123	„Presuňte odkaz na rozsah A1: D4 o 4 riadky a 4 stĺpce„Nová referencia je E5: H8Rozsah ("A1: D4"). Ofset (4,4) = "E5: H8"

Nastavenie odkazu na rozsah

Priradenie rozsahu premennej rozsahu: deklarujte premennú typu Range a potom ju pomocou príkazu Set nastavte na rozsah. Upozorňujeme, že musíte použiť príkaz SET, pretože RANGE je objekt:

12345678

„Vyhláste premennú rozsahuDim myRange as Range„Nastavte premennú na rozsah A1: D4Nastaviť myRange = rozsah ("A1: D4")'Vytlačí $ A $ 1: $ D $ 4MsgBox myRange.Address

Zmeňte veľkosť rozsahu

Metóda zmeny veľkosti objektu Range zmení rozmer referenčného rozsahu:

1234567

Dim myRange As Range„Rozsah je možné zmeniťNastaviť myRange = rozsah ("A1: F4")'Vytlačí $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Address

Ľavá horná bunka rozsahu zmenenej veľkosti je rovnaká ako ľavá horná bunka pôvodného rozsahu

Zmeniť veľkosť syntaxe

Rozsah. Zmena veľkosti (počet_rowov, počet_ stĺpcov)

OFFSET vs Zmena veľkosti

Offset nezmení rozmery rozsahu, ale posunie ho o určený počet riadkov a stĺpcov. Zmeniť veľkosť nezmení pozíciu pôvodného rozsahu, ale zmení rozmery na zadaný počet riadkov a stĺpcov.

Všetky bunky v hárku

Objekt Bunky odkazuje na všetky bunky v hárku (1048576 riadkov a 16384 stĺpcov).

12	„Vymažte všetky bunky v pracovných listochBunky. Jasné

UsedRange

Vlastnosť UsedRange vám dáva obdĺžnikový rozsah od použitej bunky vľavo hore po použitú bunku vpravo dole v aktívnom hárku.

1234567

Dim ws As worksheetNastaviť ws = ActiveSheet'$ B $ 2: $ L $ 14, ak L2 je prvá bunka s akoukoľvek hodnotou'a L14 je posledná bunka s ľubovoľnou hodnotou na„aktívny listDebug.Print ws.UsedRange.Address

CurrentRegion

Vlastnosť CurrentRegion vám dáva súvislý obdĺžnikový rozsah od bunky vľavo hore po použitú bunku vpravo dole, ktorá obsahuje odkazovanú bunku/rozsah.

1234567891011

Dim myRange As RangeNastaviť myRange = rozsah ("D4: F6")'Vytlačí $ B $ 2: $ L $ 14„Ak je vyplnená cesta od D4: F16 do B2 A L14Debug.Print myRange.CurrentRegion.Address„Môžete sa odvolať aj na jednu počiatočnú bunkuNastaviť myRange = rozsah („D4“) 'Tlač $ B $ 2: $ L $ 14

Vlastnosti rozsahu

Môžete získať adresu, číslo riadka/stĺpca bunky a počet riadkov/stĺpcov v rozsahu uvedenom nižšie:

123456789101112131415161718192021

Dim myRange As RangeNastaviť myRange = rozsah ("A1: F10")'Vytlačí $ A $ 1: $ F $ 10Debug.Print myRange.AddressNastaviť myRange = rozsah ("F10")„Vytlačí 10 pre riadok 10Debug.Print myRange.Row„Tlač 6 pre stĺpec FDebug.Print myRange.ColumnNastaviť myRange = rozsah ("E1: F5")'Vytlačí 5 pre počet riadkov v rozsahuDebug.Print myRange.Rows.Count„Vytlačí 2 pre počet stĺpcov v rozsahuDebug.Print myRange.Columns.Count

Posledná bunka v hárku

Môžeš použiť Riadky. Počet a Stĺpce. Počet vlastnosti s Bunky namieste získať poslednú bunku na hárku:

1234567891011

„Vytlačte číslo posledného riadka'Vytlačí 1048576Debug.Print "Riadky v hárku:" & Rows.Count„Vytlačte číslo posledného stĺpca„Vytlačí 16384Debug.Print "Stĺpce v hárku:" & Stĺpce.Počet„Vytlačte adresu poslednej bunky'Vytlačí $ XFD $ 1048576Debug.Print "Adresa poslednej bunky v hárku:" & Bunky (riadky. Počet, stĺpce. Počet)

Číslo posledného použitého riadka v stĺpci

Vlastnosť END vás zavedie na poslednú bunku v rozsahu a End (xlUp) vás prevedie na prvú použitú bunku z tejto bunky.

123	Dim lastRow As LonglastRow = bunky (riadky. počet, „A“). Koniec (xlUp). riadok

Číslo stĺpca, ktoré ste naposledy použili v rade

123	Dim lastCol As LonglastCol = bunky (1, stĺpce. počet). Koniec (xlToLeft). stĺpec

Vlastnosť END vás prevedie poslednou bunkou v rozsahu a End (xlToLeft) vás prevedie doľava k prvej použitej bunke z tejto bunky.

Vlastnosti xlDown a xlToRight môžete použiť aj na navigáciu do prvých použitých alebo použitých buniek aktuálnej bunky.

Vlastnosti bunky

Spoločné vlastnosti

Tu je kód na zobrazenie bežne používaných vlastností bunky

12345678910111213141516171819202122

Dim bunka ako rozsahNastaviť bunku = rozsah („A1“)bunka. AktivovaťLadenie. Vytlačte bunku. Adresa„Vytlačiť $ A $ 1Bunka ladenia. Vytlačiť. Hodnota„Tlačí 456'AdresaLadenie. Vytlačte bunku. Vzorec'Výtlačky = SUM (C2: C3)'KomentárDebug.Print cell.Comment.Text' ŠtýlLadenie. Vytlačte bunku. Štýl„Formát bunkyDebug.Print cell.DisplayFormat.NumberFormat

Bunkové písmo

Objekt Cell.Font obsahuje vlastnosti písma bunky:

1234567891011121314151617181920

Dim bunka ako rozsahNastaviť bunku = rozsah („A1“)„Pravidelné, kurzíva, tučné a tučné kurzívacell.Font.FontStyle = "Tučné kurzíva"' Rovnaké akocell.Font.Bold = Truecell.Font.Italic = True'Nastaviť písmo na Couriercell.Font.FontStyle = "Kuriér"'Nastavte farbu písmacell.Font.Color = vbBlue“alebocell.Font.Color = RGB (255, 0, 0)'Nastavte veľkosť písmacell.Font.Size = 20

Kopírovanie a vkladanie

Prilepiť všetko

Rozsahy/bunky je možné kopírovať a vkladať z jedného miesta na druhé. Nasledujúci kód kopíruje všetky vlastnosti zdrojového rozsahu do cieľového rozsahu (ekvivalent CTRL-C a CTRL-V)

1234567

„Jednoduchá kópiaRozsah („A1: D20“). SkopírujtePracovné listy („List2“). Rozsah („B10“). Prilepte“alebo„Kopírovať z aktuálneho listu na list s názvom„ List2 “Rozsah ("A1: D20"). Cieľ kopírovania: = Pracovné listy ("List2"). Rozsah ("B10")

Prilepiť špeciálne

Vybraté vlastnosti zdrojového rozsahu je možné skopírovať do cieľa pomocou možnosti PASTESPECIAL:

123	„Rozsah prilepte iba ako hodnotyRozsah („A1: D20“). SkopírujtePracovné listy („List2“). Rozsah („B10“). PasteSpecial Paste: = xlPasteValues

Tu sú možné možnosti pre možnosť Prilepiť:

12345678910111213

„Prilepte špeciálne typyxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats

Obsah AutoFit

Veľkosť riadkov a stĺpcov je možné zmeniť tak, aby zodpovedali obsahu, pomocou nižšie uvedeného kódu:

12345

„Zmeňte veľkosť riadkov 1 až 5 tak, aby zodpovedali obsahuRiadky („1: 5“). Automatické prispôsobenie„Zmeňte veľkosť stĺpcov A na B, aby zodpovedali obsahuStĺpce („A: B“). Automatické prispôsobenie

Viac príkladov rozsahu

Pri vykonávaní požadovanej akcie prostredníctvom grafického používateľského rozhrania sa odporúča používať záznam makra. Pomôže vám to pochopiť rôzne dostupné možnosti a ich používanie.

Pre každý

Je jednoduchšie prechádzať rozsahom pomocou Pre každý zostrojte tak, ako je uvedené nižšie:

123	Pre každú bunku v rozsahu („A1: B100“)„Urob niečo s celouĎalšia bunka

Pri každej iterácii slučky je jednej bunke v rozsahu priradená premenná c a pre túto bunku sú vykonávané príkazy v slučke For. Po spracovaní všetkých buniek sa slučka ukončí.

Zoradiť

Zoradenie je metóda objektu Range. Rozsah môžete zoradiť zadaním možností zoradenia podľa Range.Sort. Nasledujúci kód zoradí stĺpce A: C podľa kľúča v bunke C2. Zoradiť poradie môže byť xlAscending alebo xlDescending. Hlavička: = xlÁno by sa malo použiť, ak je prvým riadkom riadok hlavičky.

12	Stĺpce ("A: C"). Triediaci kľúč1: = rozsah ("C2"), _poradie1: = xlAscending, hlavička: = xlAno

Nájsť

Nájdenie je tiež metóda objektu Range. Nájde prvú bunku s obsahom zodpovedajúcim kritériám vyhľadávania a vráti bunku ako objekt rozsahu. Vracia sa to Nič ak neexistuje zhoda.

Použite Nájdi ďaľší metóda (alebo FindPrevious) na nájdenie nasledujúceho (predchádzajúceho) výskytu.

Nasledujúci kód zmení písmo na „Arial Black“ pre všetky bunky v rozsahu, ktoré začínajú na „John“:

12345

Pre každé c v rozsahu („A1: A100“)Ak c ako „John*“ Potomc.Font.Name = "Arial Black"Koniec AkĎalej c

Nasledujúci kód nahradí všetky výskyty „na testovanie“ na „úspešné“ v uvedenom rozsahu:

12345678910

S rozsahom ("a1: a500")Nastaviť c = .Find („Na testovanie“, LookIn: = xlValues)Ak nie, potom nie je ničfirstaddress = c.AddressUrobc.Value = "Prešlo"Nastaviť c = .FindNext (c)Slučka, aj keď nie je c, je nič, c. Adresa ako prvá adresaKoniec AkUkončiť s

Je dôležité si uvedomiť, že musíte zadať rozsah, v ktorom sa má FindNext používať. Musíte tiež zadať podmienku zastavenia, inak sa slučka bude vykonávať navždy. Normálne je adresa prvej bunky, ktorá sa nájde, uložená v premennej a slučka sa zastaví, keď znova dosiahnete túto bunku. Musíte tiež skontrolovať prípad, keď sa nenájde nič, čo by zastavilo slučku.

Adresa rozsahu

Na získanie adresy v štýle A1 použite Range.Address

123	Rozsah MsgBox ("A1: D10"). Adresa“aleboRozsah ladenia.tlače ("A1: D10"). Adresa

Na získanie adries v štýle R1C1 použite xlReferenceStyle (predvolený je xlA1)

123	Rozsah MsgBox ("A1: D10"). Adresa (referenčný štýl: = xlR1C1)“aleboDebug.Print Range ("A1: D10"). Address (ReferenceStyle: = xlR1C1)

Je to užitočné, keď pracujete s rozsahmi uloženými v premenných a chcete spracovať iba pre určité adresy.

Rozsah do poľa

Je rýchlejšie a jednoduchšie preniesť rozsah do poľa a potom spracovať hodnoty. Pole by ste mali deklarovať ako variant, aby ste sa vyhli výpočtu veľkosti potrebnej na vyplnenie rozsahu v poli. Rozmery poľa sú nastavené tak, aby zodpovedali počtu hodnôt v rozsahu.

123456789

Dim DirArray ako variant'Uložte hodnoty v rozsahu do poľaDirArray = Rozsah ("a1: a5"). Hodnota„Slučka na spracovanie hodnôtZa každé c In DirArrayDebug.Print cĎalšie

Array to Range

Po spracovaní môžete pole zapísať späť do rozsahu. Ak chcete zapísať pole v uvedenom príklade do rozsahu, musíte zadať rozsah, ktorého veľkosť sa zhoduje s počtom prvkov v poli.

Pomocou nižšie uvedeného kódu napíšte pole do rozsahu D1: D5:

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

Upozorňujeme, že pole musíte transponovať, ak ho píšete do riadka.

Rozsah súčtov

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

Môžete použiť mnoho funkcií dostupných v programe Excel v kóde VBA zadaním Application.WorkSheetFunction. pred názvom funkcie ako v príklade vyššie.

Rozsah počítania

1234567

„Spočítajte počet buniek s číslami v rozsahuCountOfCells = Application.WorksheetFunction.Count (rozsah ("A1: A10"))Debug.Print CountOfCells'Spočítajte počet prázdnych buniek v rozsahuCountOfNonBlankCells = Application.WorksheetFunction.CountA (rozsah ("A1: A10"))Debug.Print CountOfNonBlankCells

Scenár: Vinamra Chandra