En este artículo, vamos a aprender cómo crear funciones definidas por el usuario en Microsoft Excel usando el VBA.

Función definida por el usuario: – Microsoft Excel ya tiene muchas funciones, pero aún todos tienen diferentes requisitos, situación, podemos crear nuestra propia función según el requisito que se llama Función definida por el usuario. Podemos usar la función definida por el usuario como otras funciones en Excel.

A continuación se muestran los temas para los que crearemos la función definida por el usuario: 1). ¿Cómo contar el número de palabras en celda o rango?

2). ¿Cómo extraer una palabra de una oración o celda en Excel?

3). ¿Cómo crear la fórmula para ISO?

4). ¿Cómo saber la hoja de trabajo y el nombre del libro de trabajo usando el VBA?

5). ¿Cómo extraer la primera y la última palabra de una celda en Excel?

¿Cómo crear la función definida por el usuario para contar el número de palabras en la celda o rango? Tenemos datos en la hoja 1 en la que tenemos algunas direcciones, por lo que queremos contar las palabras en una celda o un rango creando la función definida por el usuario a través de VBA en Excel.

image 1

Para que la función definida por el usuario, siga los pasos que se indican a continuación: – * Abra la página VBA y presione la tecla Alt + F11.

  • Insertar un módulo.

Escriba el código mencionado a continuación:

Function WORDSCOUNT(rRange As Range) As Long

Dim rCell As Range

Dim Count As Long

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1

Next rCell

WORDSCOUNT = lCount

End Function

Explicaciones del código: – Para hacer la función definida por el usuario, iniciamos el código en el nombre de la función y definimos las variables. Hemos utilizado «For Each bucles» en el código para contar las palabras en el rango.

¿Cómo utilizar esta función en Excel?

Para utilizar esta función, siga los pasos que se indican a continuación: – * Vaya a la hoja de Excel.

  • Para contar las palabras de una celda, ingrese la fórmula en la celda D7.

  • = CUENTA PALABRAS (C7), la celda C7 es la celda en la que queremos calcular las palabras.

  • La función devolverá 6, lo que significa que la celda C7 contiene 6 palabras.

  • Para hacer el mismo cálculo para el resto de celdas, copie la misma fórmula y péguela en el rango.

image 2

  • Para contar las palabras en el rango, use la fórmula como = CUENTA PALABRAS (C7: C16) y presione Entrar.

  • La función devolverá el recuento de palabras.

image 3

Nota: – Esta UDF será útil para contar las palabras en un rango o en una sola celda.

Ahora escribiremos el código para contar la palabra usando el delimitador especificado (,). Siga los pasos que se indican a continuación: –

Function SEPARATECOUNTWORDS(rRange As Range, Optional separator As Variant) As Long

Dim rCell As Range

Dim Count As Long

If IsMissing(separator) Then

separator = ","

End If

For Each rCellInrRange

lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), separator, ""))

Next rCell

SEPARATECOUNTWORDS = lCount

End Function

Para utilizar esta función, siga los pasos que se indican a continuación: –

  • Vaya a la hoja de Excel.

  • Para contar los delimitadores específicos en la palabra, usaremos esta función definida.

  • = SEPARATECOUNTWORDS (C7) y presione Enter.

  • La función devolverá el recuento de los delimitadores específicos.

image 4

¿Cómo extraer una palabra de una oración o celda en Microsoft Excel usando el VBA? Tenemos datos en sheet1. En las que tenemos algunas direcciones, queremos extraer las palabras de una oración o celda o un rango creando la función definida por el usuario a través de VBA en Excel.

image 5

Para hacer la función definida por el usuario, siga los pasos que se indican a continuación: –

  • Abra la página VBA y presione la tecla Alt + F11.

  • Insertar un módulo.

Escriba el código mencionado a continuación *: –

Function GETWORD(Text As Variant, N As Integer, Optional Delimiter As Variant) As String

If IsMissing(Delimiter) Then

Delimiter = " "

End If

GETWORD = Split(Text, Delimiter)(N - 1)

End Function

Explicación del código: – En el código mencionado anteriormente, hemos mencionado el nombre de la función con las variables. Y luego habíamos definido los criterios para extraer la palabra de oración o celda.

Ahora aprenderemos a usar esta fórmula. Siga los pasos que se indican a continuación: – * Vaya a la hoja de Excel.

  • Use esta fórmula en la celda D7.

  • = GETWORD (C7,2) y presione Entrar.

  • La función devolverá la segunda palabra de la celda porque en la fórmula mencionamos la palabra 2 ^ nd ^ número. Si desea recuperar la palabra que se encuentra en la posición 3 ^ rd ^, debe cambiar el número de 2 a 3 en la fórmula.

image 6

¿Cómo crear la fórmula del número de semana ISO en Microsoft Excel usando el VBA? Aprenderemos cómo podemos crear una fórmula de número de semana ISO en Excel con esta UDF. Esta función la usaremos para identificar que la fecha mencionada pertenece a qué número de semana del año.

Tenemos una lista de fechas en la hoja y, en la segunda columna, queremos recuperar los números de la semana.

image 7

Para crear la UDF para este requisito, siga los pasos que se indican a continuación: – * Abra la página VBA y presione la tecla Alt + F11.

  • Insertar un módulo.

  • Escriba el código mencionado a continuación: –

Function ISOWEEKNUMBER(Indate As Date) As Long

Dim Dt As Date

Dt = DateSerial(Year(Indate - Weekday(Indate - 1) + 4), 1, 3)

ISOWEEKNUMBER = Int((Indate - Dt + Weekday(Dt) + 5) / 7)

End Function

Explicación del código: -: – En el código anterior, hemos mencionado el nombre de la función con las variables. Y luego establecimos el valor de la fecha y luego definimos los criterios de la función «ISOWEENUMBER».

¿Cómo podemos usar esta función en nuestro archivo de Excel?

  • Vaya a la hoja de Excel.

  • Ingrese la fórmula en la celda D7.

  • = ISOWEEKNUMBER (C7) y presione Entrar.

  • La función devolverá la semana para la fecha ingresada en la celda.

Ahora, para recuperar el número de la semana para cada fecha, copie la misma fórmula en el rango.

image 8

Ahora aprenderemos cómo devolver las normas ISO a principios de año en Excel: primer lunes del año.

Básicamente, esta función comprobará que el 1 ^ st ^ lunes del año va a caer en qué fecha y luego comenzará a calcular el número de semanas a partir de esa fecha. Veamos cómo podemos crear la UDF para este requisito.

image 9

Siga los pasos que se indican a continuación: – * Abra la página VBA y presione la tecla Alt + F11.

  • Insertar un módulo.

Escriba el código mencionado a continuación *: –

Function ISOSTYR(Year As Integer) As Date

Dim WD As Integer

Dim NY As Date

NY = DateSerial(Year, 1, 1)

WD = (NY - 2) Mod 7

If WD < 4 Then

ISOSTYR = NY - WD

Else

ISOSTYR = NY - WD + 7

End If

End Function

Explicación del código: – En el código anterior, hemos mencionado el nombre de la función con las variables. Y luego habíamos establecido los criterios para las variables y luego habíamos definido la entrada de la fórmula.

Solo necesita proporcionar el año 2001 en este formato y la fórmula le dará el 1 ^ st ^ lunes del año.

Ahora aprenderemos cómo usar la UDF en un archivo de Excel. Siga los pasos que se indican a continuación: – * Vaya a la hoja de Excel.

  • Ingrese la fórmula en la celda D7.

  • = ISOSTYR (C7) y presione Entrar.

  • La función devolverá la fecha del 1 ^ st ^ lunes de la primera semana del Año Nuevo.

  • Para devolver la fecha del 1 ^ st ^ lunes de la primera semana de Año Nuevo, copie la misma fórmula y péguela en el rango.

image 10

¿Cómo saber el nombre de la hoja de trabajo y el libro de trabajo usando el VBA en Microsoft Excel? Siga el código y los pasos que se indican a continuación: – * Abra la página VBA y presione la tecla Alt + F11.

  • Insertar un módulo.

Escriba el código mencionado a continuación *: –

Function Worksheetname()

Worksheetname = Range("A1").Parent.Name

End Function

Explicación del código: – En el código anterior, hemos mencionado el nombre de la función y luego hemos definido cómo conocer el nombre de la hoja.

Para usar esta fórmula, solo necesita ingresar la fórmula en cualquier celda de esta manera: – = Nombre de la hoja de trabajo (). La función devolverá el nombre de la hoja.

image 11

Para crear la función para el nombre del libro de trabajo, siga los pasos y el código a continuación: – * Abra la página de VBA presione la tecla Alt + F11.

  • Insertar un módulo.

  • Escriba el código mencionado a continuación: –

Function Workbookname()

Workbookname = ThisWorkbook.Name

End Function

Explicación del código: -: – En el código anterior, hemos mencionado el nombre de la función y luego hemos definido cómo conocer el nombre del libro de trabajo.

Para usar esta fórmula, solo necesita ingresar la fórmula en cualquier celda de esta manera: – = Nombre del libro de trabajo (). La función devolverá el nombre de la hoja.

image 12

¿Cómo extraer la primera y la última palabra de una celda usando VBA en Microsoft Excel? Tenemos datos en sheet1 en los que tenemos algunas direcciones, por lo que queremos extraer la última y la primera palabra de una oración o celda o un rango creando la función definida por el usuario a través de VBA en Excel.

image 13

Primero, escribiremos la función para extraer la primera palabra. Siga los pasos que se indican a continuación: – * Abra la página VBA, presione la tecla Alt + F11.

Inserte un módulo Escriba el código mencionado a continuación *: –

Function GETFW(Text As String, Optional Separator As Variant)

Dim FW As String

If IsMissing(Separator) Then

Separator = " "

End If

FW = Left(Text, InStr(1, Text, Separator, vbTextCompare))

GETFW = Replace(FW, Separator, "")

End Function

Explicación del código: – En el código mencionado anteriormente, hemos mencionado el nombre de la función con las variables. Y luego hemos definido los criterios para extraer la palabra de oración o celda.

Ahora aprenderemos a usar esta fórmula. Siga los pasos que se indican a continuación: –

  • Vaya a la hoja de Excel.

  • Use esta fórmula en la celda D9.

  • = GETFW (C9) y presione Entrar.

  • La función devolverá la primera palabra de los datos. Ahora, para recuperar la primera palabra de todas las celdas, copie la misma fórmula en el rango.

image 14

  • Ahora escribiremos el código para extraer la última palabra de la celda.

Siga el código mencionado a continuación: – * Abra la página VBA, presione la tecla Alt + F11.

  • Insertar un módulo.

Escriba el código mencionado a continuación *: –

Function GETLW(Text As String, Optional Separator As Variant)

Dim LW As String

If IsMissing(Separator) Then

Separator = " "

End If

LW = StrReverse(Text)

LW = Left(lastword, InStr(1, LW, Separator, vbTextCompare))

GETLW = StrReverse(Replace(LW, Separator, ""))

End Function

Ahora aprenderemos a usar esta fórmula. Siga los pasos que se indican a continuación: – * Vaya a la hoja de Excel.

  • Use esta fórmula en la celda D9.

  • = GETLW (C9) Presione Enter.

  • La función devolverá la última palabra de los datos. Ahora, para recuperar la última palabra de todas las celdas, copie la misma fórmula en el rango.

image 15

Estas son las funciones que podemos definir a través de VBA y luego podemos usarlo como fórmula de Excel. Además, podemos crear muchas más funciones definidas por el usuario.

Sigue aprendiendo con nosotros, encontraremos fórmulas más complicadas.

Excel