Solved

Working with an Array stored in the Excel Defined Names Collection

Posted on 2011-03-15
13
193 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

12 Experts available now in Live!

Get 1:1 Help Now