CODEWITHSUNDEEP
arraysvbaexcel
Thierry
Thierry

Reputation: 43

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

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?

Upvotes: 4

Views: 3443

Answers (3)

lori_m
lori_m

Reputation: 5567

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)

Upvotes: 2

Arconath
Arconath

Reputation: 453

This is a user defined function which would do the trick for you. Just pass the reference cell and the increment value as arguments.

It doesn't handle cases where you have letters in the input cells so you'd need to create your own handling for that or ensure good data.

Function udf_IncrementArrayByVal(cellRef As Range, increment As Double)
    Dim tempStr As String
    Dim splitArray() As String
    Dim cntr As Long
    Dim arrayLength As Long

    tempStr = Replace(Replace(cellRef(1, 1).Value, ")", ""), "(", "")

    splitArray = Split(tempStr, ",")

    For cntr = 0 To UBound(splitArray)
        splitArray(cntr) = splitArray(cntr) + increment
    Next cntr

    tempStr = "(" + Join(splitArray, ",") + ")"

    udf_IncrementArrayByVal = tempStr
End Function

Upvotes: 0

Wolfgang Kuehn
Wolfgang Kuehn

Reputation: 12926

Well, this is kind of cheating:

a = Array(1, 2, 3, 4, 5)
Range("a1:e1") = a
b = Evaluate("=a1:e1+1")

Upvotes: 2

Related Questions