(VBA) Dynamically populating an array

[Tim_Excel_]

Hi forum

I am trying to populate an array in a loop. However, out of inexperience with arrays, I can't seem to populate my array.

Dim SuppressArray As Variant

i = 1
   For Each c In m_wb.Sheets("Componenten").Range(Cells(cell.Row, "N"), Cells(cell.Row, LCol))
        If c.Value <> "" And IsNumeric(Left(m_wb.Sheets("Componenten").Cells(1, c.Column), 1)) = True Then
        SuppressArray(i) = Left(m_wb.Sheets("Componenten").Cells(1, c.Column), InStr(1, m_wb.Sheets("Componenten").Cells(1, c.Column), "M") - 1)
        i = i + 1
        End If
    Next c
    If i = 1 Then GoTo nosuppress

In short: If the cell in the range is not empty and the header's first character is a number, then populate the array with the first numbers in that header. Then go to the next cell and repeat the process.

The code is breaking on the line that is supposed to fill the array (Type Mismatch).

 

[Fluff]

You need to give the array a size.
Are you just looping through a single row?

 

[Tim_Excel_]

Fluff,

I've read about that, but I am not sure how to do that since my array size is unknown until the loop stops. I am indeed looping through a single row.

I wonder, can I size the Array from 1 to 100 but populate it with only a couple of values? That would, I think, solve the issue.

 

[Fluff]

try

Redim SuppressArray(1 to LCol)

 

[mikerickson]

I wonder, can I size the Array from 1 to 100 but populate it with only a couple of values? That would, I think, solve the issue.

You could use that approach

Dim SuppressArray() As Variant
Dim c As Range, Pointer As Long

Redim SuppressArray(1 to 100)
Pointer = 0

For Each c In m_wb.Sheets("Componenten").Range(Cells(cell.Row, "N"), Cells(cell.Row, LCol))
    If c.Value <> "" And IsNumeric(Left(m_wb.Sheets("Componenten").Cells(1, c.Column), 1)) = True Then
        Pointer = Pointer + 1
        SuppressArray(Pointer) = Left(m_wb.Sheets("Componenten").Cells(1, c.Column), InStr(1, m_wb.Sheets("Componenten").Cells(1, c.Column), "M") - 1)
       
    End If
Next c

Redim Preserve SuppressArray(1 to Pointer)

 

[Tim_Excel_]

Cheers Fluff, that does what I want.

I wonder, why ReDim? This function isn't known to me. I've only used ReDim and dropped Dim as Variant.

 

[Tim_Excel_]

Thank you for the insight Mike

 

[Fluff]

I wonder, why ReDim? This function isn't known to me. I've only used ReDim and dropped Dim as Variant.

I normally declare all my variables at the top of the sub. Therefore I do not know how big an array needs to be when it's declared. As you can onlyDim as variable once, you use ReDim.
From what you've shown of your code it looks like LCol has a value before the Dim statement, in which case you could simply use Dim SuppressArray (1 to LCol)

 

[Tim_Excel_]

Thanks for that explanation.

I normally don't declare my variables at all which is a terrible practice but so far has yet to cost me.