Під час роботи з Excel більшість вашого часу проводиться в області робочого аркуша - робота з клітинками та діапазонами.
А якщо ви хочете автоматизувати свою роботу в Excel за допомогою VBA, вам потрібно знати, як працювати з клітинками та діапазонами за допомогою VBA.
З діапазонами у VBA можна зробити багато різних дій (наприклад, вибрати, скопіювати, перемістити, редагувати тощо).
Тому, щоб висвітлити цю тему, я поділю цей підручник на розділи і покажу вам, як працювати з клітинками та діапазонами в Excel VBA, використовуючи приклади.
Давайте розпочнемо.
Усі коди, які я згадую у цьому підручнику, потрібно розмістити у редакторі VB. Перейдіть до розділу «Де розмістити код VBA», щоб дізнатися, як він працює.Якщо ви зацікавлені у вивченні VBA простим способом, ознайомтеся з моїм Онлайн навчання Excel VBA.
Вибір комірки / діапазону в Excel за допомогою VBA
Щоб працювати з клітинками та діапазонами в Excel за допомогою VBA, не потрібно вибирати його.
У більшості випадків краще не вибирати клітинки чи діапазони (як ми побачимо).
Незважаючи на це, важливо пройти цей розділ і зрозуміти, як він працює. Це буде вирішальним у вашому навчанні VBA, і багато концепцій, описаних тут, будуть використані у цьому посібнику.
Тож почнемо з дуже простого прикладу.
Вибір однієї клітинки за допомогою VBA
Якщо ви хочете вибрати одну клітинку в активному аркуші (скажімо, A1), то можете скористатися наведеним нижче кодом:
Діапазон Sub SelectCell () ("A1"). Виберіть End Sub
Наведений вище код містить обов’язкові частини «Sub» та «End Sub» та рядок коду, який вибирає комірку A1.
Діапазон (“A1”) повідомляє VBA адресу комірки, на яку ми хочемо посилатися.
Виберіть є методом об'єкта Range і вибирає комірки/діапазон, зазначені в об'єкті Range. Посилання на клітинки мають бути у подвійних лапках.
Цей код покаже помилку, якщо аркуш діаграми є активним. Аркуш діаграми містить діаграми і не використовується широко. Оскільки він не містить клітинок/діапазонів, вищевказаний код не може його вибрати і в кінцевому підсумку відображається помилка.
Зауважте, що оскільки ви хочете вибрати клітинку в активному аркуші, вам просто потрібно вказати адресу комірки.
Але якщо ви хочете вибрати клітинку на іншому аркуші (скажімо, Sheet2), вам потрібно спочатку активувати Sheet2, а потім вибрати клітинку в ньому.
Sub SelectCell () Робочі аркуші ("Sheet2"). Активуйте діапазон ("A1"). Виберіть End Sub
Подібним чином можна також активувати книгу, потім активувати у ній певний аркуш, а потім вибрати клітинку.
Sub SelectCell () Робочі книги ("Book2.xlsx"). Робочі аркуші ("Sheet2"). Активуйте діапазон ("A1"). Виберіть End Sub
Зауважте, що коли ви звертаєтесь до книг, вам потрібно використовувати повне ім’я разом із розширенням файлу (.xlsx у наведеному вище коді). Якщо книгу ніколи не було збережено, вам не потрібно використовувати розширення файлу.
Тепер ці приклади не дуже корисні, але далі в цьому підручнику ви побачите, як ми можемо використовувати ті самі поняття для копіювання та вставлення клітинок у Excel (за допомогою VBA).
Так само, як ми вибираємо клітинку, ми також можемо вибирати діапазон.
У разі діапазону це може бути діапазон фіксованого розміру або діапазон змінних розмірів.
У фіксованому діапазоні розмірів ви знаєте, наскільки великий діапазон, і можете використовувати точний розмір у своєму коді VBA. Але з діапазоном змінних розмірів ви не уявляєте, наскільки великий діапазон, і вам потрібно використовувати трохи магії VBA.
Давайте подивимось, як це зробити.
Вибір діапазону фіксованого розміру
Ось код, який буде вибирати діапазон A1: D20.
Діапазон Sub SelectRange () ("A1: D20"). Виберіть End Sub
Інший спосіб зробити це за допомогою коду нижче:
Діапазон Sub SelectRange () ("A1", "D20"). Виберіть End Sub
Наведений вище код бере верхню ліву адресу комірки (A1) та нижню праву адресу комірки (D20) і вибирає весь діапазон. Ця техніка стає корисною, коли ви працюєте з діапазонами різного розміру (як ми побачимо, коли властивість End буде розглянуто далі в цьому підручнику).
Якщо ви хочете, щоб виділення відбувалося в іншій книзі або на іншому аркуші, вам потрібно сказати VBA точні назви цих об’єктів.
Наприклад, у наведеному нижче коді буде вибрано діапазон A1: D20 на аркуші Sheet2 у книзі Book2.
Sub SelectRange () Робочі книги ("Book2.xlsx"). Робочі аркуші ("Sheet1"). Активуйте діапазон ("A1: D20"). Виберіть End Sub
Що робити, якщо ви не знаєте, скільки рядків. Що робити, якщо ви хочете виділити всі клітинки, у яких є значення.
У цих випадках вам потрібно використовувати методи, наведені в наступному розділі (щодо вибору діапазону змінних розмірів).
Вибір діапазону змінних розмірів
Діапазон клітинок можна вибрати різними способами. Вибраний вами метод залежатиме від структури даних.
У цьому розділі я розгляну деякі корисні прийоми, які дійсно будуть корисними під час роботи з діапазонами у VBA.
Виберіть Використання властивості CurrentRange
У випадках, коли ви не знаєте, скільки рядків/стовпців містить дані, ви можете використовувати властивість CurrentRange об’єкта Range.
Властивість CurrentRange охоплює всі суміжні заповнені клітинки в діапазоні даних.
Нижче наведено код, який буде вибирати поточну область, що містить клітинку A1.
Sub SelectCurrentRegion () Діапазон ("A1"). CurrentRegion. Виберіть End Sub
Наведений вище метод хороший, коли у вас є всі дані у вигляді таблиці без порожніх рядків/стовпців.
Але якщо у ваших даних є порожні рядки/стовпці, вони не вибиратимуть ті, що стоять після порожніх рядків/стовпців. На зображенні нижче код CurrentRegion вибирає дані до рядка 10, оскільки рядок 11 порожній.
У таких випадках ви можете скористатися властивістю UsedRange об’єкта аркуша.
Виберіть Використання властивості UsedRange
UsedRange дозволяє посилатися на будь -які клітинки, які були змінені.
Таким чином, код нижче виділить усі використані клітинки в активному аркуші.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Зауважте, що у випадку, якщо у вас є віддалена клітинка, яка була використана, вона буде враховуватися за допомогою наведеного вище коду, і всі комірки будуть виділені до цієї використаної комірки.
Виберіть Використання кінцевої властивості
Тепер ця частина дійсно корисна.
Властивість End дозволяє вибрати останню заповнену клітинку. Це дозволяє імітувати ефект клавіші зі стрілкою вниз або вгору або клавіші управління вправо/вліво.
Спробуємо зрозуміти це на прикладі.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете швидко вибрати останні заповнені клітинки у стовпці А.
Проблема тут у тому, що дані можуть змінюватися, і ви не знаєте, скільки клітинок заповнено. Якщо вам потрібно зробити це за допомогою клавіатури, ви можете вибрати клітинку A1, а потім натиснути клавішу Control + клавіша зі стрілкою вниз, і вона вибере останню заповнену клітинку у стовпці.
Тепер давайте подивимося, як це зробити за допомогою VBA. Ця техніка стане в нагоді, коли ви хочете швидко перейти до останньої заповненої клітинки у стовпці різного розміру
Sub GoToLastFilledCell () Діапазон ("A1"). Кінець (xlDown). Виберіть End Sub
Наведений вище код перейде до останньої заповненої клітинки у стовпці А.
Аналогічно, ви можете скористатися End (xlToRight), щоб перейти до останньої заповненої комірки в рядку.
Sub GoToLastFilledCell () Діапазон ("A1"). Кінець (xlToRight). Виберіть End Sub
Що робити, якщо ви хочете вибрати весь стовпець, а не переходити до останньої заповненої клітинки.
Ви можете зробити це за допомогою коду нижче:
Sub SelectFilledCells () Діапазон ("A1", Діапазон ("A1"). Кінець (xlDown)). Виберіть End Sub
У наведеному вище коді ми використовували перше та останнє посилання на клітинку, які нам потрібно вибрати. Незалежно від того, скільки заповнених клітинок, наведений вище код виділить усі.
Пригадайте приклад вище, де ми вибрали діапазон A1: D20, використовуючи наступний рядок коду:
Діапазон ("A1", "D20")
Тут A1-верхня ліва комірка, а D20-нижня права клітинка діапазону. Ми можемо використовувати ту саму логіку при виборі діапазонів різного розміру. Але оскільки ми не знаємо точної адреси нижньої правої комірки, ми використали властивість End, щоб її отримати.
У діапазоні (“A1”, Діапазон (“A1”). Кінець (xlDown)), “A1” відноситься до першої комірки та Діапазону (“A1”). Кінець (xlDown) відноситься до останньої клітинки. Оскільки ми надали обидва посилання, метод Select виділяє всі клітинки між цими двома посиланнями.
Подібним чином можна також вибрати весь набір даних, який має кілька рядків і стовпців.
У наведеному нижче коді будуть вибрані всі заповнені рядки/стовпці, починаючи з комірки A1.
Sub SelectFilledCells () Діапазон ("A1", Діапазон ("A1"). Кінець (xlDown). Кінець (xlToRight)). Виберіть End Sub
У наведеному вище коді ми використовували Range (“A1”). End (xlDown) .End (xlToRight), щоб отримати посилання на нижню праву клітинку набору даних.
Різниця між використанням CurrentRegion та End
Якщо вам цікаво, навіщо використовувати властивість End для вибору заповненого діапазону, коли у нас є властивість CurrentRegion, дозвольте мені сказати вам різницю.
За допомогою властивості End можна вказати початкову клітинку. Наприклад, якщо у вас є дані в A1: D20, але перший рядок - це заголовки, ви можете використовувати властивість End, щоб вибрати дані без заголовків (використовуючи код нижче).
Sub SelectFilledCells () Діапазон ("A2", Діапазон ("A2"). Кінець (xlDown). Кінець (xlToRight)). Виберіть End Sub
Але CurrentRegion автоматично вибере весь набір даних, включаючи заголовки.
Досі в цьому підручнику ми бачили, як звертатися до діапазону клітинок різними способами.
Тепер давайте розглянемо деякі способи, за допомогою яких ми дійсно можемо використати ці прийоми для виконання певної роботи.
Скопіюйте комірки / діапазони за допомогою VBA
Як я вже згадував на початку цього підручника, вибір комірки не є необхідним для виконання дій над нею. У цьому розділі ви побачите, як копіювати клітинки та діапазони, навіть не вибираючи їх.
Почнемо з простого прикладу.
Копіювання одноклітинної
Якщо ви хочете скопіювати клітинку A1 і вставити її в клітинку D1, це зробить код нижче.
Додатковий діапазон CopyCell () ("A1"). Діапазон копіювання ("D1")
Зауважте, що метод копіювання об’єкта діапазону копіює комірку (так само, як Control +C) і вставляє її у вказане місце призначення.
У наведеному вище прикладі коду призначення вказано в тому ж рядку, де використовується метод Copy. Якщо ви хочете зробити свій код ще більш читабельним, скористайтеся наведеним нижче кодом:
Sub CopyCell () Діапазон ("A1"). Призначення копіювання: = Діапазон ("D1") Кінцевий підрозділ
Наведені вище коди будуть копіювати та вставляти значення, а також форматування/формули.
Як ви могли вже помітити, наведений вище код копіює комірку, не виділяючи її. Незалежно від того, де ви знаходитесь на робочому аркуші, код буде копіювати клітинку A1 і вставляти її на D1.
Також зверніть увагу, що вищевказаний код перезапише будь -який існуючий код у комірці D2. Якщо ви хочете, щоб Excel повідомляв вам, чи є щось у комірці D1, не перезаписуючи це, ви можете скористатися наведеним нижче кодом.
Sub CopyCell () If Range ("D1") "" Тоді Response = MsgBox ("Ви хочете перезаписати існуючі дані", vbYesNo) End If If Response = vbYes Тоді Range ("A1"). Copy Range ("D1 ") Закінчити, якщо кінець Sub
Копіювання діапазону фіксованого розміру
Якщо ви хочете скопіювати A1: D20 у J1: M20, ви можете скористатися наведеним нижче кодом:
Додатковий діапазон копіювання () ("A1: D20"). Діапазон копіювання ("J1")
У комірці призначення вам просто потрібно вказати адресу верхньої лівої комірки. Код автоматично копіює точний скопійований діапазон у місце призначення.
Ви можете використовувати ту саму конструкцію для копіювання даних з одного аркуша на інший.
Наведений нижче код буде копіювати A1: D20 з активного аркуша на Sheet2.
Додатковий діапазон CopyRange () ("A1: D20"). Копіювання робочих аркушів ("Аркуш2"). Діапазон ("A1") Кінцевий підрозділ
Наведене вище копіює дані з активного аркуша. Тому перед запуском коду переконайтеся, що аркуш із даними є активним аркушем. Щоб бути безпечним, ви також можете вказати назву аркуша під час копіювання даних.
Sub CopyRange () Робочі аркуші ("Sheet1"). Діапазон ("A1: D20"). Копіювати робочі аркуші ("Sheet2"). Діапазон ("A1") End Sub
Хорошим у вищенаведеному коді є те, що незалежно від того, який аркуш активний, він завжди буде копіювати дані з Sheet1 і вставляти їх у Sheet2.
Ви також можете скопіювати іменований діапазон, використовуючи його ім’я замість посилання.
Наприклад, якщо у вас є іменований діапазон під назвою "SalesData", ви можете скористатися наведеним нижче кодом, щоб скопіювати ці дані на Sheet2.
Додатковий діапазон CopyRange () ("SalesData"). Копіювати робочі аркуші ("Sheet2"). Діапазон ("A1") Кінцевий підрозділ
Якщо областю іменованого діапазону є вся книга, вам не потрібно бути на аркуші з іменованим діапазоном для запуску цього коду. Оскільки іменований діапазон є областю дії для книги, ви можете отримати доступ до нього з будь -якого аркуша за допомогою цього коду.
Якщо у вас є таблиця з назвою Table1, ви можете скористатися наведеним нижче кодом, щоб скопіювати її на Sheet2.
Додатковий діапазон CopyTable () ("Таблиця1 [#All]"). Копіювання робочих аркушів ("Аркуш2"). Діапазон ("A1") Кінцевий підрозділ
Ви також можете скопіювати діапазон до іншої книги.
У наведеному нижче прикладі я копіюю таблицю Excel (Таблиця1) у книгу Book2.
Sub CopyCurrentRegion () Діапазон ("Таблиця1 [#All]"). Копіювати робочі книги ("Book2.xlsx"). Робочі аркуші ("Аркуш1"). Діапазон ("A1") Кінець
Цей код буде працювати, лише якщо книга вже відкрита.
Копіювання діапазону змінних розмірів
Одним із способів копіювання діапазонів змінних розмірів є їх перетворення в іменовані діапазони або таблицю Excel та використання кодів, як показано у попередньому розділі.
Але якщо ви не можете цього зробити, ви можете використовувати CurrentRegion або End властивість range.
Нижче наведений код буде копіювати поточний регіон в активному аркуші та вставити його у Sheet2.
Sub CopyCurrentRegion () Діапазон ("A1"). CurrentRegion.Copy Робочі аркуші ("Sheet2"). Діапазон ("A1") End Sub
Якщо ви хочете скопіювати перший стовпець вашого набору даних до останньої заповненої клітинки та вставити його у Sheet2, ви можете скористатися наведеним нижче кодом:
Sub CopyCurrentRegion () Діапазон ("A1", Діапазон ("A1"). Кінець (xlDown)). Копіювати робочі аркуші ("Аркуш2"). Діапазон ("A1") Кінець
Якщо ви хочете скопіювати рядки, а також стовпці, ви можете скористатися наведеним нижче кодом:
Sub CopyCurrentRegion () Діапазон ("A1", Діапазон ("A1"). Кінець (xlDown). Кінець (xlToRight)). Копіювання робочих аркушів ("Аркуш2"). Діапазон ("A1") Кінцевий підрозділ
Зауважте, що всі ці коди не виділяють клітинки під час виконання. Загалом, ви знайдете лише кілька випадків, коли вам дійсно потрібно вибрати клітинку/діапазон, перш ніж працювати над нею.
Призначення діапазонів об’єктним змінним
До цього часу ми використовували повну адресу клітинок (наприклад, Робочі зошити (“Book2.xlsx”). Робочі аркуші (“Аркуш1”). Діапазон (“A1”)).
Щоб зробити ваш код більш керованим, ви можете призначити ці діапазони для об’єктних змінних, а потім використовувати ці змінні.
Наприклад, у наведеному нижче коді я призначив вихідний і цільовий діапазони для об’єктних змінних, а потім використовував ці змінні для копіювання даних з одного діапазону в інший.
Sub CopyRange () Затемнення SourceRange як діапазон Dim DestinationRange як діапазон Набір SourceRange = Робочі аркуші ("Sheet1"). Діапазон ("A1: D20") Набір DestinationRange = Робочі аркуші ("Sheet2"). Діапазон ("A1") SourceRange.Copy DestinationRange End Sub
Почнемо з оголошення змінних як об’єктів Range. Потім ми призначаємо діапазон цим змінним за допомогою оператора Set. Після того, як діапазон був призначений змінній, ви можете просто використовувати її.
Введіть дані в наступну порожню клітинку (за допомогою поля введення)
Ви можете використовувати поля введення, щоб дозволити користувачеві вводити дані.
Наприклад, припустимо, що у вас є набір даних нижче, і ви хочете ввести запис продажів, ви можете використовувати поле введення у VBA. Використовуючи код, ми можемо переконатися, що він заповнює дані в наступному порожньому рядку.
Sub EnterData () Dim RefRange As Range Set RefRange = Діапазон ("A1"). Кінець (xlDown) .Offset (1, 0) Встановити ProductCategory = RefRange.Offset (0, 1) Встановити кількість = RefRange.Offset (0, 2 ) Встановити суму = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Категорія товару") Quantity.Value = InputBox ("Кількість") Amount.Value = InputBox ("Amount") End Sub
Наведений вище код використовує поле введення VBA для отримання вхідних даних від користувача, а потім вводить вхідні дані у зазначені клітинки.
Зауважте, що ми не використовували точні посилання на клітинки. Натомість ми використовували властивість End та Offset, щоб знайти останню порожню клітинку та заповнити в ній дані.
Цей код далекий від використання. Наприклад, якщо ви введете текстовий рядок, коли поле для введення запитує кількість або кількість, ви помітите, що Excel це дозволяє. Ви можете скористатися умовою If, щоб перевірити, чи є числовим значенням, а потім дозволити його відповідно.
Цикл через клітинки / діапазони
Поки що ми могли побачити, як вибирати, копіювати та вводити дані у клітинки та діапазони.
У цьому розділі ми побачимо, як прокрутити набір комірок/рядків/стовпців у діапазоні. Це може бути корисним, якщо ви хочете проаналізувати кожну клітинку та виконати на її основі певну дію.
Наприклад, якщо ви хочете виділити кожен третій рядок у виділенні, вам потрібно прокрутити цикл і перевірити номер рядка. Аналогічно, якщо ви хочете виділити всі негативні клітинки, змінивши колір шрифту на червоний, вам потрібно прокрутити і проаналізувати значення кожної клітинки.
Ось код, який прокручуватиме рядки у вибраних осередках та виділятиме альтернативні рядки.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Вибір для кожного Myrow у Myrange.Rows If Myrow.Row Mod 2 = 0 Тоді Myrow.Interior.Color = vbCyan End Якщо наступний Myrow End Sub
Наведений вище код використовує функцію MOD для перевірки номера рядка у виділенні. Якщо номер рядка парний, він виділяється блакитним кольором.
Ось ще один приклад, коли код проходить через кожну клітинку та виділяє клітинки з негативним значенням.
Sub HighlightAlternateRows () Dim Myrange як діапазон Dim Mycell як діапазон Set Myrange = Вибір для кожної Mycell у Myrange Якщо Mycell <0 То Mycell.Interior.Color = vbRed End Якщо наступний Mycell End Sub
Зауважте, що ви можете зробити те ж саме за допомогою умовного форматування (що є динамічним і кращим способом це зробити). Цей приклад призначений лише для того, щоб показати вам, як цикл працює з клітинками та діапазонами у VBA.
Де розмістити код VBA
Цікаво, де код VBA міститься у вашій книзі Excel?
Excel має бекенд VBA, який називається редактором VBA. Вам потрібно скопіювати та вставити код у вікно коду модуля VB Editor.
Нижче наведено кроки для цього.
- Перейдіть на вкладку Розробник.
- Натисніть на опцію Visual Basic. Це відкриє редактор VB у бекенді.
- На панелі «Провідник проектів» у редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно вставити код.Якщо ви не бачите Провідник проектів, перейдіть на вкладку Вигляд і натисніть Провідник проектів.
- Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до вашої книги.
- Скопіюйте та вставте код у вікно модуля.
