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)

Possible duplicate of [VBA array sort function?](http://stackoverflow.com/questions/152319/vba-array-sort-function) — cxw, Jun 29 '16 at 13:34
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, Jun 29 '16 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, Jun 29 '16 at 14:41
I think you need to add some sample data - both for array and the worksheet - showing before and after scenarios. It looks like you're also changing contents of the cells — dbmitch, Jun 29 '16 at 14:45
remove from array and rearrange array are not the same. Remove means delete, and for this purpose i would use dictionnary or collection. In Arrays to change the index of a value, you will need a second array created from the fiirst one, and put this in a function, the second array being the result of the function. — Patrick Lepelletier, Jun 30 '16 at 08:22

score 1 · Accepted Answer · answered Jun 29 '16 at 14:21

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 '16 at 14:21

user3598756

28,893
4
18
28

its saying there is a byref argument mismatch with the j when i call the sub. How do I fix this? – aggieman Jun 29 '16 at 15:39
1

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 – user3598756 Jun 29 '16 at 15:47
side note, why would anyone use `integer` when there are `Long`'s ? it's like i'd use chr() when there is `String`... – Patrick Lepelletier Jun 30 '16 at 08:26
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... – user3598756 Jun 30 '16 at 08:31

vba array element removal

1 Answers1