• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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.

   
0
momer123
Asked:
momer123
  • 5
  • 3
1 Solution
 
[ 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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
[ fanpages ]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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now