VBA: Ocultando y Mostrando varias hojas al tiempo.

Sin duda una de las cuestiones más tediosas de Excel es el momento en que queremos Ocultar o Mostrar varias hojas a la vez.. entonces nos damos cuenta que el estándar de Excel únicamente nos permite realizar la acción hoja a hoja!!!...
Algo a mejorar por Microsoft ;-)

La propuesta de hoy es construir un UserForm (formluario de usuario) que gestione fácilmente esta acción múltiple de ocultar/mostrar.

Emplearé un código muy práctico (algo largo) ya expuesto en el blog (ver) que nos habilita la operación simplemente arrastrando los elementos...

Comenzaremos creando un Userform al que he llamado 'frmOcultarMostrar'.
Este formulario tiene los siguientes controles:
Dos ListBox: ListVisible y ListOcultas.
Dos etiquetas
Un Botón: CmdCerrar

El formulario tendría un diseño similar a este:

Insertamos el siguiente código dentro de la ventana de código del UserForm desde el editor de VB:

Dim matrizHojas() As Boolean
Private Sub UserForm_Initialize()
ReDim matrizHojas(Sheets.Count)
'limpiamos los ListBox del formulario
Me.ListVisible.Clear
Me.ListOcultas.Clear

'recorremos todas las hojas del libro
For i = 1 To Sheets.Count
    'llenamos nuestra Array con TRUE o FALSE
    matrizHojas(i) = Sheets(i).Visible
        
    If matrizHojas(i) = True Then
        'si es visible lo cargamos en el ListBox de 'hojas visibles'
        Me.ListVisible.AddItem Sheets(i).Name
    Else
        'si NO es visible lo cargamos en el ListBox de 'hojas ocultas'
        Me.ListOcultas.AddItem Sheets(i).Name
    End If
Next i
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ListOcultas_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As _
    MSForms.DataObject, ByVal x As Single, _
    ByVal Y As Single, ByVal DragState As Long, _
    ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    'este evento se produce cuando se está ejecutando una operación de arrastrar y colocar.
    Cancel = True
    Effect = 1
End Sub
Private Sub ListVisible_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As _
    MSForms.DataObject, ByVal x As Single, _
    ByVal Y As Single, ByVal DragState As Long, _
    ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    'este evento se produce cuando se está ejecutando una operación de arrastrar y colocar.
    Cancel = True
    Effect = 1
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ListOcultas_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, _
    ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal x As Single, _
    ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    'este otro evento se produce cuando el usuario está a punto de colocar o pegar datos en un objeto.
    Cancel = True
    Effect = 1

    Dim iIndex As Long, col As Integer
    With ListVisible
        'determinamos cuál ha sido el elemento seleccionado
        iIndex = ListVisible.ListIndex
        'añadimos un elemento al ListBox de destino...
        ListOcultas.AddItem .List(iIndex, 0), 0
        'incorporamos los valores de las diferentes columnas
        'desde el ListBox de origen al ListBox destino
        For col = 1 To ListVisible.ColumnCount - 1
            ListOcultas.List(0, col) = .List(iIndex, col)
        Next col
    End With
End Sub
Private Sub ListVisible_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, _
    ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal x As Single, _
    ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    'este otro evento se produce cuando el usuario está a punto de colocar o pegar datos en un objeto.
    Cancel = True
    Effect = 1

    Dim iIndex As Long, col As Integer
    With ListOcultas
        'determinamos cuál ha sido el elemento seleccionado
        iIndex = ListOcultas.ListIndex
        'añadimos un elemento al ListBox de destino...
        ListVisible.AddItem .List(iIndex, 0), 0
        'incorporamos los valores de las diferentes columnas
        'desde el ListBox de origen al ListBox destino
        For col = 1 To ListOcultas.ColumnCount - 1
            ListVisible.List(0, col) = .List(iIndex, col)
        Next col
    End With
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ListVisible_MouseMove(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'este evento se produce cuando el usuario mueve el mouse...
Dim MyDataObject As DataObject
If Button = 1 Then
    Set MyDataObject = New DataObject
    Dim Effect As Integer
    'Con el método .SetText copiamos una cadena de texto al objeto DataObject...
    MyDataObject.SetText ListVisible.Value
    'el método .StartDrag da inicio a una operación de arrastrar y colocar para un objeto DataObject.
    Effect = MyDataObject.StartDrag
End If

For i = 0 To ListVisible.ListCount - 1
    If ListVisible.Selected(i) = True Then
    For j = 1 To Sheets.Count
        If Sheets(j).Name = ListVisible.List(i) Then
            matrizHojas(j) = False
            On Error GoTo finaliza
            Sheets(j).Visible = False
        End If
    Next j
    End If
Next i

'refrescamos situación....
ActualizaFormulario
Exit Sub

finaliza:
'controlamos que siempre quede al menos una hoja visible...
If ListVisible.ListCount = 1 Then
    MsgBox "No se pueden ocultar todas las hojas.." & vbCrLf & "Siempre debe quedar al menos una visible"
    ActualizaFormulario
    Exit Sub
End If

End Sub
Private Sub ListOcultas_MouseMove(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'este evento se produce cuando el usuario mueve el mouse...
Dim MyDataObject As DataObject
If Button = 1 Then
    Set MyDataObject = New DataObject
    Dim Effect As Integer
    'Con el método .SetText copiamos una cadena de texto al objeto DataObject...
    MyDataObject.SetText ListOcultas.Value
    'el método .StartDrag da inicio a una operación de arrastrar y colocar para un objeto DataObject.
    Effect = MyDataObject.StartDrag
End If


For i = 0 To ListOcultas.ListCount - 1
    If ListOcultas.Selected(i) = True Then
    For j = 1 To Sheets.Count
        If Sheets(j).Name = ListOcultas.List(i) Then
            matrizHojas(j) = True
            Sheets(j).Visible = True
        End If
    Next j
    End If
Next i

'refrescamos situación....
ActualizaFormulario
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ActualizaFormulario()
'limpiamos los ListBox del formulario
Me.ListVisible.Clear
Me.ListOcultas.Clear

'recorremos todas las hojas del libro
For i = 1 To Sheets.Count
    'llenamos nuestra Array con TRUE o FALSE
    matrizHojas(i) = Sheets(i).Visible

    If matrizHojas(i) Then
        'si es visible lo cargamos en el ListBox de 'hojas visibles'
        Me.ListVisible.AddItem Sheets(i).Name
    Else
        'si NO es visible lo cargamos en el ListBox de 'hojas ocultas'
        Me.ListOcultas.AddItem Sheets(i).Name
    End If
Next i
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub CmdCerrar_Click()
    Unload Me
End Sub

Basta ejecutar o lanzar el formulario y arrastrar hojas de un ListBox a otro...

El código tiene un control de error para evitar que todas las hojas queden ocultas... lo que es imposible para Excel.

Es un código un poco largo, pero la funcionalidad y los eventos:
_MouseMove
_BeforeDropOrPaste
y
_BeforeDragOver
se deben repetir para ambos ListBox, ya que el arrastrar de una posición a otra debe estar configurada...

Seguir leyendo...

La función INDICE en su sintaxis de referencia.

Expondré hoy un caso habitual de búsqueda sobre diferentes opciones empleando la famosa combinación de funciones INDICE y COINCIDIR, pero esta vez empleado la segunda de sus sintaxis en forma de referencia:
=INDEX(ref, núm_fila, [núm_columna], [núm_área])
lo que nos permitirá elegir el área o rango de donde extraer la información requerida.

En definitiva, esta función INDICE en esta forma, devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna determinadas; sabiendo que si la referencia se compone de rangos no adyacentes, podremos elegir el rango donde buscar...

Partiremos de un rango de celdas que representan tres listas de precios para un mismo producto.
El objetivo es, a partir de un producto dado y una Lista de precios, obtener el importe correspondiente:

Con la información necesaria:
producto en celda F5
y Lista elegida en celda G5,
podemos construir la siguiente función:
=INDICE((B2:B5;C2:C5;D2:D5);COINCIDIR(F5;A2:A5;0);1;COINCIDIR(G5;B1:D1;0))

La clave está en el primer argumento de la función, donde vemos entre paréntesis las tres áreas de donde pretendemos recuperar el precio:
(B2:B5;C2:C5;D2:D5)

Con el primer COINCIDIR
COINCIDIR(F5;A2:A5;0)
aplicado sobre el rango A2:A5 de los códigos de los productos determinamos qué fila es la que nos interesa.

El segundo COINCIDIR será quien nos devuelva de cuál de las tres áreas (B2:B5;C2:C5;D2:D5) debemos recuperar el precio:
COINCIDIR(G5;B1:D1;0)

Lo interesante del uso de esta función, de esta forma, es que nos permitiría trabajar sobre rangos no adyacentes o discontinuos, siempre que seamos capaces de indicarle sobre qué rango queremos trabajar...

Seguir leyendo...

VBA: Matrices y Colecciones en nuestras macros.

Se me planteaba la siguiente cuestión:

tengo un problema y no se como hacerlo, ya que recién estoy empezando a programar, mi problema es el siguiente: tengo varias celdas con textos separados por comas, el cual me piden que ordene por filas quedando de esta forma. texto1: casa, habitaciones, edificio, oficinas texto2: licuadora, frutas, molino , grano,fiesta patrias, bandera casa habitaciones edificio oficinas licuadora frutas molino grano fiestas patrias bandera

La idea es, por tanto, listar todos los elementos de los diferentes textos o celdas, en pares de elementos...

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código de un módulo estándar desde el editor de VB:

Sub Separar_Pares()
Dim matriz() As String
Dim MatrizFinal() As String
Dim coleccion As New Collection
Set Rng = Range("A1:A2")

'recorremos cada celda del rango...
For Each celda In Rng
    'construimos una matriz con los elementos separados por comas
    'en cada celda
    matriz = Split(Trim(celda), ",")
    'cada elemento lo añadimos a una Collection
    For item = 0 To UBound(matriz)
        coleccion.Add Trim(matriz(item))
    Next item
Next celda

'pasamos por todos los elementos cargados en la Collection
Dim num As Long
'controlamos que haya un número par de elementos...
'en caso contrario añadimos uno en blanco.
If coleccion.Count Mod 2 <> 0 Then
    coleccion.Add ""
End If
'Preparamos una última matriz con los pares cargados
num = Int(coleccion.Count / 2)
ReDim MatrizFinal(1 To num) As String

'y procedemos a la carga de la matrizFinal
x = 1
For m = 1 To coleccion.Count Step 2
    MatrizFinal(x) = coleccion(m) & " " & coleccion(m + 1)
    x = x + 1
Next m

'Retornamos los pares a la Hoja de cálculo
Range("C1:C" & num).Value = Application.Transpose(MatrizFinal)

End Sub

El trabajo ha consistido en, tal y como se explica en el paso a paso, generar una matriz o Array con los diferentes elementos de cada celda (Array llamada 'matriz').
A continuación, como parte del mismo bucle se ha generado una Collection con los elementos individuales de cada matriz anterior ('coleccion').

Finalmente, uniendo a pares los elementos individuales de nuestro Collection, cargando los datos en una última Array: 'MatrizFinal'.. para finalmente volcar todos sus elementos en la columna C de la hoja...

Seguir leyendo...

Las Vistas Personalizadas.

Hoy veremos una manera muy simple, sin emplear macros) de gestionar qué hojas visualizar (mostrar u ocultar) según el perfil del usuario.
Emplearemos la herramienta de Vistas personalizadas a nuestra disposición desde la Ficha Vista > grupo Vistas de libro > botón Vistas personalizadas..

Supongamos hemos trabajado sobre nuestro Libro de trabajo, y ya tenemos la configuración final en cuanto al número de hojas, sabiendo que unas hojas solo afectan al departamento de 'Contabilidad', otras a 'Comercial' y otras a 'Recursos Humanos'.

El trabajo a realizar es simple. Crearemos cuatro Vistas personalizadas: una para cada perfil, y un perfil general que visualice todas las hojas.
Entraremos en la la Ficha Vista > grupo Vistas de libro > botón Vistas personalizadas. y desde la Ventana Diálogo agregaremos las distintas, ocultando y mostrando aquellas hojas de trabajo que correspondan.
Podríamos decir que hacemos 'fotos' de aquellas situaciones (en cuanto a hojas visibles) que necesitemos.

Por ejemplo, en primer lugar, la Vista 'General' con todas las hojas visibles:

Una segunda Vista personalizada 'Contabilidad' con las Hoja 'Inicio¡ y las tres de 'contabilidad' accesibles, y el resto -comercial y RRHH- ocultas:

Repetiríamos la acción tantas veces como se requiriera (un par de ocasiones más en nuestro ejemplo, para Comercial y RRHH), teniendo el cuidado necesario para Ocultar y Mostrar las hojas correspondientes...
Acabaríamos entonces con este Listado de Vistas Personalizadas:

Será desde esta ventana desde donde cada usuario gestionará fácilmente a qué hojas de trabajo acceder , o en definitiva ver.
Basta hacer doble clic sobre la Vista elegida o presionar el botón de Mostrar.

Nota importante 1: Nuevas hojas incorporadas después de la creación de las Vistas quedan fueran de este juego de ocultar/mostrar.
Nota importante 2: También afecta al tamaño de la ventas (maximizado o minimizado).

Seguir leyendo...

UDF y Validación de datos.

Planteaba un lector a través de un comentario la siguiente cuestión:

¿Se puede hacer validación de datos con una función de usurario?. Cuando intento utilizar una UDF para validación de datos me da un error: 'No se puede encontrar uno de los rangos especificados'. ¿A qué puede deber?

El asunto es que a pesar que una UDF (función personalizada con programación VBA por el usuario) es plenamente operativa en otras funcionalidades (formato condicional, por ejemplo), parece ser que existen restricciones en cuanto al uso de UDF dentro de las validaciones de datos.

Una solución consiste en crear un Nombre definido donde incluir la UDF, para luego incorporar el Nombre definido en la validación de datos (no devuelve el error comentado, pero no es 100% fiable).

Otra opción más segura, pero menos limpia, es construir la Validación sobre la función personalizada insertada en una celda auxiliar...

Para ejemplificar este caso, insertamos la siguiente Function dentro de la ventana de código de un Módulo estándar desde el editor de VB:

Function validar(ref As Range) As Boolean
If ref.Value <> "" Then
    validar = True
Else
    validar = False
End If
End Function

Si ahora intentamos agregar esta función a una Validación de datos obtendríamos el error:

Prefiero optar por la opción fiable, por tanto, nn nuestra hoja añadiremos en la celda auxiliar D2 la función personalizada:
=Validar(B2)
Y seleccionando la celda a validar C2 configuraremos la Validación de datos personalizada con la fórmula:
=D2=VERDADERO

Podremos probar ahora y comprobar, como apoyándonos en la celda auxiliar que contiene la UDF, la restricción de la Validación de datos se verifica...

Como recomendación general diría que evitemos siempre que sea posible (y casi siempre lo es) las UDF si tenemos que emplear la herramienta de Validación de datos, empleando las funciones lógicas habituales (Y, O, NO, etc).

Seguir leyendo...

Búsquedas sensibles a las mayúsculas o minúsculas.

Hoy veremos cómo conseguir realizar búsquedas que discriminen las mayúsculas y minúsculas.
El problema que se plantea en nuestras búsquedas, con las funciones habituales: BUSCAR, BUSCARV, BUSCARH, COINCIDIR etc es que NO son sensibles a las mayúsculas-minúsculas, y devuelven coincidencias por caracter, independientemente del tipo de letra.

Pero y ¿qué puedo hacer si deseo realizar búsquedas precisas, incluyendo la tipología de la letra (mayúscula-minúscula)?

Partamos de la siguiente tabla de información, donde aparecen en el primer campo una referencias desordenadas, algunas en mayúsculas y otras minúsculas:

Si deseamos buscar la referencia exacta 'X1' en mayúscula no podremos optar por un simple BUSCARV, ya que nos devolvería el primer dato...(incorrecto para nuestra precisión)
Algunas opciones de las que disponemos, si el valor buscado se encuentra en la celda E1.

Primera. Con la condición obligatoria que los datos/referencias buscadas estén ordenadas en sentido ascendente (de A a Z).
=BUSCAR(VERDADERO;IGUAL(TblDatos[ref];E1);TblDatos[uds])

Segunda. Bajo la hipótesis que el valor devuelto sea numérico.
=SUMAPRODUCTO(IGUAL(TblDatos[ref];E1)*TblDatos[uds])

Y tercera. Para cualquier hipótesis y en cualquier caso.
Usaremos la función matricial (ejecutada presionando Ctrl+Mayúsc+Enter en vez de Enter):
=INDICE(TblDatos[uds];COINCIDIR(VERDADERO;IGUAL(TblDatos[ref];E1);0))

haz clic en la imagen

En todas las fórmulas mostradas la clave reside en el uso de la función IGUAL, la cual sí detecta y distingue entre mayúsculas y minúsculas (justo lo que necesitamos).
Si extrapolamos y sacamos la fórmula de las funciones de búsqueda obtendríamos este rango de VERDADEROS y FALSOS sobre el que luego realizar la búsqueda con cualquier método:

Rápidamente se comprende el sentido de la búsqueda en los diferentes ejemplos... solo existe una coincidencia exacta/igual, la cual devuelve VERDADERO... que será nuestro valor buscado.

Seguir leyendo...

VBA: Añadiendo comentario a celda automáticamente con el contenido de esa celda.

Días atrás un compañero me planteaba la siguiente cuestión:

¿Es posible añadir a un comentario textos desde una celda de manera automática? Mi intención es que el texto que exista en una celda determinada se incorpore de manera automática a un comentario, y cuando cambie el texto de esa celda, cambia el comentario.

La idea es por tanto asociar la creación de un comentario al contenido de una celda de nuestra hoja de trabajo, para que cuando cambie el contenido de ésta, se inserte un comentario...

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código de la Hoja donde necesitemos la funcionalidad desde el editor de VB:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    On Error Resume Next
    'verificamos que la celda tiene contenido.
    If .Value <> "" Then
        'eliminando cualquier Comentario anterior
        .ClearComments
        'Añadimos uno nuevo...
        .AddComment
        '...donde insertamos un texto con el texto de la celda
        .Comment.Text Text:=Application.UserName & ":" & Chr(10) & Target.Value
    Else
        'eliminando cualquier Comentario anterior
        .ClearComments
    End If
End With
End Sub

Vemos el resultado..

Si necesitáramos concretar el rango dentro de la hoja donde necesitamos esta funcionalidad, podríamos combinar nuestra macro con el método .Intersect (ver aquí)

Seguir leyendo...

Elementos de una Serie Geométrica en Excel.

Hace ya unos tres años publiqué una forma sencilla de completar, u obtener, los elementos intermedios de una serie (ver).
En el ejemplo anterior vimos cómo, a partir de un valor inicial y uno final, podíamos concretar, con la herramienta de Series, los valores intermedios. En ese caso planteamos el ejemplo para una Serie Lineal o Aritmética.

Hoy veremos el mismo caso para una Serie Geométrica (ver wikipedia), empleando la herramienta de Series, Funciones o Buscar Objetivo.

Comenzaremos con la más sencilla: las Series.
Partimos del siguiente rango, donde deseamos descubrir cuáles son los tres elementos intermedios entre el valor 3 y 20, si siguiéramos una serie geométrica.

Para este caso seleccionamos el rango B2:B6 y desde la ficha Inicio > grupo Modificar > botón Rellenar > opción Series accedemos a la ventana de configuración.

Nos aseguramos haber seleccionado la opción de Series en: Columnas, Tipo: Geométrica y marcar la casilla de Tendencia.

El resultado será:

Lo potente de esta herramienta de Excel es que calcula por nosotros la razón de la serie, y en base a ella calcula los valores de los elementos intermedios....

Para conocer cuál es la razón de esa serie basta dividir dos elementos seguidos.
en nuestro ejemplo razón=1,6068568378893

Sobre el mismo ejercicio podemos conseguir lo mismo trabajando con la herramienta Buscar Objetivo.
Construimos nuestra secuencia de valores formulados, esto es, a partir del primer elemento, vamos multiplicando el elemento anterior por la razón (en el ejemplo en la celda amarilla, celda I2):

Seguir leyendo...