ÚNETE A VBA

Similar a lo que tenemos en la hoja de trabajo como función Concatenar y el comando & que se usa para unir dos o más de dos cadenas, en VBA usamos el comando Unir para hacerlo, en Unir en VBA tomamos la fuente de los datos en una matriz y de forma similar a la concatenación, usamos un delimitador para unirlos.

Función Excel VBA JOIN

Como sugiere el propio nombre, la función VBA JOIN se utiliza para unir una matriz de subcadenas con el delimitador especificado. Si no especificamos ningún delimitador, entonces toma 'espacio' como carácter delimitador predeterminado. Hace el mismo trabajo que la función Concatenar en Excel, excepto que tenemos que especificar el carácter delimitador una sola vez, mientras que, en la función Concatenar, tenemos que especificar el carácter delimitador cada vez entre cada dos cadenas.

La sintaxis de la función es

Como podemos ver, la función toma dos argumentos y devuelve una cadena. Los argumentos son:

  1. SourceArray : Necesitamos especificar o dar referencia a una matriz de subcadenas que se van a unir.
  2. Delimitador : el delimitador se utiliza para separar cada una de las subcadenas al crear la cadena resultante. Como se trata de un argumento opcional, si lo omitimos, el delimitador se establece en un espacio ”“.

La función VBA SPLIT es una función exactamente opuesta a la función VBA JOIN.

Ejemplos de función de combinación de VBA

A continuación se muestran los ejemplos de la función de combinación en Excel VBA.

Puede descargar esta plantilla de Excel de función de unión de VBA aquí - Plantilla de Excel de función de unión de VBA

Unión VBA - Ejemplo n. ° 1

Supongamos que queremos unir el primer nombre (Ramesh), el segundo nombre (Kumar) y el apellido (Mishra).

Los pasos serían:

  • Primero, necesitamos abrir el editor visual básico. Podemos hacer lo mismo haciendo clic en el comando 'Visual Basic' en el grupo 'Código' en la pestaña 'Desarrollador' de Excel o podemos usar la tecla de acceso directo de Excel Alt + F11 .

  • Inserte el módulo haciendo clic derecho en una “hoja 1” y eligiendo el comando 'Insertar' en el menú contextual y luego elija 'Módulo' para insertar.

  • Cree una subrutina llamada 'JoiningName'.

Código:

 Sub JoiningName () End Sub

  • Utilice la función JOIN de la siguiente manera

Código:

 Sub JoiningName () Rango ("D2"). Valor = Join (Array ("Ramesh", "Kumar", "Mishra")) End Sub

Podemos ver que hemos usado la función ARRAY para proporcionar SourceArray a la función JOIN y omitimos especificar el carácter delimitador para que 'espacio' sea el carácter predeterminado. El valor procesado de la función JOIN se escribirá en la celda D2 cuando ejecutemos este código usando la tecla F5 o manualmente.

Unión de VBA - Ejemplo n. ° 2

Supongamos que queremos crear varios archivos de Excel con el nombre del artículo que contiene las ventas solo para ese artículo.

  • Abra el Editor de Visual Basic con la tecla de método abreviado Alt + F11.
  • Haga clic derecho en la hoja 'Hoja1' (Ejemplo 2) 'para abrir el menú contextual y haga clic en' Insertar 'para insertar un' Módulo 'de VBA en el proyecto VBA.

  • Defina una subrutina denominada 'CreateItemSoldFiles'.

Código:

 Sub CreateItemSoldFiles () End Sub

  • Necesitamos establecer una referencia a la biblioteca de objetos 'Microsoft Scripting Runtime' usando el menú Herramientas -> Referencias… comando, ya que usaremos algún código (objetos), que no funcionará si no incluimos esta biblioteca de objetos.

  • Ahora declararemos todas las variables.

Código:

 Atenuar FSO como nuevo scripting.FileSystemObject

La variable FSO anterior da acceso al VBA FileSystemObject. Después del enlace, podemos usar funciones como BuildPath, CopyFile, CreateTextFile, etc.

  • La siguiente declaración crea un objeto TextStream. A través del objeto TextStream, podemos leer o agregar al archivo original.

Código:

 Atenuar FSO como nuevo scripting.FileSystemObject Dim ts como scripting.TextStream

  • Declararemos más variables. 'r' es para mantener filas en el rango, 'fs' es para almacenar la cadena final unida, 'cols' para almacenar números de columnas en el rango, 'FolPath' para almacenar la ruta de la carpeta para que podamos guardar los archivos en la carpeta y 'Items_Sold' para almacenar varios nombres de elementos para crear un archivo con estos nombres.

Código:

 Dim r como rango Dim fs como cadena Dim cols como entero Dim FolPath como cadena Dim Items_Sold As String

  • Para contar el número total de columnas en el rango, definiremos la siguiente declaración.

Código:

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

Esta declaración primero seleccionará la región actual para la celda A1 y luego contará el número total de columnas en la región actual.

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