01 MacrosVBAExcelAvanzado_EAA de 11

8/7/2019 01 MacrosVBAExcelAvanzado_EAA de 11

1/31

Macros en Excel con VBAIng. Enrique Alfaro


2/31

Por qu usar VBA en Excel 2010?

Microsoft Excel 2010 es una herramienta muy eficazque se puede usar para manipular, analizar ypresentar datos. A veces, no obstante, a pesar delamplio conjunto de caractersticas que ofrece la

interfaz de usuario (UI) estndar de Excel, es posibleque se desee encontrar una manera ms fcil derealizar una tarea repetitiva y comn, o de realizaralguna tarea no incluida en la interfaz de usuario.

Afortunadamente, las aplicaciones de Office, comoExcel, tienen Visual Basic para Aplicaciones (VBA),un lenguaje de programacin que brinda laposibilidad de ampliar dichas aplicaciones.

29/03/2011Ing. Enrique Alfaro

2


3/31


VBA funciona mediante la ejecucin de macros ,procedimientos paso a paso escritos en Visual Basic.Aprender a programar podra parecer intimidante,pero con algo de paciencia y algunos ejemplos, como

los que se incluyen en este curso, muchos usuariosencuentran que conocer aunque sea una pequeaparte del cdigo de VBA facilita su trabajo y lesbrinda la posibilidad de ejecutar tareas en Office que

antes crean imposibles de realizar. Si se adquierenalgunos conocimientos sobre VBA, resultar muchoms sencillo seguir aprendiendo, y las posibilidadesde que hacer son ilimitadas.


3


4/31


Sin duda, la razn ms frecuente por la cul se usaVBA en Excel es para automatizar tareas repetitivas.Por ejemplo, suponga que tiene docenas de libroscon docenas de hojas de clculo y necesita realizarcambios en cada uno de ellos. Los cambios podranser tan simples como aplicar formato nuevo a algnrango fijo de celdas, o bien tan complejos comobuscar algunas caractersticas estadsticas de losdatos en cada hoja, elegir el mejor tipo de grficopara mostrar datos con esas caractersticas y, acontinuacin, crear y dar formato al grfico enconsecuencia.


4


5/31


VBA no es til solo para tareas repetitivas. Tambinpuede usar VBA para crear nuevas funcionalidadesen Excel (por ejemplo, puede desarrollar nuevosalgoritmos para analizar datos y, a continuacin,usar las funcionalidades de grficos de Excel paramostrar los resultados) y realizar tareas que integrenExcel con otras aplicaciones de Office, comoMicrosoft Access 2010. De hecho, de todas lasaplicaciones de Office, Excel es la ms usada como

algo similar a una plataforma de desarrollo general.Adems de todas las tareas obvias que implican listasy contabilidad, los programadores usan Excel en unaamplia variedad de tareas, desde visualizacin de

datos hasta prototipos de software.


5


6/31

Uso de cdigo para hacer que las

aplicaciones realicen las tareasQuiz crea que escribir cdigo es algo misterioso o

complicado, pero sus principios bsicos usan lalgica diaria y son bastante accesibles. Lasaplicaciones de Office 2010 estn creadas de talmodo que exponen lo que se denomina objetos. Estosobjetos reciben instrucciones. Para interactuar conlas aplicaciones, se envan instrucciones a variosobjetos de la aplicacin. Los objetos, si bien sonvariados y flexibles, tienen sus lmites. Solo puedenhacer aquello para lo que fueron diseados y soloharn lo que se les indique que hagan.


6


7/31

Objetos

Los objetos de programacin se relacionanentre s sistemticamente en una jerarquadenominada modelo de objetos de la

aplicacin. El modelo de objetos bsicamenterefleja lo que se ve en la interfaz de usuario.Por ejemplo, el modelo de objetos de Excelcontiene los objetos Application,Workbook, Sheet yChart, entre muchosotros. El modelo de objetos constituye el mapaconceptual de la aplicacin y sus

funcionalidades.


7


8/31

Propiedades y mtodos

Es posible manipular objetos al establecersus Propiedades y llamar a sus mtodos. Sise establece una propiedad, se producen

algunos cambios en la calidad del objeto. Sise llama a un mtodo, se logra que el objetorealice una determinada accin. Por

ejemplo, el objeto Workbooktiene unmtodo Close que cierra el libro y unapropiedad ActiveSheet que representa la

hoja activa del libro.


8


9/31

Colecciones

Varios objetos vienen en las versionessingular y plural; por ejemplo, libro y libros,hoja de clculo y hojas de clculo. Las

versiones en plural se denominancolecciones. Los objetos de una coleccinse usan para realizar una accin en varios

elementos de esa coleccin. Ms adelante, eneste artculo, se explicar cmo usar lacoleccin Worksheets para cambiar elnombre de cada hoja de clculo de un libro.


9


10/31


11/31

Ficha Programador

Todas las aplicaciones de Office 2010 usan la cinta deopciones. La ficha Programador es una de las fichas

incluidas en la cinta de opciones, donde se puede tener accesoal Editor de Visual Basic y a otras herramientas deprogramador. Debido a que Office 2010 no muestra la fichaProgramador de manera predeterminada, debe habilitarla

mediante el siguiente procedimiento:1. Ficha Archivo

2. Opciones

3. Personalizar cinta de opciones

4. Activar la casilla Programador y


11


12/31

Problemas de seguridad

Haga clic en el botn de Seguridad de macros paraespecificar qu macros pueden ejecutarse y en qucondiciones. Aunque el cdigo de macros de sistemas noconfiables puede daar gravemente el equipo, las condicionesde seguridad que impiden ejecutar macros tiles pueden

disminuir en gran medida la productividad. Si la barraAdvertencia de seguridad: las macros se handeshabilitado aparece entre la cinta de opciones y el libro,cuando abre un libro que contiene una macro, puede hacer

clic en el botn Habilitar contenido para habilitar lasmacros.

Adems, como medida de seguridad, no puede guardar unamacro en el formato de archivo predeterminado de Excel(.xlsx); debe guardar la macro en un archivo con extensin

especial (.xlsm).


12


13/31

Problemas de Seguridad


13


14/31

mi primera Macro

Editor de Visual Basic Alt+F11 Barra de Herramientas: Visual

Basic Herramientas, Macro, Editor

de V.B.

InsertarMdulo

Sub Bienvenido1()

msgBox Hola a todos"

End Sub

Sub Bienvenido2()

ActiveCell.Value = Hola a todos"

End Sub


15/31

Uso de la grabadora de macros

A veces una simple macro grabada es todo lo que senecesita; en estos casos, incluso, no es necesario mirar elcdigo. Por lo general, la grabacin por s sola no essuficiente, pero s, un punto de inicio en el procesoautomatizado para la solucin de su problema.

Para usar la grabadora de macros como punto deinicio de la solucin1. Grabe las acciones que desea codificar.2. Revise el cdigo y busque las lneas que realizan esas

acciones.3. Elimine el resto del cdigo.4. Modifique el cdigo grabado.5. Agregue variables, estructuras de control y otro cdigo

que la grabadora de macros no pueda grabar.


15


16/31

OBJETO RANGE, SELECCIN CELDAS

SELECCIONAR UNA CELDARange(B5).select

SELECCIONAR VARIAS CELDAS

Range(B7:C20).select

SELECCIONAR CELDAS CONTIGUAS DE UNALISTA(De izquierda a derecha y hacia abajo)Range(Selection, Selection.End(xlToRight)).SelectRange(Selection, Selection.End(xlDown)).Select(De abajo hacia arriba y a la izquierda)Range(Selection, Selection.End(xlUp)).Select

Range(Selection, Selection.End(xlToLeft)).Select


16


17/31


IR AL FINAL DE CELDAS SELECCIONADAS ODETENERSE EN UNA CELDA CON DATOS

Selection.End(xlDown).Select

IR A UNA CELDA DEBAJO DE LA ACTIVA

ActiveCell.Offset(1, 0). Select

ESCRIBIR EN UNA CELDA Range(A5").Value = "SENATI pni"


17


18/31


ESCRIBIR EN UNA CELDA A UNA DISTANCIARELATIVA A LA CELDA ACTIVA

Range(A5").Select

ActiveCell.Range(A1").Value = "Trujillo"

ActiveCell.Range(B2").Value = "La Libertad"


18


19/31


ESCRIBIR EL MISMO DATO EN UN RANGODE CELDAS

Range("A1:B10").Value="Per"

ESCRIBIR EN LA CELDA TRES ABAJO Y UNAA LA DERECHA DE LA CELDA ACTIVA

ActiveCell.Cells(4,2).Value="Excel"

recuerde que lo anteriores lo mismo que:ActiveCell.Range("B4").Value = "Excel"

y lo mismo que:

ActiveCell.Offset(3, 1).Value = "Excel"


19


20/31


ESCRIBIR UN DATO EN UNA CELDAABSOLUTA UTILIZANDO CELLS(N)

Cells(32769).Value = "Fcil"

Nota: CELLS Cuenta desde la celda A1 como 1, B1 como 2, C1 como 3 .XFD1 como 16384. Es decir de izquierda a derecha a partir de la celda A1 sinimportar la celda activa.

ESCRIBIR EN UNA CELDA RELATIVA A UN

RANGO SELECCIONADORange("A1:D5").Select

Selection.Interior.Color = RGB(200, 200, 200)

Selection.Cells(6) = "123"

Cells(1).Select


20


21/31


21

Cells(1) Cells(2)

Cells(5) Cells(6)


22/31

TRATAMIENTO DE HOJAS

NOMBRAR HOJASheets(1).Name = BaseDatosSheets(2).Name = ConsolidadosSheets(3).Name = Grficos

AGREGAR HOJA(siempre se agrega antes de lahoja activa en Excel 2010)

Sheets.Add

ACTIVAR UNA HOJASheets(n).Activate o Sheets(n).SelectSheets("Peru").Select 'activa Peru si existe

29/03/2011

22Ing. Enrique Alfaro


23/31


MOVER LA HOJA ACTIVA AL FINALActiveSheet.Move after:=Sheets(Sheets.Count)

Nota: Sheets.Countdevuelve un valor entero que esigual al total de hojas.

NOMBRAR LAPENULTIMA HOJA

Sheets(Sheets.Count-1).Name=etiqueta

ELIMINAR LA ULTIMA HOJA

Sheets(Sheets.Count).Delete

29/03/2011

23Ing. Enrique Alfaro


24/31


29/03/2011

Pregunta Que realiza la siguiente lnea decdigo?:

MsgBox (La ultima hoja se llama" & chr(13) _&Sheets(Sheets.Count).Name)

COPIAR UNA HOJA EN EL LIBRO ACTIVO

Ejemplo: Reemplazar la Hoja1 con el nombre de la

hoja a ser copiada (Uso de After o Before)'Duplicar la hoja2 a la derecha

Sheets(2).Copy After:=Sheets(2)

Sheets(2).Name=Copia

Ing. Enrique Alfaro

24


25/31


COPIA LA HOJA ACTIVA ANTES DE LA HOJA1ActiveSheet.Copy Before:=Sheets(1)

COPIA LAPRIMERA HOJA AL FINAL

Sheets(1).Copy After:=Sheets(Sheets.Count)

COPIA LAPRIMERA HOJA DE UN LIBRO

ABIERTO A OTRO LIBRO ABIERTOWorkbooks("Libro1.xlsm").Sheets(1).Copy _

After:=Workbooks("Libro2.xlsx").Sheets(1)


25


26/31


27/31

APLICAR BORDES A UNA LISTA COMPLETA

Ojo: Partir ubicndose en la celda superior izquierda de la lista

Sub Seleccionarlista() Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Call aplicarbordes

End Sub______________________________________________ Sub aplicarbordes() Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous Selection.Borders(xlEdgeTop).LineStyle = xlContinuous Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous

Selection.Borders(xlEdgeRight).LineStyle = xlContinuous Selection.Borders(xlInsideVertical).LineStyle = xlContinuous Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous 'Selection.Borders(xlDiagonalUp).LineStyle = xlNone 'Selection.Borders(xlDiagonalDown).LineStyle = xlNone ActiveCell.Offset(0, -1).Range("B1").Select

End Sub


27


28/31

Ejercicio propuesto

Crear una lista de 12 Celdas En la hoja 3 de un nuevo libro

La celda esquina superior izquierda es B4

La lista tendr 4 columnas

Los titulos sern: "Items","Enero","Febrero" y "Marzo"

Los ttulos tendran relleno Celeste, letras azules

Toda la lista tendr borde delgado de color Rojo.

La 1ra columna de Items tendr los datos: "Trujillo" y "Vir"

Repetir el dato "100" en las otras celdas de la lista.


28


29/31


30/31

Un posible ejemplo de solucin del ejercicio (2/2)

Sub aplicarformato()Seleccionar rango a la derecha (Ctrl+Shift+Dcha.)

Range(Selection, Selection.End(xlToRight)).Select

Seleccionar rango abajo (Ctrl+Shift+ABAJO.)

Range(Selection, Selection.End(xlDown)).SelectAplicar borde y color simple a todas las celdas seleccionadas

Selection.Borders.LineStyle = xlContinuous

Selection.Borders.Color = RGB(255, 0, 0)EndSub


30


31/31

Ahorrando cdigo con With End With

Sub ej2() 'a partir de la activacin de la celda superior

izquierda Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select 'Aplicar bordes a todas las celdas interior y

exterior With Selection.Borders

.LineStyle = xlDash .Weight = xlMedium .Color = RGB(255, 50, 10) EndWith

EndSub


31

Documents

01 MacrosVBAExcelAvanzado_EAA de 11