current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

this is a general VBA Array issue, it is not for MS Office apps (no tables involved).

I'm looking to find out how to create multiple one-dimension arrays at runtime (maybe even public ones), using data from a .csv file. I can explain. This is an example of how the csv file would look:

------- CSV FILE ----------------------------
Colors,white,red,blue,green (... and so on)
Animals,cat,dog,wolf,bear (...and so on)
Food,cake,bread,garlic (...and so on)

...and so on, more rows

The opening part is solved, even the part where each row is assigned to a temporary variable, and more - the row is split into values and assigned to a temporary array.

So, I have:

  • tempArray1, containing ("Colors", "white", "red" ...etc)
  • tempArray2, containing ("Animals", "cat", "dog" ...etc)
  • ...

The goal is to create (or to address to) an (existing) array NAMED after the first value of each row and then assign the rest of the values from row to that array.

Please do not ask me why am I not using a multi-dimensional array. I have my reasons.

A similar question related to this case is: if I already have a one-dimension public array, defined, named and populated - let's say it is Colors() - how can I address to it using the value "Colors"? Not only to address, but also to erase, redim or change values in it?

When I say "Colors" I mean a string value, not 'hard-coded' Colors() into the sub or function.

share|improve this question

1 Answer 1

up vote 0 down vote

With respect to your "a similar question related to this case", you can do the following:

  1. Create a public class module containing your array Colors()
  2. Then, add a "Microsoft Script Control" ActiveX control (possibly to your form), and keep it hidden
  3. Add code (as string) dynamically to your ScriptControl, and execute it. Now, if this code contains (as a string), say " Colors(1)="red" " , then it will actually modify the Colors array in your class-module.

Note: However, there's a catch. Since it is a class module, and not a normal module, it will only modify the object created inside the script-control. So, you might have to do all the rest of the coding too in that script-control (by dynamically adding code to it and executing it), otherwise, all changes would be lost as the scope of that object would be limited to that code contained inside the script-control

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.