Dictionary object and multidimensional array

Why not just add the arrays in two steps? Depending on how you want to add the data, transform the arrays with Worksheetfunction.Transpose(). You may need to Resize the Range.

If you need help with an example, post back.

Your question is way beyond my experience level but I did find this:

http://www.mssqltips.com/tip.asp?tip=1637

http://docs.python.org/library/csv.html#csv-examples
(scroll down & see: class csv.DictReader & class csv.DictWriter)

hope its useful

Hi Kenneth,

You are right; I can do it with more than one step. I already wrote the code to combine the two 1D arrays into a 2D array. But I was hoping that someone knew of a way to extract a 2D array directly from the dictionary object.

Nbrcrunch, thanks for your response. I think we are talking about different dictionary objects. I am using the Dictionary object from the Microsoft Scripting Runtime library.

What I was thinking was that you could write the two arrays to 2 columns in a New sheet. You can then get the data as a 2 dimensional array. The last step would be to hide the sheet or delete it. I call this the scratchpad method.

I would have to run some time tests to determine if or when the scatchpad method becomes more efficient than the iterative array method and AddItem.

A 2nd scratchpad method becomes even more attractive since you are using a Userform anyway. You can add a spreadsheet control and poke the data into it in two steps as in the first method. You can then get your List for the Listbox from that Spreadsheet control's UsedRange. Like the 1st scratchpad method, you can add or unhide the Spreadsheet control to do the scratch work and then delete or hide that control.

Kenneth,not sure if it is necessary to use the Excel scratchpad method. Here is a snipet of the code that I am using.
<code>
myArray1 = myDict.Keys
myArray2 = myDict.Items
ReDim myArray(LBound(myArray1) To UBound(myArray1), 0 To 1)
For i = LBound(myArray1) To UBound(myArray1)
myArray(i, 0) = myArray1(i)
myArray(i, 1) = myArray2(i)
Next i
ListBox5.List() = myArray
</code>
As you can see I am creating two 1D arrays (myArray1 and myArray2) from the Dictionary object (myDict) and combining them into one 2D array (myArray). Then I populate the ListBox with myArray.

I am looking for a way to create the 2D array directly from the dictionary object and avoid having to create the two 1D arrays.

That is the way that I would do it too. Combining as you have is probably your best bet. Array iterations are usually pretty fast.

Of course if you have a large amount of data, the scratchpad method might be a bit faster. As I said, I just don't know without testing. If I get time tomorrow, I may work up a short example to test speed.

Hi Al, Kenneth

This worked for me:

Thanks for the idea pgc! Good to know for sure.

That method was not shown in the help. Occasionally, I find commands that can be used in ways that the help does not detail. Commands are usually documented well.

I would call that the Splice method. If we had Slice method for arrays, it would make some things easier. It is one area where I like the array functions in PerfectScript better than other programming languages.

Hi Al, Kenneth

Click to expand...

I'm sorry nbrcrunch, I didn't mean to leave you out, I simply didn't see your post when I read the thread.

I would call that the Splice method. If we had Slice method for arrays, it would make some things easier. It is one area where I like the array functions in PerfectScript better than other programming languages.

Click to expand...

Kenneth

You don't have a vba "slice" function, but if it's a 2 column array, like in the case we have in this thread you can use the worksheet function Index()

Ex.


t and u get the third column of the array v:

t is a 2 dimensions array (1 to 3,1 to 1)
t(1,1) = Empty
t(2,1) = 8
t(3,1) = 6

u is a 1 dimension array
u(1) = Empty
u(2) = 8
u(3) = 6

But I agree with you, vba lacks functions to manipulate arrays directly.

One other is the ability to redimension the array, you can only redimension the last dimension. Again if it's an array with 2 dimensions you can use the worksheet functions.

Ex., you can redimension a dynamic array (1 to 3, 1 to 4) to (1 to 3, 1 to 6), but you cannot redimension directly an array (1 to 3, 1 to 4) to (1 to 5, 1 to 4) as ReDim only lets you redimension the last dimension. You can use the worksheet function Transpose() to do it:


This way, although not directly you can redimension the first dimension. You probably knew this already, but I'm taking this opportunity to write it all in the same thread to use it as future reference.

Cheers