Используйте VBA для многократного генерирования случайных чисел

Question

Я знаю, что в Excel можно использовать функцию rand() для генерации случайных чисел, и при каждом нажатии клавиши F9 все результаты rand() на этом листе будут обновляться.

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

Например у меня

• диапазон A1 ~ A100, где я хотел бы генерировать случайные числа;
• столбец B - это место расчета, в котором VBA выполняет некоторые вычисления с помощью функции Excel "Solver";
• в столбце C я хочу записать 1000-кратный расчет, каждый раз из столбца B;
• окончательный результат представляет собой среднее значение столбца C.

Теперь мои проблемы

1. Я не знаю, как обновить ячейки в столбце A, используя VBA. Как я могу это сделать?
2. Я замечаю, что во время функции решателя VBA, когда ячейки в столбце B обновляются, при каждом обновлении случайные числа столбца A обновляются - как я могу подавить это?

Answer 1

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

 Sub GenerateRandom()
     Dim i As Long
     For i = 1 To 100
         Range("A" & i) = rnd()
     Next i 
End Sub

ALT+F8 для View Macros

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

Answer 2

Я вставил элемент управления формы "Кнопка" (необходимо установить флажок, чтобы включить вкладку "Разработчик" в разделе «Параметры-> Настройка ленты-> Основные вкладки»), и запускал этот макрос при каждом нажатии:

Public Sub ran()
Dim a As Range
    For i = 1 To 100
        Dim r As Integer
        r = Int(Timer * Rnd())   'truncate the random number to an integer
        Cells(i, 1).Value = r    'set the value in the cell to this random number
    Next i
End Sub

Rnd() производит число в диапазоне [0, 1), поэтому я просто умножил его на системный таймер (который использует сотые доли секунды) и округлил его до целого числа, используя Int (). Вы можете опустить Int (), если вам нужны десятичные разряды, и при желании установить минимальный-максимальный диапазон, вы можете сделать что-то вроде

r = (max - min) * r + min

... после того, как вы получите семя таймера.

Answer 3

Нажатие F9 не будет генерировать новые случайные значения из вашей функции VBA, но нажатие Ctrl+Alt+F9 (комбинация клавиш "глобальный пересчет" в Excel) будет.

Answer 4

1. Я думаю, что Excel автоматически обновляет все ячейки по умолчанию при изменении некоторых ячеек. Вы можете сбросить это в настройках
2. Во-первых, вы должны знать, что повторное генерирование случайных чисел в Excel может привести к очень коротким циклам, после которых числа повторяются. Возможно, он изменился в более поздних версиях Excel (хотя я сомневаюсь в этом), но в Excel 97 я видел цикл длиной 4! Т.е. после четырех раз, генерирующих 100 случайных чисел, он сгенерировал ровно 100 таких же случайных чисел. Вам нужно будет правильно посеять семена. Во-вторых, вы можете сгенерировать необходимый код VBA, записав макрос, в котором вы генерируете случайные числа, используя модуль случайных чисел плагина анализа данных (VBA): он заполняет ячейки числами, а не функцией rand (). Чтобы добавить набор инструментов в XL2007-VBA:

AddIns("Analysis ToolPak - VBA").Installed = True

чтобы получить 100 случайных чисел в ячейках A1:A100:

Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$A$1"), 1, 100 _ , 1, , 0, 1