0

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! :)

Community
  • 1
  • 1
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 2
    Assuming that's a control on the sheet, use: `activesheet.oleobjects("chb_g_ba1").visible = true` – Rory Apr 13 '16 at 10:28
  • 1
    No, other than actually writing the code to a module. – Rory Apr 13 '16 at 10:31
  • 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 Apr 13 '16 at 10:32
  • 1
    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 Apr 13 '16 at 10:36
  • I meant the comment, before your second comment. – Vityata Apr 13 '16 at 10:36
  • @Rory do you know why the following does not work - activesheet.oleobjects("chb_g_ba1").value = true – Vityata Apr 13 '16 at 11:26
  • 1
    You need to access the object contained in the OLEobject: `activesheet.oleobjects("chb_g_ba1").Object.value = True` – Rory Apr 13 '16 at 11:29

1 Answers1

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
Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25