VBA：将多个值传递给Instr [英] VBA: Passing multiple values to Instr

问题描述

现在，我有一长串用户通过表单提交的行业值。我已经编写了一个宏，它将搜索这些值中的特定术语，并粘贴符合我明显较小的可接受行业值列表的值。关键是将用户所提交的所有当前行业价值重新分类到我现有的分类中。这就是我的If-Then语句现在的样子：

Right now I have a long list of "Industry" values that users have submitted through a form. I've written a macro that will search for particular terms in those values and paste values that conform to my significantly smaller list of "acceptable" Industry values. The point is to reclassify all of the current industry values users have submitted into my existing taxonomy. Here's the way my If-Then statements look as of right now:

If InStr(1, Cells(i, "A").Value, "Energy") > 0 Or InStr(1, Cells(i, "A").Value, "Electricity") > 0 Or InStr(1, Cells(i, "A").Value, "Gas") > 0 Or InStr(1, Cells(i, "A").Value, "Utilit") > 0 Then
Cells(i, "B").Value = "Energy & Utilities"
End If

这很丑陋工作完全正常，但是一只熊读书。我的宏在这些几乎不能理解的If-Then语句中散落，很难阅读。有没有办法将多个值传递给 Instr 的String2参数？

It's pretty ugly. Works perfectly fine, but is a bear to read. My macro is littered with these barely-intelligible If-Then statements, and it's difficult to read through it all. Is there any way to pass multiple values to the "String2" argument of Instr?

推荐答案

[编辑：我修改了这个函数，以便如果最后一个参数是一个整数，它扮演 compareMode 参数的角色在 Instr （区分大小写搜索为0，默认为1，不区分大小写为1）。作为最后的调整，您还可以传递一个布尔值而不是整数，用于可选的最终参数，对应于不区分大小写的 True 和 False 对应于默认区分大小写]

[On Edit: I modified the function so that if the last argument is an integer it plays the role of the compareMode parameter in Instr (0 for case-sensitive search, which is the default, and 1 for case-insensitive). As a final tweak, you could also pass a Boolean rather than an integer for the optional final argument with True corresponding to case-insensitive and False corresponding to the default case-sensitive]

如果你做这样的事情很多，写一个类似于 inStr ，但可以处理多个模式。

If you do this sort of thing a lot it makes sense to write a function which is similar to inStr but can handle multiple patterns. ParamArray is a natural tool to use:

Function Contains(str As String, ParamArray args()) As Boolean
    Dim i As Long, n As Long, mode As Integer

    n = UBound(args)
    If TypeName(args(n)) <> "String" Then
        mode = args(n)
        mode = Abs(mode) 'So True => -1 => 1 for case-insensitive search
        n = n - 1
    End If

    For i = 0 To n
        If InStr(1, str, args(i), mode) > 0 Then
            Contains = True
            Exit Function
        End If
    Next i
    Contains = False
End Function

测试如下：

Sub Test()
    Debug.Print Contains("General Electric", "Gas", "Electric", "Oil")
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil")
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", False)
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", True)
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", 1)
    Debug.Print Contains("General Motors", "Gas", "Electric", "Oil")
End Sub

输出： / p>

Output:

True
False
False
True
True
False

这篇关于VBA：将多个值传递给Instr的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！