简单的 VBA 数组连接不起作用 [英] Simple VBA array join not working

问题描述

我很困惑为什么我不能 msgbox 这个连接的数组.如果我创建一个带有输入值的静态数组，我可以做得很好，但是对于来自 excel 的一系列值，我不断收到 无效的过程调用或参数"

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

推荐答案

直接从工作表范围创建的变体数组是二维的(即它有行和列) - Join 需要一维数组.

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

[更新 将范围读入变体数组，然后将变体数组转换为一维数组进行连接 - 避免单元格循环]

另请注意，使用 TRANSPOSE 就像 Issun 在下面的单列一样，确实会立即强制使用 1D octome.因此，另一种替代方法是遍历 2D 变体数组的列或行，然后 TRANSPOSE 逐列(或逐行)快速生成一维数组.

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