VBA: Detener una macro desde otra

En ocasiones necesitamos parar o detener nuestras macros en ejecución...

[...]Quiero que mi macro mande a cerrar una macro de otro libro[...]

Habitualmente las macros se pueden detener desde el código de la propia macro (End, Exit) o bien manualmente a veces presionando las teclas:
Esc
o quizá Ctrl+break/pause

Esto suele ser suficiente... aunque en ocasiones no es todo lo efectivo que desearíamos.

Hoy veremos cómo configurar nuestras macros para que en cualquier momento del proceso en ejecución de una primera macro, poder detenerla desde una segunda.
Para ello emplearemos la función DOEVENTS, sabiendo que DoEvents transfiere el control al sistema operativo.
DoEvents es útil principalmente para acciones sencillas como permitir a un usuario que cancele un proceso después de que se haya iniciado, ya que la tarea puede continuar completamente independiente de la aplicación y el sistema operativo se encarga de la 'multitarea' y el rebanado de tiempo.

OJO!!: No uses DoEvents si otras aplicaciones pueden interactuar con el procedimiento de manera imprevista durante el tiempo que ha producido la ejecución del control.

Para mostrar el caso insertaremos dos controles 'CommadButton' tipo ActiveX con el código adjunto.

Insertamos en la ventana de código de la hoja el siguiente código:

Dim ParadaEmergencia As Boolean

Private Sub CommandButton1_Click()
'iniciamos con la variable a FALSE
ParadaEmergencia = False

'lanzamos a modo de ejemplo un proceso largo
For i = 1 To 50000
    'reflejamos el dato en A1
    Range("A1").Value = i
    'Pasamos la ejecución de modo que el sistema operativo pueda procesar otros eventos.
    DoEvents
    'y controlamos la posible salida el bucle con un Exit
    If ParadaEmergencia = True Then Exit For
Next i
End Sub


Private Sub CommandButton2_Click()
'cambiamos el estado de la variable a TRUE
ParadaEmergencia = True
End Sub

Podemos comprobar si lanzamos la primera macro con el CommandButton1 (Ejecutar), en cualquier momento podemos detenerla presionando el segundo CommandButton2 (Detener).

Importante la definición de nuestra variable ParadaEmergencia al principio de la ventana, no incluida en ninguno de los procedimientos... así nos aseguramos su uso compartido entre las dos macros.

Seguir leyendo...

Ordenar en una hoja protegida

Aprenderemos en el post del día a Ordenar un rango cuando la hoja esté protegida.

A priori podría parecer algo tan simple como proteger la hoja y marcar la opción de 'Ordenar' entre los permisos habilitados...

Si lo haces te llevarás una sorpresa!!.
Resulta que existen algunos permisos de esa lista que no funcionan como 'prometen', y es que cuatro de esos permisos no operan cuando las celdas están bloqueadas y la hoja protegida!!:
Ordenar
Eliminar columnas
Eliminar filas
Insertar hipervículos.

Es decir, el tema no es tan simple como permitir usar el Autofiltro o Seleccionar celdas bloqueadas.
La explicación es que al ordenar hay un proceso 'real' de reescritura entre las celdas involucradas, y al estar las celdas por defecto bloqueadas hay una incompatibilidad manifiesta.

No nos preocupemos... hay una solución.
supongamos en nuestra hoja de cálculo un rango de datos que deseamos poder ordenar a pesar de tener la hoja protegida.

Comencemos por el paso 1.
Seleccionamos el rango completo, incluyendo el 'encabezado'.

Paso 2
Accederemos a la herramienta de Permitir editar rangos (desde la Ficha Revisar > grupo Proteger > botón Permitir editar rangos).

En la ventana diálogo abierta configuramos las opciones tras presionar el botón de Nuevo:

Daremos título al rango tratado y si fuera el caso contraseña:

Paso 3
Aprovechando la opción habilitada en la ventana diálogo anterior (o accediendo al botón de Proteger hoja), y nos aseguraremos de desmarcar la opción de permitir Seleccionar celdas bloqueadas y la de Ordenar (y 'Usar autofiltro' que siempre viene bien)

Si lo estimamos añadiremos una contraseña.

Nos aseguramos así que el usuario final no podrá seleccionar las celdas del rango de datos...

Listo, al Aceptar podremos ordenar ( y filtrar) sin problemas, impidiendo que este usuario pueda editar las celdas de nuestra hoja de cálculo... de hecho no podrá ni seleccionarlas!!

Es una condición limitante en muchos aspectos, pero poco más se puede hacer sin macros...

Seguir leyendo...

VBA: Retrasar entrega de email desde Excel

Una acción poco empleada, en mi opinión, dentro de Outlook, es Retrasar la entrega...lo que nos permite programar el momento de envío de un email...

Veremos en este artículo cómo conseguirlo desde la programación en Excel empleando la propiedad .DeferredDeliveryTime

Insertamos en la ventana de código del módulo estándar el siguiente código:

Sub RetrasarEnvio()
'OJOOO.. activar referencia/librería Microsoft Outlook 16.0 Object Library o superior !!!
Dim olMailItm As Outlook.MailItem

Set olMailItm = Outlook.CreateItem(olMailItem)
With olMailItm
  .To = "excelforo@gmail.com"
  .Subject = "Retrasar o programar envío email..."
  .Body = "Hola!!" & vbNewLine & "Saludos cordiales"
  .DeferredDeliveryTime = "13/09/2019 13:13:13 PM" 'retrasamos el envío a una fecha concreta
  .Display
  .Send
End With
End Sub

Tras lanzar la macro podemos comprobar en la carpeta de Bandeja de salida el email con la programación de fecha y hora de entrega...

Seguir leyendo...

VBA: Enviar email desde cuenta específica de Outlook

Aprenderemos en el post de hoy a elegir una de nuestras cuentas de Outlook desde la que enviar nuestros emails...

[...] en mi outlook tengo configurada más de una cuenta,  con que código especifico de cual quiero que se envíe el correo?[...]

La propiedad buscada en .SendUsingAccount.
Fundamental para el correcto funcionamiento asignar la propiedad como una variable declarada con Set:
Set .SendUsingAccount = xxxxx
Importante (y obvio) emplear la propiedad antes de llamar al método Send.

Supongamos los siguiente valores A2:D2 en nuestra hoja:

Insertamos en la ventana de código de un módulo estándar el siguiente código:
No olvides activar la librería de Microsoft Outlook...!!

Sub EnvioCuentaEspecificaOutlook()
'OJOOO.. activar referencia/librería Microsoft Outlook 16.0 Object Library o superior !!!
Dim olApp As Object
Dim olMailItm As Object

'Creamos 'la aplicación de MS Outlook'
'que será la que voy a utilizar
Set olApp = CreateObject("Outlook.Application")
'y genero un email vacío... de momento
Set olMailItm = olApp.CreateItem(0)
'completamos elementos del email
With olMailItm
    .to = Range("A2").Value     'el destinatario
    '.bcc = Range("A3").Value   'si quisiéramos enviar con copia oculta
    .Subject = Range("B2").Value    'el asunto del correo
    .Body = Range("C2").Value       'el cuerpo del email
    .Attachments.Add Range("D2").Value  'si queremos adjuntar algún fichero
    Set .SendUsingAccount = olApp.Session.Accounts.Item(2)        'elegimos la cuenta desde donde enviar el email..
    .Display 'mostramos el email completo
    '.Send      'y lo enviamos... (si queremos)
End With

'Dejamos limpias las variables empleadas
Set olMailItm = Nothing
Set olApp = Nothing
End Sub

Y listo... enviando emails desde la cuenta de Outlook deseada.

Un paso previo interesante para conocer qué cuentas tenemos en nuestro Outlook y sobre todo a qué orden/numeración corresponde, sería lanzar la siguiente macro...

Sub testCuenta()
Dim olApp As Object

Set olApp = CreateObject("Outlook.Application")

For i = 1 To olApp.Session.Accounts.Count
    MsgBox "La cuenta " & olApp.Session.Accounts.Item(i) & " tiene el orden " & i
Next i
End Sub

De esta forma identificamos el número de cuenta a elegir...

Un método alternativo es enviar correos en nombre de otra persona o cuenta (.SentOnBehalfOfName).
Por supuesto podremos hacer uso de esta propiedad si tenemos acceso a esa cuenta ;-)

Insertamos en la ventana de código de un módulo estándar el siguiente código:
No olvides activar la librería de Microsoft Outlook...!!

Sub SendEmailOnBehalf()
Dim olMailItm As Outlook.MailItem

Set olMailItm = Outlook.CreateItem(olMailItem)
With olMailItm
  .SentOnBehalfOfName = "consultoria@excelforo.com"     'indicamos la cuenta desde donde enviar el email (en nombre de...)
  .To = "excelforo@gmail.com"
  .Subject = "Email en nombre de otra cuenta"
  .Body = "Hola!!" & vbNewLine & "Saludos cordiales"
  .Display
  .Send
End With
End Sub

Seguir leyendo...

VBA: Recuperar columna de un ListBox

Hoy veremos una sencilla manera de obtener los valores de una columna en concreto de un ListBox dentro de un UserForm.
Tratamos de dar respuesta al comentario de un lector al hilo de este post.

[...]lo que estoy tratando de lograr es que los datos de una columna del listbox sean los que aparecen en los textbox.  [...]

Tenemos en nuestra hoja de cálculo una tabla de datos (llamada 'Tabla1'), y ya en el Editor de VB un UserForm1 con un:
1- ListBox llamado LstDatos
con propiedades siguientes:
.ColumnCount = 3
.ColumnsHeads = True
.ListStyle = 1-fmListStyleOption
.MultiSelect = 1-fmMultiSelectMulti
.RowSource = Tabla1
y
2- un CommandButton llamado cmdReg

Insertamos en la ventana de código del UserForm el evento siguiente asociado al CommandButton

Private Sub cmdReg_Click()
Dim i As Integer, contador As Integer
Dim txt As String

contador = 0
'recorremos los elementos del ListBox
For i = 0 To Me.LstDatos.ListCount - 1
    'verificamos si está seleccionado
    If Me.LstDatos.Selected(i) = True Then
        'en caso afirmativo recuperamos el dato de la columna 3 (0,1,2)
        txt = txt & Me.LstDatos.List(i, 2) & vbCrLf
        'y aumentamos el conatdor.
        contador = contador + 1
    End If
Next i

'devolvemos a un MsgBox los resultados....
MsgBox "Has seleccionado los siguientes " & contador & " elementos" & vbCrLf & txt

End Sub

El resultado tras seleccionar uno o varios elementos del listbox es un mensaje con los valores de la tercera columna listado...

Seguir leyendo...

AGREGAR y cómo recuperar coincidencia múltiples

Un par de meses atrás aprendimos a recuperar coincidencias múltiples usando una función específica de Excel 2019 o 365 (UNIRCADENAS), ver post aquí.
Hoy aprenderemos a obtener un listado de coincidencias usando la función AGREGAR como apoyo.
Tenemos el siguiente listado de ventas por país y comercial, del cual deseamos obtener una lista de aquellos comerciales que hayan trabajado en España (ES).

Según la distribución de nuestros datos la fórmula buscada sería, a insertar en E7:E17:
=INDICE($B$2:$B$18;AGREGAR(15;6;($A$2:$A$18=$E$5)*(FILA($A$2:$A$18)-1)/($A$2:$A$18=$E$5);FILAS($E$7:E7)))

Lo interesante es el uso de la función AGREGAR(num_funcion; opciones; rango_matriz; [k]).
En nuestro ejemplo:
AGREGAR(15;6;($A$2:$A$18=$E$5)*(FILA($A$2:$A$18)-1)/($A$2:$A$18=$E$5);FILAS($E$7:E7))

donde usamos la función 15 - K.ESIMO.MENOR que nos permitirá recuperar de menor a mayor los diferentes valores del argumento rango_matriz.
Igualmente importante es la opción elegida 6 - Omitir valores de error , que restringe la elección de los elementos de menor valor sin considerar los errores...
Y siguiendo esta línea de actuación construimos una matriz 'virtual' en el argumento 'rango_matriz':
($A$2:$A$18=$E$5)*(FILA($A$2:$A$18)-1)/($A$2:$A$18=$E$5)

que si lo convertimos en valores tendríamos para nuestro ejemplo:
{1;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;5;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;9;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;13;#¡DIV/0!;#¡DIV/0!;#¡DIV/0!;17}

Los tres argumentos primeros de AGREGAR nos devolverán únicamente los importes {1;5;9;13;17}... que son las posiciones de filas que necesitamos recuperar, lo que hacemos con la función INDICE.

Y sobre éstos recuperaremos con el cuarto argumento [k], en orden, el primero, segundo, tercer, etc... para lo cual usaremos el clásico:
FILAS($E$7:E7)
que retorna, según arrastramos la fórmula hacía abajo valores 1, 2, 3, etc...

Con lo que finalizamos nuestra búsqueda de coincidencias múltiples...

Seguir leyendo...

Herramientas OLAP-Convertir en fórmulas

Al hilo del post anterior del blog sobre las funciones CUBO, hoy veremos una herramienta curiosa que convierte un informe de tabla dinámica a fórmulas CUBO...
Por supuesto la tabla dinámica debe haberse creado desde el complemento PowerPivot.

Con los datos agregados al Modelo de datos, accedemos al editor de PowerPivot.
En su ficha Inicio > desplegable Tabla dinámica > opción Tabla dinámica e insertaremos un informe de tabla dinámica en nuestra hoja de cálculo.

Damos forma a nuestro informe de tabla dinámica... para conseguir un informe similar al del post anterior.

Y ahora lo interesante, vamos a convertir en fórmulas (funciones CUBO) nuestro informe de tabla dinámica.

Para ello seleccionamos la tabla dinámica, y desde su barra de herramientas > pestaña Analizar > grupo Cálculos > Herramientas OLAP > Convertir en fórmulas

Veamos el efecto conseguido... y las funciones CUBO generadas.

Lo primero curioso de ver es que el campo de la tabla dinámica en el área de filtros se mantiene... (en teoría para mantener activa la posibilidad de seguir aplicando filtros), y por tanto la tabla dinámica sigue viva...
En otros casos, sin campos en este área, la tabla dinámica desaparece 'eternamente'.

El otro aspecto a analizar es cómo han sido reemplazadas todas las celdas de la tabla dinámica por funciones tipo CUBO (como VALORCUBO, MIEMBROCUBO, etc.)

Esta funcionalidad facilita la generación de informes empleado funciones de CUBO.

Seguir leyendo...

Funciones de CUBO

Existe una categoría de funciones muy desconocida, pero con un potencial asombroso, hablo de las funciones de CUBO:
CONJUNTOCUBO
MIEMBROCUBO
MIEMBROKPICUBO
PROPIEDADMIEMBROCUBO
RECUENTOCONJUNTOCUBO
y sobe todo
VALORCUBO

En primer lugar, y esto es fundamental, deberemos cargar al Modelo de datos (PowerPivot) nuestra tabla de información:

Para esto lo más sencillo, de entre varias formas de realizarlo, es acceder a la ficha PowerPivot > grupo Tablas > botón Agregar al Modelo de datos

Agregar la tabla al modelo de datos es necesario para emplear las funciones CUBO !!.

Ya estamos en disposición de usar nuestras funciones CUBO.
Un punto interesante de estas funciones es que reconocen, en cascada o jerarquía, los distintos niveles de información:
[Tabla].[Campo].[Elemento]
y siempre entrecomillado!!
Por suerte, es predictivo, y según escribimos nos ofrece las opciones habilitadas...

Empezaremos listando los elementos del campo país, para esto usaremos la función MIEMBROCUBO(conexión; expresión; [título]).
Así en el rango F3:F7 escribiremos:
=MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[pais].[All].[DE]")
y sucesivos países.

A continuación, apoyándonos en estas celdas, obtendremos las unidades acumuladas correspondientes a cada país, empleando la función VALORCUBO(conexión; expresión1; expresión2; ....)
En nuestro ejemplo en G3:G7 añadimos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[suma de unidades]";F3)
Notemos que la 'expresión1' responde al dato que deseamos mostrar, en este caso la suma de unidades para cada país!!

Igualmente podríamos haber incorporado más opciones de 'filtro', por ejemplo, sobre la disposición de la imagen siguiente, aplicamos la fórmula:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[suma de unidades]";$F3;G$2;$F$2)
siendo todos los 'encabezados' obtenidos con MIEMBROCUBO

Otra función interesante es CONJUNTOCUBO(conexión; expresión_conjunto; título; orden; ordenar por), que nos devuelve un conjunto de elementos ordenados...

Supongamos en F11:F15 nuestros países listados (conseguidos con la función MIEMBROCUBO vista anteriormente).
Y en H10 insertamos la función:
=CONJUNTOCUBO("ThisWorkbookDataModel";(F11:F15);"todos países";1;"[Measures].[Suma de unidades]")
En esa celda tenemos, no a la vista, el conjunto de elementos de los países ordenados en ASC según la suma de unidades...!!!

Y desde ese conjunto de elementos, empleando otra función importante MIEMBRORANGOCUBO(conexión;expresión_conjunto;rango/posición;título)
conseguimos recuperar elementos individuales de dicho conjunto, según su posición ordenada...
En nuestro ejercicio en las celdas I11:I15 añadimos:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";$H$10;H11)

Vemos el resultado en la imagen siguiente

Para comprobar que efectivamente ese conjunto de elementos/países está ordenado según las unidades vendidas, podemos añadir en el rango J11:J15 la función VALORCUBO para recuperar esa suma de unidades por país...
En J11 insertamos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Suma de unidades]";$I11)
y arrastramos

Como vemos son funciones muy especificas para trabajar sobre cubos OLAP (como PowerPivot) sin emplear las funciones típicas de la hoja de cálculo...

Seguir leyendo...