Curso de Microsoft Excel - chconsultores.netchconsultores.net/descargas/XLVBA/CH_XLVBA_PPT.pdf · •No pueden ser palabras clave reservadas de Visual Basic. •Mnemotécnicos, se

Lic. Genaro Chiu [email protected]

MicrosoftExcel y VBA

2Curso de Microsoft Excel y VBAwww.chconsultores.net

PrimeraParte

Excel básico(8hrs.)

“Un viaje de mil millas comienza con un primer paso”


XLVBA: Primera parte

Agenda:1. Introducción al interfaz

de Excel.2. Tipos de datos. 3. Manipulación de libros,

hojas y celdas.4. Operaciones y reglas de

precedencia.5. Funciones básicas.6. Herramientas básicas.7. Elaboración de gráficos.8. Caso práctico.


XLVBA: Introducción al interfaz

Hoja activa (Hojas)

Desplazador del libro

Barra de estado

Desplazador horizontal

Desplazador vertical

Celda activa (Celdas)

Columna

Fila

Barra de formato

Menú

Barra estándar

Barra de fórmulas

AyudaNombre


XLVBA: Tipos de datos

>> Tools - Format Cells• Número• Moneda• Porcentaje• Contabilidad• Fracción• Científica• Fecha y hora • Texto• Bolean


XLVBA: Manipulación

Teclas (Key)Operación (Operation)

Mover (Move)

Seleccionar (Select)

<shft><arrows>

<shft> <ctrl>

Una celda (Single Cell)

Select & Drag

Cortar y Pegar (Cut & Paste)

Rangos Discontinuos

(Separate Ranges)

Un Rango (Single Range)

Celdas discontinuas

[drag] arrastrar o dragar (drag)

<arrows>

(Separate Cells)

<ctrl x> cortar (cut)

<ctrl v> pegar (paste)

<ctrl>


XLVBA: Manipulación

Teclas (Key)Operación (Operation)

Autofill

Copiar (Copy)

Cross Drag <ctrl c> <ctrl v> [drag from cross]

[drag]

<ctrl>

<shft>

Selección de Hojas (Sheet

Selection)

Toggle <ctrl><pg up> <ctrl><pg down>

Continuous

Separate

Cross Drag


XLVBA: Agrupación de la información

Según el tipo de información las hojas de cálculo pueden agrupar:• Bases de datos:

Input SheetsWeb QueriesDatabase Queries

• ProcesamientoFunctionsValues by VBA

• Reporte• Gráficas


XLVBA: Caso práctico

Elabore el estado de resultados de MSFT; presentando la información por trimestre, de forma anual y trimestre promedio.

Elabore y analice la evolución por trimestre del costo de ventas, gasto operativo, costo de capital, otros gastos y su impacto en las utilidades netas (gráfica)



30-Sep-02 31-Dec-02 31-Mar-03 30-Jun-03Total Revenue 7,746,000.00$ 8,541,000.00$ 7,835,000.00$ 8,065,000.00$ Cost of Revenue 1,199,000.00$ 2,034,000.00$ 1,215,000.00$ 1,238,000.00$ Gross Profit 6,547,000.00$ 6,507,000.00$ 6,620,000.00$ 6,827,000.00$

Operating ExpensesResearch Development 1,094,000.00$ 1,118,000.00$ 1,111,000.00$ 1,336,000.00$ Selling General and Administrative 1,403,000.00$ 2,130,000.00$ 1,794,000.00$ 3,298,000.00$ Non Recurring -$ -$ -$ -$ Others -$ -$ -$ -$ Total Operating Expenses 2,497,000.00$ 3,248,000.00$ 2,905,000.00$ 4,634,000.00$

Operating Income or Loss 4,050,000.00$ 3,259,000.00$ 3,715,000.00$ 2,193,000.00$ Income from Continuing OperationsTotal Other Income/Expenses Net 19,000.00$ 363,000.00$ 453,000.00$ 674,000.00$ Earnings Before Interest And Taxes 4,069,000.00$ 3,622,000.00$ 4,168,000.00$ 2,867,000.00$ Interest Expense -$ -$ -$ -$ Income Before Tax 4,069,000.00$ 3,622,000.00$ 4,168,000.00$ 2,867,000.00$ Income Tax Expense 1,343,000.00$ 1,070,000.00$ 1,374,000.00$ 946,000.00$ Minority Interest -$ -$ -$ -$ Net Income From Continuing Ops 2,726,000.00$ 2,552,000.00$ 2,794,000.00$ 1,921,000.00$ Non-recurring EventsDiscontinued Operations -$ -$ -$ -$ Extraordinary Items -$ -$ -$ -$ Effect Of Accounting Changes -$ -$ -$ -$ Other Items -$ -$ -$ -$

Net Income 2,726,000.00$ 2,552,000.00$ 2,794,000.00$ 1,921,000.00$

Preferred Stock And Other Adjustments -$ -$ -$ -$ Net Income Applicable To Common Shares 2,726,000.00$ 2,552,000.00$ 2,794,000.00$ 1,921,000.00$

Quarter



30-Sep-02 31-Dec-02 31-Mar-03 30-Jun-0316,287,000.00$ 8,143,500.00$ 100.00% 100.00% 100.00% 100.00%3,233,000.00$ 1,616,500.00$ 15.48% 23.81% 15.51% 15.35%

13,054,000.00$ 6,527,000.00$ 84.52% 76.19% 84.49% 84.65%

2,212,000.00$ 1,106,000.00$ 14.12% 13.09% 14.18% 16.57%3,533,000.00$ 1,766,500.00$ 18.11% 24.94% 22.90% 40.89%

-$ -$ 0.00% 0.00% 0.00% 0.00%-$ -$ 0.00% 0.00% 0.00% 0.00%

5,745,000.00$ 2,872,500.00$ 32.24% 38.03% 37.08% 57.46%0.00% 0.00% 0.00% 0.00%

7,309,000.00$ 3,654,500.00$ 52.29% 38.16% 47.42% 27.19%

382,000.00$ 191,000.00$ 0.25% 4.25% 5.78% 8.36%7,691,000.00$ 3,845,500.00$ 52.53% 42.41% 53.20% 35.55%

-$ -$ 0.00% 0.00% 0.00% 0.00%7,691,000.00$ 3,845,500.00$ 52.53% 42.41% 53.20% 35.55%2,413,000.00$ 1,206,500.00$ 17.34% 12.53% 17.54% 11.73%

0.00% 0.00% 0.00% 0.00%5,278,000.00$ 2,639,000.00$ 35.19% 29.88% 35.66% 23.82%

-$ -$ 0.00% 0.00% 0.00% 0.00%-$ -$ 0.00% 0.00% 0.00% 0.00%-$ -$ 0.00% 0.00% 0.00% 0.00%-$ -$ 0.00% 0.00% 0.00% 0.00%

5,278,000.00$ 2,639,000.00$ 35.19% 29.88% 35.66% 23.82%

-$ -$ 0.00% 0.00% 0.00% 0.00%5,278,000.00$ 2,639,000.00$ 35.19% 29.88% 35.66% 23.82%

% from TR1 Year Q Average

Total RevenueCost of RevenueGross Profit

Operating ExpensesResearch DevelopmentSelling General and AdministrativeNon RecurringOthersTotal Operating Expenses

Operating Income or LossIncome from Continuing OperationsTotal Other Income/Expenses NetEarnings Before Interest And TaxesInterest ExpenseIncome Before TaxIncome Tax ExpenseMinority InterestNet Income From Continuing OpsNon-recurring EventsDiscontinued OperationsExtraordinary ItemsEffect Of Accounting ChangesOther Items

Net IncomePreferred Stock And Other AdjustmentsNet Income Applicable To Common Shares



30-Sep-02 31-Dec-02 31-Mar-03 30-Jun-03Cost of Revenue 15.48% 23.81% 15.51% 15.35%Operating Expenses 32.24% 38.03% 37.08% 57.46%Interests and Taxes 17.09% 8.28% 11.75% 3.37%Other Items 0.00% 0.00% 0.00% 0.00%Net Income 35.19% 29.88% 35.66% 23.82%Total Revenue 100.00% 100.00% 100.00% 100.00%

% from TR



Percentage from Total Revenues

15.48% 23.81% 15.51% 15.35%

32.24%

38.03%

37.08% 57.46%

17.09%

8.28%

11.75%

3.37%

0.00%

0.00%

0.00%

0.00%

35.19% 29.88% 35.66% 23.82%

0%

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

30-Sep-02 31-Dec-02 31-Mar-03 30-Jun-03

Quarter

% fr

om T

R

Cost of Revenue Operating Expenses Interests and Taxes Other Items Net Income


SegundaParte

Excel avanzado(8hrs.)

“El éxito es pasar de fracaso en fracaso sin perder la motivación” –Winston Churchill-


XLVBA: Segunda parte

Agenda:1. Funciones avanzadas:

– Funciones de cadenas de texto.– Funciones financieras.– Funciones de búsqueda.– Funciones matriciales.– Funciones mixtas.

2. Tablas dinámicas y gráficos dinámicos.

3. Herramientas avanzadas.– Herramientas de análisis.– Formato condicional.– Buscar objetivo.– Solver.

4. Interfaz gráfico.– Botones de comando.– Botones de selección.– Cajas de texto.– Cajas de listado.– Barras de incremento.

5. Caso práctico.


TerceraParte

VBA básico(8hrs.)


XLVBA: Tercera parte

Agenda:1. Introducción al interfaz de

Visual Basic for Applications.2. Grabadora de Macros.3. Introducción a la

programación orientada a objetos y eventos.

4. Objetos, métodos y propiedades básicos.

5. Identificadores, constantes y variables.

6. Tipos de datos y operadores.7. Estructuras de control básicas.8. Controles estándar básicos.9. Caso práctico.


XLVBA: Grabadora de macros

>Ver>Barra de herramientas

>Visual Basic

Fijar el nivel de seguridad en Medio


XLVBA: Introducción al interfaz de VBA

Ventana de Propiedades

Ventana de Proyectos

Área de Trabajo

Control de ejecuciónModo de diseño

Ventana de Propiedades

Ventana de Proyectos

Visor de objetos

Barra de herramientas

Menú


XLVBA: Introducción a la programación

-¿Qué es el flujo?

-Programación orientada a objetos y eventos


XLVBA: Objetos, métodos y propiedades

Objectsworkbooks, sheets, range, cells, etc.Properties

name, value, category, borders, etc.Methods

add, copy, cut, paste, insert, open, save, new, close, etc.

Sintaxisobject.properties [arguments]object.methods [arguments]


XLVBA: Objetos

Workbooksworkbooks(index).properties or methods

Sheetssheets(index).properties or methods

Rangerange(range as string).properties or methodsrange(cells1,cells2).properties or methods

Cellscells(row_index,column_index).propertiesor methods


XLVBA: Propiedades y métodos

Propiedades

-Son atributos que tienen los objetos.

-Todas las propiedades tienen un valor que

determina el estado del objeto.

-Se determinan sus valores en tiempo de

diseño para el inicio del programa.

-Se pueden modificar con código en tiempo de

ejecución.



Cells cells(row_index,column_index).-properties:

value = numbervalue = stringvalue = formulavalue = formula as string

-methods:select

Rangerange (string as range).range (cells(row_idx, col_idx) , cells(row_idx, col_idx)).-methods:

select



Sheets

sheets(index).name = string

Workbooks

workbooks(index).-methods:

open(filename as string)

close

activeworkbook.saveas


XLVBA: Métodos

Manejo de archivos

New, Open, Save,

Close

Inserción de hojas

Sheets.Add

Manejo del clipboard

Cut, Copy, Paste


XLVBA: Procedimientos y funciones

“¡Divide y vencerás!”

Unidades de código.

Procedimientos:Sintaxis

Sub nombre (parámetros)…

End Sub

Funciones:-Devuelven respuesta

SintaxisFunction nombre (parámetros)

…End function


XLVBA: Identificadores

Identificadores:

Son los nombres con los que se reconoce a cualquier elemento del programa (comúnmente a variables y constantes). Tienen las siguientes características:

•Comienzan con letra.•No tienen espacios, puntos o algunos caracteres especiales.•No pueden ser palabras clave reservadas de Visual Basic.•Mnemotécnicos, se recomienda que no superen los 25 caracteres, aunque su máximo es de 255.


XLVBA: Constantes

Constantes:

Representan un valor que tiende a repetirse varias veces en el código. Se recomienda usar un identificador en mayúsculas. Ejemplo:

Const NUM_DIAS=250

Const MENSAJE_ERROR = _“Programa abortado. Error fatal”


XLVBA: Constantes

Declaración:

Parámetro Descripción

Public Opcional, disponible en todos los módulos

Private Opcional, sólo disponible en la macro.

Nombre Nombre o identificador

Tipo Tipo de dato

Expresión Contiene el valor asignado

Sintaxis:

[Public/Private] Const nombre [as tipo] = expresión

Ejemplo:

Public Const MENSAJE_ERROR as string = “¡Error Crítico!”


XLVBA: Variables

Variables:

Almacenan temporalmente valores durante la ejecución del programa.

Sintaxis:

Dim nombre [as tipo] = expresión

Ejemplo:

Dim Ventas as currency = 10000

Dim Fecha as date = 31/10/79


XLVBA: Tipos de datos

Tipo Bytes Valor

Integer 2 -32,768 a +32,767

Long 4 -2,147,483,648 a +2,147,483,648

Single 4 1E-45 a 3E38

Double 8 5E-324 a 1.8E308

Currency 8 +/- 9E14

String 1 / char 65,400

Byte 1 0 a 255

Boolean 2 True o False

Date 8 1/1/100 a 31/12/9999


XLVBA: Operadores

-Inteligencia artificial y toma

de decisiones.

-Cuatro grupos:

•Aritméticos

•Comparación

•Lógicos

•Concatenación


XLVBA: Operadores aritméticos

Operación Operador

Potencia ^

Igual =

Suma +

Resta -

Multiplicación *

División /

División entera \

Agrupación ( )

Precedencia de los operadores

1.- Potencia

2.- Negativo

3.-Multiplicación y división

4.-División entera

5.-Suma y resta

Se pueden usar los paréntesis para alterar el orden de la precedencia de los operadores


XLVBA: Operadores de comparación

Operador Descripción

= Igualdad

<> Desigualdad

< Menor que

> Mayor que

<= Menor o igual que

>= Mayor o igual que

Comúnmente se usan con

estructuras de control.

Ejemplo:

If number<=maximum then

arguments…

End if

While number<maximum

arguments…

Wend


XLVBA: Operadores lógicos


Not Negación

And Conjunción lógica

Or Disyunción lógica

Xor Exclusión lógica

Eqv Equivalencia lógica

Imp Implicación lógica

Not – Negación

Sintaxis

resultado = not expresión

expresión resultado

verdadero falso

falso verdadero

null null



And – Conjunción lógicaSintaxis

resultado = expresión1 and expresión2

expresión1 expresión2 resultadoverdadero verdadero verdadero

verdadero falso falso

verdadero null null

falso verdadero falso

null falso falso

falso falso falso

falso null falso

null verdadero null

null null null



Or – Disyunción lógicaSintaxis

resultado = expresión1 or expresión2

expresión1 expresión2 resultadoverdadero verdadero verdadero

verdadero falso verdadero

verdadero null verdadero

falso verdadero verdadero

null falso null

falso falso falso

falso null null

null verdadero verdadero

null null null


XLVBA: Operadores de concatenación

ConcatenarConcatenar dos cadenas significa crear una tercera dos cadenas significa crear una tercera cuyo contenido es igual a la suma de las dos cuyo contenido es igual a la suma de las dos primeras.primeras.


+ Datos tipo string

& Cualquier valor

Ejemplos:

Nombre = _

“Genaro ” + “Chiu”

Ventas = 1000 & “Autos”


XLVBA: Estructuras de control

Formas avanzadas de funciones que en lugar de devolver valores conducen el flujo de ejecución de un programa


XLVBA: If-Elseif-Else

Ejecuta sentence si cumplecon la condición o es el caso excluido.Sintaxis:

If condition thensentence

Elseif condition thensentence

Elsesentence

End if


XLVBA: For-Next

true false

Sintaxis

For variable=value1 to value2 step value3sentence

Next variable

Ejemplo

For X = 1 to 10 step 1cells(X,1).value=1

Next X

X>=10 ?

X+1

X=10

1

sntc

X


XLVBA: While-Wend

Sintaxis

While conditionsentence

Wend


XLVBA: Controles estándar

El interfaz para el usuario como lenguaje universal

LabelEtiqueta

TextboxCaja de texto

PointerApuntador

Command ButtonBotón de comando


XLVBA: Form

Nueva Forma (Menu-Insert-UserForm)

Nueva Forma (Standard Bar)

Name: nombre_de_formafrm

Nueva Forma (New Form)

Aparece en la ventana de proyectos y puede ser un archivo por si mismo


XLVBA: Form

Name: tunombrefrmCaption: “tunombre, Curso de Microsoft Excel”BackColor: Yellow(&H00C0FFFF&)


XLVBA: Label

Rótulos de texto para transmitir información al usuario

Name: nombrelbl

Nueva Etiqueta (New Label)

Etiqueta (Label)


XLVBA: Label

Name: BienvenidolblCaption: “¡Bienvenido!”TextAlign: 2-frmTextAlignCenterFont: Tahoma 18


XLVBA: Textbox

Cajas de almacenaje de texto para recibir información al usuario

Name: nombretxt

Nueva Caja de texto (New textbox)

Caja de texto(Textbox)


XLVBA: Textbox

Name: MensajetxtFont: Tahoma 18


XLVBA: Command Button

Botón para recibir una orden de ejecución del usuario

Name: nombrecmd

Nueva Botón de comando (New Command Button)

Botón de comando(Command Button)


XLVBA: Command Button

Name: CambiarcmdFont: Comic Sans 14BackColor: Pale Green(&H00C0FFC0&)

Private Sub CambiarCmd_Click()Bienvenidolbl.Caption = Mensajetxt.Text

End Sub


XLVBA: Bibliografía

-Microsoft Office Excel 2003 Bible. John Walkenbach. JohnWiley & Sons.

-Financial Modeling, SimonBenninga Second Edition. TheMIT Press.

-Advanced modelling in finance using Excel and VBA. Mary Jackson and MikeStaunton. John Wiley & Sons.


XLVBA: Bibliografía

-Excel 2003 PowerProgramming with VBA. JohnWalkenbach. John Wiley & Sons.

-Favorite Excel Tips & Tricks. John Walkenbach. John Wiley& Sons.


Contactos

Lic. Genaro Chiu Lam

Profesor de CátedraDepartamento de Contabilidad y FinanzasDivisión de NegociosTecnológico de Monterrey, Campus Ciudad de México

Cel. (04455) [email protected]://www.chconsultores.net

Lic. Genaro Chiu [email protected]

¡Gracias!

Documents

Curso de Microsoft Excel - chconsultores.netchconsultores.net/descargas/XLVBA/CH_XLVBA_PPT.pdf · •No pueden ser palabras clave reservadas de Visual Basic. •Mnemotécnicos, se