Vba массив в строку: Функции VBA для работы с массивами

Преобразование строки со значениями в массив

Главная » Макросы для Excel » Преобразование строки со значениями в массив

Ситуация: дана строка, в которой через запятую перечислены значения (или диапазоны значений)

Требуется подсчитать, сколько значений содержится в строке, или же разбить строку на массив, содержащий все значения из исходной строки.

И, если при исходных строках вида «5,6,8,18,2,21» всё просто (достаточно применить VB-функцию Split), то при наличии в строке диапазонов значений вида Число1-Число2 (например, строка «9-15,18,2,11-9«) задача заметно усложняется.

В этих случаях на помощь придёт функция ArrayOfValues

Function ArrayOfValues(ByVal txt$) As Variant
 
' Принимает в качестве параметра строку типа ",,5,6,8,,9-15,18,2,11-9,,1,4,,21,"
    ' Возвращает одномерный (горизонтальный) массив в формате
    ' array(5,6,8,9,10,11,12,13,14,15,18,2,11,10,9,1,4,21)
    ' (пустые значения удаляются; диапазоны типа 9-15 и 17-13 раскрываются)
arr = Split(Replace(txt$, " ", ""), ","): Dim n As Long: ReDim tmpArr(0 To 0)
    For i = LBound(arr) To UBound(arr)
        Select Case True
            Case arr(i) = "", Val(arr(i)) < 0
                '  раскомментируйте эту строку, чтобы пустые и нулевые значения
                '  тоже добавлялись в результат (преобразовывались в значение -1)
                'tmpArr(UBound(tmpArr)) = -1: ReDim Preserve tmpArr(0 To UBound(tmpArr) + 1)
            Case IsNumeric(arr(i))
                tmpArr(UBound(tmpArr)) = arr(i): ReDim Preserve tmpArr(0 To UBound(tmpArr) + 1)
            Case arr(i) Like "*#-#*"
                spl = Split(arr(i), "-")
                If UBound(spl) = 1 Then
                    If IsNumeric(spl(0)) And IsNumeric(spl(1)) Then
                        For j = Val(spl(0)) To Val(spl(1)) Step IIf(Val(spl(0)) > Val(spl(1)), -1, 1)
                            tmpArr(UBound(tmpArr)) = j: ReDim Preserve tmpArr(0 To UBound(tmpArr) + 1)
                        Next j
                    End If
                End If
        End Select
    Next i
    On Error Resume Next: ReDim Preserve tmpArr(0 To UBound(tmpArr) - 1)
    ArrayOfValues = tmpArr
End Function

 

Использовать её можно так:

Sub ПримерИспользования()
    ' разбиваем строку в массив, содержащий все значения исходной строки
    a = ArrayOfValues(",,5,6,8,,9-15,18,2,11-9,,1,4,,21,")
 
    Debug. Print Join(a, ",") ' объединяем обратно созданный массив
    ' результатом будет строка "5,6,8,9,10,11,12,13,14,15,18,2,11,10,9,1,4,21"
End Sub

Функция нашла применение в программе выгрузки тарифов в XML

 

 

Ещё один вариант функции, только она возвращает из аналогичной текстовой строки КОЛЛЕКЦИЮ НЕПОВТОРЯЮЩИХСЯ чисел от 1 до 255

Function ArrayOfValuesEx(ByVal txt$) As Collection
    ' Принимает в качестве параметра строку типа ",,5,6,8,,9-15,18,2,11-9,,1,4,,21,"
    ' Возвращает колекцию уникальных чисел в формате    (5,6,8,9,10,11,12,13,14,15,18,2,1,4,21)
    ' (удаляются все значения кроме целых чисел от 1 до 255; диапазоны типа 9-15 и 17-13 раскрываются)
    On Error Resume Next: Set ArrayOfValuesEx = New Collection
    MaxNumber& = 255
    txt = Replace(Replace(txt, ".", ","), " ", "")
    For i = 1 To Len(txt)
        If Mid(txt, i, 1) Like "[0-9,-]" Then res = res & Mid(txt, i, 1) Else res = res & " "
    Next
    txt = Replace(res, " ", "")
 
    arr = Split(txt, ","):
    For i = LBound(arr) To UBound(arr)
        Select Case True
            Case arr(i) = "", Val(arr(i)) < 0
            Case IsNumeric(arr(i))
                v& = Val(arr(i)): If v > 0 And v <= MaxNumber& Then ArrayOfValuesEx. Add v, CStr(v)
            Case arr(i) Like "*#-#*"
                spl = Split(arr(i), "-")
                If UBound(spl) = 1 Then
                    If IsNumeric(spl(0)) And IsNumeric(spl(1)) Then
                        For j = Val(spl(0)) To Val(spl(1)) Step IIf(Val(spl(0)) > Val(spl(1)), -1, 1)
                            v& = j: If v > 0 And v <= MaxNumber& Then ArrayOfValuesEx.Add v, CStr(v)
                        Next j
                    End If
                End If
        End Select
    Next i
End Function
 
Private Sub ArrayOfValuesEx_ПримерИспользования()
    ' разбиваем строку в массив, содержащий все значения исходной строки
    Dim coll As Collection
    Set coll = ArrayOfValuesEx(",,5,6,+8,,9-12,18//,2,11-9,,1,4.6,,21,7a,ajsgh55,4-")
 
    For Each Item In coll: Debug.Print Item;: Next: Debug.Print
    ' результатом будет   5  6  8  9  10  11  12  18  2  1  4  21  7  55
End Sub

Не получается применить макрос? Не удаётся изменить код под свои нужды?

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

массивы — Заполнение строки данными из несмежных диапазонов

Ошибка не одна, их много. А без понимания конечной задачи код смотрится сборной солянкой.

  1. Не нужно задавать размерность массива, если он будет заполняться диапазоном с листа или массивом констант. При объявлении массива размерность можно задать, если он впоследствии будет заполняться поэлементно.

OrgData(i) = .Range(NumStrok) Хотите передать в массив значение ячейки? Не тут-то было…

  1. Во-первых, сразу же ошибка — не указан элемент массива-передатчика, надо NumStrok(i).

  2. При декларации массива OrgData указано, что его элементы типа Variant (т.е. могут быть любого типа). В цикле массив заполняется Range(NumStrok(i))… Но чем? В OrgData может быть передано значение, которое находится по ссылке, но может быть записан и адрес ссылки!

Вывод. Всегда явно указывать, что хотим получить — Range(NumStrok(i)).Value

  1. Знак + (плюс). Это может быть и объединение текстовых строк, и суммирование числовых данных. И его действие зависит от первого элемента перед знаком.

CInt(OrgData(1)) + 4 — здесь в элементе массива может быть текст "B2" (см. выше ошибку 3). Попытка задать тексту тип «целое» — ошибка. Если без CInt, получим конкатенацию (объединение строк) — "B2" + 4 = "B24"

Range(NumKol + KakaStroka) — здесь NumKol — число, предполагается суммирование, но KakaStroka — текcт. Результат попытки суммирования с текстом — ошибка. Если в KakaStroka число в текстовом формате (ведь переменная типа String), получим суммирование двух чисел — номера столбца и номера строки. Догадайтесь, что будет 🙂

Вывод. Для суммирования применять + , для объединения текстов &. Если в переменной предполагается хранение числового значения, то почему эта переменная декларируется строковой?

  1. Примечание. Почему в примере выше B2, а не B1? Если в модуле не декларирован оператор принудительного указания первого элемента массивов, то при заполнении массивом констант по умолчанию первый элемент массива — 0 (ноль). Отсюда еще дна неточность — цикл нужно начинать с нуля, а не с единицы.

  2. Sheets("Ввод").Select. Для обращения к диапазонам не обязательно активировать лист.

  3. Массив NumKol лишний. Ссылку можно формировать и так: Cells(KakaStroka, i) = OrgData(i)

  4. Массив OrgData и первый цикл тоже лишние, если значение .Range(NumStrok(i)) передавать во втором цикле напрямую в ячейки.

Но можно поступить по-другому. Обращение к объектам листа — процесс медленный. Лучше значения массива передать в диапазон, без цикла.

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

Sub Макрос1()
    Dim NumStrok(), OrgData(20)
    Dim KakaStroka As Long ' перевод: совсем плохая строка :) '
    Dim j As Long
    NumStrok = Array("B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "h21", "I11", "J11", "K11", "h23", "I13", "J13", "K13", "B14", "D14", "B15", "D15", "h26")
    With Sheets("Ввод")
        For j = 0 To 20
            OrgData(j) = .Range(NumStrok(j)).Value
        Next j
        KakaStroka = OrgData(0) + 4
        Sheets("Ввод").Cells(KakaStroka, 1).Resize(1, 21).Value = OrgData
    End With
End Sub

Дополнение. Неважно, в каком месте кода декларируются переменные, память откусывается сразу, в начале работы макроса.

Excel VBA: диапазон в массив строк за 1 шаг

Задавать вопрос

спросил

Изменено
3 года, 9 месяцев назад

Просмотрено
91к раз

Я знаю, что вы можете легко взять диапазон ячеек и поместить их в Variant Array, но я хочу работать со строковым массивом (поскольку он одномерный и занимает меньше памяти, чем Variant array).

Есть ли способ автоматически преобразовать диапазон в массив строк?

Прямо сейчас я использую функцию, которая берет диапазон и сохраняет значения в массиве вариантов, а затем преобразует массив вариантов в массив строк. Это работает хорошо, но я ищу способ перейти непосредственно из диапазона в массив строк. Любая помощь будет принята с благодарностью.

 Функция RangeToArray(ByVal my_range As Range) As String()
Dim vArray как вариант
Dim sArray() как строка
Дим и пока
vArray = мой_диапазон.Значение
ReDim sArray (1 в UBound (vArray))
Для i = 1 To UBound (vArray)
    sArray(i) = vArray(i, 1)
Следующий
RangeToArray = sArray()
Конечная функция

ОБНОВЛЕНИЕ:
Похоже, что нет способа пропустить шаг добавления данных в массив переменных, прежде чем преобразовать его в одномерный строковый массив. Обидно, если это правда (даже если это не требует особых усилий, мне нравится ультраоптимизировать, поэтому я надеялся, что есть способ пропустить этот шаг). Я закрою вопрос через несколько дней, если решение не появится. Спасибо за полезные комментарии, ребята!

ОБНОВЛЕНИЕ2:
Ответ принадлежит Саймону, который приложил огромные усилия (как и все остальные) и в конечном итоге указал, что действительно невозможно перейти от диапазона к массиву строк за один раз. Спасибо всем.

  • массивы
  • строка
  • excel
  • vba
  • диапазон

3

На самом деле вы можете напрямую перейти от диапазона к массиву, используя функции Split, Join и разделитель, которого нет в тексте.

Предполагая, что вы уже назначили диапазон значений 1D как SrcRange

 Dim Array() As String: Array = Split(Join(Application.Transpose(SrcRange), "#"), "#")

3

Как насчет…

 Открытая функция RangeToStringArray(theRange As Excel.Range) As String()
    ' Получить значения в массив вариантов
    Dim variantValues ​​As Variant
    variantValues ​​= theRange. Value
    ' Создаем для них строковый массив
    Dim stringValues ​​() как строка
    ReDim stringValues ​​(1 в UBound (вариантные значения, 1), 1 в UBound (вариантные значения, 2))
    «Положи их туда!
    Dim columnCounter As Long, rowCounter As Long
    Для rowCounter = UBound(variantValues, 1) To 1 Шаг -1
       Для columnCounter = UBound(variantValues, 2) до 1 Шаг -1
           stringValues ​​(rowCounter, columnCounter) = CStr (variantValues ​​(rowCounter, columnCounter))
       Следующий столбецСчетчик
    Следующая строкаСчетчик
    ' Возвращаем массив строк
    RangeToStringArray = строковые значения
Конечная функция

4

 Функция RangeToStringArray (myRange как диапазон) как String()
    ReDim strArray (myRange.Cells.Count - 1) как строка
    Dim idx As Long
    Dim c As Диапазон
    Для каждого c в моем диапазоне
        strArray(idx) = c.Text
        ИДКС = ИДКС + 1
    Следующий с
    RangeToStringArray = массив строк
Конечная функция

Если вы не против изменить содержимое буфера обмена, то:

  1. КОПИРОВАТЬ диапазон в буфер обмена методом Копировать:

     MyTargetRange. Copy

  2. Скопируйте содержимое из буфера обмена в строковую переменную (поищите на этом сайте или в другом месте функции для передачи строк в/из буфера обмена).

  3. РАЗДЕЛИТЬ строку на вариантный массив:

     MyStringArray = Разделить (MyClipboardText, vbCrLf)

  4. ДОПОЛНИТЕЛЬНО: Массив будет иметь один дополнительный пустой элемент, потому что всегда есть дополнительный возврат (vbCrLf) в конце текста, который вы только что скопировали в буфер обмена. Чтобы удалить, просто измените размер массива:

     Redim Preserve MyStringArray (Ubound (MyStringArray) - 1)

Очень просто и быстро!!!

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

Это было бы ЧРЕЗВЫЧАЙНО ПОЛЕЗНЫМ, если вы работаете с большим количеством повторяющихся функций (тысячи), которые используют одни и те же данные (тысячи точек данных). При первом вызове вашей функции выполните все промежуточные вычисления в диапазонах данных, которые вам нужны, но сохраните результаты в статических переменных. Также сохраните строковую копию ваших входных диапазонов через буфер обмена. При каждом последующем вызове вашей функции преобразовывайте входные диапазоны в текст, опять же через буфер обмена, и сравнивайте с сохраненной копией. Если они совпадают, вы можете обойти свои предварительные расчеты.

1

Именованные диапазоны, используемые в VBA, уже являются массивами. Поэтому сначала сделайте диапазон именованным, затем обратитесь к нему и удалите именованный диапазон.
Например:

 ThisWorkbook.Names.Add Name:="test_array", RefersTo:=Sheet1.Range("A4:B10")
a = Sheet1.Range("test_array")
ThisWorkbook.Names("test_array").Удалить

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Массив строк VBA | Как использовать массив строк в Excel VBA?

Когда у нас есть несколько переменных, которые должны быть объявлены в коде VBA, мы можем объявить точное количество переменных с нужным нам типом данных. Но этот процесс довольно длительный, когда количество переменных превышает 5. Зачем объявлять переменные несколько раз, когда мы можем обрамить это в массив строк. Массив строк VBA используется, когда нам нужно хранить более одного строкового значения со строковой переменной. Это выглядит сложно, но на самом деле это очень легко реализовать. Нам не нужно объявлять переменную одного типа несколько раз, если каждая переменная хранит разные значения. Эта косая черта в огромном коде VBA выполняется с помощью массива строк Excel VBA.

Как использовать массив строк VBA?

Использовать массив строк VBA в любом коде очень просто. Для этого нам просто нужно определить, сколько переменных нам потребуется. Сначала это будет сделано с помощью DIM. Предположим, нам нужно 10 переменных любого типа данных, чтобы это можно было сделать, как показано ниже.

Код: 

 Sub VBA_StringArray()

Dim NameOfVariable (от 1 до 10) как тип данных

End Sub

Мы можем выбрать любое имя на место Имя переменной и любой тип данных в поле Тип данных , как указано выше.

Примеры массива строк в Excel VBA

Ниже приведены примеры массива строк Excel VBA.

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

Пример № 1

В этом примере мы увидим, как использовать массив строк в VBA без каких-либо ограничений на переменные. Здесь мы не будем определять длину переменных, вместо этого мы напрямую создадим массив строк и будем использовать количество переменных по мере необходимости. Для этого выполните следующие шаги:

Шаг 1: Вставьте новый модуль в редактор Visual Basic (VBE). Нажмите на вкладку «Вставка»> выберите «Модуль».

Шаг 2:  Определите подпроцедуру предпочтительно с именем массива строк VBA, или мы можем выбрать любое имя по нашему выбору.

Код: 

 Sub VBA_StringArray1()

End Sub

Шаг 3: Теперь мы будем использовать имена сотрудников для создания массива. Для этого объявите переменную с таким же именем и после этого поставьте скобки «()». И выберите любой тип данных. Мы можем выбрать String или Integer или Variant. Но так как данные могут отличаться, поэтому с помощью Вариант будет хорош.

Код: 

 Sub VBA_StringArray1()

Dim EmployeeData() как вариант

End Sub

Шаг 4: Теперь используйте ту же переменную, которую мы объявили выше, и используйте функцию Array .

Код: 

 Sub VBA_StringArray1()

Dim EmployeeData() как вариант
Данные Сотрудника = Массив(

End Sub

Как мы видим, согласно синтаксису Array, он допускает только тип данных Variant и список аргументов (). Причина, по которой мы видим тип данных Variant, заключается в том, что мы можем хранить в нем значения любого типа.

Шаг 5: Теперь рассмотрим имена сотрудников, которые мы будем использовать здесь. У нас есть Ананд, Шраддха, Аникет, Ашвани и Дипиндер в качестве имен сотрудников. И это должно быть так, как мы делаем конкатенацию.

Код: 

 Sub VBA_StringArray1()

Dim EmployeeData() как вариант
EmployeeData = Array ("Ананд", "Шраддха", "Аникет", "Ашвани", "Дипиндер")

End Sub

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

Код: 

 Sub VBA_StringArray1()

Dim EmployeeData() как вариант
EmployeeData = Array ("Ананд", "Шраддха", "Аникет", "Ашвани", "Дипиндер")
MsgBoxДанныеСотрудника(0) & ", " &ДанныеСотрудника(1) & ", " &ДанныеСотрудника(3) & ", " &ДанныеСотрудника(4)

End Sub

Шаг 7: Запустите этот код, нажав кнопку F5 или кнопку «Выполнить», расположенную на самой верхней ленте VBE.

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

Шаг 9:  Попробуем изменить последовательность массива данных сотрудников. Здесь мы обменялись 0 и 4 друг с другом.

Код: 

 Sub VBA_StringArray1()

Dim EmployeeData() как вариант
EmployeeData = Array ("Ананд", "Шраддха", "Аникет", "Ашвани", "Дипиндер")
MsgBoxДанныеСотрудников(4) & ", " &ДанныеСотрудников(1) & ", " &ДанныеСотрудников(3) & ", " &ДанныеСотрудников(0)

Конец суб

Шаг 10:  Давайте снова запустим этот код. Мы заметим, что имя сотрудника Данные Deepinder теперь перемещено на первое место, а Ананд находится на 4 -м месте.

Пример #2

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

Шаг 1: Напишите подпроцедуру.

Код: 

 Sub VBA_StringEmployeDataay2()

End Sub

Шаг 2:  Определите переменную как Variant с расположением ячеек в виде (1, 3), где 1 показывает позицию 2 и .

Код: 

 Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) как вариант

End Sub

Шаг 3: Теперь мы назначим каждому имени сотрудника разные координаты. Например, в 1 ст ряд, 2 й столбец у нас установлен сотрудник Ананд .

Код: 

 Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) как вариант
EmployeData(0, 1) = "Ананд"

End Sub

Шаг 4: Точно так же мы выберем разные координаты из позиции (1, 3) и дадим каждому сотруднику имя в другой позиции.

Код: 

 Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) как вариант
EmployeData(0, 1) = "Ананд"
EmployeData(0, 2) = "Шраддха"
EmployeData(1, 2) = "Аникет"
EmployeData(1, 3) = "Ашвани"
EmployeData(0, 0) = "Углубление"

Конец суб

Теперь, чтобы получить вывод из определенного массива, мы будем использовать окно сообщения.

Шаг 5: Мы использовали положение координат. Например, для (0, 1).

Код: 

 Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) как вариант
EmployeData(0, 1) = "Ананд"
EmployeData(0, 2) = "Шраддха"
EmployeData(1, 2) = "Аникет"
EmployeData(1, 3) = "Ашвани"
EmployeData(0, 0) = "Углубление"
MsgBox ("EmployeData In Index 0,1: " & EmployeData(0, 1))

Конец суб

Шаг 6:  Аналогично, другое окно сообщения для просмотра других значений, хранящихся в других координатах.

Код: 

 Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) как вариант
EmployeData(0, 1) = "Ананд"
EmployeData(0, 2) = "Шраддха"
EmployeData(1, 2) = "Аникет"
EmployeData(1, 3) = "Ашвани"
EmployeData(0, 0) = "Углубление"
MsgBox ("EmployeData In Index 0,1: " & EmployeData(0, 1))
MsgBox ("EmployeData In Index 1,2: " & EmployeData(1, 2))

Конец суб

Шаг 7:  После этого скомпилируйте код, нажав кнопку F8 или кнопку «Выполнить». Мы увидим, что значения, хранящиеся в массиве (0, 1), — это Ананд.

Шаг 8: Второй массив (1, 2) сохраняет значение как Aniket.

Так работает согласование в массиве String.

Шаг 9:  Что, если мы изменим координаты массива для второго окна сообщения с (1, 2) на (2, 2).

Код: 

 Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) как вариант
EmployeData(0, 1) = "Ананд"
EmployeData(0, 2) = "Шраддха"
EmployeData(1, 2) = "Аникет"
EmployeData(1, 3) = "Ашвани"
EmployeData(0, 0) = "Углубление"
MsgBox ("EmployeData In Index 0,1: " & EmployeData(0, 1))
MsgBox ("EmployeData In Index 1,2: " & EmployeData(2, 2))

End Sub

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