Всем привет, меня зовут Андрей, и это снова я.
В данной статье я хочу рассказать о том, как можно достаточно быстро создавать массивы внутри макросов эксель, как создавать эти самые массивы или из тех цифр, что находятся на листе эксель, или из случайных чисел, как переносить массивы из макросов (из редактора VBA) в ячейки эксель.
Допустим, что у нас есть несколько цифр на листе эксель:
Задача: превратить эти цифры в двумерный массив, элементы которого соответствуют тем цифрам, что находятся на этом листе.
Но вначале с помощью InputBox введем количество строк и столбцов в нашем будущем массиве:
'Начало фрагмента № 1 макроса
'Вначале вводим исходные данные
a1 = 1 * InputBox("Введите высоту")
a2 = 1 * InputBox("Введите ширину")
'Конец фрагмента № 1 макроса
А теперь переходим к превращению цифр из листа эксель в элементы массива, рассмотрим и «длинный» путь, и более удобный, более короткий, а при этом результат будет таким же:
- Первый путь – более «длинный»:
'начало фрагмента № 2 макроса
'создаем массив с исходными данными
Dim iskh()
ReDim iskh(a1, a2)
For x1 = 1 To a1
For x2 = 1 To a2
iskh(x1, x2) = Cells(x1, x2)
Next x2
Next x1
'конец фрагмента № 2 макроса
- Есть и второй путь – более короткий, более простой и более быстрый:
'начало фрагмента № 3 макроса
Dim iskh As Variant
iskh = Range(Cells(1, 1), Cells(a1, a2)).Value
'конец фрагмента № 3 макроса
В данном примере Cells (1, 1) – это то же самое, что и ячейка A1, потому что ячейка A1 - это та ячейка, что находится в строке № 1 и в столбце № 1 эксель. Применительно к тому массиву, что нам надо создать, A1 - это левый верхний угол от того прямоугольника с цифрами (данными), где содержатся сами числа, которые нужно превратить в массив. Аналогично, Cells(a1, a2) – это правый нижний угол того же прямоугольника. Хотя у нас количество цифр равно количеству столбцов, то есть получился квадрат, в любом случае: каждый квадрат является прямоугольником.
Фрагмент № 2 макроса и фрагмент № 3 макроса – это по сути одно и то же. И в том, и в другом случае мы создаем массив под названием (под именем) iskh. Можно даже проверить, каким будет элемент этого массива в строке 7 и в столбце 3, например, с помощью строки:
MsgBox (iskh(7, 3))
Мы увидим результат:
Результат – цифра 6. Действительно, в этом массиве в строке № 7 и в столбце № 3 находится цифра 6. Кстати, других шестерок в нашем массиве нет. Но это и не важно, ведь мы не ставили задачу "найти шестерку в массиве", а просили показать элемент массива, который находится в строке № 7 и в столбце № 3.
Аналогичным образом («в одну строку, без использования циклов «for-next») можно отобразить на листе эксель нужный массив, используем следующий код макроса (назовем его так: "Вывод массива (матрицы) на лист эксель"):
Range(Cells(1, a2 + 2).Address).Resize(UBound(iskh, 1), UBound(iskh, 2)).Value = iskh
В этой формуле после «Cells» мы аналогичным образом указываем номер строки, а затем номер столбца той ячейки, в которой будет отображен левый верхний элемент нашего массива. Нам даже не нужно указывать правую нижнюю границу, потому что количество строк и столбцов массива нам уже заранее известно, мы же хотим отобразить весь массив целиком, а не его какую-то часть. Фраза
Resize(UBound(iskh, 1), UBound(iskh, 2)
позволяет выдать все элементы нужного нам массива как по строкам, так и по столбцам.
Вот как это будет выглядеть в нашем примере:
Здесь в ячейке K1 – левый верхний элемент массива, а все элементы массива были отображены в ячейках эксель благодаря одной-единственной строчке макроса на VBA, о которой мы уже говорили.
Приведем другой пример. Очистим лист эксель и введем в него другие данные:
Пусть у нас есть следующие числа на листе эксель.
Главные отличия этого блока чисел от того, что мы рассмотрели в предыдущем примере, в следующем:
- данные начинаются не с ячейки A1;
- количество строк массива не совпадает с количеством столбцов (то есть мы имеем дело именно с тем прямоугольником, что не является квадратом).
Допустим, что эти числа надо превратить в массив с помощью макросов VBA (если предыдущие фрагменты макросов можно было считать частями одного и того же макроса, потому что они между собой связаны логически (превратить числа из листа эксель в элементы макроса, отобразить элементы того же макроса, но в других ячейках того же листа эксель, и т.д.), то приведенные ниже фрагменты уже будут иметь отношение к другому макросу, это уже другой пример):
'начало фрагмента № 4 макроса
Dim iskh2 As Variant
Iskh2 = Range(Cells(6, 4), Cells(8, 8)).Value
'конец фрагмента № 4 макроса
Здесь ситуация аналогичная предыдущей. Cells(6,4) – это левая верхняя ячейка блока с данными (D6), а Cells(8, 8) – это правая нижняя ячейка того же блока, то есть H8. Здесь мы уже не вводим отдельные переменные для высоты и ширины массива, они высчитываются автоматически, потому что мы вводим левую верхнюю и правую нижнюю границы нужного блока с данными.
Таким образом, основные параметры (номер строки и номер столбца левой верхней и правой нижней ячеек того диапазона, из которого нужно собрать массив) можно вводить не только с помощью Inputbox, но также и в виде конкретных чисел.
И если мы, например, захотим проверить какой-то элемент массива (например, запросив элемент массива, который находится в строке 3 и столбце 5), то введем в макрос строчку:
MsgBox (iskh2(3, 5))
то получим результат – число 15, ведь оно будет в третьей строке и пятом столбце массива:
Другого числа 15 нет в этом массиве, но и в данном случае это тоже не важно, ведь мы опять не делаем запрос «где находится число 15», а просим показать элемент массива, который находится в третьей строке и в пятом столбце массива.
Кстати, если бы стояла задача "найти число 12 в массиве", то мы бы ввели следующий макрос:
Sub Найти_число()
Dim iskh As Variant
iskh = Range(Cells(6, 4), Cells(8, 8)).Value
For t = 1 To 3
For s = 1 To 5
If iskh(t, s) = 12 Then GoTo pokaz
Next s
Next t
pokaz:
MsgBox ("строка=" & t & "; " & "столбец=" & s)
End Sub
Здесь t изменяется от 1 до 3, потому что в нашем массиве 3 строки, а s изменяется от 1 до 5, потому что в нашем массиве 5 столбцов.
Вот и результат работы этого макроса:
Аналогичным образом можно находить любой другой элемент массива, тогда вместо 12 нужно ввести нужное значение. Можно даже предварительно с помощью InputBox ввести именно то значение массива, которое нужно найти. pokaz - это номер строки, к которой надо перейти, если будет найдено совпадение.
Можно даже добавить такие строки в массиве, которые будут сообщать о том, что нужный элемент массива не найден. Заменим 12 на 158, чтобы было видно, как работает макрос, если нужное число отсутствует в массиве. В этом случае модернизированный макрос будет следующим:
Sub Найти_число()
Dim iskh As Variant
iskh = Range(Cells(6, 4), Cells(8, 8)).Value
For t = 1 To 3
For s = 1 To 5
If iskh(t, s) = 158 Then GoTo pokaz
Next s
Next t
GoTo net
pokaz:
MsgBox ("строка=" & t & "; " & "столбец=" & s)
Exit Sub
net:
MsgBox ("Ничего не найдено")
End Sub
А вот и результат работы модернизированного макроса:
Как мы и говорили, можно то число, которое необходимо искать (найти), заранее обозначить с помощью InputBox. Тогда макрос будет следующим:
Sub Найти_число()
Dim iskh As Variant
iskh = Range(Cells(6, 4), Cells(8, 8)).Value
s1 = InputBox("Что будем искать?")
For t = 1 To 3
For s = 1 To 5
If iskh(t, s) = s1 Or iskh(t, s) = 1 * s1 Then GoTo pokaz
Next s
Next t
GoTo net
pokaz:
MsgBox ("строка=" & t & "; " & "столбец=" & s)
Exit Sub
net:
MsgBox ("Ничего не найдено")
End Sub
Приведенный выше макрос будет искать числовые значения, но не текстовые (при попытке искать текст мы увидим сообщение об ошибке в тексте макроса).
Кстати, не всегда массивы можно получить только из тех данных, что находятся в ячейках эксель.
Можно, например, создать массив 100 на 100, который состоял бы из случайных чисел от 0 до 100. Вот как это будет выглядеть:
Sub Matrix()
Dim matrix(1 To100, 1 To 100) As Integer
Dim i As Integer, j As Integer
' Заполнение матрицы случайными числами
For i = 1 To 100
For j = 1 To 100
matrix(i, j) = Int(Rnd() * 101)
Next j
Next i
' Вывод матрицы на лист
Range(Cells(1, 1).Address).Resize(UBound(matrix, 1), UBound(matrix, 2)).Value = matrix
End Sub
Мы получим 10 тысяч цифр, вот фрагмент листа с данными:
Одно из основных преимуществ переноса данных из массива в ячейки эксель заключается в огромной скорости, на которой происходит заполнение ячеек эксель.
Даже если бы мы аналогичным образом получили массив не 100 на 100, а 10000 на 10000, время заполнения ячеек (а если 10 тысяч умножить на 10 тысяч, то это уже будет 100 миллионов ячеек) не превысило бы пяти минут:
Кстати, в реальности обычно используются макросы гораздо меньшего размера, и это значит, что время переноса массива в ячейки эксель будет намного меньше пяти минут. Между прочим, тот самый массив 100 на 100, о котором мы говорили чуть раньше, заполнялся меньше, чем за полторы секунды:
Кстати, массивы не всегда бывают цифровыми. Приведем еще один пример. Пусть у нас имеется массив, который состоит из смешанных элементов (букв и цифр):
Если мы в одном макросе соберем несколько элементов (частей), о которых говорилось в этой статье, а именно:
- Фрагмент № 1 макроса;
- Фрагмент № 3 макроса;
- Вывод массива (матрицы) на лист эксель,
то мы получим следующий результат: вначале данные из листа эксель перейдут в массив, а затем из массива на другие ячейки эксель:
Вот текст этого макроса целиком:
Sub fr1()
'Начало фрагмента № 1 макроса
'Вначале вводим исходные данные
a1 = 1 * InputBox("Введите высоту")
a2 = 1 * InputBox("Введите ширину")
'Конец фрагмента № 1 макроса
'начало фрагмента № 3 макроса
Dim iskh As Variant
iskh = Range(Cells(1, 1), Cells(a1, a2)).Value
'конец фрагмента № 3 макроса
Range(Cells(1, a2 + 2).Address).Resize(UBound(iskh, 1), UBound(iskh, 2)).Value = iskh
End Sub
Таким образом, полученный массив составлен так, что может работать как с текстовыми элементами, так и с цифровыми. А все из-за того, что мы при создании (открытии) массива использовали тип данных "As Variant". Это позволяет использовать любые данные, кроме данных с фиксированной длинной String.
