Learning VBA for Excel, I am trying to do as much of my coding without the use of loops. As an exercise, multiplying the numbers of two adjacent ranges, I came up with this:
Sub multiply_range()
Dim a, b, c As Range
Set a = Range("a1:a5")
Set b = Range("b1:b5")
Set c = Range("c1:c5")
a.Value = Evaluate("row(" & a.Address & ")")
b.Value = Evaluate("row(" & b.Address & ")")
c.Value = Evaluate(a.Address & "*" & b.Address)
End Sub
Which works quite nicely. Now I want to do something similar but using arrays instead. Starting with this code:
Sub multiply_array()
Dim aArr(), bArr(), cArr()
ReDim aArr(5), bArr(5), cArr(5)
For i = 0 To 4
aArr(i) = i + 1
bArr(i) = i + 1
Next
For i = 0 To 4
cArr(i) = aArr(i) * bArr(i)
Next
For i = 0 To 4
Range("D" & i + 1).Value = cArr(i)
Next
End Sub
How would you replace any one of these FOR loops with code that doesn't use loops?
Here you go:
Sub Squares()
Dim n&
n = 5
[d1].Resize(n) = Evaluate("row(1:" & n & ")^2")
End Sub
UPDATE
Here is a variant that uses no loops and no ranges:
Sub Squares()
Dim a, b, n&
n = 5
a = Array(1, 2, 3, 4, 5)
b = Array(1, 2, 3, 4, 5)
[d1].Resize(n) = Evaluate("{" & Join(a, ";") & "}*{" & Join(b, ";") & "}")
End Sub
For multiplying arbitrary arrays you can try pasting this code in a new module:
Dim X, Y
Sub MultiplyArrays()
Dim Z
X = Array(1, 2, 3, 4, 5)
Y = Array(1, 2, 3, 4, 5)
Z = [GetX()*GetY()]
Range("D1").Resize(UBound(Z)) = Application.Transpose(Z)
End Sub
Function GetX()
GetX = X
End Function
Function GetY()
GetY = Y
End Function
Another slightly trickier way is to use worksheetfunction methods:
Sub MultiplyArrays2()
Dim X, Y, Z
X = Array(1, 2, 3, 4, 5)
Y = Array(1, 2, 3, 4, 5)
With Application
Z = .PV(, 1, .PV(, X, Y))
Range("D1").Resize(UBound(Z)) = .Transpose(Z)
End With
End Sub
Also see: Adding or multiplying variants in VBA
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With