Actualizar consultas Excel mediante código VBA

Actualizar una consulta generada en Excel por Power Query (Obtener y transformar datos) es sencillo, sea cual sea el método que queramos utilizar: interfaz de usuario o VBA.

Si recopilamos los métodos más habituales son:

1. Actualizar mediante interfaz de usuario, manualmente:
• Desde el botón desplegable "Actualizar todo" de la ficha Datos

• Opción Actualizar todo (se actualizan todas las consultas y tablas dinámicas del libro)
• Opción Actualizar (se actualiza solo la tabla activa)
• Desde la lista de consultas (Ficha Datos/Consultas y conexiones/Botón derecho sobre la consulta/Actualizar)

1. Actualizar automáticamente la consulta configurando la actualización automática desde sus propiedades (Ficha Datos/Consultas y conexiones/Botón derecho sobre la consulta/Propiedades/Actualizar al abrir el archivo o Actualizar cada....x minutos)
2. Actualizar mediante una macro con código VBA:
• Para una consulta sería:

•  ActiveWorkbook.Connections("Consulta - nombreConsulta").Refresh

• Para todas las consultas sería:

• ActiveWorkbook.RefreshAll

Como comentario para usuarios avanzados, tenemos que decir que en este último caso, a veces, ocurre un problema de sincronización entre el tiempo de actualización de la consulta y el tiempo de ejecución del código VBA.

Problema
Esto es, si una consulta se ejecuta mediante código VBA e inmediatamente después del código VBA de actualización de la consulta hay otro código VBA, este último código NO SE EJECUTA DESPUÉS DE FINALIZADA LA ACTUALIZACIÓN de la consulta, sino que se ejecuta inmediatamente después de INICIADA la actualización de la consulta. En otras palabras:

Cuando se ejecuta la consulta, esta comienza a ejecutarse y tarda un cierto tiempo, pero el código siguiente se ejecuta inmediatamente después y podría ocurrir que la consulta terminara después de que el código siguiente termine. Un ejemplo sería la macro:

Sub miMacro()
    ActiveWorkbook.Connections("Consulta - REV").Refresh

    ActiveSheet.ListObjects("tbl_REV").Range.AutoFilter Field:=5, Criteria1:="="

End Sub

Con el código anterior:

1. La primera línea de código inicia la actualización de la consulta REV
2. La segunda línea de código aplica un filtro a la tabla donde se carga la consulta REV
3. La consulta termina de actualizarse y se carga sobre la tabla REV, eliminando el filtro aplicado en la línea anterior

Con todo lo anterior, se evidencia el problema: la segunda línea de código queda inutilizada.

Solución
Es simple. Configurar la consulta de forma distinta a como está configurada por defecto:  (Ficha Datos/Consultas y conexiones/Botón derecho sobre la consulta/Propiedades/Desactivar la opción Habilitar actualización en segundo plano)

 José Manuel Pomares Medrano

 

Novedades en Office 2019 y 365 (Capítulo 2: PowerPoint)

Continuamos con la serie de artículos dedicados a las novedades de la versiones 2019 y 365 de la suite ofimática de Microsoft. Aunque este es un blog sobre Excel, remitimos al usuario a nuestro blog de presentaciones para ver el dossier completo de novedades. Podemos afirmar que casi cualquier usuario o usuaria que utiliza Excel, alguna vez (o quizá muchas), utiliza también PowerPoint.

Al igual que en el caso de Excel, lo primero que debemos decir es que no todas las novedades están disponibles para usuarios de PowerPoint 2019 (la versión de pago único). Sin embargo, los suscriptores de Office 365 si podrán disfrutar de un PowerPoint con todas las nuevas funcionalidades.

Aquí os dejo el enlace para que veáis todas las novedades de PowerPoint:


Novedades en PowerPoint 2019 y 365

Si queréis ver más sobre novedades de Office, podéis hacer clic en el siguiente enlace y tenéis todo un curso gratuito grabado por mí para LinkedIn Learning:

Novedades Office 2019

 José Manuel Pomares Medrano

 

Extraer la última palabra o parte de un texto de una celda de Excel

Muchos usuarios conocen algunas funciones de Excel como IZQUIERDA, DERECHA, EXTRAE, ENCONTRAR, LARGO, etc. Estas funciones, ya sea en solitario o actuando coordinadamente entre ellas, pueden conseguir con más o menos dificultad extraer cualquier palabra o cadena de texto desde la parte izquierda, central o derecha de un texto que pueda haber en una celda.

La cuestión se complica si lo que queremos extraer no tiene un ancho fijo (un número fijo de caracteres). Pero tiene también solución si, como hemos comentado antes, ponemos a trabajar juntas a varias funciones anidadas. Por ejemplo, para extraer la palabra de la izquierda (la primera palabra) de un texto que tiene varias palabras, podríamos utilizar la siguiente fórmula:

=IZQUIERDA(A1;ENCONTRAR(" ";A1))

Donde A1 es la celda donde se encuentra el texto

Pero, ¿y si necesitamos extraer la última palabra de una frase, el último apellido de una persona, la última parte de un código o el dominio de un email? Esto sería algo más difícil y deberíamos anidar también varias funciones, dando como resultado una función relativamente compleja.

Una cómoda y elegante solución podría ser la creación de un complemento de Excel (addin) que contuviera una UDF (User Defined Function) o Función Definida por el Usuario. La creación de una UDF se realiza mediante unas líneas de código VBA. Esas líneas de código las vamos a alojar en un libro de Excel que convertiremos a complemento mediante Guardar como.... Una vez completado el proceso, tendremos a nuestra disposición, en nuestro Excel, una función más dentro de todas las funciones que ya teníamos propias de Excel. Además, tendríamos la ventaja añadida de poder elegir el carácter que separa lo que queremos extraer del resto de la cadena.

Para crear una UDF dentro de un addin que esté listo para ser usado, debemos:

PASO 1)

Crear un libro de Excel nuevo, presionar ALT+F11 e insertar un módulo estándar en el proyecto Libro1 (si el libro nuevo se llama Libro1). Así pues, una vez ya hemos presionado ALT+F11, tenemos que hacer...

 

PASO 2)
Pegar en la ventana que se ha abierto a la derecha el código siguiente:

Function ULTIMA_PALABRA(strTexto As String, Optional strSeparador As String) As String

    'UTILIDAD:  extrae de un texto los caracteres que hay después del último separador
    'SINTAXIS:  =ULTIMA_PALABRA(texto;[separador])
   
    'Texto:         un texto, número o ref. a la celda de donde tenemos que extraer
    '[separador]:   uno o varios caracteres de texto (debe ir entre comillas)
   
    'Si no se especifica, [separador] es un espacio
   
    If strSeparador = "" Then strSeparador = " "
    Dim bytLargo As String:         bytLargo = Len(strTexto)
    Dim bytEncontrar As Integer:    bytEncontrar = InStr(strTexto, strSeparador)
   
    ULTIMA_PALABRA = Right(strTexto, bytLargo - bytEncontrar)

End Function



Una vez pegado el código, debe verse así en la ventana de código:

PASO 3)

Guardar el Libro1 como... Tipo de archivo: Complemento de Excel (tan solo con elegir la opción Complemento de Excel, la ruta de guardado para el archivo cambia automáticamente y es conveniente respetarla). En cuanto al nombre del archivo, escribimos el que deseemos. Por ejemplo, MisComplementos.

PASO 4)

Ahora, debemos asegurarnos de que Excel cargue el complemento cada vez que se inicie. Para ello, hacemos clic en... ficha Archivo / Opciones / Complementos / Complementos de Excel / Ir...

PASO 5)
Activar el complemento (lo veremos en la lista con el nombre que dimos en el paso 3) y Aceptar.

PASO 6)

Una vez hecho lo anterior, cerramos Excel SIN GUARDAR Libro1 (puesto que ya lo guardamos antes convirtiéndolo a complemento de Excel) y lo abrimos de nuevo.

Ya podemos utilizar la función ULTIMA_PALABRA como si fuera otra función de Excel:

 

 Podemos comprobar que la sintaxis de nuestra nueva función es:

=ULTIMA_PALABRA(texto;[separador])

texto:               un texto, número o ref. a la celda de donde tenemos que extraer
[separador]:    uno o varios caracteres de texto (debe ir entre comillas). Si no se especifica, [separador] es un espacio

Por lo tanto, podemos deducir que también podemos extraer la última parte de una cadena de texto que tenga cualquier separador. Por ejemplo, en la imagen de abajo tenemos unos códigos que tienen una última parte separada siempre por un guion. Pues con nuestra nueva función ULTIMA_PALABRA podemos extraer esa parte final del código utilizando como segundo argumento un guion (siempre debemos poner nuestro separador entre comillas, al ser una cadena de texto):

En Mac, el proceso es prácticamente idéntico y solo cambian algunas imágenes y algunos detalles. Lo que exponemos a continuación son los cambios que podemos apreciar, respecto del trabajo en Windows:

PASO 1)

PASO 3)

En Mac, Excel no establece una ruta conveniente de guardado de forma automática, sino que debemos elegir una (la que queramos) para saber dónde tenemos alojado nuestro addin.

PASOS 4) y 5)
En Mac, para activar un complemento debemos ir al menú Excel / Preferencias... / Barra de herramientas y cinta de opciones

 ... y activar la casilla de Ficha Programador

 ...salimos aceptando cambios y ya tenemos una nueva Ficha o Pestaña llamada Programador. En ella, debemos hacer clic en Complementos de Excel.

 José Manuel Pomares Medrano
 

PDF con 62 mini lecciones de VBA para Excel. Serie #SabíasQueEnVBA

Al igual que hiciera con los 62 trucos de Excel de la serie gratuita #SabíasQueEnExcel, también con la serie #SabíasQueEnVBA voy a ofrecer a mis lectores y seguidores, en este mismo artículo, un eBook con 62 mini-lecciones de VBA para Excel:

Este trabajo es la recopilación, en PDF, de la serie que he ido publicando a diario en Facebook, Twitter y LinkdeIn, bajo el hashtag #SabíasQueEnVBA.

Según he podido comprobar con los comentarios, puedo decir que he quedado muy satisfecho de la utilidad ofrecida por este curso gratuito. Ha sido un placer compartir con la comunidad de usuarios y usuarias de Excel estos conocimientos.

Sin más dilación, ahí va el enlace para la descarga:

eBook PDF #SabíasQueEnVBA

Gracias por la acogida y preparaos para el próximo #SabíasQue...


José Manuel Pomares Medrano

La instrucción Resume para control de errores en VBA - Excel

Cuando se produce un error en Excel, se genera un objeto llamado Err que contiene un número de error. La instrucción Resume (en cualquiera de sus formas) reinicia el valor de Err a 0 (cero) y Excel deja de estar bajo una situación de excepción. Resume tiene tres formas sintácticas: Resume, Resume Next y Resume Etiqueta.

Resume
 

La instrucción Resume lleva la línea de ejecución al punto donde se produjo el error. Debe utilizarse fuera de la línea de código donde está la instrucción On Error. Como podemos ver en el ejemplo siguiente, esto se utiliza para dar una nueva oportunidad a la línea que produjo el error:

Debemos interpretar en el código que si no existe una hoja llamada NuevaHoja, se producirá un error y la línea de ejecución salta a la etiqueta Error. Entonces, se comprueba el número de error y si, efectivamente, ha sido por culpa de la inexistencia de la hoja, se crea una nueva. La instrucción Resume se encarga de devolver la línea de ejecución al lugar donde se produjo el error y, por lo tanto, todo continuará correctamente.

Resume Next
 
La instrucción Resume Next Se utiliza siempre inmediatamente después de On Error, Por lo tanto, hablamos de On Error Resume Next. Esta modalidad de Resume omite la línea que provoca el error y enruta la ejecución a la línea siguiente a la que provocó el error. La utilización de esta instrucción debe hacerse cuando ya sabemos que el procedimiento no provoca errores en condiciones normales y queremos que el programa no se detenga y no muestre ningún error bajo una eventualidad que no podemos conocer a priori.

Resume Etiqueta
 
La instrucción Resume Etiqueta lleva la línea de ejecución a una etiqueta que podemos situar en cualquier parte del código. Esto se utiliza si no tenemos intención de solucionar el problema que causó el error y lo que deseamos es continuar con la ejecución en algún punto concreto. Utilizando On Error Resume Etiqueta reinciamos Err = 0, a diferencia del uso de On Error GoTo.

 

José Manuel Pomares Medrano

Mantener siempre actualizada una Tabla Dinámica en Excel (2)

Hace más de cinco años, en este mismo blog, escribí un artículo donde explicaba la forma de "Mantener actualizada constantemente una tabla dinámica". En esa ocasión, el artículo estaba referido a que la tabla refleje constantemente los datos de la tabla origen, pero se asumía que el rango de datos no cambiaba de tamaño. Pero, ¿qué ocurre si el rango de datos de la tabla - origen de datos crece o decrece?. Esta es una pregunta que me han hecho muchas veces últimamente y, además, desde varios ámbitos (empresarial y académico). Así pues, aquí propongo un par de soluciones para este problema, pero para casos distintos.

CASO 1: la tabla origen de datos puede tener formato de Tabla de Excel.

Si la tabla donde están los datos de origen puede tener formato de Tabla de Excel y esto no causa ningún inconveniente, el problema ya no lo es, puesto que tan solo aplicando ese formato a la tabla-origen (y haciendo que nuestra tabla dinámica está basada en ella), la tabla dinámica siempre tendrá como origen de datos la totalidad de datos, aunque hayan crecido o disminuido.

Para aplicar un formato de Tabla de Excel a un rango de celdas donde hay datos, tenemos que asegurarnos de que no hay celdas combinadas y de que cada columna tiene un título de encabezado. Después, desde la ficha Inicio, hacemos clic en "Dar formato como tabla":

Ahora, veremos que aparece una galería de estilos donde tenemos que elegir el que más nos guste. Posteriormente, debemos confirmar los datos del cuadro de diálogo que aparece... y .... ya está.

Por último, sería recomendable dar un nombre a la nueva Tabla de Excel. Lo podemos hacer desde la ficha Diseño (la ficha contextual que aparece cuando tenemos seleccionada una celda de la nueva Tabla). Podemos ver a la izquierda un campo para editar el nombre de la Tabla de Excel.

Una vez hecho todo esto, debemos tener en cuenta que...

• Si ya teníamos la tabla dinámica construida y basada en el antiguo rango de datos, debemos decirle a Excel ahora que la tabla dinámica debe basarse en nuestra nueva Tabla de Excel. Para ello, debemos ir a la ficha Analizar (teniendo alguna celda de la tabla dinámica seleccionada), hacer clic en "Cambiar origen de datos..." y seleccionar la nueva tabla completamente.

 

• Si tenemos que construir "desde cero" nuestra tabla dinámica, tan solo tenemos que hacerlo normalmente, seleccionando como origen de datos nuestra Tabla de Excel.

CASO 2: la tabla origen de datos NO puede tener formato de Tabla de Excel.

Si nuestra tabla - origen de datos no puede tener formato de Excel (por cualquier motivo), la solución debe ser radicalmente distinta y debemos recurrir a las macros. Aunque... ¡tranquilos!, tan solo son unas líneas de código.

El proceso sería el siguiente:

• Hacemos clic con el botón derecho en la pestaña de la hoja donde está la tabla dinámica que debe estar actualizada constantemente. Acto seguido, se abrirá el editor de VBA con una ventana grande a la derecha. Ahí debemos escribir lo siguiente:

       Private Sub Worksheet_Activate()
          ActiveSheet.PivotTables("MiTD").ChangePivotCache _
          ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:=Sheets("MiHoja").Range("B8").CurrentRegion)
      End Sub

      Donde MiTD debe ser sustituido por el nombre de la tabla dinámcia (el nombre de una TD lo podemos ver si tenemos una celda de la misma seleccionada, hacemos clic en la ficha Analizar y miramos a la izquierda).

     Donde MiHoja debe ser sustituido por el nombre de la hoja donde está la tabla - origen de datos.

      Y donde B8 es un ejemplo, pero cada usuario debe escribir ahí la referencia de la primera celda de la tabla - origen de datos (por ejemplo, el primer título de los encabezados). Una condición a tener en cuenta es que la tabla - origen de datos no debe tener filas completas vacías.

•  Cerramos el editor de VBA, cerramos Excel y aceptamos Guardar los cambios.

Y ya lo tenemos.

La clave de este código la expresión "CurrentRegion", que es la que, de forma automática, detecta cuál es el rango de datos de la tabla, siempre que la referencia de ejemplo B8 sea una celda de la tabla. Por otro lado, comentar que el código se ha introducido en el evento WorkSheet_Activate, por lo tanto, la actualización del rango se realiza cada vez que seleccionamos la hoja de la tabla dinámica.


José Manuel Pomares Medrano

Trabajo con arrays en VBA para Excel

Un array (arreglo, matriz o composición ordenada) es una variable que puede almacenar y gestionar varios valores colocándolos en la memoria RAM de nuestro equipo en forma de matriz. Los arrays pueden tener una disposición matricial de una dimensión (como una simple lista de elementos), de dos dimensiones (en modo tabla, donde las filas son una de las dimensiones y las columnas la otra), pero también pueden tener una disposición en tres dimensiones (como un cubo) o incluso en n dimensiones.

En Excel, en la mayoría de casos se trabaja con arrays de 1 o de 2 dimensiones, Por ejemplo:

Los arrays se suelen utilizar para procesar grandes cantidades de datos de forma muy rápida. Están indicados en los casos en los que la información que hay en un conjunto de datos (normalmente un rango de Excel) debe ser procesada (reorganizada, calculada, modificada, etc.).

La filosofía de trabajo con un array debe ser clara:

1. Crear el array
2. Asignar valores (llenar el array) con la información (normalmente se hace mediante bucles For...Next)
3. Volcar la información en un rango de Excel o en un objeto

1. Crear un array

OPCIÓN A). Solo declarar el array (y después dimensionarlo)

Por ejemplo:

   Dim miArray() As TipoDato

   ReDim miArray(miVariable, 7)

... o también...

   Dim miArray() As TipoDato

   ReDim miArray(3, Range("A1"))
 

Esta opción se utiliza cuando el número de elementos de cada dimensión no es conocido en el momento de escribir la línea de la declaración. Un ejemplo de esto es cuando ese número de elementos deben ser variables, constantes o referencias a rangos de Excel. Hay que tener en cuenta que, en principio, solo es posible dimensionar un array con un par de números. Es decir, el código siguiente no sería admitido:

   Dim miArray(miVariable,4) As Integer

Así pues, esta Opción A (como hemos visto anteriormente) consistiría en declarar el array con la instrucción Dim y posteriormente, dimensionarla con ReDim... ¡¡Qué SÍ permite variables, constantes y referencias a celdas!!.

 OPCIÓN B). Declarar el array y dimensionarlo al mismo tiempo

Por ejemplo:

   Dim miArray(6,3) As Integer

2. Asignar valores al array (llenarlo)

OPCIÓN A). Posición a posición con líneas de código

Por ejemplo:

   miArray(2,3) = Range("A1")
   miArray(4,3) = 836

OPCIÓN B). Posición a posición mediante un bucle

Por ejemplo:

   Dim miArray(6) As String

   For i = 1 To 6
      miArray(i) = i
   Next i

En esta fase de llenado o asignación de valores al array es cuando se puede procesar la información al mismo tiempo que se produce el llenado. Por ejemplo:

   Dim miArray(6) As String

   For i = 1 To 6
      miArray(i) =Left(Cells(i, 3)) & "-" & Right(Cells(i, 4))
   Next i

Con el código de arriba se llena la única columna de miArray* con los 3 caracteres de la izquierda de los datos que hay en las 6 primeras celdas de columna 3 de Excel, más un guion, más los 3 caracteres de la derecha que hay en las 6 primeras celdas de la columna 4 de Excel.

*Trabajando con arrays de una sola dimensión (una sola columna o fila) no es necesario especificar un par (x, y)

3. Volcar la información

Una vez que el array está lleno con la información procesada (si hubiera sido necesario), lo normal es volcar los datos en un Rango de Excel o en un objeto, como puede ser un combo - box o un cuadro de lista de un UserForm. Un ejemplo del primer caso sería:

Range("A1:A6")  = miArray

¡¡Atención!!, el rango destino debe tener el mismo tamaño (filas x columnas) que el array.
Otra cuestión que podemos apreciar en la última línea de código de ejemplo es que, al hacer referencia al array de forma global (no por posiciones), no es necesario aludir al par (x, y).



Los arrays tienen algunas ventajas frente a las variables - matriz:

1. Los arrays pueden ser dimensionados para cualquier tipo de dato, no solo el tipo variant.
2. Los arrays pueden ser llenados de valores uno a uno (posición a posición) desde la primera vez que se les asignan valores. Recordemos que las variables-matriz deben ser llenadas en bloque desde un rango de celdas de Excel.
3. Los arrays pueden tener más de 2 dimensiones y, además, estas pueden ser variables, referencias a rangos, constantes, etc.

José Manuel Pomares Medrano