Excel VBA - Shifting values of an array of numbers by a constant without looping

arrays, excel, vba

Solution

Maybe this to increment the array by one:

v = Array(1, 2, 3, 4, 5)
With Application
    v = .MMult([{1,1}], .Choose([{1;2}], v, 1))
End With

Update

Here's a more direct approach that also allows for incrementing 2D arrays

v = Application.Standardize(v,-1,1)

Worksheet function methods provide a large variety of math functions but the following were the only viable options i could find for basic arithmetic that support VBA arrays in arguments and return values:

(u-v)/w  = .Standardize(u,v,w) 
-u*v -w  = .Fv(0,u,v,w) 
int(u/v) = .Quotient(u,v)

Problem

Is there a way to add a constant to an array of numbers in Excel VBA (Excel 2007) without looping? For instance, I have the following array: ``` MyArray = (1,2,3,4,5) ``` And I want to obtain: ``` MyArray = (2,3,4,5,6) ``` Without looping. On the Spreadsheet, if the values are in cells `A1:A5`, I can select `B1:B5` and enter the array formula `{=A1:A5+1}` ``` MyArray = MyArray + 1 ``` does not seem to work (Type mismatch error). Any ideas?

Original source