In questo articolo, impareremo come creare funzioni definite dall’utente in Microsoft Excel utilizzando VBA.

Funzione definita dall’utente: – Microsoft Excel ha già molte funzioni, ma ognuno ha ancora requisiti, situazioni diverse, possiamo creare la nostra funzione secondo il requisito chiamato Funzione definita dall’utente. Possiamo usare la funzione definita dall’utente come altre funzioni in Excel.

Di seguito sono riportati gli argomenti per i quali creeremo la funzione definita dall’utente: 1). Come contare il numero di parole nella cella o nell’intervallo?

2). Come estrarre una parola da una frase o da una cella in Excel?

3). Come creare la formula per ISO?

4). Come conoscere il foglio di lavoro e il nome della cartella di lavoro utilizzando VBA?

5). Come estrarre la prima e l’ultima parola da una cella in Excel?

Come creare la funzione definita dall’utente per contare il numero di parole nella cella o nell’intervallo? Abbiamo i dati nel foglio 1 in cui abbiamo alcuni indirizzi quindi vogliamo contare le parole in una cella o in un intervallo creando la funzione definita dall’utente tramite VBA in Excel.

image 1

Per rendere la funzione definita dall’utente, seguire i passaggi indicati di seguito: – * Aprire la pagina VBA e premere il tasto Alt + F11.

  • Inserisci un modulo.

Scrivi il codice sotto indicato:

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

Spiegazioni del codice: – Per rendere la funzione definita dall’utente, iniziamo il codice per il nome della funzione e definiamo le variabili. Abbiamo utilizzato “For Each loop” nel codice per contare le parole nell’intervallo.

Come utilizzare questa funzione in Excel?

Per utilizzare questa funzione, segui i passaggi indicati di seguito: – * Vai al foglio Excel.

  • Per contare le parole per una cella, inserisci la formula nella cella D7.

  • = WORDSCOUNT (C7), la cella C7 è la cella in cui vogliamo calcolare le parole.

  • La funzione restituirà 6, il che significa che la cella C7 contiene 6 parole.

  • Per eseguire lo stesso calcolo per il resto delle celle, copia la stessa formula e incolla nell’intervallo.

image 2

  • Per contare le parole nell’intervallo, utilizzare la formula come = WORDSCOUNT (C7: C16) e premere Invio.

  • La funzione restituirà il conteggio delle parole.

image 3

Nota: – Questa UDF sarà utile per contare le parole in un intervallo o in una singola cella.

Ora scriveremo il codice per contare la parola utilizzando il delimitatore specificato (,). Segui i passaggi indicati di seguito: –

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

Per utilizzare questa funzione, segui i passaggi indicati di seguito: –

  • Vai al foglio Excel.

  • Per contare i delimitatori specifici nella parola, utilizzeremo questa funzione definita.

  • = SEPARATECOUNTWORDS (C7) e premere Invio.

  • La funzione restituirà il conteggio dei delimitatori specifici.

image 4

Come estrarre una parola da una frase o da una cella in Microsoft Excel utilizzando VBA? Abbiamo i dati in sheet1. In cui abbiamo alcuni indirizzi quindi vogliamo estrarre le parole da una frase o da una cella o da un intervallo creando la funzione definita dall’utente tramite VBA in Excel.

image 5

Per rendere la funzione definita dall’utente, seguire i passaggi indicati di seguito: –

  • Apri la pagina VBA e premi il tasto Alt + F11.

  • Inserisci un modulo.

Scrivi il codice sotto indicato *: –

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

Spiegazione del codice: – Nel codice sopra menzionato, abbiamo menzionato il nome della funzione con le variabili. E poi abbiamo definito i criteri per estrarre la parola dalla frase o dalla cella.

Ora impareremo come usare questa formula. Segui i passaggi indicati di seguito: – * Vai al foglio Excel.

  • Usa questa formula nella cella D7.

  • = GETWORD (C7,2) e premere Invio.

  • La funzione restituirà la seconda parola dalla cella perché nella formula che avevamo menzionato per la 2 ^ nd ^ parola del numero. Se vuoi recuperare la parola che si trova in 3 ^ r ^ posizione, devi cambiare il numero da 2 a 3 nella formula.

image 6

Come creare la formula del numero della settimana ISO in Microsoft Excel utilizzando VBA? Impareremo come creare la formula del numero della settimana ISO in Excel con questo UDF. Questa funzione verrà utilizzata per identificare che la data indicata appartiene a quale numero della settimana dell’anno.

Abbiamo un elenco di date nel foglio e nella seconda colonna vogliamo recuperare i numeri delle settimane.

image 7

Per creare l’UDF per questo requisito, seguire i passaggi indicati di seguito: – * Aprire la pagina VBA e premere il tasto Alt + F11.

  • Inserisci un modulo.

  • Scrivi il codice sotto indicato: –

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

Spiegazione del codice: -: – Nel codice sopra, abbiamo menzionato il nome della funzione con le variabili. Poi abbiamo impostato il valore della data e poi abbiamo definito i criteri della funzione “ISOWEENUMBER”.

Come possiamo utilizzare questa funzione nel nostro file Excel?

  • Vai al foglio Excel.

  • Immettere la formula nella cella D7.

  • = ISOWEEKNUMBER (C7) e premere Invio.

  • La funzione restituirà la settimana per la data inserita nella cella.

Ora per recuperare il numero della settimana per ciascuna data, copia la stessa formula nell’intervallo.

image 8

Ora impareremo come restituire gli standard ISO all’inizio dell’anno in Excel – Primo lunedì dell’anno.

Questa funzione fondamentalmente controllerà che il 1 ° ^ lunedì dell’anno cadrà in quale data e poi inizierà a calcolare il numero di settimane da quella data. Vediamo come possiamo creare l’UDF per questo requisito.

image 9

Segui i passaggi indicati di seguito: – * Apri la pagina VBA e premi il tasto Alt + F11.

  • Inserisci un modulo.

Scrivi il codice sotto indicato *: –

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

Spiegazione del codice: – Nel codice precedente, abbiamo menzionato il nome della funzione con le variabili. E poi abbiamo impostato i criteri per le variabili e poi abbiamo definito l’input della formula.

Devi solo fornire l’anno 2001 in questo formato e la formula ti darà il 1 ^ ^ lunedì dell’anno.

Ora impareremo come utilizzare l’UDF nel file Excel. Segui i passaggi indicati di seguito: – * Vai al foglio Excel.

  • Immettere la formula nella cella D7.

  • = ISOSTYR (C7) e premere Invio.

  • La funzione restituirà la data del 1 ^ ^ lunedì della prima settimana di Capodanno.

  • Per restituire la data del 1 ^ ^ lunedì della prima settimana di Capodanno, copia la stessa formula e incolla nell’intervallo.

image 10

Come conoscere il foglio di lavoro e il nome della cartella di lavoro utilizzando VBA in Microsoft Excel? Segui il codice ei passaggi indicati di seguito: – * Apri la pagina VBA e premi il tasto Alt + F11.

  • Inserisci un modulo.

Scrivi il codice sotto indicato *: –

Function Worksheetname()

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

End Function

Spiegazione del codice: – Nel codice sopra, abbiamo menzionato il nome della funzione e quindi abbiamo definito come conoscere il nome del foglio.

Per utilizzare questa formula, è sufficiente inserire la formula in qualsiasi cella in questo modo: – = Worksheetname (). La funzione restituirà il nome del foglio.

image 11

Per creare la funzione per il nome della cartella di lavoro, seguire i passaggi e il codice indicati di seguito: – * Aprire la pagina VBA premere il tasto Alt + F11.

  • Inserisci un modulo.

  • Scrivi il codice sotto indicato: –

Function Workbookname()

Workbookname = ThisWorkbook.Name

End Function

Spiegazione del codice: -: – Nel codice sopra, abbiamo menzionato il nome della funzione e quindi abbiamo definito come conoscere il nome della cartella di lavoro.

Per utilizzare questa formula, è sufficiente inserire la formula in qualsiasi cella in questo modo: – = Workbookname (). La funzione restituirà il nome del foglio.

image 12

Come estrarre la prima e l’ultima parola da una cella utilizzando VBA in Microsoft Excel? Abbiamo dati in sheet1 in cui abbiamo alcuni indirizzi quindi vogliamo estrarre l’ultima e la prima parola da una frase o cella o un intervallo creando la funzione definita dall’utente tramite VBA in Excel.

image 13

Per prima cosa, scriveremo la funzione per estrarre la prima parola. Si prega di seguire i passaggi indicati di seguito: – * Aprire la pagina VBA premere il tasto Alt + F11.

Inserisci un modulo Scrivi il codice sotto indicato *: –

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

Spiegazione del codice: – Nel codice sopra menzionato, abbiamo menzionato il nome della funzione con le variabili. E poi abbiamo definito i criteri per estrarre la parola dalla frase o dalla cella.

Ora impareremo come usare questa formula. Segui i passaggi indicati di seguito: –

  • Vai al foglio Excel.

  • Usa questa formula nella cella D9.

  • = GETFW (C9) e premere Invio.

  • La funzione restituirà la prima parola dai dati. Ora, per recuperare la prima parola per tutte le celle, copia la stessa formula nell’intervallo.

image 14

  • Ora scriveremo il codice per estrarre l’ultima parola dalla cella.

Segui il codice sotto indicato: – * Apri la pagina VBA, premi il tasto Alt + F11.

  • Inserisci un modulo.

Scrivi il codice sotto indicato *: –

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

Ora impareremo come usare questa formula. Segui i passaggi indicati di seguito: – * Vai al foglio Excel.

  • Usa questa formula nella cella D9.

  • = GETLW (C9) Premere Invio.

  • La funzione restituirà l’ultima parola dai dati. Ora, per recuperare l’ultima parola per tutte le celle, copia la stessa formula nell’intervallo.

image 15

Queste sono le funzioni che possiamo definire tramite VBA e quindi utilizzarle come formula di Excel. Inoltre, possiamo creare molte più funzioni definite dall’utente.

Continua ad imparare con noi, troveremo formule più complicate.

Excel