EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS

Hola a todos, ¿qué tal estáis?, ¡espero que bien!.

Hace unos días envié a un lector las instrucciones para ejecutar un procedimiento programado en Access usando el programador de tareas de windows. En el post de hoy mostraré la manera de poder hacer de una forma muy sencilla en Excel.

Antes de comenzar vamos a seleccionar una tarea que queramos que se ejecute todos los días y cuya ejecución se realice mediante una macro. Por ejemplo que todos los días a las siete de la mañana nuestro ordenador cierre Microsoft Word en nuestro equipo.

Para ello, tenemos en esta web una macro que sirve para cerrar programas, entre ellos, word: CERRAR UN PROGRAMA DESDE EXCEL CON VBA

Y vamos a añadir en el módulo de Thisworkbook:

Private Sub Workbook_Open()
Call Cierra_Programa
End Sub

A continuación os mostraré cómo acceder al programador de tareas, lo más fácil es usar el buscador de nuestra barra de herramientas o preguntar a “Cortana” por el programador de tareas:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS

Una vez que lo tenemos abierto, se nos mostrarán una serie de pantallas que iremos completando:

En la primera pantalla se nos presentan varias acciones a realizar, en nuestro ejemplo, elegiremos crear una tarea básica:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS2

A continuación, debemos nombrar nuestra tarea y añadir un breve descripción. Luego pulsamos en “Siguiente”:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS3

En la siguiente pantalla “Desencadenar”, vamos a indicar la periodicidad con la que queremos que se ejecute nuestra tarea y la hora:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS4

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS5

En el siguiente paso debemos marcar la opción “Iniciar un programa“:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS6

Y en la siguiente pantalla debemos seleccionar el archivo en el que hemos incluido la macro para cerrar word:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS7

Finalmente pulsamos en finalizar y se nos mostrará un resumen con los pasos realizados:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS8

Ahora ya tenemos la tarea creada y, cuando lleguen las siete de la mañana, windows abrirá el archivo que hemos indicado y, como hemos incluido un macro en el evento workbook.open:

Private Sub Workbook_Open()
Call Cierra_Programa
End Sub

La macro se ejecutará en el instante Excel se abra. Os recomiendo que modifiquéis la seguridad de macros para evitar que las macros queden bloqueadas por el propio Excel.

Si en algún momento queréis prescindir de una tarea, solo tenéis que volver al programador, localizar la tarea y eliminarla.

Para este ejemplo, no voy dejar archivo, dado que es puramente teórico, os propongo que la práctica la realicéis vosotros según vuestras necesidades.

Importante: Para que funcione debéis dejar el ordenador encendido, no es necesario que Excel esté abierto, ese trabajo lo hará Windows por nosotros.

Y eso es todo, espero que os haya sido de utilidad.

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

ENVIAR FIRMA CON IMAGEN Y TEXTO DESDE OUTLOOK USANDO EXCEL Y VBA

Hola a todos, ¿qué tal estáis?, espero que muy bien.

En el post de hoy voy os mostraré un método para incorporar imágenes y texto en un correo que vamos a enviar desde Outlook pero utilizando VBA en Excel.

Este tema suele ser muy recurrente y no es la primera vez que me piden en la web una macro para realizar esta tarea.

En una hoja Excel utilizaré dos columnas, una para indicar el correo electrónico y otra para indicar el texto de correo que queremos enviar:

ENVIAR FIRMA CON IMAGEN Y TEXTO DESDE OUTLOOK USANDO EXCEL Y VBA

Para enviar el correo utilizaremos la siguiente macro:

ENVIAR FIRMA CON IMAGEN Y TEXTO DESDE OUTLOOK USANDO EXCEL Y VBA1

El código os lo dejo como imagen (el motivo es el campo HTMLBody que al tener caracteres en HTML se ejecutan en el post y hacen que no se vea correctamente). De todas formas, en el adjunto lo podéis copiar.

En esta macro generamos un correo por cada línea de datos que tengamos en la hoja “CORREO”. Para que la imagen sea visible y se pueda embeber o incrustar en el correo primero hacemos referencia al lugar en el que se encuentra del equipo, en este caso es el logo de Excel Signum:

imagen = "C:\Users\Segu\Documents\EXCEL SIGNUM\EXCELSIGNUM.jpg"

Una vez que la hemos seleccionado la adjuntamos al correo e indicamos que no sea visible como archivo adjunto:

.Attachments.Add imagen, 0

Ahora que ya la tenemos, componemos con Html la firma del correo:

ENVIAR FIRMA CON IMAGEN Y TEXTO DESDE OUTLOOK USANDO EXCEL Y VBA2

En la firma he incluido un hipervínculo a mi web y hago referencia a la imagen que hemos adjuntado y ocultado para que se muestre en el cuerpo del correo. Para que quede como si fuese una firma lo ponemos al final e incorporamos unos cuantos saltos de línea “br” antes del texto que he incluido en la variable “texto”.

El resultado es el siguiente:

ENVIAR FIRMA CON IMAGEN Y TEXTO DESDE OUTLOOK USANDO EXCEL Y VBA3

Como podéis observar, ya tenemos nuestra firma con imagen y texto (y un vínculo) en nuestro cuerpo de correo. Perfecto!.

Este método solo funcionará correctamente en outlook, dado que estamos trabajando en todo momento con el objeto Outlook.applicatacion.

Y eso es todo, espero que os resulte de interés y lo podáis usar en vuestras comunicaciones.

Descarga el archivo de ejemplo pulsando en: ENVIAR FIRMA CON IMAGEN Y TEXTO DESDE OUTLOOK USANDO EXCEL Y VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

OBTENER NÚMEROS ALEATORIOS SIN DUPLICADOS

Hola a todos!:

Esta semana he recibido varias consultas y como siempre, me gustaría compartir con vosotros alguna de ellas.

En concreto, me pedían una macro que pudiese generar números aleatorios entre un rango establecido y que fuesen únicos, sin duplicados.

Bien, sobre este tema hay varias macros que se pueden encontrar por la red (incluso usando solo fórmulas), pero he querido dedicarle un poco de tiempo y desarrollar un código distinto y que creo que es más sencillo de comprender.

Como el lector quería que a través de la hoja Excel pudiera indicar la cantidad de números a generar y también el tramo entre los que obtener los números, lo adapté a sus necesidades.

Utilizaremos la “Hoja1” e indicaremos los siguientes campos donde vamos a generar 5 números aleatorios y únicos entre el 1 el 50 (ambos inclusive):

OBTENER NUMEROS ALEATORIOS SIN DUPLICADOS

Ahora, para poder generar la información, debemos pegar este código en un módulo estándar de nuestros editor de VBA:

Option Explicit
Sub OBTENER_NUMEROS_ALEATORIOS_UNICOS()
'Declaramos variables
Dim oDic As Object, palabra As Variant
Dim Micelda As String, matrix1 As Variant, matrix2 As Variant
Dim sCadena As String, i As Integer, unicos As String
Dim j As Integer, nNum As Double, fin As Integer
With Sheets("Hoja1")
'eliminamos información generada en la consulta anterior.
fin = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Rows.Count
.Range("A2:A" & 2 + fin).ClearContents
'Creamos objeto diccionario
Set oDic = CreateObject("scripting.dictionary")
'Ejecutamos loop hasta el total de números que queremos obtener
Do Until j = .Cells(2, 6)
'generamos aleatorios entre 1 y 50
nNum = Application.WorksheetFunction.RandBetween(.Cells(4, 6), .Cells(4, 7))
'componemos string con los números que vamos generando
Micelda = Micelda & " " & nNum
matrix1 = Split(Micelda, " ")
'Eliminamos números repetidos
For i = 0 To UBound(matrix1)
If Not oDic.Exists(matrix1(i)) Then oDic.Add matrix1(i), matrix1(i)
Next i
'Creamos una nueva cadena sin duplicados y seguimos el loop
unicos = Join(oDic.Keys, " ")
sCadena = Trim(unicos)
matrix2 = Split(sCadena, " ")
'contamos los números aleatorios únicos que vamos generando
j = UBound(matrix2) + 1
Loop
'Pasamos los datos a la hoja1
matrix2 = Split(sCadena, " ")
For j = 0 To UBound(matrix2)
.Cells(j + 2, 1) = matrix2(j)
Next j
End With
'Vaciamos variable de objeto
Set oDic = Nothing
End Sub

El resultados que vamos a obtener es el siguiente:

OBTENER NUMEROS ALEATORIOS SIN DUPLICADOS1

La macro genera correctamente la cantidad de números que hemos indicado y entre el rango establecido. Básicamente, lo que hacemos es ir componiendo una cadena de texto con los números que se van generando aleatoriamente y a la vez con el objeto dictionary eliminando los duplicados hasta llegar a la cantidad especificada en la hoja Excel.

El loop se ejecutará tantas veces como sea necesario para llegar al valor que hemos indicado.

Esta aplicación resulta muy interesante y puede ser útil para multitud de tareas, desde un generador para obtener números de la lotería, primitiva, euromillones, etc hasta para tareas de encriptado.

Por cierto, si la usáis para jugar a loterías y os toca, acordaos de mi : )

Como siempre, os dejo la hoja con el ejemplo:

Descarga el archivo de ejemplo pulsando en: OBTENER NÚMEROS ALEATORIOS SIN DUPLICADOS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

ELIMINAR DUPLICADOS DIRECTAMENTE SOBRE EL CONTENIDO DE UN LISTBOX

Hola a todos:

Ya sabéis que en esta web hay varias entradas que tratan el tema de los duplicados o cómo obtener registros únicos en nuestras informaciones, tanto en formularios como en bases de datos y usando métodos diferentes.

Sin embargo, esta semana un lector me solicitaba lo siguiente:

¿Es posible quitar los duplicados de un listbox mediante un boton
tomando los datos directamente del listbox sin tener que guardar la informacion a una hoja o tabla de calculo y que mantengan el mismo orden en el que fueron encontrados los registros por primera vez?

Es decir, interactuar directamente con el contenido del listbox sin posibilidad de acudir a la fuente de los datos y eliminar los duplicados antes de cargar la información en el listbox.

Aunque soy partidario de tratar la información antes de enviarla a un listbox (o combobox, etc…), lo cierto es que sí es posible hacer lo que el lector solicita. Y ese es el objetivo de este post.

Vamos a tomar un ejemplo en el que tenemos una base de datos con duplicados:

ELIMINAR DUPLICADOS DIRECTAMENTE SOBRE EL CONTENIDO DE UN LISTBOX

Estos datos los pasamos a un listbox en nuestro formulario y el código que utilizaremos para cargar el listbox es el siguiente:

Private Sub UserForm_Initialize()
Dim fin As Long
'Indicamos el número de columnas que tendrá el listbox
Me.ListBox1.ColumnCount = 7
'Definimos tamaño de los espacios
Me.ListBox1.ColumnWidths = "30pt;150pt;150pt;50pt;50pt;60pt"
'Cargamos listbox
With Sheets("BBDD")
fin = Application.CountA(.Range("A:A"))
Me.ListBox1.List = .Range("A2:G" & fin).Value
End With
End Sub

Es importante usar la propiedad listbox.list para cargar la información, dado que nos posibilitará el poder eliminar duplicados directamente en el listbox con el método RemoveItem.

ELIMINAR DUPLICADOS DIRECTAMENTE SOBRE EL CONTENIDO DE UN LISTBOX1

Podéis ver que nuestro listbox muestra todos los duplicados, pues bien, para eliminar los duplicados usaremos esta otra macro, vinculada al botón de comando “ELIMINAR DUPLICADOS”:

Private Sub CommandButton1_Click()
'Declaramos variables
Dim i As Long, j As Long
With ListBox1
'Eliminamos duplicados
For i = 0 To .ListCount - 1
For j = .ListCount - 1 To (i + 1) Step -1
If .List(j) = .List(i) Then .RemoveItem j
Next j
Next i
End With
End Sub

Una vez que hemos pulsado el botón, automáticamente se eliminan los duplicados en nuestro listbox, así:

ELIMINAR DUPLICADOS DIRECTAMENTE SOBRE EL CONTENIDO DE UN LISTBOX2

Pues bien, el utilizar la propiedad .list nos ha permitido solucionar este problema. Ahora ya tenemos nuestro listbox sin duplicados y listo para ser usado en otras tareas.

Espero que os haya resultado interesante y que os pueda ayudar en vuestros trabajos y proyectos.

Como siempre, os dejo el archivo de ejemplo : )

Descarga el archivo de ejemplo pulsando en: ELIMINAR DUPLICADOS DIRECTAMENTE SOBRE EL CONTENIDO DE UN LISTBOX

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

OBTENER DATOS A TRAVÉS DEL OBJETO WScript.Network Y OTRAS INFORMACIONES

Hola a todos!.

Recientemente recibí una consulta en la que un lector me preguntaba si le podía echar una mano  con una macro con la que deseaba extraer información de su equipo, en concreto necesitaba obtener el usuario de un equipo, el nombre del equipo, el nombre del dominio y la fecha y hora de la extracción de los datos.

Para poder conseguir los datos vamos a programar sobre el objeto WScript.Network, dado que sus propiedades (Username, UserDomain, ComputerName) nos van a permitir conocer los tres primeros datos.

De hecho, con esta simple macro obtendríamos la información:

Set sNetwork = CreateObject("WScript.Network")
nEqu = sNetwork.ComputerName
nDom = sNetwork.UserDomain
nUser = sNetwork.UserName

Efectivamente, creamos el objeto WScript.Networky a través de sus propiedades obtenemos la información que guaramos en tres variables.

Para obtener la fecha y la hora, podríamos usar la función Now en VBA, pero dado que estamos trabajando con scripts, lo haremos de la siguiente forma, con un objeto SWbemDateTime:

Set dtTime = CreateObject(“WbemScripting.SWbemDateTime”)
dtTime.SetVarDate (Now)
rDate = dtTime.GetVarDate

Y ya tendríamos los datos que el lector nos ha pedido. : )

El código completo sería así (con algunas cosas más que he añadido)

Sub INFORMACION_USUARIO()
'Declaramos variables
Dim sNetwork As Object, fSistem As Object, tSistem As Object, dir_Archivo As Object
Dim dtTime As Object, rDate As Date, i As Integer, MatrizInfo As Variant
Dim nEqu As String, nDom As String, nUser As String, Directorio As String
'Abrimos cuadro de dialogo para seleccionar carpeta
Set dir_Archivo = Application.FileDialog(msoFileDialogFolderPicker)
dir_Archivo.Show
'Si no seleccionamos nada salimos del proceso
If dir_Archivo.SelectedItems.Count = 0 Then
Exit Sub
End If
'Guardamos ruta a esta carpeta
Directorio = dir_Archivo.SelectedItems(1)
'Creamos objeto Network para obtener las propiedades de usuario, equipo y dominio
Set sNetwork = CreateObject("WScript.Network")
nEqu = sNetwork.ComputerName
nDom = sNetwork.UserDomain
nUser = sNetwork.UserName
'obtenemos la fecha y hora del sistema
Set dtTime = CreateObject("WbemScripting.SWbemDateTime")
dtTime.SetVarDate (Now)
rDate = dtTime.GetVarDate
'Creamos objeto filesistem para crear TXT
Set fSistem = CreateObject("Scripting.FileSystemObject")
Set tSistem = fSistem.CreateTextFile(Directorio & "\INFORMACION.txt", True)
'Pasamos los datos al TXT
tSistem.WriteLine "Nombre del equipo:" & nEqu
tSistem.WriteLine "Nombre del dominio:" & nDom
tSistem.WriteLine "Nombre del usuario:" & nUser
tSistem.WriteLine "Fecha y hora:" & rDate
'También pasamos los datos a la hoja
MatrizInfo = Array(nEqu, nDom, nUser, rDate)
For i = LBound(MatrizInfo) To UBound(MatrizInfo)
Sheets(1).Cells(i + 1, 1) = MatrizInfo(i)
Next i
'Vaciamos variable de objeto
Set sNetwork = Nothing
Set fSistem = Nothing
Set tSistem = Nothing
Set dir_Archivo = Nothing
Set dtTime = Nothing
End Sub

Me ha parecido interesante incluir un cuadro de diálogo para seleccionar un directorio en el que vamos a guardar en un TXT los datos extraídos:

El cuadro de diálogo y la extracción del directorio lo hacemos así:

Set dir_Archivo = Application.FileDialog(msoFileDialogFolderPicker)
dir_Archivo.Show
'Si no seleccionamos nada salimos del proceso
If dir_Archivo.SelectedItems.Count = 0 Then
Exit Sub
End If
'Guardamos ruta a esta carpeta
Directorio = dir_Archivo.SelectedItems(1)

y para crear el TXT y pasar los datos, usaremos el objeto FileSystemObject:

'Creamos objeto filesistem para crear TXT
Set fSistem = CreateObject("Scripting.FileSystemObject")
Set tSistem = fSistem.CreateTextFile(Directorio & "\INFORMACION.txt", True)
'Pasamos los datos al TXT
tSistem.WriteLine "Nombre del equipo:" & nEqu
tSistem.WriteLine "Nombre del dominio:" & nDom
tSistem.WriteLine "Nombre del usuario:" & nUser
tSistem.WriteLine "Fecha y hora:" & rDate

Una vez seleccionada la carpeta la macro creará el TXT (INFORMACION) y pasará la info, en mi caso la información es muy repetitiva, dado que siempre es la misma:

OBTENER DATOS A TRAVES DEL OBJETO WScript.Network Y OTRAS INFORMACIONES

Por último, he añadido los resultados en una matriz que luego pasamos a un loop para mostrar la información en la primera hoja de nuestro archivo, en la columna A:

MatrizInfo = Array(nEqu, nDom, nUser, rDate)
For i = LBound(MatrizInfo) To UBound(MatrizInfo)
Sheets(1).Cells(i + 1, 1) = MatrizInfo(i)
Next i

Y se mostraría así:

OBTENER DATOS A TRAVES DEL OBJETO WScript.Network Y OTRAS INFORMACIONES2

Aunque he incluido algunas cosas que el lector no pedía, creo que siempre es bueno incorporar herramientas nuevas o diferentes, de un modo o de otro, siempre nos podrían servir para otros proyectos.

Espero que os haya resultado de interés y os pueda ser de utilidad.

Descarga el archivo de ejemplo pulsando en:  OBTENER DATOS A TRAVES DEL OBJETO WScript.Network Y OTRAS INFORMACIONES

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

BUSCAR VALORES CON EL MÉTODO RANGE.FIND EN EXCEL CON VBA

Hola a todos!.

¿Qué tal todo?, ¡espero que bien!.

Hoy voy a escribir acerca de un método interesante para buscar datos en Excel. Se trata del método range.find, que en comparación con otras funciones, se presenta mucho más rápido que un loop y ofrece mayor versatilidad que el uso de fórmulas.

Su uso es muy sencillo y ofrece unos resultados muy interesantes. Imaginad que tenemos en una hoja una dispersión de nombres de personas por diferentes celdas:

BUSCAR VALORES CON EL METODO RANGE.FIND EN EXCEL CON VBA

y estamos realizando una programación para obtener la localización del nombre que hemos seleccionado. Lo primero que se nos puede ocurrir es plantear un loop que recorra todas las celdas y vaya seleccionando el nombre que hemos elegido, sin embargo esto ocasionaría que nuestro código fuese demasiado lento (y sobre todo si ampliamos el tamaño del área que contiene la información).

La solución optima sería el uso del método range.find y que vamos a usar en el siguiente código:

Sub ENCONTRAR_DATO()
'Declaramos variables
Dim Dato As Range, cDato As String, nDato As String
Dim sLoc As String, nombre As String
With Sheets("DATOS").Cells
Sheets("RESULTADO").Cells(3, 2).ClearContents
'En la celda B2 indicamos el nombre a buscar
nombre = Sheets("RESULTADO").Cells(2, 1).Value
'Si el nombre está vacío salimos del proceso
If nombre = vbNullString Then Exit Sub
'Buscamos nombre (cualquier palabra o texto que lo contenga)
Set Dato = .Cells.Find(What:=nombre, lookat:=xlWhole)
'Si el nombre no existe, controlamos el error
On Error Resume Next
cDato = Dato.Address
On Error GoTo 0
'Si existe nombre entonces iniciamos un loop
If Not Dato Is Nothing Then
Do
'Que busca todas las coincidencias
Set Dato = .FindNext(Dato)
'En una cadena de texto grabamos la localización
'de las coincidencias
nDato = Dato.Address
sLoc = sLoc & " " & nDato
'Cuando la cDato sea igual a nDato
'salimos del loop
Loop Until cDato = nDato
End If
'pasamos los datos a celda B3
Sheets("RESULTADO").Cells(3, 1) = sLoc
End With
End Sub

Este código lo pegamos en un módulo estándar de nuestro editor VBA y en la hoja RESULTADO incluimos un botón de comando. El nombre a buscar lo escribimos en la celda A2 y ejecutamos la macro pulsando el botón.

En este caso, buscamos todos los nombres que se correspondan con “RAQUEL”, obteniendo el siguiente resultado:

BUSCAR VALORES CON EL METODO RANGE.FIND EN EXCEL CON VBA2

Es decir que existen dos resultados, ambos en las celdas J15 Y A14 respectivamente.

He dejado el dólar en el resultado, pero lo podéis eliminar usando un replace: replace(sloc,"$","")

Si comprobamos en la hoja DATOS podemos ver que el resultado es correcto:

BUSCAR VALORES CON EL METODO RANGE.FIND EN EXCEL CON VBA3.jpg

Y esto ha sido todo. Como podéis observar, se trata de un código sencillo y muy útil como alternativa al uso de loops que recorren grandes rangos.

Espero que os haya resultado de interés y os pueda ser de utilidad.

Descarga el archivo de ejemplo pulsando en:  BUSCAR VALORES CON EL MÉTODO RANGE.FIND EN EXCEL CON VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA

Hola a todos:

Espero que hayáis tenido unas buenas fiestas!. Ahora que ya se acaban, es hora de volver a lo cotidiano y como no podía ser de otra manera, voy a escribir un sencillo post para mostrar cómo podemos calcular una matriz de correlaciones (aunque se podría utilizar para calcular otros estadísticos).

Para este trabajo me he descargado los datos de temperatura media durante el año 2015 en ciertos puntos de España (Fuente: INE). Aquí podéis ver la tabla de ejemplo que vamos a usar:

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA

Es solo una muestra de algunos de los puntos de extracción de información climatológica, pero que nos va a servir perfectamente para realizar nuestro ejemplo.

A continuación,  en una hoja nueva, vamos a crear una matriz de 15 X 15 dado que son el total de elementos que tenemos:

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA1

El siguiente paso es utilizar una sencilla UDF, para ayudarnos con el cálculo de las correlaciones, para ello, en un módulo estándar de nuestro editor de VBA insertáis este código:

Function Correlacion_matrix(rng As Range)
'Declaramos variables
Dim nColum As Long, i As Long, j As Long
Dim matrix As Variant
nColum = rng.Columns.Count - 1
'dimensionamos la matriz
ReDim matrix(nColum, nColum)
'Iniciamos loop para calcular matriz de correlaciones
For i = 0 To nColum
For j = 0 To nColum
matrix(i, j) = Application.WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))
Next j
Next i
'Pasamos resultado a la función
Correlacion_matrix = matrix
End Function

Ahora ya podemos utilizar esta función en nuestra hoja para realizar el cálculo matricial. Para introducir la función, debemos ir a la hoja MATRIZ y seleccionar el espacio que vamos a utilizar, es decir el rango B2:P16

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA2.jpg

Y ahora, esto es importante, sin desmarcar el área seleccionada, os situáis en el espacio de fórmulas y escribís el nombre de la función: =Correlacion_matrix( y os posicionáis en la hoja DATOS, seleccionando el área que contiene los datos de temperatura, que es el rango B2:P13. Es decir, la fórmula quedaría así: =Correlacion_matrix(DATOS!B2:P13)

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA3

Ahora que tenemos la fórmula completa, finalizamos la función seleccionando la fórmula y luego presionando CTRL + MAYUS + ENTRAR

El resultado es este:

CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA4

Como podéis observar, ya tenemos nuestra matriz de correlaciones entre las temperaturas medias mensuales durante el año 2015. Dado que las cifras hacen referencia a las temperaturas recogidas en el total de las estaciones, y estas son las mismas para todos los lugares con variaciones similares (no de la temperatura, sino de los incrementos y decrementos y su proporción), la correlación es alta. Las variaciones las debemos buscar en el segundo y tercer decimal, si bien, el dato a interpretar la correlación de la proporción de aumento o disminución de temperatura en el rango de tiempo seleccionado.

Obviamente, en la diagonal de la matriz siempre aparecerá una línea con un 1, que es donde se cruzan los datos del mismo lugar y la correlación es perfecta.

Y esto es todo por hoy, espero que os haya resultado de interés!!.
Descarga el archivo de ejemplo pulsando en: CALCULAR UNA MATRIZ DE CORRELACIONES EN EXCEL CON VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!