In Excel VBA, “worksheet attributes” usually means the properties of a Worksheet object: values that describe or control a sheet, such as its name, visibility, tab color, and protection state. You read or change them through a worksheet reference, for example ThisWorkbook.Worksheets("Data").Visible.
What worksheet attributes mean in VBA
Excel’s VBA object model represents a workbook as a collection of objects. A workbook contains worksheets, and each worksheet exposes properties, methods, and events. A property is an attribute or setting you can read, and in many cases change. A method performs an action, while an event runs code in response to something happening.
For example, Name is a property that returns a sheet’s tab name; Protect is a method that applies protection; and Worksheet_Change is an event procedure that can respond to edits on that sheet. The word “attribute” is informal here: VBA documentation generally calls these members properties, methods, and events.
A worksheet reference can be obtained by name, position, or code name. The usual name-based form is ThisWorkbook.Worksheets("Data"). ThisWorkbook means the workbook containing the VBA code, while ActiveWorkbook means the workbook currently active in Excel. That distinction matters when more than one workbook is open.
Common Worksheet properties
These are useful worksheet properties to know. Some return simple values; others return objects that you can use to work with cells or formatting.
Namereturns or sets the visible sheet-tab name.CodeNameidentifies the worksheet in the VBA project. It is different from the visible tab name and is read-only through the ordinary Worksheet object model.Indexgives the sheet’s position in the workbook’s tab order. The position can change when sheets are moved or inserted, so it is not a permanent identifier.Visiblereports or sets whether the sheet is visible, hidden, or very hidden.ProtectContents,ProtectDrawingObjects, andProtectScenariosreport protection states for different parts of the sheet.Tab.Colorgets or sets the sheet tab’s color.UsedRangereturns a Range object representing the worksheet’s used area. Its boundary can include cells that were formatted or edited in the past, even if they now look empty.Cells,Rows,Columns, andRangeprovide access to cell ranges on the worksheet.
These properties do not all have the same type. For example, Name is text, ProtectContents is a Boolean value, and UsedRange is an object. Knowing the return type helps you decide whether to compare a value, assign a value, or continue accessing a member of the returned object.
How to inspect worksheet attributes
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, Option+F11 opens it on many keyboards; you may need to hold Fn as well, depending on the keyboard settings. Insert a standard module and place a procedure there. The Immediate window in the editor can display the results of Debug.Print.
Before running a macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z (or the Mac equivalent), so changes made by code may require restoring that copy. The following example only prints information, but this is a useful habit for VBA work in general.
Sub InspectWorksheet()
Dim ws As Worksheet
' Point to a worksheet in the workbook containing this code.
Set ws = ThisWorkbook.Worksheets("Data")
' Print selected properties in the Immediate window.
Debug.Print "Name: " & ws.Name
Debug.Print "Code name: " & ws.CodeName
Debug.Print "Visible value: " & ws.Visible
Debug.Print "Contents protected: " & ws.ProtectContents
Debug.Print "Used range: " & ws.UsedRange.Address
End Sub
Open the Immediate window with Ctrl+G in the Visual Basic Editor on Windows. On Mac, use the editor’s View menu to show the Immediate window if that shortcut differs on your setup. Run the procedure with the cursor inside it and choose Run Sub/UserForm, or use F5 in the editor. The value printed for Visible is an enumeration value, not a descriptive word, so it is often clearer to compare it with the named constants shown below.
Changing a property safely
Properties that are writable can be assigned with the equals sign. For instance, changing Name changes the visible sheet tab name. Excel requires sheet names to be unique within the workbook, no longer than 31 characters, and free of characters Excel does not allow in sheet names, including a colon, slash, backslash, question mark, asterisk, and square brackets. A rename can fail if the proposed name breaks these rules.
This example changes a tab color. It assumes a worksheet named Report already exists. Save a copy before running it because changing a workbook through VBA cannot be reversed with Undo.
Sub SetReportTabColor()
Dim ws As Worksheet
' Select the target sheet explicitly.
Set ws = ThisWorkbook.Worksheets("Report")
' Set the tab to a blue color.
ws.Tab.Color = RGB(0, 112, 192)
End Sub
Explicit references are safer than relying on ActiveSheet. The active sheet can change when a user clicks another tab or when other code activates a different sheet. If the target sheet might not exist, add error handling or check the collection before trying to use it; otherwise VBA raises a run-time error when it cannot find the requested name.
Visibility and the three sheet states
The Visible property uses the XlSheetVisibility constants. xlSheetVisible shows the sheet, xlSheetHidden hides it using Excel’s ordinary hide/unhide behavior, and xlSheetVeryHidden hides it so it does not appear in the standard Unhide dialog. Very hidden sheets can still be made visible through VBA or the Visual Basic Editor’s Properties window. Neither hidden state is a security boundary.
To read the state in plain language, compare the property with the constants rather than relying on its numeric value:
Sub ReportSheetVisibility()
Dim ws As Worksheet
' Use a known worksheet in this workbook.
Set ws = ThisWorkbook.Worksheets("Data")
' Interpret the visibility setting.
If ws.Visible = xlSheetVisible Then
Debug.Print "The sheet is visible."
ElseIf ws.Visible = xlSheetHidden Then
Debug.Print "The sheet is hidden."
ElseIf ws.Visible = xlSheetVeryHidden Then
Debug.Print "The sheet is very hidden."
End If
End Sub
Setting a sheet’s visibility can fail when the workbook’s structure is protected. If a macro needs to hide a sheet, verify that the workbook permits structural changes and that at least one worksheet remains visible. To restore a sheet through code, assign xlSheetVisible to its Visible property.
Worksheet, Worksheets, and Sheets are not interchangeable
A Worksheet is one worksheet object. Worksheets is the collection of worksheet objects in a workbook, while Sheets includes worksheets and chart sheets. This distinction matters in a workbook that has a chart sheet: the sheet-tab collection can contain an item that is not a worksheet.
Use Worksheets("Data") when you specifically need a grid worksheet. Use Sheets when your code is intentionally meant to address any kind of sheet. Avoid depending on a numeric index for long-lived code if sheets may be rearranged. A descriptive tab name or the worksheet’s VBA code name is usually a better way to identify a particular worksheet, though the code name is normally changed through the Visual Basic Editor rather than by assigning to Worksheet.CodeName in regular VBA.
Also distinguish a worksheet property from a workbook property. A worksheet has its own Name and Visible settings; the workbook has properties and methods that apply to the file or its overall structure. If a setting concerns the entire workbook rather than one tab, check that you are using the appropriate object.
Properties, methods, and events in practical code
These member types can look similar in VBA, but they serve different roles. A property is read or assigned as part of an object reference. A method is called to make something happen, often with parentheses when arguments are supplied. An event procedure is placed in the relevant worksheet or workbook code module and runs when its triggering event occurs.
- Read a property:
ws.Name. - Set a writable property:
ws.Visible = xlSheetVisible. - Call a method:
ws.Calculate. - Handle a worksheet event: place a correctly named event procedure, such as
Worksheet_Change, in that worksheet’s code module.
Putting an event procedure in a standard module does not make it a worksheet event. In the Visual Basic Editor, select the target worksheet under the workbook’s Microsoft Excel Objects section, then choose the event from the code window’s procedure list. This placement is different from ordinary macros that you run manually.
Common errors and ways to avoid them
Many worksheet-property errors come from an incorrect reference or an assumption that a property is always writable. The following checks prevent common problems:
- Sheet not found: Confirm spelling and spaces in the tab name.
Worksheets("Data")uses the visible name, not the code name. - Wrong workbook: Prefer
ThisWorkbookwhen the code should operate on the workbook that contains it. UseActiveWorkbookonly when the currently active file is deliberately the target. - Property is read-only: A property such as
CodeNamecan be useful to read but cannot be assigned through the ordinary Worksheet object reference. - Protection blocks a change: Sheet protection and workbook-structure protection are different. A protected worksheet may restrict edits to cells, while workbook structure protection can restrict actions such as moving, adding, or hiding sheets.
- UsedRange seems too large: Formatting or old content can extend Excel’s recorded used area. Do not treat
UsedRangeas a guaranteed count of meaningful data rows. - Index points to a different sheet: Sheet order can change. Use a name or another stable identifier rather than assuming a sheet will always occupy a particular position.
For macros that change a workbook, add appropriate error handling and avoid silently ignoring failures. If you distribute a macro-enabled workbook, save it in a macro-capable format such as .xlsm. Excel for the web can open and edit many macro-enabled workbooks but cannot create, run, or edit VBA macros; use desktop Excel for VBA. Excel for Mac supports VBA, although some features and automation capabilities differ from Windows.
Frequently Asked Questions
What is the difference between a worksheet attribute and a worksheet property?
In everyday questions, “attribute” usually means a property such as Name, Visible, or ProtectContents. VBA’s formal object-model term is property.
How do I get a worksheet by its tab name?
Use a workbook reference followed by the worksheet collection, such as ThisWorkbook.Worksheets("Data"). The text inside the parentheses must match the visible sheet-tab name.
Can I change a worksheet’s CodeName with VBA?
Not by assigning to the ordinary Worksheet.CodeName property; it is read-only through that object model. The code name can be edited in the Visual Basic Editor’s Properties window, subject to the project’s settings.
What is the difference between Hidden and VeryHidden?
Both remove a sheet from the visible tabs. A normally hidden sheet can be restored from Excel’s Unhide dialog, while a very hidden sheet is omitted from that dialog and must be restored through VBA or the Visual Basic Editor.
Are hidden or protected worksheets secure?
No. Hiding a sheet controls whether it is displayed, and worksheet protection is intended to restrict editing rather than provide strong confidentiality. Do not rely on either feature to keep sensitive information secret.
Related
- Excel Solver Binary Constraint: How to Set It Up
- How to Create Goal Seek in Excel: A Practical Guide
- Excel Hangman Solver: Build a Word-Guessing Tool
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Find All Cells With a ValueTo find every cell that matches a particular value in Excel VBA, use the Range.Find method and repeat…
- VBA Functions: Basics, Examples, and How to Write ThemA VBA function is a named block of code that performs a task and returns a value. In…
- Excel VBA Zoom: Set and Manage Worksheet MagnificationTo change the zoom of the active Excel worksheet with VBA, set ActiveWindow.Zoom to a percentage from 10…
- VBA AutoFilter: What True Means and How to Use ItYes, VBA AutoFilter uses True in some related settings, but True is not a universal command to filter…
- VBA Sort Cells in Excel: Code, Options, and Common FixesTo sort cells with VBA in Excel, sort the entire data range by a key column—not just one…
- VBA Dictionary: How It Works, Examples, and Common UsesA VBA Dictionary is a key–value collection: you store a value under a unique key, then retrieve it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.