Excel: Macros BásicosVisual Basic para aplicaciones

Estrictamente necesario entender ingles

By Renee Mangino 2008

Objetivos de la sesion: • Saber que en Excel se pueden realizar : Procedimientos: pasos repetidos y automáticosManejo de Objetos: un ítem que puedes controlar con visual

(botones)Funciones: Hacer calculaciones y devolver un valor x (* si hay

tiempo)

Publico: Personas que no tienen conocimientos de programación, pero tienen

la necesidad o curiosidad de mejorar su trabajo:

• Hacerlo mas rápido• Evitar errores de entradas manuales• Maximizar el uso de Excel

¿Donde está el Menu de Macros?

¿Barra de Herramientas?

¿Por que Visual Basic?

• Visual Basic es el lenguaje en el que se apoya Excel para hacer procedimientos por medio de programación.

• Cuando surge una duda pon en

• "Duda" visual basic para buscar códigos que te sirvan

Grabar un Macro

Record Macro: Asigna un nombre y un procedimiento, pon "stop"

Puedes asignar un "shortcut"Puedes editar las líneas de la grabación

Ejercicio: GRABA un Macro que ejecute lo siguiente: "Miprimermacro"

Cambia el nombre a la hoja por "Prueba"Pon tu nombre completo en la Celda "B2"Pon los años que tienes en la celda "B3"Haz un "Scroll down" (Muévete en la hoja con las barritas)En la celda "B4" pon una formula que calcule el numero de días vividosEn la celda "C5" coloca la leyenda "Días vividos"

STOP

Correr un Macro

• Pasos para correr• Selecciona todo lo que escribiste en la hoja

• Bórralo

• En el menú de macros, pon "play"

• Revisa que ejecute lo mismo que hicimos en el ejercido anterior

Agrega Shortcuts (atajos de teclado)

Veamos ¿Que hicimos?

• La carpeta de modulo solo aparece ya que se grabó un macro, si no hay macros grabados no aparece

• Abre la barra de herramientas de EDIT en Visual Basic

• Las letras en verde no son parte de la programación, son comentarios y empiezan con un apostrofe ' o REM (juega con estos dos iconos: )

Lenguaje visual:

• Empezemos a hablar en Visual Basic

Visual Basic Coloquial

Sub Nombre()

End Sub

Mellamo Nombre()Acabé

Todas las instrucciones Visual Basic es mejor decirles que empiezen Y que acaben SIEMPRE

Activar y desactivar

Codigo: "Miprimermacro"

Que.Como (Accion o propiedad)

Range("B2").Select Referencia AbsolutaActivecell.Select Referencia Relativa

El texto siempre va entre comillasPara no ser confunido con Texto

Propiedades: Propiedades:

Cambian valores, formatos, ubican, etc,

• Select: Referencia de ubicación• Value: = "Texto" o = valor• Offset: Desfase (Row,Column)

Absolutas

Range("A2").Select

Range("A2").Value = 5

Range("A3").Select

Relativas

ActiveCell.select

ActiveCell.Value = 5

Activecell.Offset(1,0).Select

Propiedades:• Ejercico:

GRABA un macro de nombre formatos

Escribe en 3 celdas diferentes 3 nombres de superheroes

Rellena una celda de color

Cambia el color del texto a otra

Agrega bordes a la tercera

Selecciona las 3 celdas y centra el texto

STOP

Sub superheroe()''

Range("J4").Select ActiveCell.FormulaR1C1 = "Superman" Range("J5").Select ActiveCell.FormulaR1C1 = "La mujer maravilla" Range("J6").Select ActiveCell.FormulaR1C1 = "Afroman" Range("J4").Select With Selection.Interior .ColorIndex = 39 .Pattern = xlSolid End With Range("J5").Select Selection.Font.ColorIndex = 5 Range("J6").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With.....Range("J4:J6").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With

RevisaLo que es necesario en la macroLo que da valor al macro "Quita la paja" paja

Revision de Codigo:Sub superheroe()

' Escribe 3 superheroes

Range("J4").Select ActiveCell.FormulaR1C1 = "Superman" Range("J5").Select ActiveCell.FormulaR1C1 = "La mujer

maravilla" Range("J6").Select ActiveCell.FormulaR1C1 = "Afroman" Rem Pone color solido a una celda Range("J4").Select With Selection.Interior .ColorIndex = 39 .Pattern = xlSolid End With Rem Cambia color de celda Range("J5").Select Selection.Font.ColorIndex = 5

Rem Agrega bordes (Los bordes usan muchas lineas de codigo x que son 4 lineas 'en un rectangulo mas dos lineas interiores)

Range("J6").Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin End With ' Centra los textos de la seleccion de J4 a J6 Range("J4:J6").Select With Selection .HorizontalAlignment = xlCenter End With

End Sub

Revision de Codigo:

Correr completo: F5

Correr completo: F5

Loop infinito: Esc (una sola vez, basta)

Resumen:Hasta ahora ya aprendí: • Grabar macros, correr macros y editar macros• Identificar donde acaba y donde empieza una macro• Ordenar la macro con comentarios para recordar que está

haciendo• Leer codigo de Visual Basic • Manejar coordenada absolutas y relativas• Saber que es importante y que puedo borrar

Cuadros de dialogo:

Comando Uso Ejemplo

Msgbox

Para dar un aviso:

Puedes tener OK, Abort, Yes & No, etc

MsgBox "Tu mensaje", (boton+simbolo)

MsgBox "Botones OK y Cancel", 1

Msgbox "Tu mensaje", 4+32

Inputbox Para pedir información al usuario como números o Texto y guardar esa información (variable)

Se requiere declarar variable

Dim x as integer

Dim x as integer

__________________________________________

Sub box()

X = InputBox("¿Cuantos escalones hay en tu casa?")

Range("J8").Value = X

Range("J8").Select

End Sub

Comandos Logicos: IF Comando Uso Ejemplo

If condition Then

Statement

Condicion con una alternativa , Si la condicion no es verdadera, no pasa nada

Sub Macro5()

' Condicion con una alternativa

If ActiveCell.Value < 0 Then

MsgBox "Tu mensaje"

End If

End Sub

If condition Then

statement

Else

Condicion con dos alternativas: si la condicion es verdadera se ejecuta un procedimiento, si es falsa se ejecuta otro procedimiento

Sub Macro6()

' Condicion con dos alternativas

If ActiveCell.Value < 0 Then

MsgBox "Tu mensaje"

Else

Msgbox "Tu mensaje altenativo"

End If

End Sub

IF condition Then

statement

ElseIF condition Then

statement

End if

Ejecuta mas alternativas

Cada vez que se llega a un "Elseif" se vuelve a evaluar la condicion

No disponible para este curso

Ejercicio:

Quiero hacer un Gaffette para una expo de accesorios para automoviles: Si el participante es propietario de dos o mas coches, es mas probable que compre mis productos:

Haz un Macro que funcione para hacer estas

Dos variantes de gaffette : Tu escoge los colores(6 columnas x 8 renglones) empieza en C3 y usa referencia absolutas

Participante

Comprador

Participante

Comprador Potencial

Comandos Logicos: Do...Loop

Comando Uso Ejemplo

Do Until

Loop

(Si es Falso , ejectuto)

Sirve para repetir el procedimiento muchas veces hasta que aparezca una condicion que nos hara parar el ciclo de repeticiones

Dim counter as integer

Sub colores()

´hace la instruccion desde el inicio y hasta que la condicion se cumpla

Do Until counter = 50

counter = counter + 1

ActiveCell.Select

ActiveCell.Value = counter

ActiveCell.Select

Selection.Interior.ColorIndex = counter

ActiveCell.Offset(1, 0).Select

Loop

End Sub

333435363738394041424344454647484950

151617181920212223242526272829303132

123456789

1011121314

Comandos Logicos: Do...Loop

Comando Uso Ejemplo

Do While

Loop

(Si es verdadero, ejecuto)

Sirve para repetirse mientras las condiciones se cumplan

Sub Ahora()

´hace la instruccion hasta que se cambie de estado

Range("a2").Select

Do While ActiveCell.Value > 0

ActiveCell.Offset(0, 1).Select

If ActiveCell.Value = "" Then

ActiveCell.Value = Date + Time

ActiveCell.Offset(1, -1).Select

Else

ActiveCell.Offset(1, -1).Select

End If

Loop

End Sub

Cuando corro un Macro: Boton

¿Como hago para que al presionar un Boton (imagen) se corra un Macro?

Al abrir o cerrar el libro: automaticos

• Auto_Open () o Auto_Close() son procedimientos que se ejecutan al abrir o cerrar el libro.

Sub Auto_Open() Sub Auto_Close()

Statements Statements

End Sub End Sub

¿Porque sería bueno correr los macros al abrir o al cerrar el libro?

Ejercicio:

• Haz un macro que al abrirlo te de la bienvenida

Y al cerrarlo se despida de ti. (usa msgbox)

Hola!!!!Adios!!!!

Practicas ¿Que reportes hago?

Ejercicio: Sheets• Haz una base de datos de gastos:

Mes Pagos Concepto TarjetaEnero $2,500 Colegiatura AmexEnero $500 Mantenimiento AmexFebrero $800 Varios AmexMarzo $2,000 Mantenimiento BBVAMarzo $6,000 Varios AmexAbril $2,500 Colegiatura BBVAAbril $1,700 Varios BBVAMayo $1,800 Mantenimiento BBVAMayo $2,000 Varios BBVAMayo $2,500 Colegiatura Amex

Sheets• GRABA un macro que haga una tabla dinamica

con Mes en Renglones, Concepto en columnas y Tarjeta como campo de pagina. STOP

Tarjeta (All)

Sum of Pagos ConceptoMes Colegiatura Mantenimiento Varios Grand TotalAbril 2500 1700 4200Enero 2500 500 3000Febrero 800 800Marzo 2000 6000 8000Mayo 2500 1800 2000 6300Grand Total 7500 4300 10500 22300

Sheets

• GRABA un Macro que cambie el nombre a la

Hoja de la tabla pivote por "Pivote"

Stop

Graba un macro que copie los valores de la tabla pivote y los pegue en el renglon "A100" pero como valor

Stop

SheetsGraba un macro que grafique los gastos como

columnas moradasSTOP

*Haz una Macro "General" que llame a todas las macros que grabaste

Graba un macro que al cerrarse el libro borre la Hoja llamada PivoteSTOP

Explication:

Aplicaciones para hojas:

Absolutas :

Sheets("Sheet4").Select

Sheets("Sheet4").Name = "Pivote"

Relativas:

ActiveSheet.Select

ActiveSheet.Name = "Pivote2"

Funciones• Son "Formulas" que con argumentos devuelven un valor. • Hacen trasformaciones de datos a partir de bases lógicas• Se llaman por medio de un macro

Function Name(argument)

Statments

End functionFunction CelciusConversion(F)Celsiusconversion = (5 / 9) * (F - 32)End Function

Sub Fahrenheit_Celsius()F = ActiveCell.ValueActiveCell.Offset(0, 3) = Celsiusconversion(F)End Sub

Llamar a la función:

Ahora apaece en el menu de f(x) funcion Aparece como formula