• Author / Uploaded
• czavalaga

Citation preview

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 VBA Existen 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 [email protected] 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 Day 2. Función Month en VBA 3. WORKSHEETFUNCTION.MAX para el promedio de notas 4. Función Right en VBA 5. Función WorksheetFunction.Average 6. Uso de WorksheetFunction.CountBlank 7. Uso de WorksheetFunction.CountA 8. Función CINT 9. Función Mid para calificación crediticia 10. Función Abs 11. Función Now en VBA 12. Función Formatpercent 13. Función DateAdd (Teoría Nagele) 14. Función FileLen en VBA 15. Función IsEmpty en VBA 16. Función Len en VBA 17. Función Left 18. Función Space en BVA 19. Uso de WorksheetFunction.RandBetween 20. Función Rnd 21. Funcion IsNumeric 22. Función FormatCurrency en VBA 23. Función IsNull en VBA 24. Función Cbyte en VBA 25. Función StrConv 26. Función Replace 27. Función Val en VBA 28. Función CLng en VBA 3

29. Uso de WorksheetFunction.Vlookup 30. Función Cdec 31. Función "Choose" en VBA 32. Función CStr 33. Función CurDir 34. Función CVAR en VBA 35. Función InputBox en VBA 36. Función CCur en VBA 37. Función DateSerial en VBA 38. Función WeekdayName 39. Función LCase con VBA 40. Función ROUND 41. Función Sgn 42. Función Date 43. Función INT 44. Función DateDiff 45. Función CDate con VBA 46. Función Fix con VBA 47. Función LTrim 48. Función CHR 49. Función DateSerial 50. Funcion Cbool 51. Función TimeSerial 52. Funcion Ucase 53. Función InStr 54. Función Format 55. Función CDbl 56. Función IsDate 57. Función Asc 58. 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 Temperatura 2. UDF del cálculo de pronostico usando el método estacional multiplicativo 3. UDF Funcion de Indice Masa Corporal 4. El Campo Magnético de un alambre según la Ley de Biot-Savart 5. UDF aplicado a Cálculo de la CTS 6. UDF Aplicado al cálculo del Índice de Ruffier 7. Función Porcuantotevas 8. UDF para calcular el costo de la energía consumida por un aparato eléctrico. 9. Función ValorCajaCero 10. UDF aplicado al Cálculo de la Rentabilidad Real 11. UDF aplicado a Cálculo de Ahorro en un Cuadro Comparativo de Precios 12. UDF aplicado a obtener la Variación de Productividad de una empresa 13. UDF aplicado al calculo de rentabilidad de un portafolio, modelo CAPM 14. UDF aplicado al cálculo del Indice de Masa Corporal 15. UDF Aplicado a la suma de decibeles 16. UDF Aplicado a cálculo de Fracciones de Colesterol y Riesgo coronario 17. UDF Suma de valores en negrita 18. UDF para calcular el Descuento de AFP 19. UDF aplicado a los rangos de Retorno Esperado de una acción 20. 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 IRA 23. UDF Función Tipo de Cambio Forward Venta (fwventa) 24. Función CompensacLCE 25. Velocidad Final y Altura en MRUV en caida libre 26. UDF-cuota leasing 27. USD - Función del Ratio BEP 28. Extraer números VBA - Ejemplo con Tipo de cambio 29. Cambiar de categoría de una UDF 30. Ideas para concatenar celdas con VBA o Macros 31. UDF aplicado a Cálculo de las Notas Curso Excel 32. UDF aplicado a Calculo de la CTS 33. UDF Promedio de Notas del curso de Decisiones de Inversión 34. UDF - Estado de Resultados para cuantificar la Utilidad Empresarial 35. Cálculo Nitrógeno Amoniacal 36. UDF Promedio de ventas 37. Distancia entre los puntos 38. UDF - Tipo de Obesidad según el IMC 39. UDF Función Utilidad Retail 40. UDF: determinación del tamaño de una muestra estadística 41. UDF - Cálculo de notas del laboratorio de Sistemas Integrados de Producción 42. Formulas para sacar promedio total y nota requerida 43. Cantidad Económica de Pedido - modelo comercial de inventarios 44. Función sumtotaldeQ 45. Concatenar el contenido de un rango de celdas (mail recordatorio) 46. Función Suma de Dígitos 47. UDF – Presupuesto 48. UDF sacar promedio del curso de Finanzas 1 49. UDF para calcular el pedido de materiales en el mes 50. UDF Nota Ponderada de Curso de Postgrado 51. Función FRC/FCS 52. Cálculo de Tasas Efectivas y Anualidades 53. Cálculo del WACC 54. Crecimiento promedio geométrico del PBI 55. Función RandomNumber en VBA 56. UDF aplicado al conteo de datos 57. UDF para el cálculo del valor futuro según pagos con Gradiente Geométrico 58. Punto de Equilibrio y Valor de Equilibrio 59. Excel Avanzado UDF aplicado al cálculo de la cuota flat 60. Excel Avanzado: UDF aplicado a determinar la variación de productividad 61. Excel Avanzado: UDF aplicado al cálculo del costo de oportunidad de un retailer 62. Excel Avanzado: UDF aplicado al calculo del número de operarios en una planta 63. UDF aplicado al cálculo de la diagonal de un poliedro 64. Excel avanzado: UDF aplicado a valuación de inventarios 65. Excel Avanzado: UDF para el cálculo de Productividad de una Agencia Bancaria 66. Excel avanzado: UDF aplicado al cálculo del área de un círculo 67. Excel Avanzado: UDF Aplicado a eleccion de Depositos a Plazo Fijo 68. 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ólares 72. UDF aplicado a la aprobación de un curso 73. Excel Avanzado: UDF Número de cifras de un Decimal transformado en Binario 74. Cálculo de tiempo de trabajo de un empleado 75. Funcion Excel Avanzado: UDF aplicado al calculo del sobregiro bancario 76. UDF Aplicado al calculo del Rendimiento Esperado de una accion 77. Uso de Macros En la Elaboracion de un Índice Bursátil 78. Ejemplo de "For" aplicado al Cálculo del Factorial de un Número 79. 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 proyecto 81. 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 ventas 83. Funcion para calcular el tipo de cambio proyectado 84. Ejemplo de función para el cálculo del porcentaje de merma ideal 85. Función para el Cálculo del Test de Diferencias 86. Calculo de función FORGEN (fórmula general) 87. Funcion para calcular pago ventas 88. Función para la Descomposición Factorial de un Número con recursividad 89. Función para calcular el CRAEST 90. Función para el Cálculo de la Hipotenusa de un Triángulo Rectángulo 91. Excel Avanzado: UDF Aplicado al cálculo del índice de Lerner 92. Ejemplo de UDF para valorar opciones Call con el modelo Black Scholes 93. Ejemplo de UDF para el Cálculo de Pago de Impuesto a la Renta de Cuarta Categoría 94. Ejmplo de UDF para Calcular el Tipo de Cambio Forward Teórico 95. Ejemplo de UDF para calcular el Volumen de un cilindro 96. Función Ingreso 97. Función Consumo 98. Función definida por el usuario: Par-Impar 99. Función Discriminante de un Polinomio Cuadrático 100. Función definida por el usuario: Área de un triangulo 101. Función Calificación 102. Función BeneficioCosto 103. Función IMC

7

Estructuras de Control VBA En 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 Curso 3. Ejemplo de Select Case y If ... Then aplicado a la conversión de Tipo de cambio 4. If Plan Postpago - Prepago Celular 5. Ejemplo de If then Else aplicado a descuento por tardanzas 6. 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éstamo 8. Ejemplo de if then else aplicado al craest 9. Userform: Ejemplo de If Then and Else aplicado a la toma de Pagares 10. Ejemplo de IF THEN ELSE aplicado a la elección del valor de variable 11. Ejemplo de If then else aplicado al Cálculo de Descuentos por Volumen de Compra 12. Ejemplo de IF THEN ELSE para el cambio de un numero en base menor a 10 13. Ejemplo de If Then Else aplicado a verificación de fechas 14. Aplicación de IF THEN ELSE a Put Options 15. Estructura IF THEN ELSE aplicado a gratificaciones 16. 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. 2. 3. 4. 5. 6.

For ...Calcular Puntaje Promotor Select Case - Calcular costo de entrada Concierto Cálculo de bono por ventas Calificación crediticia por regiones y crecimiento del PBI Ejemplo de SelectCase Aplicado a Menú de Restaurante Ejemplo de Select Case aplicado a la clasificación alfabética de las notas de un exámen 7. Select Case aplicado a solicitud de becas 8. Ejemplo de SELECT CASE aplicado a la determinación de el NSE 9. Ejemplo Select Case aplicado conocer el premio de la loteria 10. Select case aplicado a códigos telefónicos 11. 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 NSE 14. Ejemplo de Select Case Aplicado a selección de tarifas 15. Ejemplo de Select Case aplicado a la detección de retención de 5ta categoría 16. Select Case aplicado a beneficios por pago puntual 17. Ejemplo de Select Case aplicado a la validación del ingreso de una fecha 18. Ejemplo de uso de select case aplicado a una planificacion de la produccion 19. Función Select Case aplicado a las horas del día 20. 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 PORTALES 2. For ...Calcular Puntaje Promotor 3. FOR para ordenar por dias 4. FOR en VBA para una Calculadora 5. FOR en VBA para el control de tiempos en la tercerización de Servicios 6. Ordenador por número de hijos con For Next 7. Carrera de Caballos: Uso del With y también el For, Do y el While Wend 8. For en VBA para la Función YEAR 9. FOR en VBA para la asignación de bonos 10. FOR en VBA para el calculo promedio de un portafolio de acciones 11. Evaluacion del dia con la Función Day 12. Extraer números VBA - Ejemplo con Tipo de cambio 13. FOR en VBA para Calcular Horas Laboradas menores a 2 14. Ordenar Hojas en excel con macros 15. For en VBA para el calculo de áreas de una lista 16. Creación de una PPT con VBA 17. For next aplicado a lista de clientes 18. Convertir el formato de fecha estándar en letras 19. FOR en VBA al calculo de número primos 20. For... Next aplicado al cálculo del promedio del curso 21. Stock acumulado de vehículos 22. Ejemplo de For...Next aplicado a destinos frecuentes de una aerolinea 23. Ejemplo de For aplicado al llenado de una plantilla para la Venta de Departamentos 24. 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 empresa 26. Ejemplo de FOR aplicado a anidar For .. do .. next 27. Ejemplo de FOR aplicado a la elaboración de listas 28. Análisis de sensibilidad de la pensión ante cambios en la tasa de venta 29. Ejemplo de "For" aplicado al Cálculo del Factorial de un Número 30. Ejemplo de uso de For aplicado al calculo del promedio de ventas 31. Ejemplo de uso de For aplicado al cálculo de la Suma de Cuadrados 32. 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. 2. 3. 4. 5. 6. 7.

DO LOOP aplicado a la localización de planta Do... Loop para evaluar si dar un crédito o no a un solicitante Do Loop aplicado a repetir indicaciones Do Loop: Aplicado a la obtención del Brevete Carrera de Caballos: Uso del With y también el For, Do y el While Wend DO LOOP en VBA para seleccionar balones reglamentarios 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 pasajero 9. DO LOOP en VBA aplicado al cálculo del porcentaje de proveedores homologados 10. DO LOOP en VBA para generar datos aleatorios 11. DO LOOP en VBA para contraseña de usuario 12. Uso de DO LOOP para el ejercicio del nivel estudiantil (con IF THEN ) 13. Crear un Archivo por cada hoja de Excel 14. Ejemplo de DO LOOP para solicitar datos 15. Aplicación de la estructura de control "Do Loop" 16. Selección de beneficiarios de un programa social: aplicación del Bucle Do Loop 17. Excel Avanzado: Do Loop aplicado a la selección de candidatos 18. Do...Loop aplicado para la actualización de estado de un velocímetro 19. Estructura Do Loop aplicado al rango de notas 20. Ejemplo de Do loop para establecer un resultante 21. Ejemplo de Do...Loop aplicado al cálculo de la suma de los N primeros números naturales 22. Ejemplo de Do Loop aplicado al registro de proyectos 23. Ejemplo de do loop aplicado al calculo del valor fob exportado 24. Ejemplo de Do loop aplicado para Rellenos de Información Personal 25. Ejemplo de uso de Loop, aplicado a los ingresos tributarios del gobierno 26. Ejemplo de uso de “Do Loop” aplicado al relleno de plantillas de Costos. 27. Ejemplo de Uso de DoLoop aplicado a llenado Fechas 28. 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. 2. 3. 4. 5. 6. 7. 8.

Uso de With para dar formato a un registro de facturas For ...Calcular Puntaje Promotor With en BVA para mostrar alertas sobre el retraso en las tareas programadas Carrera de Caballos: Uso del With y también el For, Do y el While Wend Evaluacion de Status con With Estructura With...End With para determinar el mejor método de pronóstico Función With Formato para presentación de cuadros estadísticos Ejemplo de With...End With aplicado a la creación de una plantilla de horario 10

9. Función With aplicada a una tabla 10. Ejemplo de With aplicado al registro de Horas de Trabajo de Equipos 11. Ejemplo de With aplicado a dar formato a una base de datos de colegios 12. Ejemplo de With aplicado a formato de cabecera de una tabla 13. Ejemplo de With aplicado para formato de notas 14. Ejemplo de With aplicado a dar formato a un registro de promedios finales de alumnos 15. Ejemplo de With aplicado al ordenamiento y formato de datos 16. Ejemplo de With aplicado al formato de notas y promedio de notas 17. Ejemplo de With-end With aplicado a formato de celdas 18. Ejemplo de with and end with aplicado a dar formato a una imagen 19. Función With aplicada a modificar un gráfico 20. Ejemplo de uso de With- end With aplicado a dar Formato a Reportes

Temas relevantes de Excel Avanzado En 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 excel 2. Declarar arreglos en VBA (ejemplo) 3. Uso de Cells para comparar el mayor valor 4. La shell de windows 5. Eliminar los hipervínculos de la hoja activa 6. VBA - Objeto Aplication 7. Algunas formas de Copiar Hojas en VBA 8. VBA - Objeto Worksheets 9. Formularios VBA – Uso y propiedades del OptionButton 10. Propiedades y eventos del Textbox 11. Ejemplo de uso de Resize en Cells 12. Propiedades del UserForm 13. Protección por contraseña para las macros 14. Utilidad y uso del Variant 15. Diferencia entre Byval y Byref 16. Ejemplo de adición y eliminación de elementos de un Combobox 11

17. Ejemplo de Application.Run 18. Formularios VBA - Uso y propiedades del ListBox 19. Uso y Propiedades del Combobox 20. Uso y propiedades del Label 21. 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"). value 24. Función Inputbox 25. El Objeto Application 26. Uso de Arreglos en VBA

Ejemplos de Sintaxis de VBA Muchas 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 facturas 2. FOR en BVA para la venta de terrenos de la inmobiliaria LOS PORTALES 3. For ...Calcular Puntaje Promotor 4. DO LOOP aplicado a la localización de planta 5. Do... Loop para evaluar si dar un crédito o no a un solicitante 6. FOR para ordenar por dias 7. FOR en VBA para una Calculadora 8. FOR en VBA para el control de tiempos en la tercerización de Servicios 9. Do Loop aplicado a repetir indicaciones 10. With en BVA para mostrar alertas sobre el retraso en las tareas programadas 11. Do Loop: Aplicado a la obtención del Brevete 12

12. Ordenador por número de hijos con For Next 13. Carrera de Caballos: Uso del With y también el For, Do y el While Wend 14. DO LOOP en VBA para seleccionar balones reglamentarios 15. 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 YEAR 17. Do Loop en VBA para detallar la condición de un pasajero 18. FOR en VBA para la asignación de bonos 19. DO LOOP en VBA aplicado al cálculo del porcentaje de proveedores homologados 20. DO LOOP en VBA para generar datos aleatorios 21. DO LOOP en VBA para contraseña de usuario 22. FOR en VBA para el calculo promedio de un portafolio de acciones 23. For next aplicado a lista de clientes 24. Grupos de botones de opción para el registro de una Clínica 25. Función WeekdayName 26. Añadir o Eliminar Elementos de un ListBox 27. Ejemplo This WorkBook aplicado a Creacion de New Sheet 28. Utilidad del offset 29. Ejemplo de adición y eliminación de elementos de un Combobox 30. Función Inputbox 31. Comparar textos usando funciones (trim, UCase, LCase, etc)

Gráficos con Macros Los 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. 2. 3. 4. 5. 6. 7.

Gráfico en VBA - Crecimiento de Sectores Económicos - 2012 Gráfico en VBA para hallar el número de asistencias de los trabajadores Gráficos en VBA sobre Flujos Macroeconómicos del BCRP Creación de Gráfico para ventas por área, caso con múltiples locales Creación de gráficos aplicado a ventas Excel avanzado: utilización de macros para la creación de gráficos Creación de gráfico aplicado a visualizar el número de faltas de los trabajadores 8. Catálogos de imágenes con macros 9. Excel Avanzado: Creación de Gráfico de Transacciones Bancarias 10. Creación de Gráfico para el Análisis del PBI por sectores productivos 11. Creación de Gráfico aplicado a manejar información de ventas y saldos en empresas 12. 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 Macros En 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 distribuidora 2. Tabla dinámica para el control de compras de productos en un hotel 3. Tabla Dinámica para el Análisis de las Preferencias en el Consumo de los Clientes 4. Tabla Dinámica para control de inventario de una Farmacia 5. Tabla dinámica para evaluar el estado del parque automotor de una ciudad 6. Tabla dinámica para evaluar el nivel de analfabetismo del Perú por Departamento, Provincia y Distrito 7. Tabla dinámica aplicada a la clasificación de sueldos a pagar por áreas de una empresa 8. Tabla Dinámica para Identificar Tipo de Transacciones 9. Tabla Dinámica para obtener los precios promedio y el volumen de transacciones por año del índice S&P500 10. Tabla Dinámica del Censo por Distritos del Perú 2007 11. Tabla dinámica para el registro de ventas de una empresa 12. Tabla Dinámica aplicado a la obtención de mejores alumnos por horario 13. Tabla Dinámica con Macros para Totalizar Ventas anuales 14. Tabla Dinámica aplicada a Empresa de Archivos y Custodia de Documentos 15. Creación de Tabla Dinámica aplicada a Exportaciones 16. Creación de tabla dinámica para calcular las ventas de vehículos por zonas 17. Creación de tabla dinámica: Resumen de acogida a concierto según edad 18. Macro para generar tabla dinámica de reporte de producción 19. Creación de una Tabla Dinámica - Despachadores de aduanas 20. Tabla dinámica para las exportaciones 15

21. Tabla Dinámica de gastos pre-operativos para apertura de un restaurante 22. Tabla dinámica para equipos internados en una empresa de telecomunicaciones 23. Tabla Dinámica para organizar y cuantificar la cantidad de gaseosas 24. Tabla Dinámica para Estado de Inventario según Marca y Responsable 25. Ejemplo de Tabla Dinámica para Indicadores de Niveles de Servicio 26. Excel Avanzado: Tabla Dinámica para Cuantificar el saldo de clientes por criterio 27. Excel Avanzado: Tabla Dinámica para análisis de gastos vs presupuesto 28. Excel Avanzado: Tabla dinámica para Consolidar Comisiones Bancarias 29. Tabla Dinámica - Presupuesto Planillas y Aportaciones 30. Tabla dinámica para análisis de Quejas y Reclamos - VBA 31. Tabla dinámica de consumo de materiales médicos por pacientes 32. Ejemplo de Tabla Dinámica para Control de Costos 33. Tabla dinámica con macros sobre exportaciones de palta en el Perú 34. Ejemplo de Tabla Dinamica para las Ventas por Meses 35. 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écada 37. Tabla Dinámica con Macros para el PBI y el Ingreso Fiscal de algunos Países 38. Tabla Dinámica para Micro y Pequeña empresa 39. Tabla Dinámica con macros para Análisis de la Producción vs. Presupuesto 40. Tabla Dinámica - Inventariado de Software 41. Tabla Dinámica: Control de Inventario para empresa de Telefonía 42. Tablas Dinámicas con macros: Impacto de Incidencias en una organización 43. Tabla Dinámica: base de almacenes SAP de Backu's

16

Userform en Excel Los 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 clientes 2. Reporte de Condiciones/actos subestándar 3. Formulario aplicado a la evaluación del stock de una botica 4. Formulario aplicado a una Orden de Compra 5. Formulario para el registro de alumnos del sistema de colegio 6. Formulario en VBA aplicado al cálculo del valor intrínseco de una opción 7. Formulario para ingresar a Banca por Internet 8. Formulario en VBA aplicado a la creación de una Agenda telefónica 9. Formulario Aplicado a una Encuesta de Consumo 10. Formulario: Registro de Ventas de un Restaurant 11. Formulario en VBA aplicado al cálculo de volúmenes 12. Formulario en VBA para Registro de Gimnasio 13. Formulario en VBA aplicado a obtener tipo de cambio 14. Formulario de Registro de usuario 15. Formulario en VBA aplicado a la elaboración de una Encuesta de Cine 16. Formulario en VBA para proteger data 17. Formulario en VBA para calcular Funciones Trigonométricas 18. ¿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 Restaurante 22. Formulario Aplicado a la búsqueda de productores 23. Ejemplo de Select Case y If ... Then aplicado a la conversión de Tipo de cambio 24. Valorización de Acciones y Portafolios 25. Userform aplicado al registro de facturas 26. Ejemplo de Userform para el registro de egresos en una organización sin fines de lucro 27. Ejemplo de userform para registro de operaciones 17

28. Ejemplo de Userform para Proyección de pago de tributos 29. Ejemplo de UserForm para cálculo de precio de medicinas 30. Registro de Caja usando UserForms 31. UserForm para determinar la condición del alumno (Aprobado – Desaprobado) 32. Formulario de Excel Avanzado para la compra de entradas a un concierto 33. Formulario de Excel Avanzado para Logistica (Inventarios) 34. Formulario de Excel Avanzado para Cálculo de Cuota Flat en casas comerciales 35. 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 tienda 37. Formulario de Excel Avanzado Para boleta de entrada al cine 38. Formulario de Excel Avanzado para el ingreso de documentos de Salida de un Almacén 39. Formulario Excel aplicado para determinar Límites de Control en análisis de calidad 40. Formulario de Excel para el cálculo de cuotas uniformes de un préstamo 41. Formulario de Excel Avanzado para calcular el costo de un seguro de salud 42. Formulario de excel avanzado para convertir unidades de presión 43. Userform para el calculo de sobregiro bancario 44. Formulario de Excel avanzado para el registro de datos relevantes para proyectos 45. Formulario para controlar el consumo de quimicos 46. Excel Avanzado, Formulario para Calcular el Costo de una pizza 47. Formulario aplicado a Ingreso de data: Encuesta-Evento 48. Formulario aplicado a la evaluación de otorgamiento de un préstamo 49. Formulario para realizar conversion de Divisas a Soles 50. Formulario para calcular la velocidad de un cuerpo en caida libre 51. Formulario aplicado a uso de Registro de Personal de Capacitación 52. Formulario para Convertir Grados Sexagesimales a Radianes o Centesimales 53. Ejemplo de Userform para calcular años trabajados 54. Calculador de tarifa de algún restaurante 55. Ejemplo de UserForm para calcular intereses 56. Ejemplo This WorkBook aplicado a Creacion de New Sheet 57. Ejemplo de Userform para evaluar créditos 58. Ejemplo de UserForm para hacer cambio de unidades de Temperatura 59. Propiedades del UserForm 60. Ejemplo de Userform para calcular descuentos de dos productos. 61. Ejemplo de Userform para Calcular el Costo Total de Un Artículo 62. Userform aplicado a la transformación de edad 63. Ejemplo de adición y eliminación de elementos de un Combobox 64. Formularios VBA - Uso y propiedades del ListBox 65. Formularios VBA - Eventos del ListBox 66. Formularios VBA - Uso y propiedades del CommandButton

18

Eventos en VBA El 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. 2. 3. 4.

Principales eventos de un listbox en un userform Uso de formularios para el registro de datos estudios en el exterior Principales eventos de un label en un userform Eventos del CommandButton

19

Trucos de Excel Avanzado Muchas 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 PDF 2. Ejemplo de Números Aleatorios Únicos 3. Ordenar datos con macros - VBA EXCEL 4. Manejo de colores con macros en excel 5. Identificar si archivo esta en uso VBA 6. ¿Cómo identificar la fecha de modificación de una celda en VBA? 7. Enviar archivo adjunto en Email con VBA 8. Cambiar de categoría de una UDF 9. ¿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 columnas 13. Ordenar Hojas en excel con macros 14. Crear un Archivo por cada hoja de Excel 15. Uso de colores para autofiltro en VBA 16. Crear hojas de una lista (VBA) 17. Asignar una tecla de acceso directo a una macro existente 18. Ideas para concatenar celdas con VBA o Macros 19. Contar palabras en Excel aplicando VBA Macros 20. Verificar un formato de fecha con Macros 21. La Desviación Estándar en VBA 22. 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 macros 25. Unir contenido de Celdas 26. ¿Cómo se usan los botones de opción en VBA? 27. Arreglos Multidimensionales en VBA 28. Manejo de Hojas de Excel con macros 29. ¿Cómo se usan las casillas de verificación en VBA? 30. Catálogos de imágenes con macros 31. Excel Avanzado: Manejo de Libros de Excel con Macros 32. Mostrar y ocultar líneas de división con macros 33. Eliminar Validación de datos con macros 34. Eliminar tíldes con Macros 35. Cambiar un color de letra en función de la fecha 36. Convertir números en letras con Macros en Excel 20

37. ¿Cómo encontrar celdas combinadas? 38. VBA - Objeto WORKBOOKS 39. Intercalar filas en blanco con Macros o VBA 40. Algunos trucos para optimizar macros en Excel 41. Eliminación de comentarios en Hojas Excel 42. Listar los links existentes con VBA 43. Ejemplo Application.DisplayAlerts 44. Eliminar los hipervínculos de la hoja activa 45. Operaciones básicas con archivo y carpetas 46. Uso de WorksheetFunction.Vlookup 47. Formulario de Excel Avanzado para el ingreso de documentos de Salida de un Almacén 48. UDF para el cálculo del valor futuro según pagos con Gradiente Geométrico 49. Uso de Macros En la Elaboracion de un Índice Bursátil 50. Macro para convertir capital con tasa efectiva 51. Rentabilidad de activos en un horizonte de inversion variable 52. Encontrar información eliminando filas 53. Tabla dinámica para análisis de Quejas y Reclamos - VBA 54. Principales propiedades de un Textbox en un UserForm 55. Principales propiedades de un checkbox en un userform 56. Generador de Horarios en Excel 57. Calculo de posiciones de partidos de la eliminatoria Brasil 2014 58. Combobox Dependientes 59. Validar el ingreso de un email en un textbox 60. Validar que solo existan letras y números en un textbox 61. Activar o Desactivar el recalculo automático (F9) en una función 62. Identificar la última fila en uso con VBA 63. Ejemplo de uso de Solver con Macros 64. Cambiar los colores de fondo con VBA 65. Uso de Resize en Offset 66. Mostrar cuadro de dialogo "Abrir" donde se elige un archivo 67. Como mostrar todas las hojas que esten ocultas 68. Uso de la función PROPER 69. Creación de un listado de Archivos 70. Buscar Objetivo (con macros) 71. Buscar y reemplazar masivo (parte1)

21

Macros en Office Las 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. 2. 3. 4. 5. 6.

Guardar rango de celdas como PDF Creación de una PPT con VBA Generar un archivo de Word desde VBA Excel Cómo crear un archivo en word desde vba - excel Creación de PPT Copiar un contenido de Excel a Word

22