Dictionary of Arrays to Range
I have a Dictionary filled with 1 dimensional Arrays which I want to put on a Worksheet entirely
without looping and by accessing the Worksheet just once.
I tried an ArrayList filled with 1 dimensional Arrays which did the job, but using an ArrayList (not default in Excel)
meant I have to install additional Windows features to all PC's where I want to use my macro, which I don't want to do.
How can do this without said ArrayList? Is a Dictionary indeed my best option?
Many thanks in advance!
Hi, what do you mean with "additional Windows features" ?
Can you explain it a little more. Since you mention looping does this mean that your are using macros (VBA) or other method with array formula's and so.
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
ArrayLists are not default in Excel, you need to install a few Windows features to get them to work
Please view this link:
https://www.automateexcel.com/vba/arraylist/
In summary this means you need to add a specific Reference in Excel and also add .NET 3.5 (if it's not already installed):
https://stackoverflow.com/questions/...ating-an-array
I installed both features and finally got it to work. But when I tried using my Macro (with an ArrayList) on a different PC,
I apparently needed to install all above features on this different PC as well to get it all to work.
I don't want to have to install these Windows features on each PC on which my colleagues will use my Macro, that's all ;-)
Could you attach the file you use?
You see, depending on how you do it you only need one file with all the tools references installed in the file, but without know how you're doing it...
I write VBA code as a free-lancer and use many extra functionality but in that manner that the user does not have to do anything on his/her system to have it run.
You can show the links and everything but within the VBA project it can all be solved and taken care of.
I'm just curious to what you are running in to
Rezzy777
The main difference between Dictionary and ArrayList is that
1) Dictionary has Key and item, whereas ArrayList only have item
2) Dictionary has case insensitive compare option and accept mixed data types, whereas ArrayList has no case insensitive compare and no mixed data types.
3) Dictionary has no sort method whereas ArrayList has.
So, if you want to Sort with the Dictionary, you can do it anyhow depends on how your code does.
@Keebellah
I'm sorry, it's for the company I'm working for so attaching company files won't be appreciated ;-)
I'll try to explain a bit better.
I have a range of 2D data which I want to paste in another worksheet
without have to access both worksheets more than once.
This shouldn't be too difficult, but here's the thing: I need to change the order of the columns before pasting.
So I figured I'd follow these steps:
1. Make a 2D Array of the entire Range
2. Go through each column inside the 2D Array and make a String Array of each Column
3. Convert each String Array into a regular 1D Array
4. Add each new 'Column Array' to an ArrayList in the right (manual) order
5. Paste the ArrayList (filled with these 'Column Arrays') via Transpose into the other worksheet
This actually works.
But then I'll have to install said additional Windows features (mscorlib + .NET 3.5) on each PC, which I don't want.
So now I'm trying to replace the ArrayList with a regular Dictionary, but this doesn't work...
Last edited by Rezzy777; 05-10-2021 at 05:35 AM.
Yes and I do solve everything with macro code without the external references, see what you mean. I suggest all bu macros including sorting, extra code but it works
Hello Jindon, I actually don't need to sort.Originally Posted by jindon
I just need an option to convert my Dictionary / Array / Collection into a regular 2D Array which I can paste into a Worksheet ;-)
@Keebellah
How can I embed this mscorlib file and .NET 3.5 reference into my Macro?
Do you have a link which explains how to do this?
Or perhaps a better way to achieve what I'm trying to get? :-D
Then no better reason to use ArrayList.
Yeah but then I need to install additional Windows features on each PC I want to use this Macro on,
which is also no option...
I'm saying "Dictionary" is the better option.
Ah, I'm sorrymiscomm
Okay, how can I paste all 1D Arrays inside my Dictionary into a Worksheet at once?
How are you storing the data in Dictionary?
I am going through all columns inside a 2D Array I made of a Range on my Worksheet.
Each column is then stored as a String Array inside my Dictionary in the right (manual) order.
So you store as a key?
e.g
Please Login or Register to view this content.
Yeah something like that. I'm first adding all String Arrays to the Dictionary (using a simple number as Key)
and then I hope to paste the entire Dictionary into another Worksheet.
I am adding each 1D Array to an 'Item'. Or should I use 'Key' instead?
I'm not very familiar with Dictionaries...
Last edited by Rezzy777; 05-10-2021 at 05:57 AM.
If you want to output keys and items then
Please Login or Register to view this content.
No I only want to output Items.
But when I try this, I get the error 'Types don't match'.
Maybe this is because I put String Arrays inside the Dictionary instead of just Strings?
2013...
Transpose function with array has a limitation.
Length of any one element in an array exceeds 255 characters, it fails with the error.
Why not prepare 2d array for output and store the data in it and output at once.
Ah.... that explains a lot, thank you so much!
Is it possible to convert my Dictionary (filled with String Arrays) to a 2D Array at once?
I'd prefer not to use loops...
Or is there any other way to copy a Range to another Worksheet and switch some columns in VBA first?
You're in good hands, I'm out
Thanks anyway! ;-)You're in good hands, I'm out
Funny though, everything did seem to work when I used an ArrayList...
As I suggested
Please Login or Register to view this content.
Re: Dictionary of Arrays to Range
Hi !
So any Dictionary is useless, using an advanced filter seems the best way …
Then you will need to post your code.
Oh this looks interesting! Never tried though, so I have something to do next! Thanks man!
Post deleted.
Last edited by kvsrinivasamurthy; 05-10-2021 at 06:59 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Given these steps, I can't see why you can't just use a 2D array as was suggested previously, or even just an array of arrays.
Rory
But that means I still have to use loops to fill the 2D Array, right?
I think I've got the Autofilter working...
Last edited by Rezzy777; 05-10-2021 at 08:26 AM.
Yes but you appear to be looping anyway. Looping is a perfectly normal thing to do in code - do you have an actual performance issue with it, or are you just assuming it will be slow?
I don't, but if there's a single operation option to do the same as a loop, I prefer the single operation ;-)
I am still learning VBA and I'm always trying to use the most efficient code possible.
Ah, you're of the "shorter code is better code" persuasion. Not always true.
the link might help, but I don't suggest you to use without basic knowldge.
How to filter quickly 2 mutidimensional array?
P.S.
The sorter code doesn't mean better.
You are probably right again ;-)Ah, you're of the "shorter code is better code" persuasion. Not always true.
It's just that I often have lots of code so I love it when I can replace a big block of code with just a single line - if only for readability reasons ;-)
Thanks Jindon, will look into that!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks