How to multiply a range of values in Excel by a scalar variable using VBA?

Question

I have implemented this method to multiply every array element by a number held in a variable. It is terribly slow.

Is there an accepted "fastest" way to multiply every element in a range by a constant? Or at least one which is not as slow? I have to do this 10 times and it takes a couple of minutes.

MultFactor = 10
For Each cell In Sheet1.Range("B3:B902")
    cell.Value = cell.Value * MultFactor
Next cell

The solution cited in Multiply Entire Range By Value? multiplies by a constant (not a variable). If I use this code (changing the range from "A1:B10" to "B3:B902"), I get a nonsense answer.

Dim rngData As Range
Set rngData = Sheet12.Range("B3:B902")
rngData = Evaluate(rngData.Address & "*2")

My original values in B3:B902 are zero for the first 100 elements or so and then increase a bit and finally decrease and have another run of zeros, but what ends up in my range is a series of numbers that clobbers everything in my range. It begins at -224.5 and decreases by 0.5 all the way to the last cell.

-224.5
-224.0
-223.5
 etc.

Even if that worked, how would I modify it to use the variable MultFactor?

No matter which solution you will use: You need to be extra careful that you don't run the code multiple times. My advice would be to use a new column instead (and for that, you could use a simple formula instead of code) — FunThomas, May 06 '22 at 08:15

Excel Hero · Accepted Answer · 2022-05-06T09:05:13.980

This will be hundreds to thousands of times faster. The difference is that all of the calcs are done to a VBA array instead of directly to worksheet cells, one by one. Once the array is updated it is written back to the worksheet in one go. This reduces worksheet interaction to just two instances, reading the array and writing it. Reducing the number of instances that your VBA code touches anything on the worksheet side is critical to execution speed.

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

Building on the above idea, a better way to manage the code is to encapsulate the array multiplication with a dedicated function:

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

This was at least 100x faster. (< 1 second vs 150 seconds) and if multfactor is stored in a variable, I just had to remove the "#" in the Function and everybody was happy. — Tom Mozdzen, May 06 '22 at 16:58

score 2 · Answer 2 · answered May 06 '22 at 07:55

You need:

rngData = Sheet12.Evaluate(rngData.Address & "*2")

since the address property doesn't include the sheet name by default (so your formula is evaluated in the context of the active sheet's range B3:B902)

Then it would need:

rngData = Sheet12.Evaluate(rngData.Address & "*" & MultFactor)

to add in your variable.

How to multiply a range of values in Excel by a scalar variable using VBA?

2 Answers2