Execute a variable as an insertion into a VBA code line
in [Programming]
|
From: EagleOne on 16 Apr 2010 07:39 Martin, Very clever approach. EagleOne Martin Brown <|||newspam|||@nezumi.demon.co.uk> wrote: >EagleOne(a)discussions.microsoft.com 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).CodeModule > .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
From: EagleOne on 16 Apr 2010 07:43 sali, Have not used vbScript yet. Now I have a reason to start. EagleOne "sali" <sali(a)euroherc.hr> wrote: >"Martin Brown" <|||newspam|||@nezumi.demon.co.uk> je napisao u poruci >interesnoj grupi:vkVxn.264680$Dv7.161682(a)newsfe17.iad... >> EagleOne(a)discussions.microsoft.com wrote: >>> 2003 & 2007 >> >> 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. > >why don't take 'vbscipt' as host, and from script access excel object? >vbscript has 'execute' statement and 'eval' function, where parameter to >them may be any composed string variable, so you are free to create them at >run-time >
From: Chip Pearson on 16 Apr 2010 13:27
You can use CallByName Dim X As String Dim MyToolsObject As ToolsNET.Tools Set MyToolsObject = New ToolsNET.Tools X = "Test" CallByName MyToolsObject, X, VbMethod Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 15 Apr 2010 21:58:16 -0400, EagleOne(a)discussions.microsoft.com 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 |