A function within a function in vba?

Vote count:

0

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.

asked 58 secs ago

mistre42

6

A function within a function in vba?