Funciones de Excel VBA
Hemos visto que podemos usar las funciones de la hoja de trabajo en VBA, es decir, las funciones de la hoja de trabajo de Excel en la codificación de VBA usando el método application.worksheet, pero ¿cómo usamos una función de VBA en Excel? Bueno, tales funciones se llaman funciones definidas por el usuario, cuando un usuario crea una función en VBA, también se puede usar en la hoja de trabajo de Excel.
Aunque tenemos muchas funciones en Excel para manipular los datos, a veces necesitamos tener alguna personalización en las herramientas para que podamos ahorrar nuestro tiempo ya que hacemos algunas tareas repetidamente. Tenemos funciones predefinidas en Excel como SUM, COUNTIF, SUMIF, COUNTIFS, VLOOKUP, INDEX, MATCH en excel, etc. pero hacemos algunas tareas a diario para las cuales un solo comando o función no está disponible en Excel, luego usamos VBA, podemos crear la función personalizada que se llama Funciones definidas por el usuario (UDF).
¿Qué hacen las funciones de VBA?
- Realizan ciertos cálculos; y
- Devuelve un valor
En VBA, al definir la función, usamos la siguiente sintaxis para especificar los parámetros y su tipo de datos.
El tipo de datos aquí es el tipo de datos que contendrá la variable. Puede contener cualquier valor (cualquier tipo de datos u objeto de cualquier clase).
Podemos conectar el objeto con su propiedad o método usando el símbolo de punto o punto (.).
¿Cómo crear funciones personalizadas usando VBA?
Ejemplo
Suponga que tenemos los siguientes datos de una escuela donde necesitamos encontrar las calificaciones totales obtenidas por el estudiante, el resultado y la calificación.
En cuanto a resumir las calificaciones obtenidas por un estudiante individual en todas las materias, tenemos una función incorporada, es decir, SUM, pero para averiguar la calificación y el resultado según los criterios establecidos por la escuela no está disponible en Excel de forma predeterminada .
Esta es la razón por la que necesitamos crear funciones definidas por el usuario.
Paso 1: Encuentre las marcas totales
Primero, encontraremos las marcas totales usando la función SUMA en Excel.
Presione Enter para obtener el resultado.
Arrastre la Fórmula al resto de las celdas.
Ahora para averiguar el resultado (aprobado, reprobado o repetición esencial), el criterio establecido por la escuela es ese.
- Si el estudiante ha obtenido una puntuación superior o igual a 200 como puntuación total de 500 y el estudiante tampoco ha reprobado en ninguna materia (ha obtenido más de 32 en cada materia), entonces un estudiante pasa,
- Si el estudiante obtuvo una puntuación superior o igual a 200, pero el estudiante reprobó en 1 o 2 materias, entonces un estudiante obtuvo la "repetición esencial" en esas materias,
- Si el estudiante obtuvo menos de 200 o reprobó 3 o más materias, entonces el estudiante reprobó.
Paso 2: Cree la función ResultOfStudent
Para crear una función llamada 'ResultOfStudent', necesitamos abrir el "Editor de Visual Basic" usando cualquiera de los métodos siguientes:
- Utilizando la pestaña Desarrollador excel.
Si la pestaña Desarrollador no está disponible en MS Excel, podemos obtenerla mediante los siguientes pasos:
- Haga clic con el botón derecho en cualquier lugar de la cinta y luego elija Personalizar la cinta en Excel ' .
Cuando elegimos este comando, se abre el cuadro de diálogo "Opciones de Excel" .
- Necesitamos marcar la casilla de "Desarrollador" para obtener la pestaña.
- Usando la tecla de atajo, es decir, Alt + F11.
- Cuando abrimos el editor de VBA, necesitamos insertar el módulo yendo al menú Insertar y eligiendo un módulo.
- Necesitamos pegar el siguiente código en el módulo.
Función ResultOfStudents (marcas como rango) como cadena Dim mycell como rango Dim Total como entero Dim CountOfFailedSubject como entero para cada mycell en marcas Total = Total + mycell.Value If mycell.Value = 200 y CountOfFailedSubject 0 Then ResultOfStudents = "Repetición esencial" ElseIf Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Passed" Else ResultOfStudents = "Fallido" End If End Function
La función anterior devuelve el resultado de un estudiante.
Necesitamos entender cómo funciona este código.
La primera declaración, 'Función ResultOfStudents (Marks As Range) As String', declara una función llamada 'ResultOfStudents' que aceptará un rango como entrada para las calificaciones y devolverá el resultado como una cadena.
Dim mycell As Range Dim Total As Integer Dim CountOfFailedSubject As Integer
Estas tres declaraciones declaran variables, es decir,
- 'myCell' como rango,
- 'Total' como entero (para almacenar las calificaciones totales obtenidas por un estudiante),
- 'CountOfFailedSubject' como entero (para almacenar el número de materias en las que un estudiante ha reprobado).
Para cada mycell en marcas Total = Total + mycell.Value If mycell.Value <33 Then CountOfFailedSubject = CountOfFailedSubject + 1 End If Next mycell
Este código busca cada celda en el rango de ' Marcas' y agrega el valor de cada celda en la variable ' Total' , y si el valor de la celda es menor que 33, luego agrega 1 a la variable 'CountOfFailedSubject' .
If Total> = 200 And CountOfFailedSubject 0 Then ResultOfStudents = "Essential Repeat" ElseIf Total> = 200 And CountOfFailedSubject = 0 Then ResultOfStudents = "Aprobado" Else ResultOfStudents = "Fallido" End If
Este código verifica el valor de 'Total' y 'CountOfFailedSubject' y pasa el ' Informe esencial', 'Aprobado' o 'Fallido' de acuerdo con el 'ResultOfStudents'.
Paso 3: Aplicar la función ResultOfStudents para obtener el resultado
La función ResultOfStudents toma notas, es decir, una selección de 5 notas obtenidas por el estudiante.
Ahora seleccione el rango de celdas, es decir, B2: F2.
Arrastre la fórmula al resto de las celdas.
Paso 4: Cree la función 'GradeForStudent' para obtener calificaciones
Ahora, para averiguar la calificación del estudiante, crearemos una función más llamada 'GradeForStudent'.
The code would be:
Function GradeForStudent(TotalMarks As Integer, Result As String) As String If TotalMarks> 440 And TotalMarks 380 And TotalMarks 320 And TotalMarks 260 And TotalMarks = 200 And TotalMarks <= 260 And (Result = "Passed" Or Result = "Essential Repeat") Then GradeForStudent = "E" ElseIf TotalMarks < 200 Or Result = "Failed" Then GradeForStudent = "F" End If End Function
This function assigns a ‘Grade’ to the student based on the ‘Total Marks’ and ‘Result.’
We just need to write the formula and open the brackets in Cell H2 and pressing Ctrl+Shift+A to find out about the arguments.
La función GradeForStudent toma las calificaciones totales (suma de las calificaciones) y el resultado del estudiante como argumento para calcular la calificación.
Ahora seleccione las celdas respectivas, es decir, G2, H2.
Ahora solo necesitamos presionar Ctrl + D después de seleccionar las celdas para copiar las fórmulas.
Podemos resaltar los valores inferiores a 33 con el color de fondo rojo para que averigüemos las materias en las que el alumno ha reprobado.
