Excel Avanzado

Docente: Ing. Dikxon Luque

 AGENDA

1. Sesión 09 y 10: Fundamentos de programación y Sentencias de

programación con código VBA

• Fundamentos de programación en VBA, objetos, propiedades,

métodos y eventos.
• Operaciones utilizando objetos, propiedades y métodos.
• Operaciones con objeto Rango.
• Operaciones con filas y columnas.
• Operaciones con hoja de cálculo.
• Cuadros de diálogo con inputbox y msgbox.
• Programación con estructura condicional y repetitivas.
SESIÓN 09 Y 10: FUNDAMENTOS
DE PROGRAMACIÓN Y SENTENCIAS
DE PROGRAMACIÓN CON CÓDIGO
VBA
 OBJETOS PRINCIPALES EN EXCEL

Para programar en VBA es importante identificar los objetos o elementos principales del entorno de trabajo en
Excel. Estos principales objetos de Excel están constituidos por Libro, Hojas, Rangos, Celdas, Tablas y otros.
 CARACTERÍSTICAS DE LOS OBJETOS DE EXCEL

La programación en Excel se hace utilizando las características de los objetos y se clasifican

en Propiedades y Métodos

1. PROPIEDADES
Son las características, atributos o aspectos del objeto
Sintaxis:
NombredelObjeto.Propiedad

PROPIEDAD DE LECTURA
Ejemplo:
En la celda A1 mostrará el nombre de la hoja
Range(“A1”)=Sheets(“Hoja1”).Name

En este ejemplo la propiedad Name devuelve el nombre de la hoja y se muestra en la celda A1

 CARACTERÍSTICAS DE LOS OBJETOS DE EXCEL

La programación en Excel se hace utilizando las características de los objetos y se clasifican

en Propiedades, Eventos y Métodos

PROPIEDAD DE ESCRITURA DE VALOR

Utilizado para asignar valores a las características de los objetos

En este ejemplo la propiedad Name recibe un valor, que es el nombre que le estamos dando a la
hoja.
 2. METODOS
Los métodos son acciones, que tienen efecto sobre los objetos.

Objeto + Método

Range(“A1”).Select

Objeto Método

En este ejemplo se ejecuta el método Select, para seleccionar el objeto A1, es decir seleccionar la
celda A1

En este ejemplo se ejecuta el método Clear, para borrar el valor de la celda A1

 FUNDAMENTOS DE PROGRAMACION EN EXCEL

EJEMPLOS DE MÓDULOS, PROPIEDADES Y MÉTODOS

Procedimiento ACTIVIDAD 01
• Crear archivo nuevo de Excel
• Guardarlo como archivo para macros
 USER FORM Y CONTROLES DE FORMULARIO
INSERTAR MÓDULO
Sirve para crear procedimientos o funciones, que tiene
un grupo de instrucciones y que realiza una actividad
concreta.
Procedimiento
• Seleccionar cinta Programador
• Seleccionar Visual Basic
• Seleccionar menú Insertar
• Elegir Módulo

ESTRUCTURA DEL PROCEDIMIENTO

Un procedimiento está compuesto por un grupo de
instrucciones que realizan una tarea especifica
Sintaxis:
Nombre del procedimiento
Sub NombreProcedimiento
Instrucciones Inicio de procedimiento

End Sub Instrucciones

Ejemplo: Fin de procedimiento
 FUNDAMENTOS DE PROGRAMACION EN EXCEL
EJEMPLOS DE PROPIEDADES Y MÉTODOS
Procedimiento
• Crear los procedimientos Ejemplo01,02,03,04 para utilizar propiedades y
métodos
• Ejecutar los procedimientos desde ícono Ejecutar o pulsar F5.
• También puede ejecutar desde Cinta Programador y Macros
Clic para ejecutar código VBA

Clic para activar módulo

 FUNDAMENTOS DE PROGRAMACION EN EXCEL
OBJETO RANGE (RANGO)
Es un objeto que hace referencia a una celda o un conjunto de celdas
Propiedades
Value: Permite asignar un valor al rango especificado
Activate: Hacer activa una celda determinada

Métodos
Clear: Borra el contenido de una celda
Copy: Copia contenido de celda a portapapeles
Select: Permite seleccionar el contenido de un rango

OBJETO ACTIVESHEET
Es un objeto utilizado para hace referencia, a cualquier elemento de la hoja activa.
Método
Paste: Permite pegar contenido de portapapeles

OBJETO SHEETS
Utilizar para hace referencia a una hoja
Sintaxis:
Sheest(“Nombre de hoja”).Método
 FUNDAMENTOS DE PROGRAMACION EN EXCEL

ACTIVIDAD 2
EJEMPLOS PROPIEDADES Y MÉTODOS
Copiar un rango de celdas de la Actividad_2 a la hoja3
Procedimiento
• Crear procedimiento
• Programas el siguiente código
• Ejecutar procedimiento
 FUNDAMENTOS DE PROGRAMACION EN EXCEL
ACTIVIDAD 03
OPERACIONES CON OBJETO RANGO
Utilizado para hace referencia en distintos modos
Procedimiento
• Inserta nuevo modulo
• En propiedad Name cambiar de nombre por OperacionesRango
• Programar los siguientes procedimientos
 FUNDAMENTOS DE PROGRAMACION EN EXCEL
ACTIVIDAD 03
OPERACIONES CON HOJAS
Utilizado para realizar acciones con hojas
Procedimiento
• Programas el siguiente procedimiento

Para Ocultar hoja, asignar nombre a hoja

desde venta de propiedades
 VARIABLES Y SENTENCIAS DE CONTROL
VARIABLES
Una variable es un espacio de memoria que el procedimiento o función de reserva para guardar datos.

Sintaxis:
Dim variable As tipo
Ejemplo1:
Definir la variable nombre para guardar textos
Dim Nombre as String

Ejemplo2: Variable de tipo objeto

'Declaramos una variable de tipo "Hoja de Excel"
'Con Set asignamos una propiedad para hacer referencia
'posteriormente solo a la variable
Dim Wks As Worksheet
Set Wks = ActiveSheet
Dim R As Range 'Declaramos una variable de tipo "Rango"
Set R = Range("A1") 'Establecemos que Range("A1") se llame a partir de ahora R
 TIPOS DE VARIABLES
Tipo de dato Tamaño de almacenamiento Intervalo

Byte 1 byte 0 a 255

Boolean 2 bytes True o False
Integer 2 bytes -32.768 a 32.768
Single 4 bytes 3.402823E38 a -1,401298 E-45 para
valores negativos y desde 1,401298E-45
a 3.402823E38 para valores positivos.

Double 8 bytes 1.79769313486232E308 a -

4,94065645841247E-324 para valores
negativos y de 4,94065645841247E-324
a 1.79769313486232E308 para valores
positivos.

Date 8 bytes 1 enero de 100 a 31 diciembre 9999

String Longitud cadena Desde 1 a 65.400 aprox.

Object 4 bytes Cualquier referencia a tipo Object
 VARIABLES Y SENTENCIAS DE CONTROL

FUNCIÓN INPUTBOX
Utilizado para ingresar datos desde una ventana, y almacenarlos en variables. Todos los datos
ingresados por defecto son de tipo texto.

Sintaxis:
NombreVariable=InputBox(“Mensaje”,”Titulo”)
Mensaje: Es el mensaje que se muestra en la ventana
Titulo: Es el título para la ventana

Nota: Si desea poner mas de una línea usar Chr(13)

CONVERTIR TEXTO A NUMERO

La función Val(Dato String), convierte una cadena de caracteres
a valor numérico. Si la cadena a convertir contiene algún
carácter no numérico devuelve 0.
 OPERACIONES CON INPUTBOX Y SENTENCIAS DE CONTROL
Procedimiento ACTIVIDAD 04
• Insertar nuevo módulo
• En propiedad Name cambiar de nombre por OperacionesControl
FUNDA
• Ejemplo: Elaborar un programa que permite ingresar un nombre por ventana y
MENT
mostrar este valor en la celda A1. Escribir el siguiente código.
OS DE
PROGR
AMACI
ON EN
EXCEL Resultado
 FUNDAMENTOS DE PROGRAMACION EN EXCEL
Utilizando la Actividad_4 aplicar:
ESTRUCTURA CONDICIONAL SI SIMPLE
Ejecuta instrucciones dependiendo si la condición es verdadera o falsa
1. IF SIMPLE: Ejecuta instrucciones si la condición es verdadera
Sintaxis
If condición Then
Instrucciones
End If
Ejemplo: Elaborar un programa que permita ingresar nombre, cantidad y precio por ventana, pedir un descuento si el precio es mayor a
500 soles, mostrar el descuento y calcular el nuevo precio

Escribir código en procedimiento

Condicional() para ingresar datos y
realizar operaciones

Digitar títulos
 FUNDAMENTOS DE PROGRAMACION EN EXCEL

Utilizando la Actividad_4 aplicar:

ESTRUCTURA CONDICIONAL SI SIMPLE
Mejorar el ingreso de datos para que salte de fila automáticamente

Detectar la última fila ocupada

Variable para almacenar
última fila ocupada de la
columna 1

Referencia las filas de la Desplaza a la última fila

columna 1 ocupada
 FUNDAMENTOS DE PROGRAMACION EN EXCEL
Utilizando la Actividad_4 aplicar:
2. IF THEN ELSE: Utilizar para ejecutar
instrucciones en caso verdadero o falso
Sintaxis
If condición Then
Instrucciones
Else
Instrucciones
End If
Ejemplo: Elaborar un programa que permita
ingresar nombre, cantidad y precio por
ventana, si el precio es mayor a 500, calcular
un descuento del 10%, caso contrario 5%,
mostrar el % de descuento, y el nuevo precio
descontado.

PREPARAR FILA DE TÍTULOS EN ACTIVIDAD_4,

A PARTIR DE CELDA I1
 FUNDAMENTOS DE PROGRAMACION EN EXCEL

3. SELECT CASE: Utilizado para evaluar las

diferentes posibilidades que puede asumir una
variable.
Sintaxis
Select case Variable
Case valor1: Ejemplo: Elaborar un programa que permite ingresar por
instrucciones ventana Nombre, Categoría y Precio de producto, calcular la
Case valor2: utilidad y precio de venta de acuerdo a la categoría.
instrucciones Categoría % utilidad
Case valor3: Verduras 35%
instrucciones Carnes 40%
Case valorN: Aves 20%
instrucciones Limpieza 30%
Case Else Otros 10%
Instrucciones
End Select
 FUNDAMENTOS DE PROGRAMACION EN EXCEL
ACTIVIDAD 05 • Codificar el procedimiento
OPERACIONES CON SELECT CASE
Procedimiento
• Inserta nuevo modulo
• En propiedad Name cambiar de nombre por
SeleccionMultiple
 CUADRO DE MENSAJES
FUNCIÓN MSGBOX()
Utilizado para enviar mensajes en un cuadro de dialogo, devolviendo un valor de tipo entero en función al botón
pulsado.
Sintaxis:
MsgBox(Mensaje, Botones, Titulo)
Mensaje: Es el mensaje que se muestra dentro del cuadro de diálogo
Botones: Es opcional, indica los botones que se van a mostrar
Titulo: Texto que se muestra en la barra de títulos

TABLA DE BOTONES TABLA DE VALORES QUE DEVUELVE

MSGBOX
 CUADRO DE MENSAJES
ACTIVIDAD_5
Resultado
Enviar mensaje de conformidad con registro ingresado
Procedimiento
• Seleccionar el procedimiento creado anteriormente Seleccion01
• Agregar al final el siguiente código

Agregar esta línea procedimiento

Seleccion01

Definir variable respuesta

Dim Respuesta as Integer
 ESTRUCTURA REPETITIVA

DEFINICIÓN:
Son utilizadas para ejecutar más de una vez un grupo de instrucciones.

1. FOR/NEXT
Utilizado para repetir un grupo de instrucciones un número definido de veces

Sintaxis:
For variable=Valor inicial To valor final Step incremento
instrucciones
Next

Ejemplo:
Mostrar la suma de los 10 primeros números enteros
For x=1 to 10
suma=suma+x
Next
Range(“A1”).Value=suma
 ESTRUCTURA REPETITIVA
ACTIVIDAD 06
Ejemplo:
Ingresar por medio de ventana 5 importes de
una factura y mostrar en una celda este monto
acumulado
Procedimiento
• Inserta nuevo modulo
• En propiedad Name cambiar de nombre por
Repetitivas
 ESTRUCTURA REPETITIVA
ACTIVIDAD 06
Utilizando el mismo ejemplo anterior, registrar los importes en las celdas de la columna 1.
Crear procedimiento reperitivas02()

Propiedad Cells
Sirve para referenciar una celda o un rango de celdas según coordenadas de fila y
columna.
 2. DO WHILE/LOOP
Utilizando para repetir un grupo de instrucciones, controlados por una condición y un
contador.

Sintaxis:
ESTRU
Do While (Condiciòn)
Instrucciones
CTURA
Loop
REPETI
Ejemplo:
TIVA
Mostrar la suma de los 10 primeros números enteros
Do while x<10
x=x+1
suma=suma+x
Loop
Range(“A1”).Value=suma
ACTIVIDAD 07
Ejemplo:
Ingresar por medio de ventana 5 ventas y mostrar el acumulado de estas ventas
Procedimiento
• Inserta nuevo modulo
• En propiedad Name cambiar de nombre por Bucles.
ESTRU
• Crear procedimiento llamado Bucle01
CTURA
REPETI
TIVA
 ESTRUCTURA REPETITIVA

ACTIVIDAD 07
Utilizando el mismo ejemplo anterior, registrar las ventas en las celdas de la columna

1.Crear procedimiento Bucle02()

 ESTRUCTURA REPETITIVA
ACTIVIDAD 07
Ingresar por medio de ventana 5 importes de una factura, registrar los importes en cada columna y preguntar si desea
continuar ingresando más importes Si o No. De ser SI debe continuar y si es No debe terminar el proceso.
 ESTRUCTURA REPETITIVA
ACTIVIDAD 08
Casos prácticos

CASO 01: Usar IF/Then

Elaborar un programa que ingrese en modo de ventana nombre del Cliente, Sub total de la compra, Tipo de documento.
Si el tipo de documento es Factura Calcular el IGV. Mostrar el Monto final sumando el sub total+IGV, caso contrario solo
mostrar el Sub total sin IGV.

ACTIVIDAD 09
CASO 2: Usar Select Case
Elaborar un programa que permita ingresar Nombre, Departamento, calcular el sueldo liquido de acuerdo a lo
siguiente:.
Departamento Sueldo % Dscto
Sistemas 3500 10%
Administración 3800 15%
Contabilidad 3200 8%
Otros 2800 5%
Mostrar el sueldo de acuerdo al departamento, calcular el descuento y mostrar sueldo líquido.
GRACIAS