Appending an array to another after each iteration

How can I append an array to another in Excel VBA?

I have a loop, and in each iteration, a new array of variant type is created. The output at the end of the loop should be all the arrays appended together. The number of columns in each array are always the same.


For Each cell In Worksheets("Sheet2").Range(MRG)
    'An array is generated at each iteration. Append generated array to the already existing array, which is empty at iteration 1.  Lowerbound=0 for both dimension, upper bound varies for 1st dimension (rows) of array at each iteration. No of columns are always same for each new array.


next


Appreciate any help.

Thanks.

   
momer123Asked:
Who is Participating?
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Why not use your SQL statement as the basis, but initially create a temporary table that you append the matching records to rather than creating an array?  At the end of the loop you can read sequentially through this table processing as you go, then drop the table.

BFN,

fp.
0
 
[ fanpages ]IT Services ConsultantCommented:
You could dimension one huge array (to the extent of all the UBound()'s of each individual array) & then use the CopyMemory (RTLMoveMemory) API routine on each in turn with an offset to a pointer of the next element in the resultant array.

[ Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpTo As Any, lpFrom As Any, ByVal lLen As Long) ]

But... why the need to create a new array on every iteration.

Could you not just create one array before the loop begins, and use the ReDim Preserve NameOfArray(...) syntax?

BFN,

fp.
0
 
[ fanpages ]IT Services ConsultantCommented:
PS. You may find suggestions from other Experts if you place a "pointer" question with a low (say, 20) point allocation in the MS-Excel "Topic Area" with a direct link to this question.

[ http://www.experts-exchange.com/Applications/MS_Office/Excel/ ]

BFN,

fp.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
momer123Author Commented:
Thanks for your reply, fp.

In each iteration, I am running a SQL query (using the cell value at that iteration) and returning a recordset which I copy to an array using recordset.getrows. The name of the array is the same but its contents are different in each loop.

My idea was to have one final array having the results of all executed queries and display it as the output of user defined function in spreadsheet. If I change the cell values (which will change the SQL query), the output variant array will change.

Thanks.






0
 
momer123Author Commented:
I would have liked to change my SQL query so that I dont have to do much programming in VB.

But unfortunately I am using SPSS 32-bit driver to retrieve values from SPSS into excel using ADO, and I am getting error such as "-2147217887 : ODBC driver does not support the requested properties.". May be this ODBC connection requires a different SQL syntax. I will spend some time trying to make some SQL queries work.

Thanks for your suggestion.





0
 
[ fanpages ]IT Services ConsultantCommented:
I wish I could help further, but I'm sorry I do not know what SPSS is.

Perhaps if you post your SQL we could advise on whether anything obvious looks bespoke towards a specific ODBC driver; i.e. is not ANSI SQL compliant.

BFN,

fp.
0
 
momer123Author Commented:
I found that spss driver doesnt support any queries using joins, or anything more advanced than "select ... where.. group by."

Thanks for the ideas you gave, I am using a temporary table and append matching records to it, and at the end retrieve all records.

0
 
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Glad I pointed you towards a more efficient method.

Happy codin'.

BFN,

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