ExcelBanter - View Single Post - Execute a variable as an insertion into a VBA code line

Martin Brown

wrote:
2003 & 2007

Is there a way to obtain, via Inputbox, to run the code line below:
"MyToolsObject.Test"

X = "MyToolsObject." & Application.InputBox("VB.NET File to test: ", "ENTER PROCEDURE NAME")

Assume: X = "MyToolsObject.Test "

Effectively, how can I "Execute" X as if it were the codeline below?

Sub DecodeRunDLL()
'
Dim MyToolsObject As ToolsNET.Tools
Set MyToolsObject = New ToolsNET.Tools
MyToolsObject.Test ' <<<<< *********** How Execute "X"
Set MyToolsObject = Nothing

End Sub

TIA EagleOne

It is ugly but you could do it as self modifying code by accessing the
project model. ISTR XL2007 will be tetchy about doing this and
additional security settings need to be changed to make it work.

Sub AddMyCode(mysub as String)
Debug.Print ("start add VBA code")
With ActiveWorkbook.VBProject.VBComponents(1).CodeModul e
.InsertLines .CountOfLines + 1, "Sub ExecTestCode"
.InsertLines .CountOfLines + 1, mysub
.InsertLines .CountOfLines + 1, "End Sub"
End With

Calling AddMyCode(X) will add to the end of the module

Sub ExecTextCode
MyToolsObject.Test
End Sub

(untested but should be close enough to get you started)

Otherwise do it as a case statement (which may be easier if you have a
lot of tests to do by mapping the list onto a single keystroke).

Regards,
Martin Brown