Выбор случайной ячейки из диапазона Excel с помощью кода VBA и присвоение ее значения переменной. Заполнение диапазона ячеек случайными числами.
Выбор случайной ячейки из диапазона
Задание
Необходимо написать код VBA Excel для кнопки, при нажатии на которую выбирается случайная ячейка из заданного диапазона и ее значение присваивается переменной.
Решение
Для решения поставленной задачи в качестве произвольного диапазона ячеек отлично подойдет объект Selection, который при тестировании кода позволит не менять адрес диапазона в процедуре, а выбирать его непосредственно на рабочем листе.
Кнопка CommandButton1 добавлена из набора элементов управления рабочего листа ActiveX. Модуль листа открывается двойным кликом по кнопке в режиме конструктора.
Для выбора случайной ячейки будем использовать свойство диапазона Cells, функцию Rnd и оператор Randomize, инициализирующий генератор случайных чисел функции Rnd и задающий начальное число для генерации первого псевдослучайного числа.
1. Объявляем переменную x с универсальным типом данных (Variant), так как в конечном итоге ей будет присваиваться содержимое случайной ячейки, которое может быть любым:
|
1 |
Dim x As Variant |
2. Определяем количество ячеек в выделенном диапазоне:
|
1 |
x = Selection.Count |
3. Инициируем генератор случайных чисел функции Rnd, используя оператор Randomize без аргумента:
|
1 |
Randomize |
В этом случае, в качестве аргумента оператора Randomize, используется текущее значение системного таймера.
4. Выбираем случайный порядковый номер ячейки в выделенном диапазоне:
|
1 |
x = Round(Rnd * (x - 1), 0) + 1 |
Так как в выделенном диапазоне индексация ячеек начинается с единицы, прибавляем к округленному значению случайного числа, которое может быть равно нулю, единицу. Псевдослучайное число, возвращенное функцией Rnd, умножаем на число ячеек минус один, чтобы исключить результат, когда порядковый номер на единицу больше количества ячеек в выделенном диапазоне.
5. Присваиваем значение случайной ячейки переменной x, используя свойство диапазона Cells:
|
1 |
x = Selection.Cells(x) |
Как известно, индексация ячеек в диапазонах осуществляется не только по строкам и столбцам, но и по порядковым номерам. Нумерация идет слева направо и сверху вниз, ее мы здесь и используем.
6. Выводим значение переменной в информационное окно MsgBox:
|
1 |
MsgBox x |
Теперь весь код выбора случайной ячейки из выделенного диапазона и присвоения ее значения переменной в сборе:
|
1 2 3 4 5 6 7 8 |
Private Sub CommandButton1_Click() Dim x As Variant x = Selection.Count Randomize x = Round(Rnd * (x - 1), 0) + 1 x = Selection.Cells(x) MsgBox x End Sub |
И тот же код VBA Excel в сокращенном виде:
|
1 2 3 4 5 6 |
Private Sub CommandButton1_Click() Dim x As Variant Randomize x = Selection.Cells(Round(Rnd * (Selection.Count - 1), 0) + 1) MsgBox x End Sub |
Заполнение ячеек случайными числами
Задание
Необходимо заполнить произвольный диапазон ячеек случайными числами с помощью кода VBA Excel.
Решение
В качестве заданного диапазона, как и в предыдущем решении, будем использовать блок выделенных ячеек. Пройти по всем ячейкам диапазона из кода VBA Excel нам поможет цикл For Each… Next.
|
1 2 3 4 5 6 7 |
Private Sub CommandButton1_Click() Dim myCell As Range Randomize For Each myCell In Selection myCell = Round(Rnd * 10000, 0) Next End Sub |
Этот код заполняет по очереди все ячейки выделенного диапазона случайными числами в интервале от 0 до 10000. После нажатия кнопки процесс заполнения виден визуально, но его можно ускорить.
Для ускорения заполнения ячеек случайными числами можно использовать массив, циклы в котором работают значительно быстрее, чем при переборе ячеек в диапазоне.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Private Sub CommandButton1_Click() Dim massiv() As Variant, n1 As Long, n2 As Long, i1 As Long, i2 As Long If Selection.Count = 1 Then Randomize Selection = Round(Rnd * 10000, 0) Else massiv = Selection n1 = UBound(massiv, 1) n2 = UBound(massiv, 2) Randomize For i1 = 1 To n1 For i2 = 1 To n2 massiv(i1, i2) = Round(Rnd * 10000, 0) Next Next Selection = massiv End If End Sub |
При запуске этого кода VBA Excel ячейки выбранного диапазона заполнятся случайными числами практически мгновенно.
Условие, проверяющее, не состоит ли диапазон из одной ячейки, необходимо для того, чтобы избежать ошибки, возникающей при присвоении массиву единственного значения из диапазона, состоящего из одной ячейки.
Также стоит учитывать, что максимальный размер массива зависит от операционной системы и доступного объема памяти. Например, если выделить весь рабочий лист и нажать кнопку, произойдет ошибка.
Добрый день. Не подскажете как найти случайное значение в указанном диапазоне. Замена
x = Selection.Countнаx = Range("D1:D8")не работает.x = Range("D1:D8").Countесть 5 ячеек:
A1
G8
D2
F7
N4
как выбрать одну случайную
Обсуждение закрыто.