Assign a name to an object in Excel – How to

Assign a name to an object in Excel – How to

Assign a name to an object in Excel - How to

This lesson shows you how to assign a name to an object in Excel.

When you work with objects in Excel, it helps to give them unique names. Especially, if you plan to use them in code. That is probably the main purpose for naming an object.

Steps to assign a name to an object in Excel

Follow the steps below to assign a name to an object in Excel.

Steps 1 and 2
Steps 1 and 2 - Assign a name - Sheet with selected object and unique name in Name Box
Sheet with selected object and unique name in the Name Box.
  1. Select your object.
  2. In the Name Box, enter a unique name for your object and press Enter.

There you have it. You just named an object in Excel. That is all to it.

Overall, we like to prefix our names with a three-letter string, relating to the object. So, we use shp for shape. Other examples include txt for text, rng for range, and ref for reference. Moreover, spaces in names are okay. But, we suggest using underscores instead of spaces.


Test your object using VBA

Here are the steps to test your named object in VBA.

Steps 3 and 4
Steps 3 and 4 - Assign a name - Steps to show VBA window
Steps to show VBA window.
  1. Show the Developer ribbon.
  2. Under the Code group in the Developer ribbon, select Visual Basic. This will open the VBA window.
Steps 5 and 6
Steps 5 and 6 - Show Immediate Window
Show the Immediate Window.
  1. Click View from the main menu of the VBA window.
  2. From the View menu, select Immediate Window.

The Immediate Window will appear in the gray area. Also, it allows us to test single lines of code.

Now, let’s create a subroutine to test certain properties of our named object.

Step 7
Step 7 - Assign a name - Steps to show module window for current sheet
Steps to show module window for current sheet.
  1. Under the Project section, double-click on the sheet containing your object. For the most part, any code we create or test will pertain to this sheet.
Step 8
Step 8 - Sheet code
Sheet code to check if an object is visible.
  1. Enter the following code in the code window for your sheet.
Sub Test()
    Debug.Print Shapes("shpTriangle").Visible
End Sub
Steps 9 and 10
Steps 9 and 10 - Assign a name - run subroutine
Steps to run a subroutinue
  1. Click Run from the main menu.
  2. Under the Run menu, select Run Sub/UserForm.

If you typed everything correctly, then you should see a result in the Immediate Window.

Basically, Debug.Print sends results of a single line of code to the Immediate Window. In addition, you can run certian Debug.Print routines directly in the Immediate Window.

Below is the result of Debug.Print Shapes(“shpTriangle”).Visible, which tells us if our object is visible or not.

As a result of the Debug.Print Shapes(“shpTriangle”).Visible instruction, the Immediate Window displays -1. In VBA, -1 represents True, and 0 represents False. Essentially, any number that is not zero is typically looked at as True.

If you want to hide your shape, then try the following code.

Sub Test()
    Shapes("shpTriangle").Visible = False
    Debug.Print Shapes("shpTriangle").Visible
End Sub

So, after you run this code, line 2 hides the shape, and line 3 should display 0 in the Immediate Window.

By the way, make sure you unhide your object by changing line 2 to Shapes(“shpTriangle”).Visible = True. Also, run it after the change.


Success!

Were you successful in this task? If not, then give it another shot.

Also, a great illustration of using objects in Excel is our Wingding Match game.


Related


Education home  Excel Effects home