28 de octubre de 2008

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.

21 de octubre de 2008

Functions list

Versión en Español.

Please read this post.

=CONVERT, 638844971
=WORKDAY, 1679294519
=YEARFRAC, 734789689
=WEEKNUM, -601489317
=AMORLINC, 2050949212
=AMORDEGRC, -760479651
=RECEIVED, 1825046605
=COUPDAYS, -1225457601
=COUPDAYBS, -1886650304
=COUPDAYSNC, 85852225
=COUPPCD, 1738735682
=COUPNCD, -2019426237
=COUPNUM, -1858994114
=DURATION, -2027749294
=MDURATION, -2114781101
=ACCRINT, -692060092
=EFFECT, 226623539
=TBILLEQ, -600899503
=TBILLPRICE, -2087911345
=TBILLPRICE, -2087911345
=TBILLYIELD, -2095251376
=DOLLARDE, -2108293060
=DOLLARFR, -2128216003
=CUMIPMT, 1933180986
=CUMPRINC, -1730019269
=PRICE, -1461321658
=PRICEDISC, 1303642186
=ODDFPRICE, -2005663660
=ODDLYIELD, 1307508823
=YIELDMAT, 526319689
=YIELD, 1736245319
=YIELDDISC, -2110848949
=ODDFYIELD, 1206845525
=ODDLYIELD, 1307508823
=YIELDMAT, 526319689
=DISC, -1081081780
=INTRATE, -1046478770
=NOMINAL, 1421541428
=XIRR, -1080295335
=FVSCHEDULE, 1605632050
=XNPV, -841220008
=RANDBETWEEN, 1788346458
=GCD, 1334050856
=LCM, -15466455
=MULTINOMIAL, -164822998
=SQRTPI, 406257673
=MROUND, 147456011
=SERIESSUM, 1365508135
=SERIESSUM, 1365508135
=BESSELI, 571539472
=BESSELJ, 572588046
=BESSELY, 588316687
=BIN2DEC, 908066819
=BIN2HEX, -141557713
=BIN2OCT, -2125463506
=COMPLEX, -1477705690
=CONVERT, 638844971
=DEC2BIN, 908066853
=DEC2HEX, -1690304477
=DEC2OCT, -310378460
=DELTA, -286195700
=FACTDOUBLE, 1382350854
=ERF, 2124677124

Partial list of ATP's functions and returned results when typed without arguments and parenthesis.

15 de octubre de 2008

Unexpected result

Versión en español.

When you type an equal sign and a function name (for example "=SUM") and press Enter, you get the #NAME? error, because Excel "thinks" that you want to work with a name, wich is not defined.

Or not?

A few days ago, I was working on a model involving date calculations. Since I wanted to calculate the number of labor days between two dates, I used the NETWORKDAYS function.
However, while writing the formula, I (accidentally) hit Enter right after the function name (i. e. "=NETWORKDAYS", Enter). Unexpectedly Excel returned:

840368184

Where that number comes from, what does it refers to, or why I got preciselly that number and no other, is something that I completely ignore. Since this behavior was totally unexpected, I wanted to repeat it in other machines. Same result. I wondered if this would happen too with other functions, so I tried with =MAX, =MIN, =OFFSET, =RIGHT, =MID, and some others. In all this cases I got the expected #NAME? However, trying with =CONVERT gave me:

638844971

Other results were:

=WORKDAY, 1679294519

=CONVERT, 638844971

There's even negative results. =WEEKNUM returns -601489317.

I concluded that this is an exclusive behavior of the Analysis Toolpak add-in functions (for a complete list of functions and results, click here).

Continuing with these tests in my lab (sure...), I tested now with other add-ins functions I've downloaded from the web. For example, with =COUNTDIFF I got: 1769668796. With all the functions of my add-ins I watched the same behavior. After this, I tried with some UDF's. In all cases I got #NAME?, so I had to modify my original theory: This behavior ocurrs with any function that belongs to any add-in (ATP or any other), but it doesn't happen nor with built-in neither user defined functions.

Since all this was kind of an oddity to me, I sent an e-mail to John Walkenbach (brief Spanish biography). This was my original message:

Hi John:

I entered this “formula”:

=CONVERT

Notice that I didn’t put the parenthesis. Excel returned:

638844971

That happens only whit add-in’s functions. With any other built-in function Excel returns, as usual, #NAME?

Other examples:

=NETWORKDAYS produces 840368184

=WEEKNUM, -601489317

=UNIQUEVALUES, -1451032386

=WORKDAY, 1679294519

Always the syntax =[add-in function] (no parenthesis)

I think this is kind of an oddity. Or, if you may explain me where those numbers came from…

Thank you.

This was his reply:

That's pretty strange. It doesn't happen in Excel 2007 because the ATP functions are now built-in.

I have Excel 2003 installed, but I didn't install the ATP. I'll see if I can find the original CD and install the ATP to check it out.

Regards,
John

If any reader have some idea of why Excel returns this mysterious numbers, please post your comments.

14 de octubre de 2008

Lista de funciones

English version.

Por favor lean
esta nota.

=CONVERTIR, 638844971
=DIA.LAB, 1679294519
=FRAC.AÑO, 734789689
=NUM.DE.SEMANA, -601489317
=AMORTIZ.LIN, 2050949212
=AMORTIZ.PROGRE, -760479651
=CANTIDAD.RECIBIDA, 1825046605
=CUPON.DIAS, -1225457601
=CUPON.DIAS.L1, -1886650304
=CUPON.DIAS.L2, 85852225
=CUPON.FECHA.L1, 1738735682
=CUPON.FECHA.L2, -2019426237
=CUPON.NUM, -1858994114
=DURACION, -2027749294
=DURACION.MODIF, -2114781101
=INT.ACUM, -692060092
=INT.EFECTIVO, 226623539
=LETRA.DE.TES.EQV.A.BONO, -600899503
=LETRA.DE.TES.PRECIO, -2087911345
=LETRA.DE.TES.PRECIO, -2087911345
=LETRA.DE.TES.RENDTO, -2095251376
=MONEDA.DEC, -2108293060
=MONEDA.FRAC, -2128216003
=PAGO.INT.ENTRE, 1933180986
=PAGO.PRINC.ENTRE, -1730019269
=PRECIO, -1461321658
=PRECIO.DESCUENTO, 1303642186
=PRECIO.PER.IRREGULAR.1, -2005663660
=RENDTO.PER.IRREGULAR.2, 1307508823
=RENDTO.VENCTO, 526319689
=RENDTO, 1736245319
=RENDTO.DESC, -2110848949
=RENDTO.PER.IRREGULAR.1, 1206845525
=RENDTO.PER.IRREGULAR.2, 1307508823
=RENDTO.VENCTO, 526319689
=TASA.DESC, -1081081780
=TASA.INT, -1046478770
=TASA.NOMINAL, 1421541428
=TIR.NO.PER, -1080295335
=VF.PLAN, 1605632050
=VNA.NO.PER, -841220008
=ALEATORIO.ENTRE, 1788346458
=M.C.D, 1334050856
=M.C.M, -15466455
=MULTINOMIAL, -164822998
=RAIZ2PI, 406257673
=REDOND.MULT, 147456011
=SUMA.SERIES, 1365508135
=SUMA.SERIES, 1365508135
=BESSELI, 571539472
=BESSELJ, 572588046
=BESSELY, 588316687
=BIN.A.DEC, 908066819
=BIN.A.HEX, -141557713
=BIN.A.OCT, -2125463506
=COMPLEJO, -1477705690
=CONVERTIR, 638844971
=DEC.A.BIN, 908066853
=DEC.A.HEX, -1690304477
=DEC.A.OCT, -310378460
=DELTA, -286195700
=FACT.DOBLE, 1382350854
=FUN.ERROR, 2124677124

Parte de las funciones del complemento Herramientas para análisis y resultados obtenidos al tipearlas sin argumentos ni paréntesis.

2 de octubre de 2008

John Walkenbach: Mr. Spreadsheet

John Walkenbach (Mr. Spreadsheet) es considerado la máxima autoridad mundial en hojas de cálculo (léase Excel).
John se dedica de tiempo completo al estudio y desarrollo de productos en Excel. Es autor de The Spreadsheet Page, así como de más de 50 libros de Excel (listados aquí). Ha escrito más de 300 artículos y revisiones en revistas como InfoWorld, PC World, PC/Computing, entre otras. Fue autor durante tres años de la columna mensual de hojas de cálculo de PC World. Asimismo, es autor de The Spreadsheet Page Blog y co-autor de Daily Dose of Excel, dos de los blogs sobre Excel más populares. Desde el 2000, ha sido nombrado Excel MVP por Microsoft por noveno año consecutivo. Ha elaborado numerosos complementos y programas en Excel, la mayoría de ellos gratuitos, y continuamente da conferencias. Recibe las nuevas versiones de Excel seis meses antes que los demás usuarios para evaluarlas. Actualmente reside en Tucson, Arizona, en los Estados Unidos.

El día de ayer, 1 de octubre, recibí un correo del sr. Walkenbach.
En él, Mr. Spreadsheet me da su opinión acerca de un raro comportamiento que observé en Excel 2003, el cual documentaré después, con más calma (por aquello de la piratería, you know...). Esta fue su respuesta tal cual:


That's pretty strange. It doesn't happen in Excel 2007 because the ATP functions are now built-in.

I have Excel 2003 installed, but I didn't install the ATP. I'll see if I can find the original CD and install the ATP to check it out.
Regards,
John


En lo que a Excel se refiere, el haber encontrado algo sobre lo cual Mr. Spreadsheet no tenía noticia, constituye uno de mis mayores logros.
Es exactamente esto lo que me apasiona de Excel: su absoluta inabarcabilidad. Nadie, ni siquiera John, puede preciarse de conocerlo todo.

Nota: ATP son las iniciales de Analysis Toolpak, nombre en inglés de Herramientas para análisis.

1 de octubre de 2008

Resultado inesperado

Cuando ingresamos una función sin argumentos ni paréntesis (por ejemplo, "=PROMEDIO"), al aceptarla, Excel nos devolverá el error #¿NOMBRE?, ya que interpreta que quisimos introducir un nombre, el cual no está definido.

¿O no?

Hace poco, estuve trabajando en un modelo con fechas en Excel. Como necesitaba calcular el número de días hábiles entre dos fechas, me dispuse a utilizar la función DIAS.LAB. Sin embargo, al comenzar a escribir la fórmula, presioné (accidentalmente) Enter justo después de escribir el nombre de la función (es decir "=DIAS.LAB", Enter). Inesperadamente Excel devolvió:

840368184

De dónde viene este número, a qué se refiere o por qué se obtiene precisamente este número y no otro, es algo que ignoro completamente. Como este comportamiento fue completamente inesperado, quise reproducirlo en otros equipos. Mismo resultado. Me pregunté también si esto ocurriría con otras funciones, así que ingresé =SUMA, =MIN, =DESREF, =DERECHA, =EXTRAE, entre otras. En todos estos casos obtuve el resultado esperado #¿NOMBRE?. Sin embargo, al probar con =CONVERTIR Excel devolvió:

638844971.

Otros resultados fueron:

=DIA.LAB devuelve 1679294519

=FRAC.AÑO devuelve 734789689

Incluso hay resultados negativos. =NUM.DE.SEMANA devuelve -601489317.

Concluí que esto es un comportamiento exclusivo de las funciones del complemento Herramientas para análisis (para una lista completa de sus funciones y resultados, clic aquí).

Al continuar con esta "experimentación", probé ahora con funciones de otros complementos que he descargado de la red. Por ejemplo, con =COUNTDIFF obtuve: 1769668796. Similar comportamiento observé con el resto de las funciones de mis complementos. Posteriormente, traté con algunas funciones personalizadas. En todos los casos el resultado fue #¿NOMBRE?. Así pues, tuve que modificar mi teoría original: Este comportamiento ocurre con cualquier función perteneciente a cualquier complemento (Herramientas para análisis o cualquier otro). No ocurre con funciones nativas de Excel ni con funciones definidas por el usuario (FDU o UDF).

Dado que todo esto me pareció muy extraño, envié un correo a John Walkenbach sobre esto. Este fue mi mensaje original:

Hi John:

I entered this “formula”:

=CONVERT

Notice that I didn’t put the parenthesis. The cell shows:

638844971

That happens only whit add-in’s functions. With any other Excel built-in function we get #NAME?

Other examples:

=NETWORKDAYS produces 840368184

=WEEKNUM, -601489317

=UNIQUEVALUES, -1451032386

=WORKDAY, 1679294519

Always the syntax =[add-in function] (no parenthesis)

I think this is kind of an oddity. Or, if you may explain me where those numbers came from…

Thank you.

Esta fue su respuesta:


That's pretty strange. It doesn't happen in Excel 2007 because the ATP functions are now built-in.

I have Excel 2003 installed, but I didn't install the ATP. I'll see if I can find the original CD and install the ATP to check it out.

Regards,
John


Si algún lector tiene idea de por qué se producen estos números misteriosos, por favor háganoslo saber. Entre tanto, veré que obtengo de las distintas discusiones que surjan sobre esto para comentarlo aquí.

Primer año de vida

Este mes se cumple un año desde la publicación de la primera nota de este blog. Estas son las estadísticas del mismo según OneStat.com (gráfica semanal):

OneStat-semanal

Los principales indicadores son (pido una disculpa a los lectores por no utilizar un reporte en Excel):

Total de vistas de página: 14,753

Total de visitas: 8,898

Visitantes únicos por año: 7,802 (hay un discrepancia con lo mostrado por la gráfica de NeoCounter, al margen, debido a que este contador lo agregué ligeramente antes que el de OneStat)

La tercera semana de abril ha sido la más activa con 996 visitas. El día más activo fue el 25 de abril con 255 visitas. Puede verse que las visitas tuvieron un rápido incremento en los primeros meses de vida del blog hasta llegar al máximo observado en abril, así como un descenso menos acelerado, en los siguientes meses. Supongo que esto se debió a la menor frecuencia de publicación de las entradas. No obstante, al retomar una mayor frecuencia de publicación (a partir de agosto), el número de vistas ha vuelto a aumentar. Espero retomar la periodicidad en la publicación mostrada en los primeros meses.

A todos aquellos que han visitado este blog, gracias.

Creative Commons License
Esta obra está bajo una licencia de Creative Commons.
Excel, Excel 2003, Excel 2007 y el logo XL son marcas registradas de Microsoft Corporation.