3

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?

Improve this question

4 Answers 4

Reset to default
7

You can just use VBA's Split function.

Dim A as Variant
A = Split(str, ",")
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Though for the given example the output of Split() either needs some trimming or the string is prepared with some regex like \s*,\s*.
Good one... but if the string consists of str = "name, Sam, age, 24" can the 24 be loaded into the array as an Integer because its now being loaded as a string?
VBA should let you just assign the string ("24") to an integer, though, since it's not very strict about those things...
Looks like you need a small function to do that. Make an Array-of-Variant returning function that accepts the string as an argument, calls Split() on it, and ReDims the output array to proper size based in UBound(). Then fill the array one by one with the help of IsNumeric() and CInt().
@Reed Copsey: But as a result of Split(), you will get a string, and it will stay a string. VBA is strongly typed, only the Variant type gives the impression of duck-typing. (VBScript is different, there you have Variants only, which essentially makes the whole language look like it is duck-typed. Which it still isn't, strictly speaking.)
|
1

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

Does VBA have Dictionary Structure?

Improve this answer

1 Comment

I have read about the dictionary object, but I was looking for some solutions using an array because I have some legacy code that I have to cater to and I don't want to make major changes in it... the legacy code uses an Array as one of its parameters which I have to feed in.
0 
A = Array(split(str,","))

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

Improve this answer

3 Comments

This returns a 2D array with A(0)(0) = "name", A(0)(1) = "Sam"
yes. imagine a table where the column name is in the first column, the value in the second. then A(x)(0) returns variable name, A(x)(1) returns its value.
@Kevin: The Split function already returns an array, so Array(Split(str, ",")) creates an single-element array containing an array.
-1

Actually A = Split(str,",") works fine and returns a one dimensional array.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.