Working with an Array stored in the Excel Defined Names Collection

My code creates a 2-D array that I store in the workbook names collection like this:  

Public Sub Save_Default_CV_Report()
Dim arSaveDefault()

Call GetCVRecordset("PDP")     '   RETURNS rsPDP
rsPDP.MoveFirst
rsPDP.Filter = "REPORT = 'Y'"
n = 0

ReDim arSaveDefault(rsPDP.RecordCount, 1)
Do Until rsPDP.EOF
    arSaveDefault(n, 0) = Trim(rsPDP("PROJ_NAME")) & "|" & Trim(rsPDP("MILE_NAME"))
    arSaveDefault(n, 1) = Trim(rsPDP("REPORT"))
    n = n + 1
    rsPDP.MoveNext
Loop
    
ActiveWorkbook.Names.Add Name:="arDefaultCVReport", RefersTo:=arSaveDefault

    
End Sub

Open in new window


I don't know how to  loop thru the array elements to find a value. I've tried using indices like this [myArray](0, 1) using the brackets for the Evaluate Method - that doesn't work. I've also tried assigning the array to a new array variable like this myNewArray = ActiveWorkbook.Evaluate("myArray"), but that doesn't work either. I can't figure out how to address the array and it's elements when I retrieve it from the Names collection. Here's the code I have tried. Can you help?

 
Public Sub PostDefaults()
Dim Projects As Range, c As Range, n As Long, concat As String, arDefault()

'   IDENTIFY DEFAULT PROJECTS WITH A "Y" IN THE REPORT COLUMN
Set Projects = ActiveSheet.Range(Cells(2, 1), Cells(GetLastRow(ActiveSheet, 1), 1))
arDefault = Evaluate("arDefaultCRMReport").RefersTo

For Each c In Projects
    concat = c.Offset(0, 1) & "|" & c.Offset(0, 2)
    
    For n = 0 To UBound([arSaveDefault], 2)
        If arDefault(n, 0) = concat Then
            c.Offset(0, 7).value = arDefault(n, 1)
            Exit For
        End If
    Next
Next

End Sub

Open in new window

dsimcoxAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
You can use:
arDefault = Evaluate("arDefaultCRMReport")

Open in new window

or:
arDefault = [arDefaultCRMReport]

Open in new window

then:
    For n = 0 To UBound(arDefault, 2) 

Open in new window

0
 
nutschConnect With a Mentor Commented:
change
arDefault = Evaluate("arDefaultCRMReport").RefersTo

to

arDefault = Evaluate(Names("arDefaultCRMReport").RefersTo)

Thomas
0
 
Rory ArchibaldCommented:
Sorry, Thomas - should have refreshed.
Rory
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
nutschCommented:
none taken.
0
 
Rory ArchibaldCommented:
Confused, would we? :)
0
 
nutschCommented:
I was answering your subtext.
0
 
Rory ArchibaldCommented:
And I, yours.
0
 
nutschCommented:
un partout, la balle au centre.

T
0
 
Rory ArchibaldCommented:
I am often accused of being touché with madness.

Have we scared the Asker away yet, do you think? ;)
0
 
nutschCommented:
working on it.
0
 
dsimcoxAuthor Commented:
OK guys, back to work!

Both of your solutions worked - thanks for the prompt replies -  but there is a problem.

There are 38 records in the recordset rsPDP (I've verified by counting them on the worksheet)
In the following code, the UBound of arSaveDefault is 38. So far, so good.

'   THIS CODE SAVES AN ARRAY OF THE ITEMS IN THE DEFAULT CRM PDP REPORT WHERE REPORT = 'Y'
Public Sub Save_Default_CRM_Report()
Dim arSaveDefault(), n As Long

Call GetCRMRecordset("PDP")     '   RETURNS rsPDP
rsPDP.MoveFirst
rsPDP.Filter = "REPORT = 'Y'"
n = 0

ReDim arSaveDefault(rsPDP.RecordCount, 1)
Do Until rsPDP.EOF
    arSaveDefault(n, 0) = Trim(rsPDP("PROJ_NAME")) & "|" & Trim(rsPDP("MILE_NAME"))
    arSaveDefault(n, 1) = Trim(rsPDP("REPORT"))
    n = n + 1
    rsPDP.MoveNext
Loop
    
ActiveWorkbook.Names.Add Name:="arDefaultCRMReport", RefersTo:=arSaveDefault

    
End Sub

Open in new window



But when I retrieve the array from Names and assign it to the variant array arDefault in line 6, there are 39 records in it. The last record arDefault(39, 1) is "Error 2042". That's "N/A". Something seems to be happening to that array when it is saved in the Names collection and then retrieved using this code that you helped me with. It's not the same as it was going in. Any ideas?

 
Public Sub PostDefaults()
Dim Projects As Range, c As Range, n As Long, concat As String, arDefault()

'   IDENTIFY DEFAULT PROJECTS WITH A "Y" IN THE REPORT COLUMN
Set Projects = ActiveSheet.Range(Cells(2, 1), Cells(GetLastRow(ActiveSheet, 1), 1))
arDefault = Evaluate("arDefaultCRMReport")

For Each c In Projects
    concat = c.Offset(0, 1) & "|" & c.Offset(0, 2)
    For n = 1 To UBound(arDefault, 1)
        If arDefault(n, 1) = concat Then
            c.Offset(0, 7).value = arDefault(n, 2)
            Exit For
        End If
    Next
Next

End Sub

Open in new window

0
 
Rory ArchibaldConnect With a Mentor Commented:
Your array is zero based but you are sizing it to the recordcount. You should size it to recordcount-1.
0
 
dsimcoxAuthor Commented:
Of course . . . Thanks for pointing this out. Good work!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.