Developer

Render an Excel Defined Name invisible to users

Defined Names don't have to be visible to everyone, if you use VBA to create them.

Last week, I showed you a quick trick for creating a Defined Name using the Name Box (the control at the left end of the Formula bar). Simply type the name in the Name Box and press [Enter]. Whether you use the Name Box or the Define Names dialog box to create a Defined Name, all names created manually are visible to the user. It stands to reason that you might want to hide a name, probably to protect it. The only way to hide a Defined Name is to create it using VBA's Add method: ThisWorkbook.Names.Add name, refersto, visible There are other arguments, but for our purposes, we need only the first three arguments, which are self-explanatory. (To learn more about the Add method's other arguments, run a search in Excel's VBA Help files on Add method.) Now, let's define an invisible Defined Name using the Add method, as follows:

  1. Determine the cell reference and name — we'll refer to cell C8 in Sheet2 using the name GrandTotal.
  2. In the Visual Basic Editor (VBE), open a new module by selecting Module from the Insert menu.
  3. In the new module, enter the procedure shown below.
  4. To execute the procedure, position the cursor anywhere inside it and press [F5].
Sub DefineName() 'Create an invisible name. ThisWorkbook.Names.Add "GrandTotal", _ Worksheets("Sheet2").Range("C8"), False
End Sub
The DefineName() subprocedure (macro) defines GrandTotal at C8, but if you return to the sheet, you won't see GrandTotal listed in the Name Box or the Define Names dialog box. You can prove to yourself that the name exists by selecting a cell and entering the reference =GrandTotal. The visible argument is optional, so you can also use this procedure to create a visible name. A word of warning: This Add method will replace an existing name without warning and that may or may not be what you want. To delete a Defined Name using VBA, run the following statement:
ThisWorkbook.Names(name).Delete
If you want to use VBA to create Defined Names often, pass the arguments to the procedure so you can reuse it:
Sub DefineName2(nme As String, sht As String, _
 ca As String, visble As Boolean)
  'Create a defined name.
  ThisWorkbook.Names.Add nme, _
   Worksheets(sht).Range(ca), visble
End Sub
Creating Defined Names is easy, whether you go the manual route or use VBA. Just remember that you must use VBA to create a hidden or invisible Defined Name.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox