VBA Bli med - Trinnvise eksempler på Excel VBA Join-funksjon

Innholdsfortegnelse

I likhet med det vi har i regnearket som Concatenate-funksjon og & kommandoen som brukes til å koble to eller flere enn to strenger sammen, i VBA bruker vi Join-kommandoen for å gjøre det, i Join in VBA tar vi kilden til dataene i en matrise og ligner på sammenkoblingen bruker vi en avgrenser for å bli med dem.

Excel VBA JOIN-funksjon

Som navnet antyder, brukes VBA JOIN-funksjonen til å koble sammen en rekke understrenger med den angitte avgrenseren. Hvis vi ikke spesifiserer noen skilletegn, tar det "mellomrom" som standard skilletegn. Det gjør det samme som Concatenate-funksjonen gjør i Excel, bortsett fra at vi bare må spesifisere skilletegn en gang, mens vi i Concatenate-funksjonen må spesifisere skilletegn hver gang mellom hver annen streng.

Syntaksen til funksjonen er

Som vi kan se, tar funksjonen to argumenter og returnerer en streng. Argumentene er:

  1. SourceArray : Vi må spesifisere eller henvise til en rekke understrenger som skal sammenføyes.
  2. Avgrenser : Avgrenseren brukes til å skille hver av understrengene når du oppretter den resulterende strengen. Ettersom dette er et valgfritt argument, hvis vi utelater det, er skilletegnet satt til et mellomrom ”“.

VBA SPLIT-funksjonen er en nøyaktig motsatt funksjon av VBA JOIN-funksjonen.

Eksempler på VBA Join-funksjon

Nedenfor er eksemplene på sammenføyningsfunksjon i Excel VBA.

VBA Join - Eksempel nr. 1

Anta at vi vil bli med i fornavn (Ramesh), mellom (Kumar) og etternavn (Mishra).

Fremgangsmåten vil være:

  • Først må vi åpne Visual Basic Editor. Vi kan gjøre det samme ved å klikke på "Visual Basic" -kommandoen i "Kode" -gruppen under "Utvikler" -fanen excel, eller vi kan bruke hurtigtasten excel Alt + F11 .
  • Sett inn modulen ved å høyreklikke på et "ark 1" og velge "Sett inn" -kommandoen fra hurtigmenyen, og velg deretter "Modul" for å sette inn.
  • Lag en underrutine som heter 'JoiningName'.

Kode:

Sub JoiningName () End Sub
  • Bruk JOIN-funksjonen som følger

Kode:

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

Vi kan se at vi har brukt ARRAY-funksjonen for å gi SourceArray til JOIN-funksjonen og hoppet over for å spesifisere skilletegnet slik at 'space' vil være standardtegn. Den behandlede verdien av JOIN-funksjonen vil bli skrevet i celle D2 når vi utfører denne koden ved hjelp av F5-tasten eller manuelt.

VBA Join - Eksempel nr. 2

Anta at vi ønsker å opprette forskjellige Excel-filer med varenavnet som bare inneholder salg for den varen.

  • Åpne Visual Basic Editor ved hjelp av hurtigtasten Alt + F11.
  • Høyreklikk på 'Sheet1' (Eksempel 2) ark for å åpne kontekstmenyen og klikk på 'Sett inn' for å sette inn en VBA 'modul' i VBA-prosjektet.
  • Definer en underrutine med navnet 'CreateItemSoldFiles'.

Kode:

Sub CreateItemSoldFiles () Slutt Sub
  • Vi må angi en referanse til objektbiblioteket 'Microsoft Scripting Runtime' ved hjelp av Verktøy-menyen -> Referanser … -kommandoen, da vi vil bruke noen kode (objekter), som ikke fungerer hvis vi ikke inkluderer dette objektbiblioteket.
  • Nå skal vi erklære alle variablene.

Kode:

Dim FSO as New Scripting.FileSystemObject

Ovennevnte FSO-variabel gir tilgang til VBA FileSystemObject. Etter binding kan vi bruke funksjoner som BuildPath, CopyFile, CreateTextFile, etc.

  • Den neste setningen oppretter et TextStream-objekt. Gjennom TextStream-objektet kan vi lese fra eller legge til originalfilen.

Kode:

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

Nå for å utføre koden, vil vi trykke på F5 da, vi kan se at en mappe med navnet 'Items_Sold' er opprettet ved hjelp av VBA-kode på skrivebordet.

I mappen er det 7 unike filer opprettet med navnene på varen, og vi kan finne ut detaljer om bare det aktuelle elementet i filene.

Laptop.xls

Ting å huske på VBA JOIN-funksjon

  • Den SourceArray må være en en-dimensjonal matrise. Vi kan ikke referere til en enkelt celle, da dette vil skape flere flerdimensjonale matriser.
  • Hvis vi spesifiserer en streng med null lengde (“”) som skilletegn, blir alle elementene i matrisen sammenkoblet uten avgrensere.

Interessante artikler...