Execute VBA code stored in variable

[hawk9]

Hi,

I'm trying to execute VBA code stored inside a variable, but so far no luck.

I can compare it to PowerShell where you're able to do this:

$currentuser = whoami
PS C:\Users\testuser> $currentuser
azuread\testuser

PowerShell will execute "whoami" when you run $currentuser.

If i try to include VBA code in my macro and execute with Application.Run i get an error that the string is longer than 255 characters or out of stack space.
However, i might think that Application.Run is not right for this. The macro should execute whatever is stored in the variable (fetched from another source) in the current Excel process.
What i've tried so far (short string, not longer than 255 for this test)

Sub test()
Dim y As String
y = "Dim x As Double" & vbNewLine & "x = 5.5" & vbNewLine & "MsgBox 'value is ' & x"
Application.Run MacroName:="Normal.NewMacros.test"
End Sub

Does anyone have experience with this or can point me in the right direction?
Thank you.

 

[offthelip]

Have a read of this MS article whcih explains how you can do that sort of thing:
How to dynamically add and run a VBA macro from Visual Basic

 

[hawk9]

Thank you very much, offthelip