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
0
EagleOne
4/16/2010 1:58:16 AM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
1361 Views

Similar Articles

[PageSpeed] 59

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 )



  
<EagleOne@discussions.microsoft.com> 
wrote in message news:ieffs5hll1asj9vi051r4kjnnjc656ueta@4ax.com...
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
0
Jim
4/16/2010 3:51:49 AM
EagleOne@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
0
Martin
4/16/2010 8:36:45 AM
"Martin Brown" <|||newspam|||@nezumi.demon.co.uk> je napisao u poruci 
interesnoj grupi:vkVxn.264680$Dv7.161682@newsfe17.iad...
> EagleOne@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


0
sali
4/16/2010 8:55:56 AM
Thanks Jim.

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

EagleOne

"Jim Cone" <james.coneXXX@comcast.netXXX> 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
0
EagleOne
4/16/2010 11:38:29 AM
Martin,

Very clever approach.

EagleOne


Martin Brown <|||newspam|||@nezumi.demon.co.uk> wrote:

>EagleOne@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
0
EagleOne
4/16/2010 11:39:47 AM
sali,

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

EagleOne


"sali" <sali@euroherc.hr> wrote:

>"Martin Brown" <|||newspam|||@nezumi.demon.co.uk> je napisao u poruci 
>interesnoj grupi:vkVxn.264680$Dv7.161682@newsfe17.iad...
>> EagleOne@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
>
0
EagleOne
4/16/2010 11:43:14 AM
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@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
0
Chip
4/16/2010 5:27:56 PM
Reply:

Similar Artilces:

Input mask
Can anyone please help me with setting an input mask for a cell phone field in the format +00 00 111 2222 or +00 00 111 222. Would really appreciate some guidance on this. ...

Excel 12, insert chart does not work. Exel restart. Why?
When I insert chart and select chart data, MS Office encounters a problem and need to close. Another thing: On chart tools the desings are empty. Any solution? In article <A678D3D8-566D-46A1-8A19-5F85D34BB2C8@microsoft.com>, tujauja@discussions.microsoft.com says... > When I insert chart and select chart data, MS Office encounters a problem and > need to close. > Another thing: > On chart tools the desings are empty. > Any solution? > Office 12 is still in beta. You should have received information from MS about where and how to post questions about it. -- Rega...

Cell.Find in VBA
Hi, I have the following VBA Macro: Set FoundCell = .Cells.Find(What:="199", _ After:=.Cells(1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) I don't how to write a VBA macro that would cells.find all 5-digit numbers. Here's an example: Column A 199 199 75781 75899 199 80012 Thanks, Hi I think you have to loop through all cells and check the length/value of each cell -- Regards Frank Kabel Frankfurt, Germany "Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag news:D3CEA329-2A...

Adding variables to a static text control (displaying contents of array)
Hello, I was wondering if the following was possible ? I have a static text control in a dialog application. I also have an array which contains about 5 items. I would like the text control to display the contents of this array and then have a button which would move to next position into the array and thus the static text control would display something different ? How could achieve this? I'm using Visual Studio 6.0 and I'm quite new to MFC Many Thanks. >I have a static text control in a dialog application. I also have an >array which contains about 5 items. I would lik...

selecting rows using a variable
I am using excel 2003 and I need to select rows using a variable. Here is what I have now: Dim name As String name = Range("e800") ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select Selection.EntireRow.Hidden = True Dim Start As Integer Dim finish As Integer Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("t800:t881")) finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("u800:u881")) ActiveWorkbook.Sheets("individual stats&q...

Insert random number only if cell empty
Hello. I'd like my spreadsheet to place a 7 digit random number into a cell only the cell is empty, otherwise leave the contents as is. I simply don't want the number to change if one is already present. I currently keep the number static by pressing F9 when I enter the RAND formula, but that forces me to manually change the number when I save the spreadsheet to another name. I'd like the spreadsheet to automatically calculate a new number in the cell as soon as I erase the previous number in that cell. Any help on this issue will be appreciated. Thanks. "M.L.&qu...

How do I identify Filter criteria or variable graph title?
I have a graph at the top of a sheet. In line 20 starts data with a filter on the data elements. When I set a filter on A20, the data below is filtered and the graph above is changed. However, I want the graph title to reflect the filter selection criteria. Is this possible? ...

How do I freeze lines on a form
I have a form that was created in word that co-workers have to type on with client information. How do I make the lines & boxes freeze so that when they do type on it it doesn't move? Insert a table. Put each form field in a separate cell of the table. In the Table Properties dialog, Rows tab, set the row heights to "Exactly" values. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. rauten46 wrote: > I have a form that was created i...

Copy/Paste VBA
Ich will eine ganze Visio Seite kopieren mit allen Verbindungen zwischen den Shape und deren Verklebungen (glue Ich habs versucht mit select all, group , copy paste und ungroup auch mit der Option bei copy/paste das es an der orignalposition positioniert werden sol abe 1) die Positionen stimmten nicht überei 2) die Verbindungen waren eingetragen aber nicht verglue Was nun einer eine Version die funktioniert und einfach is thx, mfg Daniel H. You might try Copy / Paste without doing the Group / Ungroup. Are you working with a specific set of shapes? Some Visio shapes have protections t...

Access 2007 ControlSource code in Report causing shut down
The following code worked successfully in Access 2002 & 2003 but for some reason, the lines that contain ControlSource are causing Access 2007 to shut down and error out. Any suggestions you have would be greatly appreciated. In short, I’m using a combo box on a form “ReportCenter” to change the GroupLevel and textbox values on a report. If I comment out the lines with ControlSource the code runs successfully, if any one of them are uncommented, Access 2007 shuts down and restarts. 'Group 1 If Forms![ReportCenter]!T2GroupCheck1 = True And Forms![ReportCenter]!T2GroupDescendC...

VBA to count periods in a single cell
I am looking to count the number of periods within a single cell using vba. For instance if I have 4 cells that contain the following: 5M09-0000700.01.10 5M09-0000700.01.10.10 5M09-0000700.01.10.10.10 5M09-0000700.01.10.10.20 Thus the output of the code for the first cell should be '2', the second cell output would be '3', and the third/fourht cells would be '4' because that many period characters were encountered. Data within these cells will contain letters, numbers, periods, a few spaces and the "-" dash symbol. Any help is greatly appre...

Compiling MFC project from command line...
Hi, How do I compile and link MFC activex project from command line in MSVC 6.0 Is there any tool which generates such info... Help ! sandSpiderX If you look in the project settings for your ActiveX project, you will see the command line that is used by the IDE for building the project. You should be able to use that command line directly. "sandSpider" <m74.piscean@gmail.com> wrote in message news:1120655631.691634.137580@o13g2000cwo.googlegroups.com... > Hi, > > How do I compile and link MFC activex project from command line in MSVC > 6.0 > > Is the...

How to start cluster from command line?
We are running Windows 2003 domain and Exchange 2003 cluster. I had to take ALL of the cluster resources offline install a Microsoft fix. Now the cluster won't show up in Cluster Administrator so I can't bring the cluster back online. Anyone know the command line commands to do this, or how I open a connection to the cluster in Cluster Administrator? TIA, Lamar The virtual node name is "stms". Whe I try to connect to that cluster it says that the cluster service is not running on that node. Lamar "Lamar Thomas" <none@none.com> wrote in me...

Method of calling a function where function name is stored in a variable or control
Is there a way to call a function (or sub for that matter) where the function/sub name is stored in a variable? For instance lets say I have a table that stores some function names. On a form I have a list box that displays the records in that table. For this example lets say they all accomplish similar things and all require the same numbef of arguments and none of them return a value. If Call were the right method to accomplis this it might look sometihng like this Dim lngArgument as Long lngArgument = 1234 Call Me.lstBox, lngArgument In this case Call would run the function...

Line selection for formating in Excel chart
Have "plain vanilla" line chart in excel spread sheet. Data series consist of several lines closely space. Normally I can select and RClick a single series for formating such as line color and thickness. Is it possible to simultaneously select all data series to change a common format such as line thickness. I'd like to do this without individually selecting each line while the control key is depressed. (lazy and not that good-a shot). ~Dave Hi, No you can not select multiple series, like you can with shape objects. The only time saver is, select 1 on the series and the f...

Save query result in a variable
hi! hope you can help me AGAIN! i have a form that let's the user pick a name, job, between dates, tax(with or without) and percentage. this is for calculating the comision each employee has earned. i did this by a query that gets the info from the form adn then opens a report and in the report i make the calculations. but it turns out that several employees have 2 or more "jobs" and for each one diferent % of comission. the form and the reports handdle it fine, i'm looking for a way to add all of this reports for each person. i was thinking of saving the sum of each que...

Some grid lines disappear randomly. How do I restore them?
Column and row lines have broken on my excel document. I'm a new user. How can I restore column and row breaks? Hello: Did you apply a background fill to some cell? If you apply a background file to a cell the gridlines will disappear. This is easiest to see if you apply a white background fill. This condition will be copied with cell if you copy to another part of the sheet. To test if this is your problem go to (after saving a backup) edit/clear/formats. If that returns all your gridlines then your problem is formating. Pieter Vandenberg Lizzeebee007 <Lizzeebee007@discussion...

Executing a macro command
How do I execute a macro command by just typing something in a cell? For example, when a user type letter Y in cell A1.. macro 1 will execute, then when he types letter B also in cell A1, the second macro will execute and so on. Thank you. There is an event macro that runs when you change a cell value, the worksheet_change macro, make that macro decide which macro to call and do it. See http://www.cpearson.com/Excel/Events.aspx HTH. Best wishes Harald "Salza" <zainuddinz@gmail.com> wrote in message news:a496ff7c-18db-4130-91ea-193508da79a5@n16g2000yqm.goog...

Solver VBA
Currently I have a simple solver VBA script in my excel workbook. The script sets the value of cell "I1" to 1 by changing cell "H1", as shown below: Sub repeatsolve() ' SolverOk SetCell:="$I$1", MaxMinVal:=3, ValueOf:="1", ByChange:="$H$1" SolverSolve End Sub What I would like to do is set the value of all cells in the "I" column, of a specific range, to 1 by changing its respective cell in column "H". Does anyone have any suggestions? Thank in advance. -- kwrohde --------------------------------------------------...

How can I choose which lines are frozen using "freeze pane"?
I've tried highlighting the row I would like to freeze, usually just the first row, but it always freezes it after the 16th row. Any suggestions!? This is really frustrating! See if this old post helps: http://tinyurl.com/2qdzt -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "emilyp" <emilyp@discussions.microsoft.com> wrote in message news:35457940-EC4E-4071-B8C5-4AFD3DABDCB6@microsoft.com... I've tried highlighting the row I would like...

how can i build a three variable data table in excel ?
I know it is feasible but I can't figure out how to do it. Hi not really sure what you mean with this. Could you give an example? "Solario" wrote: > I know it is feasible but I can't figure out how to do it. Maybe?? dim myTable(1 to 2, 1 to 8, 1 to 17) as variant mytable(1,1,1) = "hi" mytable(1,1,2) = "there" ..... mytable(2,8,17) = "whew! that's a lot of entries" Solario wrote: > > I know it is feasible but I can't figure out how to do it. -- Dave Peterson ...

How to hide the Group Box lines around options buttons??
I'm working on my first Excel form and need to create option buttons with Excel's forms toolbar. After making several sets of Group Boxes, I can't figure out how to remove the Group Box frame surrounding the option buttons. I know it's something very simple but would appreciate your help. Thanks, Charlie charlie6067 You can hide the groupboxes in code--I don't think that there's any other way. Hit alt-f11 to get to the VBE hit ctrl-g to see the immediate window type this and hit enter. activesheet.groupboxes.visible = false If you only want to hide a single groupbo...

How do I create a Line Chart (Special)
Hi there, I know how to create a line chart, what i want basically to do is create a stack chart without showing the border or colour, all i want is to show the line only ay the top of the stack. hope this makes sense. regards Harry Stacked charts in general, and stacked line charts specifically, are not very effective at showing information, but if you look at the other line chart subtypes, the two in the center column of the Chart Type dialog (when Line is selected in the list) are "stacked line" types, which only show the line for the cumulative total at each catego...

lines in cells with numbers
When I print an excel worksheet, I have line lines running through th some of the numbers. It looks like I put a number in and then crosse it out. I have gone into format cells and cleared everything but the line still appear -- Message posted from http://www.ExcelForum.com Move the cursor slowly across the line, and if it changes into a different kind of cross with arrowheads on the ends, then the line is probably a drawing object...........while the cursor is in that new shape, just Right-click > Cut......... Otherwise, the cell might be being formatted by a ChangeEvent macro..........

Excel VBA ?
In excel vba I want to add a line that displays the name of the workbook on a summary sheet Is there an automatic way to add the name without typing it in here's what I have so far: 'Enter Name ..Range ("B1") = "NAME" Thank you in advance !!! Hi, The first line puts the name in a cell the second one puts the full path and name Sheets("Sheet1").Range("A1") = ThisWorkbook.Name Sheets("Sheet1").Range("A2") = ThisWorkbook.FullName -- Mike When competing hypotheses are otherwise equal, adopt the h...