jueves, 17 de mayo de 2018

SUMAR.SI condicionado sobre diferentes hojas

Daremos una segunda vuelta de tuerca a un tema recurrente que ya hemos visto en ocasiones anteriores en el blog (ver uno y dos).

Trataremos la forma de conseguir sumar de manera condicionada sobre todas las hojas del libro deseadas, siempre que existe una distribución por columnas homogénea en todas las hojas.


El desarrollo que veremos nace por la necesidad de una alumna en uno de los cursos que impartí recientemente, donde a partir de ficheros con un número alto de hojas de trabajo (más de cien en algún caso), debía obtener un consolidado de acuerdo a uno elemento o condición... y lo expuesto en esto otro post) quedaba un poco inoperativo.

Supongamos un fichero con tres hojas (en realidad podrían ser 300...): ene, feb y mar; donde tenemos una serie de códigos repetidos en las columnas A de cada hoja, igualmente cantidades a consolidar 'unidades' e 'importe' en las columnas B y C.
Es fundamental que se de esta circunstancia: Datos dispuestos en mismas columnas!!!

SUMAR.SI condicionado sobre diferentes hojas



Nuestro objetivo acumular en una primera hoja 'resumen' los valores de unidades e importes que aparezcan en el resto de hojas...

La diferencia respecto al método empleado en el post anterior comentado será el número de hojas involucradas, que podría ser desconocido.
La clave por tanto es identificar y listar los nombres de todas las hojas de libro empleando la función de Excel 4.0: GET.WORKBOOK o en español INDICAR.LIBRO.

OJO!!, esto requerirá que nuestro libro de trabajo se grabe como libro de Excel habilitado para macros!!.

Por otra parte para poder hacer uso de este tipo de funciones en nuestras hojas, deberemos emplearlo dentro de los nombres definidos!!.


El primer paso es generar un nombre definido que comprenderá todas las hojas.
Nuestro nombre definido se llamará 'NombreHojas' y deberemos incluir la siguiente fórmula:
=REEMPLAZAR(INDICAR.LIBRO(1)&T(AHORA());1;ENCONTRAR("]";INDICAR.LIBRO(1)&T(AHORA()));"")

SUMAR.SI condicionado sobre diferentes hojas


INDICAR.LIBRO devuelve el nombre completo de nuestro libro incluyendo la hoja...
Con la función REEMPLAZAR eliminamos el nombre del libro y nos quedamos únicamente con lo que nos interesa, que es el nombre de la hoja.


Para conseguir el listado de nuestras hojas, aplicaremos la función INDICE sobre el conjunto de nombres que retornará INDICAR.LIBRO.
Para ello crearemos una tabla donde insertaremos un orden desde 2 hasta un número alto, que represente la posición de todas las hojas de nuestro libro (esto es, 2,3,4,5, ..., 300).
Comenzamos en 2 por que la hoja 1 es la de 'resumen', hoja sobre la que no deseamos operar.

En la tabla añadimos la fórmula:
=SI.ERROR(INDICE(NombreHojas;[@orden]);"")

SUMAR.SI condicionado sobre diferentes hojas



Ya tenemos el listado de hojas del libro que necesitábamos.
Este paso se puede obviar y escribir manualmente si el listado fuera personalizado.


En el siguiente paso generaremos un nuevo nombre definido con fórmula para trabajar sobre las hojas existentes.
El nombre lo llamaré 'MisHojas' y tendrá la fórmula:
=DESREF(TblHojas[[#Encabezados];[hojas]];1;0;CONTARA(TblHojas[hojas])-CONTAR.BLANCO(TblHojas[hojas]))

que devolverá las hojas existentes, evitando las vacías que provocarían fallos en los pasos siguientes.

SUMAR.SI condicionado sobre diferentes hojas



Y finalmente compondremos nuestra fórmula matricial definitiva para consolidar los datos de las diferentes hojas según el código buscado.

En D4 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!B:B")))

y en E2 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!C:C")))

arrastrando después tanto como necesitemos...

SUMAR.SI condicionado sobre diferentes hojas



Consiguiendo nuestra meta...

martes, 15 de mayo de 2018

VBA: una función para gobernarlas a todas

En el pasado post hablamos de algunos de los métodos del objeto Application...
En el día de hoy aplicaremos uno de ellos .Evaluate, que nos permitirá trabajar con cualquier función de tipo lineal (una variable).
Crearemos de una manera muy sencilla una función para gobernarlas a todas. (Si Mr. Tolkien me permite la licencia...)


En la ventana de código de un módulo estándar incluimos el siguiente procedimiento:

Function FuncionesLineales(fx As String, valor_x As Double) As Double
'aplicamos dos reemplazamientos
'1- para cambiar la variable por el valor concreto asignado
'2- tenemos en cuenta el cambio de configuración de los separadores decimales
'(recuerda que en el entorno del editor de VB el separador decimal es el punto, y no la coma!!)
func = Replace(Replace(fx, "x1", valor_x), ",", ".")

'retornamos el valor a la función creada
FuncionesLineales = Application.Evaluate(func)

End Function



Veamos el uso de nuestra UDF recién creada:

VBA: una función para gobernarlas a todas



Comprobamos cómo a partir de una función expresada como cadena de texto en una celda, somos capaces de convertirla en una función 100% operativa.
Como se observa en la imagen la misma función creada 'FuncionesLineales' aplicada sobre diferentes celdas/funciones, devuelve los datos correspondientes...
Relevante es llamar a nuestra variable como 'x1' (equis uno) para que el reemplazamiento funcione correctamente...

OJO: la función debe emplear los términos, operadores o nombres de funciones en algo entendible en el entorno de programación.

jueves, 10 de mayo de 2018

VBA: El objeto Application en Excel

Me gustaría repasar uno de los objetos de Excel menos conocidos pero más amplios: el objeto Application.

Sobre este objeto Application podemos trabajar con multitud de eventos, métodos y propiedades.
Son tantos que he optado por mostrar un corta de lista de los más frecuentes.


Comenzaré por algunos de los métodos más interesantes (a mi elección):
1- Application.Calculate: que permite forzar el recálculo del libro completo, una hoja o un rango específico.

'Todos los libros abiertos 
Application.Calculate
'o sencillamente 
Calculate

'Una hoja de cálculo determinada 
Worksheets(1).Calculate

'Un rango específico 
Worksheets(1).Range("A1:E10").Calculate



Otro método:
2- Application.Evaluate: de manera similar a INDIRECTO convierte un nombre o texto que siga la convención de nomenclatura de Excel (la longitud del nombre debe ser menor o igual a 255 caracteres) en un objeto o un valor...

Importante!: El uso de corchetes (por ejemplo, "[A1:D13]") equivale a llamar al método Evaluate con un argumento de cadena. Por ejemplo, son equivalentes:

[A13].Value = 1313
Evaluate("A13").Value = 1313

Vble = [COS(90)]
Vble = Evaluate("COS(90)")

Set celda = Workbooks("Libro1.xlsm").Sheets(1).[A1]
Set celda = Workbooks("Libro1.xlsm").Sheets(1).Evaluate("A1")



Otro método más:
2- Application.Quit: sale y cierra la aplicación Excel (no únicamente los libros de trabajo).
Si hubiera algún libro abierto, Excel mostrará un cuadro de diálogo para preguntarnos si queremos guardar los cambios... igual que si cerráramos manualmente la aplicación.

Application.Quit



Otro método más:
2- Application.Union: para componer rangos a partir de otros ya existentes

Set RangoUnion = Application.Union(Range("A1:A13"), Range("C1:C13"))
RangoUnion.Formula = "Excelforo"



Pasaremos a continuación a recorrer algunas propiedades interesantes.

1- Application.Calculation: que permite modificar la opción de cálculo (Automático, Manual o Automático excepto tablas de datos).

2- Application.CalculateBeforeSave: indicaremos True para que los libros se calculen antes de guardarlos en el disco
OJO:si la propiedad Calculation se establece como xlManual.

3- Application.CalculateBeforeSave: indicamos cuál es la tecla habilitada para interrumpir el recálculo de nuestras fórmulas (xlAnyKey, xlNoKey o xlEscKey).

Application.Calculation = xlCalculationAutomatic
Application.CalculateBeforeSave = True
Application.CalculateBeforeSave = xlEscKey



4- Application.Caption: Devuelve o establece un valor String que representa el nombre que aparece en la barra de título de la ventana principal de Microsoft Excel (es la parte superior de Excel... donde encontramos el nombre del libro de trabajo).
Por defecto, si no establecemos un nombre o si se establece el nombre en blanco (Application.Caption = "") , esta propiedad devuelve el texto "Excel".)

Application.Caption = "Aprendiendo VB con Excelforo"




Otra propiedad curiosa:
5- Application.Cursor: Cambia la tipología del icono del cursor entre:
xlDefault-El puntero predeterminado.
xlIBeam-El puntero en i.
xlNorthwestArrow-El puntero flecha noroeste.
xlWait-El puntero reloj de arena.
La propiedad Cursor no se restablecerá automáticamente cuando la macro termine de ejecutarse.
Debereremos restablecer el puntero xlDefault antes de que la macro detiene su ejecución.

Sub CambioCursor()
'xlDefault .El puntero predeterminado.
'xlIBeam .El puntero en i.
'xlNorthwestArrow .El puntero flecha noroeste.
'xlWait .El puntero reloj de arena.
'La propiedad Cursor no se restablecerá automáticamente cuando la macro termine de ejecutarse.
'Deberá restablecer el puntero xlDefault antes de que la macro detiene su ejecución.

 Application.Cursor = xlIBeam
 For x = 1 To 100
 For y = 1 To 100
    Range("A1").Value = x + y
 Next y
 Next x
 Application.Cursor = xlDefault

End Sub



6- Application.DecimalSeparator: para configurar los separadores decimales o de miles de nuestro libro...

Sub CambioSeparadoresSistema()

Range("A1").Formula = "1,234,567.89"
MsgBox "procedemos al cambio..."

'definimos los nuevos separadores
Application.DecimalSeparator = "-"
Application.ThousandsSeparator = " "
'y aplicamos
Application.UseSystemSeparators = False

End Sub



7- Application.DisplayAlerts: daremos valor True para que Excel muestra ciertos mensajes y avisos mientras se ejecuta una macro; y False para suprimir los mensajes y los mensajes de alerta mientras se ejecuta una macro. Cuando un mensaje requiere una respuesta, Microsoft Excel elige la respuesta predeterminada.

'se cierra el libro y no se solicita al usuario que guarde los cambios. 
'No se guardan los cambios realizado en Libro1.xlsm !!
Application.DisplayAlerts = False 
Workbooks("Libro1.xlsm").Close 
Application.DisplayAlerts = True



8- Application.Caption: valor True si los eventos están habilitados para el objeto especificado.
Asociado normalmente a eventos de nuestra hoja de cálculo o objetos ActiveX
OJO por que no es funcional para eventos dentro de un userForm!!

'En este ejemplo deshabilita los eventos antes de que se guarde un archivo para que no se produce el evento BeforeSave
Application.EnableEvents = False 
ThisWorkbook.Save 
Application.EnableEvents = True



9- Application.ScreenUpdating: True si está activada la actualización/refresco de la pantalla.
Acelera la ejecución de las macros...
Recomendable finalizar nuestra macro dando valor False.

'veamos un ejemplo ocultando columnas
Sub TestAceleración_ScreenUpdating()
Dim elapsedTime(2)
'activamos el refresco de pantalla
Application.ScreenUpdating = True
For i = 1 To 2
     'para el segundo caso lo desactivamos
    If i = 2 Then Application.ScreenUpdating = False
    startTime = Time
    Worksheets("Hoja1").Activate
    'recorremos todas las columnas de la hoja
    For Each c In ActiveSheet.Columns
       'si la columna es par
       If c.Column Mod 2 = 0 Then
           'la ocultamos
           c.Hidden = True
       End If
    Next c
    stopTime = Time
    elapsedTime(i) = (stopTime - startTime) * 24 * 60 * 60
Next i
'lo dejamos activo
Application.ScreenUpdating = True
'y mostramos los tiempos
MsgBox "Tiempo transcurrido, screenupdating ON: " & elapsedTime(1) & " segundos." & vbCrLf & _
 "Tiempo transcurrido, screenupdating OFF: " & elapsedTime(2) & " segundos."
End Sub

VBA: El objeto Application en Excel



Y la última propiedad a comentar (por no eternizar la lista):
10- Application.Visible: True o False para determinar si el objeto (nuestra aplicación) está o no visible a ojos del usuario...

'Hacemos Excel invisible.. pero operativo.
Application.Visible = False

'forzamos la espera de 13 segundos
Application.Wait Now + TimeValue("00:00:13")
'con un mensajito
MsgBox "sigo operativo"

'y terminamos haciendo visible la aplicación
Application.Visible = True



Por supuesto hay muchos más.. y te invito a investigar
;-)