Диапазони и клетки във VBA
Електронните таблици на Excel съхраняват данни в клетки. Клетките са подредени в редове и колони. Всяка клетка може да бъде идентифицирана по точката на пресичане на нейния ред и колона (Примери B3 или R3C2).
Диапазон на Excel се отнася до една или повече клетки (напр. A3: B4)
Адрес на клетката
Нотация A1
В нотация A1, клетка се обозначава с буквата на колоната (от A до XFD), последвана от номера на реда (от 1 до 1 048 576).
Във VBA можете да се обърнете към всяка клетка, използвайки Range Object.
| 123456789 | 'Вижте клетка В4 на текущо активния листОбхват на MsgBox ("B4")„Вижте клетка В4 на листа, наречена„ Данни “Работни листове на MsgBox ("Данни"). Обхват ("B4")„Вижте клетка В4 на листа, наречена„ Данни “в друга ОТВОРЕНА работна книгас име „Моите данни“Работни книги на MsgBox ("Моите данни"). Работни листове ("Данни"). Обхват ("B4") |
Обозначение R1C1
В нотация R1C1 клетка се обозначава с R, последвано от номер на ред, след това буква „C“, последвана от номера на колоната. напр. B4 в нотация R1C1 ще бъде посочена от R4C2. Във VBA използвате Клетъчен обект да се използва R1C1 нотация:
| 12 | 'Вижте клетка R [6] C [4], т. Е. D6Клетки (6, 4) = "D6" |
Обхват на клетките
Нотация A1
За да се обърнете към повече от една клетка, използвайте „:“ между началния адрес на клетката и последния адрес на клетката. Следното ще се отнася за всички клетки от А1 до D10:
| 1 | Диапазон ("A1: D10") |
Обозначение R1C1
За да се обърнете към повече от една клетка, използвайте „,“ между началния адрес на клетката и последния адрес на клетката. Следното ще се отнася за всички клетки от А1 до D10:
| 1 | Обхват (клетки (1, 1), клетки (10, 4)) |
Писане в клетки
За да запишете стойности в клетка или съседна група от клетки, просто се обърнете към диапазона, поставете знак = и след това напишете стойността, която да се съхранява:
| 12345678910 | „Съхранявайте F5 в клетка с адрес F6Диапазон ("F6") = "F6"„Съхранявайте E6 в клетка с адрес R [6] C [5], т. Е. E6Клетки (6, 5) = "E6"„Съхранявайте A1: D10 в диапазона A1: D10Диапазон ("A1: D10") = "A1: D10"' илиОбхват (клетки (1, 1), клетки (10, 4)) = "A1: D10" |
Четене от клетки
За да прочетете стойности от клетки, просто се обърнете към променливата, за да съхраните стойностите, поставете знак = и след това се обърнете към диапазона за четене:
| 1234567891011 | Dim val1Dim val2„Прочетете от клетка F6val1 = Обхват ("F6")„Прочетете от клетка E6val2 = Клетки (6, 5)MsgBox val1Msgbox val2 |
Забележка: За да съхранявате стойности от диапазон от клетки, трябва да използвате масив вместо проста променлива.
Несъседни клетки
За да се обърнете към несъседни клетки, използвайте запетая между адресите на клетките:
| 123456 | 'Съхранявайте 10 в клетки A1, A3 и A5Обхват ("A1, A3, A5") = 10„Съхранявайте 10 в клетки A1: A3 и D1: D3)Обхват ("A1: A3, D1: D3") = 10 |
Пресичане на клетки
За да се позовавате на несъседни клетки, използвайте интервал между адресите на клетките:
| 123 | „Съхранявайте„ Col D “в D1: D10„което е често срещано между A1: D10 и D1: F10Диапазон ("A1: D10 D1: G10") = "Колон D" |
Отместване от клетка или диапазон
Използвайки функцията Offset, можете да преместите препратката от даден диапазон (клетка или група от клетки) с посочените number_of_rows и number_of_columns.
Офсетов синтаксис
Range.Offset (брой_на_стрели, номер_на_колони)
Отместване от клетка
| 12345678910111213141516 | 'OFFSET от клетка A1'Вижте самата клетка'Преместете 0 реда и 0 колониДиапазон ("A1"). Офсет (0, 0) = "A1"„Преместете 1 ред и 0 колониДиапазон ("A1"). Офсет (1, 0) = "A2"'Преместете 0 реда и 1 колониДиапазон ("A1"). Офсет (0, 1) = "B1"„Преместете 1 ред и 1 колонаДиапазон ("A1"). Офсет (1, 1) = "B2"„Преместете 10 реда и 5 колониДиапазон ("A1"). Офсет (10, 5) = "F11" |
Отместване от диапазон
| 123 | „Преместете препратката към диапазон A1: D4 с 4 реда и 4 колони„Новата справка е E5: H8Обхват ("A1: D4"). Офсет (4,4) = "E5: H8" |
Задаване на препратка към диапазон
За да присвоите диапазон на променлива на диапазон: декларирайте променлива от тип Range и след това използвайте командата Set, за да я зададете на диапазон. Моля, обърнете внимание, че трябва да използвате командата SET, тъй като RANGE е обект:
| 12345678 | 'Декларирайте променлива RangeЗатъмнете myRange като Range'Настройте променливата на диапазона A1: D4Задайте myRange = Range ("A1: D4")'Отпечатва $ A $ 1: $ D $ 4MsgBox myRange.Address |
Преоразмерете диапазон
Методът за преоразмеряване на обекта Range променя размера на референтния диапазон:
| 1234567 | Затъмнете myRange As Range„Диапазон за преоразмеряванеЗадайте myRange = Range ("A1: F4")'Отпечатва $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Address |
Горната лява клетка на диапазона с изменен размер е същата като горната лява клетка на оригиналния диапазон
Преоразмеряване на синтаксиса
Range.Resize (number_of_rows, number_of_columns)
OFFSET срещу Resize
Офсетът не променя размерите на диапазона, а го премества с посочения брой редове и колони. Преоразмеряването не променя позицията на оригиналния диапазон, но променя размерите до определения брой редове и колони.
Всички клетки в лист
Обектът Клетки се отнася до всички клетки в листа (1048576 реда и 16384 колони).
| 12 | „Изчистете всички клетки в работни листовеКлетки. Ясно |
UsedRange
Свойството UsedRange ви дава правоъгълния диапазон от горната лява клетка, използвана клетка, до дясната долна използвана клетка на активния лист.
| 1234567 | Затъмнете като работен листЗадайте ws = ActiveSheet'$ B $ 2: $ L $ 14, ако L2 е първата клетка с произволна стойност'и L14 е последната клетка с всякаква стойност на'активен листDebug.Print ws.UsedRange.Address |
CurrentRegion
Свойството CurrentRegion ви дава непрекъснат правоъгълен диапазон от горната лява клетка до дясно-долната използвана клетка, съдържаща референтната клетка/диапазон.
| 1234567891011 | Затъмнете myRange As RangeЗадайте myRange = Range ("D4: F6")'Отпечатва $ B $ 2: $ L $ 14„Ако има пълен път от D4: F16 до B2 И L14Debug.Print myRange.CurrentRegion.Address„Можете да се обърнете и към една начална клеткаSet myRange = Range ("D4") 'Отпечатва $ B $ 2: $ L $ 14 |
Свойства на диапазона
Можете да получите адрес, номер на ред/колона на клетка и брой редове/колони в диапазон, както е дадено по -долу:
| 123456789101112131415161718192021 | Затъмнете myRange As RangeЗадайте myRange = Range ("A1: F10")'Отпечатва $ A $ 1: $ F $ 10Отстраняване на грешки. Отпечатайте myRange.AddressЗадайте myRange = Range ("F10")„Отпечатва 10 за ред 10Debug.Print myRange.Row„Отпечатва 6 за колона FОтстраняване на грешки. Отпечатайте myRange.ColumnЗадайте myRange = Range ("E1: F5")„Отпечатва 5 за брой редове в обхватаDebug.Print myRange.Rows.Count'Отпечатва 2 за брой колони в обхватаDebug.Print myRange.Columns.Count |
Последна клетка в лист
Можеш да използваш Редове. Брой и Колони. Брой имоти с Клетки обект, за да получите последната клетка на листа:
| 1234567891011 | 'Отпечатайте номера на последния ред„Отпечатва 1048576Debug.Print "Rows in the sheet:" & Rows.Count'Отпечатайте номера на последната колона„Отпечатва 16384Debug.Print "Columns in the sheet:" & Columns.Count'Отпечатайте адреса на последната клетка„Отпечатва $ XFD $ 1048576Debug.Print "Адрес на последната клетка в листа:" & Cells (Rows.Count, Columns.Count) |
Последно използван номер на ред в колона
Свойството END ви отвежда последната клетка в диапазона, а End (xlUp) ви отвежда до първата използвана клетка от тази клетка.
| 123 | Dim lastRow As LonglastRow = Клетки (Rows.Count, "A"). End (xlUp) .Row |
Последно използван номер на колона в ред
| 123 | Dim LastCol As LonglastCol = клетки (1, колони. брой). край (xlToLeft). колона |
Свойството END ви отвежда последната клетка в диапазона, а End (xlToLeft) ви отвежда наляво до първата използвана клетка от тази клетка.
Можете също да използвате свойствата xlDown и xlToRight, за да навигирате до първите долни или десни използвани клетки на текущата клетка.
Свойства на клетката
Общи свойства
Ето код за показване на често използвани свойства на клетката
| 12345678910111213141516171819202122 | Затъмняване на клетката като обхватЗадаване на клетка = Обхват ("A1")клетка.АктивирайтеDebug.Print cell.Address„Отпечатайте $ A $ 1Debug.Print cell.Value„Печат 456„АдресОтстраняване на грешки. Печат на клетка. Формула'Отпечатъци = SUM (C2: C3)'КоментирайтеDebug.Print cell.Comment.Text'СтилDebug.Print cell.Style'Формат на клеткатаDebug.Print cell.DisplayFormat.NumberFormat |
Клетъчен шрифт
Обектът Cell.Font съдържа свойства на Cell Font:
| 1234567891011121314151617181920 | Затъмняване на клетката като обхватЗадаване на клетка = Обхват ("A1")„Обикновен, курсив, удебелен и удебелен курсивcell.Font.FontStyle = "Удебелен курсив"' Същото катоcell.Font.Bold = Вярноcell.Font.Italic = Вярно'Задайте шрифт на Куриерcell.Font.FontStyle = "Куриер"'Задайте цвят на шрифтаcell.Font.Color = vbBlue' илиcell.Font.Color = RGB (255, 0, 0)'Задайте размер на шрифтаcell.Font.Size = 20 |
Копиране и поставяне
Поставяне на всички
Диапазоните/клетките могат да бъдат копирани и поставени от едно място на друго. Следният код копира всички свойства на изходния диапазон до целевия диапазон (еквивалентен на CTRL-C и CTRL-V)
| 1234567 | „Просто копиеДиапазон ("A1: D20"). КопиранеРаботни листове ("Sheet2"). Диапазон ("B10"). Поставяне'или„Копиране от текущия лист на лист с име„ Sheet2 “Обхват ("A1: D20"). Дестинация за копиране: = Работни листове ("Sheet2"). Диапазон ("B10") |
Специална паста
Избраните свойства на обхвата на източника могат да бъдат копирани до местоназначението с помощта на опцията PASTESPECIAL:
| 123 | 'Поставете диапазона само като стойностиДиапазон ("A1: D20"). КопиранеРаботни листове ("Sheet2"). Диапазон ("B10"). PasteSpecial Paste: = xlPasteValues |
Ето възможните опции за опцията Поставяне:
| 12345678910111213 | „Поставяне на специални типовеxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
Съдържание на AutoFit
Размерът на редовете и колоните може да бъде променен, за да пасне на съдържанието, като използвате кода по -долу:
| 12345 | „Променете размера на редове от 1 до 5, за да отговарят на съдържаниетоРедове („1: 5“). AutoFit„Променете размера на колоните от А до В, за да пасне на съдържаниетоКолони ("A: B"). AutoFit |
Още примери за диапазон
Препоръчително е да използвате Macro Recorder, докато извършвате необходимите действия чрез GUI. Това ще ви помогне да разберете различните налични опции и как да ги използвате.
За всеки
По -лесно е да преминете през диапазон с помощта За всеки конструирайте, както е показано по -долу:
| 123 | За всяка клетка в обхвата ("A1: B100")- Направи нещо с клеткатаСледващата клетка |
При всяка итерация на цикъла една клетка в диапазона се присвоява на променливата c и инструкциите в цикъла For се изпълняват за тази клетка. Цикълът излиза, когато се обработят всички клетки.
Вид
Сортирането е метод на Range обект. Можете да сортирате диапазон, като посочите опции за сортиране към Range.Sort. Кодът по -долу ще сортира колоните A: C въз основа на ключ в клетка C2. Редът за сортиране може да бъде xlAscending или xlDcecending. Заглавка: = xl Да, трябва да се използва, ако първият ред е заглавният ред.
| 12 | Колони ("A: C"). Клавиш за сортиране1: = Диапазон ("C2"), _order1: = xlAscending, Header: = xlДа |
намирам
Find също е метод на Range Object. Той намира първата клетка със съдържание, отговарящо на критериите за търсене, и връща клетката като обект Range. Връща се Нищо ако няма съвпадение.
Използвайте Намери следващото метод (или FindPrevious) за намиране на следващо (предишно) възникване.
Следният код ще промени шрифта на „Arial Black“ за всички клетки в диапазона, които започват с „John“:
| 12345 | За всеки c в обхват ("A1: A100")Ако c Като "Джон*", тогаваc.Font.Name = "Arial Black"Край АкоСледващ c |
Следният код ще замени всички събития от „To test“ на „Passed“ в посочения диапазон:
| 12345678910 | С обхват ("a1: a500")Задайте c = .Find ("За тестване", LookIn: = xlValues)Ако не c Няма нищоfirstaddress = c.АдресНаправетеc.Value = "Премина"Задайте c =. FindNext (c)Цикъл, докато не е c Нищо и c.Адрес firstaddressКрай АкоКрай с |
Важно е да се отбележи, че трябва да посочите диапазон, за да използвате FindNext. Също така трябва да предоставите условие за спиране, в противен случай цикълът ще се изпълнява завинаги. Обикновено адресът на първата намерена клетка се съхранява в променлива и цикълът се спира, когато достигнете отново до тази клетка. Трябва също да проверите за случай, когато нищо не е намерено, за да спре цикъла.
Адрес на обхвата
Използвайте Range.Address, за да получите адреса в стил A1
| 123 | MsgBox Range ("A1: D10"). Адрес' илиDebug.Print Range ("A1: D10"). Адрес |
Използвайте xlReferenceStyle (по подразбиране е xlA1), за да получите адреси в стил R1C1
| 123 | MsgBox Range ("A1: D10"). Адрес (ReferenceStyle: = xlR1C1)' илиDebug.Print Range ("A1: D10"). Адрес (ReferenceStyle: = xlR1C1) |
Това е полезно, когато се занимавате с диапазони, съхранявани в променливи и искате да обработвате само за определени адреси.
Обхват до масив
По -бързо и лесно е да се прехвърли диапазон в масив и след това да се обработят стойностите. Трябва да декларирате масива като вариант, за да избегнете изчисляването на размера, необходим за попълване на диапазона в масива. Размерите на масива са зададени да съвпадат с броя на стойностите в диапазона.
| 123456789 | Dim DirArray като вариант'Съхранявайте стойностите в диапазона до масиваDirArray = Диапазон ("a1: a5"). Стойност'Цикъл за обработка на стойноститеЗа всеки c В DirArrayОтстраняване на грешки Печат cСледващия |
Масив за обхват
След обработката можете да запишете масива обратно в диапазон. За да напишете масива в горния пример в диапазон, трябва да посочите диапазон, чийто размер съвпада с броя на елементите в масива.
Използвайте кода по -долу, за да запишете масива в диапазона D1: D5:
| 123 | Диапазон ("D1: D5"). Стойност = DirArrayДиапазон ("D1: H1"). Стойност = Приложение. Транспониране (DirArray) |
Моля, обърнете внимание, че трябва да транспонирате масива, ако го записвате в ред.
Обхват на сумата
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Диапазон ("A1: A10"))Отстраняване на грешки. Отпечатайте SumOfRange |
Можете да използвате много функции, налични в Excel във вашия VBA код, като посочите Application.WorkSheetFunction. преди Името на функцията, както в горния пример.
Графичен диапазон
| 1234567 | „Пребройте броя на клетките с числа в диапазонаCountOfCells = Application.WorksheetFunction.Count (Обхват ("A1: A10"))Debug.Print CountOfCells'Брой Брой непразни клетки в диапазонаCountOfNonBlankCells = Application.WorksheetFunction.CountA (Range ("A1: A10"))Debug.Print CountOfNonBlankCells |
Написано от: Винамра Чандра
