VBA Name statement is used to give a name to a variable, constant, array, or procedure in VBA. In this blog post, we will dive into the purpose, syntax, examples, important notes & remarks of the Name statement in VBA.
VBA Name Statement
Purpose of the Name Statement in VBA
The Name statement in VBA has two main purposes:
- To create an alias or an alternative name for a variable, constant, array, or procedure
- To refer to a variable, constant, array, or procedure in a different VBA module
This can be particularly useful when you have multiple VBA modules that need to access the same variable or procedure. Instead of using the full name, you can use the alias name that has been assigned using the Name statement.
Syntax
The syntax for the Name statement is as follows:
Name OldPathName As NewPathName
Here, OldPathName refers to the existing name that you want to change, and NewPathName refers to the new name that you want to assign.
Examples on Name Statement using VBA
Creating an Alias for a Variable
Suppose you have a variable named total in a VBA module named “Module1.” Now, if you want to access this variable in another module called “Module2,” you can use the Name statement to create an alias for total.
Name total As sum
In this case, sum will be the alias for the variable total, and you can use it in Module2 without any confusion with other variables named total.
Renaming a File
The Name statement can also be used to rename a file in VBA.
Name "C:\Users\Documents\oldfile.txt" As "C:\Users\Documents ewfile.txt"
This example will rename the file “oldfile.txt” to “newfile.txt” in the specified directory.
Changing Procedure Names
In VBA, procedure names must be unique within a module. However, you can use the Name statement to assign an alias name to a procedure, allowing you to have multiple procedures with the same name within a module.
Sub MyProcedure() 'Code goes here End Sub Name MyProcedure As MyProc Sub MyProc() 'Code goes here End Sub
Here, MyProc is the alias name for the procedure MyProcedure.
Changing Worksheet Names
You can use the Name statement to rename a worksheet in your Excel workbook.
Name Worksheets("Sheet1").Name As "NewName"
This will change the name of “Sheet1” to “NewName” in your workbook.
Renaming Controls on Forms
If you have a userform in your VBA project with multiple controls, you can use the Name statement to change the names of these controls.
Name UserForm1!TextBox1 As "NewName"
This will change the name of the textbox ‘TextBox1’ on UserForm1 to “NewName.”
Important Notes & Remarks
- The Name statement can only be used within a VBA module and not in a worksheet or UserForm
- The Name statement does not work with built-in Excel objects, such as worksheets, chart objects, etc.
- The Name statement is not case sensitive
- If you use the Name statement more than once for the same identifier, the newest assignment will take precedence
- The Name statement can also be used in VBA code to rename controls and objects at run-time
Conclusion
In this blog post, we covered the purpose, syntax, examples, and important notes & remarks of the Name statement in VBA. This statement has a variety of use cases and can be a helpful tool in simplifying your VBA code. Have you used the Name statement in your VBA projects? Share your thoughts and experiences in the comments below.
What did you think of this blog post? Did it provide helpful information about the Name statement in VBA? We would love to hear your feedback and views. Leave a comment below and let us know your thoughts.