简单的VBA阵列加入不工作 [英] Simple VBA array join not working
问题描述
我很困惑,为什么我不能MSGBOX此加入数组。我能做到这一点就好了,如果我创建了输入值的静态数组,但是从Excel值的范围我不断收到的无效的过程调用或参数的
我做的研究相当数量,但我没能找到这个问题的例子。我在做什么错在这里?
子From_sheet_make_array()
昏暗的myArray的()为Variant
昏暗dudeString作为字符串 myArray的()=范围(B2:B10)值
dudeString =加入(myarray的(),) MSGBOX dudeString
结束小组
直接从片范围内创建的变量数组是2D(即它有行和列) - 加入
需要一维数组。
所以,你需要做这样的事情。
[更新 阅读范围分为变量数组,然后变量数组转换成一维数组加盟 - 避免了细胞循环的]
另外,在使用移调
作为Issun低于上一列不立即强制1D ouctome。因此,另一种方法是遍历一个二维变量数组的行或列,而 TRANSPOSE由列(或行由行)
他们柱快速制作一维数组。
子From_sheet_make_array()
昏暗的点¯x
昏暗lngRow只要
昏暗myArray的()
X =范围(B2:B10)值2。
使用ReDim myArray的(1向UBound函数(X,1)) 对于lngRow = 1到UBound函数(X,1)
myArray的(lngRow)= X(lngRow,1)
下一个 昏暗dudeString作为字符串
dudeString =加入(myArray的,,)
MSGBOX dudeString
结束小组
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
这篇关于简单的VBA阵列加入不工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!