Need help for shifting a vector via array function on Excel VBA

Question

I need to shift elements of an (mx1) vector up by n rows. Last 3 cells become "0" (zero) instead of corresponding. I shared the task and my code below. I'de glad for further help.

Option Explicit
Option Base 1

Function ShiftVector(rng As Range, n As Integer)
    Dim i As Integer, nr As Integer, b() As Variant
    
nr = rng.Rows.Count
ReDim b(nr, 1) As Variant
    
    For i = 1 To n
        b(i, 1) = rng.Cells(i + n, 1)
    Next i
    For i = n + 1 To nr
        b(i, 1) = rng.Cells(i - n, 1)
    Next i

    ShiftVector = WorksheetFunction.Transpose(b())
End Function

T.M. · Accepted Answer · 2020-08-05 16:45:13Z

As close as possible to your code:

Function ShiftVector(rng As Range, n As Long)
    Dim i As Long, nr As Long, b() As Variant
    
    nr = rng.Rows.Count
    ReDim b(nr, 1) As Variant
    
    For i = 1 To n
        b(nr - n + i, 1) = rng.Cells(i, 1)
    Next i
    For i = n + 1 To nr
        b(i - n, 1) = rng.Cells(i, 1)
    Next i

    ShiftVector = b()
    
End Function

Further hints

Assuming the above question is an exercise, you might consider the above code as learning chance and compare it to your original code. Generally I'd prefer @ScottCraner 's approach looping through a 2-dimensional array which isn't as time consuming as looping through a range by means of VBA, at least for greater data sets.

It's better to declare counters for such data as Long instead of Integer as today's range rows (1048576, earlier 65k) exceed the Integer data limit (-32,768 to 32,767) by far.

You don't need to transpose the b array as it's already 2 dimensional and can be inserted as vertical data set.

Suggestion for improvement

You can assign a whole data set to a 2-dim (1-based) array, e.g. as follows

    Dim tmp As Variant
    tmp = rng.Value

By changing the range offset you can shift up the main part of data automatically (preserving the same range size):

    tmp = rng.Offset(n).Value

This allows you to re-enter only n "wrap around" data to the bottom of the prefilled tmp array:

Example function

Function SV(rng As Range, n As Long)
'a) get main part (starting n rows higher)
    Dim tmp As Variant
    tmp = rng.Offset(n)       ' shift up vertically (by n rows)
'b) get "wrap around" part of n first rows
    Dim wrap
    wrap = rng.Resize(n, 1)    ' assign to temporary array
'c) enter "wrap around" values to n bottom rows
    Dim i As Long
    For i = 1 To n
        tmp(UBound(tmp) - n + i, 1) = wrap(i, 1)
    Next i
'c) return rearranged array as function result
    SV = tmp
End Function

Thanks a lot. I understand my mistake and practise further hints!

Scott Craner · Accepted Answer · 2020-08-05 14:58:56Z

1

You were close, but you need a separate counter for the output in b. I also prefer to make the output a 2D array and avoid the WorksheetFunction.Transpose()

Function ShiftVector(rng As range, n As Integer)
    If rng.Count < n Then Exit Function
    
  
    Dim rngArr() As Variant
    rngArr = rng.Value
    
    ReDim b(1 To UBound(rngArr, 1), 1 To 1)
    
    Dim j As Long
    j = 1
    
    Dim i As Long
    For i = n + 1 To UBound(rngArr, 1)
        b(j, 1) = rngArr(i, 1)
        j = j + 1
    Next i
    For i = 1 To n
        b(j, 1) = rngArr(i, 1)
        j = j + 1
    Next i
    
    
    ShiftVector = b
End Function

answered Aug 5, 2020 at 14:58

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

2 Comments

T.M. Over a year ago

Posted my answer intentionally sticking close to OP (exercise?) roughly the same minute; of course I'd prefer your approach profiting from the additional 2-dim array.

T.M. Over a year ago

FYI Might be interested in late edit: I shortened array code to a single loop with n=3 iterations only. @Scott-Craner

Collectives™ on Stack Overflow

Need help for shifting a vector via array function on Excel VBA

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related