Razpon in celice Excel VBA

Merila in celice v VBA

Excelove preglednice shranjujejo podatke v celicah. Celice so razporejene v vrstice in stolpce. Vsako celico je mogoče identificirati s presečiščem njene vrstice in stolpca (npr. B3 ali R3C2).

Razpon Excel se nanaša na eno ali več celic (npr. A3: B4)

Naslov celice

Zapis A1

V zapisu A1 je celica označena s črko stolpca (od A do XFD), ki ji sledi številka vrstice (od 1 do 1.048.576).

V VBA se lahko sklicujete na katero koli celico z uporabo Objekt obsega.

123456789

'Glejte celico B4 na trenutno aktivnem listuMsgBox Range ("B4")"Glejte celico B4 na listu z imenom" Podatki "Delovni listi MsgBox ("Podatki"). Obseg ("B4")"Glejte celico B4 na listu z imenom" Podatki "v drugem ODPRTEM delovnem zvezkuz imenom 'Moji podatki'Delovni zvezki MsgBox ("Moji podatki"). Delovni listi ("Podatki"). Obseg ("B4")

Zapis R1C1

V zapisu R1C1 je celica označena z R, ki ji sledi številka vrstice, nato črka „C“, ki ji sledi številka stolpca. npr. B4 v zapisu R1C1 bo omenjen z R4C2. V VBA uporabljate Objekt celic uporabiti zapis R1C1:

12	'Glej celico R [6] C [4], to je D6Celice (6, 4) = "D6"

Obseg celic

Zapis A1

Če se želite sklicevati na več celic, uporabite »:« med naslovom začetne celice in naslovom zadnje celice. Naslednje se nanaša na vse celice od A1 do D10:

1	Obseg ("A1: D10")

Zapis R1C1

Če se želite sklicevati na več celic, uporabite »,« med naslovom začetne celice in zadnjo celico. Naslednje se nanaša na vse celice od A1 do D10:

1	Obseg (celice (1, 1), celice (10, 4))

Pisanje v celice

Če želite vrednosti zapisati v celico ali sosednjo skupino celic, se preprosto obrnite na obseg, postavite znak = in nato napišite vrednost, ki jo želite shraniti:

12345678910

'Shranite F5 v celico z naslovom F6Obseg ("F6") = "F6"„Shranite E6 v celico z naslovom R [6] C [5], tj. E6Celice (6, 5) = "E6"'Shranite A1: D10 v območju A1: D10Obseg ("A1: D10") = "A1: D10"'ozObseg (celice (1, 1), celice (10, 4)) = "A1: D10"

Branje iz celic

Če želite prebrati vrednosti iz celic, se preprosto obrnite na spremenljivko, da shranite vrednosti, postavite znak = in se nato obrnite na obseg, ki ga želite prebrati:

1234567891011

Dim val1Dim val2'Branje iz celice F6val1 = Razpon ("F6")'Preberite iz celice E6val2 = Celice (6, 5)MsgBox val1Sporočilo val2

Opomba: Če želite shraniti vrednosti iz obsega celic, morate namesto preproste spremenljivke uporabiti niz.

Ne sosednje celice

Če se želite sklicevati na celice, ki niso povezane, uporabite vejico med naslovi celic:

123456

'Shranite 10 v celicah A1, A3 in A5Razpon ("A1, A3, A5") = 10'Shranite 10 v celicah A1: A3 in D1: D3)Razpon ("A1: A3, D1: D3") = 10

Presek celic

Za sklicevanje na neprekinjene celice uporabite presledek med naslovi celic:

123	'Shranite' Col D 'v D1: D10', kar je običajno med A1: D10 in D1: F10Obseg ("A1: D10 D1: G10") = "Stol D"

Odmik od celice ali območja

S funkcijo Odmik lahko sklic iz določenega obsega (celice ali skupine celic) premaknete za podano število_številk_številk in_številk_ stolpcev.

Odstopna sintaksa

Obseg. Odmik (število_smer, število_ stolpcev)

Odmik od celice

12345678910111213141516

'OFFSET iz celice A1'Glej celico samo'Premakni 0 vrstic in 0 stolpcevRazpon ("A1"). Odmik (0, 0) = "A1"'Premakni 1 vrstico in 0 stolpcevRazpon ("A1"). Odmik (1, 0) = "A2"'Premakni 0 vrstic in 1 stolpecRazpon ("A1"). Odmik (0, 1) = "B1"'Premakni 1 vrstico in 1 stolpecRazpon ("A1"). Odmik (1, 1) = "B2"'Premakni 10 vrstic in 5 stolpcevRazpon ("A1"). Odmik (10, 5) = "F11"

Odmik od razpona

123	'Premakni sklic na obseg A1: D4 za 4 vrstice in 4 stolpce„Nova referenca je E5: H8Razpon ("A1: D4"). Odmik (4,4) = "E5: H8"

Nastavitev sklica na obseg

Če želite razponu dodeliti spremenljivko obsega: razglasite spremenljivko tipa Range in jo nato z ukazom Set nastavite na obseg. Upoštevajte, da morate uporabiti ukaz SET, saj je predmet RANGE:

12345678

'Razglasite spremenljivko RangeZatemni myRange kot obseg'Spremenljivko nastavite na obseg A1: D4Nastavi myRange = Razpon ("A1: D4")'Natisne $ A $ 1: $ D $ 4MsgBox myRange.Address

Spremenite velikost obsega

Metoda spreminjanja velikosti predmeta Range spremeni dimenzijo referenčnega območja:

1234567

Zatemni myRange As Range'Razpon za spreminjanje velikostiNastavi myRange = Razpon ("A1: F4")'Natisne $ A $ 1: $ E $ 10Odpravljanje napak Natisni myRange.Resize (10, 5) .Naslov

Zgornja leva celica spremenjenega obsega je enaka zgornji levi celici prvotnega obsega

Spremeni velikost sintakse

Obseg.

OFFSET vs Resize

Odmik ne spremeni dimenzij obsega, ampak ga premakne za določeno število vrstic in stolpcev. Spreminjanje velikosti ne spremeni položaja prvotnega obsega, ampak spremeni dimenzije na določeno število vrstic in stolpcev.

Vse celice na listu

Objekt Celice se nanaša na vse celice na listu (1048576 vrstic in 16384 stolpcev).

12	'Počisti vse celice na delovnih listihCelice. Jasno

Rabljeno območje

Lastnost UsedRange vam daje pravokoten razpon od zgornje leve celice uporabljene celice do desne spodnje uporabljene celice aktivnega lista.

1234567

Zatemni kot delovni listNastavite ws = ActiveSheet'$ B $ 2: $ L $ 14, če je L2 prva celica s poljubno vrednostjo'in L14 je zadnja celica s poljubno vrednostjo na'aktivni listDebug.Print ws.UsedRange.Address

CurrentRegion

Lastnost CurrentRegion vam daje sosednji pravokotni razpon od zgornje leve celice do uporabljene celice desno od dna, ki vsebuje referenčno celico/obseg.

1234567891011

Zatemni myRange As RangeNastavi myRange = Razpon ("D4: F6")'Natisne $ B $ 2: $ L $ 14„Če je zapolnjena pot od D4: F16 do B2 IN L14Debug.Natisni myRange.CurrentRegion.Address"Lahko se sklicujete tudi na eno samo začetno celicoSet myRange = Range ("D4") 'Natisne $ B $ 2: $ L $ 14

Lastnosti razpona

Naslov, številko vrstice/stolpca celice in število vrstic/stolpcev lahko dobite v spodnjem obsegu:

123456789101112131415161718192021

Zatemni myRange As RangeNastavi myRange = Razpon ("A1: F10")'Natisne $ A $ 1: $ F $ 10Debug.Natisni myRange.AddressNastavi myRange = Razpon ("F10")'Natisne 10 za 10. vrsticoDebug.Natisni myRange.Row'Natisne 6 za stolpec FOdpravljanje napak Natisni stolpec myRange.ColumnNastavi myRange = Razpon ("E1: F5")'Natisne 5 za število vrstic v doseguDebug.Natisni myRange.Rows.Count'Natisne 2 za število stolpcev v doseguDebug.Print myRange.Columns.Count

Zadnja celica na listu

Lahko uporabiš Vrstice. Štetje in Stolpci. Štetje lastnosti z Celice predmet, da dobimo zadnjo celico na listu:

1234567891011

'Natisnite številko zadnje vrstice'Natisne 1048576Debug.Print "Vrstice na listu:" & Rows.Count'Natisnite številko zadnjega stolpca'Natisne 16384Debug.Print "Stolpci na listu:" & Columns.Count'Natisnite naslov zadnje celice'Natisne XFD $ 1048576Debug.Print "Naslov zadnje celice na listu:" & Cells (Rows.Count, Columns.Count)

Zadnja uporabljena številka vrstice v stolpcu

Lastnost END vas popelje do zadnje celice v obsegu, End (xlUp) pa do prve uporabljene celice iz te celice.

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

Zadnja uporabljena številka stolpca v vrsti

123	Zatemni lastCol As LonglastCol = celice (1, stolpci.števanje). konec (xlToLeft). stolpec

Lastnost END vas popelje do zadnje celice v obsegu, End (xlToLeft) pa vas popelje levo do prve uporabljene celice iz te celice.

Lastnosti xlDown in xlToRight lahko uporabite tudi za krmarjenje do prvih spodnjih ali desnih uporabljenih celic trenutne celice.

Lastnosti celic

Skupne lastnosti

Tukaj je koda za prikaz pogosto uporabljenih lastnosti celice

12345678910111213141516171819202122

Zatemni celico kot obsegNastavi celico = obseg ("A1")celico.AktivirajOdpravljanje napak. Natisni celico. Naslov'Natisnite $ A $ 1Odpravljanje napak. Natisni celico. Vrednost'Natisne 456'NaslovOdpravljanje napak. Natisni celico. Formula'Natis = SUM (C2: C3)'KomentirajOdpravljanje napak. Natisni celico. Komentiraj. Besedilo'SlogOdpravljanje napak. Natisni celico. Slog'Oblika celiceDebug.Print cell.DisplayFormat.NumberFormat

Pisava celice

Objekt Cell.Font vsebuje lastnosti pisave celice:

1234567891011121314151617181920

Zatemni celico kot obsegNastavi celico = obseg ("A1")„Običajno, ležeče, krepko in krepkocell.Font.FontStyle = "Krepko kurzivno"' Enako kotcell.Font.Bold = Rescell.Font.Italic = True'Nastavi pisavo na Couriercell.Font.FontStyle = "Kurir"'Nastavi barvo pisavecell.Font.Color = vbBlue'ozcell.Font.Color = RGB (255, 0, 0)'Nastavi velikost pisavecelica.Font.Size = 20

Kopiraj in prilepi

Prilepi vse

Obsege/celice je mogoče kopirati in prilepiti z ene lokacije na drugo. Naslednja koda kopira vse lastnosti izvornega območja v ciljno območje (enakovredno CTRL-C in CTRL-V)

1234567

'Enostavna kopijaObseg ("A1: D20"). KopirajDelovni listi ("List2"). Obseg ("B10"). Prilepi'oz'Kopiraj s trenutnega lista na list z imenom' List2 'Obseg ("A1: D20"). Cilj kopiranja: = delovni listi ("List2"). Obseg ("B10")

Posebno lepljenje

Izbrane lastnosti izvornega območja lahko kopirate na cilj z možnostjo PASTESPECIAL:

123	'Obseg prilepite samo kot vrednostiObseg ("A1: D20"). KopirajDelovni listi ("List2"). Obseg ("B10"). PasteSpecial Paste: = xlPasteValues

Tu so možne možnosti za možnost Prilepi:

12345678910111213

'Prilepi posebne vrstexlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats

Vsebina AutoFit

Velikost vrstic in stolpcev lahko spremenite glede na vsebino s spodnjo kodo:

12345

'Spremenite velikost vrstic od 1 do 5, da ustreza vsebiniVrstice ("1: 5"). Samodejno prilagodi'Spremenite velikost stolpcev A v B, da se prilega vsebiniStolpci ("A: B"). Samodejna prilagoditev

Več primerov obsega

Priporočljivo je, da med izvajanjem potrebnih dejanj prek grafičnega vmesnika uporabite makro snemalnik. Pomagal vam bo razumeti različne razpoložljive možnosti in kako jih uporabljati.

Za vsakogar

Z uporabo je lažje krožiti skozi obseg Za vsakogar sestavite, kot je prikazano spodaj:

123	Za vsako celico v razponu ("A1: B100")"Naredi nekaj s celicoNaslednja celica

Pri vsaki iteraciji zanke je spremenljivki c dodeljena ena celica v obsegu, za to celico pa se izvedejo stavki v zanki For. Zanka izstopi, ko so obdelane vse celice.

Razvrsti

Razvrščanje je metoda predmeta Range. Obseg lahko razvrstite tako, da določite možnosti razvrščanja v Range.Sort. Spodnja koda bo razvrstila stolpce A: C glede na ključ v celici C2. Vrstni red razvrščanja je lahko xlAscending ali xlDescending. Glava: = xlDa je treba uporabiti, če je prva vrstica vrstica glave.

12	Stolpci ("A: C"). Tipka za razvrščanje1: = obseg ("C2"), _vrstni red1: = xlAscending, Glava: = xlDa

Najti

Find je tudi metoda Range Object. Najde prvo celico z vsebino, ki ustreza iskalnim kriterijem, in vrne celico kot predmet Range. Vrne se Nič če ni ujemanja

Uporaba FindNext metodo (ali FindPrevious), da bi našli naslednji (prejšnji) pojav.

Naslednja koda bo spremenila pisavo v "Arial Black" za vse celice v razponu, ki se začnejo z "John":

12345

Za vsak c v dosegu ("A1: A100")Če c Kot "John*", potemc.Font.Name = "Arial Black"Konec ČeNaprej c

Naslednja koda bo v navedenem obsegu nadomestila vse pojavitve »Za preizkus« na »Preneseno«:

12345678910

Z dosegom ("a1: a500")Nastavite c = .Find ("Za testiranje", LookIn: = xlValues)Če ni c, potem ni ničfirstaddress = c.NaslovNaredic.Value = "Opravljeno"Nastavite c =. FindNext (c)Zanka Medtem ko ni c Ni nič In c.Naslov prvi naslovKonec ČeKončaj s

Pomembno je omeniti, da morate za uporabo FindNext podati obseg. Vnesti morate tudi pogoj za ustavitev, sicer se bo zanka izvajala večno. Običajno je naslov prve najdene celice shranjen v spremenljivki, zanka pa se ustavi, ko znova pridete do te celice. Prav tako morate preveriti, če ni ničesar, kar bi ustavilo zanko.

Naslov območja

Uporabite Range.Address, da naslov dobite v slogu A1

123	MsgBox Range ("A1: D10"). Naslov'ozObmočje tiskanja za odpravljanje napak ("A1: D10"). Naslov

Uporabite xlReferenceStyle (privzeto je xlA1), da dobite naslove v slogu R1C1

123	Obseg polja MsgBox ("A1: D10"). Naslov (referenčni slog: = xlR1C1)'ozObmočje tiskanja za odpravljanje napak ("A1: D10"). Naslov (referenčni slog: = xlR1C1)

To je uporabno, če se ukvarjate z obsegi, shranjenimi v spremenljivkah, in želite obdelati samo za določene naslove.

Domet do polja

Hitreje in lažje je prenesti obseg v matriko in nato obdelati vrednosti. Polje morate razglasiti kot Variant, da se izognete izračunu velikosti, ki je potrebna za polnjenje obsega v matriki. Dimenzije niza so nastavljene tako, da se ujemajo s številom vrednosti v obsegu.

123456789

Zatemni DirArray kot varianto'Vrednosti shranite v obseg v nizDirArray = Razpon ("a1: a5"). Vrednost'Zanka za obdelavo vrednostiZa vsakega c V DirArrayuOdpravljanje napak cNaslednji

Niz za doseg

Po obdelavi lahko matriko zapišete nazaj v obseg. Če želite zapisati niz v zgornjem primeru v obseg, morate podati obseg, katerega velikost se ujema s številom elementov v nizu.

S spodnjo kodo zapišite niz v obseg D1: D5:

123	Razpon ("D1: D5"). Vrednost = DirArrayObseg ("D1: H1"). Vrednost = Aplikacija.Prenos (DirArray)

Upoštevajte, da morate matriko prestaviti, če jo zapišete v vrstico.

Razpon vsote

12	SumOfRange = Application.WorksheetFunction.Sum (obseg ("A1: A10"))Odpravljanje napak. Natisni SumOfRange

V kodi VBA lahko uporabite številne funkcije, ki so na voljo v Excelu, tako da podate Application.WorkSheetFunction. pred imenom funkcije, kot v zgornjem primeru.

Count Range

1234567

'Štej število celic s številkami v obseguCountOfCells = Application.WorksheetFunction.Count (obseg ("A1: A10"))Odpravljanje napak. Število natisnjenih celic'Šteje število praznih celic v obseguCountOfNonBlankCells = Application.WorksheetFunction.CountA (obseg ("A1: A10"))Debug.Print CountOfNonBlankCells

Zapisala: Vinamra Chandra