En esta sección se recogen todas las ayudas de MS Excel publicadas:
Arrays
El Gurú del Excel
Sitio web dedicado a solventar cualquier tipo de dudas relacionadas con Microsoft Excel y con Visual Basic, por medio de ejemplos y de distintas soluciones.
21 ene 2014
MS Excel - Arrays
A continuación veremos un ejemplo práctico de como utilizar arrays en Microsoft Excel.
Supongamos que tenemos una lista de nombres los cuales pertenecen únicamente a un equipo como se muestra en la siguiente ilustración:
Nuestro objetivo es diseñar una tabla con algunos nombres y el equipo al que pertenecen. El objetivo es algo semejante a lo siguiente:
Para lograr el resultado mostrado en la columna I usaremos arrays, los cuales nos permitirán efectuar una serie de fórmulas condicionales sobre rangos de celdas.
Para ello escribiremos la siguiente fórmula:
=IF(OR(H2=$C$3:$C$4);$B$3;IF(OR(H2=$C$5:$C$9);$B$5;IF(OR(H2=$C$10:$C$22);$B$10;"Ningun equipo")))
Y acto seguido pulsaremos Ctrl + Shift + Enter
De este modo habilitaremos los arrays y automáticamente la veremos la fórmula de la siguiente manera:
Como podemos observar, se han colocado automáticamente unos corchetes '{' y '}' los cuales nos indican que se están utilizando arrays.
Si se sigue todo el procedimiento pero no se llegan a utilizar los arrays, podremos comprobar como la fórmula no funciona correctamente.
Cualquier cuestión que les surja, no duden en preguntar.
Supongamos que tenemos una lista de nombres los cuales pertenecen únicamente a un equipo como se muestra en la siguiente ilustración:
Nuestro objetivo es diseñar una tabla con algunos nombres y el equipo al que pertenecen. El objetivo es algo semejante a lo siguiente:
Para lograr el resultado mostrado en la columna I usaremos arrays, los cuales nos permitirán efectuar una serie de fórmulas condicionales sobre rangos de celdas.
Para ello escribiremos la siguiente fórmula:
=IF(OR(H2=$C$3:$C$4);$B$3;IF(OR(H2=$C$5:$C$9);$B$5;IF(OR(H2=$C$10:$C$22);$B$10;"Ningun equipo")))
Y acto seguido pulsaremos Ctrl + Shift + Enter
De este modo habilitaremos los arrays y automáticamente la veremos la fórmula de la siguiente manera:
Como podemos observar, se han colocado automáticamente unos corchetes '{' y '}' los cuales nos indican que se están utilizando arrays.
Si se sigue todo el procedimiento pero no se llegan a utilizar los arrays, podremos comprobar como la fórmula no funciona correctamente.
Cualquier cuestión que les surja, no duden en preguntar.
20 ene 2014
Macro - Guardar un archivo Excel a fecha de hoy
Con la siguiente macro guardaremos un fichero de Excel con la fecha del dia de hoy en su nombre.
Para ello suponemos que anteriormente el nombre del archivo seguía el mismo patrón.
Es decir, si antes era NombreFichero140116.xls, ahora lo guardaremos como NombreFichero140120.xls.
Para ello suponemos que anteriormente el nombre del archivo seguía el mismo patrón.
Es decir, si antes era NombreFichero140116.xls, ahora lo guardaremos como NombreFichero140120.xls.
Sub GuardarFecha() 'Guardar archivo con fecha
de hoy
Dim dia As String
Dim nombre$
nombre$ =
ThisWorkbook.Name 'Cogemos el nombre actual del fichero
nombre$ = Left(nombre$, InStr(1, nombre$, ",") - 1) 'Le quitamos la fecha anterior
dia
= Format(Date, "yymmdd") 'Cogemos la fecha del dia de hoy
nombre$ = nombre$ & ", " & dia 'Concatenamos el nombre
del fichero con la fecha de hoy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" &
nombre$ 'Guardamos el documento a fecha de hoy
End Sub
Macro - Pintar pestañas
Macro para pintar las pestañas de un excel.
Dim hoja1, hoja2 As String
hoja1 =
"Hoja 1"
hoja2 = "Hoja 2"
Dim
hojas As Integer
Sheets(hoja1).Tab.Color
= 65535 'Pintamos la hoja 1 de amarillo
Sheets(hoja2).Tab.Color = xlNone 'Dejamos la hoja 2 sin color
Loop
Podríamos pintar todas las hojas del libro con un bucle.
Etiquetas:
ayuda,
color,
dudas,
excel,
hojas,
macro,
pestañas,
pintar,
programacion,
visual basic
Control de errores
Siempre es recomendable mantener un control en nuestras macros de Visual Basic sobre los errores que puedan saltar durante la ejecución.
Para ello incluiremos un código para controlarlas al inicio de la macro.
Tenemos dos opciones:
1.- En caso de error, omitimos la instrucción y pasamos a la siguiente (NO RECOMENDADO).
2.- En caso de error, tratamos el error en una subrutina.
Para ello incluiremos un código para controlarlas al inicio de la macro.
Tenemos dos opciones:
1.- En caso de error, omitimos la instrucción y pasamos a la siguiente (NO RECOMENDADO).
'**************************************************
'CONTROL DE ERRORES
On
Error Resume Next 'En caso de error pasa a la siguiente instruccion
'*******************************
'**************************************************
'CONTROL DE ERRORES
On Error GoTo ControldeErrores 'En caso de error se pasa a la subrutina ControldeErrores
'*******************************
Dicha subrutina está al final de la macro:
ControldeErrores:
'Código de la subrutina. Se recomiendo mostrar un mensaje personalizado indicando que existen errores en la ejecución de la macro.
Resume Next
Optimizar Macros
A continuación se muestra un código que optimizará todas las macros que realicen cálculos sobre una hoja de excel.
Básicamente consiste en deshabilitar al inicio de la macro los cálculos del Excel, la actualización automática de las celdas y demás, y posteriormente habilitarlas nuevamente.
Básicamente consiste en deshabilitar al inicio de la macro los cálculos del Excel, la actualización automática de las celdas y demás, y posteriormente habilitarlas nuevamente.
'**************************************************
'OPTIMIZACIÓN (Al principio de la macro)
'**************************************************
Application.ScreenUpdating = False
Application.Calculation =
xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
'**************************************************
'**************************************************
'OPTIMIZACIÓN (Al final de la macro)
'**************************************************
Application.ScreenUpdating = True
Application.Calculation =
xlCalculationAutomatic
Application.EnableEvents = True
Application.CutCopyMode = False
'**************************************************
Macro - Actualizar Pie de página
Con la siguiente macro seremos capaces de actualizar todos los pie de página de un libro entero de Excel a la vez.
Esto nos será de gran utilidad cuando tengamos archivos de excel donde en el pie de página varíe la fecha mes a mes.
Sub Pie_de_pagina()
Dim sWhat As String, sReplacment As String
Const csTITLE As String = "Find and Replace"
'Un inputbox nos preguntará el texto que queremos modificar
sWhat = InputBox("Remplazar el texto", csTITLE)
If Len(sWhat) = 0 Then Exit Sub
'Un inputbox nos preguntará el texto que nuevo que queramos introducir
sReplacment = InputBox("Con el nuevo texto", csTITLE)
'Contador para recorrer las hojas
contador = Sheets.Count
For i = 1 To contador
Sheets(i).Activate
With ActiveSheet.PageSetup
' Substitute Footer values
.LeftFooter = Application.WorksheetFunction.Substitute( _
.LeftFooter, sWhat, sReplacment)
.CenterFooter = Application.WorksheetFunction.Substitute( _
.CenterFooter, sWhat, sReplacment)
.RightFooter = Application.WorksheetFunction.Substitute( _
.RightFooter, sWhat, sReplacment)
End With
Next i
End Sub
Esto nos será de gran utilidad cuando tengamos archivos de excel donde en el pie de página varíe la fecha mes a mes.
Sub Pie_de_pagina()
Dim sWhat As String, sReplacment As String
Const csTITLE As String = "Find and Replace"
'Un inputbox nos preguntará el texto que queremos modificar
sWhat = InputBox("Remplazar el texto", csTITLE)
If Len(sWhat) = 0 Then Exit Sub
'Un inputbox nos preguntará el texto que nuevo que queramos introducir
sReplacment = InputBox("Con el nuevo texto", csTITLE)
'Contador para recorrer las hojas
contador = Sheets.Count
For i = 1 To contador
Sheets(i).Activate
With ActiveSheet.PageSetup
' Substitute Footer values
.LeftFooter = Application.WorksheetFunction.Substitute( _
.LeftFooter, sWhat, sReplacment)
.CenterFooter = Application.WorksheetFunction.Substitute( _
.CenterFooter, sWhat, sReplacment)
.RightFooter = Application.WorksheetFunction.Substitute( _
.RightFooter, sWhat, sReplacment)
End With
Next i
End Sub
Macro - Eliminar código de Visual Basic
Con la siguiente macro lograremos borrar código de una macro de excel. Es decir, será posible ejecutar una macro y que una vez ejecutada, se borre su código sin dejar rastro. Para ello, la macro debe estar en un módulo de Visual Basic.
Sub EliminaMacro()
Dim liDeb, NbLi
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
liDeb = .ProcStartLine("NombreDeLaMacroQueQueremosBorrar", 0)
NbLi = .ProcCountLines("NombreDeLaMacroQueQueremosBorrar", 0)
.DeleteLines liDeb, NbLi
End With
End Sub
Sub EliminaMacro()
Dim liDeb, NbLi
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
liDeb = .ProcStartLine("NombreDeLaMacroQueQueremosBorrar", 0)
NbLi = .ProcCountLines("NombreDeLaMacroQueQueremosBorrar", 0)
.DeleteLines liDeb, NbLi
End With
End Sub
Macro - Ejecutar macro al abrir un archivo Excel
Para ejecutar una macro al abrir un archivo Excel basta con utilizar la función predefinida Auto_open() y dentro de ella escribir la macro que queremos que se ejecute, cuyo código ha de existir en este mismo fichero Excel.
Sub Auto_open()
nombredelaMacro
End Sub
Sub Auto_open()
nombredelaMacro
End Sub
Macro - Ejecutar macro a una determinada hora
Con este código se ejecutará una macro a una hora predeterminada. Por ejemplo, en el código siguiente, se lanzaría a las 13:20. El único requisito es que el archivo excel debe permanecer abierto.
'Sub macro()
' Application.OnTime TimeValue("13:20"), "Nombre de la macro que queremos ejecutar"
'End Sub
'Sub macro()
' Application.OnTime TimeValue("13:20"), "Nombre de la macro que queremos ejecutar"
'End Sub
Macros - Visual Basic
En esta sección se recogen todas las macros publicadas:
Actualizar Pie de página
Control de errores
Ejecutar macro a una determinada hora
Ejecutar macro al abrir un Excel
Eliminar código de Visual Basic
Enviar e-mail desde Microsoft Outlook usando Visual Basic
Guardar un Excel con la fecha de hoy
Optimizar macros
Pintar pestañas
Actualizar Pie de página
Control de errores
Ejecutar macro a una determinada hora
Ejecutar macro al abrir un Excel
Eliminar código de Visual Basic
Enviar e-mail desde Microsoft Outlook usando Visual Basic
Guardar un Excel con la fecha de hoy
Optimizar macros
Pintar pestañas
Macro - Enviar e-mail desde Microsft Excel usando Visual Basic
En esta entrada veremos como enviar un e-mail através de Microsoft Outlook, usando una macro programada en Visual Basic.
Sub enviarMail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
'Definimos la variable que contendrá el mensaje.
Dim email As String
email = "Buenos días, " & vbCrLf & vbCrLf & "Cuerpo del mensaje"
'Utilizamos el comando vbCrLf para añadir saltos de línea en el mensaje
With OutMail
'.To = "Destinatarios separados por ;"
.CC = "Destinatarios en copia separados por ;"
.BCC = "Destinatarios en copia oculta separados por ;"
.Subject = "Asunto"
.Body = email 'Aquí va la variable del cuerpo del mensaje con todo el texto
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Solo faltaría añadir la macro a algún botón para ejecutarla cómodamente siempre que nos haga falta.
Un saludo.
Sub enviarMail()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
'Definimos la variable que contendrá el mensaje.
Dim email As String
email = "Buenos días, " & vbCrLf & vbCrLf & "Cuerpo del mensaje"
'Utilizamos el comando vbCrLf para añadir saltos de línea en el mensaje
With OutMail
'.To = "Destinatarios separados por ;"
.CC = "Destinatarios en copia separados por ;"
.BCC = "Destinatarios en copia oculta separados por ;"
.Subject = "Asunto"
.Body = email 'Aquí va la variable del cuerpo del mensaje con todo el texto
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Solo faltaría añadir la macro a algún botón para ejecutarla cómodamente siempre que nos haga falta.
Un saludo.
Dudas
Sección destinada a plantear dudas que tengan acerca de Excel y de Visual Basic.
Intentaré de solucionar dichas dudas y de publicarlas lo más rápido posible. Si dejan su email les avisaré cuando su duda haya sido respondida.
Intentaré de solucionar dichas dudas y de publicarlas lo más rápido posible. Si dejan su email les avisaré cuando su duda haya sido respondida.
Suscribirse a:
Entradas (Atom)


