Alternate use of the Array() function in VBA?

Question

We generally use the array function is VBA as :

Dim A As Variant

A = Array("B", 1)

This will give me the first element in A as "B" and second element as 1

However I want to decide the contents of A at run-time so is it possible for me to do something like

Dim str As String
Dim A As Variant

str = "name, Sam"
A = Array(str)

When I run this code it gives me first element in A as "name, Sam", but I need first element as "name" and second element as "Sam".

What could be the solution to this? How could I populate A at run-time?

Answer 1

It appears that you are looking for a Dictionary object or an associative array structure. An example of one can be found here:

http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure

Answer 2

You can just use VBA's Split function.

Dim A as Variant
A = Split(str, ",")

Answer 3

A = Array(split(str,","))

but for your purposes you should go with Robert Harvey's link.