1

Curso de Excel Avanzado

INTRODUCCIÓN A VBA

J. David Moreno

TRABAJANDO CON RANGOS

J. David Moreno

2

INTRODUCCIÓN A VBA3

Para programar correctamente en VBA y trabajar con Excel es obligatorio aprender atrabajar con Excel es obligatorio aprender a trabajar con rangos (seleccionar, copiar, pegar…)

INTRODUCCIÓN A VBA4

COPIAR UN RANGO Y PEGARLO EN OTROCOPIAR UN RANGO Y PEGARLO EN OTROE E l l i j t dEn Excel es muy normal copiar un conjunto de datos y pegarlos en otro sitio. Vamos a copiar los datos en las celdas A1 a B7 y pegarlos en la celda D1.Ejemplo:

Si no sabe como empezar, puede usar la Grabadora de Macros y examinar el código que genera.

3

INTRODUCCIÓN A VBA5

GRABADORA DE MACROSUsemos la grabadora de macros para ver el códigoUsemos la grabadora de macros para ver el código que genera. Código de la grabadora

PRIMERO hace selección SEGUNDO: CopiaTERCERO: Selecciona destinoCUARTO: PegaQUINTO: anula modo copiar

Resultado de ejecutar la macro

INTRODUCCIÓN A VBA6

CÓDIGO VBA DIRECTOCÓDIGO VBA DIRECTO

Podemos copiar el Rango A1:B7 directamente usando el método Copy. Este permite indicar el destino donde se copiará

Cuidado…En VBA no es necesario SELECCIONAR un rango determinado para trabajar con él.

destino donde se copiará.

4

INTRODUCCIÓN A VBA7

COPIAR UN RANGO DE TAMAÑO VARIABLECOPIAR UN RANGO DE TAMAÑO VARIABLE

En muchos casos queremos copiar un rango de celdas en el que las dimensiones exactas (filas y columnas) se desconocen.

Ejemplo: En el fichero DatosPedidos.xls contiene datos de los clientes de una empresa, pero cada día aparecen nuevos clientes, así el número de filas no es fijo.

Queremos una macro que copie este rango variable y lo pegue en la hoja 1

INTRODUCCIÓN A VBA8

CÓDIGO VBACÓDIGO VBA

Debemos usar la propiedad CurrentRegion – Que devuelve el objeto Range que incluye el bloque de celdas que rodean a la celda en concreto.

5

INTRODUCCIÓN A VBA9

SELECCIONAR EL EXTREMO DE UNA FILA O COLUMNASELECCIONAR EL EXTREMO DE UNA FILA O COLUMNA

En Excel es muy común seleccionar desde la celda actual todas las celdas hasta el final de las columnas o de la filaSe hace con Control+Mayuscula+FlechaEn VBA podemos usar el método EndEnd es un método del objeto RangeEl método End tiene solo un argumento que puede ser:

xlUpxlUpxlDownxlToLeftxlToRight

INTRODUCCIÓN A VBA10

Ejemplo: Usando el ejemplo de los pedidos seleccionar todas las filas hasta el final de losseleccionar todas las filas hasta el final de los datos.

6

INTRODUCCIÓN A VBA11

SELECCIONAR UNA FILA O UNA COLUMNASELECCIONAR UNA FILA O UNA COLUMNA

Utilizamos la propiedad EntireColumn, que devuelve un Rangeque es una columna.Para seleccionar una fila usaremos EntireRow.

Ejemplo: La macro siguiente selecciona la columna de la celda activa.

INTRODUCCIÓN A VBA12

Ejercicio: Usando la propiedad EntireRow cambie a negrita todas las celdas en la fila 4cambie a negrita todas las celdas en la fila 4 del ejemplo de días de la semana.

Si lo hacemos con la grabadora no usa EntireRow sino selección diferenteRows("4:4").SelectSelection.Font.Bold = True

7

TRABAJAR CON FUNCIONES

Silvia Mayoral

INTRODUCCIÓN A VBA14

Recordar que hay 2 tipos de procedimientos: PROCEDIMIENTOS SUB y FUNCIONES.yLos procedimientos de Funciones se pueden usar:

A. Desde otro procedimiento en VBAB. Desde una hoja de Excel, como cualquier otra función.

Su código sería: La diferencia, es que una función siempre DEVUELVE UN VALOR

Function NombreFunción(Arg1,Arg2,…)[Sentencias]

End Function

DEVUELVE UN VALOR

8

INTRODUCCIÓN A VBA15

Podemos escribir directamente el código en VBA, i i i d F ti E d F tiiniciando con Function…End FuctionO darle al menu INSERTAR → Elegir FUNCIÓN

INTRODUCCIÓN A VBA16

Ejemplo: Supongamos que usted tiene que calcular el área de un rectángulo frecuentemente, y desea crearárea de un rectángulo frecuentemente, y desea crear una función que le de directamente este área. Area= Base * Altura

Solución código:

Ahora vaya a Excel y busque en FÓRMULAS DEFINIDAS POR EL USUARIO

9

INTRODUCCIÓN A VBA17

Podemos introducirlo así

O directamente al escribir el nombre de la función AreaRectang ya nos aparece como una función

INTRODUCCIÓN A VBA18

Usar una función dentro de un procedimiento SUB

Podemos ejecutar un procedimiento Función dentro de un procedimiento SUB simplemente llamándola con los argumentos que requiere y como debe dar un valor, es necesario asignarle una variable.

Ejercicio: Cree una macro que a través de InputBox pida al usuario la base y altura, y usando la función AreaRectang calcule el área. Después muestre el resultado con MsgBox.

10

INTRODUCCIÓN A VBA19

Solución:

INTRODUCCIÓN A VBA20

Para insertar funciones que no está grabado su código en ese mismo libro.

Si la función fue grabada en otro libro, puede usarla pero debe indicar antes de la función el nombre del libro donde se grabó.

=NombreLibro.xlsm!NombreFuncion(Arg1,Arg2)

Lo mejor si está en otro lib l A i t tlibro es usar el Asistente para Funciones, y Excel pone toda la ruta directamente

11

INTRODUCCIÓN A VBA21

Podemos introducir la definición o explicación a nuestra función siguiendo estos pasos:

C l F ió VBA1. Cree la Función en VBA2. Seleccione en Excel menú PROGRAMADOR→MACROS

3. Aquí aparecen solo los nombres de macros SUB. Tiene que introducir el nombre de la función→ AreaRectango b e de a u c ó ea ecta g

4. Clic en Opciones e introduzca la explicación

CONTROLES DE EJECUCIÓN

Silvia Mayoral

12

CONTROLES DE EJECUCION23

VBA utiliza muchos términos que se encuentran en muchos otros lenguajes de programación.Estos términos se usan para controlar el flujo de ejecución.

If-ThenFor-NextWith-End-WithSelect CaseDo WhileDo Until

CONTROLES DE EJECUCION24

Constructor If-ThenConstructor If Then

Una de las estructuras más importantes, que da a las aplicaciones la capacidad de tomar decisiones.

If condicion Then intrucciones End If (no necesario sino se salta de línea)

Si la condición es verdadera entonces se ejecuta una serie de instrucciones. Si incluye Else, entonces se ejecutará otras sentencias si la condición no es verdadera.

13

CONTROLES DE EJECUCION25

Constructor If-Then

Ejemplo:

Ejercicio: Construir una Sub que si una celda es negativa le j q greste 5 y te lo diga y en caso de que sea positivo sume 5 y te lo enseñe

26

Solución:

CONTROLES DE EJECUCION

14

CONTROLES DE EJECUCION27

Bucle For-NextBucle For Next

Se utiliza para ejecutar una o más instrucciones varias veces.

For contador To Final Instrucciones Next contador

CONTROLES DE EJECUCION28

Constructor With-End With

Modo abreviado de usar varias propiedades o métodos al mismo objeto.

SIMPLIFICA MUCHO LA VIDA

15

CONTROLES DE EJECUCION29

Constructor Select Case

Modo abreviado de escoger entre varias opciones.

Simplifica el no tener que hacer muchos If anidados

CONTROLES DE EJECUCION30

Ejercicio: Estas preparado para hacer una función que calcule unas comisiones sobre unas ventas (dato inicial), de forma que la comisión cambia:

Ventas < 1.000 8%Entre 1.000 y 10.000 10%Entre 10.000 y 100.000 12%Superiores a 100.000 14%En caso que gane más de 1.000 le de un mensaje de “lo que has ganado tio”

16

31

Solución:

CONTROLES DE EJECUCION

CONTROLES DE EJECUCION32

Bucle Do….Loop Until

Estructura que ejecuta un bloque de sentencias un número indefinido de veces hasta que una condición llegue a ser True.

Do Until condicion …. Sentencias ….LoopDo While Condicion … Sentencias …Loop

Cuidado no se ejecute de manera indefinidaCuidado no se ejecute de manera indefinida

17

Algunas Funciones Interesantes33

Función que Escribe las Formulas que hay en una Hoja en Otra

Algunas Funciones Interesantes34

Función que Habla lo que pone en una celda

Función que indica el usuario