Aargh. Came across an old problem with a spreadsheet today; trying to create and then write values to a dynamic array in VBA.
The position is that I’m trying to write email addresses to a file for a bulk mailing; the mail client particularly likes the local part of the email addresses in a comma delimited list, and the mailing lists are dynamic in nature – I might have 10 on one occasion and 1000 on another.
Problem:
- Excel doesn’t like one dimensional dynamic arrays
- Once you have 1000 rows in a spreadsheet of unique email addresses you need to concatenate them with a comma
- This is difficult to do in a single cell as you can quite quickly exceed the char limit for one cell
- It can be done by splitting the concatted addresses across multiple cells, but this isn’t a sure-fire winner
- Would be much easier to stuff all the values into an array and then simply join the array with a comma.
Found a work-around in amongst google code etc which goes like this:
1 2 3 4 5 6 7 8 9 10 | Open file_to_open For Output As #1 Dim addr As Variant addr = Sheets("Sheet1").Range(Cells(2, 1), Cells(uniques + 1, 1)).Value ReDim addresses(UBound(addr, 1) - 1) As String For i = 0 To UBound(addr, 1) - 1 addresses(i) = addr(i + 1, 1) Next i print_addresses = Join(addresses, ",") Print #1, print_addresses Close #1 |
This may not be the most elegant solution in the world, but it works for me (and it’s fast).
By declaring addr as a Variant type it can become a dynamic TWO dimension array, which is then populated with the values of the addresses. i can then step through the dynamically created array to create a single array of known size, and then join it with the comma.
There’s probably a way to do this without creating the two dimension array first, but I couldn’t find it, or get one to work, so this’ll do for me :)
