excel vba的自定义函数里调用自定义过程的方法

一般来说，excel vba的自定义函数function()有局限性，只能返回值，不能修改单元格。转而调用过程sub()的话，又要传入参数，又要创建按钮，比较麻烦, 对于输入参数经常要变动的，比如ADO+sql语句，更是麻烦。但是也有例外，比如wind万得的返回历史价格序列函数histquote()可以在输入函数的单元格的下面返回一系列价格，这好像是通过调用c++语言编译的COM组件实现的。

在网上我有找到一个简单点的办法，通过添加类模块（用来制作高级对象的类class，也就是对象的母体）实现。（见Appendix）原理就是定义一些公共变量，在模块里定义函数的部分给它们赋值，并且封装进一些固定数据，然后在类模块里编写过程，调用这些公共变量的数值，并且结合那些固定数据，生成最终结果数据。

http://s3/mw690/001Tmgp8zy78itMJxECd2&690vba的自定义函数里调用自定义过程的方法" TITLE="excel vba的自定义函数里调用自定义过程的方法" />

我发现类模块里编写过程和写一般的过程差不多，不一定非要定义一堆属性，方法。从而把它修改了一下，用来实现ADO+SQL语句，比如 = func("select * from [pos$] ")调用了类模块，类似wind万得的histquote函数。在单元格输入该函数后回车，下面自动出现sql语句执行输出的结果。含有函数的单元格因为要返回值，所以设定它显示sql语句。这样就能方便的在单元格里输入参数，然后回车就调用过程，而不用创建按钮。不然的话就是分两步，比如制作一个sql连接函数，单元格A =func_merge(sheet2!A1, B1)会在本单元格输出"select * from [sheet2!A1:D5] a left join [sheet1!B1:E10] b on a.key=b.key"， 然后用按钮调用过程，或者用前面那个连接类模块的函数 = func(单元格A)来执行这串sql命令。

http://s7/mw690/001Tmgp8zy78itNOoaa36&690vba的自定义函数里调用自定义过程的方法" TITLE="excel vba的自定义函数里调用自定义过程的方法" />

另外，在用excel vba+sql创建access数据库时候，数值字段的类型选择要注意：

Appendix：下面是网上那个例子http://www.excelpx.com/thread-143802-1-1.html

模块部分

Public myc, mycc, aa, bb, cc, dd, Nam, Num

Function f(a As String, b As Integer, c As Integer, d As Integer)

    On Error GoTo ren

    f = ""

    aa = a

    bb = b

    cc = c

    dd = d

    Nam = Array("INE 101 0520", "INE 101 0517", "INE 101 0814", "INE 101 0933", "INE 101 0565", "INE 101 0534", "INE 101 0533")

    Num = Array(1, 2, 2, 1, 1, 1, 1)

    Set myc = New css

    Set myc.sht = ActiveSheet

    Exit Function

ren:

    Set myc = New css

    Set myc.sht = ActiveSheet

End Function

css类模块

Public WithEvents sht As Worksheet

Private Sub sht_Change(ByVal Target As Range)

On Error GoTo ren

    Set myc.sht = Nothing

    Set myc = Nothing

    Set s = CreateObject("MSScriptControl.ScriptControl")

    s.Language = "VBScript"

    s.AddObject "ActiveWorkbook", ActiveWorkbook

    s.AddObject "Application", Application

    s.AddObject "Activesheet", ActiveSheet

    s.AddObject "sheets", Sheets

    s.AddObject "cells", Cells

    Cells(cc, dd) = aa

    Cells(cc, dd + 1) = bb

    For i = 1 To UBound(Nam) + 1

    Cells(cc + i, dd) = Nam(i - 1)

    Cells(cc + i, dd + 1) = bb * Num(i - 1)

    Next i

    s.Reset

ren:

End Sub

将以上两部分的模块导入excel，使用时在需要的单元格上输入带参数的函数即可，例如输入

=f("SE800-R-BASE3-001",1,24,5)则以行24列5的单元格处为左上顶点出现

SE800-R-BASE3-001  1

INE 101 0520    1

INE 101 0517    2

INE 101 0814    2

INE 101 0933    1

INE 101 0565    1

INE 101 0534    1

INE 101 0533    1

又如A3单元格内容为SE800-R-BASE3-001的话输入=f(A3,4,24,3)则会在以行24列3的单元格处为左上顶点出现

SE800-R-BASE3-001 4

INE 101 0520           4

INE 101 0517           8

INE 101 0814           8

INE 101 0933           4

INE 101 0565           4

INE 101 0534           4

INE 101 0533           4