Vba массивы примеры работы с массивами join: Все про массивы в VBA читать в пошаговом руководстве по Excel

VBA-Урок 13.2. Использование массивов (Arrays) (Продолжение)

ВВЕРХ

❮
❯

Для того, чтобы сохранить более одного столбца данных, нам необходима другая размерность массива. например:

Сохранение данных в двумерный массив:

'Декларирование
Dim array_example (10, 2) '11 x 3 "определенный" массив
'Сохранение данных в массиве
For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
Next

Далее есть несколько примеров работы с этими значениями:

MsgBox array_example (0, 0) '=> возвращает: 03.11.2026
MsgBox array_example (0, 1) '=> возвращает: 24
MsgBox array_example (9, 2) '=> возвращает: NO
MsgBox array_example (10, 2) '=> возвращает: YES

Динамический массив

Давайте представим на минутку, что нам нужно обновлять данные в нашем массиве на регулярной основе, и поэтому мы не можем присвоить фиксированные значения при декларировании . ..

Чтобы узнать номер строки последней непустой ячейки, или другими словами, последнюю строку нашей базы данных, мы используем следующую формулу:

last_row = Range("A1").End(xlDown).Row

Excel не принимает переменные в декларации.

Вместо этого, задекларируем динамический массив (используя пустые скобки), затем определим его размер используя Redim:

Dim array_example ()
ReDim array_example (last_row - 2, 2)

Используя следующую процедуру, вы можете сохранить все строки вашего набора данных (таблицы) в нашем массиве:

Dim array_example()
ReDim array_example(last_row - 2, 2)

Ubound

В предыдущем примере, последний номер в нашем массиве был last_row — 2:

For i = 0 To last_row - 2

Другой способ, чтобы определить последний номер в нашем массиве, мог бы быть через использование Ubound :

For i = 0 To UBound (array_example)

Эта функция возвращает наибольший номер в массиве для выбранного измерения (первое измерение есть по-умолчанию).

Далее есть несколько примеров, которые прояснят это подробнее:

Sub example()
     Dim array_example(10, 2)
     
     MsgBox UBound (array_example) '=> возвращает: 10
     MsgBox UBound (array_example, 1) '=> возвращает: 10
     MsgBox UBound (array_example, 2) '=> возвращает : 2
End Sub

Сохранение данных в диапазоне элементов массива

Есть возможным заполнить массив значениями из диапазона ячеек на рабочем листе даже без использования цикла:

'Декларирование
Dim array_example (10, 2) '11 x 3 "определенный" массив
'Сохранение данных в массиве
For i = 0 To 10
     array_example(i, 0) = Range("A" & i + 2)
     array_example(i, 1) = Range("B" & i + 2)
     array_example(i, 2) = Range("C" & i + 2)
Next

Предыдущий код может быть эффективно заменен этим:

'Декларирование
Dim array_example ()
'Сохранение данных в массиве
array_example = Range("A2:C12").Value

Хотя второй метод кажется более привлекательным, чем первый, будьте осторожны, что в большинстве случаев он может вам стоить больше времени на выполнение, чем первый.

Если вы сохраните данные в вашем массиве таким способом, первый номер будет 1, а не 0, что может привести к недоразумению …

Далее в процессе увеличения кода, если вы решите сохранить только данные, которые соответствуют определенным критериям поиска в массиве (или проводить совсем другую операции), вам придется полностью переписать код, используя другую функцию цикла …

Но этот второй метод является весьма полезным, если вам нужно сохранить все содержимое большого набора данных, потому что это быстрее, чем циклом (экономит примерно 0,2 секунд на каждые 15 000 записей).

Массив (Array)

Но если вам нужно создать массив, который имеет «фиксированное» содержание.

Одним из решений могло бы быть прописать значение строчка за строчкой:

Dim en(5)
en(0) = "IF"
en(1) = "VLOOKUP"
en(2) = "SUM"
en(3) = "COUNT"
en(4) = "ISNUMBER"
en(5) = "MID"

К счастью, вы можете упростить этот код, используя массив ( Array ):

en = Array ("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")

Вот демонстрация использования функции Replace (это поможет вам понять следующий пример):

Sub replace_example()
     Dim var_translate As String
     'Текстовая строчка для этого примера
     var_translate = "Hello World!"
    
     'Замена "World" на "you" в текстовой строке
     var_translate = Replace (var_translate, "World", "you")
     'Строка после замены
     MsgBox var_translate '=> возвращает "Hello you!"
End Sub

Теперь, если мы хотим заменить ряд значений другим набором данных, использование массивов и Array функции будет чрезвычайно полезным:

Sub translate () 'Упрощенный пример перевода формул с английского на французский
     Dim var_translate As String
     'Текстовая строчка для этого примера
     var_translate = "Formula to translate: SUM (IF (ISNUMBER (A1: E1), A1: E1,0))"
     
     'Два набора значений
     en = Array ("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
     fr = Array ("SI", "RECHERCHEV", "SOMME", "NB", "ESTNUM", "STXT")
     
     'Замена" SI "на" IF "," RECHERVEV "на" VLOOKUP "и т. д.
     For i = 0 To UBound (en)
         var_translate = Replace (var_translate, en (i), fr (i))
     Next
     'Строка после замены
     MsgBox var_translate '=> возвращает "Formula to translate : SOMME(SI(ESTNUM(A1:E1),A1:E1,0))"
End Sub

Разделение (Split)

Функция Split позволяет нам превратить символьную строку в массив.

Чтобы превратить строку в массив, сделайте следующее:

variable = "IF/VLOOKUP/SUM/COUNT/ISNUMBER/MID"

Используйте функцию Split и укажите разделитель:

en = Split (variable, "/")

Массив en вернет следующие значения:

MsgBox en(0)  '=> возвращает: IF
MsgBox en (1) '=> возвращает: VLOOKUP
MsgBox en (2) '=> возвращает: SUM
MsgBox en (3) '=> возвращает: COUNT
MsgBox en (4) '=> возвращает: ISNUMBER
MsgBox en (5) '=> возвращает: MID

Следующие 3 массивы также вернут те же значения:

en = Array("IF", "VLOOKUP", "SUM", "COUNT", "ISNUMBER", "MID")
en = Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")
en = Split("IF VLOOKUP SUM COUNT ISNUMBER MID", " ")

Следующий пример возвращает третье значение в строке:

MsgBox Split("IF,VLOOKUP,SUM,COUNT,ISNUMBER,MID", ",")(2) '=> возвращает : SUM

Обратной к Split является функция Join .

Эта функция собирает значение массива в строку.

MsgBox Join (Array (1, 2, 3, 4, 5), "") '=> возвращает: 12345

Статьи по теме:

  • VBA-Урок 13.1. Использование массивов (Arrays)
  • VBA-Урок 13.3. Использование массивов (Arrays) (Упражнения)

    • VBA Excel. Функция Join (синтаксис, параметры, значения)

    Использование функции Join в VBA Excel, ее синтаксис и параметры. Значения, возвращаемые функцией Join. Примеры использования.

    Функция Join предназначена в VBA Excel для объединения подстрок, содержащихся в линейном массиве, в одну строку с добавлением разделителей. Разделителем может быть как отдельный символ или строка из нескольких символов, так и пустая строка. Функция Join по своему действию является обратной функции Split, которая создает массив подстрок из одной строки.

    Синтаксис функции Join

    Join (SourceArray,[Delimiter])

    Обязательным параметром функции Join является SourceArray, если Delimiter явно не указан, используется его значение по-умолчанию.

    Параметры функции Join

    ПараметрОписаниеЗначение
    по умолчанию
    SourceArrayОдномерный массив, содержащий объединяемые подстрокиНет
    DelimiterРазделитель, представляющий пустую строку*, один или более символовПробел

    *Если в качестве разделителя используется пустая строка, то все элементы списка объединяются без разделителей — слитно.

    Возвращаемые значения

    Функция Join возвращает строку, полученную путем объединения исходных подстрок, содержащихся в одномерном массиве, с добавлением между соединяемыми подстроками разделителей, состоящих из одного или более символов.

    Пример 1

    1

    2

    3

    4

    5

    6

    7

    8

    Sub Test1()

    Dim a(2) As String, s As String

    a(0) = «Иван»

    a(1) = «да»

    a(2) = «марья»

    s = Join(a, «-«)

    MsgBox s

    End Sub

    Результат в MsgBox: Иван-да-марья

    В первом примере используется Delimiter = «-» (дефис).

    Пример 2

    1

    2

    3

    4

    5

    6

    7

    8

    Sub Test2()

    Dim a(2) As String, s As String

    a(0) = «Кро»

    a(1) = «ко»

    a(2) = «дил»

    s = Join(a, «»)

    MsgBox s

    End Sub

    Результат в MsgBox: Крокодил

    Во втором примере используется Delimiter = «» (пустая строка).

    Пример 3

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    Sub Test3()

    Dim a(4) As Variant, s As String

    a(0) = «Бегемот»

    a(1) = 255

    a(2) = «Жираф»

    a(3) = 788

    a(4) = «Зайчик»

    s = Join(a)

    MsgBox s

    End Sub

    Результат в MsgBox: Бегемот 255 Жираф 788 Зайчик

    Третий пример показывает, что функция Join работает не только с массивами строкового типа (As String), но и универсального типа (As Variant). Числовые значения автоматически преобразуются в текстовые и объединяются в общую строку. В третьем примере используется Delimiter по-умолчанию.

    Вы можете скопировать коды из приведенных примеров в модуль VBA своей рабочей книги Excel, посмотреть, как они работают. Поэкспериментируйте, подставляя свои данные, чтобы на практике ознакомиться с возможностями функции Join.

    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    Присоединиться к VBA | Как использовать функцию соединения Excel VBA с примерами?

    Обновлено 8 апреля 2023 г.

    Функция VBA JOIN используется для объединения массива подстрок с указанным разделителем. Так же, как само слово означает, что оно должно соединять две или более строк. Теперь вот несколько уловов с ним. Строки находятся в массиве, и он также использует разделитель в качестве функции конкатенации. Но разделитель для функции конкатенации может отличаться для любых двух строк, потому что нам нужно предоставить один разделитель между каждыми двумя строками, используя функцию конкатенации. Но в Join in VBA нам нужно указать разделитель только один раз. Этот разделитель используется для каждой присоединяемой строки. Но этот параметр также является необязательным. Итак, что происходит, когда мы не предоставляем разделитель для функции? Когда мы не предоставляем какой-либо разделитель для функции, она по умолчанию использует «пробел» в качестве разделителя.

    Синтаксис функции соединения в Excel VBA

    Функция соединения имеет следующий синтаксис в Excel VBA:

    Теперь давайте разберемся с аргументами для функции соединения VBA,

    • Исходный массив: 9 0010 Это массив или набор строк, которые мы хотим соединить вместе.
    • Разделитель: Это разделитель или символ, который мы используем, чтобы различать одну строку от другой. Разделителем может быть пробел, запятая, точка или любой символ с нашего компьютера.

    Эта функция возвращает строку.

    Как использовать функцию соединения Excel VBA?

    Мы узнаем, как использовать функцию соединения VBA, на нескольких примерах в Excel.

    Вы можете скачать этот шаблон соединения Excel VBA здесь — Шаблон соединения Excel VBA

    Объединение Excel VBA — пример № 1

    Во-первых, давайте начнем с простого примера. У нас есть путь к файлу, хранящемуся в разных ячейках нашего рабочего листа на листе 1. Нам нужен общий путь к этому месту в другой ячейке. Путь, который необходимо соединить, следующий:

    Нам нужен путь в ячейке E2. Выполните следующие шаги, чтобы использовать функцию соединения в Excel VBA.

    Шаг 1: Перейдите на вкладку разработчика и щелкните Visual Basic, чтобы получить доступ к редактору VB.

    Шаг 2: Щелкните вкладку «Вставка» и вставьте модуль в проект VBA.

    Шаг 3:  Теперь давайте объявим нашу первую подпроцедуру следующим образом.

    Код: 

     Подпример()

Конец суб

    Шаг 4: Поскольку у нас есть данные, мы можем просто использовать значение свойства диапазона, чтобы присоединиться к пути следующим образом.

    Код: 

     Подпример()

Диапазон ("E2"). Значение =

End Sub

    Шаг 5:  Используйте функцию соединения, чтобы соединить все строки вместе с разделителем «\».

    Код: 

     Подпример()

Диапазон("E2").Значение = Соединение(Массив(Диапазон("A2").Значение, Диапазон("B2").Значение, Диапазон("C2").Значение, Диапазон("D2").Значение),
"\")

Конец суб

    Шаг 6: Запустите приведенный выше код, нажав клавишу F5 или нажав кнопку «Выполнить запуск», и мы получим результат в ячейке E2, как показано ниже.

    Мы видим, что все четыре строки объединены общим разделителем «\».

    Соединение с VBA в Excel — пример № 2

    Давайте рассмотрим практический пример. У меня есть имя учащегося, оценки и зачет/незачет на одном листе. Мы хотим создать отдельную папку с файлами, содержащими информацию о том, сдал или не прошел студент, или был отмечен. Для этого мы позаимствуем некоторые концепции FSO (объекты файловой системы), используя функцию соединения. Что ж, данные выглядят так, как показано ниже.

    Выполните следующие шаги, чтобы использовать функцию соединения в Excel VBA.

    Шаг 1: В том же модуле запустим еще одну подпроцедуру следующим образом.

    Код: 

     Подпример2()

End Sub

    Шаг 2: Объявите две переменные как FSO и textstream, которые являются методом FSO следующим образом.

    Код: 

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

    Шаг 3:  Теперь давайте объявим еще несколько переменных: одну как диапазон для хранения строк, другую как целое число для хранения столбцов, а другую как строку для хранения объединенного строкового значения и пути к папке, и еще одну для создания имена файлов.

    Код: 

     Dim rw As Диапазон
Dim res как строка
Dim col As Integer
Dim FolPath как строка
Dim Result As String

    Шаг 4: Поскольку у нас есть данные на листе 2, давайте сначала активируем лист 2.

    Код: 

     Рабочие листы ("Лист2"). Активировать

    Шаг 5:  Теперь давайте подсчитаем количество столбцов, которые у нас есть в данных, следующим образом:

    900 09 Код: 

     цв. = Range("A1").CurrentRegion.Columns.Count

    Шаг 6:  Теперь давайте назначим путь к Folpath с помощью информационной функции ENVIRON следующим образом.

    Код: 

     FolPath = Environ("UserProfile") & "\Desktop\Result"

    Шаг 7: Теперь давайте проверим, существует ли папка или нет, и если нет, то давайте создадим ее, используя метод FSO, как показано ниже.

    Код: 

     Если не FSO.FolderExists(FolPath), то FSO.CreateFolder FolPath

    Шаг 8: Теперь добавим имена, сохраненные с помощью функции смещения, следующим образом .

    Код: 

     Для каждой строки в диапазоне ("A2", Range ("A1").  Конец (xlDown))
Результат = rw.Offset(0, 1).Value

    Это приведет к одновременному открытию файлов с именами в режиме добавления, где новые значения будут добавляться последними. Мы использовали приведенный выше код, чтобы значение результата присваивалось одно за другим.

    Шаг 9:  Теперь, поскольку функция соединения использует одномерный массив, мы будем использовать функцию application.transpose с функцией соединения для преобразования его в одномерный массив следующим образом.

    Код: 

     Установить St = FSO.OpenTextFile(FolPath & "\" & Result & ".xls", ForAppending, True)
res = Join(Application.Transpose(Application.Transpose(rw.Resize(1, col).Value)), vbTab)
St.WriteLine разрешение

    Почему мы использовали application.transpose? Поскольку в целом диапазон массива (1-D) должен быть горизонтальным, что означает, что в одной строке много столбцов. Теперь, поскольку у нас есть вертикальный диапазон здесь, в столбце B, который является результатом, мы использовали эту функцию транспонирования, чтобы преобразовать его в одномерный массив. Мы использовали VbTab в качестве разделителя, чтобы значения находились в следующих ячейках.

    Шаг 10:  Перед завершением цикла for давайте закроем файл, а затем закончим цикл следующим образом.

    Общий код выглядит так, как показано ниже.

    Код: 

     Подпример2()

Dim FSO как новый Scripting.FileSystemObject
Dim St As Scripting.TextStream
Dim rw As Range
Dim res как строка
Dim col As Integer
Dim FolPath как строка
Затемнить результат как строку
Рабочие листы("Лист2").Активировать
col = Диапазон("A1").ТекущаяОбласть.Столбцы.Количество
FolPath = Environ("UserProfile") & "\Desktop\Result"

Если не FSO.FolderExists(FolPath), то FSO.CreateFolder FolPath

Для каждой строки в диапазоне («A2», диапазоне («A1»). End (xlDown))
Результат = rw.Offset(0, 1).Value

Установите St = FSO.OpenTextFile (FolPath & "\" & Result & ".xls", ForAppending, True)
res = Join(Application.Transpose(Application.Transpose(rw. Resize(1, col).Value)), vbTab)
Разрешение St.WriteLine
Св. Закрыть

Следующий ряд

Конец суб

    Шаг 11: Теперь давайте запустим приведенный выше код, нажав клавишу F5, мы увидим на рабочем столе, что папка была создана с именем . Результат , как показано ниже.

    Шаг 12: Откройте папку, в которой у нас будет три файла: Pass, Fail и Grace.

    Если мы откроем любой из файлов, скажем, мы откроем файл Fail, мы сможем увидеть данные для студентов, которые не смогли сдать экзамен.

    Что нужно помнить

    • Используется для объединения массива строк с общим разделителем.
    • Эта функция возвращает строку.
    • Противоположно функции Split в VBA.
    • Если мы не указываем разделитель для этой функции, по умолчанию в качестве разделителя используется пробел.
    • Массив в аргументе должен быть одномерным. Если нет, мы можем использовать обычные методы application. transpose, как описано в примере 2.

    Рекомендуемые статьи

    Это руководство по функции соединения VBA. Здесь мы обсуждаем, как использовать функцию соединения в Excel с использованием кода VBA, а также практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть другие наши рекомендуемые статьи —

    1. Функция даты Excel
    2. Союз VBA
    3. Изошибка VBA
    4. VBA RGB

    excel — объединение двух массивов в vba?

    Объединение двух массивов

    В качестве альтернативы правильному и рабочему подходу, предложенному Скоттом Кранером

    Создайте третий массив, пустой размер обоих массивов вместе взятых,
    затем прокручивайте каждый массив, добавляя элементы один за другим.

    … Демонстрирую способ

    • вставить только элемент(ы) 2-го массива по циклу
      в основной массив, тогда как
    • основной массив получает только , реструктурированный с помощью одного вкладыша через Application. Index() .

    Поскольку эта функция изменит результаты на массив с отсчетом от 1, я верну размер массива обратно к массиву с отсчетом от нуля. Кроме того, я добавил дополнительный дисплей в окно Immediate VBE, в результате чего получилось 2|4|5|3|7|6 значений:

    1-й шаг: простая демонстрация с теми же значениями массива, что и в OP (вставка 1 элемента) 

     Sub SimpleDemo ()
'[0]объявлять и назначать одномерные массивы с отсчетом от нуля
    Dim main, newTop
    основной = Массив (4, 5, 3, 7, 6)
    newTop = Array(2) 'только один элемент на первом шаге
'[1] преобразовать основной массив, вставив (/т.е. повторив) "другой" 1-й элемент
    main = Application.Index(main, Array(1, 1, 2, 3, 4, 5)) ' заменяется на 1-мерный массив на основе 1
    ReDim Preserve main(0 To UBound(main) - 1) ' вернуться к 1-мерному массиву с отсчетом от нуля
'[2]перезаписать новый первый элемент 1-м (единственным) элементом newTop
    основной (0) = новый Верх (0)
'[3] (необязательно) отображение в окне Immediate Window VBE: main(от 0 до 5) ~> 2|4|5|3|7|6
    Debug. Print "main(" & LBound(main) & " To " & UBound(main) & ") ~> " & _
                Присоединиться(основной, "|")
Конец сабвуфера

    2-й шаг: более общий подход с использованием процедуры AddElem

    В приведенной выше демонстрации вставляется только один элемент . Поэтому я закодировал процедуру AddElem и функцию справки addElems() , чтобы позволить вставку дополнительных элементов . Предполагается, что все 1-мерные массивы отсчитываются от нуля, как в исходном сообщении; кстати, можно легко адаптировать 🙂

     Sub AddElem(main, newTop)
«Пурп. : добавить/вставить другой элемент(ы) массива поверх основного массива с отсчетом от нуля
' Автор: https://stackoverflow.com/users/6460297/т-м
' Дата : 2020-02-05
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~
' а) вставить элемент(ы) newTop поверх основного массива
    main = Application.Index(main, addElems(main, newTop)) ' временно меняется на mainay!
' б) снова сделать основной массив нулевым (необязательно)
    ReDim Preserve main(0 To UBound(main) - 1)
' c) перезаписать вставленный начальный элемент(ы) элементом(ами) newTop
    Dim i&: For i = 0 To UBound(newTop): main(i) = newTop(i): Next i
Конец сабвуфера

    Функция справки addElems() 

     Функция addElems(main, newTop) As Variant()
'Примечание: функция справки, вызываемая AddElem()
'Purp. : вернуть счетчики порядковых элементов комбинированных массивов.
    Dim i&, n&: n = UBound(main) + UBound(newTop) + 1
    ReDim tmp (от 0 до n)
    Для i = 0 To UBound(newTop): tmp(i) = i: Next i
    Для i = i To n: tmp(i) = i - UBound(newTop): Next i
    addElems = tmp ' возвращает комбинированные счетчики элементов, например Массив (1,2, 1,2,3,4,5)
Конечная функция

    Пример вызова

    Я немного изменил значения второго массива OP ( Массив (2) ~> Массив (20,21) , чтобы продемонстрировать вставку большего количества элементов, таким образом
    в результате получается объединенный массив (20,21,2,4,5,3,7,6) .

     ПодпримерCall()
     '[0]объявлять и присваивать одномерные массивы с отсчетом от нуля
     Dim main, newTop
     основной = Массив (4, 5, 3, 7, 6)
     новая вершина = массив (20, 21)
     '[1]Добавить/вставить newTop поверх основного массива
     AddElem main:=main, newTop:=newTop ' или просто: AddElem main, newTop
     '[2] (необязательно) отображение в окне Immediate VBE: ~~> main(0 To 6) .