REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA StatementsTags: , Last Updated: September 28, 2023

Leave A Comment