Utilizando Arrays en VBA – Parte 1

Estándar

Muchos de los estudiantes de programación se sienten intimidados al escuchar la palabra Array (matriz) y evitan en muchos casos el uso de los mismos en sus proyectos, pero esto se debe al poco o mal conocimiento de los mismos. No se diga si se tiene que programar en C, C++ o Java. En estos lenguajes uno puede quedar frustrado al intentar hacer uso de los Arrays. Sin embargo existen otros lenguajes donde usar Arrays es cosa de niños (fácil e intuitivo).

Para el caso de VBA Excel se está en un punto medio, no es tan difícil hacer uso de ellos y realmente pueden hacer que algunas macros tarden tan poco tiempo, el problema de los Arrays en VBA es que existen pocas herramientas a nivel programación que nos brinda el lenguaje para poder procesarlos fácil y rápidamente.

¿Pero que es un array?, ¿Para qué sirve?  y  ¿Cómo se usan?. Antes que nada un array es un tipo de variable como lo es un integer, un string pero con la capacidad especial de poder albergar más de una variable y en el caso de VBA dichas variables pueden ser de distinto tipo.

Todos aquellos que han usado Excel, sean o no programadores ya han trabajado con una matriz o array, pero por supuesto!. Una hoja de cálculo no es más que una matriz (array) bidimensional de más de un millón de filas y poco más de 16 mil columnas (en el caso de Excel 2007 en adelante) y como todos sabemos podemos introducir un número, fecha o texto dentro de una celda, lo mismo es aplicable a los arrays en VBA.

En la próxima entrega empezaremos a trabajar de manera eficiente con los arrays, veremos más propiedades de los mismos y las herramientas que nos brinda VBA para su manipulación.

Anuncios

Cinta de opciones (RibbonX)

Estándar

Como es bien sabido a partir de la versión 2007, Microsoft agrego la cinta de opciones al Office, incluyendo a Excel, a muchos les disgusto el cambio ya que para entonces se podía ocultar, modificar y manipular la barra de menús y herramientas que proporcionaba Excel por medio de VBA. La cinta de opciones desplazo a la barra de menús y herramientas desde entonces.

Aun se puede recurrir a VBA para crear barras de menús y herramientas dentro de la cinta opciones pero no se aprovecha al máximo las prestaciones y personalización de la nueva cinta. Para poder programar la cinta y sacar ventaja de la misma en nuestras macros o aplicativos se puede utilizar Visual Studio u otras herramientas.

En este artículo no explicare como se programa la cinta, si no que les comparto el libro: “RibbonX for Dummies ” el cual explica y brinda números ejemplos para acceder y manipular la cinta de opciones.

MXUtilities Ver 1.5

Estándar

Estoy de regreso con la versión 1.5 del complemento MXUtilities a la cual se le han corregido algunos bugs para Excel 2013. Como novedad este complemento incorpora tres nuevas funcionalidades:

  1. Poder prorratear un total en un conjunto contiguo de celdas
  2. Poder prorratear una cantidad en un conjunto de celdas visibles
  3. Poder descargar el audio en formato MP3 de algunos videos de Youtube

Las dos primeras utilidades serán de gran ayuda para todos aquellos que trabajen en contabilidad y finanzas. Cabe mencionar que se debe ser cuidadoso ya que al hacer uso de estas do herramientas no se podrán deshacer los cambios.

La tercera será de gran entretenimiento para todos aquellos que deseen descargar una canción de su gusto a formato MP3 desde Youtube. Esta utilidad puede que no funcione para todos los videos y solo se pueden descargar como máximo 7 minutos de audio.

MXUtilities Ver 1.5

Utilizar Access como aplicación de soporte para mejorar el acceso multiusuario a los datos

Estándar

Desde la versión 2007 de Excel, se cuenta con más de un millón de filas, una mejora sustancial de la versión 2003 donde solo se contaban con 65 mil, pero aunque parezca que un millón puede ser suficiente, en algún momento se tiene que admitir que, aunque Excel es el mejor producto del mundo, ha llegado el momento de pasarse a Access y aprovecharse de los archivos ACCDB de Access.

Incluso antes de que tenga que se tenga un millón de filas, otra razón irresistible para utilizar archivos de datos ACCDB es permitir el acceso multiusuario a los datos sin los dolores de cabeza asociados a los libros compartidos.

Excel ofrece una opción para compartir un libro, pero automáticamente pierde un número de importantes características Excel cuando lo hace. Después de compartir un libro, no se puede utilizar subtotales automáticos, tablas dinámicas, agrupaciones, escenarios, protección, autoformato, estilos, imágenes, añadir gráficos o insertar hojas.

Al utilizar la interfaz de Excel VBA y almacenar los datos en una base de datos ACCDB, tiene lo mejor de dos mundos. Tiene la potencia y flexibilidad de Excel y la posibilidad de acceso multiusuario disponible de Access.

ACCDB es el nuevo formato de archivo oficial tanto de Microsoft Access como de Microsoft Visual Basic (en versiones anteriores, el formato era MDB). Esto significa que se puede desarrollar una solución Excel que lea y escriba de un ACCDB para clientes que no tengan Microsoft Access. Por supuesto, ayuda si como desarrollador tiene una copia de Access, porque puede utilizar la interfaz de Access para configurar tablas y consultas.

Caso Práctico

Linda y Virginia son dos compradoras de una cadena minorista de tiendas. Cada mañana importan datos de las cajas registradoras para obtener la información actual de ventas y el inventario para 2000 referencias. Durante el día, cualquier compradora puede incorporar transferencias de inventario de una tienda a otra. Sería ideal si Linda pudiera ver las transferencias pendientes incorporadas por Virginia y viceversa.

Cada compradora tiene una aplicación Excel con VBA ejecutándose en su escritorio. Ambas importan los datos de la caja registradora y tienen rutinas VBA que facilitan la creación de informes de tabla dinámica que les ayudan a tomar decisiones de compra.

Tratar de almacenar los datos de la transferencia en un archivo Excel común genera problemas. Cuando la compradora trata de escribir en el archivo Excel, el archivo se convierte en sólo lectura para la otra compradora. Con un libro compartido, Excel deshabilita la posibilidad de crear tablas dinámicas y esto es necesario en nuestra aplicación.

Ni Linda ni Virginia cuentan con la versión profesional de Office, por lo que no tienen Access ejecutándose en sus PC.

La solución es generar una base de datos Access en un disco de red que puedan ver tanto Linda como Virginia.

La aplicación proporciona acceso multiusuario a ambas compradoras. Tanto Linda como Virginia pueden leer y escribir en la tabla al mismo tiempo. El único momento en el que podría ocurrir un conflicto es si ambas tratan de actualizar el mismo registro al mismo tiempo.

MXUtilities Ver 1.4

Estándar

En el post “MX Utilities – El complemento de Excel que hace el trabajo más fácil” explico la idea de este complemento. Ahora les comparto la nueva versión la cual ofrece un menú más amigable para el usuario e incorpora nuevas herramientas para la gestión y manipulación de hojas de un libro.

mxutilities001

En breve espero estar compartiendo video tutoriales de lo que se puede hacer con este magnífico y práctico complemento que hace el trabajo más fácil.

Les comparto el enlace para la descarga del MXUtilities.

Utilidades para el programador VBA/Excel

Estándar

Todo buen programador debe evitar perder el tiempo en actividades repetitivas que fácilmente podrían realizarse o automatizarse de forma fácil y práctica, suena contradictorio leer esto. ¿Un programador haciendo cosas repetitivas y redundantes? Si, los programadores somos humanos después de todo y a veces no nos damos cuenta de que no solo se trata de estar codificando líneas y líneas de código, más aun si ya sabemos lo que queremos implementar y como funciona dicha implementación.

Es aquí donde debemos echar mano de los módulos y módulos de clase, los primeros nos servirán para agrupar código y el segundo igual pero con la ventaja de poder crear objetos desde cero o personalizar los ya existentes. Empecemos por ver el uso de los módulos. Siempre hemos usado los módulos estándar, es así donde reside el código de nuestras macros, tanto el creado por la grabadora de macros o el codificado manualmente.

En mi caso personal, decidí crear un módulo que contuviera funciones y procedimientos de uso general que pudieran ahorrarme el tener que escribir las mismas líneas de código una y otra vez en cada proyecto y si me funcionan a mi le pueden a funcionar a cualquier otro programador.

Al módulo decidí llamarlo “Utilities” y dentro de él residen por el momento 5 funciones y un procedimiento los cuales como programador me simplifican el trabajo, las cuales son:

  • IsInternetConnected

Validar si se cuenta con conexión a internet

  • IsFormLoaded

Validar si un formulario se encuentra cargado en memoria

  • SendMail

Enviar correo por medio de Outlook

  • MonthsNames

Devuelve el nombre de los meses del año

  • DictionaryFromRange

Crear diccionario a partir de los datos de dos rangos

  • MatrizFromDictionary

Reemplaza el contenido de una matriz con el contenido de un diccionario

Para poder hacer uso de estas herramientas en cada uno de sus proyectos, bastara con importar el modulo “Utilities”. Les comparto el libro con los ejemplos de cada una de las funciones.

Creación personalizada de mapa en VBA/Excel

Estándar

Hace algunos meses en el trabajo me asignaron la tarea de construir unos reportes de ventas por región. En mi compañía se divide al país (México) en seis regiones. Uno de mis compañeros se basó en la siguiente herramienta para poder construir reportes previos por región:

http://analisisydecision.es/trucos-excel-mapa-de-mexico-por-estados/

Era muy buena herramienta, pero mi compañero solo me estrego las autoformas y yo tenia que seleccionar todos los estados que conformaran una región para posteriormente pintarlos de un color, así sucesivamente con cada región, francamente no es nada cómodo ir seleccionando estado por estado agrupar y pintar así que se me ocurrió la idea de en una hoja tener el id de cada estado con su nombre y el color que le corresponda y ya después al terminar de asignar colores dar clic en un botón y por arte de magia se construye un mapa de México personalizado como cada quien desee.

Los usos para esta macro pueden ser variados (principalmente para todas aquellas empresas mexicanas), los cuales pueden ser:

  • Identificar estados de acuerdo su venta
  • Identificar estados con mayor rotación o días de inventario de un producto
  • Agrupar regiones
  • Destacar estados o grupos de estados de otrossadsa

Los anteriores son los que se me ocurren a mí pero estoy seguro que pueden ocurrírseles decenas de usos más en su trabajo diario.

Les comparto la macro con el código desprotegido para su consulta o modificación.