string - 具有多个条件和下拉列表的 Excel VBA if/elseif 函数

Question

我是 VBA 新手，目前使用它来创建一个函数，该函数使用三个标准来输出计算结果。三个输入单元格包括一个数值(“emissionsquantity”)和两个文本值(“gas”和“units”，这两个值都具有从下拉菜单中选择的值。

我基本上想创建一个函数，根据从下拉菜单中选择的值计算结果。例如，如果下拉 B(“gas”)列出了某个值，下拉 C“units”列出了某种类型的单位度量，我想使用 A 列中的数量来计算最终值。这是我迄今为止开始开发的(针对三种污染物类型之一):

Function CO2EquivalentEmissions(emissionsquantity As Double, gas As String, units As String)
    If (gas = "CH4" Or "ch4") And units = "tons" Then
        CO2EquivalentEmissions = emissionsquantity * 25
    ElseIf (gas = "CH4" Or "ch4") And units = "lbs" Then
        CO2EquivalentEmissions = (emissionsquantity / 2000) * 25
    ElseIf (gas = "CH4" Or "ch4") And units = "tonnes" Then
        CO2EquivalentEmissions = (emissionsquantity * 1.10231) * 25
End If
End Function

然而，使用这个函数，我得到的结果是 0.00，不管我为“排放量”使用哪个值。有什么建议吗？非常感谢!

Answer 1

你有点困惑如何测试字符串，你放错了 " 的位置，见下面的代码:

Function CO2EquivalentEmissions(emissionsquantity As Double, gas As String, units As String)

If gas = "CH4" And units = "tons" Then
    CO2EquivalentEmissions = emissionsquantity * 25
ElseIf gas = "CH4" And units = "lbs" Then
    CO2EquivalentEmissions = (emissionsquantity / 2000) * 25
ElseIf gas = "CH4" And units = "tonnes" Then
    CO2EquivalentEmissions = (emissionsquantity * 1.10231) * 25
End If

End Function

编辑 1 : 更新更正 Function (包括 Or 用于 气体 标准)

Function CO2EquivalentEmissions(emissionsquantity As Double, gas As String, units As String)

If (gas = "CH4" Or gas = "ch4") And units = "tons" Then
    CO2EquivalentEmissions = emissionsquantity * 25
ElseIf (gas = "CH4" Or gas = "ch4") And units = "lbs" Then
    CO2EquivalentEmissions = (emissionsquantity / 2000) * 25
ElseIf (gas = "CH4" Or gas = "ch4") And units = "tonnes" Then
    CO2EquivalentEmissions = (emissionsquantity * 1.10231) * 25
End If

End Function