domingo, 25 de noviembre de 2018

Insertar fórmulas con macros

En muchas ocasiones los usuarios/as de Excel desean insertar fórmulas a través de VBA; para este fin hay varias propiedades del objeto “Range” que nos pueden ayudar. Aunque hay más, vamos a centrarnos, en esta ocasión, en cuatro:

- Formula

- FormulaLocal

- FormulaR1C1

- FormulaR1C1Local

En el caso de la propiedad “Formula”, toma como base el idioma primigenio del VBA, es decir, el inglés; además de eso toma como separador de lista la coma (,). Estos detalles son muy importantes a tomar en cuenta cuando insertemos una fórmula en una celda ya que significa que, sea cual sea el idioma de nuestro Office, debemos colocar la función en inglés. Asimismo, aunque nuestro separador de lista sea el punto y coma (;) deberemos usar la coma (,) en la macro. En todos los casos, el VBA detectará el idioma del Office y el separador de lista del sistema operativo, y en la celda colocará el adecuado. Podríamos decir que el inglés y la coma (,) son “universales” cuando de la propiedad “Formula” hablamos. Esto se entenderá mejor con un ejemplo. En mi portátil tengo el Office en Castellano y mi separador de lista es el punto y coma (;) pero insertaré fórmulas del siguiente modo:

Range(“A11”).Formula="=Sum(A1:A10)"
Range("G16").Formula = "=VlookUp(F16,B25:C29,2,1)" 

En las respectivas celdas tendremos las siguientes fórmulas:

=Suma(A1:A10)
=BuscarV(F16;B25:C29;2;1) 

Cuando usamos la propiedad “FormulaLocal”, al contrario, debemos usar las funciones en el idioma de nuestro Office y el separado de lista de nuestro sistema operativo. Por ejemplo, en mi portátil tendría que colocar así:

Range(“A11”).FormulaLocal="=Suma(A1:A10)"
Range("G16").FormulaLocal = "=BuscarV(F16;B25:C29;2;1)" 
La desventaja, para mí, es que solo será útil en computadoras con Office en nuestro mismo idioma y con el mismo separador de lista; en otros casos insertará las fórmulas con errores.

En el caso de la propiedad “FormulaR1C1”, vamos a explicar su uso con uno de los ejemplos que ya hemos usado. Insertaremos en la celda “A11” la fórmula "=Suma(A1:A10)" del siguiente modo:

 Range("A11").FormulaR1C1 ="=SUM(R[-10]C:R[-1]C)"

Tal como con la propiedad “Formula”, en el caso de “FormulaR1C1” también se usan las funciones en inglés y el separador de lista debe ser la coma (,). Las referencias R1C1 deben ser entendidas del siguiente modo, para este caso:

- La celda de referencia sería en este caso la “A11”

- “R[-10]” hace referencia a 10 filas arriba de “A11”, es decir la fila 1

- “C” hace referencia a la misma columna, es decir a la “A”

- “R[-1]” hace referencia a una fila arriba de “A11”, es decir la fila 10

- “C”, nuevamente, hace referencia a la misma columna, es decir a la “A”

Con la propiedad “FormulaR1C1Local”, se usa la función en el idioma del Office y el separador de lista del sistema operativo y las referencia a filas cambia de “R” a “F”. Para insertar la misma fórmula del ejemplo anterior sería así:

Range("A11").FormulaR1C1Local ="=Suma(F[-10]C:F[-1]C)"

Y eso es todo en esta ocasión, espero se haya entendido.. Hasta la próxima.

Abraham Valencia

martes, 30 de octubre de 2018

Exportar rangos como archivos de imagen

En los foros, en mucha ocasiones, los usuarios desean convertir algún rango de sus hojas en un archivo de imagen por diversos motivos. Si bien Excel no tiene una herramienta propia que logre convertir un rango y exportarlo como archivo de imagen de modo automático, a través de VBA podemos lograrlo.


Para lograrlo vamos a comentar que es lo que haremos al estilo herramientas de Excel. Primero vamos a copiar el rango que deseamos tener como imagen y aprovecharemos que a través de “Pegado especial” dicho rango copiado se puede pegar justamente como imagen pero ello lo haremos dentro de un objeto “Chart” ¿por qué? Pues los objetos “Chart” tienen el método “Export” que nos permitirá exportar el nuevo objeto como un archivo de imagen propiamente dicho. Entonces, manos a la obra.
Vamos a suponer que el rango que necesitamos exportar va de la celda “A1” a la “D5”:


Para obtener la imagen de ese rango, vamos a usar las propiedades Top (Límite superior), Left (límite izquierdo), Width (Ancho) y Height (Alto) del rango que necesitamos. Previamente declararemos las variables, obtendremos los datos requeridos y usaremos el método “CopyPicture” para copiar el rango deseado.
Dim Izquierda As Single, Arriba As Single, Ancho As Single, Alto As Single 
Application.DisplayAlerts = False
Application.ScreenUpdating= False 
With Worksheets("Hoja1").Range("A1:D5")
  Izquierda = .Left
  Arriba = .Top
  Ancho = .Width
  Alto = .Height
       .CopyPicture 
End With  
Para evitar las alertas del Excel las hemos desactivado así como hemos desactivado las actualizaciones de pantalla.
Luego lo que haremos es crear un objeto “Chart” cuyas características de tamaño serán similares al rango de nuestro interés (lo lograremos a través de las variables usadas), pegaremos en él la imagen y con el método “Export” exportaremos la imagen hacia la ruta y con el nombre que deseemos. Por último con el método “Delete” borraremos el gráfico una vez ya exportado.
With Worksheets("Hoja1").ChartObjects.Add(Izquierda, Arriba, Ancho, Alto)
  .Chart.Paste
   .Chart.Export "D:\Temporal.jpg"
  .Delete 
End With 
No olvides reemplazar el rango por el tuyo, los nombres de la hoja por las tuyas y por supuesto la ruta y nombre del archivo también tienes que adaptarlas. Eso es todo por hoy.
Abraham Valencia

lunes, 15 de octubre de 2018

¿Excel como base de datos?

Microsoft Excel es una hoja de cálculo y según los datos conocidos sigue siendo el programa más usado de la famosa suite Microsoft Office. Millones, de personas usan Excel como base de datos y muchos se apoyan en VBA pasa simular tener sus propios sistemas de venta, de facturación, etc. ¿Está mal eso? No necesariamente, aunque finalmente para una misma cantidad de datos Excel ocupará más espacio que un programa como Access, que es parte de la misma suite y que en realidad es el programa que debería usarse, de preferencia, para cosas como las mencionadas.

Cuando finalmente se opta por Excel como base de datos, es muy habitual que se cometan muchos errores que al final perjudican a los propios usuarios. Lo clásico es que para una misma base de datos se usan diferentes archivos. En muchas ocasiones, por ejemplo, para ventas o compras, he visto cosas de este tipo, en donde tienen un archivo por año:

Y dentro de esos archivos tienen normalmente una hoja por mes::

Siendo todos los archivos iguales. Por supuesto que cada hoja tiene los mismos campos. Otro ejemplo:

Entonces, se puede tener, para tres años entre ventas y compras, unos seis archivos y en total unas 72 hojas ¿una bicoca, cierto? Aunque no es nada comparado a casos como este:

En este caso, para tres años tendríamos 72 archivos y 864 hojas ¿qué les parece? Y eso contando solo hojas de datos cuando sabemos bien que muchos tienes además hojas para facturar, reportes, etc. Ahora, imagínense intentar hacer consolidados, ver ventas o compras por mes o año o entre fechas, o ver datos solo de ciertos clientes, etc. Hay que copiar/pegar datos, abrir y cerrar archivos, ubicar cada archivo necesario, etc. generando muchas veces perdida de tiempo, perdida de datos, archivos que se pueden ir corrompiendo y varias otras cosas más.

Entonces, idealmente ¿cómo se tendría que hacer? Pues en general es suficiente con un solo archivo y con una hoja para los datos, podría colocarse un campo para el tipo (venta o compra) y una columna de fechas para que de ese modo con filtros o tablas dinámicas (u otra herramienta) se puedan tener datos específicos por periodos de tiempo o clientes.

Les aseguro que de ese modo les será mucho más fácil manejar sus datos y en otra hoja pueden hacer sus reportes usando quizá VBA o funciones tipo “BuscarV”, aunque recordemos que Excel, a diferencia de Access, no tiene una herramienta automática para generarlos por lo que habrá que usar bastante el ingenio (y por supuesto leer mucho).

Espero haberlos ayudado. Hasta la próxima.

Abraham Valencia

domingo, 30 de septiembre de 2018

Microsoft Excel 2019

Microsoft Office 2019 es la nueva versión de la suite de Microsoft Office. Dicha suite fue anunciado el 6 de septiembre del 2017 y ha sido lanzada ya oficialmente el día 24 de septiembre de 2018.


En el caso de Excel se han incluido nuevas funciones, más tipos de gráficos,  elementos visuales mejorados, mejoras en Power Pivot y varias cosas más. Otra de las novedades más es esperadas son las anunciadas "Funciones Personalizadas" que son distintas a las que se crean con VBA, ya que la base será JavaScript y además estarán sincronizadas entre todos los dispositivos en donde se tenga instalado el Excel.


Eso sí, la nueva suite solo podrá ser usada en Windows 10 (y en Mac OS) y no en versiones anteriores por lo que si deseas utilizarla no te quedará de otra que actualizar tu sistema operativo.

Entonces, ya lo saben, a prepararse para la nueva versión de Excel.

Eso es todo por ahora pero estaremos atentos a más novedades. Hasta la próxima.

Abraham Valencia