Диапазоны и ячейки в VBA
Таблицы Excel хранят данные в ячейках. Ячейки организованы в строки и столбцы. Каждую ячейку можно определить по точке пересечения строки и столбца (например, B3 или R3C2).
Диапазон Excel относится к одной или нескольким ячейкам (например, A3: B4)
Адрес ячейки
Обозначение A1
В нотации A1 ячейка обозначается буквой столбца (от A до XFD), за которой следует номер строки (от 1 до 1 048 576).
В VBA вы можете ссылаться на любую ячейку, используя Объект диапазона.
| 123456789 | 'Обратитесь к ячейке B4 на текущем активном листеДиапазон MsgBox ("B4")'См. Ячейку B4 на листе "Данные".Листы MsgBox ("Данные"). Диапазон ("B4")'Обратитесь к ячейке B4 на листе "Данные" в другой ОТКРЫТОЙ книге.'под названием' Мои данные 'Книги MsgBox («Мои данные»). Таблицы («Данные»). Диапазон («B4») |
Обозначение R1C1
В нотации R1C1 ячейка обозначается буквой R, за которой следует номер строки, затем буква «C», за которой следует номер столбца. например, B4 в нотации R1C1 будет обозначаться как R4C2. В VBA вы используете Объект Cells использовать нотацию R1C1:
| 12 | 'См. Ячейку R [6] C [4], т.е. D6Ячейки (6, 4) = "D6" |
Диапазон ячеек
Обозначение A1
Чтобы обратиться к более чем одной ячейке, используйте «:» между адресом начальной ячейки и адресом последней ячейки. Следующее будет относиться ко всем ячейкам от A1 до D10:
| 1 | Диапазон ("A1: D10") |
Обозначение R1C1
Чтобы ссылаться на более чем одну ячейку, используйте знак «,» между адресом начальной ячейки и адресом последней ячейки. Следующее будет относиться ко всем ячейкам от A1 до 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" |
Смещение от ячейки или диапазона
Используя функцию смещения, вы можете переместить ссылку из заданного диапазона (ячейки или группы ячеек) на указанное число_строчек и число_столбцов.
Синтаксис смещения
Диапазон.Смещение (число_строчек, число_столбцов)
Смещение от ячейки
| 12345678910111213141516 | 'СМЕЩЕНИЕ из ячейки 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 | 'Объявить переменную RangeDim myRange as Range'Установите переменную в диапазон A1: D4Установить myRange = Range ("A1: D4")'Печатает $ A $ 1: $ D $ 4MsgBox myRange.Address |
Изменить размер диапазона
Метод Resize объекта Range изменяет размер ссылочного диапазона:
| 1234567 | Dim myRange As RangeДиапазон изменения размераУстановить myRange = Range ("A1: F4")'Печатает $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Address |
Левая верхняя ячейка диапазона измененного размера такая же, как левая верхняя ячейка исходного диапазона
Изменить размер синтаксиса
Range.Resize (число_строчек, число_столбцов)
СМЕЩЕНИЕ против изменения размера
Смещение не изменяет размеры диапазона, а перемещает его на указанное количество строк и столбцов. Изменение размера не изменяет положение исходного диапазона, но изменяет размеры на указанное количество строк и столбцов.
Все ячейки на листе
Объект Cells относится ко всем ячейкам на листе (1048576 строк и 16384 столбца).
| 12 | 'Очистить все ячейки на листахCells.Clear |
UsedRange
Свойство UsedRange дает прямоугольный диапазон от используемой верхней левой ячейки до правой нижней используемой ячейки активного листа.
| 1234567 | Dim ws как рабочий листУстановить ws = ActiveSheet'$ B $ 2: $ L $ 14, если L2 - первая ячейка с любым значением'и L14 - последняя ячейка с любым значением на'активный листDebug.Print ws.UsedRange.Address |
CurrentRegion
CurrentRegion предоставляет непрерывный прямоугольный диапазон от верхней левой ячейки до правой нижней используемой ячейки, содержащей указанную ячейку / диапазон.
| 1234567891011 | Dim 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 | Dim myRange As RangeУстановить myRange = Range ("A1: F10")'Печатает $ A $ 1: $ F $ 10Debug.Print myRange.AddressУстановить myRange = Range ("F10")'Отпечатывает 10 для строки 10Debug.Print myRange.Row'Отпечатки 6 для столбца FDebug.Print myRange.ColumnУстановить myRange = Range ("E1: F5")'Печатает 5 для количества строк в диапазонеDebug.Print myRange.Rows.Count'Печатает 2 для количества столбцов в диапазонеDebug.Print myRange.Columns.Count |
Последняя ячейка в листе
Вы можете использовать Rows.Count а также Columns.Count свойства с Клетки объект, чтобы получить последнюю ячейку на листе:
| 1234567891011 | 'Вывести номер последней строкиПечать 1048576Debug.Print «Строки на листе:» & Rows.Count'Вывести номер последнего столбцаОтпечатки 16384Debug.Print «Столбцы на листе:» & Columns.Count'Вывести адрес последней ячейкиПечать $ XFD $ 1048576Debug.Print «Адрес последней ячейки на листе:» & Ячейки (Rows.Count, Columns.Count) |
Последний использованный номер строки в столбце
END переместит вас к последней ячейке в диапазоне, а End (xlUp) перенесет вас к первой использованной ячейке из этой ячейки.
| 123 | Dim lastRow As LonglastRow = Ячейки (Rows.Count, «A»). Конец (xlUp) .Row |
Последний использованный номер столбца в строке
| 123 | Dim lastCol как долгоlastCol = Ячейки (1, Columns.Count) .End (xlToLeft) .Column |
END переместит вас к последней ячейке в диапазоне, а End (xlToLeft) переместит вас влево к первой использованной ячейке из этой ячейки.
Вы также можете использовать свойства xlDown и xlToRight для перехода к первой нижней или правой использованной ячейке текущей ячейки.
Свойства ячейки
Общие свойства
Вот код для отображения часто используемых свойств ячейки
| 12345678910111213141516171819202122 | Тусклая ячейка как диапазонУстановить ячейку = Диапазон ("A1")cell.ActivateОтладка. Печать ячейки. Адрес'Распечатать $ A $ 1Debug.Print cell.Value'Отпечатки 456' АдресОтладка.Печатать ячейку.Формула'Печать = СУММ (C2: C3)КомментарийDebug.Print cell.Comment.Text' СтильDebug.Print cell.Style'Формат ячейкиDebug.Print cell.DisplayFormat.NumberFormat |
Шрифт ячейки
Объект Cell.Font содержит свойства шрифта Cell:
| 1234567891011121314151617181920 | Тусклая ячейка как диапазонУстановить ячейку = Диапазон ("A1")'Обычный, курсив, полужирный и полужирный курсивcell.Font.FontStyle = "Полужирный курсив"' Такой же какcell.Font.Bold = Truecell.Font.Italic = True'Установить шрифт Couriercell.Font.FontStyle = "Курьер"'Установить цвет шрифтаcell.Font.Color = vbBlue' илиcell.Font.Color = RGB (255, 0, 0)'Установить размер шрифтаcell.Font.Size = 20 |
Скопировать и вставить
Вставить все
Диапазоны / ячейки можно копировать и вставлять из одного места в другое. Следующий код копирует все свойства исходного диапазона в целевой диапазон (эквивалент CTRL-C и CTRL-V).
| 1234567 | 'Простая копияДиапазон ("A1: D20"). КопироватьРабочие листы («Лист2»). Диапазон («B10»). Вставить'или'Копировать с текущего листа на лист с именем' Sheet2 'Диапазон ("A1: D20"). Место назначения копирования: = Рабочие листы ("Лист2"). Диапазон ("B10") |
Специальная вставка
Выбранные свойства исходного диапазона можно скопировать в место назначения с помощью параметра PASTESPECIAL:
| 123 | 'Вставить диапазон только как значенияДиапазон ("A1: D20"). КопироватьЛисты ("Sheet2"). Диапазон ("B10"). PasteSpecial Paste: = xlPasteValues |
Вот возможные варианты для опции Вставить:
| 12345678910111213 | 'Вставить специальные типыxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
AutoFit Содержание
Размер строк и столбцов можно изменить, чтобы они соответствовали содержимому, используя приведенный ниже код:
| 12345 | 'Измените размер строк с 1 по 5, чтобы они соответствовали содержимомуРяды ("1: 5"). Автоподбор.'Измените размер столбцов A на B, чтобы они соответствовали содержимомуСтолбцы ("A: B"). Автоподбор |
Больше примеров диапазонов
Рекомендуется использовать Macro Recorder при выполнении необходимого действия через графический интерфейс. Это поможет вам понять различные доступные варианты и способы их использования.
Для каждого
Проще перебрать диапазон, используя Для каждого построить, как показано ниже:
| 123 | Для каждой ячейки в диапазоне ("A1: B100")'Сделай что-нибудь с камеройСледующая ячейка |
На каждой итерации цикла одна ячейка в диапазоне назначается переменной c, и операторы цикла For выполняются для этой ячейки. Цикл завершается, когда все ячейки обработаны.
Сортировать
Сортировка - это метод объекта Range. Вы можете отсортировать диапазон, указав параметры сортировки в Range.Sort. Приведенный ниже код отсортирует столбцы A: C на основе ключа в ячейке C2. Порядок сортировки может быть xlAscending или xlDescending. Заголовок: = xlYes следует использовать, если первая строка является строкой заголовка.
| 12 | Столбцы ("A: C"). Ключ сортировки1: = Диапазон ("C2"), _order1: = xlAscending, Header: = xlYes |
Находить
Найти также является методом 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)Если не с, то ничтоfirstaddress = c.AddressДелатьc.Value = "Пройдено"Установите c = .FindNext (c)Цикл, пока не c - ничто и c. Первый адресКонец, еслиКонец с |
Важно отметить, что для использования 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"). Address (ReferenceStyle: = xlR1C1) |
Это полезно, когда вы имеете дело с диапазонами, хранящимися в переменных, и хотите обрабатывать только определенные адреса.
Диапазон в массив
Быстрее и проще передать диапазон в массив, а затем обработать значения. Вы должны объявить массив как Variant, чтобы избежать вычисления размера, необходимого для заполнения диапазона в массиве. Размеры массива устанавливаются так, чтобы соответствовать количеству значений в диапазоне.
| 123456789 | Dim DirArray как вариант'Сохраните значения в диапазоне в массивеDirArray = Range ("a1: a5"). Значение'Цикл для обработки значенийДля каждого c в DirArrayОтладка.Печать cСледующий |
Массив в диапазон
После обработки вы можете записать массив обратно в диапазон. Чтобы записать массив в приведенном выше примере в диапазон, вы должны указать диапазон, размер которого соответствует количеству элементов в массиве.
Используйте приведенный ниже код, чтобы записать массив в диапазон D1: D5:
| 123 | Диапазон ("D1: D5"). Значение = DirArrayДиапазон ("D1: H1"). Значение = Application.Transpose (DirArray) |
Обратите внимание, что вы должны транспонировать массив, если вы записываете его в строку.
Сумма Диапазон
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Range ("A1: A10"))Debug.Print SumOfRange |
Вы можете использовать многие функции, доступные в Excel, в коде VBA, указав Application.WorkSheetFunction. перед именем функции, как в примере выше.
Диапазон счета
| 1234567 | 'Подсчитать количество ячеек с числами в диапазонеCountOfCells = Application.WorksheetFunction.Count (Диапазон ("A1: A10"))Debug.Print CountOfCells'Подсчитать количество непустых ячеек в диапазонеCountOfNonBlankCells = Application.WorksheetFunction.CountA (Диапазон ("A1: A10"))Debug.Print CountOfNonBlankCells |
Автор сценария: Винамра Чандра
