Execute a variable as an insertion into a VBA code line
 

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

Execute a variable as an insertion into a VBA code line
 

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 )





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

Execute a variable as an insertion into a VBA code line
 

Execute a variable as an insertion into a VBA code line
 

"Martin Brown" je napisao u poruci
interesnoj ...
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

Execute a variable as an insertion into a VBA code line
 

Thanks Jim.

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

EagleOne

"Jim Cone" wrote:

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

Execute a variable as an insertion into a VBA code line
 

Martin,

Very clever approach.

EagleOne


Martin Brown wrote:

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

Execute a variable as an insertion into a VBA code line
 

sali,

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

EagleOne


"sali" wrote:

"Martin Brown" je napisao u poruci
interesnoj ...
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

Execute a variable as an insertion into a VBA code line
 

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,
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