Como Ocultar rangos con nombre en Excel: 4 formas sencillas

Los rangos con nombre son una característica útil de Excel. A menudo los utilizamos para crear herramientas más complejas que podemos compartir con otros usuarios. Por lo tanto, esos rangos con nombre se convierten en una parte fundamental de nuestra hoja de cálculo. Desafortunadamente, si esos usuarios modifican o eliminan los rangos nombrados, los cálculos pueden interrumpirse. Entonces, en esta publicación, quiero compartir 4 formas en que puedes ocultar rangos con nombre (y, por supuesto, cómo mostrarlos nuevamente).

Tabla de contenido

• Ocultar rangos con nombre de las fórmulas (Método n.° 1)
• Ocultar rangos con nombres con macros de VBA (Método n.° 2)
• Ocultar rangos con nombre individuales
• Ocultar todos los rangos con nombre
• Listar todos los rangos con nombre ocultos
• Mostrar rangos con nombre individuales
• Mostrar todos los rangos con nombre
• Ocultar rangos con nombres con Office Scripts (Método n.° 3)
• Ocultar rangos con nombre individuales
• Ocultar todos los rangos con nombre
• Listar todos los rangos con nombre ocultos
• Mostrar rangos con nombre individuales
• Mostrar todos los rangos con nombre
• Cambiar la estructura del archivo (Método #4)
• Alcance de la hoja frente al alcance del libro de trabajo, rangos con nombres
• Rangos con nombres desconocidos
• Envolver

Ocultar rangos con nombre de las fórmulas (Método n.° 1)

Cuando creamos rangos con nombre, se encuentran en tres lugares de la interfaz de usuario.

1. En fórmulas (si se usan)
2. Nombre de Caja
3. Administrador de nombres

Para evitar que los usuarios vean fórmulas podemos:

• Formatee la celda que contiene fórmulas para que esté oculta.
  
• Luego proteja la hoja de trabajo haciendo clic en Revisar hoja protegida en la cinta

No aparecerán fórmulas en la barra de fórmulas si esa celda está seleccionada.

Sin embargo, esto no ayuda con el Cuadro de nombres [1] o el Administrador de nombres [2].

En el resto de esta publicación, veremos métodos para ocultar aún más rangos con nombres, ya que no queremos que estén visibles ni en el Cuadro de nombres ni en el Administrador de nombres.

La interfaz de usuario no proporciona ninguna herramienta que nos ayude con esto; por lo tanto, debemos buscar otros métodos más avanzados.

Ocultar rangos con nombres con macros de VBA (Método n.° 2)

Comencemos mirando las macros de VBA. A continuación se muestran cinco ejemplos de código para ocultar, enumerar y mostrar rangos con nombres. Tenga en cuenta que estos solo funcionarán en el escritorio de Excel.

Ocultar rangos con nombre individuales

El siguiente ejemplo oculta un rango con nombre individual llamado Ejemplo1 del libro de trabajo activo.

Sub hideIndividualNamedRange()'Hide an individual named range from the active workbookActiveWorkbook.Names("Example1").Visible = FalseEnd Sub

Ocultar todos los rangos con nombre

El siguiente código recorre cada rango con nombre en el libro activo y los oculta todos.

Sub hideAllNamedRanges()Dim wb As WorkbookDim nm As NameSet wb = ActiveWorkbook'Loop through all names and hide themFor Each nm In wb.Names    nm.Visible = FalseNext nmEnd Sub

Listar todos los rangos con nombre ocultos

Habiendo ocultado rangos con nombres, es posible que queramos saber cuáles son esos nombres para poder usarlos o mostrarlos. El siguiente código enumera todos los rangos con nombres ocultos en el libro activo. La lista comienza en la celda A1 de la hoja activa. Las macros no se pueden deshacer, así que comience una nueva hoja de trabajo para evitar sobrescribir todo lo que desee conservar.

Si ve elementos en la lista que no esperaba, consulte la sección Rangos con nombres desconocidos al final de esta publicación.

Sub listAllHiddenNamedRanges()Dim wb As WorkbookDim ws As WorksheetDim nm As NameDim i As IntegerSet wb = ActiveWorkbookSet ws = ActiveSheet'Loop through all namesFor Each nm In wb.Names    'If names are hidden then list them    If nm.Visible = False Then        ws.Range("A1").Offset(i, 0).Value = nm.Name        i = i + 1    End IfNext nmEnd Sub

Mostrar rangos con nombre individuales

Después de haber ocultado algunos rangos con nombre, la siguiente pregunta es: ¿cómo puedo mostrarlos nuevamente? El siguiente código muestra un rango con nombre llamado Ejemplo1 .

Sub unhideIndividualNamedRange()'Unhide an individual named range from the active workbookActiveWorkbook.Names("Example1").Visible = TrueEnd Sub

Mostrar todos los rangos con nombre

El siguiente ejemplo muestra todos los rangos con nombre del libro activo.

Sub unhideAllNamedRanges()Dim wb As WorkbookDim nm As NameSet wb = ActiveWorkbook'Loop through all names and hide themFor Each nm In wb.Names    nm.Visible = TrueNext nmEnd Sub

Si desea saber más sobre el uso de rangos con nombre con VBA, consulte nuestra guía: Rangos con nombre de VBA: métodos de propiedades comunes

Ocultar rangos con nombres con Office Scripts (Método n.° 3)

Ahora pasemos a los scripts de Office. Estos funcionarán en Excel en línea y, con suerte, pronto funcionarán completamente en el escritorio. Al momento de escribir este artículo, existe soporte parcial para Office Scripts en Excel Desktop.

Los códigos siguientes reflejan los de la sección de macros de VBA anterior.

Ocultar rangos con nombre individuales

El siguiente ejemplo oculta un rango con nombre llamado Ejemplo1 del Libro activo.

function main(workbook: ExcelScript.Workbook) {//Hide an individual named range from the active workbookworkbook.getNamedItem("Example1").setVisible(false)}

Ocultar todos los rangos con nombre

El siguiente código oculta todos los rangos con nombre en un libro de trabajo.

function main(workbook: ExcelScript.Workbook) {//Declare variable to hold array of nameslet nm = workbook.getNames()//Loop through array and hide namesfor (let i = 0; i  nm.length; i++) {  nm[i].setVisible(false)}}

Listar todos los rangos con nombre ocultos

El siguiente código de ejemplo enumera los rangos con nombres ocultos en un libro de trabajo. La lista comienza en la celda A1 de la hoja activa, así que asegúrese de que no sobrescriba nada importante antes de ejecutar el script.

Si ve elementos con nombres extraños que no esperaba, consulte la sección Rangos con nombres desconocidos a continuación.

function main(workbook: ExcelScript.Workbook) {//Declare variable to hold array of nameslet nm = workbook.getNames()//Loop through array of namesfor (let i = ; i  nm.length; i++) {  //Check if name is hidden  if (nm[i].getVisible() == false) {      //Add name to the active worksheet      workbook.getActiveWorksheet().getRange("A1").        getOffsetRange(i,0).setValue(nm[i].getName())          }    }}

Mostrar rangos con nombre individuales

El siguiente código muestra un rango con nombre llamado Ejemplo1 .

function main(workbook: ExcelScript.Workbook) {//Unhide an individual named range from the active workbookworkbook.getNamedItem("Example1").setVisible(true)}

Mostrar todos los rangos con nombre

El siguiente código muestra todos los rangos con nombre en el libro de trabajo.

function main(workbook: ExcelScript.Workbook) {//Declare variable to hold array of nameslet nm = workbook.getNames()//Loop through array and unhide namesfor (let i = 0; i  nm.length; i++) {  nm[i].setVisible(true)}}

Los scripts de Office siguen siendo una forma relativamente nueva de trabajar con Excel. Para obtener más información, consulte nuestras otras publicaciones sobre Office Scripts .

Cambiar la estructura del archivo (Método #4)

Es poco probable que utilice este último método con frecuencia. Sin embargo, nunca se sabe; podría ser útil en algún momento. Para ocultar rangos con nombres usando este método, descomprimimos la estructura de archivos del libro de Excel y cambiamos el código fuente.

Tenga en cuenta que este método puede dañar su archivo si sale mal. Haga una copia de seguridad para intentar este método.

1. Cambie el nombre del libro de Excel para que la extensión del archivo sea .zip , en lugar de .xlsx o .xlsm
2. Navegue hasta el archivo zip y busque el archivo llamado xlworkbook.xml
3. Copie y pegue el archivo workbook.xml fuera de la carpeta zip
4. Abra el archivo XML copiado usando un editor de texto como el Bloc de notas
5. Dentro del texto, encontrará un código similar al siguiente
   nombredefinido=”Ejemplo1″Hoja1!$A$1/nombredefinido
   En ese código, podemos agregar una propiedad para ocultar el rango con nombre (resaltado en negrita a continuación)
   nombredefinido=”Ejemplo1″ oculto=”1″ Hoja1!$A$1/nombredefinido
6. Guarde el archivo XML. Luego, copie y pegue el archivo nuevamente en la carpeta comprimida xl .
7. Cambie la extensión del archivo zip nuevamente a .xlsx o .xlsm
8. Vuelva a abrir el libro de Excel. Todos los nombres configurados con hide=”1″ estarán ocultos.

La mayoría de los usuarios no saben que los archivos de Excel son carpetas comprimidas. Si esto es nuevo para usted, puede que le resulte interesante .

Alcance de la hoja frente al alcance del libro de trabajo, rangos con nombres

Los ejemplos anteriores utilizan rangos con nombres con ámbito de libro de trabajo. Sin embargo, los rangos con nombre también pueden tener el alcance de la hoja de trabajo. Para estos, el método de hacer referencia al nombre cambia para incluir el nombre de la hoja.

Lo siguiente hace referencia a un libro llamado rango:

ActiveWorkbook.Names("sheetExample").Visible = False

Si bien lo siguiente hace referencia a un rango con nombre cuyo ámbito es la Hoja1

ActiveWorkbook.Names("'Sheet1'!sheetExample").Visible = False

La sección subrayada identifica la hoja a la que se refiere el rango nombrado.

Rangos con nombres desconocidos

Cuando enumera o muestra todos los rangos con nombre, es posible que se sorprenda al encontrar elementos que usted no creó. Estos son elementos que Excel crea en segundo plano para su propio uso.

Por ejemplo, si ha aplicado un filtro, el rango con nombre podría aparecer como 'Hoja1'._FilterDatabase

O, si tiene una función de matriz dinámica en el libro de trabajo, el rango con nombre podría aparecer como _xlfn._xlws.SORT

No elimine ninguno de estos, así es como Excel controla otras partes del libro.

Envolver

Como hemos visto, para ocultar completamente los rangos con nombre, necesitamos un enfoque de 2 pasos:

1. Oculte los nombres usando VBA, Office Scripts o cambiando la estructura del archivo (Método #2, #3 y #4)
2. Establezca las fórmulas como Ocultas en el cuadro de diálogo Formato de celdas (Método n.° 1)

Después de aplicar estos dos pasos, los usuarios ahora verán los rangos con nombre. Los nombres existirán pero no serán visibles en el cuadro de nombres ni en el administrador de nombres ni en ninguna fórmula.

---