excel-vba
Funciones definidas por el usuario (UDF)

Sintaxis

1. Function functionName (argumentVariable As dataType, argumentVariable2 As dataType, opcional argumentVariable3 As dataType) As functionReturnDataType
   Declaración básica de una función. Cada función necesita un nombre, pero no tiene que tomar ningún argumento. Puede tomar 0 argumentos, o puede tomar un número dado de argumentos. También puede declarar un argumento como opcional (lo que significa que no importa si lo proporciona cuando llama a la función). Es una práctica recomendada proporcionar el tipo de variable para cada argumento, y de la misma manera, devolver el tipo de datos que la función misma devolverá.

2. functionName = theVariableOrValueBeingReturned
   Si viene de otros lenguajes de programación, puede estar acostumbrado a la palabra clave Return . Esto no se usa en VBA; en cambio, usamos el nombre de la función. Puede establecerlo en el contenido de una variable o en algún valor suministrado directamente. Tenga en cuenta que si estableció un tipo de datos para el retorno de la función, la variable o los datos que está proporcionando en este momento deben ser de ese tipo de datos.

3. Función final
   Obligatorio. Significa el final del Bloque de código de la Function y, por lo tanto, debe estar al final. El VBE normalmente proporciona esto automáticamente cuando creas una nueva función.

Observaciones

UDF - Hola Mundo

1. Abrir Excel
2. Abra el Editor de Visual Basic (vea Abrir el Editor de Visual Basic )
3. Agregue un nuevo módulo haciendo clic en Insertar -> Módulo:

4. Copie y pegue el siguiente código en el nuevo módulo:

Public Function Hello() As String
'Note: the output of the function is simply the function's name
Hello = "Hello, World !"
End Function

Para obtener :

5. Vuelva a su libro de trabajo y escriba "= Hello ()" en una celda para ver "Hello World".

Permitir referencias de columnas completas sin penalización

Es más fácil implementar algunas UDF en la hoja de trabajo si se pueden pasar referencias de columnas completas como parámetros. Sin embargo, debido a la naturaleza explícita de la codificación, cualquier bucle que incluya estos rangos puede estar procesando cientos de miles de celdas que están completamente vacías. Esto reduce su proyecto de VBA (y libro de trabajo) a un desastre congelado mientras se procesan valores sin valor innecesarios.

Pasar por las celdas de una hoja de trabajo es uno de los métodos más lentos para realizar una tarea, pero a veces es inevitable. Cortar el trabajo realizado a lo que realmente se requiere tiene mucho sentido.

La solución es truncar las referencias de columna completa o fila completa a la propiedad Worksheet.UsedRange con el método Intersect . La siguiente muestra replicará de forma holgada la función SUMIF nativa de una hoja de trabajo, por lo que el criterio de rango de criterios también cambiará de tamaño para adaptarse al sum_range, ya que cada valor en el sum_range debe ir acompañado de un valor en el criterio .

El Application.Caller para un UDF utilizado en una hoja de cálculo es la celda en la que reside. La propiedad .Parent de la celda es la hoja de trabajo. Esto se utilizará para definir el .UsedRange.

En una hoja de código de módulo:

Option Explicit

Function udfMySumIf(rngA As Range, rngB As Range, _
                    Optional crit As Variant = "yes")
    Dim c As Long, ttl As Double
    
    With Application.Caller.Parent
        Set rngA = Intersect(rngA, .UsedRange)
        Set rngB = rngB.Resize(rngA.Rows.Count, rngA.Columns.Count)
    End With
    
    For c = 1 To rngA.Cells.Count
        If IsNumeric(rngA.Cells(c).Value2) Then
            If LCase(rngB(c).Value2) = LCase(crit) Then
                ttl = ttl + rngA.Cells(c).Value2
            End If
        End If
    Next c
    
    udfMySumIf = ttl

End Function

^Sintaxis:
=udfMySumIf(*sum_range*, *criteria_range*, [*criteria*])

Si bien este es un ejemplo bastante simplista, demuestra adecuadamente pasar dos referencias de columna completas (1,048,576 filas cada una) pero solo procesa 15 filas de datos y criterios.

_{Documentación oficial vinculada de MSDN de métodos y propiedades individuales, cortesía de Microsoft ™.}

Contar valores únicos en rango

Function countUnique(r As range) As Long
    'Application.Volatile False ' optional
    Set r = Intersect(r, r.Worksheet.UsedRange) ' optional if you pass entire rows or columns to the function
    Dim c As New Collection, v
    On Error Resume Next   ' to ignore the Run-time error 457: "This key is already associated with an element of this collection".
    For Each v In r.Value  ' remove .Value for ranges with more than one Areas
        c.Add 0, v & ""
    Next
    c.Remove "" ' optional to exclude blank values from the count
    countUnique = c.Count
End Function

Colecciones