How do I use programmatically created worksheet functions in VBA

Question

Is there a "Register" or "Re-compile" function needed before using programmatically created functions?

When I add a function to a worksheet I cannot use it until after control is returned to the worksheet.

For example: If my code adds a function to a worksheet, then tries to use it I get the following error: Run-Time Error 438 - Object does not support this property or method When I look at the code for the worksheets the functions are there and if I run code that only uses the created functions, there is no error.

How can I use the functions right after I create them, without stopping first?

Here is an example in code - I get the error when I run TestingWorkSheetFunctions but not when I run TestWorkSheetFunction after the functions are created.

Example assumes a new workbook with at least two sheets (sheet1 and sheet2)

Option Explicit

Public Sub TestingWorksheetFunction()

    AddWorkSheetFunction

    TestWorkSheetFunction

End Sub

Public Sub AddWorkSheetFunction()

    'Sheet1's Function
    Dim strFunctionCode As String
        strFunctionCode = _
            "Public Function HelloWorld() as string" & vbCrLf & _
            vbCrLf & _
            vbTab & "HelloWorld = ""Hello World from Sheet 1""" & vbCrLf & _
            vbCrLf & _
            "End Function"
    ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Sheets("Sheet1").CodeName).CodeModule.AddFromString strFunctionCode

    'Sheet2's Function
    strFunctionCode = _
        "Public Function HelloWorld() as string" & vbCrLf & _
        vbCrLf & _
        vbTab & "HelloWorld = ""Hello World from Sheet 2""" & vbCrLf & _
        vbCrLf & _
        "End Function"
    ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Sheets("Sheet2").CodeName).CodeModule.AddFromString strFunctionCode

End Sub

Public Sub TestWorkSheetFunction()

    Dim wsWorksheet1 As Object
    Set wsWorksheet1 = ThisWorkbook.Sheets("Sheet1")
    Dim wsWorksheet2 As Object
    Set wsWorksheet2 = ThisWorkbook.Sheets("Sheet2")

    MsgBox wsWorksheet1.HelloWorld()
    MsgBox wsWorksheet2.HelloWorld()

End Sub

Answer 1

I think the problem here is that your vba is added to the worksheets and is not compiled, so that when the rest of your code tries to access this functions, they are written, but not part of the program yet. This can be seen when you run the code again and everything works fine.

Please try to switch the code you have on the following procedure to:

Public Sub TestingWorksheetFunction()

    AddWorkSheetFunction
    Application.OnTime Now, "TestWorkSheetFunction"

End Sub

This way, the vba will run the first part of the code and release the process, and then the procedure "TestWorkSheetFunction" will be called right away. Important: This is a workaround for your problem, this might not be the best solution, but it might work for your specific case.