vba - 使用VBA的Excel验证下拉列表

我有一个值数组。我想使用VBA在Excel单元格中将这些值显示为下拉列表。

这是我的代码。它显示“类型不匹配错误!”

Dim xlValidateList(6) As Integer
xlValidateList(1) = 1
xlValidateList(2) = 2
xlValidateList(3) = 3
xlValidateList(4) = 4
xlValidateList(5) = 5
xlValidateList(6) = 6

With Range("A1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidationList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

问题出现在下面的行中...

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

请让我知道问题出在哪里...预先感谢

最佳答案

您将数组定义为xlValidateList()，因此当您尝试分配类型时，它对尝试分配给该类型的内容感到困惑。

相反，请尝试以下操作:

Dim MyList(5) As String
MyList(0) = 1
MyList(1) = 2
MyList(2) = 3
MyList(3) = 4
MyList(4) = 5
MyList(5) = 6

With Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyList, ",")
End With