Excel Avanzado – Ejemplos de VBA y Macros

En Excel-Avanzado.com, podemos encontrar una gran variedad de recursos que podemos emplear para el aprendizaje de Macros, o VBA.

Uno de los aspectos mas importante en el aprendizaje de Excel Avanzado, es el manejo de funciones, tanto las correspondientes al propio lenguaje, VBA o macros, como a las funciones definidas por el usuario.

En el caso de las funciones de VBA, estas nos permitirán manipular los datos de forma efectiva, en esta relación incluimos a las más importantes con ejemplos de uso de las mismas, revisa una lista de funciones disponibles en nuestro blog desde el siguiente enlace:

Excel Avanzado: Lista de funciones en VBA

Las Funciones Definidas por el Usuario (UDF), son las que nos permiten ampliar la relación de funciones que vienen predefinidas en Excel, en esta lista se encuentran algunos ejemplos prácticos de como podemos aprovecharlas.

Excel Avanzado: Funciones Definidas por el Usuario (UDF) 

Una de las primeras cosas que se debe aprender como parte de un curso de Excel Avanzado es el  lenguaje de programación,  VBA cuenta con algunas estructuras disponibles, en el siguiente enlace encuentra algunos ejemplos de la sintaxis de estas estructuras.

Excel Avanzado: Estructuras de control en VBA

Comprender un nuevo lenguaje suele a veces ser complicado,  es posible empezar con algunos temas, y  si bien VBA es un lenguaje sencillo de aprender, alguno de sus aspectos pueden ser fuente de duda, por lo que siempre es conveniente revisar mediante ejemplos como se pueden emplear las definiciones necesarias.

Excel Avanzado: Temas Relevantes

Ejemplos de sintaxis VBA no solo son importantes sino relevantes para comprender el uso que les podemos dar, cuanto mayor sea el número de instrucciones que posee un lenguaje de programación, y VBA no es la excepción, mayor será la posibilidad de que lo podamos aprovechar al máximo, existe y muy amplia variedad de instrucciones que podemos utilizar, revise tantas como les sea posible, los siguientes ejemplos le mostrarán no solo la sintaxis sino algunas ideas de como emplearlas:

1

Excel Avanzado: Ejemplos de Sintaxis de VBA

Los gráficos son un elemento esencial para cualquier presentación de impacto, sin embargo el elaborar de forma masiva los gráficos puede tornarse una tarea complicada, los siguientes ejemplos nos pueden dar una idea de como lograr gráficos con VBA, al igual que en la mayoría de los ejemplos, se recomienda comenzar realizado lo ejemplos mas sencillos para luego ir revisando algunos mas complejos o añadiendo nuevos detalles a la macros que este utilizando.

Excel Avanzado: Gráficos con Macros

La creación de tablas dinámicas son un elemento importante para lograr comprender los datos, los siguientes ejemplos nos permiten hacer un uso avanzado de estas, logrando que estas puedan generarse de forma automática.

Excel Avanzado: Ejemplos de Tablas Dinámicas con Macros

Desde el editor de VBA, también se puede hacer uso de formularios, estos nos pueden permitir tener un manejo avanzado del manejo de los datos.

Excel Avanzado: Ejemplos de Userforms 

Muchos de los Eventos en VBA no suelen ser aprovechados, sin embargo algunos de ellos nos podrían ser de utilidad, los eventos nos dan un mayor control sobre lo todo aquello que suceda en nuestro Excel, en particular suele ser de mayor utilidad cuando se emplean formularios, descubra en las siguientes macros  los que puede lograr con el manejo de eventos en VBA.

Ejemplos de Eventos en VBA

No pueden faltar los trucos o tips de Excel Avanzado, aquellos consejos que nos pueden ser de utilidad.

Trucos de Excel Avanzado

La integración con Office, puede ser un tema complicado,  si esta comenzando a aprender le recomendamos que antes de ver los siguientes ejemplos de integración con Office revise previamente los ejemplos de sintaxis y/o las estructuras de control de forma que al utilizar los siguientes ejemplos de Macros en Office con fluidez.

Uso de Macros (VBA) en Office

Requiere de mas ejemplos de Macros, por favor registre un comentario en el que nos indique que ejemplo le gustaría que incluyamos.

2

Funciones en VBAExisten diversas funciones en VBA  o Macros que podrían ser de mucha ayuda para las diversas tareas en las que nos veamos involucrados en resolver, en esta relación tenemos un acercamiento a las funciones en VBA mas usuales que podríamos llegar a emplear.

A la fecha en la web no encontramos una relación lo suficientemente exhaustiva de las funciones en VBA disponibles así que desde el blog hemos comenzado, paso a paso, a establecer nuestra propia relación de funciones, prestando especial interés a los ejemplos de uso de cada función que desarrollemos,  si desean que agreguemos alguna en particular o han encontrado alguna observación sobre algunas de las funciones descritas, por favor avísennos a info@exel-avanzado.com a fin de realizar las revisiones y adecuaciones que sean necesarias.

Debe tenerse en cuenta que estas funciones en VBA pueden emplearse desde cualquier parte del proyecto, es decir ya sea desde los módulos, hojas, ThisWorkbook, y formularios.

Algunas de las funciones en VBA revisadas: 

1. Evaluacion del dia con la Función Day2. Función Month en VBA3. WORKSHEETFUNCTION.MAX para el promedio de notas4. Función Right en VBA5. Función WorksheetFunction.Average6. Uso de WorksheetFunction.CountBlank7. Uso de WorksheetFunction.CountA8. Función CINT9. Función Mid para calificación crediticia10.Función Abs11.Función Now en VBA12.Función Formatpercent13.Función DateAdd (Teoría Nagele)14.Función FileLen en VBA15.Función IsEmpty en VBA16.Función Len en VBA17.Función Left18.Función Space en BVA19.Uso de WorksheetFunction.RandBetween20.Función Rnd21.Funcion IsNumeric22.Función FormatCurrency en VBA23.Función IsNull en VBA24.Función Cbyte en VBA25.Función StrConv26.Función Replace27.Función Val en VBA28.Función CLng en VBA

3

29.Uso de WorksheetFunction.Vlookup30.Función Cdec31.Función "Choose" en VBA32.Función CStr33.Función CurDir34.Función CVAR en VBA35.Función InputBox en VBA36.Función CCur en VBA37.Función DateSerial en VBA38.Función WeekdayName39.Función LCase con VBA40.Función ROUND41.Función Sgn42.Función Date43.Función INT44.Función DateDiff45.Función CDate con VBA46.Función Fix con VBA47.Función LTrim48.Función CHR49.Función DateSerial50.Funcion Cbool51.Función TimeSerial52.Funcion Ucase53.Función InStr54.Función Format55.Función CDbl56.Función IsDate57.Función Asc58.Función InStrRev

4

Funciones definidas por el usuario (UDF)Las funciones definidas por el usuario, conocidas también como UDF por sus siglas en Inglés (User Defined Functions)  permiten, por medio del uso de macros o código vba, el contar con funciones similares a las que existen de forma predefinida en excel.

La variedad de temas que se pueden abordar por medio de estas funciones es numerosa, si se esta comenzando a aprender el uso de este tipo de funciones convendría comenzar realizando rutinas pequeñas que favorezcan el aprendizaje del lenguaje,  a  continuación se presenta una relación de ejemplos de lo que se pueden realizar por medio de las funciones definidas por el usuario.

Cabe recordar que estas funciones pueden ser empleadas desde cualquier parte del proyecto, ya sean las hojas, ThisWorbook, módulos, y formularios, y si son empleadas desde las hojas la invocación se realiza de la misma forma que se procede para las funciones ya existentes como BuscarV, Concatenar, etc.

Revise los siguientes ejemplos de UDF (funciones definidas por el usuario), en todos ellos encontrará muy diversos ejemplos de como aplicar estas funciones.

Si alguna de estas funciones definidas por el usuario posee un error o contiene una mejora que usted considera conveniente o necesaria, por favor registre un comentario para que procedamos a revisar la publicación.

1. UDF Conversiones de Temperatura2. UDF del cálculo de pronostico usando el método estacional multiplicativo3. UDF Funcion de Indice Masa Corporal4. El Campo Magnético de un alambre según la Ley de Biot-Savart5. UDF aplicado a Cálculo de la CTS6. UDF Aplicado al cálculo del Índice de Ruffier7. Función Porcuantotevas8. UDF para calcular el costo de la energía consumida por un aparato eléctrico.9. Función ValorCajaCero10.UDF aplicado al Cálculo de la Rentabilidad Real11.UDF aplicado a Cálculo de Ahorro en un Cuadro Comparativo de Precios12.UDF aplicado a obtener la Variación de Productividad de una empresa13.UDF aplicado al calculo de rentabilidad de un portafolio, modelo CAPM14.UDF aplicado al cálculo del Indice de Masa Corporal15.UDF Aplicado a la suma de decibeles16.UDF Aplicado a cálculo de Fracciones de Colesterol y Riesgo coronario17.UDF Suma de valores en negrita18.UDF para calcular el Descuento de AFP19.UDF aplicado a los rangos de Retorno Esperado de una acción20.UDF: Cantidad de agua requerida, según peso, calorías consumidas y horas

de actividad física.21.UDF aplicado al cálculo del lote económico de compra (EOQ)

5

22.UDF: función del Indice de Riesgo Ambiental IRA23.UDF Función Tipo de Cambio Forward Venta (fwventa)24.Función CompensacLCE25.Velocidad Final y Altura en MRUV en caida libre26.UDF-cuota leasing27.USD - Función del Ratio BEP28.Extraer números VBA - Ejemplo con Tipo de cambio29.Cambiar de categoría de una UDF30. Ideas para concatenar celdas con VBA o Macros31.UDF aplicado a Cálculo de las Notas Curso Excel32.UDF aplicado a Calculo de la CTS33.UDF Promedio de Notas del curso de Decisiones de Inversión34.UDF - Estado de Resultados para cuantificar la Utilidad Empresarial35.Cálculo Nitrógeno Amoniacal36.UDF Promedio de ventas37.Distancia entre los puntos38.UDF - Tipo de Obesidad según el IMC39.UDF Función Utilidad Retail40.UDF: determinación del tamaño de una muestra estadística41.UDF - Cálculo de notas del laboratorio de Sistemas Integrados de Producción42.Formulas para sacar promedio total y nota requerida43.Cantidad Económica de Pedido - modelo comercial de inventarios44.Función sumtotaldeQ45.Concatenar el contenido de un rango de celdas (mail recordatorio)46.Función Suma de Dígitos47.UDF – Presupuesto48.UDF sacar promedio del curso de Finanzas 149.UDF para calcular el pedido de materiales en el mes50.UDF Nota Ponderada de Curso de Postgrado51.Función FRC/FCS52.Cálculo de Tasas Efectivas y Anualidades53.Cálculo del WACC54.Crecimiento promedio geométrico del PBI55.Función RandomNumber en VBA56.UDF aplicado al conteo de datos57.UDF para el cálculo del valor futuro según pagos con Gradiente Geométrico58.Punto de Equilibrio y Valor de Equilibrio59.Excel Avanzado UDF aplicado al cálculo de la cuota flat60.Excel Avanzado: UDF aplicado a determinar la variación de productividad61.Excel Avanzado: UDF aplicado al cálculo del costo de oportunidad de un

retailer62.Excel Avanzado: UDF aplicado al calculo del número de operarios en una

planta63.UDF aplicado al cálculo de la diagonal de un poliedro64.Excel avanzado: UDF aplicado a valuación de inventarios65.Excel Avanzado: UDF para el cálculo de Productividad de una Agencia

Bancaria66.Excel avanzado: UDF aplicado al cálculo del área de un círculo67.Excel Avanzado: UDF Aplicado a eleccion de Depositos a Plazo Fijo68.Calculando ahorros

6

69.Excel Avanzado: UDF aplicado para el cálculo de variación de costo por eficiencia

70.Excel Avanzado: UDF aplicado al cambio de una Tasa de Interés en Dólares a Soles

71.Excel Avanzado: UDF para dar vuelto en soles a una venta en dólares72.UDF aplicado a la aprobación de un curso73.Excel Avanzado: UDF Número de cifras de un Decimal transformado en

Binario74.Cálculo de tiempo de trabajo de un empleado75.Funcion Excel Avanzado: UDF aplicado al calculo del sobregiro bancario76.UDF Aplicado al calculo del Rendimiento Esperado de una accion77.Uso de Macros En la Elaboracion de un Índice Bursátil78.Ejemplo de "For" aplicado al Cálculo del Factorial de un Número79.Ejemplo de UDF para el cálculo de la retracción de una factura.80.UDF aplicado a la evaluación de avance de un proyecto81.Excel Avanzado: UDF aplicado al cálculo del promedio de prácticas y

promedio final.82.Ejemplo de función para el cálculo de la comisión ganada por ventas83.Funcion para calcular el tipo de cambio proyectado84.Ejemplo de función para el cálculo del porcentaje de merma ideal85.Función para el Cálculo del Test de Diferencias86.Calculo de función FORGEN (fórmula general)87.Funcion para calcular pago ventas88.Función para la Descomposición Factorial de un Número con recursividad89.Función para calcular el CRAEST90.Función para el Cálculo de la Hipotenusa de un Triángulo Rectángulo91.Excel Avanzado: UDF Aplicado al cálculo del índice de Lerner92.Ejemplo de UDF para valorar opciones Call con el modelo Black Scholes93.Ejemplo de UDF para el Cálculo de Pago de Impuesto a la Renta de Cuarta

Categoría94.Ejmplo de UDF para Calcular el Tipo de Cambio Forward Teórico95.Ejemplo de UDF para calcular el Volumen de un cilindro96.Función Ingreso97.Función Consumo98.Función definida por el usuario: Par-Impar99.Función Discriminante de un Polinomio Cuadrático100. Función definida por el usuario: Área de un triangulo101. Función Calificación102. Función BeneficioCosto103. Función IMC

7

Estructuras de Control VBAEn VBA al igual en que otros lenguajes de programación se encuentran disponibles las principales estructuras de programación o estructuras de control en esta página encontraremos algunos ejemplos de las principales estructuras de control disponibles para la creación de nuestras  macros.

Una de las estructuras de programación mas comunes, sencillas y muy útiles para la programación es la estructura IF THEN ELSE, en la siguiente lista se encuentran algunos ejemplos:

1. Uso de DO LOOP para el ejercicio del nivel estudiantil (con IF THEN )2. Ejemplo de If then Else aplicado al Cálculo del Promedio Final de un Curso3. Ejemplo de Select Case y If ... Then aplicado a la conversión de Tipo de

cambio4. If Plan Postpago - Prepago Celular5. Ejemplo de If then Else aplicado a descuento por tardanzas6. Ejemplo de If..Else aplicado a la valuación de opciones "Call" y "Put"7. Ejemplo de If then Else aplicado a la aprobación de un préstamo8. Ejemplo de if then else aplicado al craest9. Userform: Ejemplo de If Then and Else aplicado a la toma de Pagares10.Ejemplo de IF THEN ELSE aplicado a la elección del valor de variable11.Ejemplo de If then else aplicado al Cálculo de Descuentos por Volumen de

Compra12.Ejemplo de IF THEN ELSE para el cambio de un numero en base menor a 1013.Ejemplo de If Then Else aplicado a verificación de fechas14.Aplicación de IF THEN ELSE a Put Options15.Estructura IF THEN ELSE aplicado a gratificaciones16.Ejemplo de uso de IF then else (VBA)17. Ejemplo de uso de IF then else (desde formulario)

Una de las particularidades del If then else, radica en que solo se tienen dos alternativas, una para superar ello se pueden emplear ifs anidados (un if dentro de otro if), una  alternativa en algunos casos podría ser emplear la estructura Select Case, a continuación una lista de ejemplos de select case:

1. For ...Calcular Puntaje Promotor2. Select Case - Calcular costo de entrada Concierto3. Cálculo de bono por ventas4. Calificación crediticia por regiones y crecimiento del PBI5. Ejemplo de SelectCase Aplicado a Menú de Restaurante6. Ejemplo de Select Case aplicado a la clasificación alfabética de las notas de

un exámen7. Select Case aplicado a solicitud de becas8. Ejemplo de SELECT CASE aplicado a la determinación de el NSE9. Ejemplo Select Case aplicado conocer el premio de la loteria10.Select case aplicado a códigos telefónicos11.Ejemplo se Select Case aplicado a Tarifas InfoPUC

8

12.Ejemplo de Select Case aplicado al cálculo del monto a pagar por entradas al estadio

13.Ejemplo de Select Case aplicado a la determinación del NSE14.Ejemplo de Select Case Aplicado a selección de tarifas15.Ejemplo de Select Case aplicado a la detección de retención de 5ta categoría16.Select Case aplicado a beneficios por pago puntual17.Ejemplo de Select Case aplicado a la validación del ingreso de una fecha18.Ejemplo de uso de select case aplicado a una planificacion de la produccion19.Función Select Case aplicado a las horas del día20.Uso de Select Case para sistema progresivo de impuesto a la renta

Si bien el grabador de macros  nos puede proporcionar una secuencia de pasos a repetir, esa secuencia al ejecutarla se ejecuta una sola vez, por lo que se podría emplear l estructura FOR para repetir la ejecución de algo varias veces, en a siguiente lista encuentre algunos  ejemplos de como se puede emplear for:

1. FOR en BVA para la venta de terrenos de la inmobiliaria LOS PORTALES2. For ...Calcular Puntaje Promotor3. FOR para ordenar por dias4. FOR en VBA para una Calculadora5. FOR en VBA para el control de tiempos en la tercerización de Servicios6. Ordenador por número de hijos con For Next7. Carrera de Caballos: Uso del With y también el For, Do y el While Wend8. For en VBA para la Función YEAR9. FOR en VBA para la asignación de bonos10.FOR en VBA para el calculo promedio de un portafolio de acciones11.Evaluacion del dia con la Función Day12.Extraer números VBA - Ejemplo con Tipo de cambio13.FOR en VBA para Calcular Horas Laboradas menores a 214.Ordenar Hojas en excel con macros15.For en VBA para el calculo de áreas de una lista16.Creación de una PPT con VBA17.For next aplicado a lista de clientes18.Convertir el formato de fecha estándar en letras19.FOR en VBA al calculo de número primos20.For... Next aplicado al cálculo del promedio del curso21.Stock acumulado de vehículos22.Ejemplo de For...Next aplicado a destinos frecuentes de una aerolinea23.Ejemplo de For aplicado al llenado de una plantilla para la Venta de

Departamentos24.Excel Avanzado : Ejemplo de For aplicado al llenado de una planilla de

trabajo.25.Ejemplo de For aplicado al cálculo de la producción diaria en una empresa26.Ejemplo de FOR aplicado a anidar For .. do .. next27.Ejemplo de FOR aplicado a la elaboración de listas28.Análisis de sensibilidad de la pensión ante cambios en la tasa de venta29.Ejemplo de "For" aplicado al Cálculo del Factorial de un Número30.Ejemplo de uso de For aplicado al calculo del promedio de ventas31.Ejemplo de uso de For aplicado al cálculo de la Suma de Cuadrados32. Ejemplo de la Función FOR

9

Si bien el for es flexible no siempre se conoce cuantas veces se debe producir cuantas veces se debe producir una repetición , el emplear una condición para detener la ejecución de una repetición es mas sencillo si se emplea la estructura Do Loop, en la siguiente lista revise algunos ejemplos de la sintaxis de do loop:

1. DO LOOP aplicado a la localización de planta2. Do... Loop para evaluar si dar un crédito o no a un solicitante3. Do Loop aplicado a repetir indicaciones4. Do Loop: Aplicado a la obtención del Brevete5. Carrera de Caballos: Uso del With y también el For, Do y el While Wend6. DO LOOP en VBA para seleccionar balones reglamentarios7. DO LOOP en VBA para inscribir a un postulante a la PNP de acuerdo a su

talla.8. Do Loop en VBA para detallar la condición de un pasajero9. DO LOOP en VBA aplicado al cálculo del porcentaje de proveedores

homologados10.DO LOOP en VBA para generar datos aleatorios11.DO LOOP en VBA para contraseña de usuario12.Uso de DO LOOP para el ejercicio del nivel estudiantil (con IF THEN )13.Crear un Archivo por cada hoja de Excel14.Ejemplo de DO LOOP para solicitar datos15.Aplicación de la estructura de control "Do Loop"16.Selección de beneficiarios de un programa social: aplicación del Bucle Do

Loop17.Excel Avanzado: Do Loop aplicado a la selección de candidatos18.Do...Loop aplicado para la actualización de estado de un velocímetro19.Estructura Do Loop aplicado al rango de notas20.Ejemplo de Do loop para establecer un resultante21.Ejemplo de Do...Loop aplicado al cálculo de la suma de los N primeros

números naturales22.Ejemplo de Do Loop aplicado al registro de proyectos23.Ejemplo de do loop aplicado al calculo del valor fob exportado24.Ejemplo de Do loop aplicado para Rellenos de Información Personal25.Ejemplo de uso de Loop, aplicado a los ingresos tributarios del gobierno26.Ejemplo de uso de “Do Loop” aplicado al relleno de plantillas de Costos.27.Ejemplo de Uso de DoLoop aplicado a llenado Fechas28. Ejemplo de uso de Do loop

Es común encontrar la estructura WITH cuando usamos el grabados, claro también podemos emplear dicha estructura por nuestra propia cuenta, aquí algunos ejemplos de with.

1. Uso de With para dar formato a un registro de facturas2. For ...Calcular Puntaje Promotor3. With en BVA para mostrar alertas sobre el retraso en las tareas programadas4. Carrera de Caballos: Uso del With y también el For, Do y el While Wend5. Evaluacion de Status con With6. Estructura With...End With para determinar el mejor método de pronóstico7. Función With Formato para presentación de cuadros estadísticos8. Ejemplo de With...End With aplicado a la creación de una plantilla de horario

10

9. Función With aplicada a una tabla10.Ejemplo de With aplicado al registro de Horas de Trabajo de Equipos11.Ejemplo de With aplicado a dar formato a una base de datos de colegios12.Ejemplo de With aplicado a formato de cabecera de una tabla13.Ejemplo de With aplicado para formato de notas14.Ejemplo de With aplicado a dar formato a un registro de promedios finales de

alumnos15.Ejemplo de With aplicado al ordenamiento y formato de datos16.Ejemplo de With aplicado al formato de notas y promedio de notas17.Ejemplo de With-end With aplicado a formato de celdas18.Ejemplo de with and end with aplicado a dar formato a una imagen19.Función With aplicada a modificar un gráfico20.Ejemplo de uso de With- end With aplicado a dar Formato a Reportes

Temas relevantes de Excel AvanzadoEn esta relación de temas se encuentran los correspondientes a temas que son motivo de frecuente consulta, para la comprensión de algunos de estos temas es posible que se requiera la revisión de algunos temas previos.

A la fecha no contamos con un archivo único desde el cual bajar todo el contenido ( gracias por la consulta Ernesto), esto es debido a que nuestro contenido se encuentra en permanente actualización, pueden recurrir revisar el contenido mostrado en cualquier momento.

1. Detectar cambio de valor en celda con macros en excel2. Declarar arreglos en VBA (ejemplo)3. Uso de Cells para comparar el mayor valor4. La shell de windows5. Eliminar los hipervínculos de la hoja activa6. VBA - Objeto Aplication7. Algunas formas de Copiar Hojas en VBA8. VBA - Objeto Worksheets9. Formularios VBA – Uso y propiedades del OptionButton10.Propiedades y eventos del Textbox11.Ejemplo de uso de Resize en Cells12.Propiedades del UserForm13.Protección por contraseña para las macros14.Utilidad y uso del Variant15.Diferencia entre Byval y Byref16.Ejemplo de adición y eliminación de elementos de un Combobox

11

17.Ejemplo de Application.Run18.Formularios VBA - Uso y propiedades del ListBox19.Uso y Propiedades del Combobox20.Uso y propiedades del Label21.Uso y propiedades del Checkbox (desde una hoja excel)22.Utilidad de ThisWorkBook (grabar cambios)23.Diferencias y ejemplos entre Range("A1").text y Range("A1"). value24.Función Inputbox25.El Objeto Application26. Uso de Arreglos en VBA

Ejemplos de Sintaxis de VBAMuchas veces resulta complicado poder entender o identificar como emplear las diversas instrucciones disponibles en VBA, en los siguientes ejemplos encontrará algunas ideas de como emplear varias de los instrucciones disponibles en VBA , la mayoría de esto ejemplos son sencillos de comprender, sin embargo si alguno le parece algo complejo por favor registre un comentario sobre el los mismos a fin de mejorar los ejemplos publicados.

De igual forma si identifica alguno de nuestros artículos que considera que debamos incluir en esta relación por favor registre un comentario en esta página para que podamos incluirlo.

1. Uso de With para dar formato a un registro de facturas2. FOR en BVA para la venta de terrenos de la inmobiliaria LOS PORTALES3. For ...Calcular Puntaje Promotor4. DO LOOP aplicado a la localización de planta5. Do... Loop para evaluar si dar un crédito o no a un solicitante6. FOR para ordenar por dias7. FOR en VBA para una Calculadora8. FOR en VBA para el control de tiempos en la tercerización de Servicios9. Do Loop aplicado a repetir indicaciones10.With en BVA para mostrar alertas sobre el retraso en las tareas programadas11.Do Loop: Aplicado a la obtención del Brevete

12

12.Ordenador por número de hijos con For Next13.Carrera de Caballos: Uso del With y también el For, Do y el While Wend14.DO LOOP en VBA para seleccionar balones reglamentarios15.DO LOOP en VBA para inscribir a un postulante a la PNP de acuerdo a su

talla.16.For en VBA para la Función YEAR17.Do Loop en VBA para detallar la condición de un pasajero18.FOR en VBA para la asignación de bonos19.DO LOOP en VBA aplicado al cálculo del porcentaje de proveedores

homologados20.DO LOOP en VBA para generar datos aleatorios21.DO LOOP en VBA para contraseña de usuario22.FOR en VBA para el calculo promedio de un portafolio de acciones23.For next aplicado a lista de clientes24.Grupos de botones de opción para el registro de una Clínica25.Función WeekdayName26.Añadir o Eliminar Elementos de un ListBox27.Ejemplo This WorkBook aplicado a Creacion de New Sheet28.Utilidad del offset29.Ejemplo de adición y eliminación de elementos de un Combobox30.Función Inputbox31.Comparar textos usando funciones (trim, UCase, LCase, etc)

Gráficos con MacrosLos gráficos también pueden ser creados por medio de macros, en los siguientes ejemplos podrá encontrar algunos casos en los que los gráficos son generados desde una macro sencilla.

Tenga presente algunos detalles al momento de crear un gráfico mediante macros, como por ejemplo que existen grandes diferencias en los gráficos generados entre la versión 2003, y la versión 2007 de Excel, por lo que un gráfico que fue generado con una macro empleada en Excel 2003, en Excel 2010 no debería tener problemas en poder ser reutilizada, sin embargo el caso contrario no va a ser siempre posible que se pueda emplear con sencillez.

Recuerde que la utilidad de una macro radica entre otros aspectos en que se pueda llevar a cabo una labor que realizándola de forma manual sería algo complicado, rutinario, o nos consume mucho tiempo innecesario, si ese es el caso (por ejemplo que tuviese que crear medio centenar de gráficos) entonces es conveniente que utilice macros, lo recomendable para ello es que comience con la creación de un gráfico sencillo, luego explore el añadir mas condiciones que mejoren el gráfico logrado.

13

En la siguiente relación de gráficos con macros, se encuentran algunos bastante interesantes que podría resultarle de utilidad, por favor registres sus comentarios sobre los mismos a fin de tener presente que aspectos mejorar en los mismos, o que cosas podríamos tomar en cuenta en los siguientes gráficos que lleguemos a crear.

Ejemplos de Gráficos creados con macros: 

1. Gráfico en VBA - Crecimiento de Sectores Económicos - 20122. Gráfico en VBA para hallar el número de asistencias de los trabajadores3. Gráficos en VBA sobre Flujos Macroeconómicos del BCRP4. Creación de Gráfico para ventas por área, caso con múltiples locales5. Creación de gráficos aplicado a ventas6. Excel avanzado: utilización de macros para la creación de gráficos7. Creación de gráfico aplicado a visualizar el número de faltas de los

trabajadores8. Catálogos de imágenes con macros9. Excel Avanzado: Creación de Gráfico de Transacciones Bancarias10.Creación de Gráfico para el Análisis del PBI por sectores productivos11.Creación de Gráfico aplicado a manejar información de ventas y saldos en

empresas12.Creación de gráficos con macros (parte 2)13.Creación de gráficos con macros (parte 1)

14

Ejemplos de Tablas Dinámicas con MacrosEn un curso de Excel Avanzado se debe poder elaborar tablas dinámicas por medio de macros (o vba), como es conocido  las táblas dinámicas pueden ser creadas con una amplia variedad de propósitos, la importancia de poder crear tablas dinámicas de forma automatizada radica en diversos aspectos:

- Cada vez que se necesite volver a generar la tabla dinámica, debe lograr crearse de la misma forma, es decir, si se crea una vez las próximas deben ser creadas con el mismo formato.

- Si la misma tabla dinámica tiene que volver a crearse una y otra vez, ya no se requerirá de mucho tiempo adicional, sino solo de volver a ejecutar la macro respectiva.

- Una tabla dinámica cuando se tiene que volver a generar, normalmente no cambia la relación de columnas, pero el número de filas disponibles si cambia con frecuencia, el programa debe controlar que al volver a ejecutar la macro para la generación de la tabla dinámica  esta sea generada tomando en cuenta todas las filas.

1. Tabla Dinámica en VBA para calcular la suma de ventas de una distribuidora2. Tabla dinámica para el control de compras de productos en un hotel3. Tabla Dinámica para el Análisis de las Preferencias en el Consumo de los

Clientes4. Tabla Dinámica para control de inventario de una Farmacia5. Tabla dinámica para evaluar el estado del parque automotor de una ciudad6. Tabla dinámica para evaluar el nivel de analfabetismo del Perú por

Departamento, Provincia y Distrito7. Tabla dinámica aplicada a la clasificación de sueldos a pagar por áreas de

una empresa8. Tabla Dinámica para Identificar Tipo de Transacciones9. Tabla Dinámica para obtener los precios promedio y el volumen de

transacciones por año del índice S&P50010.Tabla Dinámica del Censo por Distritos del Perú 200711.Tabla dinámica para el registro de ventas de una empresa12.Tabla Dinámica aplicado a la obtención de mejores alumnos por horario13.Tabla Dinámica con Macros para Totalizar Ventas anuales14.Tabla Dinámica aplicada a Empresa de Archivos y Custodia de Documentos15.Creación de Tabla Dinámica aplicada a Exportaciones16.Creación de tabla dinámica para calcular las ventas de vehículos por zonas17.Creación de tabla dinámica: Resumen de acogida a concierto según edad18.Macro para generar tabla dinámica de reporte de producción19.Creación de una Tabla Dinámica - Despachadores de aduanas20.Tabla dinámica para las exportaciones

15

21.Tabla Dinámica de gastos pre-operativos para apertura de un restaurante22.Tabla dinámica para equipos internados en una empresa de

telecomunicaciones23.Tabla Dinámica para organizar y cuantificar la cantidad de gaseosas24.Tabla Dinámica para Estado de Inventario según Marca y Responsable25.Ejemplo de Tabla Dinámica para Indicadores de Niveles de Servicio26.Excel Avanzado: Tabla Dinámica para Cuantificar el saldo de clientes por

criterio27.Excel Avanzado: Tabla Dinámica para análisis de gastos vs presupuesto28.Excel Avanzado: Tabla dinámica para Consolidar Comisiones Bancarias29.Tabla Dinámica - Presupuesto Planillas y Aportaciones30.Tabla dinámica para análisis de Quejas y Reclamos - VBA31.Tabla dinámica de consumo de materiales médicos por pacientes32.Ejemplo de Tabla Dinámica para Control de Costos33.Tabla dinámica con macros sobre exportaciones de palta en el Perú34.Ejemplo de Tabla Dinamica para las Ventas por Meses35.Ejemplo de tabla dinámica para el seguimiento de desempeño de maquinaria.36.Tabla Dinámica con Macros para resumir PBI de la última década37.Tabla Dinámica con Macros para el PBI y el Ingreso Fiscal de algunos Países38.Tabla Dinámica para Micro y Pequeña empresa39.Tabla Dinámica con macros para Análisis de la Producción vs. Presupuesto40.Tabla Dinámica - Inventariado de Software41.Tabla Dinámica: Control de Inventario para empresa de Telefonía42.Tablas Dinámicas con macros: Impacto de Incidencias en una organización43.Tabla Dinámica: base de almacenes SAP de Backu's

16

Userform en ExcelLos formularios disponibles en VBA son conocidos como Userforms, estos formularios pueden ser empleados desde cualquier hoja de Excel, simplemente invocándolos desde un botón.

Cuando se desea ejecutar un macro, al ingresar la opción “Macros” desde la ficha de programador, se muestra una relación de macros que pueden ser empleadas, desde dicha pantalla no existe mas interacción relevante que solo seleccionar cual de las macros emplear, en cambio desde un  Userform se pueden crear objetos como cajas de texto, botones de opción, listas, etc, con las cuales se puede interactuar antes de proceder a ejecutar alguna macro.

El contenido disponible en un Userform puede ser empleado para interactuar con algún contenido en cualquiera de las hojas que encuentren en nuestro archivo Excel, o solo interactuar entre los datos disponibles desde el mismo Userform.

La cantidad de usos que le podemos dar a un Userform es muy diverso, en la siguiente lista encontraremos algunos ejemplos de como podemos emplearlos.

1. Formulario para el Registro de datos de contacto de clientes2. Reporte de Condiciones/actos subestándar3. Formulario aplicado a la evaluación del stock de una botica4. Formulario aplicado a una Orden de Compra5. Formulario para el registro de alumnos del sistema de colegio6. Formulario en VBA aplicado al cálculo del valor intrínseco de una opción7. Formulario para ingresar a Banca por Internet8. Formulario en VBA aplicado a la creación de una Agenda telefónica9. Formulario Aplicado a una Encuesta de Consumo10.Formulario: Registro de Ventas de un Restaurant11.Formulario en VBA aplicado al cálculo de volúmenes12.Formulario en VBA para Registro de Gimnasio13.Formulario en VBA aplicado a obtener tipo de cambio14.Formulario de Registro de usuario15.Formulario en VBA aplicado a la elaboración de una Encuesta de Cine16.Formulario en VBA para proteger data17.Formulario en VBA para calcular Funciones Trigonométricas18.¿Cómo buscar un valor en un rango de celdas en VBA?19.¿Cómo validar un email con macros?20.¿Cómo crear una función en VBA?21.Ejemplo de SelectCase Aplicado a Menú de Restaurante22.Formulario Aplicado a la búsqueda de productores23.Ejemplo de Select Case y If ... Then aplicado a la conversión de Tipo de

cambio24.Valorización de Acciones y Portafolios25.Userform aplicado al registro de facturas26.Ejemplo de Userform para el registro de egresos en una organización sin

fines de lucro27.Ejemplo de userform para registro de operaciones

17

28.Ejemplo de Userform para Proyección de pago de tributos29.Ejemplo de UserForm para cálculo de precio de medicinas30.Registro de Caja usando UserForms31.UserForm para determinar la condición del alumno (Aprobado –

Desaprobado)32.Formulario de Excel Avanzado para la compra de entradas a un concierto33.Formulario de Excel Avanzado para Logistica (Inventarios)34.Formulario de Excel Avanzado para Cálculo de Cuota Flat en casas

comerciales35.Formulario para crear el Status de procesos de Reclutamiento y Selección

(R&S)36.Formulario de Excel Avanzado para el registro de los clientes de una tienda37.Formulario de Excel Avanzado Para boleta de entrada al cine38.Formulario de Excel Avanzado para el ingreso de documentos de Salida de

un Almacén39.Formulario Excel aplicado para determinar Límites de Control en análisis de

calidad40.Formulario de Excel para el cálculo de cuotas uniformes de un préstamo41.Formulario de Excel Avanzado para calcular el costo de un seguro de salud42.Formulario de excel avanzado para convertir unidades de presión43.Userform para el calculo de sobregiro bancario44.Formulario de Excel avanzado para el registro de datos relevantes para

proyectos45.Formulario para controlar el consumo de quimicos46.Excel Avanzado, Formulario para Calcular el Costo de una pizza47.Formulario aplicado a Ingreso de data: Encuesta-Evento48.Formulario aplicado a la evaluación de otorgamiento de un préstamo49.Formulario para realizar conversion de Divisas a Soles50.Formulario para calcular la velocidad de un cuerpo en caida libre51.Formulario aplicado a uso de Registro de Personal de Capacitación52.Formulario para Convertir Grados Sexagesimales a Radianes o Centesimales53.Ejemplo de Userform para calcular años trabajados54.Calculador de tarifa de algún restaurante55.Ejemplo de UserForm para calcular intereses56.Ejemplo This WorkBook aplicado a Creacion de New Sheet57.Ejemplo de Userform para evaluar créditos58.Ejemplo de UserForm para hacer cambio de unidades de Temperatura59.Propiedades del UserForm60.Ejemplo de Userform para calcular descuentos de dos productos.61.Ejemplo de Userform para Calcular el Costo Total de Un Artículo62.Userform aplicado a la transformación de edad63.Ejemplo de adición y eliminación de elementos de un Combobox64.Formularios VBA - Uso y propiedades del ListBox65.Formularios VBA - Eventos del ListBox66.Formularios VBA - Uso y propiedades del CommandButton

18

Eventos en VBAEl manejo de eventos es esencial para lograr un dominio del manejo de Formularios en Excel, la creación de los formularios suele ser sencilla e intuitiva, sin embargo lograr dominar los eventos no suele ser tan intuitivo, comprender el funcionamiento de alguno de los eventos es necesario para logar poder usar los formularios con todas las funcionalidades posibles.

En algunos de los casos el control de eventos suele complementarse con el uso apropiado de los atributos de los objetos disponibles,  incluso alguno de estos atributos podrían ayudar a evitar que se tengan que usar los eventos, de los formularios.

Uno de los primeros eventos que se aprende de forma sencilla es el uso de un botón, cuando se presiona un botón ocurre un evento (un suceso) que puede ser controlado desde una macro, de forma análoga otros eventos o sucesos pueden ocurrir que pueden ser controlados, como por ejemplo la apertura de un archivo, el cierre de un archivo, la selección de un elemento en una lista, etc.

Revise la siguiente lista, al momento de crear esta página aún existen solo unos pocos ejemplos pero seguiremos incrementándolos paulatinamente por lo que lo invitamos a regresar a nuestra página para que vea que novedades tenemos disponibles.

1. Principales eventos de un listbox en un userform2. Uso de formularios para el registro de datos estudios en el exterior3. Principales eventos de un label en un userform4. Eventos del CommandButton

19

Trucos de Excel AvanzadoMuchas veces cuando estamos resolviendo un problema en el cual empleamos VBA o macros es necesario contar con algunos ejemplos que nos puedan ser de utilidad para resolver los casos que tengamos que resolver.

En la siguiente lista de trucos de Excel Avanzado se encuentra una relación de temas relevantes, para la compresión de los mismos se requiere tener algún conocimiento de VBA o Macros a fin de poder sacarle el máximo provecho a alguno de estos, esperamos su consultas o sugerencias sobre los mismos.

1. Guardar rango de celdas como PDF2. Ejemplo de Números Aleatorios Únicos3. Ordenar datos con macros - VBA EXCEL4. Manejo de colores con macros en excel5. Identificar si archivo esta en uso VBA6. ¿Cómo identificar la fecha de modificación de una celda en VBA?7. Enviar archivo adjunto en Email con VBA8. Cambiar de categoría de una UDF9. ¿Cómo buscar un valor en un rango de celdas en VBA?10.Extraer primera letra de la palabra (Acrónimos)11.¿Cómo ejecutar macros en Excel?12.Combinar dos columnas13.Ordenar Hojas en excel con macros14.Crear un Archivo por cada hoja de Excel15.Uso de colores para autofiltro en VBA16.Crear hojas de una lista (VBA)17.Asignar una tecla de acceso directo a una macro existente18. Ideas para concatenar celdas con VBA o Macros19.Contar palabras en Excel aplicando VBA Macros20.Verificar un formato de fecha con Macros21.La Desviación Estándar en VBA22.Concatenar el contenido de un rango de celdas (mail recordatorio)23.¿Qué es el OptionButton en VBA y cómo se usa?24.Listar nombres de archivos en excel con macros25.Unir contenido de Celdas26.¿Cómo se usan los botones de opción en VBA?27.Arreglos Multidimensionales en VBA28.Manejo de Hojas de Excel con macros29.¿Cómo se usan las casillas de verificación en VBA?30.Catálogos de imágenes con macros31.Excel Avanzado: Manejo de Libros de Excel con Macros32.Mostrar y ocultar líneas de división con macros33.Eliminar Validación de datos con macros34.Eliminar tíldes con Macros35.Cambiar un color de letra en función de la fecha36.Convertir números en letras con Macros en Excel

20

37.¿Cómo encontrar celdas combinadas?38.VBA - Objeto WORKBOOKS39. Intercalar filas en blanco con Macros o VBA40.Algunos trucos para optimizar macros en Excel41.Eliminación de comentarios en Hojas Excel42.Listar los links existentes con VBA43.Ejemplo Application.DisplayAlerts44.Eliminar los hipervínculos de la hoja activa45.Operaciones básicas con archivo y carpetas46.Uso de WorksheetFunction.Vlookup47.Formulario de Excel Avanzado para el ingreso de documentos de Salida de

un Almacén48.UDF para el cálculo del valor futuro según pagos con Gradiente Geométrico49.Uso de Macros En la Elaboracion de un Índice Bursátil50.Macro para convertir capital con tasa efectiva51.Rentabilidad de activos en un horizonte de inversion variable52.Encontrar información eliminando filas53.Tabla dinámica para análisis de Quejas y Reclamos - VBA54.Principales propiedades de un Textbox en un UserForm55.Principales propiedades de un checkbox en un userform56.Generador de Horarios en Excel57.Calculo de posiciones de partidos de la eliminatoria Brasil 201458.Combobox Dependientes59.Validar el ingreso de un email en un textbox60.Validar que solo existan letras y números en un textbox61.Activar o Desactivar el recalculo automático (F9) en una función62. Identificar la última fila en uso con VBA63.Ejemplo de uso de Solver con Macros64.Cambiar los colores de fondo con VBA65.Uso de Resize en Offset66.Mostrar cuadro de dialogo "Abrir" donde se elige un archivo67.Como mostrar todas las hojas que esten ocultas68.Uso de la función PROPER69.Creación de un listado de Archivos70.Buscar Objetivo (con macros)71.Buscar y reemplazar masivo (parte1)

21

Macros en OfficeLas Macros pueden ser empleadas en los diversos programas de Office, en los siguientes ejemplos de Macros en Office podrá encontrar algunas ideas de como emplearlas, muchos de los ejemplos se centran en la idea de poder integrar el Excel con alguno de los otros programas disponibles en Office, como por ejemplo como generar un PPT desde Excel.

Muchas veces esta integración puede resultar compleja, si esta comenzando a crear macros y tiene poca experiencia en la creación de macros le recomendamos que revise antes alguno de los ejemplos de sintaxis disponibles que le ayudarán a comprender los conceptos necesarios para emplear el VBA con fluidez, y pueda llegar a crear las macros que realmente necesita.

1. Guardar rango de celdas como PDF2. Creación de una PPT con VBA3. Generar un archivo de Word desde VBA Excel4. Cómo crear un archivo en word desde vba - excel5. Creación de PPT6. Copiar un contenido de Excel a Word

22