excel - 创建验证列表

Question

我正在尝试从一维数组创建验证列表。问题是这段代码只在验证列表中创建了一个元素。一个带分隔符的大字符串，我想显示用逗号分隔的所有元素。

Sub test4()
    Dim aLineProd() As Variant
    Dim i As Long
    
    ReDim Preserve aLineProd(100)
    
    Do
        aLineProd(i) = ThisWorkbook.Worksheets(LIGNEPRODUCTION_SHEET_NAME).Range(INITIALCELL_ADRESS).Offset(i).Value
        i = i + 1
    Loop Until ThisWorkbook.Worksheets(LIGNEPRODUCTION_SHEET_NAME).Range(INITIALCELL_ADRESS).Offset(i).Value = ""
    
    ReDim Preserve aLineProd(i - 1)
    
    Call CreateValidationListGeneric(LINENUMBERABLE_ADRESS, aLineProd(), CHECKPRODUCTCODESHEET_NAME)
End Sub

Sub CreateValidationListGeneric(CellAdress As String, _
aValidationList() As Variant, sWorksheetName As String)

    With Worksheets(sWorksheetName).Range(CellAdress).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=Join(aValidationList, ",")
    End With
    
End Sub

标签： excelvba