CODEWITHSUNDEEP
excelvba
Tom Mozdzen
Tom Mozdzen

Reputation: 340

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

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?

Upvotes: 1

Views: 1773

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

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

Upvotes: 3

Rory
Rory

Reputation: 34045

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.

Upvotes: 2

Related Questions