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?

Esteban Küber
  • 36,388
  • 15
  • 79
  • 97
Kevin Boyd
  • 12,121
  • 28
  • 86
  • 128

4 Answers4

7

You can just use VBA's Split function.

Dim A as Variant
A = Split(str, ",")
Reed Copsey
  • 554,122
  • 78
  • 1,158
  • 1,373
  • Though for the given example the output of `Split()` either needs some trimming or the string is prepared with some regex like `\s*,\s*`. – Tomalak Oct 19 '09 at 17:33
  • 1
    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? – Kevin Boyd Oct 19 '09 at 17:34
  • VBA should let you just assign the string ("24") to an integer, though, since it's not very strict about those things... – Reed Copsey Oct 19 '09 at 17:36
  • 1
    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()`. – Tomalak Oct 19 '09 at 17:38
  • @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.) – Tomalak Oct 19 '09 at 17:42
  • @Tomalak: With option strict off, you can assign a variable in a string to an integer in VBA, I thought... – Reed Copsey Oct 19 '09 at 18:32
  • @Tomalak, Reed. You can always assign a string to an integer in VBA. It's called evil type coercion. VB.Net introduced Option Strict so it can be avoided, but Option Strict doesn't exist in VBA. (Why do you say this isn't duck typing? It looks like duck typing and it quacks like duck typing.) – MarkJ Oct 20 '09 at 09:11
  • @MarkJ: Yeah, I know you could do this. I thought strict typing was added in more recent versions of VBA, though - but I'm not quite as familiar with it (last time I used it heavily was in office 2000...) Thanks for the update. – Reed Copsey Oct 20 '09 at 15:19
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?

Community
  • 1
  • 1
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • 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. – Kevin Boyd Oct 19 '09 at 17:22
0
A = Array(split(str,","))

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

Kopernik
  • 2,783
  • 1
  • 20
  • 21
  • This returns a 2D array with A(0)(0) = "name", A(0)(1) = "Sam" – Kevin Boyd Oct 19 '09 at 17:27
  • 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. – Kopernik Oct 19 '09 at 17:48
  • @Kevin: The `Split` function already returns an array, so `Array(Split(str, ","))` creates an single-element array containing an array. – Mike Spross Oct 20 '09 at 14:32
-1

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

Nathan
  • 1