Come creare una funzione definita dall'utente in Microsoft Excel

Anonim

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 comunque ognuno ha requisiti diversi, situazione, 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 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 in Cell o Range?

Abbiamo 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.

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:

 Funzione WORDSCOUNT(rRange As Range) As Long Dim rCell As Range Dim Count As Long Per ogni 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 avviamo il codice per nome funzione e definiamo le variabili. Abbiamo usato "For Each loop" nel codice per contare le parole nell'intervallo.
Come utilizzare questa funzione in Excel?

Per utilizzare questa funzione, seguire i passaggi indicati di seguito: -

  • Vai al foglio Excel.
  • Per contare le parole di 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.

  • Per contare le parole nell'intervallo, usa la formula come =WORDSCOUNT(C7:C16) e premi Invio.
  • La funzione restituirà il conteggio delle parole.


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: -

 Funzione 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, seguire 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.


Come estrarre una parola da una frase o cella in Microsoft Excel usando VBA?

Abbiamo i dati nel foglio1. In cui abbiamo alcuni indirizzi, quindi vogliamo estrarre le parole da una frase o cella o un intervallo creando la funzione definita dall'utente tramite VBA in Excel.


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:-
 Funzione GETWORD(Testo come variante, N come numero intero, Delimitatore opzionale come variante) As String If IsMissing(Delimiter) Then Delimiter = " " End If GETWORD = Split(Text, Delimiter)(N - 1) End Function

Codice Spiegazione:- Nel codice sopra menzionato, abbiamo menzionato il nome della funzione con le variabili. E poi avevamo 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 avevamo menzionato per 2ns la parola del numero Se vuoi recuperare la parola che si trova a 3rd posizione, è necessario modificare il numero da 2 a 3 nella formula.


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 questa UDF. Questa funzione useremo per identificare che la data menzionata appartiene a quale numero di settimana dell'anno.

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

Per creare l'UDF per questo requisito, seguire i passaggi indicati di seguito:-

  • Apri la pagina VBA e premi il tasto Alt+F11.
  • Inserisci un modulo.
  • Scrivi il codice indicato di seguito: -
 Funzione 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) Fine funzione

Codice Spiegazione:- :- Nel codice sopra, abbiamo menzionato il nome della funzione con le variabili. E poi abbiamo impostato il valore della data e quindi abbiamo definito i criteri della funzione "ISOWEENUMBER".
Come possiamo usare questa funzione nel nostro file Excel?

  • Vai al foglio Excel.
  • Inserisci 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 ogni data, copia la stessa formula nell'intervallo.


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

Questa funzione controllerà sostanzialmente che 1ns Il lunedì dell'anno cadrà in quale data e quindi inizierà a calcolare il numero di settimane da quella data. Vediamo come possiamo creare l'UDF per questo requisito.


Segui i passaggi indicati di seguito:-

  • Apri la pagina VBA e premi il tasto Alt+F11.
  • Inserisci un modulo.
  • Scrivi il codice sotto indicato:-
 Funzione ISOSTYR(Year As Integer) As Date Dim WD As Integer Dim NY As Date NY = DateSerial(Year, 1, 1) WD = (NY - 2) Mod 7 Se WD < 4 Allora ISOSTYR = NY - WD Altrimenti ISOSTYR = NY - WD + 7 End If End Function

Codice Spiegazione: - Nel codice sopra, 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à 1ns lunedì dell'anno.

Ora impareremo come utilizzare l'UDF nel file Excel. Segui i passaggi indicati di seguito: -

  • Vai al foglio Excel.
  • Inserisci la formula nella cella D7.
  • =ISOSTIR(C7) e premere Invio.
  • La funzione restituirà la data dell'1ns Lunedì della prima settimana di Capodanno.
  • Per restituire la data del 1ns Lunedì della prima settimana del nuovo anno, copia la stessa formula e incolla nell'intervallo.


Come conoscere il nome del foglio di lavoro e della cartella di lavoro utilizzando VBA in Microsoft Excel?

Segui il codice e i 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

Codice Spiegazione:- Nel codice sopra, abbiamo menzionato il nome della funzione e poi abbiamo definito come conoscere il nome del foglio.

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


Per creare la funzione per il nome della cartella di lavoro, seguire i passaggi e il codice indicati di seguito: -

  • Apri la pagina VBA premi il tasto Alt + F11.
  • Inserisci un modulo.
  • Scrivi il codice indicato di seguito: -
 Function Workbookname() Workbookname = ThisWorkbook.Name End Function

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

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


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.

Per prima cosa, scriveremo la funzione per estrarre la prima parola. Si prega di seguire i passaggi indicati di seguito: -

  • Apri la pagina VBA premi il tasto Alt + F11.
  • Inserisci un modulo

Scrivi il codice sotto indicato:-

 Funzione 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 , Separatore, "") Fine Funzione

Codice Spiegazione: - Nel codice sopra citato, 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.

Ora scriveremo il codice per estrarre l'ultima parola dalla cella. Segui il codice indicato di seguito: -

  • Apri la pagina VBA e premi il tasto Alt+F11.
  • Inserisci un modulo.
  • Scrivi il codice sotto indicato:-
 Funzione 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) Premi 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.

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 a imparare con noi, troveremo formule più complicate.