Having trouble using an array created in a different procedure

dbostien

New Member
Joined
Jan 9, 2014
Messages
6
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi,

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

dbostien

New Member
Joined
Jan 9, 2014
Messages
6
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.
 
Last edited:

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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:

Code:
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
 
Last edited:

dbostien

New Member
Joined
Jan 9, 2014
Messages
6
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top