En este tutorial aprenderemos sobre la función Excel VBA {vacío} 1) ¿Qué es Visual Basic en Excel?

{vacío} 2) ¿Cómo usar VBA en Excel?

{vacío} 3) ¿Cómo crear una función definida por el usuario?

{vacío} 4) ¿Cómo escribir macro?

Cómo escribir VBAcode 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:

ÁREA = 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 módulo vacío escriba: Function Area y presione ENTER. El Editor de Visual Basic completa la línea por usted y agrega una línea de End Function como si estuviera creando una subrutina. Hasta ahora se ve así …​

Function Area()

End Function

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 una pieza de información necesaria para hacer el cálculo). Escriba Length como doble, Width 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.

img2

Esta función se llama Lista automática de miembros. Si no aparece, está apagado (actívelo en Herramientas> Opciones> Editor) o es posible que haya cometido un error de escritura anteriormente. Es una comprobació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. Tu código ahora se ve así …​

Function Area(Length As Double, Width As Double)

End Function

Declarar el tipo de datos de los argumentos no es obligatorio pero tiene sentido. Podría haber escrito Length, Width y dejarlo así, pero advertir a Excel qué tipo de datos esperar ayuda a que su código se ejecute más rápidamente y detecta errores en la entrada. El tipo de datos double se refiere al número (que puede ser muy grande) y permite fracciones. Ahora para el cálculo en sí. En la línea vacía, primero presione la tecla TAB para sangrar su código (haciendo que sea más fácil de leer) y escriba Area = Length * Width. Aquí está el código completo …​

Function Area(Length As Double, Width As Double)

Area = Length * Width

End Function

Notará que aparece otra de las funciones de ayuda del Editor de Visual Basic mientras escribía, Auto Quick Info …​

img31

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) …​

img3

A veces, los argumentos de una función pueden ser opcionales. En este ejemplo, podríamos hacer que el argumento Width sea opcional. Supongamos que el rectángulo es un cuadrado con Longitud y Anchura iguales. 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). Para que la función sepa cuándo puede hacer esto, debemos incluir una instrucción IF para ayudarla a decidir. Cambia el código para que se vea así …​

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Else

Area = Length * Width

End If

End Function

Tenga en cuenta que el tipo de datos de Width se ha cambiado a Variant 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 comprueba si se ha proporcionado el argumento Ancho y calcula en consecuencia …​

img4

Una función para calcular el consumo de combustible Me gusta controlar el consumo de combustible de mi automóvil, así 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 desempeño del auto. El cálculo es la cantidad de millas que ha recorrido el automóvil 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 …​

Function MPG(StartMiles As Integer, FinishMiles As Integer, Litres As Single)

MPG = (FinishMiles - StartMiles) / Litres * 4.546

End Function

y así es como se ve en la hoja de trabajo …​

img5

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 da el día de la semana como texto (por ejemplo, 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. Puede elegir qué día es 1 si no le gusta el predeterminado (domingo). En el siguiente ejemplo, la función devuelve «5» que sé que significa «jueves».

img6

Pero no quiero ver un número, quiero ver «jueves». Podría modificar el cálculo agregando una función BUSCARV 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 …​

Function DayName(InputDate As Date)

Dim DayNumber As Integer

DayNumber = Weekday(InputDate, vbSunday)

Select Case DayNumber

Case 1

DayName = "Sunday"

Case 2

DayName = "Monday"

Case 3

DayName = "Tuesday"

Case 4

DayName = "Wednesday"

Case 5

DayName = "Thursday"

Case 6

DayName = "Friday"

Case 7

DayName = "Saturday"

End Select

End Function

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, una 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 …​

img7

=== === Acceso 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 denominado 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 Definido por el usuario y selecciónelo para ver una lista de UDF disponibles …​

img8

img9

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. Vaya a Herramientas> Macro> Macros. No verá sus funciones enumeradas aquí, ¡pero Excel las conoce! En el cuadro Nombre de macro en la parte superior del cuadro de diálogo, escriba el nombre de la función y luego haga clic en el botón Opciones del cuadro de diálogo. 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. Haga clic en Aceptar 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 …​

img10

Al igual que las macros, las funciones definidas por el usuario se pueden utilizar en cualquier otro libro de trabajo siempre que el libro de trabajo 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 cualquier UDF en otros libros de trabajo …​

img11

Si abre el libro de trabajo en el que usó la función en un momento en que el libro de trabajo que contiene la función está cerrado, verá un mensaje de error en la celda en la que usó 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 usarlas en más de un libro, el mejor método es crear un complemento de Excel. 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 …​

img12

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 = TEXT (A1, «ddd») para devolver «Thu», = TEXT (A1, «mmmm») para devolver «September», etc. La ayuda de Excel tiene algunos ejemplos más de formas de uso 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íbanos a [email protected] __