initializing an array multiple times in vba

Question

I'm working on a VBA code in which I'm inserting Integer values:

    Dim IgnoreCol() As Integer

    For j = 1 To LastCol
        If Cells(1, j).Value = "IGNORE" Then
            ReDim Preserve IgnoreCol(Temp)
            IgnoreCol(Temp) = j
            Temp = Temp + 1
        End If
    Next

After this part of the code, I have in my program an Int array of column numbers - now, in the next loop I would like to approach the array:

For j = 1 To LastCol
    If Not IsInArray(j, IgnoreCol) Then
        DataLine = DataLine + Trim(Cells(Row, j).Value)
    End If
Next j

So now I have 2 questions:

Say that neither of the columns in the sheet had "IGNORE" in their first cell and that my array "IgnoreCol" is empty, and none of the cells were initialized - what condition returns "True" if the array is really empty?
I'm using this code inside another loop - which means, I want to initialize my "IgnoreCol" array at the end of this code before entering it again (by initializing I mean delete all, not just put 0 in all of the cells for instance)

Thank you very much!

1. if ubound(IgnoreCell) > 0, 2. Redim Preserve IgnoreCell(0)? — user2140173
– user2140173, Commented Apr 28, 2014 at 7:55
@mehow UBound(IgnoreCell) returns "TypeMismatch" since none of the cells were initialized — Bramat
– Bramat, Commented Apr 28, 2014 at 8:07
TypeMismatch? that's strange normally it's subscript out of range if the array has not been initialized — user2140173
– user2140173, Commented Apr 28, 2014 at 8:09

Mike Woodhouse · Accepted Answer · 2014-04-28 08:11:24Z

1

This would test for an empty array:

Function ArrayIsEmpty(a) As Boolean
Dim temp
  On Error Resume Next
  temp = LBound(a)
  If Err.Number <> 0 Then ArrayIsEmpty = True
End Function

Use the Erase function to clear the array:

Dim a() As Integer
If ArrayIsEmpty(a) Then Debug.Print "Array starts empty" Else Debug.Print "Array NOT empty???"
Redim a(1 To 100)
If ArrayIsEmpty(a) Then Debug.Print "Array empty" Else Debug.Print "Array NOT empty"
Erase a
If ArrayIsEmpty(a) Then Debug.Print "Array NOW empty" Else Debug.Print "Array still not empty"

But I'd prefer to use a Dictionary object (from the "Microsoft Scripting Runtime", which you can add using "Tools...References" in the VBA editor).

Dim IgnoreCols As New Dictionary

For j = 1 To LastCol
    If Cells(1, j).Value = "IGNORE" Then
        IgnoreCols.Add j, 1
    End If
Next

For j = 1 To LastCol
    If Not IgnoreCols.Exists(j) Then
        DataLine = DataLine + Trim(Cells(Row, j).Value)
    End If
Next j

... or even better, something like this:

Dim IncludeCols As New Dictionary

For j = 1 To LastCol
    If Cells(1, j).Value <> "IGNORE" Then
        IncludeCols.Add j, 1 ' store the cols we *want*
    End If
Next

' now just loop through the list of wanted cols
For j = 0 To IncludeCols.Count
    DataLine = DataLine + Trim(Cells(Row, IncludeCols.Keys(j)).Value)
Next j

answered Apr 28, 2014 at 8:11

Mike Woodhouse

52.3k12 gold badges90 silver badges128 bronze badges

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

2 Comments

Bramat Over a year ago

Thank you very much! it works!! but now I have a different problem - if there's "#N/A" in the row, then the "DataLine = DataLine + Trim(Cells(Row, j).Value)" row returns an error message... do you have any idea how to overcome this?? Thanks again!!!

Mike Woodhouse Over a year ago

Try adding an IsError() test around the offending part. Maybe move the getting of the cell value to its own line, then use IsError to decide whether to use the value or do something appropriate for #NA, then do the append.

Collectives™ on Stack Overflow

initializing an array multiple times in vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related