21 ene 2014

MS Excel

En esta sección se recogen todas las ayudas de MS Excel publicadas:

Arrays

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.

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.

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.

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).

'**************************************************
    'CONTROL DE ERRORES
    On Error Resume Next 'En caso de error pasa a la siguiente instruccion

    '*******************************

2.- En caso de error, tratamos el error en una subrutina.
'**************************************************
    '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.

'**************************************************
'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

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


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

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

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

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.

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.