title: How do I use programmatically created worksheet functions in VBA
body: <p>Is there a "Register" or "Re-compile" function needed before using programmatically created functions?</p>
  
  <p>When I add a function to a worksheet I cannot use it until after control is returned to the worksheet.</p>
  
  <p>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.</p>
  
  <p>How can I use the functions right after I create them, without stopping first?</p>
  
  <p>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.</p>
  
  <p>Example assumes a new workbook with at least two sheets (sheet1 and sheet2)</p>
  
  <pre><code>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" &amp; vbCrLf &amp; _
              vbCrLf &amp; _
              vbTab &amp; "HelloWorld = ""Hello World from Sheet 1""" &amp; vbCrLf &amp; _
              vbCrLf &amp; _
              "End Function"
      ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Sheets("Sheet1").CodeName).CodeModule.AddFromString strFunctionCode
  
      'Sheet2's Function
      strFunctionCode = _
          "Public Function HelloWorld() as string" &amp; vbCrLf &amp; _
          vbCrLf &amp; _
          vbTab &amp; "HelloWorld = ""Hello World from Sheet 2""" &amp; vbCrLf &amp; _
          vbCrLf &amp; _
          "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
  </code></pre>