将子例程转换为函数 [英] Converting a subroutine into a function

问题描述

我有以下子例程，该子例程在A列中获取预定义的字符串列表(称为我的大列表)，并取决于另一列中的字符串是否是我的大列表中的字符串之一的子字符串，它将替换它.如果没有匹配项，它将不执行任何操作(只保留字符串原样).

I have the following subroutine that takes a predefined list of strings (call this my big list) in column A and depending on if a string in another column is a sub string of one of the strings in my big list, it replaces it. If there is no match, it doesn't do anything (just leaves the string as is).

Sub Find_Bad_Replace_Good()
    Dim rng As Range, v As Long, vList As Variant
    With Selection.Parent
        vList = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Value2
        For Each rng In Selection
            For v = LBound(vList, 1) To UBound(vList, 1)
                If CBool(InStr(1, rng.Value2, vList(v, 1), vbTextCompare)) Then
                    rng = vList(v, 1)
                    Exit For
                End If
            Next v
        Next rng
    End With
End Sub

我想将其隐式转换为接受字符串作为参数(来自单个单元格)的函数，而不是适用于整个字符串范围的宏.我希望其中一位专家能为我提供帮助.我想要这个，以便可以进行更多控制，并且宏似乎冻结了.

I want to covert it to a function that accepts a string as an argument (coming from a single cell) instead of a macro that works on the entire range of strings. I'm hoping one of you experts can help me. I want this so I can have more control and the macro seems to freeze.

推荐答案

以下假定您为大列表"指定了工作簿范围的名称.就像选择一个单元格范围时，在地址栏左侧的名称框"中输入有效名称一样，此操作很容易.为了向后兼容，我将使用名称 vList .

The following assumes that you have given your 'big list' a name with workbook scope. This action can be as easy as typing a valid name into the Name Box to the left of the address bar while the range of cells is selected. For the sake of backwards conformity, I'll use the name vList.

Function udf_Alternate_Word(str As String)
    udf_Alternate_Word = "no match"
    If Not IsError(Application.Match(Chr(42) & str & Chr(42), Range("vList"), 0)) Then _
        udf_Alternate_Word = Range("vList").Cells(Application.Match(Chr(42) & str & Chr(42), Range("vList"), 0), 1).Value2
End Function

这篇关于将子例程转换为函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！