无法将数组用于Excel数据验证下拉列表及相关数组问题求助

三个问题的根本原因 #

• Debug.Print无输出、单元格写入无效：循环中先给MyArray(r)赋值，随即执行r = r + 1，之后访问MyArray(r)时，这个下标对应的元素还未被赋值（下一次循环才会赋值），所以读取的是空值。
• 数组无法用于数据验证：Excel数据验证的Formula1参数不支持直接传入数组变量，需要将数组转换为逗号分隔的字符串。

修正后的代码 #

Sub Testing()
    Dim ws As Worksheet
    Dim MyArray() As String
    Dim r As Integer
    Dim WsCount As Integer

    r = 1
    WsCount = ThisWorkbook.Worksheets.Count
    ReDim MyArray(1 To WsCount)

    For Each ws In ThisWorkbook.Worksheets
        MyArray(r) = ws.Name
        ' 先操作当前赋值的元素，再递增r
        If r <= 12 Then
            Debug.Print MyArray(r)
            Range("A" & r).Value = MyArray(r)
        End If
        r = r + 1
    Next ws

    ' 将数组转为逗号分隔的字符串，用于数据验证列表
    ActiveSheet.Cells(1, 2).Validation.Add _
        Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=Join(MyArray, ",")
End Sub

关键修正点 #

• 调整循环内的执行顺序：先对刚赋值的MyArray(r)进行Debug打印和单元格写入，再执行r = r + 1，确保操作的是已赋值的元素。
• 使用Join(MyArray, ",")将字符串数组转换为逗号分隔的文本，符合数据验证列表的参数要求。

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