编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

将子例程转换为函数:Converting a subroutine into a function

SlaterLatiao 集合 2022-5-11 10:40 8人围观

腾讯云服务器
将子例程转换为函数的处理方法

我有以下子例程,该子例程在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 

这篇关于将子例程转换为函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^