Excelforo: Consultoría Excel profesional para empresas y particulares

.. y por supuesto la mejor formación presencial o elearning(online).
Haz de Excel tu mejor aliado!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Abril de 2015.
Nunca estudiar fue tan fácil.

Los cursos de Excel y Macros abiertos para este mes de Abril son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Abril de 2015; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

VBA: Listas aleatorias sin repetición combinados con elementos manuales.

Al hilo de una entrada anterior (ver1 y ver2) un lector preguntaba por la forma de ir generando listados aleatorios no duplicados, combinados con otros elementos manuales:

...Somos 20 empleados y estoy tratando de crear una tabla para designar 5 tareas, 2 de ellas designadas por mi (escogidos de una lista desplegable con los nombres de todos los empleados), pero quiero que las otras 3 sean establecidas de manera aleatoria, escogiendo de los empleados que no participaron en las tareas que fueron designadas previamente (desde ahora Tarea1 y Tarea2, respectivamente). por ejemplo: Si designé a Empleado1 realizar Tarea 1 y Empleado2 realizar Tarea 2, quiero que las tareas 3,4 y 5 sean realizadas por cualquier empleado menos Emplado1 y Empleado2 y que tampoco un empleado elegido de manera aleatoria, realice otra de las tareas que se designan de manera aleatoria, es decir, si Empleado3 salió escogido para realizar Tarea3, que no pueda ser designado para tareas 4 o 5, o que Empleado4 (escogido aleatoriamente) y que realiza Tarea4, haga tareas 3 o 5 (lo mismo para un empleado escogido para Tarea5) Las designaciones de las tareas 1 y 2, son establecidas al principio del día....

El matiz con las entradas comentadas al inicio es que en este caso, partiremos de unos elementos introducidos manualmente, combinando por tanto la aleatoriedad 'pura' de los ejemplos primeros, con un punto de 'manualidad'.
Por eso este proceso es algo distinto, ya que nuestro proceso consiste en reiterar los valores aleatorios hasta dar con uno no introducido manualmente ni generado aleatoriamente con anterioridad.

Comenzamos viendo nuestro modelo de datos:

Insertamos y ejecutamos el siguiente procedimiento 'AleaIactaEst':

Sub AleaIactaEst()
'recorremos el rango de empleados...
For Each celda In Range("B2:B6")
'definimos una variable estática para el aleatorio....
Static aleaEmpleado As Integer
    'cuando la celda del empleado esté sin rellenar
    'supondremos queremos completarla aleatoriamente
    If celda.Value = Empty Then
inicioEmpleado:
        'nuestro valor aleatorio...
        aleaEmpleado = Int(20 * Rnd + 1)
        x = 0
        'controlamos no esté repetido el aleatorio en el rango
        For Each empleado In Range("B2:B6")
            If empleado.Value = aleaEmpleado Then
                x = x + 1
            End If
        Next empleado
        'si no está repetido completamos el elemento
        If x = 0 Then
            celda.Value = aleaEmpleado
        Else
            'en caso contrario, esto es, exista el valor aleatorio
            'volvemos a generarlo y repetir el proceso
            GoTo inicioEmpleado
        End If
    End If
Next celda

'Repetimos el mismo proceso para las Tareas
For Each celda2 In Range("C2:C6")
Static aleaTarea As Integer
    If celda2.Value = Empty Then
inicioTarea:
        aleaTarea = Int(5 * Rnd + 1)
        y = 0
        For Each tarea In Range("C2:C6")
            If tarea.Value = aleaTarea Then
                y = y + 1
            End If
        Next tarea
        If y = 0 Then
            celda2.Value = aleaTarea
        Else
            GoTo inicioTarea
        End If
    End If
Next celda2

End Sub

Tras ejecutar el procedimiento el resultado será el siguiente, donde se ve con claridad como no hay repeticiones (especialmente en la columna de Tareas):

Consiguiendo así nuestro mix aleatorio-manual sin repeticiones...

Seguir leyendo...

Excel 2013 cambio de MDI a SDI.

Al fina de una entrada anterior (ver), vimos que teníamos una opción deshabilitada, el motivo es que en esta versión (Office 2013) Microsoft migró la interfaz gráfica de Excel de MDI a SDI:
MDI = Multiple Documents Interface
SDI = Single Document Interface

¿Esto qué es y qué implica?. Pues, entre otras cosas, que en versiones anteriores podíamos trabajar en una aplicación abierta con varios documentos, sin embargo ahora, cada documento que abrimos se abre en una aplicación nueva, con su propia cinta de opciones... seguramente te hayas percatado.
Esto conlleva varias consecuencias y cambios (unas buenas y otras no tanto), que intento detallar ahora.

Un primer efecto es la forma de trabajar en un entorno MDI (como hasta ahora) o en SDI (como en 2013).
Si por ejemplo abres en Excel 2010 dos o más libros y organizas las ventanas veríamos lo siguiente (entorno MDI):

Sin embargo, en Excel 2013, trabajando con dos o más libros veríamos:

Esto es, en versiones anteriores todos los libros se trabajaban desde una única cinta de opciones, y en Excel 2013, cada libro tiene su propia cinta de opciones.
Además de lo obvio, existen otros matices, por ejemplo, si abrimos dos libros en Excel 2010 y a continuación el administrador de Tareas tendríamos:

Repetimos la operación con dos libros abiertos en Excel 2013 y veremos:

Seguir leyendo...

Proteger Libro en Excel 2013.

En respuesta a un lector del blog expondré hoy una funcionalidad poco empleada (al menos por mí), Proteger Libro:

... lo que quiero es del archivo que te pase se queden los nombres de las pestañas asi (es decir que no se puedan renombrar, son 3 nombres diferentes) y que la pestaña 1 sea la que esta en la posicion 1 la 2 en la 2 y la 3 en la 3....

Aunque se me pasó por la cabeza componer un código en VBA para tal fín, enseguida recordé la herramienta Proteger Libro que entre otras cosas realiza exactamente lo pedido por el usuario: Impide cambiar, mover, eliminar las hojas existentes en nuestro libro.

Para ejecutar esta acción navegaremos hasta la ficha Revisar > grupo Cambios > botón Proteger Libro:

Proteger Libro nos permite incorporar una contraseña (opcional) para controlar los cambios...

Una vez protegido comprobaremos cómo han quedado deshabiltadas las acciones comentadas (Mover o Copiar hoja, Eliminar, Cambiar Nombre, etc...):

Ojo, por que a partir de la versión 2013 (Office 15) la opción de proteger Libro > Ventana está deshabilitada!!.
Explicaré en una entrada posterior el motivo de esto.

Seguir leyendo...

VBA: Visualizar el último elemento de un ListBox en un UserForm.

Aprenderemos hoy algo nuevo sobre los ListBox en nuestros UserForm, en concreto veremos cómo conseguir que nuestro ListBox, en especial en aquellos casos con muchos elementos, muestre el último elemento de la Lista.
Con esto trataré de dar respuesta a la cuestión planteada por un lector:

...En un Listbox de VBA Excel que me muestra  una hoja Excel  con datos que estoy ingresando  desde un userform, quiero ver la ultima fila  que ingreso,  Actualmente  solo  muestra  el encabezado y los  disponibles dentro de la ventana, y para ello  debo  recurrir  a la barra  de desplazamiento...

La idea es clara, que tras introducir valores/elementos nuevos en nuestra lista, se muestre NO el primero de los elementos, sino el último...

Partiremos de una pequeña lista en nuestra hoja de cálculo:

A continuación construiremos un UserForm con los siguientes controles:
3 labels
3 TextBox: TxtNombre, TxtZona y TxtImporte
1 CommandButton: CmdAgregar
1 ListBox: Lista

como sigue:

De especial, para el ListBox ajustaremos las propiedades:
.ColumnCount=3
.ColumnHeads=True

Finalmente incorporamos funcionalidad, desde la ventana de código del nuestro Formulario disponemos los siguientes eventos:

Private Sub UserForm_Initialize()
Me.TxtNombre.Value = ""
Me.TxtZona.Value = ""
Me.TxtImporte.Value = ""

Me.TxtNombre.SetFocus

'cargamos el ListBox
Dim UltFila As Long, strRng As String
UltFila = Hoja1.Range("A" & Rows.Count).End(xlUp).Row
strRng = "A2:C" & UltFila
Lista.RowSource = strRng

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub CmdAgregar_Click()
Dim UltFila As Long
UltFila = Hoja1.Range("A" & Rows.Count).End(xlUp).Row + 1

'completamos el dato
Hoja1.Cells(UltFila, 1).Value = Me.TxtNombre.Value
Hoja1.Cells(UltFila, 2).Value = Me.TxtZona.Value
Hoja1.Cells(UltFila, 3).Value = CDbl(Me.TxtImporte.Value)

'refrescamos el listbox
Dim strRng As String
strRng = "A2:C" & UltFila
Lista.RowSource = strRng

'mostramos el último valor añadido en el ListBox
'Lista.Selected(Lista.ListCount - 1) = True 'seleccionando el último item de la Lista...
Lista.TopIndex = Lista.ListCount - 1        'sin seleccionar el valor....

'limpiamos el formulario...
Me.TxtNombre.Value = ""
Me.TxtZona.Value = ""
Me.TxtImporte.Value = ""

Me.TxtNombre.SetFocus
End Sub

Existirían, al menos, dos maneras de conseguir nuestra meta, una podría ser empleando la propiedad .Selected, la otra con la propiedad .TopIndex; la diferencia entre una y otra es que .Selected dejará marcado el último elemento.. mientras que con .TopIndex únicamente visualizamos (ver filas de código 31 y 32 !!).
Así dependerá de lo que queramos.

Podemos ver el efecto aquí:

Seguir leyendo...

VBA: Borrar imágenes de un rango determinado.

A través de un comentario, días atrás, un lector planteaba la cuestión de cómo eliminar unas imágenes con una posición concreta dentro de una hoja de cálculo, manteniendo el resto de las imágenes existentes en la hoja.
Supongamos la situación de la imagen siguiente:

El objetivo es claro, eliminar únicamente las imágenes situadas en el rango A3:D25.
Insertamos y ejecutamos el siguiente procedimiento 'BorrarImgRango':

Sub BorrarImgRango()
'Añadimos líneas para eliminar imágenes en el rango A3:D25
'Declaramos variables.
Dim PrimeraFila As Integer, PrimeraColumna As Integer
Dim UltimaFila As Integer, UltimaColumna As Integer
Dim img As Object
Dim tc As Long, tr As Long

PrimeraFila = Range("A3").Row
PrimeraColumna = Range("A3").Column
UltimaFila = Range("D25").Row
UltimaColumna = Range("D25").Column

'Recorre cada objeto de la hoja y valida su posición.
For Each img In ActiveSheet.Shapes
    On Error Resume Next    'controlamos fallo al NO encontrar imágenes
    tc = img.BottomRightCell.Column
    tr = img.BottomRightCell.Row
    If (tc >= PrimeraColumna And tc <= UltimaColumna) And _
       (tr >= PrimeraFila And tr <= UltimaFila) Then
        If img.Type = 13 Then
            'mensaje que muestra la posición de inicio de la imagen
            MsgBox img.Name & "-" & img.Type & "-" & img.TopLeftCell.Address
            img.Delete
        End If
    End If
    On Error GoTo 0
Next
End Sub

El resultado es:

Notemos que la imagen debe estar en el interior del rango, al menos su punto inferior-derecha, y es que en este ejemplo estamos empleando la propiedad .BottomRightCell que representa la celda que se encuentra debajo de la esquina inferior derecha del objeto.

Podríamos combinar el código con otra propiedad empleada en la línea:

MsgBox img.Name & "-" & img.Type & "-" & img.TopLeftCell.Address

donde la propiedad .TopLeftCell nos retorna la celda superior-izquierda; por tanto, sería posible con una simple modificación del código, eliminar exclusivamente aquellas imágenes que estuvieran completamente en el interior del rango...

Seguir leyendo...

Un nombre de hoja prohibido: Historial

Todos sabemos que no es posible emplear ciertos caracteres para nombrar nuestras hojas (/, *, etc), pero es poco conocido que existe un nombre de hoja prohibido, en realidad reservado, y que no podemos utilizar, el nombre en cuestión es: historial(en mayúsculas o minúsculas).

Sólo intenta cambiar el nombre de una hoja por 'historial' y verá el siguiente mensaje:

No hay solución para esto, simplemente optar por un nombre de hoja diferente.

El motivo por el que este nombre está reservado es que se emplea como parte de la característica Excel de seguimiento de la historia de cambios, esto es, se crea una hoja de cálculo con el nombre 'Historial' al seleccionar "Mostrar cambios en una nueva hoja" en el cuadro de diálogo Resaltar cambios.
En la ficha Revisar > grupo Cambios > Control de cambios > Resaltar cambios.

Esta sería la hoja generada automáticamente por la funcionalidad:

Seguir leyendo...

Convertir Tabla de referencias cruzadas en una Tabla vertical.

Quizá al leer el título del post pienses 'este Ismael se ha vuelto loco, y ya no sabe qué dice'.
Sin embargo, este es un problema mucho más frecuente de lo que podemos pensar.
la cuestión es que en ocasiones nos encontramos el origen de nuestros datos cruzados en una matriz, cuando realmente los necesitamos en formato de tabla 'normal', desplegada verticalmente.
Lo entenderemos mejor viendo el ejemplo propuesto por una usuaria del blog:

...Trabajo con clientes del mundo del calzado. Ellos me suelen pasar sus catálogos, y siempre suele ser con un formato de matriz horizontal con tallas y cantidades (te pongo un ejemplo en la pestaña uno del excel adjunto) Sin embargo para mi trabajo, necesito pasarlos siempre a un formato vertical (en la segunda pestaña, la versión vertical con la que trabajo)...

Estos serían los datos propuestos:

El objetivo es lograr algo de este estilo:

El trabajo será, a pesar de lo que pueda parecer, muy sencillo. Bastará generar con el rango seleccionado A2:H8 (la tabla de referencia cruzada) una tabla dinámica versión 2003 con rangos de consolidación múltiple (ver cómo en ejemplo1 o ejemplo2).

El resultado de nuestra tabla dinámica 'especial' será este:

Tras hacer doble clic en el Total general, esto es, tras Mostrar el detalle obtendremos un tabla de nuestros datos tal y como queríamos:

Seguir leyendo...