Click here to Skip to main content
15,671,318 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello everybody. i m struggling again with dynamic array.
below my code
Function ContaArticoli(DriveIn As Range, SLOT As String) As Variant()
Dim elemento As Variant, contElemento As Integer, ArrProvvisorio() As Variant
Dim i As Byte, contRigheArray As Integer

Dim Prodotti As Variant
Prodotti = Array("A", "B", "C", "D")

contElemento = 0
contRigheArray = 0
    For i = 0 To 3
        For Each elemento In DriveIn   'per ogni articolo nel drive in
            If elemento = Prodotti(i) Then ' se elemento è = al prodotto (i)
                contElemento = contElemento + 1
            End If
'            Debug.Print LBound(ArrProvvisorio)
'            Debug.Print UBound(ArrProvvisorio)
            ReDim Preserve ArrProvvisorio(contRigheArray, 2)  ' contRigheArray
            ArrProvvisorio(contRigheArray, 0) = SLOT
                Debug.Print ArrProvvisorio(contRigheArray, 0)
            ArrProvvisorio(contRigheArray, 1) = Prodotti(i)
                Debug.Print ArrProvvisorio(contRigheArray, 1)
            ArrProvvisorio(contRigheArray, 2) = contElemento
                Debug.Print ArrProvvisorio(contRigheArray, 2)
            contElemento = 0 'azzero contatore
            contRigheArray = contRigheArray + 1
    Next i

End Function

when loop i from 0 shift to 1, it retunrs error.

What I have tried:

i tried to specify the array sizes upon data type dim , ArrProvvisorio(0 to 0 , 0 to 2) after to be dinamically changed thgrough redim preserve command
Updated 21-Feb-22 3:35am

1 solution

See the description at ReDim statement (VBA) | Microsoft Docs[^] :
If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

But you are trying to change the size of the first one.
Share this answer
Maciej Los 21-Feb-22 10:32am    
Good point!
Mat 257 21-Feb-22 11:01am    
thanks for reply, actually is what function meant to do. after collected data upon certain condition happening, it must append the data in a new row but columns must be the same, always 3 columns (0 to 2)
Richard MacCutchan 21-Feb-22 11:45am    
Well you need to find a different way of doing it, because ReDim Preserve will not work the way you want.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900