VBA Adding to ReDim Value

Question

Ok, so I'm having trouble with using ReDim arrays; which I was told was the way to do this. I'm just not sure where to put it or had to add to the first Integer ((n, n)) for each pass through the function. FYI, the "Dim PropArray(0, 5) As String" is at the very top of the code where the "Option Compare Database" is.

Dim PropArray(0, 5) As String

Public Function ShowBeam()

For Each ctl In [Forms]![frmShelfBuilder]
    If Left(ctl.Name, 3) = "Box" Then
        If Int(Right(ctl.Name, (Len(ctl.Name)) - 3)) = Me.txtBeamCount Then
            If Box1.Visible = False Then
            Set rct = ctl
                PropArray(0) = rct.Name
                PropArray(1) = rct.Visible
                PropArray(2) = rct.Height 'Int out
                PropArray(3) = rct.Left 'Int out
                PropArray(4) = rct.Top 'Int out
                PropArray(5) = rct.Width 'Int out
            Else
            Set rct = ctl
            'ReDim Preserve PropArray(n + 1, 5) where "n" is the current number in the row (I think this is were you would add the "ReDim Preserve"?)
                PropArray(0) = rct.Name
                PropArray(1) = rct.Visible
                PropArray(2) = rct.Height 'Int out
                PropArray(3) = rct.Left 'Int out
                PropArray(4) = rct.Top 'Int out
                PropArray(5) = rct.Width 'Int out
            End If
        End If
    End If
Next

I think I'm on the right track. I'm just stuck at not know where to ReDim Preserve the array to add 1 to the first integer. Any suggestions are welcome. Muchacho apreciado!

braX · Accepted Answer · 2020-02-22 00:54:53Z

3

If you want to be able to ReDim it then you cannot specify the size in the Dim

Change it to this:

Dim PropArray() As String
ReDim PropArray(0, 5)

That will do the same thing, and allow you to ReDim Preserve it later.

Note: the ReDim will have to be inside of a routine instead of outside of it as your code shows.

So something like:

Dim PropArray() As String

Sub Prop_Init()
  ReDim PropArray(0, 5)
End Sub

Sub Prop_AddDim()
  ReDim Preserve PropArray(0, 6)
End Sub

Also keep in mind that you will only be able to ReDim the last part of the array.

edited Feb 22, 2020 at 0:54

answered Feb 21, 2020 at 21:45

braX

11.9k5 gold badges24 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

VBA Adding to ReDim Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related