excel - A function within a function in vba?

Question

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

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

Much appreciate any response.

Answer 1

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.

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

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