VBA中的功能内的功能? [英] A function within a function in vba?

问题描述

这是我在单元格中使用的函数的简化版本(例如= xxDay(B7))，用于从封闭的工作簿中检索一天:

Here is a simplified version of a function I use in a cell (=xxDay(B7) for example) to retrieve a day from a closed workbook:

    Function xxDay(row)

        Dim fName, Path, strSheet, strRef, strRng As Variant

        xxDay = ""
        Path = "C:\MMS\"
        fName = "Book1.xlsm"
        strSheet = "Sheet1"
        strRng = Cells(row, 3).Address(, , xlR1C1)
        strRef = "'" & Path & "[" & fName & "]" & strSheet & "'!" & strRng

        xxDay = ExecuteExcel4Macro(strRef)

    End Function

我在单元格中返回了一个#VALUE.我将其作为Sub运行，它将返回预期的结果.可以在内部调用一个函数吗?

I get a #VALUE returned in the cell. I run it as Sub and it will return the expected results. Is it possible to have a function call another function within itself?

    Sub SubxxDay()

        Dim fName, Path, strSheet, strRef, strRng, xxDay, row As Variant

        row = 7
        xxDay = ""
        Path = "C:\MMS\"
        fName = "Book1.xlsm"
        strSheet = "Sheet1"
        strRng = Cells(row, 3).Address(, , xlR1C1)
        strRef = "'" & Path & "[" & fName & "]" & strSheet & "'!" & strRng

        xxDay = ExecuteExcel4Macro(strRef)

        MsgBox xxDay
    End Sub

非常感谢您的答复.

推荐答案

我建议一种可能的解决方法.该方法如下:在执行UDF时，将ExecuteExcel4Macro()作为后期绑定Excell.Application实例的方法来调用，但不会通过会产生错误的主机Application对象来调用.因此，应在打开工作簿时创建该实例并使其保持可访问状态，并在关闭工作簿之前退出该实例以释放OS资源.这是下面的代码.

I suggest one of possible workarounds. The approach is as follows: while UDFs are executing, ExecuteExcel4Macro() is being called as a method of late bound Excell.Application instance, but not via host Application object that would give an error. So, that instance should be created and stay accessible while workbook is opened, and be quitted to release OS resources just before workbook closing. Here is the code below.

将此代码放入VBAProject模块:

Place this code into VBAProject Module:

Function ExcelApp()
    Static objApp As New clsExcelApp
    Set ExcelApp = objApp.ExcelApp
End Function

Function xxDay(row) ' the code this function contains is almost all yours
    Dim fName, Path, strSheet, strRef, strRng As Variant

    xxDay = ""
    Path = "C:\Test\"
    fName = "Source.xlsx"
    strSheet = "Sheet1"
    strRng = Cells(row, 3).Address(, , xlR1C1)
    strRef = "'" & Path & "[" & fName & "]" & strSheet & "'!" & strRng

    xxDay = ExcelApp.ExecuteExcel4Macro(strRef) ' reference to ExcelApp object

End Function

创建类模块，为其分配名称clsExcelApp，然后将以下代码放入其中:

Create Class Module, assign it the name clsExcelApp, and place this code into it:

Public ExcelApp

Private Sub Class_Initialize()
    Set ExcelApp = CreateObject("Excel.Application")
    ' ExcelApp.Visible = True ' uncomment for debug
End Sub

Private Sub Class_Terminate()
    ExcelApp.Quit ' the only class purpose is to quit app anyway at the end
    Set ExcelApp = Nothing
End Sub

这篇关于VBA中的功能内的功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！