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 c
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?
Best Answer
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 lineDim <variable_name>
This is a sample demonstrating why you are getting the error:
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 beIn 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 selecting
Tools>>
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.