VBA - 在Excel中执行字符串作为命令 [英] VBA - Execute string as command in Excel

问题描述

在过去的30分钟内，我试图在VBA中执行一个字符串作为命令。命令是这样的，它运行正常：

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

我想要做的是将chb_g_ba1设置为变量，因为还有chb_g_ba2，chb_g_ba3等

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"))

甚至Eval

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

Eval是MS Access VBA中的一个功能，但显然它不存在于Excel中。我的问题与我非常相似： VBA如何运行字符串作为一行代码，但它是为Excel而不是Access。

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

推荐答案

信用于Pearson软件咨询

有一种方法可以做到这一点，但是我们必须在完成时动态添加一个需要的代码和删除的新模块。

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

参考文献：Microsoft Visual Basic应用程序可扩展性5.3

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

这篇关于VBA - 在Excel中执行字符串作为命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！