NOVEDADES

Ahora también:  
Archivos  para Descargar
(a la derecha de cada sección) con ejemplos de las instrucciones allí presentadas.

¿Tu tema es la electricidad?

Descarga una planilla con Cálculos para Instalación Eléctrica (sin macros)
(click aquí)

Repaso, Instrucciones, Macros       

Al crear esta página, he intentado seguir los pasos efectuados al iniciarme en este tema de Macros, tratando de que sea una guía no solo para el que se inicia sino también para aquellos que ya manejan el tema, pero no recuerdan tal o cual instrucción, o buscan ampliar sus conocimientos con otros ejemplos.

Por eso encontrarán los temas separados en categorías, desde un repaso hasta macros más avanzadas, con varios ejemplos en cada una.

Como casi siempre hay más de una forma de programar un evento, podrán observar que a veces escribo una línea O la otra. Quiere decir que puedo utilizar cualquiera de las 2. (¡Nunca las 2 juntas!). 

Los temas están divididos en 3 grupos:

1. Conceptos básicos- Definiciones: repaso de conceptos claves para quien recién se inicia. Qué es una macro, cómo grabarla, dónde ubicarla, etc.

2. Instrucciones básicas: aquí encontrarán cómo abrir y cerrar un libro, cómo hacer referencia a otra hoja, cómo seleccionar celdas, etc.

• Trabajando con Libros

• Trabajando con Hojas

• Trabajando con Celdas                 
• Trabajando con una Colección   

• Trabajando con Objetos (UserForm, Listbox, Textbox, etc)           

3. Macros: aquí encontrarán algunas de las rutinas solicitadas con mayor frecuencia en los foros de consultas. Algunas son propias y otras no. A sus autores va nuestro agradecimiento.

        1- Conceptos básicos-Definiciones:                          

MACRO:  Una macro es un conjunto de instrucciones escritas en lenguaje Visual Basic, que nos permite automatizar ciertas tareas, que la aplicación no lo contempla. Por ejemplo, si queremos que al abrir un libro automáticamente inserte 2 hojas nuevas, recurriremos a una macro.

Las macros se pueden "grabar" o "escribir" en el Editor de Visual Basic.

1. Para "grabar" una tarea rutinaria, vamos al menú Herramientas/Macro/Grabar nueva macro. Seleccionar el libro activo y asignarle un nombre. Con el botón Opciones, se podrá asignar un atajo de teclado, para poder ejecutarla con una combinación de teclas.

Acción: En la Hoja1, introducimos un título en la celda A1, le damos formato Negrita, Cursiva, Subrayado, Color de fuente y la combinamos con las 4 celdas siguientes. Detengamos  la grabación. 

Vamos a la Hoja2, al menú Herramientas/Macro/Macros. Seleccionamos la que acabamos de grabar y hacemos clic en el botón Ejecutar. Si le hemos asignado un atajo de teclado, solamente presionamos las teclas necesarias.

Listo: ya tenemos el título con todo el formato sin necesidad de repetir todos los pasos.

2. Para "escribir" una macro, presionamos las teclas Alt+F11 para abrir el Editor de Visual Basic, o vamos por el menú Herramientas/Macro/Editor de Visual Basic.

¿Dónde colocar una macro? Dependerá de cual será su uso y para eso seleccionaremos alguno de los objetos que se encuentran en el margen izquierdo: Hoja1, ThisWorkBook, Módulos.

1. Si tiene que ver con el libro abierto o activo, como por ejemplo, si queremos que se ejecute al abrir el libro, haremos doble clic sobre ThisWorkbook. Seleccionar el objeto (en General) que será WorkBook y algún procedimiento (en Declaraciones) que puede ser: Open, Close, WindowActivate, etc. Las instrucciones las escribimos o copiamos entre Private Sub y End Sub.
2. Si se ejecutará cuando tengamos una hoja abierta, por ejemplo que la limpie o borre su contenido, haremos doble clic sobre Hoja1 o el número que sea. Seleccionamos el objeto que será WorkSheet y algún procedimiento como Activate, Change, SelectionChange, etc.
3. Si queremos que la macro se ejecute en cualquier hoja, por ejemplo: ordenar columnas, copiar rangos, dar formatos,  limpiar un rango, etc. la colocaremos en un módulo. Para eso ir al menú Insertar/Módulo del Editor. Luego doble clic sobre él. Aquí no seleccionamos ni objeto ni procedimiento sino que escribimos o copiamos toda la rutina, como el siguiente ejemplo:

Sub Borraceldas ( )

Range("A5:C20").Select

Selection.ClearContents

End Sub

En este caso se la llamará desde el menú Herramientas o se optará por asignar un atajo de teclado, para ser ejecutada desde cualquier hoja.

WORKBOOK: libro de trabajo. La expresión ActiveWorkbook hace mención al libro activo, en caso de haber más de uno abierto

SHEET: hoja de cálculo. Se utilizan también las siguientes expresiones: Worksheet, ActiveSheet (en este caso se hace referencia a la hoja activa)

RANGE: hace referencia al rango o conjunto de celdas a las que se aplicará una acción. Existen distintas maneras de mencionar un rango de celdas: Range("A4:C8"), Range(variable), Range("A:A;D:D"),  Selection

CELL: hace referencia a la celda. Se pueden utilizar expresiones como Cells(2,5), Cells(fila,col), ActiveCell, Selection

Hecho el repaso, a continuación verán las principales instrucciones, que ya sabrán dónde colocarlas. Si alguna rutina les presenta dificultad, pueden enviarme un mail con los comentarios al respecto.

 2- Instrucciones básicas:     

Trabajando con Libros:                                           Descargar archivo                          
                                                                                           TrabajandoConLibros.xls (28 Kb)

1-Abrir un segundo libro:

Application.Workbooks.Open "C:\Mis docu\Libro1.xls"    o   Workbooks.Open "C:\....."

2-Activar un segundo libro:

Workbooks("Libro2.xls").Worksheets("Hoja3").Activate     o   Workbooks(2).Sheets(3).Activate

3-Cerrar un libro (sin guardar):

Workbooks("Libro1.xls").Close  False   o   ActiveWorkbook.Close  False

4-Cerrar un libro (guardando los cambios):

ActiveWorkbook.Save

ActiveWorkbook.Close

5-Guardar un libro con otro nombre:

ActiveWorkbook.SaveAs Filename:="C:\Mis doc\Libro1.xls", FileFormat:=xlNormal, Password:="clave", ReadOnlyRecommended:=False

Estas son algunas de las opciones. Si se omiten, escribir la coma.

Ejemplo:  
ActiveWorkbook.SaveAs Filename:="C:\Mis doc\Libro.xls",,, ReadOnlyRecommended:=False

6-Guardar un libro cuyo nombre será el valor de una variable:

ActiveWorkbook.SaveAs Filename:=Range("A2").Value

7-Para no mostrar aviso al salir, o al eliminar una hoja, o cualquier aviso que queremos obviar:

Application.DisplayAlerts= False               'volverla a True al finalizar la macro

8-Deshabilitar la opción de actualizar vínculos al abrir un libro:

Application.DisplayAlerts= False               'volverla a True al finalizar la macro

WorkBooks.Open Filename:= "C:\Mis docu\pruebas.xls", UpdateLinks:= 0

9-Para no mostrar la ejecución de la macro:

Application.ScreenUpdating = False           'volverla a True al finalizar la macro

10-Ejecutar una macro al abrir un libro:

Crear una rutina pública o insertar una rutina en un módulo:

Ejemplo:     Sub Nuevamacro ()

                     'instrucciones

                End Sub

 

Y en el evento Open del objeto ThisWorkbook:    Private Sub Workbook_Open ()

                                                                   Nuevamacro

                                                                  End Sub

 

 

Trabajando con Hojas:                                       Descargar archivo                               
                                                                    TrabajandoConHojas.xls (33,5 Kb)                       

1- Activar o seleccionar otras hojas:

Sheets("Hoja2").Activate    o   Sheets(2).Select

2-Seleccionar la hoja anterior o siguiente:

ActiveSheet.Previous.Select              'hoja anterior a la activa

ActiveSheet.Next.Select                    'hoja posterior a la activa

3-Datos de la hoja:

ActiveSheet.Name                 'nombre de la hoja

ActiveSheet.Index                 'número de hoja

4-Copiar datos de una hoja a otra:

Selection.Copy                            'previamente se habrá seleccionado algo

ActiveSheet.Paste Destination:=ActiveSheet.Next.Cells(1,4)

Application.CutCopyMode= False

5- Ocultar filas o columnas:

ActiveCell.EntireRow.Hidden=True      o       ActiveCell.EntireColumn.Hidden= True

6-Proteger o desproteger una hoja:

ActiveSheet.Protect "contraseña"                 'proteger con contraseña

ActiveSheet.Unprotect  "contraseña"            'quitar la protección

7-Insertar filas o columnas:

Workbooks("Libro1").Sheets("Hoja2").Column(i).Select

Selection.EntireColumn.Insert

           'reemplazar Column por Row en caso de filas.

8-Eliminar filas o columnas:

ActiveSheet.Row(n).Select

Selection.EntireRow.Delete

9-Insertar una imagen en una hoja:

ActiveSheet.Pictures.Insert(ruta).Select    'la ruta entre comillas:  "C:\Mis docu\Foto1.jpg"

10-Imprimir la hoja seleccionada:

ActiveWindow.SelectedSheet.PrintOut Copies:=1, Collate:=True

 

 

 

Trabajando con celdas:                                  Descargar archivo                               
                                                                                TrabajandoConCeldas.xls  (33 Kb)       

1- Formas de seleccionar una celda o un rango de celdas:

Range("B7").Select                                      'selecciona la celda B7

Range("B:B").Select                                    'selecciona toda la columna B

Range("A4:A10, D10, B5:B20").Select         'selecciona rangos discontinuos

Range("A"&variable).Select             'selecciona la celda cuya fila será el valor de la variable

Si Rango=("B2"&":E"&variable)               

entonces:  Range(rango).Select  'selecciona el rango B2:E hasta la fila indicada en la variable 

Range("A:A, D:F").Select                  'selecciona las columnas A, D, E y  F

Range("2:2, 4:7").Select                    'selecciona las filas 2 y desde 4 hasta la 7.

2- Seleccionar celdas a cierta distancia de la celda activa:

Sheets(1).Range("A1").Offset(2,3).Select    'selecciona la celda D3

ActiveCell.Offset(-10,1).Select       'selecciona la celda que se encuentra 10 filas por encima
                                                     'y 1 columna a la derecha de la celda activa.

3-  Formato de celdas:

Range("B2:D10").Select

With Selection

    .Font.Bold=True                                           'formato negrita

    .Font.Italic=True                                           'formato cursiva

    .Font.Underline=xlUnderlineStyleSingle          'subrayado simple

    .Font.Color = RGB(255,0,0)                          'color de fuente (para estos valores será rojo)

    .HorizontalAlignment=xlCenter                       'alineación central (Right=derecha, Left=izquierda)

End With

4- Guardar la dirección de una celda en una variable:

lugar= ActiveCell.Address                                    'guarda la referencia absoluta

lugar= ActiveCell.Address(False, False)                'guarda la referencia relativa

5- Copiar un comentario en otra celda:

ActiveCell.Offset(0,1).Value = ActiveCell.Comment.Text   

'copia el comentario de la celda activa en la celda que se encuentra en la
                                                                               'columna siguiente.

6-Seleccionar celdas y borrarlas:

Range(rango). Select       o    Cells.Select

Selection.ClearContents

7- Ampliar un rango seleccionado

Selection.Resize(10,4).Select       ' (10 filas, 4 columnas)

                                                  ' si el rango seleccionado fue A1:B5 ahora será: A1:D10

8-  Combinar celdas selecccionadas:

Range("B1:E1").Select

Selection.Merge

9-  Seleccionar el rango donde se encuenra la celda activa:

Range("B2").CurrentRegion.Select

10- Seleccionar hasta la última celda no vacía:

Range("A2", Range("A2").End(xlDown)).Select        'seleciona desde A2 hacia abajo

Range("A2", Range("A2").End(xlToRight)).Select     'selecciona desde A2 hacia la derecha

Range("D2", Range("D2").End(xlToLeft)).Select      'selecciona desde D2 hacia la izquierda

Range("A20", Range("A20").End(xlUp)).Select         'selecciona desde A20 hacia arriba

En cambio, para seleccionar solo la última celda con datos será:

Range("A2").End(xlDown).Select

          

Trabajando con una colección:                                  Descargar archivo             
                                                                           TrabajandoConColecciones.xls (27,5 Kb)

Ejemplo1: introducir un nombre para cada hoja del libro activo:

Dim MiNombre as String

Dim hoja as Worksheet

For Each hoja in Worksheets

    MiNombre = InputBox("Ingrese nombre de hoja: ")  

next hoja

Ejemplo2: introducir valores para cada celda de un rango

Dim celdita as Range

For Each celdita in ActiveSheet.Range("A1:B10")

    celdita.Value = InputBox("Ingrese valor: ")

next celdita

Ejemplo3: introducir los mismos valores en celdas de todas las hojas

Dim hoja as Sheets

For Each hoja in Sheets

    hoja.Range("E3").Value = Date

    hoja.Range("F3").Value = Time

next hoja

 

Trabajando con objetos:                                         Descargar archivo                      
                                                                         TrabajandoConObjetos.xls  (50 Kb)            

(En cada punto encontrarán varios ejemplos. Ver otras rutinas en Macros)

1- Llamando a un Userform, desde un botón:

En una hoja de Excel, los botones que lanzan una acción, pueden ser colocados con la barra de Formularios o Cuadro de Controles.

1. Botón de formulario: se asigna una macro que previamente se escribió en un módulo en el Editor de Visual Basic. Ejemplo:

Sub mostrando ()

UserForm1.Show        'nombre del Userform que se desea mostrar en la hoja

End Sub

2. Botón del Cuadro de controles: una vez dibujado en la hoja, clic derecho, Ver código y escribir la rutina (ésta se habilita en la hoja donde aparecerá el control).  Ejemplo:

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

2- Asignando valores a los Cuadros de texto: 

Ejemplos de cómo registrar datos en los textbox:

TextBox1.Value = "CANCELADO"

TextBox2 = Sheets("Hoja3").Range("A5").Value

TextBox2 = Range("C1").Value + Range("C2").Value
 

3- Volcar datos de un UserForm a la hoja:

Cells(fila, col).Value = TextBox1.Value

Sheets("Hoja1").Cells(fila, col).Offset(1, 0).Value = TextBox2.Value

4- Cargar datos a un ListBox de un UserForm:

Private Sub UserForm_Activate()
Dim item As Variant
For Each item In Range("F1:F6")
ListBox1.AddItem item.Value
Next item
End Sub

                3-  Macros