VBA中Public Property无法传递数组至工作表模块问题排查

核心错误：局部变量遮蔽模块级变量 #

你在Workbook_Open过程里重新声明了Dim Test_Array() As Variant，这会创建一个局部作用域的Test_Array数组，和模块顶部定义的私有模块级Test_Array不是同一个变量。循环中填充的是这个局部数组，模块级的Test_Array始终是空的，所以Public Property Get返回的是空数组，调用ThisWorkbook.TestArray(0)自然会触发“下标越界”错误。

Variant类型完全可以用于Public Property Get，问题不在类型本身，而是变量作用域的冲突。

修复步骤 #

1. 删除Workbook_Open过程中的Dim Test_Array() As Variant语句，让代码直接操作模块级的Test_Array变量：

Option Explicit
Private Test_Array() As Variant

Private Sub Workbook_Open()
' 此处为重新定义数组并填充元素的代码
' 移除了局部的Dim Test_Array() As Variant
Dim i As Integer ' 建议显式声明变量，避免隐式变体类型
' 假设TestRange是已定义的工作表范围对象
For i = 1 To UBound(TestRange, 2)
     ReDim Preserve Test_Array(i - 1)
     Test_Array(i - 1) = TestRange(1, i)
Next i
End Sub

Public Property Get TestArray() As Variant
    TestArray = Test_Array()
End Property

2. （可选优化）避免多次ReDim Preserve提升效率
   每次ReDim Preserve都会重新复制数组，数据量大时影响性能。可以先获取列数，一次性初始化数组：

Private Sub Workbook_Open()
Dim i As Integer
Dim colCount As Integer
colCount = UBound(TestRange, 2)
' 一次性初始化数组大小
ReDim Test_Array(0 To colCount - 1)
For i = 1 To colCount
    Test_Array(i - 1) = TestRange(1, i)
Next i
End Sub

验证修复 #

重新打开工作簿后，在Sheet1模块中调用ThisWorkbook.TestArray(0)，就能正常获取数组元素了。

内容的提问来源于stack exchange，提问作者AhlawSahla