Solved

Working with an Array stored in the Excel Defined Names Collection

Posted on 2011-03-15
13
202 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:dsimcox
  • 6
  • 5
  • 2
13 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 167 total points
ID: 35139808
change
arDefault = Evaluate("arDefaultCRMReport").RefersTo

to

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

Thomas
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 333 total points
ID: 35139860
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139869
Sorry, Thomas - should have refreshed.
Rory
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35139902
none taken.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139918
Confused, would we? :)
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35139963
I was answering your subtext.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139980
And I, yours.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35140002
un partout, la balle au centre.

T
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35140036
I am often accused of being touché with madness.

Have we scared the Asker away yet, do you think? ;)
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35140196
working on it.
0
 

Author Comment

by:dsimcox
ID: 35140850
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 333 total points
ID: 35141021
Your array is zero based but you are sizing it to the recordcount. You should size it to recordcount-1.
0
 

Author Comment

by:dsimcox
ID: 35147551
Of course . . . Thanks for pointing this out. Good work!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now