En este artículo, analizaremos los problemas que puede encontrar al usar funciones personalizadas en sus libros de trabajo. Trataré de mostrarle qué los causa y qué tan fácil se pueden resolver.
Esto es de lo que hablaremos:
Anteriormente hablamos sobre qué es una función personalizada, cómo crearla y usarla. Si siente que necesita repasar los conceptos básicos de las UDF de antemano, tómese un descanso y consulte mi artículo anterior.
¿Por qué Excel UDF no vuelve a calcular?
Cuando realiza cambios en su libro de trabajo, Excel no volverá a calcular todas las fórmulas que tiene allí. Actualizará los resultados de aquellas fórmulas que están vinculadas solo a las celdas modificadas.
Pero esto se refiere a las funciones estándar de Excel. En cuanto a las personalizadas, Excel no puede validar el código VBA e identificar otras celdas que también podrían afectar el resultado de la función personalizada. Por lo tanto, su fórmula personalizada no puede cambiar cuando realiza cambios en el libro.
Para solucionar el problema, solo tendrás que usar Aplicación Volátil declaración. Consulte el siguiente capítulo para obtener instrucciones paso a paso sobre cómo aplicarlo.
Funciones personalizadas volátiles frente a no volátiles
De forma predeterminada, las funciones personalizadas en Excel no son volátiles. Esto significa que la UDF se vuelve a calcular solo si cambia el valor de cualquiera de las celdas a las que hace referencia. Pero si se cambia el formato de celda, el nombre de la hoja de trabajo, el nombre del archivo, no se producirán cambios en UDF.
Pasemos de las palabras a los ejemplos. Por ejemplo, debe escribir el nombre del libro de trabajo en una celda. Para hacer esto, cree una función personalizada:
Función WorkbookName() Como fila
WorkbookName = EsteLibro.Nombre
El fin Función
Ahora imagina el siguiente caso. Escribiste una fórmula personalizada =NombreLibro() en la celda y obtuve el nombre del archivo allí. En algún momento, decidió cambiar el nombre del archivo y lo guardó con un nombre diferente. Pero miras el valor en la celda y ves que no ha cambiado. Todavía hay un nombre de archivo antiguo que ya no es correcto.
Debido a que no hay argumentos para esta función, la función no se vuelve a calcular (incluso si cambia el nombre del libro, lo cierra y luego lo vuelve a abrir).
Nota. Para recalcular todas las funciones en su archivo, puede usar Ctrl+Alt+F9 atajo.
hay una manera mas facil? Para que la fórmula vuelva a calcular cada vez que cambia la hoja de trabajo, necesita una línea de código adicional. Pegue el siguiente fragmento de código al comienzo de su función:
Aplicación Volátil
Entonces su código se verá así:
Función WorkbookName() Como fila
Application.Volatile WorkbookName = EsteLibro.Nombre
El fin Función
Ahora su UDF es volátil, por lo que se volverá a calcular automáticamente si se ha vuelto a calcular cualquier celda de la hoja de trabajo o si se ha producido algún cambio en el libro de trabajo. Tan pronto como cambie el nombre del archivo, verá esa actualización inmediatamente.
Nota. Tenga en cuenta que demasiadas funciones volátiles pueden ralentizar su Excel. Después de todo, hay demasiadas funciones personalizadas que realizan cálculos complejos y operan continuamente en grandes rangos de datos.
Por lo tanto, recomiendo usar la volatilidad solo donde realmente se necesita.
Por qué las funciones personalizadas no están disponibles
Cuando ingresa las primeras letras del nombre de una función personalizada, aparece en la lista desplegable junto a la celda de entrada, al igual que las funciones estándar de Excel.
Sin embargo, esto no siempre sucede. ¿Qué errores pueden causar esta situación?
Si tiene Excel 2003-2007, el UDF nunca aparece en la lista desplegable. Allí puede ver solo las funciones estándar.
Pero incluso si está utilizando una versión más nueva de Excel, hay otro error que puede cometer accidentalmente.
Verá, la función personalizada debe estar en un módulo VBA estándar llamado Módulo. Cuando agrega un nuevo módulo para escribir código de función, se crea automáticamente una carpeta de Módulos en la que se escriben todos los módulos.
Pero a veces sucede que no se crea un nuevo módulo. En la siguiente captura de pantalla, puede ver que el código de la función personalizada se encuentra en el módulo «Objetos de Microsoft Excel» junto con ThisWorkbook.
El punto es que no puede colocar una función personalizada en el área de código de una hoja de trabajo o libro de trabajo. En este caso, la función no funcionará. Además, no aparecerá en la lista desplegable de funciones. Entonces el código siempre debe estar en la carpeta. módulos.
No se muestra el texto de ayuda de la función personalizada de Excel
Otro problema que puede ocurrir es la sugerencia que ve cuando pega una función personalizada. Si usa una función estándar, siempre verá una información sobre herramientas para la función y sus argumentos. Pero, ¿qué pasa con las UDF?
Si tiene muchas funciones personalizadas, será extremadamente difícil recordar qué cálculos hace cada una. Será aún más difícil recordar qué argumentos usar. Creo que sería una buena idea tener una descripción de sus funciones personalizadas como recordatorio.
Para esto, sugeriría usar Aplicación.MacroOpciones método. Le ayudará a mostrar no solo la descripción de la función, sino también cada uno de sus argumentos en la ventana Asistente de funciones. Verá esta ventana cuando haga clic en el botón Fx en la barra de fórmulas.
Veamos cómo agregar una sugerencia de este tipo a sus UDF En el artículo anterior, analizamos la función personalizada GetMaxBetween. Encuentra el número máximo en el rango especificado y toma tres argumentos: un rango de valores numéricos y un valor máximo y mínimo para buscar.
Ahora agregaremos una descripción para esta función personalizada. Para hacer esto, compila y ejecuta Aplicación.MacroOpciones dominio. Para ObtenerMaxEntre función, puede ejecutar el siguiente comando:
Bajo la Registrar UDF()
Oscuro strFuncName Como fila «el nombre de la función que desea registrar
Oscuro Descripción de la calle Como fila‘ la descripción de la función en sí
Oscuro strArgs() Como fila ‘descripción de los argumentos de la función
‘ Registrar la función GetMaxBetween
Redime strArgs (1 a 3) «El número de argumentos en su función
strFuncName = «ObtenerMaxEntre»
descripción de cadena = «Número máximo en el rango especificado»
strArgs (1) = «Rango de valores numéricos»
strArgs (2) = «Límite de intervalo inferior»
strArgs (3) =«Límite de intervalo superior»
Application.MacroOptions Macro := strFuncName, _ Descripción := strDescr, _ ArgumentDescriptions := strArgs, _ Categoría : =«Mis funciones personalizadas»
El fin Bajo la
o
Bajo la RegistrarUDF() Aplicación.MacroOpciones Macro : = «ObtenerMaxEntre»_ Descripción: = «Número máximo en el rango especificado»_ Categoría: = «Mis funciones personalizadas»_ ArgumentDescriptions := Matriz (_
«Rango de valores numéricos»_
«Límite de intervalo inferior» _
«Límite de intervalo superior»)
El fin Bajo la
Variable str FuncName es el nombre de la función. Descripción de la calle – descripción de la función. El strArgs Las variables contienen punteros a cada argumento.
Quizás se pregunte cuál es el cuarto argumento de Application.MacroOptions. Este argumento opcional se llama Categoría e indica la clase de funciones de Excel que está personalizando ObtenerMaxEntre() La función se colocará en. Puede asignarle el nombre de cualquiera de las categorías existentes: Matemáticas y Trigonometría, Estadística, Lógica, etc. Puede especificar un nombre para la nueva categoría en la que colocará las entidades que cree. Si no utiliza el argumento Categoría, la función personalizada se colocará automáticamente en la categoría «Definido por el usuario».
Pegue el código de la función en la ventana del módulo:
Luego haga clic en el botón «Ejecutar». El comando realizará todas las configuraciones para su uso Efectos botón contigo ObtenerMaxEntre() función.
Si intenta insertar una función en una celda usando La función de inserción herramienta, verás que ahí está tu ObtenerMaxEntre la función está en la categoría «Mis funciones personalizadas»:
Simplemente puede comenzar a escribir el nombre de la función en la celda y verá su función personalizada en la lista desplegable de funciones que puede seleccionar.
Entonces llame al El asistente de funciones con el botón Fx.
Consejo. También puede utilizar la combinación de teclas CTRL + A para abrir el asistente de funciones.
En el El asistente de funciones verá una descripción de su función, así como una pista para el primer argumento. Si pasa el cursor sobre el segundo o el tercer argumento, también verá sugerencias para ellos.
Si desea cambiar el texto de estas sugerencias, cambie los valores Descripción de la calle y strArgs variables en Registrar UDF() código. Entonces corre Registrar UDF() ordenar de nuevo.
Si desea deshacer todas las configuraciones realizadas y eliminar la descripción de la función, ejecute este código:
Bajo la UnregisterUDF () Application.MacroOptions Macro: = «ObtenerMaxEntre»_ Descripción: = desnudoArgumentoDescripciones: = desnudoCategoría: = desnudo
El fin Bajo la
Hay una forma más de obtener una pista cuando ingresa una función personalizada. Ingrese el nombre de la función y luego presione la tecla Ctrl + Mayús + A:
=GetMaxBetween( + Ctrl + Mayús + A
Verá una lista de todos los argumentos de la función:
Desafortunadamente, no verá la descripción de la función y sus argumentos aquí. Pero si los nombres de los argumentos son lo suficientemente informativos, también podrían ser útiles. Aún así, es mejor que nada 🙂
Tomará un poco más de trabajo crear inteligencias para UDF que funcionen como funciones estándar de Excel. Desafortunadamente, Microsoft no ofrece ninguna opción. La única solución disponible actualmente es una extensión Excel-DNA IntelliSense. Puede encontrar más información sobre el sitio web del desarrollador.
Esperamos que estas instrucciones lo ayuden a solucionar problemas cuando su función personalizada no funcione o no funcione de la manera que desea. Sin embargo, si su UDF aún no funciona, describa el problema con precisión en la sección Comentarios. Intentaremos resolverlo y encontrar la solución para usted;)
