Simple VBA array join not working

Question

I'm puzzled why I can't msgbox this joined array. I can do it just fine if I create a static array with typed out values, but with a range of values from excel I keep getting "Invalid Procedure Call or Argument"

I've done a fair amount of research but I'm not able to find any examples of this issue. What am I doing wrong here?

 Sub From_sheet_make_array()
     Dim myarray() As Variant    
     Dim dudeString As String

     myarray() = Range("B2:B10").Value 
     dudeString = Join(myarray(), ", ")

     MsgBox dudeString 
 End Sub

Gaijinhunter · Answer 1 · 2011-12-13T10:56:20.543

20

The cookie goes to brettdj as resizing a 1D array and populating it is the best way to go (fastest) but I wanted to offer a more lesser-known compact solution in the case that you don't plan to use this on long arrays. It's not as fast than the 1D approach, but not slow like concatenation, but it's convenient when you want to write together fast code (easier not to make typos with one-liners)!

myArray = Range("B2:B10").value
myString = Join(WorksheetFunction.Transpose(myArray), ", ")

or even just:

myString = Join(WorksheetFunction.Transpose(Range("B2:B10").value), ", ")

edited Dec 13 '11 at 10:56

answered Dec 13 '11 at 10:43

Gaijinhunter

14,587
4
51
57

Thanks, this is great for this specific application! – JoshG Dec 13 '11 at 21:51
+1 I spent an hour trying to remember/find WorksheetFunction lol. Good one! Especially as it nicely gets rid of the extra dimension when the values end up all in one row. – eric Dec 14 '11 at 09:28

brettdj · Accepted Answer · 2013-12-28T06:18:48.290

18

A variant array created directly from a sheet range is 2D (ie it has rows and columns) - Join requires a 1D array.

So you would need to do something like this

[Updated to read range into variant array, then to convert variant array into 1D array for joining - avoids cell loop]

Note also that using TRANSPOSE as Issun has below on a single column does force a 1D ouctome immediately. So another alternative would be to loop through the columns or rows of a 2D variant array, and TRANSPOSE them column by column (or row by row) to quickly produce a 1D array.

 Sub From_sheet_make_array()
  Dim X
  Dim lngRow As Long
  Dim myArray()
  X = Range("B2:B10").Value2
  ReDim myArray(1 To UBound(X, 1))

  For lngRow = 1 To UBound(X, 1)
  myArray(lngRow) = X(lngRow, 1)
  Next

  Dim dudeString As String
  dudeString = Join(myArray, ", ")
  MsgBox dudeString
 End Sub

edited Dec 28 '13 at 06:18

answered Dec 13 '11 at 00:24

brettdj

54,857
16
114
177

To clarify, Range("B2:B10").Value returns Variant(9,1), not Variant(9). I think we have to use the loop, but we can use ReDim to size the array based on the number of rows or cells in the range. – eric Dec 13 '11 at 02:05
@eric the number of rows is the length of the first dimension; with ReDim you can only modify the last dimension. You can therefore only use ReDim to add or remove columns. To accept the value of the Value property, and indeed to ReDim, you need to declare the array as a variable-size array: `Dim MyArray()`. You should not ReDim before assigning the values to the variable, because that will just waste an allocation. – phoog Dec 13 '11 at 02:10
1

@phoog We only need one dimension in the array (myArray) to hold the values to concatenate, regardless of the shape and size of the range. – eric Dec 13 '11 at 02:41
1

+1 for the cunning use of `ReDim` (actually seems a bit dangerous to me). But why `Value2`? – Jean-François Corbett Dec 13 '11 at 10:14
@Jean-FrançoisCorbett it is [quicker than value](http://www.decisionmodels.com/calcsecretsj.htm) (down the page some what). Also of interest is [a case for value2 as the default property](http://www.dailydoseofexcel.com/archives/2011/12/02/a-case-for-value-as-a-default-property/). Thx for the upvotes gents – brettdj Dec 13 '11 at 11:03
Small note that Value2 will not allow for Currency and Date data types (value will) so don't use Value2 if you are using these types. :) – Gaijinhunter Dec 13 '11 at 11:11
1

... or rather these *formats*. @brettdj: Thanks for the link. I wasn't aware of those subtleties. – Jean-François Corbett Dec 13 '11 at 12:43
@eric I see what you mean, I was thinking about `ReDim`ming the wrong array. – phoog Dec 13 '11 at 20:10
@brettdj and everyone, the "quicker than value" link is a little bit off. It says that "Range.value2 attempts to convert date and Currency formatted cells into Variants containing Doubles." But actually, the values are stored as doubles internally, which explains why Value2 is faster: *it skips the attempted conversion altogether*. – phoog Dec 13 '11 at 20:16

Simple VBA array join not working

2 Answers2

Linked