VBA Alăturați-vă - Exemple pas cu pas ale funcției de asociere Excel VBA

Similar cu ceea ce avem în foaia de lucru ca funcție Concatenare și comanda & care este utilizată pentru a uni două sau mai mult de două șiruri împreună, în VBA folosim comanda Join pentru a face acest lucru, în Join in VBA luăm sursa datelor într-o matrice și similar cu concatenarea, folosim un delimitator pentru a le uni.

Funcția Excel VBA JOIN

După cum sugerează și numele, funcția VBA JOIN este utilizată pentru a uni o serie de șiruri de caractere cu delimitatorul specificat. Dacă nu specificăm niciun delimitator, atunci va lua „spațiu” ca caracter implicit delimitator. Acționează la fel ca funcția Concatenare în Excel, cu excepția faptului că trebuie să specificăm caracterul delimitator o singură dată, în timp ce, în funcția Concatenare, trebuie să specificăm caracterul delimitator de fiecare dată între fiecare două șiruri.

Sintaxa funcției este

După cum putem vedea, funcția ia două argumente și returnează un șir. Argumentele sunt:

  1. SourceArray : Trebuie să specificăm sau să facem referire la o serie de șiruri de caractere care urmează să fie unite.
  2. Delimitator : Delimitatorul este utilizat pentru a separa fiecare dintre șiruri la crearea șirului rezultat. Deoarece acesta este un argument opțional, dacă îl omitem, delimitatorul este setat să fie un spațiu ”“.

Funcția VBA SPLIT este o funcție exact opusă funcției VBA JOIN.

Exemple de funcție de asociere VBA

Mai jos sunt exemple de funcții de asociere în Excel VBA.

VBA Join - Exemplul nr. 1

Să presupunem că vrem să ne alăturăm primului (Ramesh), mijlocului (Kumar) și prenumelui (Mishra).

Pașii ar fi:

  • În primul rând, trebuie să deschidem editorul de bază vizual. Putem face același lucru făcând clic pe comanda „Visual Basic” din grupul „Cod” din fila „Dezvoltator” excel sau putem folosi tasta de comandă rapidă Excel Alt + F11 .
  • Introduceți modulul făcând clic dreapta pe o „foaie 1” și alegând comanda „Inserare” din meniul contextual și apoi alegeți „Modul” pentru inserare.
  • Creați un subrutin numit „JoiningName”.

Cod:

Sub JoiningName () End Sub
  • Utilizați funcția JOIN după cum urmează

Cod:

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

Putem vedea că am folosit funcția ARRAY pentru a furniza SourceArray funcției JOIN și am sărit pentru a specifica caracterul delimitator, astfel încât „spațiu” să fie caracterul implicit. Valoarea procesată a funcției JOIN va fi scrisă în celula D2 atunci când executăm acest cod folosind tasta F5 sau manual.

VBA Join - Exemplul nr. 2

Să presupunem că dorim să creăm diferite fișiere Excel cu numele articolului care conține vânzări numai pentru acel articol.

  • Deschideți Editorul Visual Basic utilizând tasta de comandă rapidă Alt + F11.
  • Faceți clic dreapta pe foaia „Sheet1 ′ (Exemplul 2)” pentru a deschide meniul contextual și faceți clic pe „Insert” pentru a insera un „modul” VBA în proiectul VBA.
  • Definiți un subrutin numit „CreateItemSoldFiles”.

Cod:

Sub CreateItemSoldFiles () End Sub
  • Trebuie să setăm o referință la biblioteca de obiecte „Microsoft Scripting Runtime” utilizând meniul Instrumente -> Referințe…, deoarece vom folosi un anumit cod (obiecte), care nu vor funcționa dacă nu includem această bibliotecă de obiecte.
  • Acum vom declara toate variabilele.

Cod:

Dim FSO ca nou Scripting.FileSystemObject

Variabila FSO de mai sus oferă acces la VBA FileSystemObject. După legare, putem folosi funcții precum BuildPath, CopyFile, CreateTextFile etc.

  • Următoarea declarație creează un obiect TextStream. Prin intermediul obiectului TextStream, putem citi sau adăuga fișierul original.

Cod:

Dim FSO As New Scripting.FileSystemObject Dim ts As Scripting.TextStream
  • We will declare more variables. ‘r’ is for holding rows in the range, ‘fs’ is for storing final joined string, ‘cols’ for storing numbers of columns in the range, ‘FolPath’ for storing the path of the folder so that we can save the files in the folder and ‘Items_Sold’ for storing various item names to create a file with these names.

Code:

Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String
  • To count the total number of columns in range, we will define the following statement.

Code:

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

This statement will first select the current region for cell A1 and then will count the total number of columns in the current region.

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

Code:

FolPath = Environ("UserProfile") & "DesktopItems_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") & "DesktopItems_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

Acum, pentru a executa codul, vom apăsa F5 apoi, putem vedea că un folder numit „Items_Sold” a fost creat cu ajutorul codului VBA de pe desktop.

În dosar, există 7 fișiere unice create cu numele elementului și putem afla detalii despre acel element în fișiere.

Laptop.xls

Lucruri de reținut despre funcția VBA JOIN

  • SourceArray trebuie să fie una-dimensional matrice. Nu ne putem referi la o celulă individuală, deoarece aceasta va crea mai multe tablouri multi-dimensionale.
  • Dacă specificăm un șir de lungime zero („”) ca delimitator, toate elementele din matrice sunt concatenate fără delimitatori.

Articole interesante...