Preguntas más frecuentes (FAQs)
Sobre funciones personalizadas
- ¿Que es una función personalizada?
- ¿Es preferible el uso de funciones personalizadas a las fórmulas propias de Excel?
- ¿Dónde hay que situar una función personalizada?
- ¿Cómo se invoca una función personalizada desde una celda de una hoja?
- ¿Es posible determinar en qué categoría aparecerá una función personalizada?
- He escrito una función que debería modificar el color de una celda, pero no funciona. ¿Por qué?.
- ¿Por qué no se recalcula mi función?
- Consejos sobre el uso de funciones personalizadas
Sobre eventos
Sobre VBA
- ¿Es conveniente usar la instrucción Option Explicit al principio de los módulos?
- ¿Qué nivel de seguridad es el más aconsejable en lo relativo a la ejecución de código?
- ¿Es posible crear un libro con una sola hoja sea cual sea el número de hojas predeterminado?
Otras cuestiones
- Problemas al iniciar Excel
- ¿Puedo ver un libro de Excel aunque no tenga Excel?
- ¿Puedo hacer que una o varias celdas del libro parpadeen?
- No consigo abrir los libros de Excel directamente desde el explorador de Windows
- ¿Qué son los "rangos dinámicos"?
- ¿Qué son las "fórmulas matriciales"?
- No consigo que Excel me muestre bien un número de más de 15 dígitos.
- ¿Para qué sirve el signo dólar $ en una fórmula?
- En una celda que debería tener una fecha o una hora aparece un número. ¿Por qué?
Preguntas más frecuentes sobre funciones personalizadas.
¿Qué es una función personalizada?
En Excel, una función personalizada (UDF en inglés) es una función escrita por el usuario o por un programador para añadir alguna funcionalidad no implementada en la aplicación, o bien para simplificar algunas fórmulas que resultan largas, ininteligibles y a menudo casi imposibles de escribir y mantener.
¿Es preferible el uso de funciones personalizadas a las fórmulas propias de Excel?
Rotundamente, no.
Las fórmulas de Excel son mucho más rápidas que cualesquiera que podamos escribir los programadores de VBA, y además ocupan bastante menos espacio en disco. Pero sucede que a veces la fórmula requerida para hacer algo usando las funciones de hoja de cálculo de Excel (suponiendo que existan) resulta larguísima y complicada, haciendo casi imposible su comprensión y mantenimiento, incluso para su propio autor. En el caso de estas fórmulas, que algunos autores denominan “megafórmulas”, puede resultar más cómodo optar por una función personalizada.
¿Dónde hay que situar una función personalizada?
Lo habitual es situarla en el libro donde vaya a ser necesaria. Una vez abierto dicho libro hay que entrar en el editor de VBA (Visual Basic para Aplicaciones) pulsando Alt + F11 o desde Herramientas -> Macro -> Editor de Visual Basic.
Las funciones personalizadas deben ubicarse en módulos creados por el usuario, nunca en los módulos pertenecientes a los objetos de Excel (Thisworkbook, que es el módulo del libro, o en los módulos de las hojas).
Para crear un nuevo módulo hay que hacer Insertar -> Módulo (desde el editor de VBA), aunque las funciones también pueden situarse en módulos existentes, haciendo una doble pulsación sobre el que se desee para abrirlo. Finalmente hay que pegar o escribir el código de la función en la ventana Código.
Si se necesitase que una función personalizada estuviera disponible para muchos libros, se podría evitar tener que ponerla en todos ellos situándola en un libro que estuviera en el directorio Office\XLStart, en el directorio de inicio alternativo (Herramientas->Opciones->solapa "General"), en el libro de macros personal o en un complemento.
Si los libros estuvieran destinados a terceras personas en otras computadoras, habría que situar la función en un módulo dentro de una plantilla y basar los libros en dicha plantilla.
¿Cómo se invoca una función personalizada desde una celda de una hoja?
La sintaxis para utilizar una función personalizada en una hoja es igual a la de las funciones que incorpora Excel. Por ejemplo, supongamos una función personalizada llamada MiSuma cuyo cometido fuera sumar las celdas o rangos que se le pasaran como argumentos. La sintaxis para sumar tres celdas sería:
=MiSuma(A1;B1;C1)
La sintaxis para sumar tres rangos sería:
=MiSuma(A1:A5;B1:B5;C1:C5)
Y también sería posible sumar dos o más rangos con nombre:
=MiSuma(RangoConNombre1;RangoConNombre2)
Es necesario destacar que el separador de los argumentos puede ser el punto y coma o la coma, dependiendo del separador de listas que esté definido en la configuración regional del sistema en Panel de control -> Configuración regional.
¿Es posible determinar en qué categoría aparecerá una función personalizada?
Sí, mediante la propiedad MacroOptions del objeto Application, la cual permite también asignar el texto que aparecerá como comentario para la función cuando se hace Insertar->Función. La sintaxis para la propiedad MacroOptions es:
Application.MacroOptions Macro:="NombreDeLaFunción", Description:="Texto que aparecerá", Category:= NúmeroDeLaCategoría
Donde NúmeroDeLaCategoría es un entero del 1 al 15, según la siguiente tabla:
1.- Funciones financieras
2.- Funciones de fecha y hora
3.- Funciones matemáticas
4.- Funciones estadísticas
5.- Funciones de búsqueda y referencia
6.- Funciones de base de datos
7.- Funciones de texto
8.- Funciones lógicas
9.- Funciones de información
10.- Funciones de comandos
11.- Funciones personalizadas
12.- Funciones de control de macros
12.- Funciones DDE Externas
14.- Funciones definidas por usuario
15.- Funciones de ingeniería
Si no se usa el parámetro Category, o si no establece la propiedad MacroOptions para la función, la misma aparece en la categoría de Funciones definidas por usuario.
He escrito una función que debería modificar el color de una celda, pero no funciona. ¿Por qué?.
Las funciones personalizadas no pueden modificar el entorno de Excel. Por ejemplo, no pueden:
- cambiar el color de una celda, incluso aunque sea la que tiene la llamada a la función
- insertar o borrar filas, columnas u hojas
- ordenar rangos
- etcétera.
¿Por qué no se recalcula mi función personalizada?
Si una o varias celdas con funciones definidas por el usuario no están siendo actualizadas al modificarse los datos, es posible "forzar" a Excel a recalcularlas siempre que se modifique alguna celda declarando la función como volátil, lo que se consigue poniendo la siguiente instrucción:
Application.Volatile
dentro de la propia función, siendo una práctica habitual colocar la instrucción como primera línea de código de la función.
Consejos sobre el uso de funciones personalizadas
- El o los rangos que se le pasen como argumento a la función deben ser lo más ajustados posible en cuanto a su tamaño. Si, por ejemplo, se necesita usar una función personalizada con el rango A1:A50, ése es el rango que se le debe pasar a la función y no, por ejemplo, toda la columna A (A:A) o las 50 filas (1:50). Si no se hiciera así y la función careciera de alguna forma de filtrado de datos para procesar tan sólo los que necesitara, podría suceder que tardase varios segundos en devolver un resultado, lo que significaría que si dicha función se empleara unas cuantas veces en la hoja el rendimiento de la misma podría verse seriamente comprometido.
- Es posible usar cualquier función personalizada en un procedimiento sub o en una función de VBA. La sintaxis es igual que para cualquier función de las que incorpora VBA, por ejemplo:
Variable=FunciónPersonalizada(Argumento1, Argumento2)
Preguntas más frecuentes sobre eventos.
¿Qué es un evento?
Un evento es una acción que puede ser reconocida por un objeto. Cuando se produce un evento, se ejecuta el código asociado al mismo, si lo hay. Ejemplos de eventos podrían ser: al abrir un libro, al imprimir, al seleccionar una hoja, al cambiar la celda seleccionada, etc.
¿Qué tipos de eventos hay?
En Excel hay muchos tipos de eventos. Los eventos de libros, hojas de cálculo y hojas de gráfico pueden usarse simplemente escribiendo el código necesario en su módulo correspondiente (ThisWorkbook para los libros). Los eventos para Application, gráficos incrustados (los objetos ChartObject) y QueryTables requieren la creación de un objeto con eventos, lo que ha de hacerse desde un módulo de clase.
En www.jrgc.es/eventos01.htm hay algunos ejemplos de eventos.
¿Dónde se sitúa el código perteneciente a un evento?
El código para un evento que queramos usar se tiene que situar en el módulo del objeto en el que se produce el evento. Si es el libro, en su módulo (ThisWorkbook), y si es una hoja de cálculo también en su módulo, cuyo nombre (por defecto) es el de la hoja.
Es conveniente dejar que sea Excel el que se encargue de crear el cuerpo del código del evento porque casi todos ellos tienen uno o varios parámetros que han de figurar necesariamente en su declaración. Por lo tanto, para crear un evento lo mejor es entrar en el editor de VBA (Alt + F11), hacer doble click en el objeto para el que se necesita crear el evento (ThisWorkbook o el módulo de una hoja) y cambiar la selección del cuadro de diálogo superior izquierdo de (General) a Workbook o Worksheet, según corresponda. Por defecto Excel creará el evento Workbook_Open si se trata de ThisWorkbook o el evento Worksheet_SelectionChange si se trata del módulo de una hoja, pero si el evento creado no es el que interesa no hay más que borrarlo y seleccionar en el desplegable superior derecho el que se necesite.
Preguntas más frecuentes sobre VBA
¿Es conveniente usar la instrucción Option Explicit al principio de los módulos?
Sí.
Y, además, es aconsejable configurar el editor de VBA para que siempre ponga automáticamente dicha instrucción en todos los módulos nuevos. Esto se hace desde Herramientas->Opciones->Solapa “Editor”, marcando la casilla “Requerir declaración de variables”.
Esta instrucción obliga al programador a declarar las variables de forma explícita, lo que en principio puede resultar molesto pero a la larga facilita la depuración del código y evita errores muy difíciles de localizar, especialmente en proyectos grandes.
¿Qué nivel de seguridad es el más aconsejable en lo relativo a la ejecución de código?
El nivel de seguridad para la ejecución de código por defecto (Herramientas->Macro->Seguridad) está en “Medio”, lo que significa que antes de abrir un libro cuyo código no está firmado por una fuente de confianza Excel preguntará si se autoriza o no su ejecución; y lo mejor es dejarlo así.
Rebajarlo a la seguridad “Mínima” no acarrearía ningún riesgo si jamás se fuera a abrir un libro creado por un tercero, lo que hoy en día, con el correo electrónico y el trabajo en grupo, resulta muy difícil garantizar. Y aumentar la seguridad a su nivel máximo impediría la ejecución de cualquier código, a no ser que el proyecto VBA estuviera firmado por una fuente de confianza.
¿Es posible crear un libro con una sola hoja sea cual sea el número de hojas predeterminado para los libros nuevos?
Sí, es posible, utilizando como plantilla la constante xlWBATWorksheet. Por ejemplo, para crear un nuevo libro con una sola hoja (sea cual sea el número de hojas predeterminado para los libros nuevos) y asignárselo a un objeto WorkBook llamado wkbLibroNuevo, el código sería:
Dim wkbLibroNuevo As Workbook
Set wkbLibroNuevo = Workbooks.Add(xlWBATWorksheet)
Nota: si lo que se desea es que todos los libros nuevos tengan una sola hoja, lo único que hay que hacer es establecerlo así en Herramientas->Opciones->solapa 'General'->Número de hojas en nuevo libro.
Otras cuestiones
Problemas al intentar ejecutar Excel
Si no es posible entrar en Excel, hay algunas cosas que se pueden intentar, aunque en ningún caso puedo garantizar que lo siguiente vaya a solucionar el problema. Pero tampoco lo empeorará.
Probar si se puede entrar desactivando todos los complementos y cualquier libro que se pueda estar cargando automáticamente.
Para ello, hay que hacer Inicio->Ejecutar, y teclear en “Abrir”:
Excel /Automation
Si aparece un mensaje avisando que no es posible encontrar Excel, habría que situarse, desde una ventana de "Símbolo del sistema", en el directorio donde esté su ejecutable (Excel.exe), el cual normalmente es C:\Archivos de programa\Microsoft Office\Office, y teclear lo anterior.
Si se consiguiera entrar así, la imposibilidad de entrar en Excel en modo “normal” se debería a algún complemento o libro que se está cargando en el inicio.
En cuanto a los complementos, habría que desmarcarlos todos desde Herramientas->Complementos e intentar entrar en Excel en modo “normal”. Si se consigue, habría que ir activando de nuevo los complementos uno por uno hasta encontrar al que está provocando el problema.
En cuanto a los libros, sería necesario evitar que se cargaran moviendo a otro directorio cualquier libro que se encontrara en el directorio XLStart o en el directorio de inicio alternativo establecido en Herramientas->Opciones->solapa “General”->Archivos de inicio alternativos en:”, y luego habría que ir situándolos de nuevo en el directorio uno a uno hasta localizar el que da problemas.
Probar si se puede entrar omitiendo la configuración personalizada de las barras de herramientas.
El archivo que controla la disposición de las barras de herramientas se llama Excel.xlb, Excel10.xlb en Office XP o Excel11.xlb en Office 2003. Este archivo tiene cierta tendencia a corromperse: en los foros sobre Excel aparecen con cierta frecuencia mensajes preguntando sobre la forma de solucionar problemas al arrancar. Por ejemplo, se informa de que aparece un mensaje como “error en el módulo MS09.DLL” o que se muestra la pantalla de inicio de Excel pero no pasa de ahí. A menudo el problema está causado por este archivo .xlb.
Un problema añadido en lo que respecta a este archivo es que no es regenerado al reinstalar Office, lo cual tiene cierto sentido porque, al ser el archivo que almacena la disposición personalizada de las barras de herramientas, si se borrara se perdería dicha disposición. Pero esto tiene el grave inconveniente de que si el archivo se corrompe, reinstalar Office no solucionará el problema.
Si el sistema operativo es multiusuario (Windows 2000 o XP, por ejemplo), y hay más de un usuario creado, es posible cambiar de usuario e intentar entrar en Excel. Si se consiguiera, sería casi seguro que el archivo .xlb del usuario con el cual no se consigue entrar en Excel se ha dañado. Pero si en el equipo tan solo hay un usuario se puede saber si el problema al arrancar está causado por el archivo .xlb haciendo Inicio->Ejecutar, y tecleando en “Abrir”:
Excel /S
Si aparece un mensaje avisando que no es posible encontrar Excel, habría que situarse, desde una ventana de "Símbolo del sistema", en el directorio donde esté su ejecutable (Excel.exe), el cual normalmente es C:\Archivos de programa\Microsoft Office\Office, y teclear lo anterior.
Al abrir Excel en modo seguro no se carga la configuración de las barras de herramientas. Si se consiguiera entrar así, lo que habría que hacer es:
- Si en el ordenador tan sólo hay un usuario, buscar el archivo Excel.xlb, Excel10.xlb en Office XP, o Excel11.xlb en Office 2003, y renombrarlo
- Si hay varios usuarios, buscar ese mismo fichero del usuario que no consigue entrar, y renombrarlo.
Luego habría que intentar entrar en Excel en modo normal. Si no se consiguiera, al menos se sabría que el problema no estaba causado por el archivo .xlb. En este caso habría que volver a darle al archivo su nombre anterior. Si se consiguiera entrar en Excel, automáticamente se creará un nuevo .xlb. El único problema es que se habrá perdido la configuración personalizada de las barras de herramientas.
Artículo de la KB de Microsoft sobre el tema
NOTA: Si se hacen copias de seguridad de los datos del equipo, es aconsejable incluir el archivo Excel.xlb en dichas copias. El tamaño del archivo es muy pequeño (normalmente no llega ni a 20 Kbs.), y tener una copia de él nos puede evitar perder la configuración personalizada de las barras de herramientas.
¿Puedo ver un libro de Excel aunque no tenga Excel?
Sí, se puede. Hay disponible un visor de Excel con el que se puede ver un libro ya creado pero no modificarlo:
Descargar visor para Excel desde www.microsoft.com
¿Puedo hacer que una o varias celdas del libro parpadeen?
Sí, es posible hacerlo, pero hay que tener en cuenta que para conseguirlo es necesario ejecutar código casi continuamente, lo cual podría interferir con otro código que tuviera el libro. Lo mejor es usar el parpadeo lo menos posible, y sólo en libros que no tengan más código.
Hay un ejemplo disponible aquí, pero si se abre el libro desde Internet Explorer el código fallará al cerrar el libro, por lo que lo mejor es guardarlo en el disco duro y abrirlo desde allí.
No consigo abrir los libros de Excel desde el explorador de Windows
Éste problema normalmente puede resolverse desde Herramientas->Opciones->solapa 'General', desmarcando la opción "Omitir otras aplicaciones". El siguiente enlace es a un artículo de la KB de Microsoft al respecto:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;211494
¿Qué son los "rangos dinámicos"?
Son Rangos con Nombre que ajustan automáticamente sus dimensiones, dependiendo de las filas y/o columnas que los forman en cada momento.
Por ejemplo, supongamos que en el rango Hoja1!A2:A10 tenemos una lista de nombres (la celda A1 sería el título), pero que dicha lista irá creciendo (o, para el caso, decreciendo) y se necesita tener en todo momento un rango con nombre que haga referencia a la lista completa.
Para conseguirlo, habría que hacer Insertar -> Nombre -> Definir (atajo: Control+F3), ponerle el nombre que se desee al rango y en 'Se refiere a:' la siguiente fórmula:
=DESREF(Hoja1!$A$2;0;0;CONTARA(Hoja1!$A:$A);1)
Lo único que habría que hacer para saber el número de nombres sería usar la siguiente fórmula en cualquier celda (excepto una de la columna A):
=FILAS(NombreDelRango)
En este ejemplo se puede ver cómo trabajan unos rangos con nombre para que un gráfico esté permanentemente actualizado, sin importar si se añaden o se quitan filas del rango de datos de que se nutre.
Cuando se trabaja con rangos dinámicos, hay que tener la precaución de no dejar filas vacías entre medias de los datos.
¿Qué son las "fórmulas matriciales"?
Fórmula matricial es la que se aplica a todas las celdas de
un rango o a todos los valores de una matriz
La notación que se suele usar para este tipo de fórmulas es encerrarlas entre
llaves { }. Las llaves propiamente dichas no hay que teclearlas, sino que
deberían aparecer en la barra de fórmulas al introducirla como matricial, para
lo cual hay que pulsar simultáneamente las teclas Mayúsculas Control y Entrada.
Algunas funciones de Excel, como por ejemplo SUMAPRODUCTO, trabajan con matrices
de forma implícita, por lo que no es necesario introducirlas como matriciales.
En este enlace hay bastantes
ejemplos de fórmulas matriciales, y en
este otro se puede consultar un índice por temas de dichas fórmulas.
No consigo que Excel me muestre bien un número de más de 15 dígitos.
La precisión numérica máxima de Excel es de 16 bytes (15 dígitos más el signo), de forma que si un número es muy grande o muy pequeño, a partir de su dígito 15º Excel comenzará a mostrar ceros.
En este enlace hay una demostración sobre esto, así como una posible forma de aumentar esta precisión numérica usando funciones personalizadas.
¿Para qué sirve el signo dólar en una fórmula?
En Excel, el signo $ se usa para convertir filas y/o columnas en referencias absolutas, de forma que no cambien al ser copiadas y pegadas, o extendidas.
Por ejemplo, si en una celda tenemos una fórmula con una referencia a la celda A1 y queremos que al copiar dicha celda y pegarla en otra la fórmula resultante siga haciendo referencia a A1, lo único que hay que hacer es convertir en absolutas tanto la fila como la columna mediante $A$1.
Es posible convertir en absolutas la fila y/o la columna, de forma independiente.
Mientras se está editando la fórmula, se puede alternar entre los distintos tipos de referencias pulsando F4 (el ciclo es: columna y fila absolutas -> columna relativa y fila absoluta -> columna absoluta y fila relativa -> columna y fila relativas)
En una celda que debería tener una fecha o una hora aparece un número. ¿Por qué?
Excel almacena las fechas y horas como un número en el que la parte entera representa la fecha y la parte decimal la hora. Si en una celda con una fecha u hora aparece un número y se desea que se muestre como fecha u hora, lo único que hay que hacer es darle el formato adecuado a la celda desde Formato->Celdas->solapa 'Número'->Fecha (u hora).