Excel . Macros y VBA

Atendiendo a las palabras del presidente de Brighton Collectibles, Jerry Kohl, ??En esta era de mucha información y poco tiempo, la habilidad para llegar de modo rápido y eficaz al fondo de la cuestión es lo que distingue a las empresas más importantes en cada sector??. Pero, por muy experto que sea en Excel, es posible que en más de una ocasión le hayan dejado de funcionar las macros grabadas, cosa que hace que muchos profesionales huyan de ellas. Con este libro descubrirá por qué falla el grabador de macros y aprenderá los pasos necesarios para convertir el código grabado en código que funcione todos los días y con cualquier conjunto de datos. Aunque se da por supuesto que conoce Excel, no es necesario que tenga experiencia en programación. Entres otros aspectos, con este manual conocerá la sintaxis VBA en un lenguaje fácil de entender, aprenderá a crear aplicaciones de Excel con cuadros de diálogo personalizados y ahorrará muchas horas automatizando tareas repetitivas. Introducción Obtener resultados con VBA Qué hay en este libro El futuro de VBA y las versiones de Excel para Windows Convenciones Código fuente 1. Descubrir la potencia de Excel con VBA El poder de Excel Barreras El grabador de macro no funciona Visual Basic no es como BASIC Lo bueno es que es fácil aprender Lo mejor es que Excel con VBA merece el esfuerzo Conozca sus herramientas: la ficha Programador Seguridad de macro Agregar una ubicación de confianza Configuración de macros para habilitar macros en libros que no están en ubicaciones de confianza Utilización de Deshabilitar todas las macros con notificación Visión de conjunto de grabar, almacenar y ejecutar una macro Completar el cuadro de diálogo Grabar macro Ejecutar una macro Creación de un botón de macro Asignar una macro a un control de formulario, un cuadro de texto o una forma Nuevos tipos de archivo en Excel 2007 Entender el editor de Visual Basic (VBE) Configuración del editor de VB El explorador de proyectos La Ventana Propiedades Comprensión de las deficiencias del grabador de macros Examinar el código en la ventana Programación Ejecutar la macro otro día produce resultados indeseados A continuación... 2. Esto suena como BASIC, pero ¿por qué no me es familiar? No entiendo este código Entender las partes del discurso VBA ¿Es VBA realmente tan difícil? ¡No! Archivos de ayuda VBA: utilizar F1 para encontrar algo Utilizar temas de ayuda Examinar código de macro grabada. Utilizar el editor y la ayudade Visual Basic Parámetros opcionales Constantes definidas Las propiedades pueden devolver objetos Utilizar herramientas de depuración para entender el código grabado Código paso a paso Más opciones de depuración: puntos de interrupción Desplazarse por el código No pasar por cada línea de código Realizar consultas mientras se pasa por el código Utilizar una inspección para establecer un punto de interrupción Utilizar una Inspección sobre un objeto La mejor referencia para todos los objetos, métodos y propiedades Cinco trucos para limpiar el código grabado Truco 1: No seleccione nada Truco 2: Comenzar el rango desde abajo para encontrar la última fila Truco 3: Utilizar variables para evitar tener que escribir filas y fórmulas Truco 4: Aprender a copiar y pegar en una sentencia Truco 5: Utilizar With...End With si realiza múltiples acciones en la misma celda o rango de celdas Ponerlo todo junto. Arreglar el código grabado A continuación... 3. Referencias a rangos El objeto Range Utilizar las esquinas superior izquierda e inferior derecha de una selección para especificar un rango Rangos con nombre Método abreviado para hacer referencia a rangos Hacer referencia a rangos en otras hojas Hacer referencia a un rango relativo a otro rango Utilizar la propiedad Cells para seleccionar un rango Utilizar la propiedad Cells en la propiedad Range Utilizar la propiedad Offset para hacer referencia a un rango Utilizar la propiedad Resize para cambiar el tamaño de un rango Utilizar las propiedades Columns y Rows para especificar un rango Utilizar el método Union para unir múltiples rangos Utilizar el método Intersect para crear un nuevo rango a partir de rangos solapados Utilizar la función IsEmpty para comprobar si una celda está vacía Utilizar la propiedad CurrentRegion para seleccionar rápidamente un rango de datos Utilizar la colección de áreas para devolver un rango no contiguo Referencias a tablas A continuación... 4. Funciones definidas por el usuario Crear funciones definidas por el usuario Compartir funciones definidas por el usuario Funciones personalizadas de utilidad en Excel Establecer el nombre del libro activo en una celda Establecer el nombre y la ruta de acceso al archivo del libro activo en una celda Comprobar si un libro está abierto Comprobar si una hoja existe en un libro abierto Contar el número de libros en un directorio Recuperar la identificación de usuario Recuperar fecha y hora de los últimos cambios Recuperar fecha y hora permanente Validar una dirección de correo electrónico Sumar celdas basándose en el color interior Contar valores únicos Eliminar duplicados de un rango Encontrar la primera celda con longitud distinta a cero en un rango Sustituir múltiples caracteres Recuperar números de texto mezclado Convertir número de semana en fecha Separar una cadena delimitada Ordenar y concatenar Ordenar caracteres numéricos y alfa Buscar una cadena dentro de texto Invertir los contenidos de una celda Múltiples máximos Devolver una dirección hipervínculo Devolver la letra de columna de una dirección de celda Aleatorio estático Utilizar Select Case en una hoja A continuación... 5. Bucles y control de flujo Bucles For??Next Utilizar variables en el parámetro For Variaciones en el bucle For...Next Salir de un bucle antes de que se cumpla una condición Anidar un bucle dentro de otro Bucles Do Utilizar la cláusula While o Until en bucles Do Bucles While??Wend El bucle de VBA: ForEach Variables de objeto Control de flujo: Utilizar If...Then...Else y Select Case Control de flujo básico: If??Then??Else Condiciones Decisiones o/o: If??Then??Else??End If Utilizar If??Else If??EndIf para múltiples condiciones Utilizar SelectCase??EndSelect para múltiples condiciones Expresiones complejas en parámetros Case Anidar parámetros If A continuación... 6. Fórmulas de estilo F1C1 Hacer referencia a celdas: referencias A1 vs. F1C1 Cambiar Excel para mostrar las referencias de estilo F1C1 El milagro de las fórmulas de Excel Escribir una fórmula una vez y copiarla mil veces El secreto: no es tan sorprendente Explicación del estilo de referencia F1C1 Utilizar F1C1 con referencias relativas Utilizar F1C1 con referencias absolutas Utilizar F1C1 con referencias mixtas Hacer referencias a columnas o filas enteras con estilo F1C1 Reemplazar muchas fórmulas A1 con una sola fórmula F1C1 Recordar números de columna asociados con letras de columna Formato condicional: F1C1 obligatorio Establecer formato condicional en la interfaz de usuario Establecer formatos condicionales en VBA Las fórmulas de tabla requieren formato condicional A continuación... 7. Novedades y cambios en Excel 2007 Si hay cambios en la parte más visible, hay cambios en VBA La cinta Gráficos Tablas dinámicas Formato condicional Tablas Clasificación SmartArt El grabador de macros no grabará acciones que grababa en versiones anteriores Aprender los nuevos objetos y métodos Modo de compatibilidad A continuación... 8. Creación y manipulación de nombres en VBA Nombres en Excel Nombres globales vs. locales Añadir nombres Eliminar nombres Añadir comentarios Tipos de nombres Fórmulas Cadenas Números Tablas Utilizar hileras en nombres Nombres reservados Ocultar nombres Comprobar la existencia de un nombre A continuación... 9. Programar eventos Niveles de eventos Utilizar eventos Parámetros de evento Activar eventos Eventos de libro Eventos de hoja Eventos de hoja de gráfico Gráficos incorporados Eventos a nivel de aplicación A continuación... 10. UserForm: Introducción Métodos de interacción de usuario Cuadros de entrada Cuadros de mensaje Crear un formulario de usuario Llamar y ocultar un formulario de usuario Programación de un formulario de usuario Eventos de formulario de usuario Programación de controles Utilizar controles básicos de formulario Utilización de etiquetas, cuadros de texto y botones de comando Decidir si utilizar cuadros de lista o cuadros combinados en sus formularios Añadir botones de opción a un formulario de usuario Añadir gráficos a un formulario de usuario Utilizar botón de número en un formulario de usuario Utilizar el control Página múltiple para combinar formularios Verificar que los campos se completan Cerrado ilegal de ventana Obtener un nombre de archivo A continuación... 11. Creación de gráficos Gráficos en Excel 2007 Creación de código para las nuevas características de Office 2007 Referencia a gráficos y objetos de gráfico en código VBA Creación de gráficos Especificar el tamaño y la ubicación de un gráfico Referencias posteriores a un gráfico específico Grabación de comandos desde las cintas Presentación y Diseño Especificar un tipo de gráfico predefinido Especificar una plantilla para un tipo de gráfico Cambiar Presentación o el estilo de un gráfico Usar SetElement para emular cambios en la ficha Presentación Cambiar el título de un gráfico con VBA Emular cambios en la cinta Formato Usar el método Format para acceder a las nuevas opciones de formato Usar la ventana Inspecciones para descubrir la configuración de objetos Creación de gráficos avanzados Creación de auténticos gráficos OHLC Creación de bins para gráficos de frecuencia Creación de un gráfico de área apilada Exportar un gráfico como archivo gráfico Creación de un gráfico dinámico en un formulario de usuario Creación de gráficos dinámicos A continuación... 12. Data Mining con filtro avanzado El filtro avanzado es más sencillo en VBA que en Excel Utilizar Filtro avanzado para extraer una lista única de valores Extraer una lista única de valores con la interfaz de usuario Extraer una lista única de valores con código VBA Obtener combinaciones únicas de dos o más campos Utilizar filtro avanzado con rangos de criterios Unir múltiples criterios con un operador lógico OR Unir dos criterios con un AND lógico Otros rangos de criterios algo complejos El criterio más complejo: reemplazar la lista de valores con una condición creada como resultado de una fórmula Utilizar un Filtro avanzado in situ No obtener ningún registro al utilizar un filtro in situ Mostrar todos los registros después de un filtro in situ Utilizar un filtro in situ con sólo registros únicos xlFilterCopy con todos los registros en lugar de sólo registros únicos Copiar todas las columnas Copiar un subconjunto de columnas y reordenar Autofiltros Habilitar Autofiltro con código Desactivar unos cuantos menús desplegables de Autofiltro Filtrar una columna con Autofiltros Seleccionar múltiples valores para un filtro Seleccionar un rango de datos dinámico con Autofiltros Filtrado basado en colores o iconos A continuación... 13. Utilización de VBA para crear tablas dinámicas Introducción a las tablas dinámicas Comprensión de las versiones Novedades en Excel 2007 Crear una tabla dinámica en la interfaz de Excel Entender las nuevas características de las tablas dinámicas en Excel 2007 Crear una tabla dinámica en Excel VBA Definición de la caché de tabla dinámica Creación y configuración de la tabla dinámica Obtener una suma en lugar de contar Saber por qué no se puede mover ni cambiar una parte de un informe dinámico Determinar el tamaño de una tabla dinámica acabada Creación de un informe que muestre los ingresos por producto Eliminar las celdas en blanco en el área de valores Garantizar el uso del diseño de tabla Controlar el orden con Autoordenar Cambiar el formato de número por defecto Suprimir subtotales para múltiples campos de fila Suprimir total general para filas Gestionar otros inconvenientes al crear el informe final Crear un nuevo libro para albergar el informe Crear un resumen en una hoja de informe en blanco Completar la vista esquema Gestionar el formato final Añadir subtotales Ponerlo todo junto Tratar con dos o más campos de datos Campos de datos calculados Resumir campos de fecha con agrupaciones Entender el método Group en VBA Agrupar por semana Medir el margen de tiempo de pedidos al agrupar dos campos de fecha Técnicas avanzadas de tabla dinámica Utilizar la característica Automostrar para generar vistas ejecutivas Utilizar ShowDetail para filtrar un conjunto de registros Crear informes para cada región o modelo Filtrar manualmente dos o más elementos de un campo dinámico Controlar el orden manualmente Suma, Promedio, Cuenta, Mín, Máx y otros Crear informes de porcentajes Porcentaje del total Porcentaje de crecimiento respecto al mes anterior Porcentaje de un elemento específico Mostrar la suma parcial Usar las nuevas características de las tablas dinámicas en Excel 2007 Utilizar los nuevos filtros Aplicar un estilo de tabla Cambiar la presentación desde la ficha Diseño A continuación... 14. El poder de Excel Operaciones con archivos Listar archivos en un directorio Importar CSV Leer el CSV de memoria y analizar Combinar y separar libros Separar hojas en libros Combinar libros Filtrar y copiar datos en hojas separadas Exportar datos a Word Trabajar con comentarios de celda Listar comentarios Cambiar el tamaño de los comentarios Cambiar el tamaño de los comentarios con centrado Situar un gráfico en un comentario Utilidades para cautivar a sus clientes Utilizar formato condicional para resaltar celdas seleccionadas Resaltar celdas seleccionadas sin utilizar formato condicional Transposición personalizada de datos Seleccionar/deseleccionar celdas no contiguas Técnicas para los profesionales de VBA Detalles en la tabla dinámica Agilizar la configuración de página Calcular la hora para ejecutar código Orden de ordenación personalizado Indicador de progreso de celda Cuadro de contraseña protegido Cambiar entre mayúsculas, minúsculas y otros Seleccionar con SpecialCells Menú contextual ActiveX Aplicaciones interesantes Cotizaciones históricas Utilizar VBA Extensibility para añadir código a nuevos libros A continuación... 15. Visualización de datos y formato condicional Introducción a la visualización de datos Nuevos métodos y propiedades de VBA para la visualización de datos Adición de barras de datos a un rango Adición de escalas de color a un rango Adición de conjuntos de iconos a un rango Especificar un conjunto de iconos Especificar rangos para cada icono Utilizar trucos de visualización Crear un conjunto de iconos para un subconjunto de un rango Usar dos colores de barra de datos en un rango Utilización de otros métodos de formato condicional Dar formato a celdas que están por encima o por debajo de una media Dar formato a las celdas de los 10 mejores o los 5 peores Dar formato a celdas únicas o duplicadas Dar formato a las celdas basándose en su valor Dar formato a las celdas que contienen texto Dar formato a celdas que contienen fechas Dar formato a las celdas que contienen espacios o errores Usar una fórmula para determinar las celdas a las que dar formato Usar la nueva propiedad NumberFormat A continuación... 16. Leer y escribir en la Web Obtener datos de la Web Crear manualmente una consulta Web y refrescarla con VBA Utilizar VBA para actualizar una consulta Web existente Crear una nueva consulta con VBA Utilizar datos continuos Utilizar Application.OnTime para analizar datos periódicamente Los procedimientos programados requieren un modo Listo Especificar una ventana de hora para una actualización Cancelar una macro programada anteriormente Cerrar Excel cancela todas las macros programadas pendientes Programar una macro para que se ejecute x minutos en el futuro Programar un recordatorio verbal Programar una macro que se ejecute cada dos minutos Publicar datos en una página Web Utilizar VBA para crear páginas Web personalizadas Utilizar Excel como un sistema de gestión de contenidos FTP desde Excel Confiar en contenido Web A continuación... 17. XML en Excel 2007 ¿Qué es XML? Reglas XML Formato de archivo universal XML como el nuevo formato de archivo universal El alfabeto de XML Uso de XML como tipo de archivo por parte de Microsoft Cómo guarda Excel 2007 los libros con XML A continuación... 18. Automatizar Word Vinculación previa Error de compilación: No se encuentra objeto o biblioteca Vinculación posterior Crear y hacer referencia a objetos Palabra clave New Función CreateObject Función GetObject Uso de valores constantes Usar la ventana Inspección para recuperar el valor real de una constante Usar el Examinador de objetos para recuperar el valor de una constante Comprender los objetos de Word Objeto Document Objeto Selection Objeto Range Marcadores Controlar los campos de formulario de Word A continuación... 19. Tablas Declarar una tabla Tablas multidimensionales Completar una tabla Vaciar una tabla Las tablas facilitan la manipulación de datos, pero ¿es eso todo? Tablas dinámicas Pasar una tabla A continuación... 20. Procesamiento de archivos de texto Importar a partir de archivos de texto Importar archivos de texto con menos de 1.048.576 filas Leer archivos de texto con más de 1.084.576 filas Escribir archivos de texto A continuación... 21. Utilización de Access como aplicación de soporte para mejorarel acceso multiusuario a los datos ADO vs. DAO Las herramientas de ADO Añadir un registro a la base de datos Recuperar registros de la base de datos Actualizar un registro existente Eliminar registros vía ADO Resumir registros vía ADO Otras utilidades vía ADO Comprobar la existencia de tablas Comprobar la existencia de un campo Añadir una tabla en el momento Añadir un campo en el momento A continuación... 22. Creación de clases, registros y colecciones Insertar un módulo de clase Capturar eventos de aplicación y de gráfico incorporado Eventos de aplicación Eventos de gráfico incorporados Crear un objeto personalizado Utilizar un objeto personalizado Utilizar Property Let y Property Get para controlar cómo los usuarios utilizan objetos personalizados Colecciones Crear una colección en un módulo estándar Crear una colección en un módulo de clase Tipos definidos por el usuario A continuación... 23. Técnicas avanzadas de formulario de usuario Utilizar la barra de herramientas UserForm en el diseño de controles en formularios de usuario Más controles de formulario de usuario Casillas de verificación Barra de tabulaciones RefEdit Botones de alternar Utilizar una barra de desplazamiento como deslizador para seleccionar valores Controles y colecciones Formularios de usuario sin modo Utilizar hipervínculos en formularios de usuario Añadir controles en tiempo de ejecución Cambiar el tamaño de un formulario de usuario en el momento Añadir un control en el momento Cambiar el tamaño en el momento Añadir otros controles Añadir una imagen en el momento Ponerlo todo junto Añadir ayuda a los formularios de usuario Teclas de método abreviado Colorear el texto de ayuda Crear el orden de tabulación Colorear el control activo Formularios transparentes A continuación... 24. Interfaz de Programación de Aplicaciones (API) de Windows ¿Qué es la API de Windows? Entender una declaración API Utilizar una declaración API Ejemplos API Recuperar el nombre del equipo Comprobar si hay un archivo de Excel abierto en una red Recuperar información de resolución de pantalla Cuadro de diálogo Acerca de personalizado Desactivar la X para cerrar un formulario de usuario Cronómetro Reproducir sonidos Recuperar la ruta de acceso de un archivo Encontrar más declaraciones API A continuación... 25. Gestionar errores ¿Qué sucede cuando ocurre un error? Depurar errores dentro de código de formulario de usuario es engañoso Gestión básica de error con la sintaxis On Error GoTo Rutinas genéricas de gestión de error Gestionar errores ignorándolos Suprimir avisos de Excel Encontrar errores a propósito Formar a los clientes Errores durante el desarrollo vs. errores meses más tarde Error 9 en tiempo de ejecución: Subíndice fuera del intervalo Error 1004 en tiempo de ejecución: Error en el método ´Range´ de objeto ´_Global´ Las dolencias de proteger código Más problemas con contraseñas Errores causados por versiones diferentes A continuación... 26. Personalización de la cinta de opciones para ejecutar macros Dejar paso a lo nuevo Dónde añadir el código: archivo y carpeta customui Creación de una ficha y un grupo Añadir un control a la cinta Acceso a la estructura del archivo Comprensión del archivo RELS Renombrar el archivo de Excel y abrir el libro RibbonCustomizer Utilización de imágenes en botones Iconos de Microsoft Office Imágenes de iconos personalizadas Solución de errores El atributo "nombre del atributo" del elemento "nombre del elemento" no está definido en DTD o en el esquema Carácter de nombre completo no válido El elemento "nombre de la etiqueta" no es el esperado según el modelo de contenido del elemento primario "nombre de la etiqueta" Excel ha encontrado contenido que no se puede leer Número de argumentos erróneo o asignación de propiedad no válida No pasa nada Otras formas de ejecutar una macro Método abreviado del teclado Anexar una macro a un botón de comando Anexar una macro a un control ActiveX Ejecutar una macro desde un hipervínculo A continuación... 27. Creación de complementos Características de los complementos estándar Convertir un libro Excel en un complemento Utilizar Guardar como para convertir un archivo en un complemento Utilizar el editor de VB para convertir un archivo en un complemento Hacer que un cliente instale el complemento Los complementos estándar no son seguros Cerrar complementos Eliminar complementos Utilizar un libro oculto como alternativa para un complemento A continuación... ??ndice alfabético