在VBA中动态创建动态数组 [英] Dynamically Create Dynamic Arrays in VBA

问题描述

我的目标是使用一组名称在VBA中创建动态变量，代码如下：

  Sub mymacro ）
 Dim names（）
 names = Array（cat_code（），dog_code（），eagle_code（））
对于每个c在名称
 Dim c As Integer 
 Next 
 end sub 
  

当然，我的真实姓名数组有数以百计的动物，所以对于每一个人来说，这样做比较无聊就是 Dim 。我得到的错误是编译错误：当前范围内的重复声明

什么是最好的可行解决方案我的目标是

解决方案

您正在获取的编译错误是由当前范围内的重复声明引起的。 b
$ b

换句话说：这意味着你声明了多个具有相同名称的变量。

在您的模块之上添加 Option Explicit 语句需要声明您使用的每个变量。当您收到此错误时，非常有用，因为您可以快速扫描代码以突出显示的行的重复声明 Dim< variable_name>

这是一个示例，说明您为何收到错误：

  Option Explicit 
 
 Sub Main（）
 
 Dim c As Worksheet 
对于每个c in Sheets 
 Dim c As Long'你将在这里得到一个错误，因为
'一个名为c的变量已经在sub 
'中声明，您不能有两个名为c的变量。 
对于c = 1到ws.Range（A& Rows.Count）.End（xlUp）.Row 
'一些代码
下一个c 
下一个
 
 End Sub 
  

---

没有轻松解决您的问题。如果您更好地解释您想要实现的目标，我们将能够为您的问题提供更好的解决方案。

有一个解决方法实现你想要的，但如果你不确定你实际在做什么，我不会建议这样做;）。以下代码将在您当前的VBA项目中创建一个新模块。在使用动物名称迭代数组时，它会将新行写入 Module2 ，所以在执行后，您的模块二将是

为了这个代码的顺序，你必须添加对 Microsoft Visual Basic for Applications Extensibility 5.3的引用。你可以通过选择 Tools >> ; 在VBE窗口中的引用。

此外，这要求您信任访问VBA项目对象模型。转到Excel设置>>信任中心>>宏>>勾选对VBA项目对象模型的信任访问。

运行示例代码。

  Option Explicit 
 
'这个VBA项目需要
'1  - 引用Microsoft Visual Basic应用程序可扩展性5.3 
'通过工具>参考
'
'2  - 信任访问VBA项目对象模型
'在电子表格视图中转到Excel（应用程序选项）>>信托中心>>宏设置
'勾选对VBA项目对象模型的信任访问
 
 Sub mymacro（）
 Dim names 
 names = Array（cat_code，dog_code eagle_code）
 Dim c As Variant 
 AddAModule 
对于每个c在名称
'动态创建数组
 WriteToModule CStr（c）
下一个
 CloseModule 
 End Sub 
 
 
私有子AddAModule（）
 Dim VBProj作为VBIDE.VBProject 
 Dim VBComp作为VBIDE.vbComponent 
 Dim CodeMod As VBIDE.CodeModule 
 
设置VBProj = ThisWorkbook.VBProject 
设置VBComp = VBProj.VBComponents.Add（vbext_ct_StdModule）
设置CodeMod = VBComp.CodeModule 
 
与CodeMod 
 .DeleteLines 1，.CountOfLines 
 .InsertLines 1，Public Sub DynamicallyCreatedArrays（）
 .InsertLines 2，'代码为子
结束
 End Sub 
 
 Private Sub WriteToModule（arrayName As String）
 W ith ActiveWorkbook.VBProject.VBComponents（Module2）。CodeModule 
 .InsertLines .CountOfLines + 2，Dim& arrayName& as Variant
 End With 
 End Sub 
 
 Private Sub CloseModule（）
 With ActiveWorkbook.VBProject.VBComponents（Module2）。CodeModule 
 .InsertLines .CountOfLines + 2，End Sub
 End with 
 End Sub 
  

My objective is to use an array of names to create dynamic variables in VBA, heres the code:

Sub mymacro()
Dim names()
names = Array("cat_code()", "dog_code()", "eagle_code()")
For Each c In names
Dim c As Integer
Next
end sub

And of course my real name array has hundreds of animals so it would be rather boring doing Dim for each and every one of them. The error I'm getting is Compile Error: Duplicate declaration in current scope

What is the best feasible solution to my objective?

解决方案

The compile error you are getting is caused by a duplicate declaration in the current scope.

In other words: this means you are declaring more than one variable with the same name.

Adding an Option Explicit statement on top of you modules requires you to declare each variable you use. It's very helpful when you receive this error because you can quickly scan your code for duplicate declaration of the highlighted line Dim <variable_name>

This is a sample demonstrating why you are getting the error:

Option Explicit

Sub Main()

    Dim c As Worksheet
    For Each c In Sheets
        Dim c As Long   ' you are going to get an error in here because
                        ' a variable named: c, is already declared within the sub
                        ' you can't have two variables named: c.
        For c = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row
            ' some code
        Next c
    Next

End Sub

---

There is no easy work around your problem. We would have been able to provide a better solution to your problem if you better explain what you are trying to achieve.

There is a workaround to achieve what you want but I wouldn't recommend doing it this way if you are unsure of you are actually doing ;). The below code will create a new module in your current VBA project. While iterating over the array with the animal names it will be writing new lines to Module2 so after the execution your module two will be

In order for this code to work you have to add references to Microsoft Visual Basic for Applications Extensibility 5.3". You can do that by selectingTools>>References` in the VBE window.

Also, this requires you to Trust Access to VBA Project Object Model. Go to Excel Settings >> Trust Centre >> Macros >> tick Trust Access To VBA Project Object Model.

Run the sample code.

Option Explicit

' this VBA project requires
' 1 - references to Microsoft Visual Basic For Applications Extensibility 5.3
'     add it via Tools > References
'
' 2 - trust access to VBA project object model
'     In spreadsheet view go to Excel(application options) >> Trust Centre >> Macro Settings
'     tick the Trust Access to VBA project object model

Sub mymacro()
    Dim names
    names = Array("cat_code", "dog_code", "eagle_code")
    Dim c As Variant
    AddAModule
    For Each c In names
        ' dynamically create arrays
        WriteToModule CStr(c)
    Next
    CloseModule
End Sub


Private Sub AddAModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.vbComponent
    Dim CodeMod As VBIDE.CodeModule

    Set VBProj = ThisWorkbook.VBProject
    Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        .DeleteLines 1, .CountOfLines
        .InsertLines 1, "Public Sub DynamicallyCreatedArrays()"
        .InsertLines 2, "    ' code for the sub"
    End With
End Sub

Private Sub WriteToModule(arrayName As String)
    With ActiveWorkbook.VBProject.VBComponents("Module2").CodeModule
        .InsertLines .CountOfLines + 2, "    Dim " & arrayName & " as Variant"
    End With
End Sub

Private Sub CloseModule()
    With ActiveWorkbook.VBProject.VBComponents("Module2").CodeModule
        .InsertLines .CountOfLines + 2, "End Sub"
    End With
End Sub

这篇关于在VBA中动态创建动态数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！