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

Answer 1

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), ", ")

Answer 2

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