Діапазони та клітинки у VBA
Таблиці Excel зберігають дані в осередках. Клітини розташовані в рядках і стовпцях. Кожну клітинку можна ідентифікувати за точкою перетину її рядка та стовпця (наприклад, B3 або R3C2).
Діапазон Excel відноситься до однієї або кількох клітинок (наприклад, A3: B4)
Адреса мобільного телефону
Позначення А1
У позначенні A1 клітинка позначається літерою стовпця (від A до XFD), за якою йде номер рядка (від 1 до 1048 576).
У VBA ви можете звернутися до будь -якої клітинки за допомогою Об'єкт діапазону.
| 123456789 | 'Зверніться до комірки B4 на поточному активному аркушіДіапазон 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")'Читати з комірки Е6val2 = Клітини (6, 5)MsgBox val1Msgbox val2 |
Примітка: Щоб зберігати значення з діапазону клітинок, вам потрібно використовувати масив замість простої змінної.
Несуміжні клітини
Для посилання на несуміжні комірки використовуйте кому між адресами комірок:
| 123456 | 'Зберігайте 10 у клітинках А1, А3 та А5Діапазон ("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" |
Зсув від комірки або діапазону
Використовуючи функцію Зсув, ви можете перемістити посилання з заданого діапазону (клітинки або групи клітинок) на вказане число_стрілок та число_ стовпців.
Синтаксис зміщення
Діапазон. Зсув (кількість_стрілів, кількість_столбців)
Зсув від комірки
| 12345678910111213141516 | 'ЗМІЩЕННЯ від комірки А1'Зверніться до самої клітини'Перемістити 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 як діапазон'Встановіть для змінної діапазон A1: D4Встановити myRange = Діапазон ("A1: D4")'Друкує $ A $ 1: $ D $ 4MsgBox myRange.Address |
Змінити розмір діапазону
Метод зміни розміру об'єкта Діапазон змінює розмір опорного діапазону:
| 1234567 | Приглушити мій діапазон як діапазон'Діапазон для зміни розміруВстановити myRange = Діапазон ("A1: F4")'Друкує $ A $ 1: $ E $ 10Налагодження.Надрукувати myRange.Resize (10, 5) .Address |
Ліва верхня клітинка діапазону змінених розмірів така ж, як і верхня ліва комірка вихідного діапазону
Змінити розмір синтаксису
Діапазон. Змінити розмір (кількість_стрілів, кількість_столбців)
OFFSET vs Resize
Зсув не змінює розміри діапазону, а переміщує його на вказану кількість рядків і стовпців. Зміна розміру не змінює положення вихідного діапазону, але змінює розміри на зазначену кількість рядків і стовпців.
Усі клітинки на аркуші
Об'єкт "Клітинки" відноситься до всіх клітинок аркуша (1048576 рядків і 16384 стовпців).
| 12 | 'Очистити всі клітинки на аркушахКлітини. Очистити |
UsedRange
Властивість UsedRange дає вам прямокутний діапазон від верхньої лівої комірки використаної комірки до правої нижньої комірки активного аркуша.
| 1234567 | Затемнити як робочий аркушВстановіть ws = ActiveSheet'$ B $ 2: $ L $ 14, якщо L2 - це перша клітинка з будь -яким значенням'і L14 - остання клітинка з будь -яким значенням на'активний аркушDebug.Print ws.UsedRange.Address |
Поточний регіон
Властивість CurrentRegion дає вам безперервний прямокутний діапазон від верхньої лівої комірки до правої нижньої комірки, що містить осередку/діапазон, на який посилається.
| 1234567891011 | Приглушити мій діапазон як діапазонВстановити myRange = Діапазон ("D4: F6")'Друкує $ B $ 2: $ L $ 14'Якщо є заповнений шлях від D4: F16 до B2 І L14Налагодження.Надрукувати myRange.CurrentRegion.Address'Ви також можете звернутися до однієї вихідної клітинкиВстановити myRange = Діапазон ("D4") 'Друкує $ B $ 2: $ L $ 14 |
Властивості діапазону
Ви можете отримати адресу, номер рядка/стовпця комірки та кількість рядків/стовпців у діапазоні, як наведено нижче:
| 123456789101112131415161718192021 | Приглушити мій діапазон як діапазонВстановити myRange = Діапазон ("A1: F10")'Друкує $ A $ 1: $ F $ 10Налагодження.Надрукувати myRange.AddressВстановити myRange = Діапазон ("F10")'Друкує 10 для 10 -го рядкаНалагодження.Надрукувати myRange.Row'Друкує 6 для колонки FНалагодження.Надрукувати myRange.ColumnВстановити myRange = Діапазон ("E1: F5")'Друкує 5 для кількості рядків у діапазоніНалагодження.Надрукувати myRange.Rows.Count'Друкує 2 для кількості стовпців в діапазоніНалагодження.Надрукувати myRange.Columns.Count |
Остання клітинка в аркуші
Ви можете використовувати Ряди та Стовпці. Кількість властивості с Клітини об'єкт, щоб отримати останню клітинку на аркуші:
| 1234567891011 | 'Роздрукуйте номер останнього рядка'Друкує 1048576Налагодження.Надрукуйте "Рядки на аркуші:" & Rows.Count'Роздрукуйте номер останнього стовпця'Друкує 16384Debug.Print "Стовпці на аркуші:" & 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")АктивуватиНалагодження. Друк комірки. Адреса'Друк $ A $ 1Налагодження. Друк комірки. Значення'Друкує 456'АдресаНалагодження. Друк комірки. Формула'Відбитки = SUM (C2: C3)'ПрокоментуйтеНалагодження. Друк комірки. Коментар. Текст'СтильНалагодження. Друк комірки. Стиль'Формат коміркиНалагодження. Друк комірки. DisplayFormat.NumberFormat |
Шрифт комірки
Об'єкт Cell.Font містить властивості шрифту комірки:
| 1234567891011121314151617181920 | Яскрава клітинка як діапазонВстановити клітинку = Діапазон ("A1")'Звичайний, курсив, жирний та жирний курсивcell.Font.FontStyle = "Жирний курсив"' Такий же, якcell.Font.Bold = Істинаcell.Font.Italic = Істина'Встановити шрифт на Couriercell.Font.FontStyle = "Кур'єр"'Встановити колір шрифтуcell.Font.Color = vbBlue'абоcell.Font.Color = RGB (255, 0, 0)'Встановити розмір шрифтукомірка.Розмір шрифту = 20 |
Копіювати і вставляти
Вставити все
Діапазони/комірки можна копіювати та вставляти з одного місця в інше. Наступний код копіює всі властивості вихідного діапазону до діапазону призначення (еквівалентно CTRL-C та CTRL-V)
| 1234567 | «Проста копіяДіапазон ("A1: D20"). КопіюватиРобочі аркуші ("Аркуш2"). Діапазон ("В10"). Вставити'або'Копіювати з поточного аркуша на аркуш з назвою' Sheet2 'Діапазон ("A1: D20"). Місце копіювання: = Робочі аркуші ("Аркуш2"). Діапазон ("B10") |
Спеціальна вставка
Вибрані властивості вихідного діапазону можна скопіювати до місця призначення за допомогою опції PASTESPECIAL:
| 123 | 'Вставте діапазон лише як значенняДіапазон ("A1: D20"). КопіюватиРобочі аркуші ("Sheet2"). Діапазон ("B10"). PasteSpecial Paste: = xlPasteValues |
Ось можливі варіанти опції Вставити:
| 12345678910111213 | 'Вставити спеціальні типиxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
Вміст AutoFit
Розмір рядків і стовпців можна змінити відповідно до вмісту, використовуючи код нижче:
| 12345 | 'Змініть розмір рядків від 1 до 5 відповідно до вмістуРяди ("1: 5"). Автопідбір'Змініть розмір стовпців А на В відповідно до вмістуСтовпці ("A: B"). Автопідбір |
Більше прикладів діапазону
Рекомендується використовувати Macro Recorder під час виконання необхідних дій через графічний інтерфейс. Це допоможе вам зрозуміти різні доступні варіанти та способи їх використання.
Для кожного
Простіше прокрутити діапазон за допомогою Для кожного побудувати, як показано нижче:
| 123 | Для кожної клітинки в діапазоні ("A1: B100")'Зробіть щось із клітиноюНаступна клітина |
На кожній ітерації циклу одна клітинка в діапазоні призначається змінній c, і оператори в циклі For виконуються для цієї комірки. Цикл виходить, коли всі комірки обробляються.
Сортувати
Сортування - це метод об'єкта Range. Ви можете сортувати діапазон, вказавши параметри сортування до Range.Sort. Нижче наведений код буде сортувати стовпці A: C на основі ключа в комірці C2. Порядок сортування може бути xlз зростанням або xlз спаданням. Заголовок: = xlТак слід використовувати, якщо перший рядок - це рядок заголовка.
| 12 | Стовпці ("A: C"). Клавіша сортування1: = Діапазон ("C2"), _order1: = xlВисхідний, Заголовок: = xlТак |
Знайти
Find також є методом Range Object. Він знаходить першу клітинку з вмістом, що відповідає критеріям пошуку, і повертає клітинку як об’єкт Range. Воно повертається Нічого якщо немає відповідності.
Використовуйте FindNext метод (або FindPrevious), щоб знайти наступне (попереднє) входження.
Наступний код змінить шрифт на “Arial Black” для всіх клітинок у діапазоні, що починається на “John”:
| 12345 | Для кожного c в діапазоні ("A1: A100")Якщо c Як "John*" Тодіc.Font.Name = "Arial Black"Закінчити ЯкщоДалі c |
Наступний код замінить усі випадки "Тестувати" на "Пройдено" у вказаному діапазоні:
| 12345678910 | З діапазоном ("a1: a500")Встановіть c = .Find ("Для перевірки", LookIn: = xlValues)Якщо ні, то нічогоfirstaddress = c.АдресаЗробитиc.Value = "Пройдено"Встановіть c =. FindNext (c)Цикл, поки не є c Ніщо І c.Адреса перша адресаЗакінчити ЯкщоЗакінчити з |
Важливо відзначити, що для використання FindNext необхідно вказати діапазон. Також ви повинні надати умову зупинки, інакше цикл буде виконуватися назавжди. Зазвичай адреса першої знайденої комірки зберігається у змінній, і цикл зупиняється, коли ви знову досягаєте цієї комірки. Ви також повинні перевірити на випадок, коли нічого не знайдено, щоб зупинити цикл.
Адреса діапазону
Використовуйте Range.Address, щоб отримати адресу у стилі A1
| 123 | Діапазон MsgBox ("A1: D10"). Адреса'абоНалаштування діапазону друку ("A1: D10"). Адреса |
Використовуйте xlReferenceStyle (за замовчуванням xlA1), щоб отримати адреси у стилі R1C1
| 123 | Діапазон MsgBox ("A1: D10"). Адреса (ReferenceStyle: = xlR1C1)'абоНалаштування діапазону друку ("A1: D10"). Адреса (ReferenceStyle: = xlR1C1) |
Це корисно, коли ви маєте справу з діапазонами, що зберігаються у змінних, і хочете обробити лише певні адреси.
Діапазон до масиву
Швидше і простіше передати діапазон до масиву, а потім обробити значення. Вам слід оголосити масив як Variant, щоб уникнути обчислення розміру, необхідного для заповнення діапазону в масиві. Розміри масиву відповідають кількості значень у діапазоні.
| 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"))Налаштування.Надрукувати кількість клітин'Підрахувати кількість непустих клітинок у діапазоніCountOfNonBlankCells = Application.WorksheetFunction.CountA (Діапазон ("A1: A10"))Debug.Print CountOfNonBlankCells |
Автор: Вінамра Чандра
