Having trouble using an array created in a different procedure

[dbostien]

Hi everybody, I hope you can give me some help with using an array created in a separate procedure.

I have an array variable that I declared at the top of a module as:
Public RowNumArray() As Long

In procedure Sub EditResources(), I dynamically fill the array with the row numbers of the records that are edited. This is working fine and the array has the correct row numbers.

Later, in a different procedure, I want to do something with those edited rows, which is why I need the row number array but, while the array is still there, it seems to be empty. In the locals window, RowNumArray is listed with the type of Long(), but the value column is completely blank. There is no plus sign in front of the variable name, and no matter what subscript I use, I get a "subscript out of range error.

Can anybody help with this frustrating problem?

Thanks much.

 

[circledchicken]

Hi,

Can you post more of your code? Hopefully just a small sample replicating your specific problem?

 

[dbostien]

Sure, here is the code that creates /populates the array:

'Create array of filtered row numbers
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Resource Tracking").Range("A1:A" & LastRow)
On Error Resume Next
Set Rng = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

If Not Rng Is Nothing Then
For Each cell In Rng
Cnt = Cnt + 1
ReDim Preserve RowNumArray(1 To Cnt)
RowNumArray(Cnt) = cell.Row
Next cell
End If

The first row of var RowNumArray in the locals window has:
RowNumArray(1) 7 Long

As I said above, by the time I go to use this array in a different procedure (but same module), the var is still there but it is empty.

 

[circledchicken]

It should work ok if you are not doing something else before running the second procedure e.g. editing the module which would reset the variable.
For example, the procedure 'UseHere' should work, after 'EditResources' has been run:

Public RowNumArray() As Long


Sub EditResources()
    
    'Create array of filtered row numbers
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    With Sheets("Resource Tracking").Range("A1:A" & LastRow)
        On Error Resume Next
        Set Rng = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    End With
    
    If Not Rng Is Nothing Then
        For Each cell In Rng
            Cnt = Cnt + 1
            ReDim Preserve RowNumArray(1 To Cnt)
            RowNumArray(Cnt) = cell.Row
        Next cell
    End If


End Sub


Sub UseHere()

    MsgBox RowNumArray(1)

End Sub

An alternative option might be to declare the array variable locally and pass it to your EditResources function to get populated.

The following resources might be useful:
Scope of variables in Visual Basic for Applications
Passing And Returning Arrays With Functions

 

[dbostien]

There is something going on between the two procedures. The first procedure, EditResources, copies selected records from a sacrosanct Resource Tracking sheet and pastes them into a separate sheet for editing. When the user has finished editing records, he then clicks a button which launches the second procedure, ApplyEditsToResources. This procedure replaces the records in the Resource Tracking sheet with the edited versions. That's where the array variable is supposed to come in. There must be a problem with this, eh?