Split an Array in VBA

[flanna]

I need to do something like this that works in VBA for Excel:

HowMany As Single
FromEnd As Single
LargeArray As Variant
NewArray As Variant
HowMany = 10
FromEnd = 5
LargeArray =(1,2,3,,,10000)
NewArray = LargeArray.split(HowMany, FromEnd)

That doesn't work in VBA.
What I want is to take a large, one dimensional array of numbers and make a new array that contains the 10 numbers that are right before the last 5 numbers in the array.
Here's an example:
LargeArray(1,2,3,4,5,6,7,8,9,,,,,,,100,101,102,103,104,105,106,107,108,109,110)
Extracting from it this new array:
NewArray(101,102,103,104,105)

 

[flanna]

I need to do something like this that works in VBA for Excel:

HowMany As Single
FromEnd As Single
LargeArray As Variant
NewArray As Variant
HowMany = 10
FromEnd = 5
LargeArray =(1,2,3,,,10000)
NewArray = LargeArray.split(HowMany, FromEnd)

That doesn't work in VBA.
What I want is to take a large, one dimensional array of numbers and make a new array that contains the 10 numbers that are right before the last 5 numbers in the array.
Here's an example:
LargeArray(1,2,3,4,5,6,7,8,9,,,,,,,100,101,102,103,104,105,106,107,108,109,110)
Extracting from it this new array:
NewArray(101,102,103,104,105)

Oops, the NewArray should be (96,97,98,99,100,101,102,103,104,105)

 

[igold]

Hi flanna,

See if this comes close to what you want...

Sub test()


    Dim LargeArray() As Variant
    Dim NewArray() As String
    Dim NA As String
    Dim i As Long
    Dim HowMany As Long, FromEnd As Long


    HowMany = 3
    FromEnd = 6
    LargeArray = Array(1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20)
    For i = UBound(LargeArray) - FromEnd To UBound(LargeArray) - FromEnd + HowMany - 1
        NA = NA & LargeArray(i) & ","
    Next
    NewArray() = Split(NA)
    MsgBox NewArray(0)


End Sub

HTH

igold

 

[Rick Rothstein]

Oops, the NewArray should be (96,97,98,99,100,101,102,103,104,105)

Your sample set HowMany=10 and FromEnd=5... please explain what the FromEnd variable is from the end of (if it is from the end of the array, then you cannot get 10 elements from the array if you start drawing from there). Also, are you looking for NewArray to be a VBA one-dimensional array or an Excel two-dimensional array (like you get when you assign a range to a Variant variable)?

 

[Rick Rothstein]

Hi flanna,

See if this comes close to what you want...

Sub test()


    Dim LargeArray() As Variant
    Dim NewArray() As String
    Dim NA As String
    Dim i As Long
    Dim HowMany As Long, FromEnd As Long


    HowMany = 3
    FromEnd = 6
    LargeArray = Array(1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20)
    For i = UBound(LargeArray) - FromEnd To UBound(LargeArray) - FromEnd + HowMany - 1
        NA = NA & LargeArray(i) & ","
    Next
    NewArray() = Split(NA)
    MsgBox NewArray(0)


End Sub

Your code outputs 13,14,15... shouldn't it output 14,15,16 instead (6th element from the end)?

 

[mikerickson]

The FromEnd argument is 0 based, i.e. if 105 is "5 FromEnd", then 110, the last entry, is "0 FromEnd"
and your HowMany augment is 1 based.

If you want your ResultArray to be 1-based, you could use code like

Dim ResultArray()
' ...

ReDim ResultArray(1 to HowMany)
For i = 1 to HowMany
    ResultArray(i) = BigArray(Ubound(BigArray) - (HowMany +1) - FromEnd + i)
Next i

Excel VBA doesn't have many functions that manipulate arrays quickly, brute force looping is often needed.

 

[Rick Rothstein]

The FromEnd argument is 0 based, i.e. if 105 is "5 FromEnd", then 110, the last entry, is "0 FromEnd"
and your HowMany augment is 1 based.

If you want your ResultArray to be 1-based, you could use code like

Dim ResultArray()
' ...

ReDim ResultArray(1 to HowMany)
For i = 1 to HowMany
    ResultArray(i) = BigArray(Ubound(BigArray) - (HowMany +1) - FromEnd + i)
Next i

Excel VBA doesn't have many functions that manipulate arrays quickly, brute force looping is often needed.

If we assume that these assignments...

HowMany = 10
FromEnd = 5

are reversed and should have actually been this...

HowMany = 5
FromEnd =10

then the OP's NewArray of 101, 102, 103, 104, 105 in Message #1 makes more sense... you start the new array at the 10th element from the end and count it and 4 more (making 5 total to match what I think the HowMany should have been). Given this, we can create the one-dimensional array (which start at element #1) without using any loops whatsoever...

[table="width: 500"]
[tr]
	[td]Sub test()
  
  Dim HowMany As Long, FromEnd As Long, Start As Long, ElementCount As Long
  Dim LargeArray As Variant, NewArray As Variant
  
  HowMany = 5
  FromEnd = 10
    
  ElementCount = 110
  LargeArray = Evaluate("ROW(1:" & ElementCount & ")")
  
  Start = UBound(LargeArray) - FromEnd + 1
  NewArray = Application.Transpose(Application.Index(LargeArray, Evaluate("ROW(" & Start & ":" & Start + HowMany - 1 & ")"), 1))

  [COLOR="#008000"]' Let's see the results (joined together for display purposes)[/COLOR]
  MsgBox Join(NewArray, ", ")
  
End Sub[/td]
[/tr]
[/table]

 

[igold]

Rick,

Your code outputs 13,14,15... shouldn't it output 14,15,16 instead (6th element from the end)?

I did struggle with that, but seeing how it is really dependent upon how the OP is doing the counting, I figured if he could figure out how to change the code to match his preference...

igold

 

[Rick Rothstein]

I did struggle with that, but seeing how it is really dependent upon how the OP is doing the counting...

See the mini-analysis I posted in my response to mikerickson in Message #7.

 

[flanna]

Your sample set HowMany=10 and FromEnd=5... please explain what the FromEnd variable is from the end of (if it is from the end of the array, then you cannot get 10 elements from the array if you start drawing from there). Also, are you looking for NewArray to be a VBA one-dimensional array or an Excel two-dimensional array (like you get when you assign a range to a Variant variable)?

FromEnd is how many numbers from the end of the LargeArray the NewArray ends.
So with these variables: LargeArray(1,2,3,4,5,6,7,8,9,10) HowMany=3 FromEnd=2
NewArray would be (6,7,8). 3 numbers that finish 2 numbers from end of LargeArray.

NweArray would be a VBA one-dimensional array.