Convertir números a letras

Algo que los usuarios preguntan con cierta frecuencia, es si Excel cuenta con alguna función que convierta un número (40), a su forma "verbal" o textual ("cuarenta"). Principalmente, para elaborar facturas.

Bien, la respuesta es no. Las única formas son utilizar una macro o descargar algún complemento que pueda hacerlo. Hay varios sitios que proveen dichas macros. La que utilizo es la siguiente:

Option Explicit

Dim cTexto As String 'Variable para las funciones

Public Function NumLetras(ByVal Numero As Double, ByVal Mayusculas As Integer) As String

Dim NumTmp As String
Dim c01 As Integer
Dim c02 As Integer
Dim pos As Integer
Dim dig As Integer
Dim cen As Integer
Dim dec As Integer
Dim uni As Integer
Dim letra1 As String
Dim letra2 As String
Dim letra3 As String
Dim Leyenda As String
Dim Leyenda1 As String
Dim TFNumero As String

If Numero < 0 Then numero = Abs(Numero)
numtmp =" Format(Numero,"00000000000000.00")
c01 = 1
pos = 1
tfnumero = ""
Do While C01 <=5
C02 = 1
Do While c02 <= 3 'Extrae un digito cada vez de izquierda a derecha
dig = Val(Mid(NumTmp, pos, 1))
Select Case c02
Case 1: cen = dig
Case 2: dec = dig
Case 3: uni = dig
End Select
c02 = c02 + 1
pos = pos + 1
Loop
letra3 = Centena(uni, dec, cen)
letra2 = Decena(uni, dec)
letra1 = Unidad(uni, dec)

Select Case c01
Case 1
If cen + dec + uni = 1 Then
Leyenda = "Billon "
ElseIf cen + dec + uni > 1 Then
Leyenda = "Billones "
End If
Case 2
If cen + dec + uni >= 1 And Val(Mid(NumTmp, 7, 3)) = 0 Then
Leyenda = "Mil Millones "
ElseIf cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 3
If cen + dec = 0 And uni = 1 Then
Leyenda = "Millon "
ElseIf cen > 0 Or dec > 0 Or uni > 1 Then
Leyenda = "Millones "
End If
Case 4
If cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 5
If cen + dec + uni >= 1 Then
Leyenda = ""
End If
End Select

c01 = c01 + 1

TFNumero = TFNumero + letra3 + letra2 + letra1 + Leyenda

Leyenda = ""
letra1 = ""
letra2 = ""
letra3 = ""

Loop

If Val(NumTmp) = 0 Or Val(NumTmp) <1 Then
leyenda1 = "Cero Pesos "
ElseIf Val(NumTmp) = 1 Or Val(NumTmp) <2 Then
leyenda1 = "Peso "
ElseIf Val(NumTmp) = 0 Or Val(Mid(NumTmp, 10, 6)) = 0 Then
Leyenda1 = "de Pesos "
Else
Leyenda1 = "Pesos "
End If

TFNumero = TFNumero & Leyenda1 & Mid(NumTmp, 17) & "/100 M.N."

If Mayusculas = 1 Then
TFNumero = UCase(TFNumero)
Else
TFNumero = LCase(TFNumero)
End If

NumLetras = TFNumero

End Function


Private Function Centena(ByVal uni As Integer, ByVal dec As Integer, _
ByVal cen As Integer) As String

Select Case cen
Case 1
If dec + uni = 0 Then
cTexto = "cien "
Else
cTexto = "ciento "
End If
Case 2: cTexto = "doscientos "
Case 3: cTexto = "trescientos "
Case 4: cTexto = "cuatroscientos "
Case 5: cTexto = "quinientos "
Case 6: cTexto = "seiscientos "
Case 7: cTexto = "setescientos "
Case 8: cTexto = "ochoscientos "
Case 9: cTexto = "novescientos "
Case Else: cTexto = ""
End Select

Centena = cTexto
cTexto = ""

End Function


Private Function Decena(ByVal uni As Integer, ByVal dec As Integer) As String

Select Case dec
Case 1
Select Case uni
Case 0: cTexto = "diez "
Case 1: cTexto = "once "
Case 2: cTexto = "doce "
Case 3: cTexto = "trece "
Case 4: cTexto = "catorce "
Case 5: cTexto = "quince "
Case 6 To 9: cTexto = "dieci"
End Select
Case 2
If uni = 0 Then
cTexto = "veinte "
ElseIf uni > 0 Then
cTexto = "veinti"
End If
Case 3: cTexto = "treinta "
Case 4: cTexto = "cuarenta "
Case 5: cTexto = "cincuenta "
Case 6: cTexto = "sesenta "
Case 7: cTexto = "setenta "
Case 8: cTexto = "ochenta "
Case 9: cTexto = "noventa "
Case Else: cTexto = ""
End Select

If uni > 0 And dec > 2 Then cTexto = cTexto + "y "

Decena = cTexto
cTexto = ""

End Function


Private Function Unidad(ByVal uni As Integer, ByVal dec As Integer) As String

If dec <> 1 Then
Select Case uni
Case 1: cTexto = "un "
Case 2: cTexto = "dos "
Case 3: cTexto = "tres "
Case 4: cTexto = "cuatro "
Case 5: cTexto = "cinco "
End Select
End If

Select Case uni
Case 6: cTexto = "seis "
Case 7: cTexto = "siete "
Case 8: cTexto = "ocho "
Case 9: cTexto = "nueve "
End Select

Unidad = cTexto
cTexto = ""

End Function

En realidad, son cuatro funciones las que se utilizan para lograr el cometido.

Para usarla, simplemete escribimos en alguna celda:

=NUMLETRAS(A2,1)

Esto, claro, si hemos guardado la función en el mismo libro en el que la vamos a usar. Si la guardamos en el libro de macros personal (de forma que esté disponible en todos los libros), entonces tendremos que escribir:

=Personal.xls!NUMLETRAS(A2,1)

Para mayor seguridad, utilicen el asistente de funciones (el pequeño botón fx situado a la izquierda de la barra de fórmulas). La función estará en la categoría Definidas por el usuario.

Si queremos el resultado en minúsculas, escribimos 0 (pero no FALSO) como segundo argumento. Al final, la función agrega el texto " pesos 00/100 m.n.", el cual puede ser ajustado en el código.

La fórmula =NOMPROPIO(NUMLETRAS(1425300,1))devuelve:

Un Millon Cuatroscientos Veinticinco Mil Trescientos Pesos 00/100 M.N.

Sumar por colores

Un usaurio me pregunta si es posible sumar los valores de celdas que tengan determinado color.
En Excel 2007 sí es posible, pero el usuario tiene la versión 2003.

En Excel 2003 la única forma es a través de una función personalizada, como la siguiente:

Function SUMARCOLOR(RangoColor As Range, CeldaColor As Range) As Long

Dim rngCelda As Range

'revisamos cada celda del rango
For Each rngCelda In RangoColor

'si los colores coinciden, sumar el valor de la celda al resultado previo
If rngCelda.Interior.ColorIndex = CeldaColor.Interior.ColorIndex Then
SUMARCOLOR = SUMARCOLOR + rngCelda.Value
End If

Next

End Function

Como vemos, no es un código muy complejo que digamos. Simplemete comparamos cada celda del rango contra el color del segundo argumento y, si coinciden, lo vamos sumando al resultado de la función.

Para utilizarla, utilizamos el asistente para funciones, buscamos la función en la categoría Definidas por el usuario, y especificamos los argumentos, quedando una fórmula similar a:

=SUMARCOLOR(A2:I20, K2)

Aunque por otra parte, lo mejor hubiera sido establecer alguna condición para los colores, y después sumar los valores usando esta condición, así como utilizar esta misma condición en un formato condicional para obtener el color en las celdas. Hacerlo hubiera llevado menos tiempo que el estar coloreando cada una de las celdas.

Obtener los elementos únicos de una lista

Una tarea común es obtener los elementos únicos de una lista. La siguiente macro permite realizarlo:

Sub elementosunicos()

Dim celda As Range
Dim unicos As Collection
Dim sh As Worksheet
Dim i As Long

'nos aseguramos que la seleccion sea un rango
If TypeName(Selection) = "Range" Then

'inicializamos la coleccion
Set colUnique = New Collection

'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Selection.Cells

'si el elemento existe, se genera un error, ignorarlo
On Error Resume Next

'una coleccion solo agrega elementos no repetidos
unicos.Add celda.Value, CStr(celda.Value)
On Error GoTo 0
Next celda

'agregar hoja para la lista
Set sh = ActiveWorkbook.Worksheets.Add

'escribir los datos unicos
For i = 1 To unicos.Count
sh.Range("A1").Offset(i, 0).Value = unicos(i)
Next i

'ordenar
sh.Range(sh.Range("A2"), sh.Range("A2").End(xlDown)) _
.Sort sh.Range("A2"), xlAscending, , , , , , xlNo

End If

End Sub

Otras alternativas son generar una tabla dinámica y agregar el campo correspondiente al área de filas, o bién, utilizar el filtro avanzado. El análisis que hagamos del modelo nos dirá cuál método es el mejor.

Introducción a la programación VBA

En lo sucesivo comenzaré a publicar ejemplos y técnicas de programación útiles en el desarrollo de modelos Excel. Así que supondré que el lector tiene conocimientos intermedios en el lenguaje VBA (Visual Basic for Aplications). Si no es así y desean una introducción al tema, les recomiendo el siguiente vínculo del sitio Excel Worker, que además está en español:

http://www.excelworker.virtuabyte.cl/index.php?option=content&task=section&id=3&Itemid=27

En cambio, si tienen conocimientos intermedios - avanzados de inglés, entonces recomiendo Erlandsen Data Consulting o el sitio de Chip Pearson.

Todos contienen material suficiente para iniciarse por lo que no pretenderé reinventar la rueda.

Saludos.

Funciones personalizadas

Excel cuenta con un inmenso número de funciones integradas, de la más diversa índole. Asimismo, hay numerosos complementos (el más famoso es Herramientas para análisis) que agregan todavía más funciones a Excel. Por si fuera poco, podemos crear nuestras propias funciones, lo cual puede ser útil en el (improbable) caso de que no exista una función que haga lo que necesitemos; o bien, para simplificar nuestras fórmulas. Aunque para lograrlo es necesario tener conocimientos más o menos sólidos en programación de macros.

Programar una función es parecido a programar una macro. De hecho, algunos autores llaman también macros a las funciones personalizadas. No obstante, existe una diferencia fundamental entre ambas: una función solo puede devolver un resultado. No puede operar o cambiar el entorno de Excel. (OK, sí puede, pero son excepciones ya muy rebuscadas). Lo siguiente es una macro:

Sub insertahoja()
ActiveWorkbook.Worksheets(1).Copy Before:=ActiveWorkbook.Worksheets(4)
ActiveWorksheet.Name = "Hoja número 3"
End Sub

En cambio, esto es una función:

Private function micalculo(num_1 As Integer, num_2 As Integer) As Integer

Dim j As Integer

If num_1 <= num_2 Then
j = num_2 - num_1
Else
j = num_1 - num_2
End If

micalculo = j

End function

Como vemos, en el primer caso, alteramos el entorno Excel al copiar una hoja en el libro activo, mientras que en el segundo, solo obtuvimos el resultado j (que es la diferencia absoluta entre dos números). No hicimos nada más. Otra diferencia evidente es la forma de declarar y terminar una función con las cláusulas Private function y End function. Por lo demás el lenguaje utilizado es el mismo.

Por otra parte, el resultado que devuelve una función puede no ser (como pudiera pensarse) un número. Puede ser texto, un resultado lógico (FALSO o VERDADERO), un rango, un nombre, entre otros. Por ello, es necesario declarar el tipo de dato del resultado (el último "As Integer").

Una función puede ser utilizada o llamada por una macro, por otra función, o a través de la interfaz de Excel. Para el primer caso veamos este ejemplo:

Sub insertahoja()
ActiveWorkbook.Worksheets(1).Copy Before:=ActiveWorkbook.Worksheets(4)
ActiveWorksheet.Name = "Hoja número 3"

ActiveWorksheet.Range("A3") = micalculo(ActiveWorksheet.Range("A1").Value, ActiveWorksheet.Range("A2").Value)

End Sub

Con esta macro, modificación de la previa, escribiremos en la celda A3 el resultado de la fórmula =micalculo(A1, A2)

Para el segundo caso, veamos:

Private function otrocalculo(num_1 As Integer, num_2 As Integer) As Integer

j = micalculo(num_1 As Integer, num_2 As Integer)

j = j * -1

otrocalculo= j

End function

Esta función simplemente multiplica por - 1 el resultado de micalculo(num_1 As Integer, num_2 As Integer)

Como último caso, si queremos usar esta función desde la interfaz de Excel, simplemente escribimos en una celda:

=MICALCULO(A1, A2)

Si queremos utilizar el Asistente para funciones de Excel, necesitamos dar clic en la categoría Definidas por el usuario, al final de la lista de categorías. Un detalle importante a tener en cuenta es que Excel de alguna manera "recuerda" la forma en que escribimos el nombre de la función la primera vez, es decir, con mayúsculas o minúsculas. Si la primera vez que escribamos la función utilizamos minúsculas, Excel la mostrará siempre así en la barra de fórmulas.

Hemos visto un ejemplo sencillo, pero cuando tenemos necesidad de hacer cálculos complejos o grandes, podemos mejorar la legibilidad de la fórmula simplemente agregando argumentos a una sola función.

Para los fanáticos de AC/DC

El equipo creativo del grupo de rock AC/DC, Phil Clandillon y Steve Milbourne, ha anunciado el lanzamiento del primer video musical en formato .xls. La canción, Rock N Roll Train. Su objetivo, dijeron, era que el video pudiera superar los firewalls más potentes. A fin de cuentas, a nadie le prohiben recibir un archivo Excel ¿cierto?




El video, en realidad, es una macro (recordemos habilitar las macros antes de abrir el archivo) que copia las "imágenes" (texto en realidad) de la columna Q mientras reproduce un archivo WAV. No imagino la cantidad de trabajo que representó dibujar cada uno de los frames de este archivo.

El código del archivo no está protegido. Para ver las imagenes, primero hay que desactivar el evento Worsheet_SelectionChange de la hoja y mostrar la columna Q.

Descargar.

Descargar macros de la red

En la red hay una gran cantidad de sitios que ponen a nuestra disposición macros de la más diversa índole. Ejemplos de estos sitios son Daily Dose of Excel, Erlandsen Data, xltoday entre muchos otros (en la sección Recursos Excel, al margen, encontrarán los enlaces a estos sitios y varios más). Lo más valioso de estas macros es que son open source, es decir, públicas y gratuitas, ahorrándonos gran cantidad de trabajo y tiempo.

Un detalle común a casi todos estos sitios es que no explican al lector cómo descargar las macros a sus equipos (como dicen algunos, "a mi Excel"). ¿Cómo copiar una macro a "nuestro Excel" y ejecutarla? Seguimos los siguientes pasos:

Supongamos que queremos utilizar la siguiente macro de ejemplo (de Daily Dose of Excel):

Sub CreateTest()

Dim rCell As Range
Dim sFname As String
Dim lFnum As Long
Dim sInput As String
Dim sOutput As String

If TypeName(Selection) = "Range" Then
'Pick a name for the test file
sFname = Application.InputBox("Enter test file name to create.", "File Name")

If sFname <> "False" Then 'if inputbox not canceled

'Add file extension if not there
If Right$(sFname, 4) <> ".txt" Then
sFname = sFname & ".txt"
End If

sInput = "[Input]" & vbNewLine
sOutput = "[Output]" & vbNewLine

'Build input and output strings based on selection
For Each rCell In Selection.Cells
If rCell.HasFormula Then
sOutput = sOutput & ActiveSheet.Name & "|" & _
rCell.Address & "|" & rCell.Value2 & vbNewLine
Else
sInput = sInput & ActiveSheet.Name & "|" & _
rCell.Address & "|" & rCell.Value2 & vbNewLine
End If
Next rCell

sOutput = Left$(sOutput, Len(sOutput) - 2) 'delete new line

'Create the test file
lFnum = FreeFile
Open sFname For Output As lFnum

Print #lFnum, sInput
Print #lFnum, sOutput

Close lFnum

End If
Else
MsgBox "Please select one or more cells and try again"
End If

End Sub

Como leemos en Daily Dose... lo que hace esta macro es generar un archivo de texto que lista las referencias ingresadas como argumento, y su correspondiente valor, separadas en entradas de fórmula y fórmulas. Útil para auditar lás fórmulas de nuestros modelos.

El primer paso es seleccionar el texto de la macro y copiarlo.

A continuación, activamos Excel y vamos al editor de Visual Basic (Herramientas - Macro - Editor de Visual Basic, ó Alt + F11).

Una vez aquí, tenemos que determinar algo muy importante: el ámbito de aplicabilidad de la macro. Es decir, si la macro va a ser utilizada sólo en determinado libro o bién, en todos los libros. Esto dependerá del análisis general que hagamos de la situación. En este caso, supongamos que queremos que la macro esté disponible en todos los libros. Por lo tanto, la guardaremos en nuestro libro de macros personal.

En el editor de Visual Basic, en el Explorador de proyectos, damos doble clic en Personal y a continuación, pegamos el código que hemos copiado al final del código existente.



Si queremos pegar la macro en su propio módulo, lo insertamos con Insertar - Módulo.
Finalmente, salimos del editor, con Archivo - Salir y volver a Microsoft Excel (Alt + q). Podemos guardar previamente la macro aunque esto no es estrictamente indispensable.

Procedamos ahora a ejecutar la macro. De acuerdo a las instrucciones de la nota original, primero hay que seleccionar un grupo de celdas con o sin fórmulas. Después vamos a Herramientas - Macro - Macros... (ó Alt + F8). En este punto, podemos elegir asignar una tecla de método abreviado a la macro y/o agregar una descripción a la misma dando clic en el botón Opciones...
Finalmente, seleccionamos el nombre de la macro y hacemos clic en Ejecutar.

En caso de que queramos que la macro solo esté disponible en determinado libro, entonces, damos doble clic en el proyecto correspondiente en el Explorador de proyectos. Acto seguido, damos Insertar - Módulo, y pegamos aquí el código.

Pie de página III

Continúa de la nota anterior.

La tercera consulta es configurar Excel de forma que todo libro nuevo tenga pies de página en todas las hojas. Interesante.

La solución que propongo consiste en, primero, crear manualmete un libro que contenga pie de página en todas sus hojas. Después, guardarlo como plantilla en la carpeta de inicio de Excel, de forma que al inicializarlo se abra automáticamente. Posteriormente, crear una macro personal sencilla que abra esta plantilla. Luego, algo de personalización: asignar esta macro al comando Archivo - Nuevo, y asociarle el método abreviado Ctrl + u (utilizado por default por el comando Archivo - Nuevo). Para hacer esto aún más "invisible", guardaremos la plantilla con el nombre "Libro"; de esta forma al abrirla, Excel le cambiará el nombre a "Libro1" (cuando se abre una plantilla, Excel la convierte a libro normal y le agrega un consecutivo al final del nombre, conservando intacto el archivo original). Así pues, esta será la primera solución formal en Excel que desarrollemos.

El primer paso es abrir un libro nuevo (Ctrl + u) e insertar pies de página en las hojas, manualmente o utilizando la macro de la primera nota (sugiero que el libro tenga unas 6 hojas o más, para no tener que insertar otras después).

Posteriormente, damos Archivo - Guardar como... En nombre de archivo escribimos "Libro", y en Guardar como tipo: ponemos Plantilla. Guardamos en la ruta predifinida para plantillas o en cualquier otra ruta. Si queremos que Excel abra la plantilla al inicializarlo, guardamos una copia en la carpeta de arranque (XLSTART, pueden ubicarla realizando una búsqueda en Windows).

Ahora, la macro. Escribimos este código en un módulo del libro de macros personal:

Sub mimacro()

Workbooks.Open Filename:= _

-----"C:\Documents and Settings\leonel.quezada\Datos de programa _
-----\Microsoft\Plantillas\Libro.xlt"
End Sub

Cerramos el editor de Visual Basic. Obviamente, tienen que ajustar la ruta en la que han guardado el archivo en sus equipos.

A continuación asignamos el método abreviado Ctrl + u a la macro (utilizado actualmente por el comando Abrir - Nuevo...). Con Herramientas - Macro - Macros - clic en la macro - Opciones...

El siguiente paso es personalizar el comando Archivo - Nuevo. Para lograrlo, ejecutamos Herramientas - Personalizar... (Alt, h, z). Esto nos llevará al cuadro de diálogo Personalizar:

Con este cuadro abierto, damos clic en el menu de Excel Archivo, clic derecho en Nuevo... y clic en Asignar macro...:

En el cuadro Asignar macro seleccionamos la macro que hicimos (en el ejemplo, mimacro) y aceptamos el cuadro de diálogo. Cerramos el cuadro Personalizar.

En adelante, todos los archivos nuevos que abramos con Archivo - Nuevo... o con Ctrl + u, tendrán configurado el pie de página correctamente.

En lo personal, yo utilizaría la macro de la primera nota, ejecutándola cuando fuera requerido. Es la más sencilla de realizar y deja los archivos libres de macros. Mi segundo lugar en preferencias es la presente solución (además de que sirve para ejercitar otros temas). La desventaja es que si insertamos una hoja, ésta no contendrá pie de página. La opción de la segunda nota no la utilizaría ya que implica infestar de macros todos archivos en los que la usemos (consideremos que muchos usuarios simplemente ODIAN las macros, en algo que yo llamo macrofobia). Aparentemente lo mejor es agregar la macro de evento de la segunda nota a esta plantilla, pero esto también implicaría el uso forzoso de macros. En todo caso, la decisión corresponde al usuario final.

Pie de página II

Continúa de la nota anterior.

Veamos ahora el segundo caso, agregar pie de página al insertar una hoja nueva. La única manera de lograr esto es escribiendo una macro dentro del libro mismo al que le estaremos insertando hojas. No podemos escribirla dentro del libro de macros personal, ya que no se trata de una macro "normal", sino de una macro de evento. Estas macros de evento se ejecutan automáticamente al realizarse determinada acción (el evento) por parte del usuario. En este caso, se trata del evento NewSheet. Todo el código que asociemos a este evento se ejecutará única y exclusivamente al insertar una hoja en el libro lo contiene. Es por esto que no podemos guardarlo en el libro de macros personal, ya que en ese caso solo se ejecutaría al insertar una hoja en este mismo libro de macros personal, lo cual nunca sucederá, ya que este libro solo lo utilizamos como contenedor de nuestras macros de uso general, además de que está oculto. Como dije al principio, ya habrá tiempo para profundizar en este tema.

Seguimos los siguientes pasos:

Asegúrense de tener abierto el libro al cual asignarán el código. Acto seguido, abrimos el editor de Visual Basic (Alt + F11). Identificamos el libro en el que queremos trabajar en el Explorador de proyectos y damos doble clic en él.


Abrimos la subcarpeta Microsoft Excel Objetos y doble clic en el objeto ThisWorkbook.

En la primera lista despleglable de la parte superior del Editor, seleccionamos Workbook, y en la segunda lista selecionamos NewSheet:

Con esto aparecerán en el Editor la primera y la última línea de código del evento NewSheet. En medio de estas dos líneas, escribimos el mismo código de la nota anterior, excepto la primera y la última líneas, debiendo quedar como sigue:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim ws As Worksheet

Dim nombre As String

Set ws = ActiveSheet

nombre = Application.UserName

---With ws.PageSetup

------.LeftFooter = "&11&Z&F"

------.CenterFooter = "&11&D"

------.RightFooter = "&""Arial,Normal""&11Elaboró: " & _

---------UCase(Left(nombre, 1)) & Mid(nombre, 2, InStr(1, nombre, ".") - 2) _

---------& " " & UCase(Mid(nombre, InStr(1, nombre, ".") + 1, 1)) & _

---------Mid(nombre, InStr(1, nombre, ".") + 2)

---End With

End Sub

Finalmente, hacemos una prueba. Dado que esta es una macro del evento NewSheet, simplemente insertamos una hoja en el libro. Si lo hicieron correctamente, pueden comprobar que ya contiene pie de página.

Finalizamos en la siguiente nota.

Pies de página autómaticos

Un usaurio me pregunta cómo insertar rápidamente un pie de página en una hoja. En realidad fueron tres consultas en una: cómo insertar autómaticamente un pie de página, cómo insertar un pie de página automáticamente al insertar una hoja, y cómo abrir un libro nuevo con pies de página incluídos. Dado que $oy amable, responderé a las tres, en ese mismo orden.

Antes de comenzar, diré que, extrañamente, Excel no tiene una manera directa de hacer esta configuración automáticamente. Obviamente, podemos ir a Archivo - Cofigurar página - Encabezado y pie de página - Personalizar pie de página... clic en el botón Ruta de archivo - Aceptar (¿así o más clics?) y hacer la configuración correspondiente. Pero si queremos que esto sea automático, la única vía es utilizando macros. Como aún no hemos visto prácticamente nada acerca de macros avanzadas, solo proporcionaré las macros y el modo de utilizarlas, sin detenerme a explicarlas. Posteriormente tendremos tiempo.

Supongamos que queremos insertar el siguiente pie de página: en la sección izquierda, la ruta completa del archivo, en la sección central la fecha, y en la sección derecha la leyenda "Elaboró: [nombre usuario]"

Para el primer caso, insertar un pie de página automáticamente, abrimos el libro de macros personal, con Herramientas - Macro - Editor de Visual Basic (o Alt + F11). Damos doble clic en PERSONAL.XLS. Si lo deseamos, insertamos un nuevo módulo, con Insertar - Módulo. En la zona de edición, escribimos, tal cual, el siguiente código (desde "Sub" hasta "End Sub"):

Sub piepagina()

Dim ws As Worksheet
Dim nombre As String

Set ws = ActiveSheet
nombre = Application.UserName

---With ws.PageSetup
------.LeftFooter = "&11&Z&F"
------.CenterFooter = "&11&D"
------.RightFooter = "&""Arial,Normal""&11Elaboró: " & _
---------UCase(Left(nombre, 1)) & Mid(nombre, 2, InStr(1, nombre, ".") - 2) _
---------& " " & UCase(Mid(nombre, InStr(1, nombre, ".") + 1, 1)) & _
---------Mid(nombre, InStr(1, nombre, ".") + 2)
---End With

End Sub

Damos Archivo - Guardar (o Ctrl + S), y cerramos la ventana del Editor de Visual. A continuación, hacemos una prueba: Activamos la hoja a la que queremos insertar el pie de página, y ejecutamos la macro, con Herramientas - Macro - Macros (o Alt + F8) - Ejecutar.
Verificamos con Vista previa.

Finalmente, asignamos un método abreviado a la macro. Hay que volver al cuadro Macro (alt + F8). Clic en Opciones... y en Tecla de método abreviado: escribimos la letra que deseemos (cuidándonos de no utilizar un método que ya esté siendo utilizado, como Ctrl + c, ya que Excel ejecutaría siempre la macro, no la instrucción original, en este caso, copiar). Digamos Ctrl + q. Clic en Aceptar y cerramos el cuadro Macro. Ahora, al presionar Ctrl + q, Excel insertará el pie de página en la hoja activa.

Continuamos en la siguiente nota.

El libro de macros personal

Existen (o seguramente existirán) algunas macros que consideraremos de "aplicación general", aquellas que deseemos que estén disponibles al editar cualquier archivo, y no únicamente en el archivo en el que están guardadas. Por otro lado, puede resultar molesto el tener que habilitar la ejecución de macros cada vez que abramos un archivo que contenga alguna. Una forma de enfrentar ambas situaciones es guardando las macros en nuestro libro de macros personal.

El libro de macros personal es un archivo que Excel carga automáticamente, en caso de que exista, al iniciarse (junto con algunos otros, como el archivo de barras de herramientas y los complementos que tengamos instalados). Contiene todas aquellas macros que queramos que estén disponibles en todo momento, habilitándolas automáticamente al iniciar Excel (esto es, sin preguntarnos si deseamos habilitarlas). Cuando lo creamos, Excel lo guarda en su carpeta de inicio, generalmente en la ruta:

C:\Documents and Settings\[nombre.usuario]\Datos de programa\Microsoft\Excel\XLSTART

Si no encuentran esta ruta en sus equipos, pueden hacer una búsqueda en Windows de "xlstart" para ubicar su carpeta. Si ésta está vacía o no contiene el archivo PERSONAL.xls, necesitaremos crearlo. La forma más sencilla es iniciando la grabadora de macros, con Herramientas - Macros - Grabar nueva macro. En este cuadro de diálogo, establecemos el valor Libro de macros personal en la opción Guardar en:

y aceptamos el cuadro. Acto seguido, terminamos la grabadora presionando el botón Detener grabación de la minibarra que aparece. De esta manera hemos creado el archivo. Ahora, cada vez que queramos grabar una macro de uso frecuente, seleccionaremos la opción Libro de macros personal en el mencionado cuadro. Si queremos que la macro solo esté disponible con un solo archivo, seleccionamos cualquiera de las otras dos opciones.

Para comprobar la existencia del libro personal, repetimos la búsqueda en Windows. Otra manera es iniciando el Editor de Visual Basic, con Herramientas - Macro - Editor de Visual Basic, o bién Alt + F11. Buscamos, incrustado en el margen izquierdo, el Explorador de proyectos, cuya barra de título comienza con la palabra Proyecto:

En caso de que no se muestre el Explorador, lo mostramos ejecutando Ver - Explorador de proyectos.

Ya que ubicamos el explorador de proyectos, buscamos en el mismo el identificador del libro de macros personal, el cual debe ser: VBAProject (PERSONAL.XLS). Damos doble clic en él para mostrar sus subcarpetas, entre las cuales hay una llamada Módulos. A su vez, esta carpeta contiene un archivo llamado "Módulo1", el cual es resultado de la grabación que hicimos.

Si damos doble clic en él, veremos en la zona principal del editor, el código de la macro que grabamos (en realidad no hay ningún código ya que no grabamos nada. Solo veremos el nombre y la descripción de la misma.)

Como este módulo solo contiene una macro que no hace nada, lo eliminamos seleccionandolo en el Explorador de proyectos con clic derecho, y dando clic en Quitar Módulo1. Excel nos preguntará si queremos exportar el módulo antes de eliminarlo, a lo cual contestamos No.

Es aquí, la zona de edición del Editor de Visual Basic, el lienzo donde con la suficiente práctica podremos crear las más bellas obras de arte...

Habilitar macros

Existe la posibilidad de que, al haber elaborado una macro, Excel no nos permita ejecutarla mostrándonos este cuadro de diálogo:

Esto puede deberse a que se ha deshabilitado voluntariamente la ejecución de las macros, o bién, a que apenas hemos elaborado nuestra primera macro (como siempre, la ayuda que proporciona Microsoft es punto menos que lamentable...). Por default, Excel tiene la ejecución de macros deshabilitada. Para habiltarla, seguimos los siguientes pasos:

Vamos a Herramientas - Macro - Seguridad...:

Aquí, en Nivel de seguridad, podríamos seleccionar la opción Bajo, de forma que Excel habilite automáticamente todas las macros en nuestros libros, lo cual no es muy recomendable, a menos que estemos en una isla desierta y sin internet. Cualquier seudo programador lo suficientemente experimentado, puede crear código malicioso (un virus), bajo la forma de una macro de Excel, afectando todo el entorno de nuestro equipo con una "macro" que, por ejemplo, se auto ejecute apenas abrir el archivo que la contiene.

Así pues, es mejor idea seleccionar la opción Medio. Con esto, si uno de nuestros archivos contiene macros, Excel nos preguntará si queremos habilitarlas, con el siguiente cuadro:


Si confiamos en el origen del archivo, o si nosotros mismos hemos elaborado las macros, elegimos la opción Habilitar macros. En cualquier otro caso, elegimos Deshabilitar macros.

Tendremos que padecer este cuadro de diálogo cada vez que abramos un archivo con macros. Claro que, por seguridad, es preferible hacerlo a ser víctima de algún virus. Una alternativa a esta situación es guardar las macros en el libro de macros personales, de forma que éstas estén guardadas en nuestro equipo, y no en un archivo específico.

Link

El editor de macros de Excel

Continúa de la nota anterior.

Para verificar la ejecución de nuestra macro, primero eliminamos los formatos que aplicamos a la celda C6 (o no tendría sentido ejecutarla), y la ejecutamos desde el cuadro de diálogo Macro, visto en la previa nota. De esta manera, veremos que la celda C6 cambia su color de fondo a rojo y el de fuente a blanco. Hasta ahora todo va bién.

Sin embargo, si seleccionáramos otra celda o celdas y ejecutáramos la macro para cambiarles el formato, veríamos que esto no funciona: los formatos se aplicarán siempre a la celda C6 (y sólo en el libro que grabamos la macro). Esto es así ya que en el proceso de grabación de la macro incluímos la selección de la celda C6. Entonces, ¿cómo podemos hacer para darle más flexibilidad a la macro, de forma que aplique los formatos sobre cualquier celda o celdas seleccionadas?

Para lograrlo, necesitamos editar el código de nuestra macro. Y para acceder a este, necesitamos recurrir al Editor de macros de Excel. Ejecutamos el cuadro de diálogo Macro y presionamos el botón Modificar... El editor tiene una apariencia similar a esta:

En la pantalla principal podemos ver el nombre de la macro, su descripción y el código de la misma (puede variar un poco con el que tengan ustedes). Cada vez que ejecutamos una macro, Excel "lee" su código asociado y lo ejecuta línea por línea, siguiendo una lógica absolutamente exacta e implacable. El código del ejemplo es el siguiente:

Sub Macro2()
'
' Macro2 Macro
' Cambia el color de celda a rojo y el color de texto a blanco
'
Range("C6").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
End Sub

Intentemos comprender qué es lo que significa cada línea antes de continuar...

Como vemos, el código incluye la selección de C6 en la primera línea. Entonces, como no queremos que la macro seleccione C6, sino simplemente que aplique formatos, eliminamos la primera línea del código (¡sí, con Supr!), quedando como sigue:

Sub Macro2()
'
' Macro2 Macro
' Cambia el color de celda a rojo y el color de texto a blanco

With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
End Sub

Ahora, volvemos a Excel. Selecionamos cualquier rango de celdas y ejecutamos la macro.

Si hemos hecho la edición correctamente,veremos que el proceso ahora es más flexible, ya que aplica los formatos en cualquier selección activa, no sólo en C6.

En futuras notas emplearemos más tiempo (mucho más) al estudio del Editor de macros y a la redacción de macros.

Macros o uso racional de la fuerza bruta

Una macro es, en términos sencillos, una secuencia de instrucciones que se ejecutan en una sola acción por parte del usuario. Son útiles cuando nos vemos precisados a llevar a cabo constantemente una misma serie de pasos en nuestro modelo. Estas series de pasos pueden ir desde las sencillas (p. ej. dar color de celda rojo y color de fuente blanco a la celda seleccionada) hasta las muy complejas (crear una consulta en Access, copiarla a Excel, ordenar los datos, subtotalizarlos en otra hoja, formatear las filas de subtotal, proteger las hojas y enviar el archivo resultante a nuestros contactos).

Para ejecutar una macro, podemos hacerlo a través de los comandos nativos de Excel (Herramientas - Macro - Macros - Ejecutar), o bién, con un menú personalizado y/o un método abreviado personalizado (Ctrl + Shift + q) y/o una barra de herramientas personalizada.

Una macro puede ser redactada o bien, grabada utilizando la grabadora de macros de Excel. Procedamos a grabar la macro del primer ejemplo, dar color de celda rojo y color de fuente blanco a la celda seleccionada. Seguimos los siguientes pasos al pie de la letra:

1. Activamos la grabadora de macros de Excel. Para hacerlo, vamos a Herramientas - Macro - Grabar nueva macro..., con lo cual aparecerá en cuadro de diálogo Grabar macro:

Le damos un nombre descriptivo a nuestra macro, cuidándonos de utilizar solamente letras y/o números y de no utilizar espacios ya que Excel no lo permitirá. Si queremos asignar un método abreviado a la macro podemos hacerlo desde aquí también, pero deberemos prestar atención de no asignar un método que tenga ya asignado Excel, como Ctrl + c. Por último anotamos una breve descripción de la macro en el último cuadro, y aceptamos el cuadro de diálogo.

Con esto Excel iniciará su Grabadora de macros y aparecerá la minibarra de herramientas Detener grabación con dos o tres botones: Detener grabación, Pausa y Referencia relativa. No presionamos ninguno (aún).

2. Situamos el cursor en la celda que queramos formatear, digamos C6.

3. Modificamos adecuadamente el color de celda y el color de fuente.

4. Damos clic en el botón Detener de la barra de herramientas Detener grabación.

De esta forma hemos grabado una macro, muy sencilla, pero funcional. Para comprobar que efectivamente existe, ejecutamos Herramientas - Macro - Macros para ver el cuadro de diálogo Macro, donde podremos ver en todo momento las macros que hemos grabado (excepto si se trata de una macro oculta), además de la descripción de las mismas, si es que hemos escrito alguna:

Para ejecutar la macro, la seleccionamos de la lista y damos clic en Ejecutar.

Continuamos en la siguiente nota.

Guardar la configuración de las barras de herramientas

Esta entrada no corresponde a ninguna consulta. La publico por considerarla de utilidad para los usuarios avanzados, los power users.

Seguramente los lectores ya habrán advertido lo siguiente: Cada que cerramos Excel, este guarda la configuración de las barras de herramientas al momento de cerrarlo. Gracias e esto, cada que abrimos Excel, las barras de herramientas están exactamente igual, en la misma posición y con los mismos botones que la última vez que trabajamos. Esto lo pueden comprobar fácilmente, arrastrando una de las barras, cerrando el programa y volviéndolo a abrir.

Ahora bién, esta configuración Excel la guarda en un archivo de barras de herramientas, de extensión .xlb y cuyos nombres y ubicación pueden variar. Generalmente se llama PERSONAL.XLB o EXCEL11.XLB. En mi PC lo tengo como:

C:\Documents and Settings\[nombre de usuario]\Datos de programa\Microsoft\Excel\Excel11.xlb

En todo caso, podemos hacer una búsqueda en Windows de todos los archivos terminados en .xlb.

Entonces, cada que cerramos Excel, este archivo se actualiza automáticamente. Al abrirlo, se abre a su vez este archivo (entre otros, como el libro de macros personal y los complementos instalados). En caso de que no exista este archivo, ya sea porque lo eliminemos o le cambiemos el nombre, se creará automáticamente.

Podemos aprovechar este archivo para intercambiar rápidamente entre varias configuraciones de barras de herramientas, sin tener que estar abriendo y cerrando manualmente cada una de ellas. Si, por ejemplo, estamos trabajando en el aspecto visual de nuestro modelo, podemos elegir que Excel nos muestre las barras de herramientas Dibujo, Word Art, Gráfico y una barra personalizada en determinada posición, y que oculte la barra Formato en un solo paso. Y al terminar, que nos devuelva a la configuración Estándar.

Procedemos como sigue:

Realizamos la configuración de barras de herramientas que deseemos. Siguiendo nuestro ejemplo, abriremos cuatro barras de herramientas y ocultaremos otra, la de Formato. Las colocamos en la posición que queramos (la barra de menús también es una barra de herramientas, por lo que también podemos moverla libremente).

Acto seguido, cerramos Excel.

Sabemos que el archivo Excel11.xlb ha sido modificado y que será leído la próxima vez que abramos nuestro venerado programa. Busquemos el archivo con el Explorador de Windows y cambiémosle el nombre, digamos que a diseño.xlb.

Abrimos otra vez Excel. Vemos que, efectivamente,tenemos la última configuración. Sin embargo, al cambiarle el nombre, el archivo Excel11.xlb ya NO existe. Al no existir, Excel lo creó automáticamente, con la configuración estándar, como mencioné líneas arriba. En efecto, si en este punto hacemos una nueva búsqueda en Windows, veremos que ahora tenemos dos archivos .xlb: excel11 y diseño.

Ahora, cada que queramos ver la configuración de diseño, simplemente abrimos el archivo diseño.xlb. Para regresar a la configuración original, abrimos Excel11.xlb.

Por último, repetimos estos pasos tantas veces como configuraciones queramos. También, como tip, podemos crear una minibarra de herramientas y/o menú que abra cada uno de estos archivos y ejecute los cambios con un solo clic, lo cual será tema de otra entrada.

Gracias México.