Excel VBA-数据验证数组参考 [英] Excel VBA - Arrays for Data Validation Reference

问题描述

我有以下代码:

Sub TEST_____________Data_Validation_Machine()

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim Range1 As Range, Range2 As Range
Dim c As Range
Dim Array_Fab As Variant
Dim Array_Paint As Variant
Dim Array_Sub As Variant
Dim Array_Asy As Variant
Dim Array_Facilities As Variant
Dim Array_Machine_List_Choices As Variant

Set ws = ThisWorkbook.Worksheets("EOS Report")
Set ws2 = ThisWorkbook.Worksheets("MachineList")
Set ws3 = ThisWorkbook.Worksheets("PlantAreaList")

    With ws2
        'creating arrays based on the named ranges on the sheet "MachineList"
        Array_Fab = Application.Transpose(.Range("MACHINESFAB"))
        Array_Paint = Application.Transpose(.Range("MACHINESPAINT"))
        Array_Sub = Application.Transpose(.Range("MACHINESSUB"))
        Array_Asy = Application.Transpose(.Range("MACHINESASY"))
        Array_Facilities = Application.Transpose(.Range("MACHINESFACILITIES"))
    End With

        'Array of Arrays
        Array_Machine_List_Choices = Array(Array_Fab, Array_Paint, Array_Sub, Array_Asy, Array_Facilities)

    With ws3
        'creating an array based on the named range on the sheet "PlantAreaList"
        Array_Plant_Area_Choices = Application.Transpose(.Range("PlantAreaListCells"))
    End With

'Cell below "MACHINE" based on the activecell selection of the "Plant Area" combobox:
Set Range1 = ActiveCell.Offset(0, 1)

'Cell below "PLANT AREA", based on the user selection of the combobox:
Set Range3 = ActiveCell

对于下一部分，我将如何编码: 如果range3 = Array_Plant_Area_Choices， 然后range2 =对应的Array_Machine_List_Choices

For this next part, how would I code: if range3 = Array_Plant_Area_Choices, then range2 = corresponding Array_Machine_List_Choices

    If Range3 = "" Then

        Set Range2 = ""

            For Each c In Range1
                If c.Interior.Pattern <> xlNone Then
                Else
                    With c.Validation
                        .Delete
                        .Add Type:=xlValidateList, _
                            Formula1:="='" & ws2.Name & "'!" & Range2.Address
                        .IgnoreBlank = True
                        .InCellDropdown = True
                    End With
                End If
             Next

Application.ScreenUpdating = True
End Sub

此代码创建一个数据验证框，其中的选择基于用户已经填写的先前的数据验证框.当前，有一堆if语句允许每个原始选择，但我要添加一吨甚至更多，这可能会减慢程序的运行速度，并且看起来很凌乱.我希望可以使用数组使此过程更加简洁.

This code creates a data validation box where the choices are based on a previous data validation box the user has already filled in. Currently a have a bunch of if statements to allow for each original choice, but I will be adding a ton more and this may slow down the program as well as just looking messy. I am hoping I can use arrays to make this process much cleaner.

感谢您的帮助！预先谢谢你！

Any help is appreciated! Thank you in advance!

推荐答案

不确定是否有帮助.变量数据类型不能容纳对象，因此您不能通过数组访问范围.

Not sure if this helps. Variant data type cannot hold objects, so you cannot access the range through an array.

Sub TEST_____________Data_Validation_Machine()

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim Range1 As Range, Range2 As Range
    Dim c As Range
    Dim Array_Machine_List_Choices As Variant

    Set ws = ThisWorkbook.Worksheets("EOS Report")
    Set ws2 = ThisWorkbook.Worksheets("MachineList")
    Set ws3 = ThisWorkbook.Worksheets("PlantAreaList")

    'Array of range names
    Array_Machine_List_Choices = Array("MACHINESFAB", "MACHINESPAINT", "MACHINESSUB", "MACHINESASY", "MACHINESFACILITIES")

    With ws3
        'creating an array based on the named range on the sheet "PlantAreaList"
        Array_Plant_Area_Choices = Application.Transpose(.Range("PlantAreaListCells"))
    End With

    'Cell below "MACHINE" based on the activecell selection of the "Plant Area" combobox:
    Set Range1 = ActiveCell.Offset(0, 1)

    'Cell below "PLANT AREA", based on the user selection of the combobox:
    Set Range3 = ActiveCell

    For i = 0 To UBound(Array_Plant_Area_Choices)

        If Range3 = Array_Plant_Area_Choices(i) Then

            If c.Interior.Pattern = xlNone Then
                With c.Validation
                    .Delete
                    .Add Type:=xlValidateList, _
                        Formula1:="='" & ws2.Name & "'!" & Array_Machine_List_Choices(i)
                    .IgnoreBlank = True
                    .InCellDropdown = True
                End With
            End If
        End If
    Next i

    Application.ScreenUpdating = True
End Sub

这篇关于Excel VBA-数据验证数组参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！