VBA - Execute string as command in Excel

Question

In the last 30 minutes I am trying to execute a string in VBA as a command. The command is like this and it runs ok:

activesheet.chb_g_ba1.visible = true

What I am trying to do is to set "chb_g_ba1" as a variable, because there are "chb_g_ba2", "chb_g_ba3", etc as well.

What I have tried so far - things like:

dim l_counter as long: l_counter = 1
Evaluate (CStr("me.chb_g_ba" & l_counter & ".visible = true"))

Or even Eval

Eval (CStr("me.chb_g_ba" & l_counter & ".visible = true"))

Eval is a function in MS Access VBA, but obviously it is not present in Excel. Pretty much my question is extremely similar to this one:VBA how to run a string as a line of code, but it is for Excel, not for Access.

So, any ideas are welcomed! :)

Assuming that's a control on the sheet, use: activesheet.oleobjects("chb_g_ba1").visible = true — Rory
– Rory, Commented Apr 13, 2016 at 10:28
Ok, thank you. I have deleted my answer just to double check it. But it really works. The Question was - "Is it possible to do it like Eval()" — Vityata
– Vityata, Commented Apr 13, 2016 at 10:32
I don't know what answer you are talking about. There is no Eval or equivalent for this in Excel VBA. Nor can I think of a good reason to need one, to be honest. — Rory
– Rory, Commented Apr 13, 2016 at 10:36
You need to access the object contained in the OLEobject: activesheet.oleobjects("chb_g_ba1").Object.value = True — Rory
– Rory, Commented Apr 13, 2016 at 11:29

Karthick Gunasekaran · Accepted Answer · 2018-07-28 08:56:06Z

1

Credit to CPearson:

There is a way to do that but we have to add a new module dynamically with required code and delete while finished.

References: Microsoft Visual Basic for Applications Extensibility 5.3

Sub test()
    Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    VBComp.Name = "NewModule"
    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
    Dim l_counter As Long
    l_counter = 1
    With VBCodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, _
        "Sub MyNewProcedure()" & Chr(13) & "UserForm1.chb_g_ba" & l_counter & ".Visible = True" & Chr(13) & "End Sub"
    End With
    'run the new module
    Application.Run "MyNewProcedure"
    UserForm1.Show
    'Delete the created module
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

edited Jul 28, 2018 at 8:56

user3956566

answered Apr 13, 2016 at 10:51

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rory Over a year ago

It also requires trusted access to the vb project, which is not set by default.

Vityata Over a year ago

Thanks, Karthick. But it looks like overkill. I just wanted to eliminate writing 10 lines of code.

Karthick Gunasekaran Over a year ago

you can add more lines by using the chr(13)

Vityata Over a year ago

I mean, the whole idea was not write If Else 10 times. But thanks :)

Collectives™ on Stack Overflow

VBA - Execute string as command in Excel

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related