How do I use programmatically created worksheet functions in VBA

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