Below is the extract of some code I am writing that attempts to compare a range at the beginning of a process (captured in a global array in a sheet activate event) with the same range when the sheet is deactivated.
Here is the activate code:
Private Sub Worksheet_Activate()
garr_Agency = Array(Range("rng_Lst_Agencies").Value2) 'rng_Lst_Agencies is a 1D column.
End Sub
And here is the deactivate:
Private Sub Worksheet_Deactivate()
Dim arr_Agency() As Variant
Dim rng_Agency As Range
Dim lng_Agencies As Long
Dim lng_i As Long
Set rng_Agency = Range("rng_Lst_Agencies")
arr_Agency = Array(rng_Agency.Value2)
lng_Agencies = rng_Agency.Cells.Count
lng_i = 1
Do Until lng_i = lng_Agencies + 1
If arr_Agency(0)(lng_i, 1) <> garr_Agency(0)(lng_i, 1) Then
*rest of the code*
I would have thought I could just use:
If arr_Agency(lng_i, 1) <> garr_Agency(lng_i, 1) Then
but I get a "Subscript out of range" error.
Why do I need to add the extra "(0)"? From the locals window it looks as if I have created an array within an array somewhere along the line but I don't see how.
Many thanks.
The reason you are getting a nested array is because of this line:
arr_Agency = Array(rng_Agency.Value2)
Do this instead:
arr_Agency = rng_Agency.Value2
That will create a one dimensional array, because when you assign to a variant its automatically treated as being an array.