excel - VBA 中的类型 13 与 Case 语句不匹配

Question

我对 excel/vba 编程比较陌生，但我的任务是在 excel 中制作一个简单的按钮。这样做的目的是在一个范围内使用用户输入，然后在检查某些条件后返回另一个范围内的值。您可以在下面找到我的代码。所以我首先将变量与特定的单元格相关联。然后在 button_click 位中检查 case/nested if...then 语句。问题是它在“Case 'H232' or 'H250'”行返回类型 13 不匹配，我不知道如何修复它。提前致谢!

编辑:提供有关该项目的更多详细信息。
用户将输入全局协调系统 (GHS) 使用的危险代码 (H-Codes)，用于不同产品的危险标签。用户将在 H_Code_Input 范围内输入适当的代码(我现在理解的是不正确的)。然后，该代码旨在根据我的 Select Case 中概述的条件检查用户输入，然后在分配的单元格中生成数据。编号的输出表示标记的条件或严重程度。我将尝试将其设置为贯穿列的 For 循环，而不是使用 H_Code_Input 字符串。

我也很抱歉花了几天时间来编辑这个问题。上周末我出城了。

Private Sub GetCellValue()
    'Preparing variable values for GHS by assigning them to output cells
    Dim Pyrophoric As Range
        Set Pyrophoric = Range("G5")
    Dim Gas_Under_Pressure As Range
        Set Gas_Under_Pressure = Range("G6")
    Dim Flammable_Gas As Range
        Set Flammable_Gas = Range("G7")
    Dim Liquefied_Gas As Range
        Set Liquefied_Gas = Range("G8")
    Dim Flammable_Liquid As Range
        Set Flammable_Liquid = Range("G9")
    Dim Oxidizing_Gas As Range
        Set Oxidizing_Gas = Range("G10")
    Dim Skin_Corrosion As Range
        Set Skin_Corrosion = Range("G11")
    Dim Eye_Irritant As Range
        Set Eye_Irritant = Range("G12")
    Dim Respiratory_Sensitizer As Range
        Set Respiratory_Sensitizer = Range("G13")
    Dim Skin_Sensitizer As Range
        Set Skin_Sensitizer = Range("G14")
    Dim Germ_Cell_Mutagen As Range
        Set Germ_Cell_Mutagen = Range("G15")
    Dim Carcinogen As Range
        Set Carcinogen = Range("G16")
    Dim Respiratory_Hazard As Range
        Set Respiratory_Hazard = Range("G17")
    Dim STORE As Range
        Set STORE = Range("G18")
    Dim STOSE As Range
        Set STOSE = Range("G19")
    Dim Environ_Acute As Range
        Set Environ_Acute = Range("G20")
    Dim Environ_Chronic As Range
        Set Environ_Chronic = Range("G21")
    Dim Ozone_Deplete As Range
        Set Ozone_Deplete = Range("G22")


    'Creating input range
    Dim H_Code_Input As Range
        Set H_Code_Input = Range("B5:B24")




End Sub

Private Sub CommandButton2_Click()
    'Establishing outputs based on user input H codes
    Select Case H_Code_Input
        Case H_Code_Input = "H232" Or "H250"
            Pyrophoric = 1
        Case "H280"
            Gas_Under_Pressure = 1
        Case "H224" Or "H225" Or "H226" Or "H227"
            If H_Code_Input.Value = "H224" Then
                Flammable_Gas = 1
            ElseIf H_Code_Input.Value = "H225" Then
                Flammable_Gas = 2
            ElseIf H_Code_Input.Value = "H226" Then
                Flammable_Gas = 3
            ElseIf H_Code_Input.Value = "H227" Then
                Flammable_Gas = 4
            End If
        Case "H270"
            Oxidizing_Gas = 1
        Case "H314" Or "H315" Or "H316"
            If H_Code_Input.Value = "H314" Then
                Skin_Corrosion = "1A, B, C"
            ElseIf H_Code_Input.Value = "H315" Then
                Skin_Corrosion = 2
            ElseIf H_Code_Input.Value = "H314" Then
                Skin_Corrosion = 3
            End If
        Case "H318" Or "H319" Or "H320"
            If H_Code_Input.Value = "H318" Then
                Eye_Irritant = 1
            ElseIf H_Code_Input.Value = "H319" Then
                Eye_Irritant = "2A"
            ElseIf H_Code_Input.Value = "H320" Then
                Eye_Irritant = "2B"
            End If
        Case "H334"
            Respiratory_Sensitizer = "1, 1A, 1B"
        Case "H317"
            Skin_Sensitizer = 1
        Case "H340" Or "H341"
            If H_Code_Input.Value = "H340" Then
                Germ_Cell_Mutagen = "1A, 1B"
            ElseIf H_Code_Input.Value = "H341" Then
                Germ_Cell_Mutagen = 2
            End If
        Case "H350" Or "H351-i" Or "H351"
            If H_Code_Input.Value = "H350" Or "H350-i" Then
                Carcinogen = "1A, 1B"
            ElseIf H_Code_Input.Value = "H351" Then
                Carcinogen = 2
            End If
        'Need Respiratory Hazard parameters
        Case "H372" Or "H373"
            If H_Code_Input.Value = "H372" Then
                STORE = 1
            ElseIf H_Code_Input.Value = "H373" Then
                STORE = 2
            End If
        Case "H335" Or "H370" Or "H371"
            If H_Code_Input.Value = "H335" Then
                STOSE = 3
            ElseIf H_Code_Input.Value = "H370" Then
                STOSE = 1
            ElseIf H_Code_Input.Value = "H371" Then
                STOSE = 2
            End If
        Case "H400" Or "H401" Or "H402"
            If H_Code_Input.Value = "H400" Then
                Environ_Acute = 1
            ElseIf H_Code_Input.Value = "H401" Then
                Environ_Acute = 2
            ElseIf H_Code_Input.Value = "H402" Then
                Environ_Acute = 3
            End If
        Case "H410" Or "H411" Or "H412" Or "H413"
            If H_Code_Input.Value = "H410" Then
                Environ_Chronic = 1
            ElseIf H_Code_Input.Value = "H411" Then
                Environ_Chronic = 2
            ElseIf H_Code_Input.Value = "H412" Then
                Environ_Chronic = 3
            ElseIf H_Code_Input.Value = "H413" Then
                Environ_Chronic = 4
            End If
        Case "H420"
            Ozone_Deplete = 1

    End Select

End Sub

Answer 1

Case "H224" Or "H225" Or "H226" Or "H227"

解析为按位或表达式，这对于 String 是非法的操作数；你想要一个逗号分隔的替代列表:

Case "H224", "H225", "H226", "H227"

请注意，这只解决了您遇到的类型不匹配错误 - 此代码还有其他几个重要问题。

我强烈建议阅读有关范围界定的内容，并检查 VBIDE 选项中的“需要变量声明”框，以便 Option Explicit系统地添加到每个模块中。

Rubberduck (免责声明:我管理这个开源项目)代码检查会警告你所有这些未使用的变量，以及所有隐式声明的变量。