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
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