VBA JOIN

Simile a quello che abbiamo nel foglio di lavoro come la funzione Concatenate e il comando & che viene utilizzato per unire due o più di due stringhe insieme, in VBA usiamo il comando Join per farlo, in Join in VBA prendiamo l'origine dei dati in un array e in modo simile alla concatenazione usiamo un delimitatore per unirli.

Funzione JOIN di Excel VBA

Come suggerisce il nome stesso, la funzione VBA JOIN viene utilizzata per unire un array di sottostringhe con il delimitatore specificato. Se non specifichiamo alcun delimitatore, allora ci vuole "spazio" come carattere delimitatore predefinito. Fa lo stesso lavoro della funzione Concatenate in Excel tranne che dobbiamo specificare il carattere delimitatore una sola volta mentre, nella funzione Concatenate, dobbiamo specificare il carattere delimitatore ogni volta tra due stringhe.

La sintassi della funzione è

Come possiamo vedere, la funzione accetta due argomenti e restituisce una stringa. Gli argomenti sono:

  1. SourceArray : è necessario specificare o fornire un riferimento a un array di sottostringhe che devono essere unite.
  2. Delimitatore : il delimitatore viene utilizzato per separare ciascuna delle sottostringhe durante la creazione della stringa risultante. Poiché questo è un argomento opzionale, se lo omettiamo, il delimitatore è impostato come uno spazio "".

La funzione VBA SPLIT è una funzione esattamente opposta alla funzione VBA JOIN.

Esempi di funzione di unione VBA

Di seguito sono riportati gli esempi della funzione di unione in Excel VBA.

Puoi scaricare questo modello Excel della funzione di unione VBA qui - Modello Excel della funzione di unione VBA

VBA Join - Esempio n. 1

Supponiamo di voler unire il primo (Ramesh), il secondo (Kumar) e il cognome (Mishra).

I passaggi sarebbero:

  • Innanzitutto, dobbiamo aprire l'editor di visual basic. Possiamo fare lo stesso facendo clic sul comando "Visual Basic" nel gruppo "Codice" nella scheda "Sviluppatore" excel oppure possiamo utilizzare il tasto di scelta rapida Excel Alt + F11 .

  • Inserire il modulo facendo clic con il pulsante destro del mouse su un "foglio 1" e scegliendo il comando "Inserisci" dal menu contestuale, quindi scegliere "Modulo" da inserire.

  • Crea una subroutine denominata "JoiningName".

Codice:

 Sub JoiningName () End Sub

  • Utilizzare la funzione JOIN come segue

Codice:

 Sub JoiningName () Range ("D2"). Value = Join (Array ("Ramesh", "Kumar", "Mishra")) End Sub

Possiamo vedere che abbiamo usato la funzione ARRAY per fornire SourceArray alla funzione JOIN e saltato per specificare il carattere delimitatore in modo che 'spazio' sia il carattere predefinito. Il valore elaborato della funzione JOIN verrà scritto nella cella D2 quando eseguiremo questo codice utilizzando il tasto F5 o manualmente.

VBA Join - Esempio # 2

Supponiamo di voler creare vari file excel con il nome dell'articolo contenente le vendite solo per quell'articolo.

  • Aprire Visual Basic Editor utilizzando il tasto di scelta rapida Alt + F11.
  • Fare clic con il pulsante destro del mouse sul foglio "Foglio1" (Esempio 2) "per aprire il menu contestuale e fare clic su" Inserisci "per inserire un" Modulo "VBA nel progetto VBA.

  • Definisci una subroutine denominata "CreateItemSoldFiles".

Codice:

 Sub CreateItemSoldFiles () End Sub

  • Dobbiamo impostare un riferimento alla libreria di oggetti 'Microsoft Scripting Runtime' utilizzando il menu Strumenti -> Riferimenti ... comando, poiché utilizzeremo del codice (oggetti), che non funzionerà se non includiamo questa libreria di oggetti.

  • Ora dichiareremo tutte le variabili.

Codice:

 Dim UST come nuovo Scripting.FileSystemObject

La variabile UST di cui sopra dà accesso a VBA FileSystemObject. Dopo l'associazione, possiamo utilizzare funzioni come BuildPath, CopyFile, CreateTextFile, ecc.

  • L'istruzione successiva crea un oggetto TextStream. Tramite l'oggetto TextStream, possiamo leggere o aggiungere al file originale.

Codice:

 Dim UST As New Scripting.FileSystemObject Dim ts As Scripting.TextStream

  • Dichiareremo più variabili. 'r' è per contenere le righe nell'intervallo, 'fs' è per memorizzare la stringa unita finale, 'cols' per memorizzare il numero di colonne nell'intervallo, 'FolPath' per memorizzare il percorso della cartella in modo che possiamo salvare i file nella cartella e "Items_Sold" per memorizzare vari nomi di elementi per creare un file con questi nomi.

Codice:

 Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String

  • Per contare il numero totale di colonne nell'intervallo, definiremo la seguente dichiarazione.

Codice:

cols = Range ("A1"). CurrentRegion.Columns.Count

Questa istruzione selezionerà prima la regione corrente per la cella A1 e quindi conterà il numero totale di colonne nella regione corrente.

  • We will write the following statements for assigning the variable ‘FolPath’ a path using VBA ENVIRON function and Concatenation Operator.

Code:

FolPath = Environ("UserProfile") & "\Desktop\Items_Sold" If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath

The second statement will create the folder if the folder does not exist in the same location.

  • This code will assign the values of B column one by one to ‘Items_Sold’ We have used ‘OFFSET function’ to get the reference of cell in B column as the currently selected cell is in column A.

Code:

Items_Sold = r.Offset(0, 1).Value

  • The following bordered statement will open the files with names stored in ‘Items_Sold’ variable in one by one in appending mode (the new values will be appended at last).

Code:

 Set ts = FSO.OpenTextFile(FolPath & "\" & Items_Sold & ".xls", ForAppending, True )

We have used Concatenate operator with variables ‘FolPath’ and ‘Items_Sold’ and static values (“\” and”.xls”) to create file names for excel files.

  • We need to keep in mind that VBA JOIN function takes an only one-dimensional array as SourceArray To convert the rows into a one-dimensional array, we need to use Application.Transpose method two times.

Code:

fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab)

We have used the Resize method of range object to resize the range to the width of a number of columns in the range.

As delimiter, we have used ‘vbTab’ keyword so that values would be filled in different cells.

  • As we have stored the processed value of JOIN function into ‘fs’ variable, we will write the fs’s values into new lines of VBA created excel files for every row in our original file from row number 2 to the last row (in our case it is 350th row).

  • Before ending the loop, we will close the file opened. The code would be as shown in the screenshot.

We have written the full code now.

Code:

 Sub CreateItemSoldFiles() Dim FSO As New Scripting.FileSystemObject Dim ts As Scripting.TextStream Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String cols = Range("A1").CurrentRegion.Columns.Count FolPath = Environ("UserProfile") & "\Desktop\Items_Sold" If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath For Each r In Range("A2", Range("A1").End(xlDown)) Items_Sold = r.Offset(0, 1).Value Set ts = FSO.OpenTextFile(FolPath & "\" & Items_Sold & ".xls", ForAppending, True ) fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab) ts.WriteLine fs ts.Close Next r End Sub

Now to execute the code, we will press F5 then, we can see that a folder named ‘Items_Sold’ has been created with the help of VBA code on the desktop.

In the folder, there are 7 unique files created with the names of the item and we can find out details about only that particular item in files.

Laptop.xls

Things to Remember About VBA JOIN Function

  • The SourceArray must be a one-dimensional array. We cannot refer to an individual cell, as this will create multiple multi-dimensional arrays.
  • If we specify a zero-length string (“”) as a delimiter, all items in the array are concatenated with no delimiters.