excel-vba Using xlVeryHidden Sheets


Example

Worksheets in excel have three options for the Visible property. These options are represented by constants in the xlSheetVisibility enumeration and are as follows:

  1. xlVisible or xlSheetVisible value: -1 (the default for new sheets)
  2. xlHidden or xlSheetHidden value: 0
  3. xlVeryHidden xlSheetVeryHidden value: 2

Visible sheets represent the default visibility for sheets. They are visible in the sheet tab bar and can be freely selected and viewed. Hidden sheets are hidden from the sheet tab bar and are thus not selectable. However, hidden sheets can be unhidden from the excel window by right clicking on the sheet tabs and selecting "Unhide"

Very Hidden sheets, on the other hand, are only accessible through the Visual Basic Editor. This makes them an incredibly useful tool for storing data across instances of excel as well as storing data that should be hidden from end users. The sheets can be accessed by named reference within VBA code, allowing easy use of the stored data.

To manually change a worksheet's .Visible property to xlSheetVeryHidden, open the VBE's Properties window (F4), select the worksheet you want to change and use the drop-down in the thirteenth row to make your selection.

worksheet_properties_window_visible

To change a worksheet's .Visible property to xlSheetVeryHidden¹ in code, similarly access the .Visible property and assign a new value.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ Both xlVeryHidden and xlSheetVeryHidden return a numerical value of 2 (they are interchangeable).