Функция разделения Excel VBA — объяснение с примерами

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

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

SPLIT — это встроенная строковая функция в Excel VBA, которую можно использовать для разделения текстовой строки на основе разделителя.

Функция Excel VBA SPLIT — синтаксис

Теперь, когда мы рассмотрели основы функции SPLIT, давайте рассмотрим несколько практических примеров.

Пример 1 — Разделение слов в предложении

Предположим, у меня есть текст — «Быстрая коричневая лисица перепрыгивает через ленивую собаку».

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

Хотя код не делает ничего полезного, он поможет вам понять, что делает функция Split в VBA.

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

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

1. Функция VBA SPLIT возвращает массив, который начинается с базы 0.
2. Когда результат функции SPLIT присваивается массиву, этот массив должен быть объявлен как тип данных String. Если вы объявите его как тип данных Variant, он покажет ошибку несоответствия типа). Обратите внимание, что в приведенном выше примере я объявил Result () как тип данных String.

Пример 2 — Подсчет количества слов в предложении

Вы можете использовать функцию SPLIT, чтобы получить общее количество слов в предложении. Хитрость здесь в том, чтобы подсчитать количество элементов в массиве, которое вы получите при разделении текста.

В приведенном ниже коде будет отображаться окно сообщения с количеством слов:

В этом случае функция UBound сообщает нам верхнюю границу массива (т.е. максимальное количество элементов в массиве). Поскольку база массива равна 0, добавляется 1, чтобы получить общее количество слов.

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

После создания вы можете использовать функцию WordCount, как любую другую обычную функцию.

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

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

Пример 3 — Использование разделителя, отличного от пробела

В предыдущих двух примерах мы использовали только один аргумент в функции SPLIT, а остальные были аргументами по умолчанию.

Когда вы используете какой-либо другой разделитель, вам необходимо указать это в формуле SPLIT.

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

В приведенном выше коде я использовал цикл For Next, чтобы просмотреть каждый элемент массива «Result», назначив его переменной «DisplayText».

Пример 4 — Разделите адрес на три части

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

Но если я укажу 3 как предел, то строка будет разделена только на три части.

Я могу использовать функцию Split в VBA, чтобы разделить этот адрес на три части.

Он разделяет первые два на основе разделителя запятой, а оставшаяся часть становится третьим элементом массива.

В приведенном ниже коде адрес будет отображаться в трех разных строках в окне сообщения:

Одно из практических применений этого может быть, когда вы хотите разделить однострочный адрес в формате, показанном в окне сообщения. Затем вы можете создать настраиваемую функцию, которая возвращает адрес, разделенный на три части (каждая часть в новой строке).

Если у вас есть этот код в модуле, вы можете использовать функцию (ThreePartAddress) в книге так же, как любую другую функцию Excel.

Эта функция принимает один аргумент — ссылку на ячейку с адресом.

Обратите внимание: чтобы полученный адрес отображался в трех разных строках, вам необходимо применить формат переноса текста к ячейкам (он находится на вкладке «Главная» в группе «Выравнивание»). Если формат «Перенос текста» не включен, вы увидите весь адрес как одну строку.

Пример 5 — Получение названия города из адреса

С помощью функции Split в VBA вы можете указать, какую часть результирующего массива вы хотите использовать.

Например, предположим, что я разделяю следующий адрес на основе запятой в качестве разделителя:

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

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

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

Вышеупомянутая функция принимает два аргумента: ссылку на ячейку с адресом и номер элемента, который вы хотите вернуть. Функция Split разделяет элементы адреса и присваивает их переменной Result.

Затем он возвращает номер элемента, который вы указали в качестве второго аргумента. Обратите внимание, что, поскольку база равна 0, ElementNumber-1 используется для возврата правильной части адреса.

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

Если вам нужно название города, вы можете использовать 2 в качестве второго аргумента. Если вы используете число, превышающее общее количество элементов, оно вернет #VALUE! ошибка.

Вы можете еще больше упростить код, как показано ниже:

В приведенном выше коде вместо использования переменной Result возвращается только указанный номер элемента.

Итак, если у вас есть Split («Доброе утро») (0), он вернет только первый элемент, который является «Хорошим».

Точно так же в приведенном выше коде он возвращает только указанный номер элемента.

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Как Вывести в Столбец Массив в Vba Excel © Размещено на allbestru | 📂Все о программе Word

• Value2 – базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 – самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
• Value – значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
• Text – визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

Одно из практических применений этого может быть, когда вы хотите разделить однострочный адрес в формате, показанном в окне сообщения. Затем вы можете создать настраиваемую функцию, которая возвращает адрес, разделенный на три части (каждая часть в новой строке).