En este tutorial aprenderemos sobre la función Excel VBA
1) ¿Qué es Visual Basic en Excel?
2) ¿Cómo usar VBA en Excel?
3) ¿Cómo crear una función definida por el usuario?
4) ¿Cómo escribir macro?
Cómo escribir código VBA
Excel proporciona al usuario una gran colección de funciones listas para usar, más que suficiente para satisfacer al usuario promedio. Se pueden agregar muchos más instalando los diversos complementos disponibles. La mayoría de los cálculos se pueden realizar con lo que se proporciona, pero no pasa mucho tiempo antes de que desees que haya una función que haga un trabajo en particular y no puedas encontrar nada adecuado en la lista. Necesita una UDF. Una UDF (función definida por el usuario) es simplemente una función que crea usted mismo con VBA. Las UDF a menudo se denominan "funciones personalizadas". Una UDF puede permanecer en un módulo de código adjunto a un libro de trabajo, en cuyo caso siempre estará disponible cuando ese libro de trabajo esté abierto. Alternativamente, puede crear su propio complemento que contenga una o más funciones que puede instalar en Excel como un complemento comercial. También se puede acceder a las UDF mediante módulos de código. A menudo, los desarrolladores crean las UDF para que funcionen únicamente dentro del código de un procedimiento de VBA y el usuario nunca se da cuenta de su existencia. Como cualquier función, la UDF puede ser tan simple o tan compleja como desee. Comencemos con uno fácil …
Una función para calcular el área de un rectángulo
¡Sí, sé que podrías hacer esto en tu cabeza! El concepto es muy simple para que pueda concentrarse en la técnica. Suponga que necesita una función para calcular el área de un rectángulo. Miras la colección de funciones de Excel, pero no hay ninguna adecuada. Este es el cálculo a realizar:
AREA = LARGO x ANCHO
Abra un nuevo libro de trabajo y luego abra el Editor de Visual Basic (Herramientas> Macro> Editor de Visual Basic o ALT + F11).
Necesitará un módulo en el que escribir su función, así que elija Insertar> Módulo. En el tipo de módulo vacío: Área funcional y presione INGRESAREl editor de Visual Basic completa la línea por usted y agrega una línea de función final como si estuviera creando una subrutina. Hasta ahora se ve así …
Área de función () Función final
Coloque el cursor entre los corchetes después de "Área". Si alguna vez se preguntó para qué sirven los corchetes, ¡está a punto de averiguarlo! Vamos a especificar los "argumentos" que tomará nuestra función (un argumento es un dato necesario para realizar el cálculo). Escribe Largo como doble, Ancho como doble y haga clic en la línea vacía debajo. Tenga en cuenta que a medida que escribe, aparece un cuadro de desplazamiento que enumera todas las cosas apropiadas para lo que está escribiendo.
Esta característica se llama Miembros de la lista automática. Si no aparece tampoco está apagado (enciéndalo en Herramientas> Opciones> Editor) o es posible que haya cometido un error de escritura anteriormente. Es una verificación muy útil de su sintaxis. Busque el elemento que necesita y haga doble clic en él para insertarlo en su código. Puede ignorarlo y simplemente escribir si lo desea. Su código ahora se ve así …
Área de función (longitud como doble, ancho como doble) Función final
Declarar el tipo de datos de los argumentos no es obligatorio pero tiene sentido. Podrías haber escrito Largo, ancho y lo dejó así, pero advertir a Excel qué tipo de datos esperar ayuda a que su código se ejecute más rápidamente y detecte errores en la entrada. los doble el tipo de datos se refiere al número (que puede ser muy grande) y admite fracciones. Ahora para el cálculo en sí. En la línea vacía, primero presione el PESTAÑA clave para sangrar su código (para que sea más fácil de leer) y escriba Área = Largo * Ancho. Aquí está el código completo …
Área de función (longitud como doble, ancho como doble) Área = longitud * función de fin de ancho
Notará que aparece otra de las funciones de ayuda del Editor de Visual Basic mientras escribe, Información rápida automática…
No es relevante aquí. Su propósito es ayudarlo a escribir funciones en VBA, indicándole qué argumentos se requieren. Puede probar su función de inmediato. Cambie a la ventana de Excel e ingrese las cifras de Largo y Ancho en celdas separadas. En una tercera celda ingrese su función como si fuera una de las integradas. En este ejemplo, la celda A1 contiene la longitud (17) y la celda B1 el ancho (6.5). En C1 escribí = área (A1, B1) y la nueva función calculó el área (110,5)…
A veces, los argumentos de una función pueden ser opcionales. En este ejemplo podríamos hacer Ancho argumento opcional. Supongamos que el rectángulo es un cuadrado con el mismo largo y ancho. Para evitar que el usuario tenga que ingresar dos argumentos, podríamos dejar que ingresen solo la Longitud y hacer que la función use ese valor dos veces (es decir, multiplique Longitud x Longitud). Entonces la función sabe cuándo puede hacer esto, debemos incluir un Declaración IF para ayudarlo a decidir. Cambie el código para que se vea así …
Área de función (largo como doble, ancho opcional como variante) Si falta (ancho) Entonces área = largo * largo Else área = largo * ancho Fin si final función
Tenga en cuenta que el tipo de datos para Ancho se ha cambiado a Variante para permitir valores nulos. La función ahora permite al usuario ingresar solo un argumento, p. Ej. = área (A1).La instrucción IF en la función verifica si se ha proporcionado el argumento Ancho y calcula en consecuencia …
Una función para calcular el consumo de combustible
Me gusta controlar el consumo de combustible de mi automóvil, de modo que cuando compro combustible anoto el kilometraje y la cantidad de combustible que se necesita para llenar el tanque. Aquí en el Reino Unido el combustible se vende en litros. El cuentakilómetros del coche (OK, entonces es un cuentakilómetros) registra la distancia en millas. Y como soy demasiado viejo y estúpido para cambiar, solo entiendo MPG (millas por galón). Ahora, si piensas que todo eso es un poco triste, ¿qué tal esto? Cuando llego a casa, abro Excel e ingreso los datos en una hoja de trabajo que calcula el MPG por mí y traza el rendimiento del automóvil. El cálculo es la cantidad de millas que el automóvil ha viajado desde el último llenado dividido por la cantidad de galones de combustible usados …
MPG = (MILLAS ESTE LLENADO - MILLAS ÚLTIMO LLENADO) / GALONES DE COMBUSTIBLE
pero porque el combustible viene en litros y hay 4.546 litros en un galón …
MPG = (MILLAS ESTE LLENADO - MILLAS ÚLTIMO LLENADO) / LITROS DE COMBUSTIBLE x 4.546
Así es como escribí la función …
Función MPG (StartMiles como entero, FinishMiles como entero, Litros como simple) MPG = (FinishMiles - StartMiles) / Litros * 4.546 Función final
y así es como se ve en la hoja de trabajo …
No todas las funciones realizan cálculos matemáticos. Aquí hay uno que proporciona información …
Una función que da el nombre del día
A menudo me preguntan si hay una función de fecha que indique el día de la semana como texto (por ejemplo, el lunes). La respuesta es no *, pero es bastante fácil crear uno. (* Anexo: ¿Dije que no? ¡Consulte la nota a continuación para ver la función que olvidé!). Excel tiene la función WEEKDAY, que devuelve el día de la semana como un número del 1 al 7. Puedes elegir qué día es 1 si no te gusta el predeterminado (domingo). En el siguiente ejemplo, la función devuelve "5" que sé que significa "jueves".
Pero no quiero ver un número, quiero ver "jueves". Podría modificar el cálculo agregando una función VLOOKUP que se refiera a una tabla en algún lugar que contenga una lista de números y una lista correspondiente de nombres de días. O podría tener todo autónomo con múltiples declaraciones IF anidadas. ¡Demasiado complicado! La respuesta es una función personalizada …
Función DayName (InputDate como fecha) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Seleccione Caso DayNumber Caso 1 DayName = "Domingo" Caso 2 DayName = "Lunes" Caso 3 DayName = "Martes" Caso 4 DayName = "Miércoles" Caso 5 DayName = "Jueves" Caso 6 DayName = "Viernes" Caso 7 DayName = "Sábado" Fin Seleccione Finalizar función
He llamado a mi función "DayName" y toma un solo argumento, al que llamo "InputDate" que (por supuesto) tiene que ser una fecha. Así es como funciona…
- La primera línea de la función declara una variable que he llamado "DayNumber" que será un entero (es decir, un número entero).
- La siguiente línea de la función asigna un valor a esa variable usando la función WEEKDAY de Excel. El valor será un número entre 1 y 7. Aunque el valor predeterminado es 1 = domingo, lo he incluido de todos modos para mayor claridad.
- Finalmente un Declaración de caso examina el valor de la variable y devuelve el texto apropiado.
Así es como se ve en la hoja de trabajo …
Acceder a sus funciones personalizadas
Si un libro de trabajo tiene un módulo de código VBA adjunto que contiene funciones personalizadas, esas funciones se pueden abordar fácilmente dentro del mismo libro de trabajo como se muestra en los ejemplos anteriores. Utiliza el nombre de la función como si fuera una de las funciones integradas de Excel.
También puede encontrar las funciones enumeradas en el Asistente de funciones (a veces llamado la herramienta Pegar función). Utilice el asistente para insertar una función de la forma habitual (Insertar> Función).
Desplácese hacia abajo en la lista de categorías de funciones para encontrar Usuario definido y selecciónelo para ver una lista de UDF disponibles …
Puede ver que las funciones definidas por el usuario carecen de una descripción que no sea el inútil mensaje "No hay ayuda disponible", pero puede agregar una breve descripción …
Asegúrese de estar en el libro de trabajo que contiene las funciones. Ir a Herramientas> Macro> Macros. No verá sus funciones enumeradas aquí, ¡pero Excel las conoce! En el Nombre de macro cuadro en la parte superior del cuadro de diálogo, escriba el nombre de la función, luego haga clic en el cuadro de diálogo Opciones botón. Si el botón está atenuado, o ha escrito mal el nombre de la función, o está en el libro de trabajo incorrecto, ¡o no existe! Esto abre otro cuadro de diálogo en el que puede ingresar una breve descripción de la función. Hacer clic OK para guardar la descripción y (aquí está el bit confuso) haga clic en Cancelar para cerrar el cuadro de diálogo Macro. Recuerde guardar el libro de trabajo que contiene la función. La próxima vez que vaya al Asistente de funciones, su UDF tendrá una descripción …
Al igual que las macros, las funciones definidas por el usuario se pueden utilizar en cualquier otro libro siempre que el libro que las contiene esté abierto. Sin embargo, no es una buena práctica hacer esto. Ingresar la función en un libro de trabajo diferente no es simple. Debe agregar el nombre de su libro de trabajo host al nombre de la función. Esto no es difícil si confía en el Asistente de funciones, pero es torpe para escribir manualmente. El Asistente de funciones muestra los nombres completos de las UDF en otros libros de trabajo …
Si abre el libro de trabajo en el que utilizó la función en un momento en el que el libro de trabajo que contiene la función está cerrado, verá un mensaje de error en la celda en la que utilizó la función. ¡Excel lo ha olvidado! Abra el libro de trabajo del host de la función, vuelva a calcular y todo estará bien nuevamente. Afortunadamente, hay una mejor manera.
Si desea escribir funciones definidas por el usuario para su uso en más de un libro de trabajo, el mejor método es crear un Excel Complemento. Descubra cómo hacer esto en el tutorial Crear un complemento de Excel.
Apéndice
¡Realmente debería saberlo mejor! ¡Nunca, nunca digas nunca! Habiéndote dicho que no hay una función que proporcione el nombre del día, ahora he recordado la que sí puede. Mira este ejemplo …
La función TEXTO devuelve el valor de una celda como texto en un formato de número específico. Entonces en el ejemplo podría haber elegido = TEXTO (A1, "ddd") para devolver "Thu", = TEXTO (A1, "mmmm") para devolver "septiembre", etc. La ayuda de Excel tiene más ejemplos de formas de utilizar esta función.
Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook.
Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbenos a sitio de correo electrónico