Use a string to create VBA coding

[Henri F]

Hi,

In VFP one can store any line of code in a variable and then execute it as a line of code by putting the & sign in front of it if i remember well:

Ex:

test = "Hello"
test2 = 1
test3 = "myform.text" & test2 & ".value=" & test

&test3

I looked all around the web to find a similar solution for Excel VBA, as I am trying to create a user automation tool. Despite all the "Nay" sayers, i am convinced there must be a simple solution to this challenge.

Creating an on-the-fly procedure is not an option as it will get in trouble with many antiviruses.

I tried to use EbExecuteLine which would have been the perfect solution, but Microsoft Office 2010 does not recognize it.

Any idea would be greatly appreciated?

[Norie]

The closest thing I can think of would be CallByName.

Dim test
Dim test2

test = "Hello"
test2 = 1

CallByName Me.Controls("TextBox" & test2), "Value", VbLet, test

[Henri F]

Norie, thank you for the very quick reply.

But I am not sure it will do, as what I am trying to do is to automate any action, not just the example i gave. It could be about switching spreadsheets (Ex: sheets("Contacts").select), or running a macro behind a button (Ex: call add_new_client).

So it is not about "controls" only.

[Norie]

As far as I know CallByName isn't just for controls, I based what I posted in what you posted?

Why can't you write code in the 'normal' way?

What sort of automation are you aiming for?

[Henri F]

Is it possible to post a print screen to show what I am designing?

[Henri F]

Please see attachment.

[Henri F]

What I am trying to do is provide users with a set of icons they can use to automate any tasks in the spreadsheet. I expect most uses will be to go to specific part of the spreadsheet (Range("C2").select), call function behind buttons (call xyz_sub), type data, etc.

can callbyname do these tasks?

[Henri F]

I actually found my solution. It is as follows:

quote = chr(34)
text = "Range(" & quote & "B5" & quote & ").select"

call execute_code(text)

Sub execute_code(code_string)
'Create a new module in the current workbook, enter the code, run and remove the new module

On Error Resume Next

'Delete module in case it already exists
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("NewModule")
ThisWorkbook.VBProject.VBComponents.Remove VBComp
On Error GoTo 0

Dim LineNum As Long


Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "NewModule"

'Add the code lines
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub MyNewProcedure()" & Chr(13) & _
code_string & Chr(13) & _
"End Sub"
End With

'Run the new module
Application.Run "MyNewProcedure"

'Remove the new module
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub


Nora, thank you so much for your quick answers.

[Norie]

I thought you didn't want to create code on the fly?

PS Can you add code tags?

[Henri F]

Yes, But one has to compromise at times...

But I would still prefer a one line solution

[Izandol]

There is no one line solution for VBA. (I understand I will simply be another nay sayer to you )

Your solution requires that the user has trusted access to the VBA Project in the Trust Center settings. I think you will also run into occasional errors due to timing problems that will be very difficult to debug because you are adding code to the same project in which you are running code.

[Henri F]

Well, running one line of code at a time does the job for now. I will soon try for more and will let you know.

My motto is: When there is a will, there is a way...

VFP was really such a great tool. It is too bad Microsoft killed it.