Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Arrays - naming & retrieving from named arrays
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Arrays - naming & retrieving from named arrays
Resolved · Urgent Priority · Version 2007
Reply Mon 14 Feb 2011, 17:43Delegate Paul said...
Paul has attended:
Excel VBA Advanced course
Arrays - naming & retrieving from named arrays
Hi,
I am trying to create an array which I then save as a named array so that I can save a version of the file it was created from without the very large data worksheets but still retain the info in the code from which it can be accessed.
I have created the array & written back from it with no problems, but cannot get the name bit to work - I just get an 'application or object defined error.'
My code so far is:
Dim byT3(1 To 5, 1 To 439, 1 To 143) As Variant
Dim intRowCount As Long
For intRowCount = 3 To Sheets("data2").Range("a1")
byT3(1, Cells(intRowCount, 2), Cells(intRowCount, 3)) = Sheets("data2").Cells(intRowCount, 8)
Next intRowCount
Names.Add Name:="T3data", RefersTo:=byT3
I can't see what is wrong with this compared to the example in the course handout but it isn't working! (it fails on the last line)
Also, once I get it to work, how do I call back the data using the name? Is it just the same except the data remains in the array memory even after the file is closed & re-opened?
Thanks for any help
cheers, Paul
For upcoming training course dates see: Pricing & availability
RE: Arrays - naming & retrieving from named arrays
Hi Paul
Thanks for your question
Could you specify which line the code breaks on?
Thanks
Stephen
Reply Thu 17 Feb 2011, 12:07Delegate Paul said...
RE: Arrays - naming & retrieving from named arrays
Hi Stephen,
it is on:
Names.Add Name:="T3data", RefersTo:=byT3
If I remove this & just don't try naming the array then everything works fine.
Thanks,
Paul
Reply Mon 28 Feb 2011, 17:18Delegate Paul said...
RE: Arrays - naming & retrieving from named arrays
Hi Stephen,
it is on:
Names.Add Name:="T3data", RefersTo:=byT3
If I remove this & just don't try naming the array then everything works fine.
If I leave it in it crashes on this line which means when I re-open the file after closing it, the array is blank & needs re-populated.
Does this answer your question?
Is what I am trying to do possible?
Thanks,
Paul
Reply Sun 27 Mar 2011, 19:49Delegate Paul said...
RE: Arrays - naming & retrieving from named arrays
Hi Stephen,
it is on:
Names.Add Name:="T3data", RefersTo:=byT3
If I remove this & just don't try naming the array then everything works fine.
If I leave it in it crashes on this line which means when I re-open the file after closing it, the array is blank & needs re-populated.
Does this answer your question?
Is what I am trying to do possible?
Thanks,
Paul
RE: Arrays - naming & retrieving from named arrays
Hi Paul,
Sorry for the delay in a reponse to your last post?
Is this question still active?
If it isn't or you have resolved it, please mark the question as resolved.
Regards
Simon
Reply Wed 20 Apr 2011, 17:14Delegate Paul said...
RE: Arrays - naming & retrieving from named arrays
Hi Simon,
no, I am still looking for an answer to this. Any help would be much appreciated.
Cheers
Paul
RE: Arrays - naming & retrieving from named arrays
Hi Paul, thanks for your query and many apologies for the delay. The problem with your code you may already have worked out but for the record you have already named your array when you declared it. Names.Add is used to name worksheet ranges, not arrays held in memory. If you want the array to be named "T3data" just declare it as such in the declarations.
You are going to lose the data in the array if you close the file. The variables are destroyed at the end of the session, so your data still needs to be kept stored somewhere, either on the worksheet, a CSV file or in a database.
Finally, you appear to have a three dimensional array. To access the data you will need to use UBOUND to determine the upper limit of each dimension, loop through each dimension and pass out each value back to the worksheet. There is an example of this in the code we wrote on the course.
I hope this helps, let me know how you get on.
Anthony
Reply Thu 21 Apr 2011, 16:29Delegate Paul said...
RE: Arrays - naming & retrieving from named arrays
Hi Anthony,
thanks very much for this response. Just to be clear, is there no way to keep the data in the array after closing & re-opening the file? I thought this is what saving the array as a named range would allow me to do?
Thanks,
Paul
RE: Arrays - naming & retrieving from named arrays
Hi Paul, the array is held in memory and it collapses on file closure. If you wrote the contents of the array onto a worksheet, then saved the file you would be able to use that information down the line but you wouldn't need to name its range, you could simply lift the whole contiguous range back into the array when you return to it. It is a standard practice for any developer using arrays (to calculate, or hold incoming information from data warehouses) that you have to output it somewhere or risk losing the whole thing on file closure. The memory allocations are lost once the file closes.
Hope this helps,
Anthony
|