Execute a variable as an insertion into a VBA code line

[EagleOne]

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

 

[Jim Cone]

I think you are going to have to run X thru a Select Case statement.

Select Case True
Case Instr(1, X, "Test",vbTextCompare) > 0 Then
Call MyToolsObject.Test
Case Instr(1, X, "Sludge",vbTextCompare) > 0 Then
Call MyToolsObject.Sludge
End Select
--
Your chances of getting something consistently useful from user input may be pretty slim.
You are probably going to have to validate it against an array of correct/acceptable answers.
'--
Jim Cone
Portland, Oregon USA
(Review of the Special Sort add-in... http://www.contextures.com/excel-sort-addin.html )




<[email protected]>
wrote in message 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

 

[Martin Brown]

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

 

[sali]

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

 

[EagleOne]

Thanks Jim.

Thought I was having a brain fart and therefore missing an obvious solution.

EagleOne

 

[EagleOne]

Martin,

Very clever approach.

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

 

[EagleOne]

sali,

Have not used vbScript yet. Now I have a reason to start.

EagleOne

 

[Chip Pearson]

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