Diapazoni un šūnas VBA
Excel izklājlapās dati tiek glabāti šūnās. Šūnas ir sakārtotas rindās un kolonnās. Katru šūnu var identificēt pēc tās rindas un kolonnas krustošanās punkta (piem., B3 vai R3C2).
Excel diapazons attiecas uz vienu vai vairākām šūnām (piem., A3: B4)
Šūnas adrese
A1 apzīmējums
A1 apzīmējumā šūna tiek apzīmēta ar tās kolonnas burtu (no A līdz XFD), kam seko rindas numurs (no 1 līdz 1 048 576).
VBA varat atsaukties uz jebkuru šūnu, izmantojot Diapazona objekts.
| 123456789 | “Skatiet šūnu B4 pašlaik aktīvajā lapāMsgBox diapazons ("B4")“Skatiet šūnu B4 uz lapas ar nosaukumu“ Dati ”MsgBox darblapas ("Dati"). Diapazons ("B4")“Skatiet šūnu B4 uz lapas ar nosaukumu“ Dati ”citā OPEN darbgrāmatāar nosaukumu "Mani dati"MsgBox darbgrāmatas ("Mani dati"). Darblapas ("Dati"). Diapazons ("B4") |
R1C1 apzīmējums
R1C1 apzīmējumā šūna tiek apzīmēta ar R, kam seko rindas numurs, tad burts “C”, kam seko kolonnas numurs. piemēram, B4 R1C1 apzīmējumā tiks apzīmēts ar R4C2. VBA jūs izmantojat Šūnu objekts lai izmantotu R1C1 apzīmējumu:
| 12 | “Skatiet šūnu R [6] C [4], ti, D6Šūnas (6, 4) = "D6" |
Šūnu diapazons
A1 apzīmējums
Lai atsauktos uz vairākām šūnām, starp sākuma šūnas un pēdējās šūnas adresi izmantojiet “:”. Tālāk tiks norādītas visas šūnas no A1 līdz D10:
| 1 | Diapazons ("A1: D10") |
R1C1 apzīmējums
Lai atsauktos uz vairākām šūnām, starp sākuma šūnas un pēdējās šūnas adresi izmantojiet “”. Tālāk tiks norādītas visas šūnas no A1 līdz D10:
| 1 | Diapazons (šūnas (1, 1), šūnas (10, 4)) |
Rakstīšana šūnām
Lai ierakstītu vērtības šūnai vai blakus esošai šūnu grupai, vienkārši skatiet diapazonu, ievietojiet zīmi = un pēc tam ierakstiet saglabāto vērtību:
| 12345678910 | "Saglabājiet F5 šūnā ar adresi F6Diapazons ("F6") = "F6"“Saglabājiet E6 šūnā ar adresi R [6] C [5], ti, E6Šūnas (6, 5) = "E6"“Uzglabāt A1: D10 diapazonā A1: D10Diapazons ("A1: D10") = "A1: D10"'vaiDiapazons (šūnas (1, 1), šūnas (10, 4)) = "A1: D10" |
Lasīšana no šūnām
Lai nolasītu vērtības no šūnām, vienkārši skatiet mainīgo, lai saglabātu vērtības, ievietojiet zīmi = un pēc tam skatiet nolasāmo diapazonu:
| 1234567891011 | Dim val1Dim val2“Lasīt no šūnas F6val1 = Diapazons ("F6")“Lasīt no šūnas E6val2 = šūnas (6, 5)MsgBox val1Msgbox val2 |
Piezīme. Lai saglabātu vērtības no šūnu diapazona, vienkārša mainīgā vietā jāizmanto masīvs.
Nesavienojošās šūnas
Lai atsauktos uz blakus esošām šūnām, starp šūnu adresēm izmantojiet komatu:
| 123456 | “Saglabājiet 10 šūnās A1, A3 un A5Diapazons ("A1, A3, A5") = 10“Uzglabāt 10 šūnās A1: A3 un D1: D3”Diapazons ("A1: A3, D1: D3") = 10 |
Šūnu krustošanās
Lai atsauktos uz blakus esošām šūnām, izmantojiet atstarpi starp šūnu adresēm:
| 123 | Saglabājiet kolonu D kolonnā D1: D10"kas ir izplatīta starp A1: D10 un D1: F10Diapazons ("A1: D10 D1: G10") = "D kolonna" |
Nobīde no šūnas vai diapazona
Izmantojot nobīdes funkciju, jūs varat pārvietot atsauci no noteiktā diapazona (šūnas vai šūnu grupas) par norādīto_rindu skaitu un kolonnu skaitu.
Nobīdes sintakse
Diapazons. Nobīde (rindu skaits, kolonnu skaits)
Nobīde no šūnas
| 12345678910111213141516 | “OFFSET no šūnas A1“Atsaucieties uz pašu šūnu“Pārvietot 0 rindas un 0 kolonnasDiapazons ("A1"). Nobīde (0, 0) = "A1"“Pārvietot 1 rindu un 0 kolonnuDiapazons ("A1"). Nobīde (1, 0) = "A2"“Pārvietot 0 rindas un 1 kolonnasDiapazons ("A1"). Nobīde (0, 1) = "B1"“Pārvietot 1 rindu un 1 kolonnuDiapazons ("A1"). Nobīde (1, 1) = "B2"“Pārvietojiet 10 rindas un 5 kolonnasDiapazons ("A1"). Nobīde (10, 5) = "F11" |
Nobīde no diapazona
| 123 | “Pārvietot atsauci uz diapazonu A1: D4 ar 4 rindām un 4 kolonnām"Jauna atsauce ir E5: H8Diapazons ("A1: D4"). Nobīde (4,4) = "E5: H8" |
Atsauces iestatīšana uz diapazonu
Lai diapazona mainīgajam piešķirtu diapazonu: deklarējiet Range tipa mainīgo, pēc tam izmantojiet komandu Set, lai iestatītu to diapazonam. Lūdzu, ņemiet vērā, ka jums jāizmanto komanda SET, jo RANGE ir objekts:
| 12345678 | 'Deklarējiet diapazona mainīgoDim myRange kā diapazons'Iestatiet mainīgo uz diapazonu A1: D4Iestatīt myRange = Diapazons ("A1: D4")Drukā $ A $ 1: $ D $ 4MsgBox myRange. Adrese |
Diapazona lieluma maiņa
Diapazona objekta izmēru maiņas metode maina atsauces diapazona dimensiju:
| 1234567 | Dim myRange kā diapazons'Diapazons līdz lieluma maiņaiIestatīt myRange = Diapazons ("A1: F4")Drukā $ A $ 1: $ E $ 10Atkļūdošana. Drukāt myRange. Izmērs (10, 5). Adrese |
Mainītā diapazona augšējā kreisā šūna ir tāda pati kā sākotnējā diapazona augšējā kreisā šūna
Sintakses lieluma maiņa
Diapazons. Izmērs (rindu skaits, kolonnu skaits)
OFFSET vs Resize
Nobīde nemaina diapazona izmērus, bet pārvieto to par norādīto rindu un kolonnu skaitu. Mainot izmēru, nemainās sākotnējā diapazona pozīcija, bet tiek mainīti izmēri uz norādīto rindu un kolonnu skaitu.
Visas šūnas lapā
Šūnu objekts attiecas uz visām lapas šūnām (1048576 rindas un 16384 kolonnas).
| 12 | 'Notīrīt visas šūnas darblapāsŠūnas. Skaidrs |
UsedRange
PropertyRange rekvizīts sniedz taisnstūra diapazonu no izmantotās šūnas augšējās kreisās šūnas līdz aktīvās lapas labajai apakšējai izmantotajai šūnai.
| 1234567 | Dim ws kā darblapaIestatiet ws = ActiveSheet'$ B $ 2: $ L $ 14, ja L2 ir pirmā šūna ar jebkuru vērtību"un L14 ir pēdējā šūna ar jebkādu vērtību'aktīvā lapaAtkļūdošana. Drukāt ws. Lietotā diapazons. Adrese |
Pašreizējais reģions
Rekvizīts CurrentRegion sniedz blakus esošo taisnstūra diapazonu no šūnas augšējā kreisā līdz izmantotajai labajai apakšējai šūnai, kurā ir norādītā šūna/diapazons.
| 1234567891011 | Dim myRange kā diapazonsIestatīt myRange = Diapazons ("D4: F6")Drukā $ B $ 2: $ L $ 14“Ja ir aizpildīts ceļš no D4: F16 līdz B2 UN L14Atkļūdot. Drukāt myRange.CurrentRegion.Address“Jūs varat atsaukties arī uz vienu sākuma šūnuIestatīt myRange = Range ("D4") 'Drukā $ B $ 2: $ L $ 14 |
Diapazona rekvizīti
Tālāk norādītajā diapazonā varat iegūt adresi, šūnas rindas/kolonnas numuru un rindu/kolonnu skaitu.
| 123456789101112131415161718192021 | Dim myRange kā diapazonsIestatīt myRange = Diapazons ("A1: F10")Drukā $ A $ 1: $ F $ 10Atkļūdot. Drukāt myRange. AdreseIestatīt myRange = Diapazons ("F10")“Izdrukā 10 10. rindaiAtkļūdot. Drukāt myRange. Rinda“6. sleja F slejaiAtkļūdot. Drukāt myRange. KolonnaIestatīt myRange = Diapazons ("E1: F5")“Izdrukā 5 par rindu skaitu diapazonāAtkļūdot. Drukāt myRange.Rows.Count'Izdrukā 2 kolonnu skaitu diapazonāAtkļūdot. Drukāt myRange. Kolonnas. Skait |
Lapas pēdējā šūna
Tu vari izmantot Rindas. Skaits un Kolonnas. Skaits īpašības ar Šūnas objekts, lai iegūtu lapas pēdējo šūnu:
| 1234567891011 | 'Izdrukājiet pēdējās rindas numuru“Izdrukas 1048576Debug.Print "Rindas lapā:" & Rows.Count'Izdrukājiet pēdējās kolonnas numuru“Izdrukas 16384Debug.Print "Kolonnas lapā:" & Column.Count'Izdrukājiet pēdējās šūnas adresiDrukā XFD USD 1048576Debug.Print "Lapas pēdējās šūnas adrese:" un šūnas (Rows.Count, Columns.Count) |
Pēdējais izmantotais rindas numurs kolonnā
END rekvizīts aizvedīs pēdējo diapazona šūnu, un End (xlUp) aizvedīs uz pirmo izmantoto šūnu no šīs šūnas.
| 123 | Aptumšot pēdējo rindu tik ilgilastRow = Šūnas (Rows.Count, "A"). Beigas (xlUp). Rinda |
Pēdējoreiz izmantotais kolonnas numurs rindā
| 123 | Aptumšot pēdējo kolu tik ilgilastCol = Šūnas (1, Columns.Count). End (xlToLeft). Sleja |
END rekvizīts aizvedīs diapazona pēdējo šūnu, bet Beiga (xlToLeft) - pa kreisi uz pirmo izmantoto šūnu no šīs šūnas.
Varat arī izmantot rekvizītus xlDown un xlToRight, lai pārietu uz pašreizējās šūnas pirmajām apakšējām vai labajām izmantotajām šūnām.
Šūnas rekvizīti
Kopīgas īpašības
Šeit ir kods, lai parādītu bieži izmantotos šūnu rekvizītus
| 12345678910111213141516171819202122 | Dim šūna kā diapazonsIestatīt šūnu = diapazons ("A1")šūna. AktivizētAtkļūdot. Drukāt šūnu. Adrese'Izdrukājiet $ 1 USDAtkļūdot. Drukāt šūnu. Vērtība“Izdrukas 456' AdreseAtkļūdot. Drukāt šūnu. Formula'Izdrukas = SUM (C2: C3)'KomentētAtkļūdot. Drukāt šūnu. Komentēt. Teksts' StilsAtkļūdošana. Drukāt šūnu. Stils'Šūnas formātsAtkļūdot. Drukāt šūnu. Displeja formāts. Skaitļa formāts |
Šūnas fonts
Šūnas.Fonts objektā ir šūnas fonta rekvizīti:
| 1234567891011121314151617181920 | Dim šūna kā diapazonsIestatīt šūnu = diapazons ("A1")“Regulāri, slīprakstā, treknrakstā un treknrakstācell.Font.FontStyle = "Bold Italic"' Tāds pats kācell.Font.Bold = Truešūna. Fonts. Itāļu valoda = True'Iestatiet fontu uz kurjerucell.Font.FontStyle = "Kurjers"'Iestatiet fonta krāsušūna.Fonts.Krāsa = vbZila'vaišūna. Fonts. Krāsa = RGB (255, 0, 0)'Iestatiet fonta lielumušūna. Fonts. Izmērs = 20 |
Kopēt un ielīmēt
Ielīmēt visu
Diapazonus/šūnas var kopēt un ielīmēt no vienas vietas uz citu. Šis kods kopē visus avota diapazona rekvizītus uz galamērķa diapazonu (ekvivalents CTRL-C un CTRL-V)
| 1234567 | 'Vienkārša kopijaDiapazons ("A1: D20"). KopētDarblapas ("Sheet2"). Diapazons ("B10"). Ielīmēt'vai"Kopēt no pašreizējās lapas uz lapu ar nosaukumu" Sheet2 "Diapazons ("A1: D20"). Kopēšanas galamērķis: = darblapas ("Sheet2"). Diapazons ("B10") |
Īpaša ielīmēšana
Atlasītos avota diapazona rekvizītus var nokopēt galamērķī, izmantojot opciju PASTESPECIAL:
| 123 | 'Ielīmējiet diapazonu tikai kā vērtībasDiapazons ("A1: D20"). KopētDarblapas ("Sheet2"). Diapazons ("B10"). PasteSpecial Paste: = xlPasteValues |
Tālāk ir norādītas iespējamās opcijas Ielīmēt.
| 12345678910111213 | 'Ielīmējiet īpašos veidusxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
AutoFit saturs
Rindu un kolonnu lielumu var mainīt, lai tas atbilstu saturam, izmantojot tālāk norādīto kodu.
| 12345 | “Mainiet 1. līdz 5. rindas izmēru, lai tas atbilstu saturamRindas ("1: 5"). AutoFit“Mainiet sleju A līdz B lielumu, lai tas atbilstu saturamKolonnas ("A: B"). AutoFit |
Vairāk diapazona piemēru
Veicot nepieciešamās darbības, izmantojot GUI, ieteicams izmantot makro ierakstītāju. Tas palīdzēs jums saprast dažādas pieejamās iespējas un to izmantošanu.
Katram
Izmantojot diapazonu, ir vieglāk pārvietoties pa diapazonu Katram konstruējiet, kā parādīts zemāk:
| 123 | Katrai diapazona šūnai ("A1: B100")"Dariet kaut ko ar šūnuNākamā šūna |
Katrā cilpas atkārtojumā mainīgajam c tiek piešķirta viena diapazona šūna, un šai šūnai tiek izpildīti paziņojumi par cilpu. Cilpa iziet, kad tiek apstrādātas visas šūnas.
Kārtot
Kārtot ir Range objekta metode. Jūs varat kārtot diapazonu, norādot iespējas, kā kārtot diapazonu. Tālāk esošais kods kārtos kolonnas A: C, pamatojoties uz atslēgu šūnā C2. Kārtošanas secība var būt xlAscending vai xlDescending. Virsraksts: = xlJā jāizmanto, ja pirmā rinda ir galvenes rinda.
| 12 | Kolonnas ("A: C"). Kārtošanas taustiņš1: = Diapazons ("C2"), _secība1: = xlaugoši, galvene: = xlJā |
Atrast
Atrast ir arī diapazona objekta metode. Tā atrod pirmo šūnu, kuras saturs atbilst meklēšanas kritērijiem, un atgriež šūnu kā diapazona objektu. Tas atgriežas Nekas ja nav sakritības.
Izmantot FindNext metode (vai FindPrevious), lai atrastu nākamo (iepriekšējo) notikumu.
Sekojošais kods mainīs fontu uz “Arial Black” visām šūnām diapazonā, kas sākas ar “John”:
| 12345 | Katram diapazonā esošajam c ("A1: A100")Ja c Tāpat kā "Jānis*", tadc.Font.Name = "Arial Black"Beigas JaNākamais c |
Šis kods norādītajā diapazonā aizstās visus gadījumus no “Pārbaudīt” uz “Nokārtots”:
| 12345678910 | Ar diapazonu ("a1: a500")Iestatiet c = .Find ("Lai pārbaudītu", LookIn: = xlValues)Ja nē, tad c nav nekaspirmā adrese = c. AdreseVaic.Value = "Nokārtots"Iestatiet c = .FindNext (c)Cilpa, kamēr nav c nav nekas un c. Adrese: pirmā adreseBeigas JaBeigt ar |
Ir svarīgi atzīmēt, ka jums ir jānorāda diapazons, lai izmantotu FindNext. Jums arī jānodrošina apstāšanās nosacījums, pretējā gadījumā cilpa darbosies mūžīgi. Parasti pirmās atrastās šūnas adrese tiek saglabāta mainīgā, un cilpa tiek pārtraukta, kad atkal sasniedzat šo šūnu. Jums arī jāpārbauda gadījums, kad nekas netiek atrasts, lai apturētu cilpu.
Diapazona adrese
Izmantojiet Range.Address, lai iegūtu adresi A1 stilā
| 123 | MsgBox diapazons ("A1: D10"). Adrese'vaiAtkļūdošanas drukas diapazons ("A1: D10"). Adrese |
Izmantojiet xlReferenceStyle (noklusējums ir xlA1), lai iegūtu adreses R1C1 stilā
| 123 | MsgBox diapazons ("A1: D10"). Adrese (ReferenceStyle: = xlR1C1)'vaiAtkļūdošanas drukāšanas diapazons ("A1: D10"). Adrese (ReferenceStyle: = xlR1C1) |
Tas ir noderīgi, ja strādājat ar mainīgajos saglabātajiem diapazoniem un vēlaties apstrādāt tikai noteiktām adresēm.
Diapazons līdz masīvam
Ātrāk un vieglāk ir pārsūtīt diapazonu uz masīvu un pēc tam apstrādāt vērtības. Jums vajadzētu deklarēt masīvu kā variantu, lai neaprēķinātu izmēru, kas nepieciešams masīva diapazona aizpildīšanai. Masīva izmēri ir iestatīti tā, lai tie atbilstu diapazona vērtību skaitam.
| 123456789 | Dim DirArray kā variants'Saglabājiet masīva diapazona vērtībasDirArray = Diapazons ("a1: a5"). Vērtība“Atkārtojiet vērtību apstrādiKatram c In DirArrayAtkļūdot. Drukāt cNākamais |
Masīvs uz diapazonu
Pēc apstrādes jūs varat rakstīt masīvu atpakaļ diapazonā. Lai rakstītu masīvu iepriekš minētajā piemērā diapazonam, jums jānorāda diapazons, kura lielums atbilst masīva elementu skaitam.
Izmantojiet zemāk esošo kodu, lai masīvu ierakstītu diapazonā D1: D5:
| 123 | Diapazons ("D1: D5"). Vērtība = DirArrayDiapazons ("D1: H1"). Vērtība = lietojumprogramma. Transponēt (DirArray) |
Lūdzu, ņemiet vērā, ka masīvs ir jātransponē, ja to rakstāt rindā.
Summa
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Diapazons ("A1: A10"))Atkļūdot. Drukāt SumOfRange |
VBA kodā varat izmantot daudzas programmā Excel pieejamās funkcijas, norādot Application.WorkSheetFunction. pirms funkcijas nosaukuma, kā parādīts iepriekšējā piemērā.
Skaitīšanas diapazons
| 1234567 | 'Skaitīt šūnu skaitu ar skaitļiem diapazonāCountOfCells = Application.WorksheetFunction.Count (Diapazons ("A1: A10"))Atkļūdot. Drukāt CountOfCells'Skaitīt tukšo šūnu skaitu diapazonāCountOfNonBlankCells = Application.WorksheetFunction.CountA (Diapazons ("A1: A10"))Atkļūdot. Drukāt CountOfNonBlankCells |
Autors: Vinamra Čandra
