Macros en Excel 2010-2013

7/24/2019 Macros en Excel 2010-2013

1/83

M CROS

EN EXCEL

1

Sub Macro1()

'Nombre de la macro

'Creado por:

'Comentario

'Acceso Directo:

Activecell.value=Mi primera macro

End Sub

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

2/83

QU SON LAS MACROS?Es un conjunto de instrucciones de cdigo(programado) que permiten realizar una tarea

determinada como as tambin expandir e incrementar

las prestaciones de Excel.

Las macros se escriben en lenguaje de programacin

VBA (Visual Basic for Applications).

Con las macros podrs ahorrar mucho

tiempo y eliminar / automatizar trabajo Excelmanual

Programando una macro Excel, tareas que

llevaban horas o das de trabajo manual se

Pueden realizar en segundos.

2Macros en Excel

7/24/2019 Macros en Excel 2010-2013

3/83

Barra de TtuloBarra de Acceso rpido

Cinta de Opciones

Barra de EstadoPestaas de Hojas

rea de trabajo

COMO EMPEZAMOS?

3

Partes de la Ventana principal de Excel

Herramienta de la

Ci

nta de Opciones

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

4/83

Es posible que no veamos esta ficha, porque se encuentra oculta en forma predeterminada. Para

habilitarla en Excel 2010 - 2013 deberemos seguir los siguientes pasos.

Paso 1:Ir a menArchivo.

Paso 2:Presionamos sobre el botn Opciones y se abre una ventana.Paso 3:Hacemos clic sobre Personalizar cinta de opciones.

Paso 4:Ubicamos la seccin denominada fichas principales, all veremos los nombres de cada ficha

de la cinta de opciones y junto a ellos, Marcamos la casilla que corresponde a la fichaDesarrollador.

Paso 5:Presionamos Aceptar.

1

4

3

25

CINTA DE OPCIN PROGRAMADOR

4Macros en Excel

7/24/2019 Macros en Excel 2010-2013

5/83

SEGURIDAD DE LAS MACROS

5

Para deshabilitar la Advertencia de seguridad seguimos los siguientes pasos.

1

4

2 3

5

6

Clic enSeguridad de

Macros

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

6/83

QUE DEBEMOS CONOCER?

6

Cuando hablamos de macros hablamos de Objetos, Propiedades y

Mtodos (OPM). Estos son los 3 conceptos generales ms importantes que

debes conocer a la hora de programar las macros.De momentos solo te daremos una sencilla definicin y luego iremos

profundizando ms en ellos.

Hay un ejemplo muy prctico para comprender lo que son los Objetos,

Propiedades y Mtodos (OPM). Supongamos que tenemos una canasta

con frutas.

Cuales seran los OPM de la misma?

Objetos: Los objetos de la canasta de frutas seran las mismas frutas

(manzanas, naranjas, peras).

Propiedades: Las propiedades seran las caractersticas de las frutas(color, olor, sabor, textura).

Mtodos:Finalmente los mtodos son las acciones que podramos ejercer

sobre las frutas (comprarlas, venderlas, comerlas, almacenarlas, limpiarlas,

quitarles la piel,).

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

7/83

OBJETOS, PROPIEDADES Y MTODOS

Objetos: prcticamente cada cosa que veas en Excel es un objeto. Ejemplos de

objetos son: un libro excel, una hoja, un rango, una celda, un men, un grfico, una

tabla dinmica, un cuadro de dilogo, las etiquetas de hojas, las columnas, las filas,etc. En fin, cada partecita de Excel es un objeto. Un objeto es algo que puedes ver e

identificar con un nombre.

Propiedades: las propiedades son las caractersticas de los objetos. Por ejemplo,

para el objeto "celda" algunas de sus propiedades seran: alto, ancho, color, bloqueada

o desbloqueada, vaca, con un nmero o con una frmula, etc. Por ejemplo para el

objeto "hoja" algunas de sus propiedades seran: visible u oculta, con o sin lneas dedivisin, con o sin barras de desplazamiento vertical y horizontal, etc.

Mtodos:un mtodo es una accin que podemos realizar sobre el objeto o una de

sus propiedades. Por ejemplo sobre el objeto "hoja" podemos: activar, mover, copiar o

borrar.

En definitiva, de eso se trata programar una macro: conocer el nombre del objeto,

conocer el nombre de alguna propiedad del mismo que quieras modificar y tambin

conocer el nombre de algn mtodo que desees aplicarle.

7Macros en Excel

7/24/2019 Macros en Excel 2010-2013

8/83

EJEMPLOS DE OPM

8

Macros = VBA = OPM

El lenguaje VBA es muy fcil de entender y siempre gira en torno a seleccionar los

objetos, cambiar sus propiedades y/o ejecutarles distintos mtodos.

Veamos un sencillo ejemplo de algunos objetos de Excel y su denominacin en ingls,

el idioma utilizado por las macros:

Ejemplos de Objetos VBA

(En espaol)

Cell (celda)Range (rango)

Worksheet (hoja)

Workbook (libro)

Ejemplos de Mtodos VBA

(En espaol)

Copy (copiar)

Protect (proteger)

Delete (borrar)

Ejemplos de Propiedades VBA

(En espaol)Value (valor)

Color (color)

Format (formato)

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

9/83

EDITOR DE MACROS - VENTANA DE VBA

9

Ventana de Inmediato

Ventana deProyectos o

Explorador deproyectos

Ventana de Cdigo

Ventana de

Propiedades

Barra de Men

Barra de Herramientas

Nuestra forma preferida de acceder al editor de macros es con las teclasALT + F11

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

10/83

10

EDITOR DEL VBAEXPLORADOR DE PROYECTOSNivel 1:ProyectosPor regla general, cada libro excel tiene asociado un proyecto.

Nivel 2:Carpetas1. Microsoft Excel Objetos

1.ThisWorkbook: Hace referencia al Libro. Si escribimos una macro aqu lamisma afectar a todo el libro, tanto apertura y trabajo en el mismo.

2.Hoja1(Hoja1):Hace referencia las hojas de Excel (habr 1 por cada hojade nuestro libro). Si escribimos una macro aqu la misma solo afectar a la hojaen cuestin.

2. Formularios1. UserForm1:Dentro de esta carpeta se disean los formularios interactivos

para el usuario llamado UserForm.

3. Mdulos1. Modulo1: Dentro de esta carpeta se escriben o graban las macros.Podemos insertar tantos mdulos como necesitemos. Estos mdulos operan de

forma genrica, sin distinguir entre hojas o libros.

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

11/83

EDITOR DEL VBAEXPLORADOR DE PROYECTOS

11

Haciendo doble clic izquierdo en cualquiera de estos

objetos vers que se habilita la Ventana de Cdigo de la

izquierda (una hoja en blanco grande). En la misma es

donde se escriben las macros.

Recuerda!Antes de escribir una macro debes evaluar donde hacerlo.

1.Si es una macro que solo debe afectar una hoja en particular escrbela

en los objetos deHoja, en el nombre de Hoja correspondiente.

2.Si la macro debe afectar a todo un libro en particular escrbela en el

objetoThisWorkbook. Estas suelen ser macros que se ejecutan al abrir,cerrar o guardar el libro.

3. Si la macro es de tipo genrica escrbela en unMdulo.

4. Si quieres hacer un formulario utiliza el objetoUserform.

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

12/83

EDITOR DEL VBAVENTANA DE PROPIEDADES

12

Cada objeto tiene sus propiedades. Por ejemplo, si hacemos doble clic en el objeto

Mdulo1 que vimos en la seccin anterior, podremos ver sus propiedades:

Algunos objetos tienen muchas propiedades (por ej. losUserforms) as que tenemos la opcin de ordenar los

mismos de forma alfabtica o por categora. El objeto

Mdulo1 solo tiene la propiedad (Name)Mdulo1. Si

quisiramos podramos reemplazar el nombre de Mdulo1

y asignar otro nombre a gusto.

Consejo!En la medida quevamos insertando muchas

macros

proyecto,

creando

en nuestro

conviene ir

nuevos mdulos

con nombres apropiados

para mantener ordenadas

nuestras macros.

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

13/83

EJEMPLO DE PROPIEDADES DE HOJA

Propiedades de Hoja1

Item Valor Significado(Name) Hoja1 Nombre de la Hoja1 del Excel

DisplayPageBreaks False Muestra el contorno de impresin de hojas

DisplayRightToleft False Muestras las columnas y filas desde el lado derecho.

EnableCalculation True Activa el calculo matemtico automtico en las celdas

EnableFormatConditionsCalculation True Activa el calculo lgico automtico en las celdas

EnableSelection 0 No habilita las restricciones

-1 Desactiva las celdas bloqueadas-1142 Desactiva todas las celdas

Name Hoja1 Nombre de la Hoja1 en VBA

Visible -1 Se muestra la Hoja de Excel

0 Se oculta temporalmente la Hoja de Excel

2 Se oculta permanentemente la Hoja.

13Macros en Excel

7/24/2019 Macros en Excel 2010-2013

14/83

EDITOR DEL VBAVENTANA DE CDIGO

14

Lo que interesa es ms bien el cdigo que escribamos aqu. Puedes escribir todas

las macros que quieras en esta ventana. Recuerda que el mismo editor asignar

automticamente los colores al cdigo y adems separar con una lnea continua

cada macro.

En la medida que empecemos a escribir muchas macros, existe una forma rpida de

movernos entre ellas. Desde la lista desplegable de la parte superior derecha

podremos ver la lista de macros escritas. Haciendo clic en cualquiera de ellas el

editor nos posicionar rpidamente en la misma.

14Macros en Excel

7/24/2019 Macros en Excel 2010-2013

15/83

EDITOR DEL VBAVENTANA DE INMEDIATO

15

15

La ventana inmediato sirve para escribir, ejecutar y probar un cdigo macro

rpidamente, sin tener que armar toda la macro con la estructura que comentamos

en secciones anteriores.

Por ejemplo, si quisiramos averiguar la cantidad de hojas que tiene un Libro

podramos escribir la siguiente macro en un mdulo y ejecutarla presionando desde

el teclado la tecla F5:Sub ContarHojas()

Dim N As Long

N=

thisworkbook.Sheets.CountMsgbox N

End Sub

Entonces, en la ventana inmediato podemos escribir:?thisworkbook.Sheets.Count y al presionar Enter en la fila siguiente nos mostrar

el nmero de hojas que posee el libro. El signo ? al comienzo significa que estamos

buscando conocer el resultado de algo. El resultado final se vera as, suponiendo queel libro tiene 3 hojas:

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

16/83

CREAR Y/O GRABAR UNA MACRO

16

A la hora de crear una nueva macro existen 3 mtodos.

Escribirla manualmente

Utilizar la grabadora de macros

Combinacin de los 2 mtodos anteriores (grabarla y luego retocarla

manualmente)

En cada situacin utilizars un mtodo distinto. Por ejemplo, las macros muysencillas las puedes escribir manualmente porque tienen pocas lneas y ya

las conoces.

En otros casos la podrs crear con la grabadora de macros y olvidarte del

cdigo VBA.

Sin embargo, en la medida que progreses con las macros vers que el

mtodo ms utilizado suele ser el nmero 3 mediante el cual primero

grabars y luego hars ajustes manuales en el cdigo para hacerlo mseficiente y que se ejecute ms rpido.

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

17/83

EJEMPLOS DE MACROS

17

Crea un nuevo libro Excel y gurdalo con el nombre Libro1.

2. Accede al Editor de visual basic con las teclas ALT+F11

3. Inserta un nuevo Mdulo desde el men Insertar > Mdulo

4. Dentro del mdulo escribe textualmente el siguiente cdigo:

SubMostrarTexto()

' Esta macro muestra un texto en una ventana emergente

Msgbox Bienvenido"

End SubEsta macro utiliza la instruccin Msgbox que sirve para abrir una ventana con la

informacin que le indiquemos.

Sub OcultarLineas()

' Esta macro oculta las lneas de divisin de las celdas

ActiveWindow.DisplayGridlines =False

End Sub

Esta macro utiliza el objeto ActiveWindow (ventana activa).

Luego le asigna la propiedad DisplayGridlines=False (mostrar lneas de

divisin=falso) Macros en Excel

7/24/2019 Macros en Excel 2010-2013

18/83

EJECUCIN DE MACROS

18

Desde el Cdigo:La forma ms rpida de ejecutar una macro es desde el mismo cdigo.Cuando terminas de escribir una macro puedes posicionarte en cualquiera de sus lneas de

cdigo y presionar la tecla F5. Recuerda que puedes acceder al cdigo de las macros

desde el editor de macros, con las teclasALT+F11.

Desde el Menu:Otra forma de ejecutar una macro es desde el men de macros, que lopuedes visualizar presionandoALT+F8Vers que se abre un cuadro como el siguiente:

Para ejecutar la macro:

1. Elige el Nombre de la macro de la lista

2. Clic en Ejecutar

Desde el Teclado:Otra forma muy prctica de ejecutar una macro es desde el teclado.

Esto tambin se conoce como teclas de mtodo abreviado. Para configurar las teclas conlas cuales ejecutar debes hacer los siguiente:

1. Ir al men de macros presionandoALT+F8

2. Elegir el nombre de la macro de la lista y luego hacer clic en el botnOpciones.

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

19/83

19

Donde diceTecla de mtodo abreviadopresiona la

tecla MAYUSC y luego una letra. En nuestro ejemplo

la macro se ejecutar con Ctrl+Maysc+QDonde dice

mtodo

presiona

Tecla de

abreviado

la tecla

MAYUSC y luego una

letra.

ejemploejecutar

En nuestro

la macro secon

Ctrl+Maysc+Q

Desde ObjetosHaz clic izquierdo en la

sufigura para desplegarmen de opciones.

Elige la opcin

macro

Asignar

19Macros en Excel

7/24/2019 Macros en Excel 2010-2013

20/83

MACROS CON EVENTOS EN LIBROS

20

Los eventos de libros permiten disparar automticamente una macro cuando suceden determinadas

acciones a nivel del libro excel en cuestin, por ejemplo al abrirlo, guardarlo, cerrarlo, imprimirlo,

etc.

Instrucciones para insertar una macro de evento a nivel libro:1. Las macros de eventos se deben escribir en la carpetaThisWorkbookdel editor de macros.

2. Luego en la primera lista desplegable de la ventana de macros elegimos la opcinWorkbook.

3.Finalmente, en la lista desplegable de la derecha veremos todos los eventos de libro que

podemos utilizar para disparar nuestra macro.4.Si haces clic en la opcin Activate, vers

que en la ventana de macros se escribe

automticamente el siguiente cdigo:

Private SubWorkbook_Activate()

End Sub

Luego podrs escribir tu cdigo macro entre esas lneas. Eneste caso, la macro que escribas se ejecutar cada vez que

actives el libro excel en cuestin. Por ejemplo, puedes hacer

una macro que muestre un mensaje de saludo cada vez

que abres el libro:

Entonces, cada vez que abras el libro, la macro se ejecutar automticamente y vers el saludo:

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

21/83

En el caso de las macros de evento, siempre aparece la palabra Private. Eso significa

que esa macro no la podrs ver en la lista del men de macros. Esto es as porque la

macro no se puede ejecutar manualmente, si no que se ejecuta automticamente cuando

ocurre el evento, en este caso cuando se activa el libro excel en cuestin.

Private SubWorkbook_BeforeSave(ByValSaveAsUIAs Boolean, CancelAs Boolean)

' El cdigo que escribas aqu se ejecutar automticamente al guardar el libro

End Sub

Private SubWorkbook_Open()

'El cdigo que escribas aqu se ejecutar automticamente al abrir el libro

End Sub

Existen ms de 20 macros de evento a nivel libro. Puedes

instrucciones dadas al comienzo. Las ms importantes son:

insertarlas siguiendo las

Private SubWorkbook_BeforeClose(CancelAs Boolean)

' El cdigo que escribas aqu se ejecutar automticamente al cerrar el libroEnd Sub

Private SubWorkbook_BeforePrint(CancelAs Boolean)

' El cdigo que escribas aqu se ejecutar automticamente al imprimir el libro

End Sub

21Macros en Excel

7/24/2019 Macros en Excel 2010-2013

22/83

MACROS CON EVENTOS EN HOJAS

22

2

Los eventos de hojas permiten disparar automticamente una macro cuando suceden determinadas

acciones a nivel de hojas excel, por ejemplo al activarla, desactivarla, calcularla, etc.

Instrucciones para insertar una macro de evento a nivel hoja:

1.En nuestro caso tenemos Hoja1(Hoja1). Al hacer doble clic en dicha carpeta, la macro queescribamos solo afectar a dicha hoja.2. Luego en la primera lista desplegable de la ventana de macros elegimos la opcinWorksheet.

3.Finalmente, en la lista desplegable de la derecha veremos todos los eventos de hoja que

podemos utilizar para disparar nuestra macro automticamente.

4.Si haces clic en la opcin Activate,

vers que en la ventana de macros se

escribe el siguiente cdigo:

Private SubWorksheet_Activate()

End Sub

Luego podrs escribir tu cdigo macro entre esas lneas. En este caso, la macro que escribas se

ejecutar cada vez que actives la hoja excel en cuestin. Por ejemplo, puedes hacer una macro que

muestre un mensaje de saludo cada vez que activas la hoja:

Entonces, cada vez que actives la hoja, la macro se ejecutar

automticamente y vers el saludo:

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

23/83

En el caso de las macros de evento, siempre aparece la palabra Private. Eso significa que esa

macro no la podrs ver en la lista del men de macros. Esto es as porque la macro no se puede

ejecutar manualmente, si no que se ejecuta automticamente cuando ocurre el evento, en este casocuando se activa la hoja excel en cuestin.

Existen unas 9 macros de evento a nivel hoja. Puedes insertarlas siguiendo las instrucciones dadas

al comienzo. Las ms importantes son:


'El cdigo que escribas aqu se ejecutar automticamente al activar la hoja

End Sub

Private SubWorksheet_Calculate()' El cdigo que escribas aqu se ejecutar automticamente al calcular la hoja

End Sub

Private SubWorksheet_Deactivate()

'El cdigo que escribas aqu se ejecutar automticamente al desactivar la hoja

End Sub

Private SubWorksheet_SelectionChange(ByValTargetAsRange)

' El cdigo que escribas aqu se ejecutar automticamente al moverte entre las celdas de la hoja (cada vez

que seleccionas y te posicionas en una nueva celda)

End Sub

Private SubWorksheet_Change(ByValTargetAsRange)

' El cdigo que escribas aqu se ejecutar automticamente al producirse un cambio en la hoja (por ejemplo al

introducir un dato en una celda, seleccionar una opcin de una lista desplegable, etc.)

End Sub

23Macros en Excel

7/24/2019 Macros en Excel 2010-2013

24/83

FORMULARIOS

24

Un Formulario (o su denominacin en ingls Userform) se utiliza para crear un Cuadro de

Dilogo donde el usuario puede introducir informacin, o realizar otras operaciones. Al

ejecutar muchas de las opciones del men de Excel se abren formularios. Por ejemplo,

desde el men Herramientas > Opciones se abre un formulario como el siguiente, desde

donde se pueden activar o desactivar distintas opciones de Excel.

Los Userforms se utilizan mucho para crear

aplicaciones Excel que luzcan de forma

profesional y permitan al usuario introducir

datos o elegir opciones de una forma

guiada y ms intuitiva.

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

25/83

1. Abrir el editor de macros (por ejemplo, conCTRL+F11).

2. Insertar un objeto Userform. Esto podemos hacerlo fcilmente:

1.Seleccionamos el Libro Excel donde trabajaremos, en nuestro casoVBAProject (Libro2).

2. Hacemos clic derecho en el mismo y elegimos la opcinInsertar > Userform3.Como se ve en la fotografa, veremos que aparece un objetoUserform1que cuelga de la

carpetaFormularios

La idea es que mediante estos controles podremos tanto capturar como enviar informacin o datos

desde o hacia las celdas de Excel respectivamente. Tambin podemos asociar macros a los

controles que agreguemos al Userform (todo esto lo veremos en secciones siguientes).

Si te posicionas sobre los Controles del Cuadro de herramientas, vers que aparece su nombre.

Simplemente hacemos clic izquierdo en el control deseado y luego clic izquierdo en el Userform, en

el sitio donde queramos agregar el control. En nuestro caso hemos agregado algunos controles de

forma desordenada dentro del Userform.

25Macros en Excel

7/24/2019 Macros en Excel 2010-2013

26/83

CONTROLES DEL FORMULARIO

26

26

Ahora explicaremos brevemente la utilidad de cada

control. Los controles se explican en el mismo orden que

aparecen en el Cuadro de Herramientas y en la figura

anterior (de izquierda a derecha y de arriba hacia

abajo).

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

27/83

27Macros en Excel

7/24/2019 Macros en Excel 2010-2013

28/83

Haciendo doble clic en cada control se abrir una venta donde podremos escribir y asociarle una

macro. En la seccin siguiente veremos un ejemplo simple sobre como crear un Userform paso a paso.

28Macros en Excel

7/24/2019 Macros en Excel 2010-2013

29/83

EJEMPLO DE UN FORMULARIO

29

ObjetivoCrear un Userform para que un usuario complete unos datos personales (Nombre,

Edad y Fecha de Nacimiento). Luego que el usuario complete sus datos, al apretar unbotn, los mismos se volcarn en una tabla de Excel.

Los controles que utilizaremos son:

Textbox: para que el usuario ingrese los datos

Label: para darle el nombre e los Textbox

CommandButton: para proceder con el ingreso de datos o cancelar

1. Creamos la tabla en la hoja Excel

2. Creamos un Userform (UF)

Los UF se crean desde el editor de visual basic. Abrimos el editor conALT+F11

Desde el men superior elegimosInsertar > Userform

DatosUF.Show

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

30/83

3. Nombramos el Userform

4. Agregamos un Cuadro de Texto

5. Agregamos un Texto

30Macros en Excel

7/24/2019 Macros en Excel 2010-2013

31/83

31

6. Agregamos los Cuadros de Texto restantes

Agregamos los cuadros de texto y las etiquetas restantes (ya lo hicimos para el nombre as que

repetimos para Edad y Fecha de Nacimiento).

Repetimos los pasos 4 y 5 y agregamos:

* Un TextBox cuyo(Name)seaUFEdad, con unaEtiquetallamada "Edad"

* Un TextBox cuyo(Name)seaUFFecha, con unaEtiquetallamada "Fecha Nac."

Es muy importante que no olvides asignar bien los nombres (Name) a los Textbox, si no la macro

dar error. Puedes revisar los pasos 4 y 5 para recordar como hacerlo.

Si los textbox no quedan alineados se pueden alinear de la siguiente forma:

1.Clic en el primerTextBox

2. Mantieniendo pulsada la teclaMAYUS, selecciona los otrosTextBox

3. Vamos al menFormato > Alinear > Izquierda

4.Hacemos clic en alguna parte vaca del Userform para

seleccionarlo y mostrar el cuadro de herramientas.

31Macros en Excel

7/24/2019 Macros en Excel 2010-2013

32/83

Para permitir que el usuario ejecute una accin, se pueden agregar un Botn de comando.

Nuestro Userform tendr un botn para Agregar los datos a la tabla Excel y otro botn paraCerrar el Userform.

1.En el Cuadro de Herramientas hacemos clic enBotn de comando.

2. En el Userform hacemos clic en la parte inferior izquierda para agregar el botn.

3.Con el nuevo botn seleccionado hacemos doble clic en la propiedad (Nombre) de la

Ventana de Propiedades, tipeamosUFAgregary luego enter.

4. Con el nuevo botn seleccionado hacemos doble clic en su propiedadCaptionen la Ventanade Propiedades, tipeamosAgregary luego enter.

5.Hacemos clic en alguna parte vaca del Userform para seleccionarlo y mostrar el Cuadro de

Herramientas.

6.Repetimos los pasos anteriores para agregar otro Botn de comando llamado UFCerrar y

nombre "Cerrar".

7.8 Si fuese necesario podemos reubicar los botones dentro del Userform.

Es muy importante que no olvides asignar bien los nombres(Name)a losBotones de comando, si

no la macro dar error. Esto se explica en el paso 7.3 y luego debers repetirlo para el otro

botn, tal cual se indica en el paso 7.6.

7. Agregamos los Botones

32Macros en Excel

7/24/2019 Macros en Excel 2010-2013

33/83

8. Agregamos el cdigo al botn Agregar

Private Sub UFAgregar_Click()

DimiFila As LongDimwsAs Worksheet

Setws = Worksheets(1)

'encuenta la siguiente fila vaca

iFila = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'Verifica que se ingrese un nombre

IfTrim(Me.UFNombre.Value) = ""Then

Me.UFNombre.SetFocus

MsgBox "Debe ingresar un nombre"

Exit Sub

End If

'copia los datos a la base de datos

ws.Cells(iFila, 1).Value = Me.UFNombre.Value

ws.Cells(iFila, 2).Value = Me.UFEdad.Value

ws.Cells(iFila, 3).Value = Me.UFFecha.Value

'limpa el formulario

Me.UFNombre.Value = ""

Me.UFEdad.Value = ""

Me.UFFecha.Value = ""

Me.UFNombre.SetFocus

End Sub

Seleccionamos nuestro botn de comando

Agregar y hacemos doble clic

9. Agregamos el cdigo al botn CerrarPrivate SubUFCerrar_Click()

Unload Me

End Sub

10.Permitir al usuario cerrar el Userform conla tecla ESC

1. Seleccionamos el botn de comandoCerrar

2. En laVentana Propiedadescambiamos la

propiedadCancelaTrue.

33Macros en Excel

7/24/2019 Macros en Excel 2010-2013

34/83

MACROS VARIOS

34

Utilizar la instruccin With

Cuando tenemos que definir muchas propiedades de un nicoobjeto, no es necesario hacer mencin al objeto cada vez,

porque esto le quita velocidad de ejecucin a la macro. Para

ganar velocidad y mantener el cdigo ms simple, conviene

hacer mencin al objeto una nica con la instruccin With y

luego definir sus propiedades, por ejemplo:

SubCambiarFormatoRango()

WithRange("A1").Value = 10

.Font.Bold =True

.Interior.ColorIndex = 5

End With

End Sub

Macros privadas

Si deseas ocultar las macros del libro en dicho cuadro puedes hacerlo utilizando la instruccin Private

cuando escribas tu macro. Debes utilizar dicha instruccin delante de la palabra Sub. De este modosolo ocultars la macro en cuestin. Ejemplo:

Private SubMiMacro()

'Esta macro no aparecer en la lista de macros

'Cdigo de tu macro aqu

End Sub

Si tienes muchas macros en el mdulo y quieres ocultarlas todas, en lugar de escribir la palabraPrivateen cada una de ellas, puedes colocar la siguiente instruccin al comienzo de tu mdulo:

Option Private Module

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

35/83

INSERTAR FRMULAS RELATIVAS

35

Insertar frmulas relativas

Si queremos insertar frmulas en celdas mediante macros, existe una forma ms rpido de

hacerlo que con Copiar y pegar. Por ejemplo:

Sub CopiarFormula()

Range("A1:A10").FormulaR1C1 = "=SUM(RC[1]:RC[5])"

End Sub

Evitar el uso de Copiar, Pegar y Seleccionar

Cuando se utiliza la grabadora de macros, es comn que se genere ms cdigo del necesario y

esto hace que funcione un poco ms lento. Las intrucciones que suelen ser redundantes son Select,

Selection.CopyyPaste. Supongamos que queremos copiar un rango en otro rango. Si utilizamos la

grabadora el cdigo ser el siguiente:

Sub CopiaRango()

Range("C10:C12").SelectSelection.Copy

Range("E10").Select

ActiveSheet.Paste

End Sub

SubCopiaRango()Range("C10:C12").Copy Range("E10")

End Sub

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

36/83

MACROS

36

Variables: Qu son las variables

y cmo se utilizan.

Libros: Macros para manipular

libros.

Hojas: Macros para manipular

hojas.Rangos:Macrosparamanipular

rangos.

Estructuras: Las estructuras

y macros ms comunes.

Comandos: Diversos comandos tiles muy utilizados.

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

37/83

VARIABLES

37

Para definir una variable utilizamos una sintaxis del tipo:

DimNombreVariableAsTipodeDato

Dim:Indica que estamos creando una variableNombreVariable:Escribimos el nombre de nuestra variable a gusto (sin espacios)As:Necesario para definir el tipo de variableTipodeDato: Debemos indicar si es una variable texto, fecha, valor, etc

Veamos un ejemplo sencillo:

SubMiNombre ()

'Indicamos que 'Nombre' es una variable 'String' o sea de tipo (texto)

DimNombreAs String

'Le asignamos un valor a la variable (tambin podramos indicarle una celda Excel)

Nombre = "Pedro"

' Mostramos el valor de la variable en un mensajeMsgbox Nombre

End Sub

La idea es que Excel recordar que la variable "Nombre" tiene asignado el valor"Pedro". Podremos utilizar dicha variable ms adelante en nuestro cdigo.

Macros en Excel

V i bl

7/24/2019 Macros en Excel 2010-2013

38/83

Variables

38

Definicin

Una variable es un lugar de almacenamiento con nombre que puede contener cierto tipo de datos

que puede ser modificado durante la ejecucin del programa. Cada variable tiene un nombre

nico que la identifica dentro de su nivel de mbito. Puede especificar un tipo de datos o no. Los

nombres de variable deben comenzar con un carcter alfabtico, deben ser nicos dentro del

mismo mbito, no deben contener ms de 255 caracteres y no pueden contener un punto o

carcter de declaracin de tipo.

Podemos declarar todo tipo evariables, por ello conviene tambin adquirir la costumbre de hacer

que el propio nombre de nuestras variable resulte descriptivo de la funcin que cumplir, porejemplo:

SubMacro()

'Definimos una variable numrica TipoCambio, que es el factor de conversin entre el

euro y la libra

Dim TipoCambioAs Double' Variable definida como valor con 2 decimales

TipoCambio = 1.45

' Esta macro multiplica el valor de la celda activa por el valor de la variable

anteriorActiveCell.Value = TipoCambio * ActiveCell.Value

End Sub

Macros en Excel

7/24/2019 Macros en Excel 2010-2013

39/83

Variables

39

Declaracin

Para que siempre nos veamos en la obligacin de declarar todas las variables que podamos ir

utilizando en nuestros mdulos (es decir obligarnos a definir nuestras variables de entrada).

Esto lo podemos realizar desde el editor de VBA, a traves del men Herramientas > Opciones >

pestaa Editor > y seleccionar la casilla Requerir declaracin de variables

Se usa en el nivel de mdulo para forzar declaraciones

explcitas de todas las variables en dicho mdulo, debe

aparecer en un mdulo antes de cualquier procedimiento.

Cuando Option Explicit aparece en un mdulo, debemosdeclarar explcitamente todas las variables mediante las

instrucciones Dim, Private, Public, ReDim o Static.SubMacroMensaje()' Declaramos la variable saludo como variable tipo texto

Dimsaludo As String

Dim despedida As String

'Damos un valor a la variable declaradasaludo = "Hola que tal"

'Damos un valor a la variable declarada

despedida = "Adios"

MsgBox saludo

MsgBox despedida

End Sub

Si se intenta utilizar unavariable no declarada seobtiene un error en el tiempode compilacin

Variante (Variable no declarada)

Macros en Excel

V i bl

7/24/2019 Macros en Excel 2010-2013

40/83

Variables

40

Tipos de Datos

SubMacro()

ForX = 1To 25

Cells(X, 1) = X

NextX

End Sub

La macro anterior utiliza bastantes menos recursos que esta

prxima que no fuerza la declaracin de la variable X (y

por lo tanto es considerada como Variant):

Tipo de datos Tamao IntervaloByte byte 0 a 255

Boolean bytes True o False

Integer bytes -32,768 a 32,767

Long (entero largo) bytes -2,147,483,648a 2,147,483,647Single (coma flotante/

precisin simple)

bytes -3,402823E38 a -1,401298E-45 para valores negativos;1,401298E-45 a

3,402823E38para valores positivos

Double (coma

flotante/ precisin

doble)

bytes -1.79769313486231E308a -4,94065645841247E-324paravalores negativos;

4,94065645841247E-324 a 1,79769313486232E308 para valores positivos

Currency (entero a

escala)

bytes -922.337.203.685.477,5808a 922.337.203.685.477,5807

Decimal bytes +/-79.228.162.514.264.337.593.543.950.335 sin punto decimal; +/-

7,9228162514264337593543950335 con 28 posiciones a la derecha del signo

decimal; el nmero ms pequeo distinto de cero es +/-0,0000000000000000000000000001

Date bytes 1 de enero de 100 a 31 de diciembre de 9999

Object bytes Referencia a tipo Object

String (longitud

variable)

bytes + longitud de la

cadena

0 a 2.000 millones

String (longitud fija) longitudde la cadena 1 a 65.400 aproximadamente

Variant (con nmeros) bytes valor numrico hasta el intervalo de un tipo Double

Variant (con

caracteres)

bytes + longitud de la

cadena

mismo intervalo que paraun tipo String de longitud variable

Definido por el

usuario (utilizando

Type)

nmero requeridopor los

elementos

intervalode cada elemento es el mismo que el intervalo de su tipo de datos.

Macros en Excel

V i bl

7/24/2019 Macros en Excel 2010-2013

41/83

Variables

41

Locales

Las variables locales se declaran dentro de cada macro, y solo pueden ser utilizadas dentro de la

macro en la que han sido creadas, cuando la macro termina de ejecutarse, la variable desaparece

y Excel libera la memoria que estaba utilizando recordando a qu hacan referencia.

Una variable se declara como local cuando est declarada dentro de la macro que har uso de la

misma, por ej:

Si una variable es local todas el resto de las macros

podrn utilizar el mismo nombre de la variable tal

SubMacro()DimXAs Single

DimYAs Single

DimZAs Single

X = 5

Y = 10

Z = 50

ActiveCell = X * Y * Z

MsgBox TypeName(Resultado)

End Sub

que:

Option Explicit

SubMacro1()

DimAutorAs String

Autor = "EXCEL"

ActiveCell = Autor

End Sub

Option Explicit

SubMacro2()

DimAutorAs String

Autor = "Pedro"

ActiveCell = AutorEnd Sub

Macros en Excel

V i bl

7/24/2019 Macros en Excel 2010-2013

42/83

Variables

42

Pblicas

Las variables declaradas mediante la instruccin Public estn disponibles para todos los

procedimientos en todos los mdulos de todas las aplicaciones, a menos que Option Private Module

est en efecto; en este caso, las variables slo son pblicas dentro del proyecto en el que residen.

Este tipo de variables son tiles cuando empezamos a trabajar con macros pues facilitan la

comprensin, y nos ahorran el hecho de tener que declarar lo mismo una y otra vez.

Usamos la instruccin Public para declarar el tipo

de datos de una variable. Por ejemplo, la

instruccin siguiente declara una variable como

de tipo Integer:

Public NmeroDeEmpleados As Integer

Usamos tambin la instruccin Public para

declarar el tipo de objeto de una variable.

Option Explicit

PublicXAs Byte

SubMacro()

ForX = 1To25

CallMacro2

NextX

End Sub

SubMacro2()

Cells(X, 1) = XEnd Sub

Macros en Excel

Variables

7/24/2019 Macros en Excel 2010-2013

43/83

Variables

43

Estticas

Las variables declaradas con la instruccin Static conservan su valor mientras el cdigo se est

ejecutando.

Una vez que el cdigo del mdulo se est ejecutando, las variables declaradas con la instruccin

Static conservan su valor hasta que se reinicie o restablezca el mdulo. En los mdulos de clase,

las variables declaradas con la instruccin Static conservan su valor en cada instancia de clase

hasta que se destruye la instancia. En mdulos de formulario, las variables estticas conservan su

valor hasta que se cierra el formulario. Use la instruccin Static en procedimientos no estticos

para declarar explcitamente variables que son visibles slo dentro de un procedimiento, pero

cuya vida es la misma que la del mdulo en la que se defini el procedimiento.

Option Explicit

PublicRgAsRange

SubMacro1()

DimxAs ByteForx = 1To25

SetRg = Cells(x, 1)

CallMacro2

Next x

End Sub

Sub Macro2()

StaticxAs Byte

x = x + 5

Rg.Select

Rg = x

End Sub

Macros en Excel

Variables

7/24/2019 Macros en Excel 2010-2013

44/83

Variables

44

Option Explicit

Const nPaises As Byte = 53

Const UKrate = 1.43, USrate = 0.78

Const autorLibro As String = "TodoEXCEL"

En la 1 lnea hemos definido una variable como nPaises as byte y su valor sera siempre 53.La 2 lnea de cdigo declara dos variables como constantes pero no especifica si son as double

o as, con lo cual se consideraran as variant.

La 3 linea de cdigo declara autorLibro como una constante de tipo string y le da el valor

TodoEXCEL

Constantes

Por lo general las variables hacen referencia a valores que

acostumbran a cambiar tal que, por ej:

SubMacro()

DimRngAsRange

DimxAs Byte

Forx = 1To25SetRng = Cells(x, 1)

Rng.Select

Rng = x

Nextx

End Sub

Pero a veces queremos que una variable no cambie, es decir que

siempre haga referencia al mismo valor, ejemplo: mi nombre, la

capital de Francia etc. Lgicamente carece de sentido declarar

variables como constante si stas no son pblicas.

Para declarar una variable como constante utilizamos la instruccin

Const, por ej:

Macros en Excel

Variables

7/24/2019 Macros en Excel 2010-2013

45/83

Variables

45

ConversinEs posible convertir variables de un tipo a otro. Existen distintas funciones que permiten hacer estas

conversiones.

Si la expresin del argumento expresin que se pasa a la funcin est fuera del intervalo del

tipo de dato al que se va a convertir, se produce un error. En general, el cdigo se puede

documentar utilizando las funciones de conversin de tipos de datos para indicar que el

resultado de alguna de las operaciones se debera expresar como un tipo de datos en

particular, no como el tipo de datos predeterminado.

Funcin Tipo devuelto Intervalo del argumento expresinCBool Boolean expresin de cadena o numrica vlida.

CByte Byte 0 a 255

CCur Currency .337.203.685.477,5808 a 922.337.203.685.477,5807.

CDate Date expresin de fecha

CDbl Double .79769313486231E308 a -4,94065645841247E-324 para valores

negativos; 4,94065645841247E-324 a 1,79769313486232E308 para valores

positivos.

Cdec +/-79.228.162.514.264.337.593.543.950.335 para nmeros basados en

cero, es decir, nmeros sin decimales. Para nmeros con 28 decimales, el intervalo es+/-7,9228162514264337593543950335. La menor posicin para un nmero

que no sea cero es 0,0000000000000000000000000001.

Cint .768 a 32.767; las fracciones se redondean.

Clong .147.483.648 a 2.147.483.647; las fracciones se redondean.

CSng ,402823E38 a -1,401298E-45 para valores negativos; 1,401298E-45 a

3,402823E38 para valores positivos.

Cstr mismo intervalo que Double para valores numricos. El mismo intervalo que

String para valores no numricos.

CVar valor de retorno de CStr depende del argumento expresin.

Macros en Excel

V i bl

7/24/2019 Macros en Excel 2010-2013

46/83

Variables

46

Conversin

Cuando la parte fraccionaria es exactamente 0,5, CInt y CLng siempre redondean al nmero par

ms cercano. Por ejemplo, 0,5 redondea a 0, y 1,5 redondea a 2. CInt y CLng se diferencian de

las funciones Fix e Int en que truncan, en lugar de redondear, la parte fraccionaria de un nmero.

Adems, Fix e Int siempre devuelven un valor del mismo tipo del que se le pasa.

Utilizamos la funcin IsDate para determinar si se puede convertir date a una fecha o una hora.

Si tubiramos una cadena de texto tal que:

SubEjCdate()

DimQAZAs StringQAZ = "5/5/2008"

IfIsDate(QAZ)Then

QAZ =CDate(QAZ)

MsgBox QAZ & " SI es una Fecha

."

Else

MsgBox QAZ & " no es una Fec ha"

End IfEnd Sub

CDate reconoce literales de fecha y literales de

hora adems de nmeros comprendidos dentrodel intervalo de fechas aceptables. Al convertir

un nmero a una fecha, la parte numrica entera

se convierte a una fecha. Cualquier parte

fraccionaria del nmero se convierte a la hora

del da, comenzando a medianoche.

CDate reconoce formatos de fecha que se ajusten a la configuracin regional de tu sistema. Es

posible que no se determine el orden correcto del da, mes y ao si se proporciona en un formato

diferente del que reconoce la configuracin de fecha. Adems, no se puede reconocer un formato

de fecha largo si contiene la cadena del da de la semana.

Macros en Excel

LIBROS

7/24/2019 Macros en Excel 2010-2013

47/83

LIBROS

47Macros en Excel

Mediante macros VBA podemos manipular de distintas formas tanto libros Excel como

archivos con otras extensiones. En este captulo veremos como hacerlo.

Sub AbrirLibro()

Dim Nombre As String

Nombre = "C:\MiLibro.xls"

' Si no se encuentra el libro hacemos que salga un mensaje de aviso

IfDir(Nombre) = "" Then

MsgBox Nombre & " no se encuentra"

ElseWorkbooks.Open FileName:=Nombre, updateLinks:=False

End If

End Sub

Para abrir un libro excel utilizamos la instruccin Open. Si queremos abrir un libro

determinado podemos utilizar la siguiente macro:

Abrir Libros

Libros

7/24/2019 Macros en Excel 2010-2013

48/83

Libros

48Macros en Excel

Cerrar Libros

Sub Cerrar()

ActiveWorkbook.Close

End Sub

Para cerrar un libro utilizamos la instruccin Close:

Sub CierraExcel()

Application.Quit

End Sub

Si quisiramos cerrar Excel:

Guardar Libros

Para guardar un libro utilizamos la instruccin SaveAs. Si queremos guardarlo con un

nombre que figura en una celda (por ej. si en la celda A1 est el nombre "QAZ"):

Sub Macro()

On Error Resume Next

' Ponemos On Error Resume Next, para evitar el error derivado de una celda vaca

y/o con un caracter con el que excel no permite guardar un libroActiveWorkbook.SaveAs [A1].Value

' Tambin podramos poner

' ActiveWorkbook.SaveAs Filename:=Range('A1').Value

End Sub

Libros

7/24/2019 Macros en Excel 2010-2013

49/83

Libros

49Macros en Excel

Borrar Libros

Para borrar un libro utilizamos la instruccin Delete:Sub BorraLibro ()

Workbooks("C:\TuLibro.xls").DeleteEnd Sub

Conviene recordar que excel nos pedir confirmacin antes de borrarlo.

Crear Libros

Para crear 1 nuevo libro utilizamos la instruccin Add, por ejemplo:

Sub NuevoLibro()

Workbooks.Add

End Sub

Si quisiramos crear 2 nuevos libros:

Sub NuevoLibro()

Workbooks.Add

Workbooks.Add

End Sub

Libros

7/24/2019 Macros en Excel 2010-2013

50/83

Libros

50Macros en Excel

Si queremos imprimir un libro y hoja determinada:

Sub Imprimir ()' Imprime el libro y hoja indicados

Workbooks("MiLibro.Xls").Sheets("Hoja1").Printout

End Sub

Si queremos imprimir un rango determinado, por ejemplo A4:H100

Sub ImprimirRango ()

' Imprime el rango indicado

Sheets("Hoja1").PageSetup.PrintArea = "$A$4:$H$100"

Workbooks("MiLibro.Xls").Sheets("Hoja1").Printout

End Sub

Imprimir Libros

Para imprimir libros u hojas utilizamos la instruccin PrintOut, por ejemplo:

Sub Imprimir ()

' Imprime el libro activoActiveWorkbook.Printout

End Sub

Libros

7/24/2019 Macros en Excel 2010-2013

51/83

Libros

51Macros en Excel

Proteger/Desproteger Libros

La proteccin de libros se hace con las instrucciones Protect y Unprotect, por ejemplo:

Sub ProtegeLibro()

With ActiveWorkbook' Primero protegemos esctuctura (mover, copiar, insertar hojas) y ventana (cambiar tamao o mover ven

tana)

.Protect Structure:=True, Windows:=True

' Desprotegemos con clave 'Excel'

.Unprotect ("Excel")

' Protegemos con clave 'Excel'

.Protect ("Excel")

End With

End Sub

Para proteger un libro primero se debe desproteger con Unprotect y luego protegemos

con Protect. La clave se pone entre comillas y parntesis y es sensible a las maysculas (si

protejo con "Excel" la clave "excel" no funcionar).

Para desproteger el libro hacemos:

Sub DesprotegeLibro ()

ActiveWorkbook.Unprotect ("Excel")

End Sub

HOJAS

7/24/2019 Macros en Excel 2010-2013

52/83

HOJAS

52Macros en Excel

Agregar

Para agregar una nueva hoja al libro utilizamos la instruccin Add. Si queremos agregar

una nueva hora al libro activo utilizamos el siguiente cdigo:

Para agregar una nueva hoja al libro utilizamos la instruccin Add. Si queremos agregar

una nueva hora al libro activo utilizamos el siguiente cdigo:

Sub AgregarHoja()

ActiveWorkbook.Worksheets.Add

End Sub

Otra forma de hacerlo es:

Sub AgregarHoja()

Dim Hoja As Worksheet

Set Hoja = Worksheets.AddEnd Sub

Hojas

7/24/2019 Macros en Excel 2010-2013

53/83

Hojas

53Macros en Excel

Seleccionar

Para referirnos a una hoja dentro de un podemos utilizar la instruccin Select o Activate.

Seleccionar una hoja utilizando su nmero de hoja contenida dentro del libro, como se ve

a continuacin:

Sub Seleccionar()

' para seleccionar la primera hoja del libro activo

ActiveWorkbook.Sheets(1).Select

' para seleccionar la segunda hoja del libro activo

ActiveWorkbook.Sheets(2).Select' para seleccionar y /o activar la hoja con nombre Hoja3 del libro activo

ActiveWorkbook.Sheets(Hoja3).Activate

End Sub

Utilizando el nombre de la hoja en VBA Hoja1(PrimeraHoja) contenida dentro del libro,

como se ve a continuacin:

Sub Seleccionar()

' para seleccionar la segunda hoja del libro activo

Hoja1.Activate ' tambin podra ser Hoja1.Select

End Sub

Hojas

7/24/2019 Macros en Excel 2010-2013

54/83

Hojas

54Macros en Excel

Borrar Hojas

Cuando se elimina una hoja o un libro, excel siempre nos pide confirmacin, del mismo

modo que cuando eliminamos cualquier otro fichero.

En este ejemplo se elimina la hoja llamada "Hoja1" en caso de que

confirmemos la opcin que observaremos cuando se ejecute el cdigo:

Sub BorrarHoja()

Sheets("Hoja1").Delete

End Sub

En este otro ejemplo se elimina la hoja1 del libro activo sin mostrar el cuadro

de dilogo de confirmacin:

Sub borrarHoja()

Application.DisplayAlerts = False

Sheets("Hoja1").Delete

Application.DisplayAlerts = True

End Sub

Hojas

7/24/2019 Macros en Excel 2010-2013

55/83

Hojas

55Macros en Excel

Imprimir Hojas

Para imprimir la hoja actual hacemos la instruccin PrintOut, tal que:

Sub ImprimeHoja ()

ActiveSheet.PrintOutEnd Sub

Si adems deseamos fijar previamente un rango de impresin hacemos:

Sub ImprimeRango ()

ActiveSheet.PageSetup.PrintArea = Range("$A$1:$C$10").Address

ActiveSheet.PrintOut

End Sub

En la macro anterior, cuando queremos imprimir un rango, es necesario agregar la instruccinAddress, caso contrario no funcionar.

Si deseamos poner un encabezado a la izquierda de cada hoja, con la frase "Reporte Trimestral" (o

cualquier otro mensaje):

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim Sht As WorksheetFor Each Sht In ThisWorkbook.Sheets

With Sht.PageSetup

.LeftHeader = "Reporte Trimestral" ' O cualquier otro mensaje...

End With

Next Sht

End Sub

RANGOS

7/24/2019 Macros en Excel 2010-2013

56/83

RANGOS

56Macros en Excel

Seleccin de celdas

Existen diversas formas de seleccionar celdas, y en todas ellas utilizamos el mtodo

Select.

Veamos 3 mtodos (equivalentes) para seleccionar una celda, por ejemplo la celda C4:

' La forma clsica de seleccionar

Sub Seleccionar ()

Range("C4").Select

End Sub

' Esta es otra terminologa vlida para seleccionar

Sub Seleccionar ()

[C4].Select

End Sub

' Finalmente podemos seleccionar de esta otra forma

' El primer N equivale a fila y el segundo a columna.

Sub Seleccionar ()

Cells(4, 3).Select

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

57/83

Rangos

57Macros en Excel

Seleccin de rangos

Veamos ahora distintas formas equivalentes de seleccionar un rango:

' Mtodo clsico para seleccionar el rango de celdas B6:B10

Sub Seleccionar ()Range("B6:B10").Select

End Sub

Sub Seleccionar ()

'Otro mtodo para seleccionar el rango de celdas B6:B10

[B6:B10].SelectEnd Sub

Supongamos que queremos seleccionar un rango, pero no conocemos su dimensin,

por ejemplo porque la misma va cambiando. Un ejemplo puede ser una tabla donde se

van agregando nuevos registros. Con la instruccin CurrentRegion Excel seleccionar

todo el rango utilizado automticamente, aunque no conozcamos su tamao deantemano:

' Selecciona la regin actual

Sub Seleccionar ()

Selection.CurrentRegion.Select

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

58/83

Rangos

58Macros en Excel

Con la macro anterior se seleciona el cuadrante, rectngulo y/o cuadrado de celdas

adyacentes a nuestra celda activa. El rectngulo queda definido por las celdas con

informacin.

Tambin podramos modificar la macro anterior para que seleccione la regin actual

desde una celda determinada, por ejemplo:

' Selecciona el rectngulo adyacente a C2.

Sub Seleccionar ()[C2].CurrentRegion.Select

End Sub

o tambin podemos hacer:

' Selecciona el rectngulo adyacente a C2.Sub Seleccionar ()

Range("C2").CurrentRegion.Select

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

59/83

Rangos

59Macros en Excel

Seleccin de filas

Para seleccionar filas podemos utilizar la instruccin Rows.Select, tal como vemos en las

siguientes macros:

' Seleccionar la Fila 1.

Sub SeleccionaFila ()

Rows("1:1").Select

End Sub

' Selecciona desde la fila 4 hasta la 11, ambas inclusive

Sub SeleccionaFilas ()

Rows("4:11").Select

End Sub

' Selecciona las siguientes filas a la vez: 4,8,15 y 22.

Sub SeleccionaFilas ()

Range("4:4,8:8,15:15,22:22").Select

End Sub

' Selecciona la fila 4.

Sub SeleccionaFila ()

Rows("4:4").Select

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

60/83

Rangos

60Macros en Excel

Seleccin de columnas

Para seleccionar columnas podemos utilizar la instruccin Columns.Select, tal

como vemos en las siguientes macros:

Sub SeleccionaColumna ()

' Selecciona la Columna B

Columns("B:B").Select

' Selecciona la Columna H

Columns("H:H").Select

' Selecciona des de la Columna C hasta la F

Columns("C:F").Select

' Selecciona las siguientes Columnas: A,C,E,G, I y K.

Range("A:A,C:C,E:E,G:G,I:K").SelectEnd Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

61/83

Rangos

61Macros en Excel

Otras selecciones

Excel considera que pese a que tengamos 65.536 filas y 256 columnas, prcticamente siempre

utilizamos una nfima cantidad de estas filas y columnas.

Para saber el rango de celdas que utilizamos basta con utilizar el siguiente cdigo:Sub Rango()

ActiveSheet.UsedRange.Select

End Sub

Para seleccionar desde la celda A1 hasta la ltima celda del rango utilizado:

Sub Rango()

Range([a1], ActiveSheet.UsedRange).Select

End Sub

Para selecionar la ltima celda con datos del rango utilizado (suponemos que tenemos

seleccionado B4:D4):

Sub Seleccion ()

' Esto dara como resultado 5

R = Range([a1], ActiveSheet.UsedRange).Rows.Count' Esto dara como resultado 4

C = Range([a1], ActiveSheet.UsedRange).Columns.Count

' Selecionara la celda D5

Cells(R, C).Select

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

62/83

Rangos

62Macros en Excel

Insertar rangosTambin podemos insertar tantas nuevas

filas o columnas como tenga el rango:

Sub Inserta ()

' Inserta 3 nuevas columnas a la izqui

erda de la columna B

Range("B1:D10").EntireColumn.Inser

t

' Inserta 5 nuevas filas sobre la fila 1

0.

Rows("10:15").Insert

End Sub

Como es lgico de suponer, existen numerosas

maneras de insertar rangos, para lo cual

utilizamos la instruccin Insert:

Sub Insertar ()

' Inserta una nueva fila sobre la fila 1.

Rows(1).Insert

' Inserta una nueva columna a la izquierda de la columna A

Columns("A:A").Insert

' Tambin inserta una nueva columna a la izq

uierda de la columna A

[A1].EntireColumn.Insert

' Inserta una nueva fila sobre la fila 1

[A1].EntireRow.Insert

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

63/83

Rangos

63Macros en Excel

Borrar rangos

Podemos borrar comentarios, contenidos, formatos, etc. En general utilizamos la instruccin Clear

seguida de lo que queremos borrar. A continuacin vemos como hacerlo:

' Borra los comentarios sobre la celda A1

Sub Borrar ()

Range("A1").ClearComments

End Sub

Si queremos realizar el borrado de diferentes propiedades de un mismo rango podramos hacer:

Sub Borrar ()

With Range("A1")

.ClearComments 'Borra comentarios

.ClearContents 'Borra contenidos

.ClearFormats 'Borra formatos

.ClearNotes 'Borra notas

.Clear 'Borra todo lo anteriorEnd With

End Sub

La instruccin Clear sola, como se v en la macro anterior, borra toda informacin de la celda

(formato, contenido, comentarios, etc).

' Borra el contenido de la celda A1, no el formato

Sub Borrar ()

Cells(1,1)..ClearContents

End Sub

' Limpia la celda A1 de cualquier formato que tenga,

por ej. color

Sub Borrar ()

[A1].ClearFormats

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

64/83

Rangos

64Macros en Excel

Combinar rangos

Para combinar celdas utilizamos la instruccin MergeCells. Solo podemos combinar celdas que estn

contiguas y siempre que formen o un cuadrado o un rectngulo, pues de lo contrario Excel no lo

permitira.Para combinar un rango de celda hacemos:

' Combinar las celdas del rango B4:D4

Sub Combinar ()


Range("B4:D4").MergeCells = True

End Sub

Conviene recordar que si combinamos desde una macro, y las celdas que queremos combinar no

estan vacas, Excel nos pedir confirmacin, pues podramos perder informacin. Para evitar la

confirmacin deberamos modificar la macro anterior agregando una nueva lnea al comienzo:

' Combinar las celdas del rango B4:D4 y evitar confirmacinSub Combinar ()


Range("B4:D4").MergeCells = True

End Sub

Rangos

7/24/2019 Macros en Excel 2010-2013

65/83

Rangos

65Macros en Excel

Transponer rangos

La operacin de transponer se realiza cuando queremos que los valores de una fila se

copien como una columna o viceversa. Para ello utilizamos la instruccin Transpose.

Dicho de otra forma es una manera particular de pegar los datos copiados previamente

con lo cual:

Sub Seleccion ()

' Convertir fila en columna

Range("A1:A15").Copy

Range("D4").PasteSpecial Paste:=xlPasteAll, Transpose:=True

' Convertir columna en fila

Range("A1:B10").CopyRange("D4").PasteSpecial Paste:=xlPasteAll, Transpose:=True

End Sub

ESTRUCTURAS

7/24/2019 Macros en Excel 2010-2013

66/83

66Macros en Excel

For Next

Las estructuras son conjuntos de instrucciones VBA que deben respetar una sintaxis

determinada y que nos permiten realizar diversas operaciones

La instruccin For Next repite un grupo de instrucciones un nmero especificado deveces. La sintaxis es:

For contador = principio To fin [Step incremento]

[instrucciones]

[Exit For]

[instrucciones]

Next [contador]

La sintaxis de la instruccin For...Next consta de las siguientes partes:

-Contador (requerido): variable numrica que se utiliza como contador de bucle. La

variable no puede ser Booleana ni un elemento de matriz.

-Principio (requerido): valor inicial del contador.

-Fin (requerido): valor final del contador.

-Incremento (opcional): cantidad en la que cambia el contador cada vez que se ejecuta

el bucle. Si no se especifica, el valor predeterminado de incremento es uno.

-Instrucciones (opcional): una o ms instrucciones entre For y Next que se ejecutan un

nmero especificado de veces.

Estructuras

7/24/2019 Macros en Excel 2010-2013

67/83

67Macros en Excel

For Next

Cuando el incremento es negativo es necesario especificar el incremento que va a tener

lugar en el bucle mediante la palabra Step tal que:

Sub For_Next()Dim X As Integer

' Establece 10 repeticiones, con incremento negativo de 1

For X = 1 0 To 1 Step -1

Cells(X, 1) = X

Next X

End Sub

En el caso anterior X tomara los valores 10,9,8,7,6,5,4,3,2,1 sucesivamente.

Se pueden anidar bucles For...Next, colocando un bucle For...Next dentro de otro. Para ello,

proporcionamos a cada bucle un nombre de variable nico como su contador. La siguiente

construccin sera la correcta:

For I = 1 To 10For J = 1 To 10

For K = 1 To 10

...

Next K

Next J

Next I

Estructuras

7/24/2019 Macros en Excel 2010-2013

68/83

68Macros en Excel

While Wend

La instruccin While Wend ejecuta una serie de instrucciones mientras una condicin dada sea

True, es decir verdadera, o tenga lugar. Su sintaxis es:

While condicin

[intrucciones]

Wend

Condicin (requerido): expresin numrica o expresin de cadena cuyo valor es True o False. Si

condicin es Null, condicin se considera False.

Instrucciones (opcional): una o ms instrucciones que se ejecutan mientras la condicin es True.

Si condicin es True, todas las instrucciones se ejecutan hasta que se encuentra la instruccin

Wend. Despus, el control vuelve a la instruccin While y se comprueba de nuevo condicin. Si

condicin es an True, se repite el proceso. Si no es True, la ejecucin se reanuda con la

instruccin que sigue a la instruccin Wend.

Los bucles While...Wend se pueden anidar a cualquier nivel. Cada Wend coincide con el While ms

reciente.

Estructuras

7/24/2019 Macros en Excel 2010-2013

69/83

69Macros en Excel

With End With

La instruccin With End With ejecuta una serie de instrucciones sobre un nico objeto o sobre un tipo

definido por el usuario. Permite realizar mltiples operaciones sobre el mismo objeto. La sintaxis que

emplea es:

With objeto

[instrucciones]

End With

Objeto (requerido): nombre de un objeto o de un tipo definido por el usuario.

Instrucciones (opcional): una o ms instrucciones que se van a ejecutar sobre objeto.

Veamos un ejemplo sencillo con el cual asignamos un formato a la celda A1:

' Repetimos lo anterior pero con With

Sub Macro()

With Range("A1")

.Font.Bold = True

.Font.Size = 50

.Font.ColorIndex = 3

End With

End Sub

Estructuras

7/24/2019 Macros en Excel 2010-2013

70/83

70Macros en Excel

If Then

La instruccin If Then permite escoger entre 2 o ms posibilidades, para decidir sobre que es lo que

queremos, entre una serie de alternativas.

La instruccin ejecuta condicionalmente un grupo de instrucciones, dependiendo del valor de unaexpresin. Su sintaxis es:

If condicin Then [instrucciones]-[Else instrucciones_else]

Tambien podemos utilizar la siguiente sintaxis en formato de bloque:

If condicin Then

[instrucciones]

[ElseIf condicin-n Then[instrucciones_elseif] ...

[Else

[instrucciones_else]]

End If

Condicin (requerido): uno o ms de los siguientes dos tipos de expresiones: Una expresin numrica o

expresin de cadena que puede ser evaluada como True o False. Si condicin es Null, condicin se

considera False.

Una o ms instrucciones separadas por dos puntos ejecutados si la condicin es True. condicin-n

(Opcional). igual que -condicin.

instrucciones_elseif (Opcional): una o ms instrucciones ejecutadas si la condicin-n asociada es True.

instrucciones_else (opcional): una o ms instrucciones ejecutadas si ninguna de las expresiones anteriores

condicin o condicin-n es True.

Estructuras

7/24/2019 Macros en Excel 2010-2013

71/83

71Macros en Excel

If Then

Veamos algunos ejemplos de If, escritos en una sola lnea:

En esta macro si el valor de la celda A1 es > a 15, entonces la condicin es verdadera (esdecir TRUE) con lo cual se ejecuta la instruccin definida para tal caso, en este ejemplo

lanzamos un mensaje, que dice: "La celda A1> 15"

Sub If_Then()

If[A1] > 15 Then Msgbox "La celda A1> 15"

End Sub

En esta otra macro si el valor de la celda es >15 se cumple con la condicin demandada

con lo cual se ejecuta la primera opcin, en caso de que no sea TRUE (es decir en caso

de que sea FALSE) ejecutamos la segunda instruccin, en nuestro ejemplo, el mensaje:

"La celda A1< 15"

Sub If_Then()

If[A1] > 15 Then Msgbox "La celda A1> 15" Else MsgBox "La celda A1< 15"

End Sub

Estructuras

7/24/2019 Macros en Excel 2010-2013

72/83

72Macros en Excel

If Then

Veamos algunos ejemplos de If, escritos en bloque:

En caso de que el valor de la celda A1>15 entonces la condicion es True, y siendo True,

lanzaramos un mensaje y despus pondramos la celda A1 en negrita y con fondo Rojo.

Sub If_Then()

If[A1] > 15 Then

Msgbox "La celda A1> 15"

With [A1]

.Interior.ColorIndex = 3

.Font.Bold = TrueEnd With

End If

End Sub

o bien:

Sub If_Then()

If[A1] > 15 Then ' si la condicion es TRUE

MsgBox "La celda A1> 15" ' lanzaramos un mensaje

[A1].Interior.ColorIndex = 3 ' el fondo de la celda [A1] seria rojo

[A1].Font.Bold = True ' y la celda [A1] estara en negritaElse ' Si la condicion fuera False

[A1].Interior.ColorIndex = 10 'el fondo de la celda [A1] sera Verde

[A1].Font.Size = 50 ' y la fuente tendra tamao 50

End If

End Sub

Estructuras

7/24/2019 Macros en Excel 2010-2013

73/83

73Macros en Excel

Select Case

La instruccin Select Case se utiliza cuando las alternativas en una estructura de If Then

son muchas. En tal caso conviene evitar el If Then y hacer uso de Select Case, que

permite ejecutar uno de varios grupos de instrucciones, dependiendo del valor de unaexpresin.

La sintaxis de Select Case es:

Select Case expresin_prueba

[Case lista_expresion-n[instrucciones-n]] ...

[Case Else

[instrucciones_else]]

End Select

Estructuras

7/24/2019 Macros en Excel 2010-2013

74/83

74Macros en Excel

Select Case

Veamos un ejemplo:

Sub Select_Case()Dim Nmero As Variant ' la declaramos como Variant para evitar el error de compilacin que

' obtendramos en el caso de que [A1] fuera texto

Nmero = [A1] ' Inicializa variable.

Select Case Nmero ' Evala Nmero.

Case 1 To 5: MsgBox "Entre 1 y 5" ' Para Ns entre 1 y 5, inclusive

Case 6, 7, 8: MsgBox "Entre 6 y 8" ' Para Ns entre 1 y 5, inclusive

Case 9 To 10: MsgBox "Mayor que 8" ' Para Ns entre 9 y 10, inclusiveCase Else ' Para otros valores.

MsgBox "No est entre 1 y 10"

End Select

End Sub

Si expresin prueba coincide con cualquier lista expresin asociada con una clusula Case, las

instrucciones que siguen a esa clusula Case se ejecutan hasta la siguiente clusula Case o, para la

ltima clusula, hasta la instruccin End Select. El control pasa despus a la instruccin que sigue

a End Select. Si expresin prueba coincide con una expresin de lista expresin en ms de una

clusula Case, slo se ejecutan las instrucciones que siguen a la primera coincidencia.

COMANDOS

7/24/2019 Macros en Excel 2010-2013

75/83

75Macros en Excel

Mensajes de alerta

Los mensajes de alerta son avisos que dispara Excel antes de realizar determinadas acciones. Por

ejemplo, al intentar borrar una Hoja, nos aparece una aviso de confirmacin. A nivel de macros, es

importante tenerlos presentes, tal como vemos a continuacin:

Sub BorrarHoja()

' Borramos una hoja

ActiveSheet.Delete

' Pero esto requiere la confirmacion por parte del usuario...

' Para evitar el mensaje de alerta, ya que Excel siempre nos pedir confirmacin,' bastar con utilizar la siguiente instruccin:


' Con lo cual ahora s podremos borrar la hoja dada sin necesidad de confirmacin

ActiveSheet.Delete

' Finalmente dejamos a Excel en su estado original de mostrar los mensajes

Application.DisplayAlerts = True

End Sub

Es importante recordar que el Application.DisplayAlerts=False siempre debe ir antes de la accin

que requiere confirmacin. Al final de la macro conviene volver a asignarle la propiedad True.

COMANDOS

7/24/2019 Macros en Excel 2010-2013

76/83

76Macros en Excel

Barra de estado

La barra de estado de Excel es la que figura en la parte inferior y suele mostrar distintos

mensajes. Esta barra se puede mostrar u ocultar desde el men Ver > Barra de estado.

A continuacin se exponen algunas instrucciones para saber como manipularla y modificarla:

Sub BarraEstado()

' Si queremos ver la barra de estado original

Application.DisplayStatusBar = True

' Si NO queremos verla bastar con:Application.DisplayStatusBar = False

' Por defecto la Barra de estado siempre estar Visible, es decir:

Application.DisplayStatusBar = True

' Si queremos que la barra de estado muestre un mensage diferente bastar con:

Application.DisplayStatusBar = "Procesando mi reporte..."

' Si queremos que la Barra de Estado este limpia, es decir sin mensaje alguno:

Application.DisplayStatusBar = ""

End Sub

COMANDOS

7/24/2019 Macros en Excel 2010-2013

77/83

77Macros en Excel

Msgbox

Un Msgbox es simplemente una ventana que muestra un mensaje. Por ej., con la siguiente

instruccin mostraramos un saludo:

Sub Saludo()

' Escribimos el mensaje entre comillas

Msgbox "Hola, Excel te saluda!"

End Sub

Por defecto, un Msgbox siempre incluye el botn "Aceptar", el cual hara desaparecer el mensaje y

continuara ejecutando el cdigo restante si lo hubiera. Tambin podemos configurar para que en

lugar del botn "Aceptar" aparezca un botn de "Ok" y "Cancelar" y de acuerdo al que elija el

usuario, la macro continuara con un cdigo u otro cdigo. Esto lo realizamos agregando la

instruccin vbOKCancel, tal como vemos a continuacin:Sub Mensaje()

Dim Respuesta As Integer

Respuesta = MsgBox("Seleccione Aceptar o Cancelar", vbOKCancel)

' verificamos si se presion 'Cancelar'

IfRespuesta = vbCancel Then

MsgBox "Macro caso Cancelar" ' Aqu continuara la macro en caso de CancelarEnd If

' si se presion 'Aceptar'

IfRespuesta = vbOK Then

MsgBox "Macro caso Aceptar" ' Aqu continuara la macro en caso de Aceptar

End If

End Sub

COMANDOS

7/24/2019 Macros en Excel 2010-2013

78/83

78Macros en Excel

InputBox

Un InputBox se utiliza cuando requerimos un input del Usuario (es decir, cuando esperamos que el

usuario introduzca un dato). Bsicamente lo que hacemos es asignarle un valor a una variable, pero

le damos el control al usuario para que asigne dicho valor o dato a gusto.

Dentro del InputBox utilizamos las siguientes instrucciones:

Prompt: mensaje del InputBox

Title: ttulo del InputBox

Default: valor por defecto al abrir el InputBox (si omitimos no aparecer nada)

Type: indica el tipo de dato a introducir (1 Nmero, 2 Texto, 4 True o False, 5 Rangos)

Vamos ahora como queda el InputBox:

Sub Nombre()

' el dato que proporcionar el usuario quedar registrado por la Variable Respuesta. Como no sabem

os que tipo de dato es lo definimos como una variable de tipo Variant.

Dim Nombre As Variant

Nombre = Application.InputBox(prompt:="Escriba su nombre", Title:="Colegio de Abogados", _

Default:="Pedro", Type:=2)

Msgbox Nombre ' En lugar de este Msgbox continuara el resto de la macro...

End Sub

LIBRERIAS

7/24/2019 Macros en Excel 2010-2013

79/83

79Macros en Excel

Para borrar filas debemos posicionarnos en una celda y utilizar la instruccin

EntireRow.DeleteVeamos un ejemplo:

'En esta macro recorremos la columna 2 (la B) desde la fila 2000 hasta la 1

'Si la celda tiene el valor "q" entonces borramos la fila

SubBorrarFilas()DimrAs Long

Forr = 2000To1Step-1 'Debemos ir desde abajo hacia arriba

IfCells(r, 2).Value = "q"ThenCells(r, 2).EntireRow.Delete

Nextr

End Sub

Borrar Filas

LIBRERIAS

7/24/2019 Macros en Excel 2010-2013

80/83

80Macros en Excel

Bsquedas

SubBuscarCodigo()

'Definimos variables

DimlookupvalueAs Variant, valueAs Variant, lookupRangeAsRange

value = Range("A1").value'celda con el valor buscado

SetlookupRange = Range("A10:A100")'rango donde buscar

lookupvalue = Application.VLookup(value, lookupRange, 3,False)'Queremos la columna 3

'Si no encuentra valor terminamos la macro

If IsError(lookupvalue)Then exit sub

'Si lo encuentra lo devuelve

Else

Msgbox lookupvalue

End If

End Sub

Para hacer bsquedas (el equivalente a la funcin "buscarv" de Excel) utilizamos la

instruccinlookupvalue.

Veamos un ejemplo:

LIBRERIAS

7/24/2019 Macros en Excel 2010-2013

81/83

81Macros en Excel

Un ejemplo sencillito sobre copiar una celda y pegarla como valor.

SubCopiarPegar()

Range("A104").Copy

Range("A105").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode =False

End Sub

Copiar Pegar

ImgenesVeamos algunas operaciones que podemos realizar con imgenes.

'INSERTAR UNA IMAGEN

Public SubInsertaLogo()

DimvFullNameAs Variant

vFullName = Application.GetOpenFilename("Image Files,*.jpg;*.gif")

IfvFullName False Then

Hoja6.Pictures.InsertCStr(vFullName)

End If

End Sub

LIBRERIAS

7/24/2019 Macros en Excel 2010-2013

82/83

82Macros en Excel

'Con esto ordeno una columna ascendentemente

Range("A38:G65536").Sort Key1:=Range("F39"), Order1:=xlDescending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

'En A38:G38 esta el encabezado. El Key1 es F39 (una fila mas abajo, osea el primer registro)

Ordenar

Macros utilizadas para ordenar

LIBRERIAS

7/24/2019 Macros en Excel 2010-2013

83/83

Algunas macros de inters relacionadas con tablas dinmicas (TD).

'La siguiente macro actualiza una TD automticamente al entrar a la hoja

'Esta macro debe estar colocada en la misma hoja que contiene la TD


ActiveSheet.PivotTables(1).PivotCache.Refresh

End Sub

Tablas Dinmicas

Documents

Macros en Excel 2010-2013