VBA Array - addressing to it by its name (CorelDraw)

[Leo1965]

This is a general VBA Array issue, it is not specially for MS Office apps (no tables are 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.



As a supplemental example, I say: given a textarea on a form, when writing in it "Colors" and pressing a button, the routine should know it is about Colors() array and act accordingly (without using "if... Then" or Case... ).

[Bob Phillips]

Create a class called Colors and store your values in there.

[SamT]

Public Type ArrayName
aName As String
TheArray As Variant
EndType

Alternately:

Dim MyArrays as Dictionary

For Each Row
MyArrays.Add Cells(1), somearray
Next Row

'To access
Y = MyArrays(aName)

[Leo1965]

I had no success with Type or Dictionary (dictionary seems to be complicated in CorelDraw VBA, if not entirely missing).
When I say no succes I'm reffering to the possibility of addressing to an array by it's name.

I'll try to explain here step by step.
I have:
Public Color() as Variant
Public Animal() as Variant
Public Food() as Variant
... and a few more like this, in a Module

Then I have a form and in its code:
- I open a csv file
- get all data into MyData
- I split into rows (stringData() = Split(MyData, vbCrLf)
- using the Ubound of stringData I go thru a for... next and split each string

For theRow = 0 To Ubound(stringData) - 1
rowToSplit = stringData(theRow)
tempRow() = Split(rowToSplit, "|") ("|" is value separator, I do not use ",")


And now comes the problem:
- the first value of rowToSplit, and - as a result, the value of tempRow(0) - is the value I want to use to address to an array.

Something like this:
theNameOfTheArray = tempRow(0)
and then the usual, to take the rest of the values

For x = 1 to Ubound(tempRow)
theNameOfTheArray(x-1) = tempArray(x)
Next x

Well, it doesn't work that way...
The "machine" doesn't take the value of theNameOfTheArray as being the array with that name.

I'll appreciate a lot any help in the above lines.

[SamT]

Well, it doesn't work that way...

You are correct. Variables don't have a Name property

theNameOfTheArray = tempRow(0)

is trying to set the variable name to String Type.

Try something like this:

Dim RangeArray As Variant

Redim RangeArray(Ubound(StringData))

'And I think
For theRow = 0 To Ubound(stringData) - 1
    RangeArray(theRow) = Split(stringData(theRow), "|")

I'm not the best on arrays, but if you can get snb to chime in, he is our expert on them.

[RICVB]

I had no success with Type or Dictionary (dictionary seems to be complicated in CorelDraw VBA, if not entirely missing).
When I say no succes I'm reffering to the possibility of addressing to an array by it's name.

I'll try to explain here step by step.
I have:
Public Color() as Variant
Public Animal() as Variant
Public Food() as Variant
... and a few more like this, in a Module

Then I have a form and in its code:
- I open a csv file
- get all data into MyData
- I split into rows (stringData() = Split(MyData, vbCrLf)
- using the Ubound of stringData I go thru a for... next and split each string

For theRow = 0 To Ubound(stringData) - 1
rowToSplit = stringData(theRow)
tempRow() = Split(rowToSplit, "|") ("|" is value separator, I do not use ",")


And now comes the problem:
- the first value of rowToSplit, and - as a result, the value of tempRow(0) - is the value I want to use to address to an array.

Something like this:
theNameOfTheArray = tempRow(0)
and then the usual, to take the rest of the values

For x = 1 to Ubound(tempRow)
theNameOfTheArray(x-1) = tempArray(x)
Next x

Well, it doesn't work that way...
The "machine" doesn't take the value of theNameOfTheArray as being the array with that name.

I'll appreciate a lot any help in the above lines.

As to my knowledge there's no real way to act exactly as you'd like to.
You could obtain something similar using named ranges, through writing your arrays values in (temporary?) sheet cells. like follows

Sub ArrayName_RICVB()
Dim x As Integer
Dim TempSht As Worksheet
Dim theRow As Long, ValuesArrayDim As Long
Dim rowToSplit As Variant
Dim stringData() As Variant, tempRow() As String
Dim theNameOfTheArray As String
Dim MyRange As Range
Set TempSht = ThisWorkbook.Worksheets.Add
'....
For theRow = 0 To UBound(stringData) - 1
    rowToSplit = stringData(theRow)
    tempRow() = Split(rowToSplit, "|") ' ("|" is value separator, I do not use ",")
    theNameOfTheArray = tempRow(0)
   
   ' my proposal
    ValuesArrayDim = UBound(tempRow) - LBound(tempRow)
    With TempSht.Cells(1, 1).Offset(0, theRow) ' store in a specific column the values of every stringData row
        .Resize(ValuesArrayDim).Name = theNameOfTheArray ' create a range named after stringData row first value
        For x = LBound(tempRow) + 1 To UBound(tempRow) ' populate named range with stringData row values following the first one
            .Offset(x - 1) = tempRow(x)
        Next x
    End With
    
    
Next theRow

'now you're able to refer to, for instance, "Color" named range using it as an array
' ...
theNameOfTheArray = "Color" ' provided you have this name among those got from tempRow first values
Set MyRange = Range(theNameOfTheArray)
MsgBox MyRange(0) & " - " & MyRange(1) ' & ....
TempSht.Delete

End Sub

this way you can always use "MyRange" range only for every array, just setting it to the proper named range via "theNameOfTheArray" variable
Hope this could help you some way.

Otherwise I could only think of using "if-then-else" or "select case" constructs or Switch or Choose functions to properly set a dummy array to the specific one chosen via its actual name

[Leo1965]

Using worksheets, or even userform listboxes, maybe even "If...Then" or "Case" are all viable possibilities, but what I'm trying to do is pure virtual manipulation, not written code.

The great advantage would be that all data needed to create arrays lays in the csv itself: the number of arrays, the names of the arrays, the values...
So, without using external apps, like Excel, and without having to write code, the arrays would be created at runtime, populated and became available to the user.
Just forget the public declared arrays I mentioned, it was only to simplify the question.
Those arrays can be created at runtime, as long as a name could be associated to them, for later addressing.

It would be very flexible, because:
- if now I need colors, dimensions, animals, food (just an enumeration here, but think to anything else), I simply write a csv with those lines and then open and use it;
- if later I need some other things (cars, cities, coordinates etc.) I can write another csv with those things
- I can also edit the existing csv files and add, remove, modify or update them.

I recall some javascript abillity to build a string and then run it as a line of code.
I think, using Eval(), or something.
Unfortunately, I haven't found anything similar in VBA (except "evaluate" which needs an excel formula).

Many thanks to all who are contribuing to this thread.
If any other suggestions, please keep in mind: "no excel, no listbox storage, no hard-coded names". I'm using VBA in CorelDraw.
As a second thought, maybe listboxes would do, but for that I have the knowledge to do it.

Now I'm thinking, would it be a possibility to create a module at runtime, write specific code in it for all that is needed to manipulate the arrays (names, length, values..., based on the content of CSV file) and then run it? And, next time a csv file is opened, to "erase" the module and re-write it?

[Kyle234]

Why can't you just use a collection?

p.s Using Eval() in javascript on Forums will get you shot it's considered the devil's function

[RICVB]

if your Corel Draw VBA has access to "IDE Object Model" then you can actually have your VBA code write another VBA code "on the fly".
I know it's possible but never tried to.

on the other hand, why don't you manage one csv file row at a time with a normal variant array in a for each-next loop through csv file? this way you don't need to bother about that hardcoded array name?

[SamT]

In short, you want to create an array of named arrays at run time, with the array names based on variables.

Why not work the other end, where you are using the rowArrays.

yet another array, RowIndex = stringData(i, 1) '{"colors", "animals", "food"}

ListBox1.List = RowIndex

FinalRowArrayToBeUsed = stringData(ListBox1.ListIndex)

[Leo1965]

To RICVB

I am using a temporary array to split each line into values and populate "named" arrays.
I need those arrays for later sorting, searching items or similarities, comparing and other things.
I could use instead a listbox filled with the csv values,
but that would mean each time to search the first column for the name I need, and then go on that row and reload all values into a temp array...

That's why I was looking for an way to address "by name" any array I have loaded.
I am talking about hundreds of arrays, each one representing an fashion article with specific dimensions and specifications, needed in CorelDraw for the work to be done.
Just to give you one example:

TMX700122,covered zip,camlock,xs,xxl,4th pocket,reflective,7mm seam allowance,polyesther,.... (some 30-40 values here)

Imagine having to code and name "by hand" hundreds of arrays like this.
But if I have an array named "TMX700122" and I need references about the article, I write that name into a textbox, hit enter and get it, and based on those value I can work on that article, by automation provided by VBA.

I will try with collections, as Kyle234 said, to see what I can get.
And during the weekend, I 'll look into the IDE Obj Model.

Have a nice day

[SamT]

That's what I was talking about, create a two dimensional array from the csv.
ProductArray(numRows,numItemsInRow)

Then load the ListBox with the first Items in each row.

When selecting an item from the ListBox, use the ListIndex property to return the Row array
TempArray = ProductArray(ListBox.ListIndex)

Corel Draw always uses TempArray

[RICVB]

i think UDT could do the trick as well

Public Type Arrays
    ArrayName As string
    nValues as integer 'useful for dinamically dimensioning each single ArrayValues()
    ArrayValues() As variant
End Type

then you declare your UDT

ReDim MyArrays (0 To Ubound(stringData)) As Arrays' The UDT is declared

and populate it looping through stringData

and then when you need a particular array whose name is taken for a textbox you simply have to search for that name looping through "MyArrays(i).ArrayName" and, once found "theRow", you simply pass "MyArrays(theRow).ArrayValues" to subs/functions that need to sort/search/compare it with other.
I'd give it a try.


I think collection could be even more effective. I don't use them so can't give you real hints, but it appeals to me the fact that you can refer to a particular item directly by its name, thus not needing to go through a loop in order to retrieve it. I'd guess something like this

Dim MyArrays As New Collection

then you'd populate it looping through stringData

For theRow = 0 To Ubound(stringData) - 1
   rowToSplit = stringData(theRow)
   tempRow() = Split(rowToSplit, "|")
   MyArrays.Add item := tempRow(), key := tempRow(0)
next theRow

so that when you get TheArrayName from textbox, you can directly refer to "MyArrays(TheArrayname)" array for computational purposes.

let me know if what above could really help you

bye