Dynamically creating dynamic arrays in VBA

Question

My goal is to use an array of names to create dynamic variables in VBA, this is 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 array of names has hundreds of animals, so it would be pretty boring to do Dim for each of them. The error I get is Compile Error: Duplicate declaration in current scope

What is the best possible solution for my purpose?

Answer 1

A compilation error caused by duplicate ads in the current area.

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

Adding an Option Explicit statement on top of your modules requires declaring each variable that you use. This is very useful when getting this error, because you can quickly scan your code to re-declare the highlighted line Dim <variable_name>

This is an example 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 

---

No problem with your problem. We could 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 would not recommend doing it this way if you are not sure what you are actually doing;). The following code will create a new module in your current VBA project. Iterating over an array with animal names, it will write new lines in Module2 , so after executing your module two will

For this code to work, you must add links to Microsoft Visual Basic for Applications Extensibility 5.3". You can do that by selecting Tools >> Links` in the VBE window.

In addition, this requires Trust Access to VBA Project Object Model . Go to Excel Settings → Trust Center → Macros → check "Trust access to the 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 

Answer 2

VBA cannot really do what you are trying to do without falling into the terrible world of complications.

How about using a VBA Collection object? You will need to create a simple class to store the number, because VBA collections work with references, not values.

So, I created a class and assigned its name " AnimalCounter ", with this content:

 Public Counter As Integer 

Then your macro will become something like this:

 Sub mymacro() Dim coll As New Collection Dim c As Variant Dim ac As AnimalCounter For Each c In Array("cat", "dog", "eagle") Set ac = New AnimalCounter coll.Add ac, c Next Debug.Print coll("cat").Counter ' what in "cat"? coll("dog").Counter = coll("dog").Counter + 1 ' update "dog" by one Debug.Print coll("dog").Counter ' "dog" should now be one more End Sub 

If you need arrays, put the array in a class. Or another Collection , maybe?

Answer 3

Mike Woodhouse has the correct idea of ​​using Collection with animal keys. I add two notes:

First, I would recommend using Dictionary instead. It is faster than Collection , and provides explicit access to the Keys and Items collections. With Collection , there really is no way to get the keys, since the main goal is an ordered list of elements, not the order-agnostic hash attribute, as with Dictionary .

For early use of the Dictionary type, add a link to the Microsoft Scripting Runtime executable.

Secondly, do not use the array for individual animals! . The reason is that arrays in VBA use semantics in meaning (see Collections in VBA - Overview , Values ​​and References in VBA , Array Designation Rules for more information). In short, every time you retrieve an instance of an array from the containing Collection or Dictionary , you will get a new copy of the entire array. Thus, any changes you make to the contents of this array do not affect the actual array in Dictionary or Collection . To get around this, use Collection instead. This will use semantics by reference and make it easy to add new elements.

So here is what you would like to do:

 Sub ReadCodes() Dim ws As Worksheet Dim strAnimalName As String Dim dctAnimalCodes As New Dictionary Dim colAnimalCodes As Collection Dim lngAnimalCode As Long Set ws = Worksheets("Animal Code Data") For iRow = 1 To ws.UsedRange.Rows.Count strAnimalName = ws.Cells(iRow, 1) lngAnimalCode = ws.Cells(iRow, 2) ' Easy to check if key exists If Not dctAnimalCodes.Exists(strAnimalName) Then Set dctAnimalCodes(strAnimalName) = New Collection End If ' Getting the collection for this animal Set colAnimalCodes = dctAnimalCodes(strAnimalName) ' Easy appending of new code colAnimalCodes.Add lngAnimalCode Next End Sub