自定义函数总结.docxVIP

EXCEL VBA自定义函数 1 ?创建字典对象 Sub CreateDic(objl As Object, Optional obj2 As Object, Optional obj3 As Object, Optional obj4 As Object, Optional obj5 As Object, Optional obj6 As Object, Optional obj7 As Object, Optional obj8 As Object, Optional obj9 As Object, Optional objlO As Object} Set objl = CreateObject(^Scripting?Dictionary”} If Not IsMissing(obj2) Then Set obj2 If Not IsMissing(ob j 3) Then Set ob j3 If Not IsMissing(obj4) Then Set ob j4 If Not IsMissing(obj5) Then Set ob j5 If Not IsMissing(ob j 6) Then Set ob j6 If Not IsMissing(obj7) Then Set ob j7 If Not IsMissing(obj8) Then Set ob j8 If Not IsMissing(obj9) Then Set ob j9 If Not IsMissing(objlO) Then Set obj End Sub  = CreateObject(Scripting?Dictionary) = CreateObject(^Scripting.Dictionary”) = CreateObject(^Scripting.Dictionary”) = CreateObject(Scripting.Dictionary) = CreateObject(”Scripting.Dictionary) = CreateObject(”Scripting.Dictionary) ■ CreateObject(Scripting? Dictionary”) = CreateObject(^Scripting ?Dictionary) 0 = CreateObject (^Scripting?Dictionmiry”) 示例： Dim die as Object CreateDic die 2.批量获取列号 说明：最大参数10个，必要参数「个。从Rng中找到str并返回列号，如果没有找到相关 列，则返回未找到的列到FindMsg变量中 Dim r As Range Set r = rng.Find(strl, , , 2) If r Is Nothing Then FindMsg = Ilf (FindMsg = f,, n n, n, n) 1 n strl 11 1 n Else varl = r.Column If Not IsMissing(var2) Then Set r = rng.Find(str2, , , 2) If r Is Nothing Then FindMsg = FindMsgIlf (FindMsg = nn If r Is Nothing Then FindMsg = FindMsg Ilf (FindMsg = nn VI VI n,f,) str2 nn Else var2 r.Column End IfIf Not IsMissing(var3) ThenSet r = rng.Find(str3 End If If Not IsMissing(var3) Then Set r = rng.Find(str3z , 9 2) n ? fiIf r Is Nothing Then FindMsg = FindMsgIlf(FindMsg = nnn n ? fi If r Is Nothing Then FindMsg = FindMsg Ilf(FindMsg = nn n,ff) str3 …Else var3 r.Column End IfIf Not IsMissing(var4) ThenSet r = g.Find(sti?4, End If If Not IsMissing(var4) Then Set r = g.Find(sti?4, , , 2) If r Is Nothing Then FindMsgFindMsgIlf (FindMsg If r Is Nothing Then FindMsg FindMsg Ilf (FindMsg f!