Как умножить диапазон значений в Excel на скалярную переменную с помощью VBA. Ответ на аналогичный вопрос у меня не работает
Я реализовал этот метод, чтобы умножить каждый элемент массива на число, хранящееся в переменной, он работает, но я считаю, что он ужасно медленный. Существует ли общепринятый «самый быстрый» способ умножения каждого элемента в диапазоне на константу? Или хотя бы тот, который не такой медленный? Я должен сделать это 10 раз, и это занимает пару минут. Это мое медленное решение:
MultFactor = 10
For Each cell In Sheet1.Range("B3:B902")
cell.Value = cell.Value * MultFactor
Next cell
Приведенное здесь решение (с 14 лайками):
Умножить весь диапазон на значение?
умножается на константу (не на переменную), но даже если я использую этот код как есть (за исключением изменения диапазона с "A1:B10" на "B3:B902"),
Dim rngData As Range
Set rngData = Sheet12.Range("B3:B902")
rngData = Evaluate(rngData.Address & "*2")
Получаю бессмысленный ответ. Мои первоначальные значения в B3:B902 равны нулю для первых 100 элементов или около того, а затем немного увеличиваются и, наконец, уменьшаются и имеют еще один набор нулей, но то, что заканчивается в моем диапазоне, представляет собой ряд чисел, которые стирают все в моем диапазоне. Он начинается с -224,5 и уменьшается на 0,5 до последней ячейки.
-224.5
-224.0
-223.5
etc
Даже если бы это сработало, как бы мне изменить его, чтобы использовать переменную MultFactor? Я должен делать что-то неправильно здесь, но не знаю, что это такое. Я ценю помощь, если кто-то может определить, что я делаю неправильно.
Ответы 2
Тебе нужно:
rngData = Sheet12.Evaluate(rngData.Address & "*2")
поскольку свойство адреса по умолчанию не включает имя листа (поэтому ваша формула оценивается в контексте диапазона активного листа B3: B902)
Тогда потребуется:
rngData = Sheet12.Evaluate(rngData.Address & "*" & MultFactor)
чтобы добавить в вашу переменную.
Это будет в сотни-тысячи раз быстрее. Разница в том, что все расчеты выполняются в массиве VBA, а не непосредственно в ячейках рабочего листа, одна за другой. Как только массив обновляется, он записывается обратно на рабочий лист за один раз. Это сокращает взаимодействие с рабочим листом до двух экземпляров: чтения массива и его записи. Сокращение количества случаев, когда ваш код VBA касается чего-либо на стороне рабочего листа, имеет решающее значение для скорости выполнения.
Sub Mozdzen()
Const FACTOR = 10
Const SOURCE = "B3:B902"
Dim i&, v
v = Sheet1.Range(SOURCE)
For i = 1 To UBound(v)
v(i, 1) = v(i, 1) * FACTOR
Next
Sheet1.Range(SOURCE) = v
End Sub
Основываясь на приведенной выше идее, лучший способ управления кодом — инкапсулировать умножение массива с помощью специальной функции:
Sub Mozdzen()
Const FACTOR = 10
Const SOURCE = "B3:B902"
With Sheet2.Range(SOURCE)
.Value2 = ArrayMultiply(.Value2, FACTOR)
End With
End Sub
Function ArrayMultiply(a, multfactor#)
Dim i&
For i = 1 To UBound(a)
a(i, 1) = a(i, 1) * multfactor
Next
ArrayMultiply = a
End Function
Это было как минимум в 100 раз быстрее. (< 1 секунды против 150 секунд), и если мультифактор хранится в переменной, мне просто нужно было удалить «#» в функции, и все были счастливы.
Независимо от того, какое решение вы будете использовать: вам нужно быть особенно осторожным, чтобы не запускать код несколько раз. Я бы посоветовал вместо этого использовать новый столбец (и для этого вы могли бы использовать простую формулу вместо кода)