The following code is connecting to SQL and running a SQL queery. I works just fine. How do I store the data generated in the SQL queery to an Array in VBA?
The code above is working perfectly and the values from SQL queery is being printed in A1 and B1. Now suppose I don't want to print it in my sheets but I want to store it in an VBA array. What do I do?
Please Login or Register to view this content.
Last edited by kbdb; 07-18-2017 at 08:23 AM. Reason: Solved
Hi
Please use code tags when posting code.
You can use GetRows thus
Note that the array is transposed from the way CopyFromRecordset produces it.Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
It acutally doesn't solve my problem. I don't think TheArray is saved as an array. Later I try to refer to TheArray(2) but it wont be recognizedOriginally Posted by xlnitwit
Hi
Please use code tags when posting code.
You can use GetRows thus
Note that the array is transposed from the way CopyFromRecordset produces it.Please Login or Register to view this content.
Then I add the following
Then there is a compile error: Can't assign to array. TheArray is clearly not am array.Please Login or Register to view this content.
Last edited by kbdb; 07-18-2017 at 08:34 AM.
It will be a 2 dimensional array (If you look in the locals window I'm pretty sure you'll see an array) so you need to supply both dimensions when extracting data from it.
How can extract data then? Suppose I want to create a new variable wich is the mean. How do I calculate that by reffering to data from TheArray?
Last edited by kbdb; 07-18-2017 at 08:44 AM.
It is an array, you just apparently aren't familiar with VBA arrays. You cannot assign one array to another unless the receiving array is dynamic and of matching type.Then I add the following
Then there is a compile error: Can't assign to array. TheArray is clearly not am array.Please Login or Register to view this content.
To extract a value from a 2 dimensional array, you must, as I mentioned, provide an index for both dimensions- e.g.
Please Login or Register to view this content.
How would you do it with a one-dimensional array?
If posting code please use code tags, see here.
msgbox theArray(1, 1) is failing. It says: Subscript out of range.It is an array, you just apparently aren't familiar with VBA arrays. You cannot assign one array to another unless the receiving array is dynamic and of matching type.
To extract a value from a 2 dimensional array, you must, as I mentioned, provide an index for both dimensions- e.g.
Please Login or Register to view this content.
Do you know how to use the Locals window in the VB editor? It will tell you the type, size and shape of the array.
I just googled it so I do now. It says TheArray is of type Variant. This webpage says the same: https://msdn.microsoft.com/en-us/lib.../ff194427.aspx because we have used GetRows
Yes, and what size is it?
kb, run your code with a break point set after the array has been populated. Then view the 'locals' window, scroll down to the array and click the + sign, it'll then show you all entries in the array with their address in the array. You'll also find that a variant array indexes itself from 0, not 1. Therefore, if you're using a variant array as a one dimensional array 1,1 won't exist. 1,0 might....or possibly 0,1. See the locals window to find out.
Under type it says: Variant/Variant(0 to 1, 0 to 0)
If it's (0 to 1, 0 to 0) then you can't use (2, 2) as the indices. You only have:
(0, 0)
(1, 0)
available.
EDIT: disregard, posted in error
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks