Solved

Working with an Array stored in the Excel Defined Names Collection

Posted on 2011-03-15
13
219 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 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