Dynamically Create Macros (Specifically UDFs) from VBA Code

Question

I don't know if I have been using the correct search terms or not when searching for this functionality, but I'm curious, is it possible to dynamically generate macros in excel-vba? If so, what are the possible methods?

Is metaprogramming supported in excel-vba?

Answer 1

Here is the example I provided converted to VBA. You will need to include a reference to Microsoft Visual Basic for Applications Extensibility and enable access to the VBA project module in the trust center settings.

Sub CreateMacro()
Dim vbComp As VBComponent
Dim functionText As String

Set vbComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

functionText = "Function MyTest()" & vbCrLf
functionText = functionText + "MsgBox " & Chr(34) & "Hello World" & Chr(34) & vbCrLf
functionText = functionText + "End Function"

vbComp.CodeModule.AddFromString functionText

End Sub