En memoria de "Chip" Pearson

No conocí personalmente a Charles "Chip" H. Pearson, pero soy uno de los miles que recurrió a su página web para absolver alguna duda sobre VBA de Excel. Si mal no recuerdo la primera vez que lo leí fue el año 2006 y desde ese entonces use muchos de los enlaces a sus ejemplos y explicaciones para ayudar a guiar a muchas personas en este apasionante mundo del Excel que compartíamos.

Fue nombrado en 16 oportunidades, por Microsoft, como Most Value Professional (MVP) de Excel, galardón sin duda más que merecido. Creo que es uno de los expertos en Excel anglo parlantes más conocido por nosotros los hispano parlantes. Hace muy pocos días quise hace runa consulta en su web, como tantas veces hice, y estaba “fuera de línea”, realmente pensé que estaba migrando de “Hosting” o actualizando su web, o haciendo alguna copia de seguridad, no sé, algo de eso, jamás pensé en otra cosa. Hoy a través del Facebook de mi querido amigo Sergio Alejandro Campos (MVP Excel – México) me enteré que el gran “Chip” sufrió un accidente automovilístico hace unos días y que el día 19 de este mes falleció producto de las heridas sufridas. Una gran pérdida para su familia, pero también para esta gran familia mundial del Excel de la que muchos somos parte. Que en paz descanse el gran “CPearson”.

DESREF: Función "poderosa" y poco conocida

La función DESREF de Excel es, desde mi punto de vista, quizá una de las que podría ser más útil en cosas habituales pero aun así no es de las más usadas. Pero ¿qué hace y/o cómo trabaja dicha función? Microsoft define dicha función del siguiente modo: “Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver”.

¿Se entendió esa definición? ¿No tanto? Bueno, continuemos hasta entenderla completamente. Microsoft también nos dice que la sintaxis de la función es la siguiente:

DESREF (ref, filas, columnas, [alto], [ancho])

Asimismo, la definición de los argumentos de la función son los siguientes:

Nombre	Descripción
Ref	Argumento obligatorio. Es la referencia en la que desea basar la desviación. La referencia debe referirse a una celda o un rango de celdas que son adyacentes entre ellas.
Filas	Argumento obligatorio. Es el número de filas, hacia arriba o hacia abajo, al que se desea hacer referencia tomando como base la celda del argumento “Ref”. Filas hacia abajo se señala en positivo y hacia arriba en negativo.
Columnas	Argumento obligatorio. Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia tomando como base la celda del argumento “Ref”. Columnas hacia la derecha se señala en positivo y hacia la izquierda en negativo.
Alto	Argumento opcional. Es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo.
Ancho	Argumento opcional. Es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.

 

Es importante comentar que si en el argumento “Ref” usamos un rango de dos celdas o más, y usamos la función “DESREF” como única fórmula, no dará como resultado un error del tipo #¡VALOR!. Otra cosa a resaltar es que si alguno de los argumentos de la función se “salen” de la hoja, es decir que hacen referencia a filas menores a uno o columnas “menores” a la “A”, pues dará como resultado el error #¡REF!.

Comencemos con ejemplos simples para que se entienda como funciona:

Si bien las fórmulas están en las celdas de la columna “E”, las he colocado en texto en la columna “F” (en las celdas adyacentes correspondientes de la derecha) para que se aprecie más fácilmente. Las referencias son a una sola celda y a la posición, usando “Filas” y “Columnas”, de la que se requiere obtener el dato.

En este caso tomamos también una sola celda como referencia pero “mantenemos” dicha celda (Argumentos “Filas” y “Columnas” en cero – 0) y lo que hacemos es usar los argumentos “Alto” y “Ancho” para indicarle a la función “Suma” que es un rango más amplio que la referencia el que queremos usar.

En este caso, tenemos como referencia un rango de dos columnas de ancho y tres filas de alto, en el primer caso hacemos referencia a dos filas abajo y tres columnas a la izquierda y al no usar los argumentos “Alto” y “Ancho”, la referencia inicial mantiene su tamaño (dos columnas de ancho y tres filas de alto) por lo que el rango sumado corresponde a “B3:C5”. Para que quizá se entienda mejor, es como desplazar todo el rango inicial de referencia sin cambiarle el tamaño.

Con esos mismos datos, miren la segunda fórmula, solo suma el rango “B3:B4” dado que sí hemos usado los argumentos “Alto” y “Ancho” con los valores 2 y 1 respectivamente, cambiando, por decirlo de un modo, el tamaño inicial.

¿Hasta el momento no les convencen los ejemplos? Bueno, veamos algo quizá más habitual y/o práctico. Vamos a suponer que queremos usar una lista desplegable de validación que constantemente incrementa sus registros/elementos/datos y en cada ocasión tenemos que agregar más celdas o datos al origen, entonces, para evitar eso usaremos “DESREF”.

Supongamos que la lista está en la columna “A”, entonces vamos en la pestaña “Fórmulas” al grupo “Nombres definidos”, le damos “Click” a “Asignar nombre”, en “Nombre” colocamos “Mi_Lista” (o el nombre que quieran – sin las comillas) y en “Se refiere a” vamos a ingresar la siguiente fórmula: “=DESREF($A$1,0,0,CONTARA($A:$A),1)” (sin las comillas, no se olviden). Ahora van a la celda en donde desean la lista desplegable y en la pestaña “Datos”, en el grupo “Herramientas de datos” le dan “Click” al botón “Validación de datos”, eligen la pestaña “Configuración”, en “Permitir” eligen “Lista” y en “Origen” colocan “=Mi_Lista” (o el nombre que hayan elegido, y sin las comillas). Como resultado, cada vez que agreguemos datos en la columna “A”, dichos datos se agregaran automáticamente en la lista desplegable.

Espero haberlos ayudado. Hasta la próxima.

Abraham Valencia

¿El final de Excel está cerca? Para nada.

Fue aproximadamente el año 2007 cuando empecé a escuchar los primero rumores, en redes, acerca de la desaparición de Microsoft Excel. Para muchas personas el cambio radical que hubo entre las versiones de Excel 2003 y Excel 2007 era ya de por sí un indicio a pesar de la ventaja que fue para muchos el incremento en el número de filas y columnas. Lo mismo se dijo del VBA pues Microsoft había anunciado ya años antes que la versión 6.3 (la de Excel 2003 y 2007) no sería renovada/actualizada es decir, sería la última. Un hecho que “alertó” más a los usuarios de Excel sobre lo que creían sería el final del Visual Basic For Application (VBA) y de forma posterior del Excel es que Microsoft decide no incluir VBA en la versión de su suite 2008 para Mac.

Otro hecho que no pasaba desapercibió para los usuarios de Excel y su VBA es la aparición de Visual Studio Tools For Office (VSTO) en el año 2003. Si bien desde Microsoft se comentaba que VSTO no había sido desarrollado como sustituto de VBA ,sino como un conjunto de herramientas de desarrollo disponibles en forma de complemento de Visual Studio que permiten crear aplicaciones de Office que se alojen en .NET Framework Common Language Runtime (CLR) para exponer su funcionalidad a través de .NET; para muchos el “marketing” aplicado por Microsoft sobre dicho paquete, era otro indicio de que VSTO era el reemplazo de VBA tal como Visual Basic Net lo fue de Visual Basic “clásico”.

A pesar de todo Office trajo la novedad, entre otras, de que la versión de VBA que incluía era la 7.1, es decir, a pesar del anuncio de años atrás, Microsoft había decidido renovar dicho entorno de programación. A esto se le sumó que la versión de Office 2011 para Mac volvió a incluir VBA.

Con los años Office ha ido incluyendo nuevas funciones y herramientas como Power Query, Power Pivot y Power Viewer que maximizan, por ejemplo, el trabajo con datos a través de Excel. Asimismo, se calcula que para el año 2017, a nivel mundial Excel contaba con entre 750 y 1000 millones de usuarios, lo que lo deja muy lejos de ser un programa que va camino al olvido. Es más, incluso a nivel de celulares/móviles se calcula que ese mismo año ya existían 120 millones de usuarios. Y si mencionamos los foros de Excel, definitivamente siguen siendo de los más visitados y activos que hay en el mundo virtual.

Bueno, después de todo esto yo considero que Excel no está ni medianamente cerca de su fin, así como tampoco el VBA tampoco; Microsoft, a pesar de los antiguos rumores, no parece estar interesado en desaparecer dicho programa, o no aún al menos. Solo un último comentario, si bien VSTO no debe ser visto como el reemplazo de VBA, si considero que podría usarse más y justamente ayudaría a que el desarrollo de aplicaciones de Excel con programación “protegida”, como siempre se expresan desean muchos, sea más distribuido.

Hasta la próxima.

Abraham Valencia

Exportando archivos Excel a PDF

A pesar que desde Office 2007 (como complemento en dicha versión), Microsoft ha incluido la posibilidad de exportar en formato PDF sus archivos, es una pregunta muy frecuente en los foros de Excel la forma en la cuál se puede hacer mediante macros (VBA). Quizá la lógica nos diga a algunos/as que bastaría obtener dicho código usando la grabadora de macros con lo que tendríamos algo así:

Sub Macro1() 
'
' Macro1 Macro
' 
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _       
  "C:\Users\Abraham\Desktop\Libro1.pdf", Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ 
  False 
End Sub 

Como se puede ver lo que se ha hecho es exportar la hoja activa (ActiveSheet) haciendo uso del método “ExportAsFixedFormat” y después vemos una serie de parámetros que la grabadora no nos explicará y que justamente ayudaremos a entender.

La web MSDN nos muestra la siguiente sintaxis para el método:

Expresión.ExportAsFixedFormat (Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)

Para usar dicho método es obligatorio incluir la “Expresión” que puede ser cualquiera de estos tipos de objeto: Workbook , Sheet , Chart o Range. Entonces, podemos mandar a exportar en pdf el libro completo, hojas, gráficos e incluso solo un rango especifico. Ejemplos:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ 
  "C:\Users\Abraham\Desktop\Libro1.pdf" 

Range("A1:C2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  "C:\Users\Abraham\Desktop\Libro1.pdf" 

Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _      
  "C:\Users\Abraham\Desktop\Libro1.pdf" 

Los otros parámetros del método trabajan y/o se usan del siguiente modo:

Nombre	Descripción
Type	Es el parámetro que define el tipo de archivo en que se exportará. Puede ser de dos tipos: xlTypePDF (pdf)  o xlTypeXPS (xps). Su uso es obligatorio.
Filename	Es la cadena que indica la ruta y/o el nombre del archivo que se guardará. Su uso es opcional. De no llenarse por defecto se guarda en la misma carpeta que el archivo Excel y con el mismo nombre.
Quality	Es opcional. Define la calidad del archivo que se exportara, puede ser xlQualityStandard (normal) o xlQualityMinimum (mínima).
IncludeDocProperties	Establece si las propiedades del documento deben incluirse o no. Sus valores son True (para incluir) o False (no las incluye). Es opcional.
IgnorePrintAreas	Es opcional. Establece si se ignora las áreas de impresión establecidas al publicar. Puede ser True para dicho efecto o False para que use las áreas de impresión establecidas al momento de la publicación.
From	Establece el número de página desde donde se comenzará a publicar/exportar. Si se omite el parámetro, comienza desde la primera página. No confundir página con hoja.
To	Establece el número de la última página que se publicará/exportará. Si se omite el parámetro, se publica/exporta hasta la última página. No confundir página con hoja.
OpenAfterPublish	Establece si el archivo se abrirá después de ser publicado/exportado. Sus valores pueden ser True o False. Es opcional.
FixedFormatExtClassPtr	Es opcional. Representa un puntero de la clase personalizada en un complemento que implementa la interfaz IMsoDocExporter COM que permite llamadas a una implementación alternativa de código para el formato del documento. El valor predeterminado es un puntero nulo.

Siguiendo con los ejemplos, en éste solo se exporta a PDF desde la página 3 a la 6 de la “Hoja2”:

Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _  
  "C:\Users\Abraham\Desktop\Libro1.pdf", From:= 3, To:= 6 

En este otro ejemplo, se exporta un rango en calidad “Standard” y se abre el documento posterior a ser exportado:

Range("A1:C2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _   
  "C:\Users\Abraham\Desktop\Libro1.pdf", Quality:= xlQualityStandard, OpenAfterPublish:=True 

No olvidemos que los valores de parámetros como “Filename”, “From”, “To”, etc., pueden ser tomados de celdas o desde variables sin ningún problema si los valores ha sido bien establecidos:

Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:=  Range("C12").Value , From:= Range("R3").Value, To:= Range("R4").Value

Dim PrimeraPagina As Long, UltimaPagina As Long 
Dim RutayNombre as String
Let PrimeraPagina =  Range("R3").Value 
Let UltimaPagina =  Range("R4").Value 
Let RutayNombre= Range("C12").Value 
Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= RutayNombre, From:= PrimeraPagina, To:= UltimaPagina 

Y esas son algunas cosas que es importante concoer para exportar a PDF (o XPS) nuestros archivos de Excel.

Hasta la próxima.

Abraham Valencia