0

I am using an RTD formula in an Excel worksheet to receive a list of items in cell A1:

=RTD("josh.rtd",,"Letters",,"Lower")

The returned list in cell A1 is in curly bracket and delimited by semi colons. Basically, it's a one dimensional array of rows and looks like this

{"a";"b";"c";"d"}

If I copy that result, and paste the values in A2, then copy A2, select four cells from a column in a spreadsheet, such as A3:A6, and type an "=" in the formula window, and paste the result,

={"a";"b";"c";"d"}

I can hold down ctrl+shift+enter and populate each of those cells with an element of the array like this:

a

b

c

d

My question is this: is there a formula I can use to parse that original RTD formula result? I want to populate a combo box control with the elements of that array. I would love to be able to do this either in a cell formula or via VBA.

Community
  • 1
  • 1
Josh
  • 33
  • 5
  • For a formula, you can use the `INDEX` function. – Ron Rosenfeld Oct 05 '16 at 01:03
  • Thanks @Ron, I have looked at INDEX but I don't see how the INDEX function is used in this case. I can only find examples of INDEX being used to refer to a cell reference. At this point, the entire array exists in cell A1. If I named cell A1 "letters," then in A2 it would be great if I could put =letters[2] and get "b" (or "c" if the index is base 0). The answer from John gets me 90% there. – Josh Oct 05 '16 at 13:35
  • There is no requirement that the array argument for the Index function be a range. You would place the `RTD` formula as the `array` argument, and then look at the different rows (or columns depending on how RTD returns). Something like `INDEX(RTD(...),2,1)` should return the second element in the array. If it is returning a string that looks like an array, then this would not work. – Ron Rosenfeld Oct 05 '16 at 13:38
  • @RonRosenfeld Perfect, thank you. Using John's function below, this works: `=INDEX(ParseArray(A1),1,2)` – Josh Oct 05 '16 at 14:05

1 Answers1

3

This documentation suggests that RTD returns a string. If so, the following should be able to parse it, returning the result as an array:

Function ParseArray(ArrayString As String) As Variant
    Dim s As String
    s = Replace(ArrayString, "{", "")
    s = Replace(s, "}", "")
    s = Replace(s, """", "")
    ParseArray = Split(s, ";")
End Function
John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • You Split for rows but not for columns. It would be simpler to Evaluate it like the article you referred did. –  Oct 05 '16 at 01:37
  • @ThomasInzina Good points, but OP specifically said that in their application it was a 1-dimensional array. `Evaluate` would turn it into one of those annoying pseudo 2 dimensional arrays which wouldn't be quite as convenient for some applications. – John Coleman Oct 05 '16 at 02:56
  • Thanks, @JohnColeman! This is great. My next step is to populate a combo box with the elements of the array. I would like to be able to count the elements of the array, return an element by index, and other things via VBA. Rather than be handed the answer, are there any other documents you would recommend to learn more about working with arrays in VBA? – Josh Oct 05 '16 at 13:57
  • `=INDEX(ParseArray(A1),1,2)` gives me the second element, and `=COUNTA(ParseArray(A1))` gives me the number of elements. – Josh Oct 05 '16 at 14:11
  • I'm sure there are tutorials/ youtube videos out there, but if you like books I would recommend John Walkenbach's "Excel VBA Programming for Dummies". I first learned VBA through an earlier edition of that book. His website is also a good source of information (http://spreadsheetpage.com/ ) – John Coleman Oct 05 '16 at 14:11
  • If you assign `ParseArray(A1)` to a variable, say `A`, then simply `A(0)` is the first element, `A(1)` is the second, and `UBound(A) + 1` is the number of elements. – John Coleman Oct 05 '16 at 14:14