vba array element removal

Question

j = LBound(arrayTime)
Do Until j = UBound(arrayTime)
    j = j + 1
    b = b + 1
    cnc = b + r
    MsgBox cnc
        If cnc > 7 Then
            b = 0
            r = 0
            cnc = b + r
        End If
    numMins = Sheet5.Cells(cnc + 3, 2) - arrayTime(j)
    If numMins < 0 Then
        g = g + 1
        ReArrangeArray arrayTime, j
        'ReDim Preserve arrayTime(numrows - 1 + g)
        'arrayTime(numrows - 1 + g) = arrayTime(j)
        'MsgBox (arrayTime(numrows - 1 + g))
    Else
        Sheet5.Cells(cnc + 3, 2) = numMins
    End If
Loop

If the if statement is true I want to be able to put the array value at the end of the array and remove that value from its current spot. As the code is, it just adds it to the end and increases the size of the array from 12 to 13. How can I get the array to remain size 12 and still place the value at the end of the array and then remove it from its original position? I do not want to touch the array values in front. Just want to take that value and move it to the end. For instance array(1,2,3,4,5) If statement j on third loop. array(j)=3 end array should be array(1,2,4,5,3)

re-arranging the array would be difficult for this application because I do not want it to move the array values in front of the value when the if statement is true. I hope that makes sense — aggieman
– aggieman, Commented Jun 29, 2016 at 13:57
Do you have code that works as you describe it? As @aggieman says, you're not incrementing your j counter - and where does variable c get defined? — dbmitch
– dbmitch, Commented Jun 29, 2016 at 14:41

user3598756 · Accepted Answer · 2016-06-29 14:21:50Z

1

You could use a helper Sub like this one:

Sub ReArrangeArray(inputArray as Variant, indexToSwap as long)
    Dim I As Long
    Dim tempVal As Variant

    If indexToSwap >= LBound(inputArray) And indexToSwap < UBound(inputArray)  Then
        tempVal = inputArray(indexToSwap)
        For I = indexToSwap To UBound(inputArray) - 1
            inputArray(i) = inputArray(i + 1)
        Next I
        InputArray(UBound(inputArray))  = tempVal 
    End If
End Sub

To be called by your main Sub as follows:

ReArrangeArray arrayTime, j

answered Jun 29, 2016 at 14:21

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

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

4 Comments

aggieman Over a year ago

its saying there is a byref argument mismatch with the j when i call the sub. How do I fix this?

user3598756 Over a year ago

Probably 'j' is declared as of "integer" type in your code while ReArrangeArray() accepts a "Long" type: either you change your Sub declaration of 'j' as being of "Long" type or change the 'ReArrangeArray' last parameter type to an "Integer" type

Patrick Lepelletier Over a year ago

side note, why would anyone use integer when there are Long's ? it's like i'd use chr() when there is String...

user3598756 Over a year ago

I wouldn't say. Maybe "Integer" lasts from old languages. Or maybe books should stop frightening people with accurate choice of variable types not to waste memory as if we were still in the Commodore64 age...

Collectives™ on Stack Overflow

vba array element removal

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related