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?

or here http://stackoverflow.com/questions/13956104/add-user-defined-function-to-visual-studio-excel-add-in — Sorceri, Dec 19 '16 at 16:05
@Sorceri Forgive my ignorance regarding VBA/C#, on your response, what's the context of the provided C# code? Could that code be executed from within the VBA project? — jab, Dec 19 '16 at 16:18
@jab it is a pretty straight forward conversion, see my answer — Sorceri, Dec 19 '16 at 16:21

score 3 · Accepted Answer · answered Dec 19 '16 at 16:21

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

Dynamically Create Macros (Specifically UDFs) from VBA Code

1 Answers1