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

Question

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?

lori_m · Accepted Answer · 2013-09-20 06:57:51Z

2

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)

edited Sep 20, 2013 at 6:57

answered Aug 29, 2013 at 15:48

lori_m

5,5751 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thierry Over a year ago

This looks very elegant, and works well. I am just having a hard time understanding how it works exactly. .MMult multiplies [{1,1}] by the result of Choose. But I can't figure out what this result is.

lori_m Over a year ago

Choose appends a row of ones [{1,2,3,4,5;1,1,1,1,1}], MMult then sums these the array vertically. (You can add a watch for Application.Choose([{1;2}], v, 1) to break the formula down.)

lori_m Over a year ago

i've also added an improved approach i recently stumbled upon

Wolfgang Kuehn · Accepted Answer · 2013-08-29 06:31:37Z

2

Well, this is kind of cheating:

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

answered Aug 29, 2013 at 6:31

Wolfgang Kuehn

13k3 gold badges37 silver badges49 bronze badges

3 Comments

lori_m Over a year ago

simpler than mine... another possibility would be to use a workbook name instead of a range if allowed: Names.Add "_a",a : b=[_a+1]: Names("_a").delete

Thierry Over a year ago

When using a name, you don't actually write anything in the Worksheet, do you?

lori_m Over a year ago

The name is stored with the workbook and is visible in the name manager box, it is not written to the sheet.

Arconath · Accepted Answer · 2013-08-29 15:34:10Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related