Vba для преобразования диапазона в массив

Обновлено: 21.11.2024

Автоматизация стала проще и продуктивнее!

VBA считывает значения из диапазона в массив

VBA считывает значения из диапазона в массив в Excel. Мы можем читать значения из диапазона, ячейки или таблицы в массивы. Используя Range, мы можем считывать несколько значений из одного столбца или из нескольких столбцов или строк. Ячейка может содержать либо одно значение, либо несколько значений. Таблица также может состоять из нескольких значений в нескольких столбцах или строках. Давайте посмотрим, как читать значения из диапазона различными способами в следующем руководстве.

Макрокод для чтения значений из диапазона (один столбец) в массив

Вот пример кода макроса для чтения значений из диапазона (один столбец) в массив. В этом коде мы использовали объект диапазона для определения диапазона, UBound используется для установки верхней границы массива, а массив используется для хранения прочитанных значений из диапазона. Это пример одномерного массива.

Вы можете вывести в ближайшее окно. Вот скриншот вывода.

Код VBA для чтения значений из диапазона (несколько столбцов) в массив

Давайте рассмотрим пример кода макроса для чтения значений из диапазона (несколько столбцов) в массив. Это пример многомерного массива.

Вы можете вывести в ближайшее окно. Вот скриншот вывода.

Макрокод для чтения значений из диапазона (одной ячейки) в массив

Вот пример кода макроса для чтения значений из диапазона (одной ячейки) в массив.

Вы можете вывести в ближайшее окно. Вот скриншот вывода.

Инструкции по использованию макроса

Вот инструкции по использованию вышеуказанной процедуры макроса в редакторе Visual Basic.

Откройте редактор Visual Basic (VBE), нажав Alt +F11.
Перейдите в окно кода, нажав F7.
Копировать указанный выше макрос или процедуру
Вставьте скопированный код в окно кода.
Запустите макрос, нажав F5 или команду "Выполнить".
Вывод можно увидеть на экране.
Найти приведенный выше выходной скриншот указанной процедуры.

Вы можете вывести в ближайшее окно. Вот скриншот вывода.

Статьи по теме VBA Array

Вам также могут понравиться связанные статьи о массивах VBA.

Оставить ответ Отменить ответ

Вы должны войти в систему, чтобы оставить комментарий.

Вопросы и ответы на собеседовании по Excel VBA

Функции VBA по категориям

Шаблоны управления проектами

Пакет профессиональных шаблонов PM

Учебное пособие по VBA

Последние публикации

Программирование VBA

Категории

Создавайте профессиональные инструменты и приложения автоматизации для своих проектов с помощью наших готовых к использованию кодов и приложений VBA.

Узнайте, как легко назначить диапазон массиву с помощью Excel VBA. Я также покажу вам, как избежать распространенных ошибок, таких как несоответствие типа ошибки 13 во время выполнения.

Пример. Назначение диапазона массиву

Создавайте мощные макросы с помощью нашего бесплатного комплекта разработчика VBA

Такой макрос легко скопировать и вставить, но создать его самостоятельно сложнее. Чтобы помочь вам создавать макросы, подобные этому, мы создали бесплатный комплект разработчика VBA и написали Большую книгу макросов Excel VBA, полную сотен готовых макросов, которые помогут вам освоить файловый ввод-вывод, массивы, строки и многое другое. скопируйте ниже.

Учебное пособие. Назначение диапазона массиву

Назначение диапазона массиву — отличный способ ускорить макрос. Работа с массивами обычно намного быстрее, чем управление отдельными ячейками электронной таблицы. Если вы хотите по-настоящему научиться работать с массивами, вам понадобится копия нашей подробной памятки VBA по массивам с более чем 20 готовыми макросами и десятками советов, призванных облегчить вам работу с массивами.

Подготовьте сцену

Хорошо, давайте углубимся. Предположим, у вас есть таблица данных в Excel, показывающая все будущие солнечные затмения в мире на следующие несколько лет, например:

Вы хотите сохранить значения в массиве, чтобы иметь возможность быстро манипулировать данными.

Как только вы узнаете, насколько велик ваш диапазон, просто вставьте его в пример макроса. Важно, чтобы ваш массив был объявлен как тип данных Variant.

Чтобы доказать, что значения присваиваются массиву, я добавил Watch к переменной MyArray. Взгляните на ответ в окне просмотра:

Вы можете видеть, что массив превратился из нераспределенного в выделенный. Теперь он имеет 311 строк и 7 столбцов в ширину, как и наш первоначальный диапазон.

Распространенные ошибки

Скорее всего, вы попали на эту страницу, потому что попробовали что-то подобное и получили сообщение об ошибке. Я расскажу вам о некоторых из этих распространенных ошибок и о том, как их избежать.

Если вы не поместите расширение .Value2 в конце вашего диапазона, вы могли получить сообщение об ошибке Ошибка времени выполнения «13»: несоответствие типов.

Если у вас возникла ошибка, это связано с тем, что перед диапазоном указано имя рабочего листа или имя рабочей книги. Взгляните на этот пример:

Приведенный выше макрос вызовет ошибку, так как перед диапазоном стоит ActiveSheet. Однако следующий очень похожий макрос не вызовет ошибки:

Ошибки нет, поскольку перед диапазоном нет имени листа или названия книги.

Не беспокойтесь — приведенная выше демонстрация не означает, что вы не можете ссылаться на диапазон на другом листе или в другой книге!

Все, что вам нужно сделать, это поместить .Value2 в конец вашего диапазона, и вы можете ссылаться на любой лист, который вы хотите, перед своим диапазоном. Взгляните:

Почему .Value2, спросите вы? Я объясню в следующем разделе.

Дополнительные советы

Значение, значение2 и текст

Возможно, вы уже знаете, что добавление .Value2 в конец вашего диапазона — не единственный способ получить содержимое вашей ячейки… но это лучший способ!

Поясню. Существует 3 распространенных способа извлечения текста из ячейки:

Свойство Range.Text
Свойство Range.Value
Свойство Range.Value2

Свойство .Value возвращает значение ячейки в любом формате, в котором оно было в Excel, и преобразует его в эквивалентный тип данных VBA. Обычно это хороший способ извлечения данных, если ваши данные не отформатированы как валюта или дата. Передача в виде даты может быть полезной, но валюта может вызвать некоторые проблемы. Тип данных валюты VBA содержит только 4 десятичных знака, поэтому значение вашей ячейки может быть усечено при преобразовании в VBA.

Свойство .Value2 похоже на свойство .Value с одним заметным отличием. Он не проверяет формат ваших ячеек, поэтому не пытается сохранить ваши элементы в виде дат или валют. Это гарантирует, что ваши значения извлекаются точно так, как они должны быть, без усечения.

На снимке экрана Watch Window, который я представил ранее, вы можете увидеть, что дата в MyArray(1,1) хранится как значение даты серийного номера, которое использует Excel, а не в формате даты. Это потому, что я использовал Value2 вместо Value!

Value2 также является самым быстрым способом извлечения данных из ячейки, так что это двойная победа!

Идеи для приложений

Использование Excel VBA для присвоения диапазона массиву является чрезвычайно ценным навыком. Оказавшись в массиве, вы можете работать с данными намного быстрее, чем если бы вы манипулировали ими в исходном формате диапазона.

Я уверен, что вы все нашли отличное применение для хранения диапазонов в массивах. Я хотел бы услышать о них! Оставьте мне комментарий и дайте мне знать, какие изящные вещи вы сделали.

На этом уроке все. Когда вы будете готовы вывести свой VBA на новый уровень, подпишитесь, заполнив форму ниже.

Готовы делать больше с VBA?
Мы собрали гигантский PDF-файл с более чем 300 готовыми макросами и хотим, чтобы вы получили его бесплатно. Введите свой адрес электронной почты ниже, и мы вышлем вам копию вместе с нашим комплектом разработчика VBA, полным советов, приемов и ярлыков VBA.

Прежде чем мы начнем, я хочу сообщить вам, что мы разработали набор шпаргалок по VBA, чтобы вам было проще писать лучшие макросы. Мы включили более 200 советов и 140 примеров макросов, поэтому в них есть все, что вам нужно знать, чтобы стать лучшим программистом VBA.

В этой статье я покажу вам, как преобразовать диапазон в массив в VBA в Excel.

Преобразование диапазона в массив в Excel VBA (быстрый просмотр)

Загрузить практическую рабочую тетрадь

Загрузите это учебное пособие, чтобы тренироваться, пока читаете эту статью.

3 способа преобразования диапазона в массив в Excel VBA

Здесь у нас есть набор данных с именами некоторых учеников и их оценками по физике, химии и математике в школе под названием "Подсолнечный детский сад".

Наша сегодняшняя цель — преобразовать диапазон этого набора данных в массив в Excel VBA.

1. Преобразование диапазона в двумерный массив с помощью объекта диапазона Excel VBA

Сначала мы преобразуем диапазон в двумерный массив.

Здесь мы преобразуем диапазон B4:E13 в массив размеров 10, 4 (строка 10, столбец 4).

Сначала объявите имя массива с типом данных Variant.

Здесь я объявил его как Myarray.

Затем назначьте ему нужный диапазон (в данном примере B4:E13) с помощью свойства Range языка VBA.

Поэтому полный код VBA будет таким:

⧭ Код VBA:

Примечание. Этот код создает макрос с именем Convert_Range_to_Two_Dimensional_Array.

⧭ Вывод:

Код преобразует диапазон B4:E13 вашего активного рабочего листа в массив из строки 10 и столбца 2.

Теперь, если вы хотите увидеть какое-либо конкретное значение массива, просто поместите одну строку кода в конце, указав индексы значения в окне сообщения.

Например, чтобы увидеть оценки по химии 5-го ученика поставьте:

Затем запустите код. Вы увидите окно сообщения, отображающее 57 – оценки пятого ученика по химии.

⧭ Дополнительные уроки:

➤ Вместо фиксирования диапазона в коде вы можете использовать поле ввода, чтобы просить пользователя вводить диапазон каждый раз, когда он/она запускает макрос.

Это сделает код более гибким.

Вместо строки:

Rng = InputBox("Введите диапазон для преобразования в массив: ")

Myarray = Диапазон(Rng)

Если вы запустите этот код, он сначала запросит диапазон.

Введите желаемый диапазон и нажмите OK.

Здесь я ввел B4:E13.

Он преобразует диапазон в массив.

➤ Кроме того, этот код работает только для диапазона активного рабочего листа.

Вы можете исправить имя рабочего листа перед объектом Range, чтобы придать ему немного большей жесткости.

Например, чтобы преобразовать диапазон B4:E13 рабочего листа с именем Sheet1 в массив, вы можете использовать:

Он преобразует диапазон B4:E13 листа Sheet1 в массив независимо от того, какой рабочий лист активен.

2. Перенос диапазона в одномерный массив с помощью свойства Transpose Excel VBA

Свойство Range языка VBA по умолчанию превращает диапазон в двумерный массив.

Но иногда вам может понадобиться преобразовать один столбец или одну строку в одномерный массив.

Чтобы преобразовать диапазон в одномерный массив, необходимо использовать свойство Transpose языка VBA.

⧪ Преобразование одного столбца:

Для преобразования одного столбца необходимо поместить столбец в свойство Transpose VBA только один раз.

Например, чтобы преобразовать массив B4:B13 (имена учащихся) в одномерный массив, используйте:

Dim Myarray As Variant

Myarray = Application.Transpose(Range("B4:B13"))

Итак, полный код VBA будет таким:

⧭ Код VBA:

Примечание. Этот код создает макрос с именем Convert_Single_Column_to_One_Dimensional_Array.

⧭ Вывод:

Чтобы увидеть какое-либо конкретное значение массива, поместите строку в конце с указанием окна сообщения, содержащего индекс значения.

Например, чтобы увидеть имя 5-го ученика, введите:

Сейчас. запустите код. И вы получите имя пятого ученика, Шейн Остин.

⧪ Преобразование одной строки:

Чтобы преобразовать одну строку в массив, необходимо дважды использовать свойство Transpose языка VBA.

Например, чтобы преобразовать строку B4:E4 в массив, используйте:

Dim Myarray As Variant

Myarray = Application.Transpose(Application.Transpose(Range("B4:E4")))

Итак, полный код VBA будет таким:

⧭ Код VBA:

Примечание. Этот код создает макрос с именем Convert_Single_Row_to_One_Dimensional_Array.

⧭ Вывод:

Например, чтобы увидеть второе значение массива, используйте:

Затем запустите код. И он отобразит 82, второе значение массива.

⧭ Дополнительные уроки:

Очевидно, что вы можете использовать поле ввода и имя рабочего листа в коде, чтобы сделать код более гибким. Подробнее см. в разделе 1.

3. Преобразование диапазона в массив путем повторения цикла For (применимо как к одномерным, так и к двумерным массивам)

Вы также можете преобразовать диапазон в массив с помощью цикла For-Loop в VBA. Этот метод применим как для одномерных, так и для двумерных массивов.

⧪ Одномерный диапазон в одномерный массив:

Вы можете использовать следующий код VBA для преобразования одной строки или столбца в одномерный столбец с помощью цикла For.

⧭ Код VBA:

⧭ Примечания:

Этот код создает макрос с именем Convert_Range_to_One_Dimensional_Array_by_For_Loop.
Сначала объявляется массив Myarray с типом данных Variant.
Затем размер массива устанавливается равным количеству строк в диапазоне B4:B13. Если вам нужно преобразовать любой другой диапазон, введите его здесь. Или вы можете использовать поле ввода, чтобы получать диапазон от пользователя каждый раз, когда он/она запускает его (подробности в разделе 1).
Этот код преобразует один столбец в массив. Если вы хотите преобразовать одну строку, вместо Rows.Count в 3-й строке используйте Columns.Count.
Далее идет цикл For. Он перебирает каждое значение диапазона B4:B13 и присваивает его массиву Myarray. Очевидно, вам нужно ввести свой диапазон вместо B4:B13 здесь.

⧭ Вывод:

Чтобы отобразить какое-либо конкретное значение массива, поместите строку с MsgBox в конец. Например, чтобы получить доступ к 5-му значению массива, введите:

Теперь запустите код. Он покажет 5-й элемент массива, Шейн Остин.

⧪ Двумерный диапазон в двумерный массив:

⧭ Код VBA:

⧭ Примечания:

Этот код создает макрос с именем Convert_Range_to_Two_Dimensional_Array_by_For_Loop.
Сначала объявляется массив Myarray с типом данных Variant.
Затем размеры массива устанавливаются равными количеству строк и столбцов в диапазоне B4:E13. Если вам нужно преобразовать любой другой диапазон, введите его здесь. Или вы можете использовать поле ввода, чтобы получать диапазон от пользователя каждый раз, когда он/она запускает его (подробности в разделе 1).
Далее идет цикл For. Он перебирает каждое значение диапазона B4:E13 и присваивает его массиву Myarray. Очевидно, вам нужно ввести свой диапазон вместо B4:E13 здесь.

⧭ Вывод:

Чтобы отобразить какое-либо конкретное значение массива, поместите строку с MsgBox в конец. Например, чтобы получить доступ к значению 5-й строки и 3-го столбца, введите:

Теперь запустите код. Он покажет значение из 5-й строки и 3-го столбца, 57.

⧪ Двумерный диапазон в одномерный массив:

Это самая полезная функция цикла For-Loop.

Вы можете создать одномерный массив из двумерного диапазона с помощью цикла For-Loop, чего не могут сделать первые два метода.

Давайте создадим одномерный массив из диапазона B4:E13. Вы можете использовать следующий код VBA:

⧭ Код VBA:

⧭ Примечания:

Этот код создает макрос с именем Convert_Two_Dimensional_Range_to_One_Dimensional_Array_by_For_Loop.
Сначала объявляется массив Myarray с типом данных Variant.
Затем размеры массива устанавливаются равными общему количеству значений диапазона B4:E13 (количество строк * количество столбцов). Очевидно, вы вводите здесь свой диапазон вместо B4:E13. Или используйте поле ввода.
Далее идет цикл For. Он перебирает каждое значение диапазона B4:E13 и присваивает его массиву Myarray. Больше не нужно говорить, введите здесь свой диапазон вместо B4:E13.

⧭ Вывод:

Чтобы отобразить какое-либо конкретное значение массива, поместите строку с MsgBox в конец. Например, чтобы получить доступ к 15-му значению массива:

Теперь запустите код. Он покажет 15-е значение массива, 72 (считая построчно).

Заключение

С помощью этих методов вы можете преобразовать диапазон в массив в VBA в Excel. У вас есть вопросы? Не стесняйтесь спрашивать нас.

Версию этой статьи для Microsoft Excel 2000 см. в 213798.

Версию этой статьи для Microsoft Excel 98 и более ранних версий Excel см. в разделе 149689.

Обзор

Эта статья содержит примеры процедур Microsoft Visual Basic для приложений, которые можно использовать для работы с несколькими типами массивов.

Дополнительная информация

Microsoft предоставляет примеры программирования только для иллюстрации, без явных или подразумеваемых гарантий. Это включает, но не ограничивается, подразумеваемые гарантии товарного состояния или пригодности для конкретной цели. В этой статье предполагается, что вы знакомы с демонстрируемым языком программирования и инструментами, которые используются для создания и отладки процедур. Инженеры службы поддержки Майкрософт могут помочь объяснить функциональность конкретной процедуры, но они не будут изменять эти примеры, чтобы обеспечить дополнительную функциональность или создавать процедуры в соответствии с вашими конкретными требованиями. ПРИМЕЧАНИЕ. В процедурах Visual Basic для приложений слова после апострофа (') являются комментариями.

Чтобы заполнить массив, а затем скопировать его на рабочий лист

Откройте новую книгу и вставьте лист модуля Visual Basic.

Введите следующий код на странице модуля.

В меню "Инструменты" выберите "Макрос" и нажмите "Макросы".

В диалоговом окне "Макрос" выберите Sheet_Fill_Array, а затем нажмите "Выполнить".

Чтобы взять значения из рабочего листа и заполнить массив

Введите значения на Листе 1 в ячейки A1:A10.

На листе модуля Visual Basic введите следующий код:

В меню "Инструменты" выберите "Макрос" и нажмите "Макросы".

В диалоговом окне "Макрос" нажмите from_sheet_make_array, а затем нажмите "Выполнить".

Передача и получение массива

На листе модуля введите следующий код:

Выберите Лист1 и выделите диапазон A1:A10.

В меню "Инструменты" выберите "Макрос" и нажмите "Макросы".

В диалоговом окне "Макрос" нажмите pass_array, а затем нажмите "Выполнить".

Сравнить два массива

Создайте два именованных диапазона на Листе1. Назовите один диапазон1 и другой диапазон2.

Например, выделите диапазон ячеек A1:A10 и назовите его range1; выделите диапазон ячеек B1:B10 и назовите его range2.

Введите следующий код на странице модуля.

В меню "Инструменты" выберите "Макрос" и нажмите "Макрос".

В диалоговом окне «Макрос» нажмите «compare_two_array», а затем нажмите «Выполнить».

Вы увидите одно окно сообщения для каждого сравнения.

Заполнение динамического массива

На листе модуля введите следующий код:

В меню "Инструменты" выберите "Макрос" и нажмите "Макросы".

В диалоговом окне "Макрос" нажмите fill_array, а затем нажмите "Выполнить".

ПРИМЕЧАНИЕ. Изменение переменной "number_of_elements" определит размер массива.

Читайте также: