VBA JOIN

Semelhante ao que temos na planilha como função concatenar e o comando & que é usado para juntar duas ou mais strings, no VBA usamos o comando Join para fazer isso, no Join no VBA pegamos a fonte dos dados em um array e semelhante à concatenação, usamos um delimitador para juntá-los.

Função Excel VBA JOIN

Como o próprio nome sugere, a função VBA JOIN é usada para unir uma matriz de substrings com o delimitador especificado. Se não especificarmos nenhum delimitador, ele usará 'espaço' como caractere delimitador padrão. Ela faz o mesmo trabalho que a função Concatenar no Excel, exceto que temos que especificar o caractere delimitador apenas uma vez, ao passo que, na função Concatenar, temos que especificar o caractere delimitador todas as vezes entre cada duas strings.

A sintaxe da função é

Como podemos ver, a função recebe dois argumentos e retorna uma string. Os argumentos são:

  1. SourceArray : Precisamos especificar ou dar referência a uma matriz de substrings que devem ser unidas.
  2. Delimitador : o delimitador é usado para separar cada uma das substrings ao criar a string resultante. Como este é um argumento opcional, se o omitirmos, o delimitador é definido como um espaço ”“.

A função VBA SPLIT é uma função exatamente oposta à função VBA JOIN.

Exemplos de função de junção VBA

Abaixo estão os exemplos de função de junção no Excel VBA.

Você pode baixar este modelo do Excel da função de junção do VBA aqui - Modelo do Excel da função de junção do VBA

VBA Join - Exemplo # 1

Suponha que queremos juntar o nome (Ramesh), o nome do meio (Kumar) e o sobrenome (Mishra).

As etapas seriam:

  • Primeiro, precisamos abrir o editor visual básico. Podemos fazer o mesmo clicando no comando 'Visual Basic' no grupo 'Código' na guia 'Desenvolvedor' do Excel ou podemos usar a tecla de atalho Alt + F11 do Excel .

  • Insira o módulo clicando com o botão direito em uma “folha 1” e escolhendo o comando 'Inserir' no menu contextual e, a seguir, escolha 'Módulo' para inserir.

  • Crie uma sub-rotina chamada 'JoiningName'.

Código:

 Sub JoiningName () End Sub

  • Use a função JOIN da seguinte forma

Código:

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

Podemos ver que usamos a função ARRAY para fornecer SourceArray para a função JOIN e pulamos para especificar o caractere delimitador para que 'espaço' seja o caractere padrão. O valor processado da função JOIN será escrito na célula D2 quando executarmos este código usando a tecla F5 ou manualmente.

Junção VBA - Exemplo # 2

Suponha que desejamos criar vários arquivos do Excel com o nome do item contendo vendas apenas para aquele item.

  • Abra o Editor do Visual Basic usando a tecla de atalho Alt + F11.
  • Clique com o botão direito na planilha 'Folha1 ′ (Exemplo 2)' para abrir o menu contextual e clique em 'Inserir' para inserir um 'Módulo' VBA no projeto VBA.

  • Defina uma sub-rotina chamada 'CreateItemSoldFiles'.

Código:

 Sub CreateItemSoldFiles () End Sub

  • Precisamos definir uma referência para a biblioteca de objetos 'Microsoft Scripting Runtime' usando o menu Ferramentas -> Comando References…, pois usaremos alguns códigos (objetos), que não funcionarão se não incluirmos esta biblioteca de objetos.

  • Agora vamos declarar todas as variáveis.

Código:

 Dim FSO As New Scripting.FileSystemObject

A variável FSO acima dá acesso ao VBA FileSystemObject. Após a vinculação, podemos usar funções como BuildPath, CopyFile, CreateTextFile, etc.

  • A próxima instrução cria um objeto TextStream. Por meio do objeto TextStream, podemos ler ou anexar ao arquivo original.

Código:

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

  • Declararemos mais variáveis. 'r' é para armazenar linhas no intervalo, 'fs' é para armazenar a string final unida, 'cols' para armazenar números de colunas no intervalo, 'FolPath' para armazenar o caminho da pasta para que possamos salvar os arquivos na pasta e 'Items_Sold' para armazenar vários nomes de itens para criar um arquivo com esses nomes.

Código:

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

  • Para contar o número total de colunas no intervalo, definiremos a seguinte declaração.

Código:

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

Esta instrução selecionará primeiro a região atual para a célula A1 e então contará o número total de colunas na região atual.

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