Auto_Open() y Auto_Close()

Existe una macro de autoarranque que se ejecuta cuando se abre el libro. Se llama auto_open().

Y existe otra que se ejecuta justo antes de cerrar el libro que se llama auto_close().

Sub Auto_Open()
    Dim hora As Double
    Dim saludo As String
    hora = (Now - Int(Now)) * 24
    Select Case hora
        Case 6 To 14
            saludo = "Buenos días"
        Case 14 To 21
            saludo = "Buenas tardes"
        Case Else
            saludo = "Buenas noches"
        End Select
    MsgBox saludo & " Amo"
End Sub

Equivalente a auto_open existe otra macro Workbook_Open, pero ésta ha de ser guardada no en un módulo normal, sino en ThisWorkbook. Pruebe lo siguiente:

El siguiente procedimiento permite abrir automáticamente el libro Balance.xls al abrir el libro Informe.xls. El procedimiento ha de estar en ThisWorkbook del libro Informe.xls.

Sub Workbook_Open()
    'Apertura de libro Balance

    Workbooks.Open Filename:="C:/Temp/Balance.xls"

    'Activación del libro Informe

    Windows("Informe.xls").Activate

End Sub

Saludo al arrancar

Crear un libro Personal.xls como se ha indicado anteriormente con la macro de autoarranque siguiente:

Sub Auto_open()
   MsgBox ("Que tengas un buen día")
End Sub

Esta macro creada en el libro Personal.xls hace que al iniciar Excel nos salga un mensaje saludándonos.

Nombrar  Hoja desde una celda

Esta macro permite asignar el nombre que pongamos en la celda A1 como nombre de la Hoja actual.

Sub NombreHoja()
   ActiveSheet.Name = Range("A1").Value
End Sub

1. Pase al editor de visual basic y active el módulo a exportar.

2. Seleccione Archivo/ Exportar archivo. Aparece un cuadro de diálogo.

3. En cuadro de edición Nombre de Archivo, teclee el nombre para el archivo donde se guardará el

módulo, por ejemplo "General.Bas", observe que .BAS es la extensión de estos archivos.

4. Pulse sobre el botón Guardar.

Importar un módulo

1. Active el editor Visual Basic.

2.  Seleccione  Archivo/ Importar Archivo. Aparece un cuadro de diálogo.

3. Seleccione en la lista Buscar en: la carpeta donde tiene ubicado el archivo a importar.

4. Una vez localizada la carpeta, seleccione el archivo a importar (General.Bas en el ejemplo) y pulse

sobre Abrir.

Puede anteponer la cláusula private a todos los procedimientos y funciones que sean llamados sólo desde

el mismo módulo, es una forma de ahorrar memoria y hacer que el programa corra un poco más rápido. Si

necesita llamar un procedimiento o función desde otro módulo, nunca debe precederlo por la cláusula

private, recuerde que esta cláusula restringe el ámbito de utilización de un procedimiento a su propio

módulo.

Cuando ejecuta un programa paso a paso, si sitúa el puntero de ratón sobre una variable, se muestra el valor de la misma.

A veces resulta interesante cambiar el valor de alguna variable cuando se está ejecutando el programa,

para ver que ocurre si coge determinados valores, para terminar un bucle, etc.

Para ello agregue a la ventana de inspección la variable que desee cambiar, cambie su valor sobre la propia ventana de inspección y continúe la ejecución del programa.

Puedes inspeccionar variables desde esta ventana anteponiendo la palabra print antes de la variable, o el signo ?.

Esta expresión permite efectuar la depuración del programa de forma más cómoda. Se introduce en un programa antecediendo a una variable que se desea comprobar en la ventana Inmediato. De esta forma conseguiremos ver todos los valores que toma la variable cada vez que el programa pasa por este comando. Se ven en la Ventana Inmediato [Ctr+G].

.

Ejemplo:

Sub Dos_a_la_diez()
   Dim i As Integer
   Dim t As Integer
   t = 1
   For i = 1 To 10
      t = t * 2
      Debug.Print "Dos a la " & i & " = " & t
   Next
   MsgBox t
End Sub

Después de efectuada la depuración se quita la línea que contiene el comando Debug.Print.

Con esta propiedad deberemos definir los elementos que debe mostrar la lista, debe especificarse el rango

que contiene los elementos a mostrar, el rango debe ser una columna (o dos , o tres, etc.).

Propiedad LinKedCell del ComboBox

En esta propiedad debe especificar en que celda debe copiarse el elemento seleccionado de la lista.

Cuidado con esta propiedad, tenga en cuenta que los elementos de la lista son tratados como datos de

tipo String aunque contenga números o fechas, por lo que en estos casos, a veces será necesario aplicar

funciones de conversión de datos antes que el dato se copie en la hoja. Por ejemplo, si alguna vez

construye una lista con números verá que el dato seleccionado se alinea a la derecha, si son fechas, no se

muestra con el formato correspondiente.

Mediante esta propiedad podremos saber que elemento de la lista es el seleccionado por su número de

orden. Es decir, si está seleccionado el primero, ListIndex valdrá 0, si está seleccionado el segundo valdrá

1, etc. Si no hay ningún elemento seleccionado valdrá -1. Tenga en cuenta que esta propiedad sólo está

disponible en tiempo de ejecución, es decir la podremos leer mientras esté funcionando el programa, no

se puede establecer en modo diseño, observe que no aparece en la ventana propiedades del cuadro

combinado.

Al insertar módulos en el Editor de Visual Basic, se utilizan los nombres: Módulo 1, Módulo 2, etc. Podemos cambiar el nombre el un módulo en sus propiedades (F4). La única propiedad de un Módulo es 'name'.

Vamos a crear una función que proporcione el nombre de usuario. Primero utilizando la Grabadora de Macros. Haga lo siguiente. Active la Grabadora y luego seleccione Herramientas, Opciones, pestaña General. Realice un cambio en el nombre de usuario. Cerrar la ventana de Opciones pulsando en Aceptar. Y detener la Grabadora. La macro obtenida será la siguiente:

Sub Macro1()
'
' Macro1 Macro
' Macro grabada el 06/08/2004 por Adolfo
'

'
    With Application
        .UserName = "Adolfo Aparicio"
        .StandardFont = "Arial"
        .StandardFontSize = "10"
        .DefaultFilePath = "C:\Documents and Settings\Adolfo\Mis documentos"
        .EnableSound = False
        .RollZoom = False
    End With
End Sub

Ahora vamos a crear la función =Usuario siguiendo el procedimiento inverso.

Funcition Usuario()

    Usuario = Application.UserName

End Function

Puede probarla. La encontrará en la categoría de Funciones Definidas por el usuario.

- Módulos de clase. Extensión .cls.

- Formularios. Extensión .frm.

- Módulos estandar. Extensión .bas.

Se usan eventualmente para salir de un procedimiento o función.

Sub SalirAplic()
    If MsgBox("¿Quiere salir de la aplicación", vbQuestion + vbYesNo, "Ultima pregunta") = vbYes Then
        Application.Quit
    End If
End Sub

[Call] NombreProc [(lista de argumentos)]

La palabra clave Call es opcional para llamar a un procedimiento, salvo que existan argumentos, en cuyo caso es obligatoria. Los argumentos deben ir entre paréntesis y pueden ser valores o variables.

Sub Suma(a As Byte, b As Byte)
    MsgBox "La suma es " & a + b
End Sub

Sub sumar()
    Call Suma(3, 4)
End Sub

Para llamar a un procedimiento de otro módulo

NombreDelMódulo.NombreDelProcedimiento

Ejemplo: ThisWorkbook.SalirAplic

Para llamar a un procedimiento de otro libro

Application.Run "NombreDelLibro!NombreDelMódulo.NombreDelProcedimiento"

Ejemplo: Application.Run "Informe.xls!ThisWorkbook.SalirAplic.xls"

Al ejecutar este comando, el libro Informe.xls debe estar abierto.

Alt + F11
Herramientas => Propiedades de VBAProject => Protección => Bloquear proyecto para visualización => Contraseña

Podemos hacer que una función creada por el usuario este disponible en todos los Libros. Para ello, hemos de convertir el Libro que contiene la función en un Complemento.

Los Complementos disponibles se ven haciendo: Herramientas => Complementos.

Excel carga los complementos guardados en el directorio donde tengáis instalado MsOffice y luego en una carpeta que varía con la versión que tengas instalada. Para XP puede ser:

C:\Documents and Settings\Adolfo\Datos de programa\Microsoft\AddIns\

Para convertir el libro que contiene nuestra Función en un Complemento, bastara ir al Menú Archivo/Guardar como y elegir el formato Complemento de Microsoft Excel (*.xla). Si lo guardáis en el directorio que tiene tu versión asignado estará siempre disponible.
Un aspecto a tener en cuenta es que los Complementos no son editables, es decir no podrás modificar o añadir mas Funciones. Por eso es recomendable guardar primero el Libro en formato *.xls y luego como *.xla.

Sub Final()
    While ActiveCell.Value <> ""
        ActiveCell.Offset(1, 0).Select
    Wend
End Sub

Abrir un libro existente:

Sub AbrirLibro()

    Workbooks.Open ("C:\Mis documentos\Ejemplo.xls")

End Sub

Activar un libro ya abierto:

Sub ACtivarLibro()

    Workbooks("Ejemplo.xls").Activate

End Sub

Crear un libro nuevo:

Sub NuevoLibro()

    Workbooks.Add

End Sub

Borrar un registro de una tabla que contenga un dato concreto

Sub BorrarFilas()
    While ActiveCell.Value <> ""
        If ActiveCell.Value <> "Cadiz" Then
            ActiveCell.Offset(1, 0).Range("A1").Select
        Else
            Selection.EntireRow.Delete
        End If
    Wend
End Sub
 

Creación de una Fórmula personalizada

Cálculo del término amortizativo de un préstamo tipo francés (de pagos constantes). Similar a la fórmula =PAGO pero en este caso usando tipo nominal y con fraccionamiento.

Function Termino(Principal@, Años As Byte, tipo_nominal!, fraccionamiento As Byte)
    Dim im! 'Tipo efectivo del subperiodo
    im = tipo_nominal / fraccionamiento / 100
    Termino = Principal * im / (1 - (1 + im) ^ (-Años * fraccionamiento))
End Function

Pongamos un ejemplo para un principal de 100.000 €, 10 años, tipo nominal anual del 6%, con fraccionamiento mensual. En este caso, al ser el fraccionamiento mensual el término obtenido es la mensualidad.

Sub ejemplo()

End Sub

Macro que cierra Excel

Se puede asociar a un botón o a un icono.

Sub Auto_Close()

    Application.Quit
End Sub

Macro que elimina las barras de desplazamiento

Sub Desbarra()

    With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
End Sub

Macro que protege el libro y la hoja

Sub Blindaje()

    ActiveWorkbook.Protect Password:="1234"
    Sheets("Hoja1").Protect Password:="1234"
End Sub

Macro que borra los ceros de un rango

Sub BorrarCeros()
    For Each Celda In Range("C11:G24")
        If Celda.Value = 0 Then Celda.ClearContents
    Next
End Sub

Si sólo se quieren ocultar los ceros se ha de poner formato de celda personalizado de #.###

Macro que reemplaza una palabra por otra

Sub RemplazarPalabra()
    Dim Palabra As String
    Dim Hoja As Long
    Palabra = Trim(InputBox("Introduzca la palabra a buscar: "))
    For Hoja = 1 To Sheets.Count
        Sheets(Hoja).Activate
        Cells.Replace What:=Palabra, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Next Hoja
End Sub

En este caso reemplazamos por "", lo que supone borrar la palabra buscada.

Calculo del NIF

Esta función calcula el NIF (Número de Identificación Fiscal) utilizado en España.

Function nif(dni As Long) As String
    nif = Mid("TRWAGMYFPDXBNJZSQVHLCKE", (dni Mod 23) + 1, 1)
End Function

Función que calcula la fila que ocupa cierto valor en una tabla

Está pensado para que la matriz se ponga comenzando en la celda A1, sin encabezado y sin que se repitan los elementos.

Function DimeFila(Rango As Range, Valor_a_buscar) As Integer
    For Each c In Rango
        If c.Value = Valor_a_buscar Then
            DimeFila = c.Row
        End If
    Next
End Function

Función que muestra la fórmula de una celda

Devuelve la fórmula que contiene una celda en lenguaje local

Function  DisplayCellFormula(InputCell As Range) As String
    DisplayCellFormula = InputCell.FormulaLocal
End Function

Si se quita la palabra 'Local' devuelve la fórmula en inglés.

Determinación de si un número es primo o no es primo

Sub primo()
    Dim primo As Boolean
    Dim n As Long
    Dim d As Long
    n = Val(InputBox("Introduce un número natural", "Entrada de Datos"))
    d = 2
    primo = True
    Do While primo And d < n
        If n Mod d = 0 Then
            primo = False
        End If
        d = d + 1
    Loop
    If primo Then
        MsgBox (n & " es primo")
    Else
        MsgBox (n & " no es primo")
    End If
End Sub

Factorial

Function facto(n)
    Dim i As Integer
    facto = 1
    i = 0
    Do
        i = i + 1
        facto = facto * i
    Loop While i <> n
End Function

Inicializar una matriz

Se inicializa con ERASE
'En este caso se ponen todos los valores a Empty
Sub test()
    Dim c(1 To 20, 1 To 10) As Variant
    For i = 1 To 20
        For j = 1 To 10
            c(i, j) = RND
        Next j
    Next i
    MsgBox c(1,1)
    Erase c
    MsgBox c(1,1)
End Sub

'En este caso se pone la variable c a su estado inicial
'Borrandose los valores, las dimensiones y recuperandose
'la memoria usada.
Sub test1()
    Dim c As Variant
    c = ActiveSheet.Range("A1:J20").Value
    MsgBox c(1, 1)
    Erase c
    'Esto dara error ya que no hay ninguna matriz
    MsgBox c(1, 1)
End Sub

Evitar los movimientos de pantalla mientras se ejecuta una macro

Para evitar que durante la ejecución de una macro se vean todos los movimientos y cambios del cursor, para evitar las "chirivitas" que se ven al ejecutar una macro:

Al principio de la macro escribe

    Application.ScreenUpdating = False

y al final

    Application.ScreenUpdating = True

Evitar que al ejecutar una macro nos haga preguntas

Al principio de la macro escribe

    Application.DisplayAlerts= False

y al final

    Application.DisplayAlerts=True

While...Wend

Esta estructura permite ejecutar las instrucciones contenidas mientras la condición sea verdadera.

La siguiente macro posiciona el cursor en la última celda llena de la columna A de la Hoja1.

Otra variante:

Sub final2()
    While ActiveCell.Value <> ""
        ActiveCell.Offset(1, 0).Select
    Wend
    ActiveCell.Offset(-1, 0).Select
End Sub

La segunda macro requiere posicionar inicialmente el cursor en la primera celda llena.

Manejo de Rangos

Para señalar una tabla se hace con CurrentRegion. Un ejemplo:

Dim R As Range
Dim filas As Long
Set R = Range("A1").CurrentRegion
filas = R.Rows.Count

Ocultar Hojas

Podemos ocultar o mostrar Hojas de un libro. En el ejemplo siguiente, la Hoja2 esta inicialmente oculta, y para poder trabajar con ella la macro la mustra, y al finalizar la vuelve a ocultar.

Sub oculta()
    Sheets("Hoja2").Visible = True
    ActiveWorkbook.Sheets("Hoja2").Activate
    '... ...
    Sheets("Hoja2").Visible = False
End Sub

Determinar la fila hasta la que llega un rango

Podemos determinar facilmente la fila hasta la que llega un rango. Supongamos el rango B5:B100, del que sabemos en que fila acaba (la 100), pero no sabemos en que fila comienza. Ejecutando la macro la variable n tomará el valor 5, que es la fila de comienzo del rango.

Dim n As Long
Range("B100").End(xlUp).Select
n = Selection.Row

Retardar el tiempo de ejecución de una Macro

Utilización de un Array

Sub ColumnaArray()
Dim i As Byte
Dim uso
uso = Array("AS", "AT", "BC", "BM", "BV", "BB", "BO", "DI", "EN", "FB")
Worksheets("Hoja1").Activate
Range("B4").Select
For i = 1 To 10
    ActiveCell.Value = i
    ActiveCell.Offset(0, 1).Value = uso(i - 1)
    ActiveCell.Offset(1, 0).Activate
Next i
End Sub

Dejar una fórmula en una celda

Lo mejor de las Macros es que pueden interactuar con la hoja de cálculo. Todas las funciones de Excel estan disponibles para ser utilizadas en las macros. Por ejemplo, podemos hacer la media del valor de dos celdas y dejar el cálculo en otra celda.

Range("I3") =Application.WorksheetFunction.Average(Range("H3"), Range("J3"))

Pero si lo que queremos es dejar no el valor del cálculo sino la propia fórmula se debería hacer de esta forma:

Range("I3").Formula ="=average(H3,J3)"

Si queremos introducir la fórmula de la TIR para toda una columna, podemos escribir el siguiente código:

Range("I7").Formula ="=IRR(C:C)"

Si lo que deseamos es introducir en una celda, no la fórmula, sino el cálculo de la TIR aplicada a toda una columna, el código será el siguiente:

Range("I7") = Application.WorksheetFunction.IRR(Columns("C:C").EntireColumn)

Y si de paso queremos poner formato lo hacemos así:

Range("H3:J3").NumberFormat ="##.#0%"