3.9.9. Функции для работы с массивами. Работа с массивами vba

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 () ‘Сохранение данных в массиве array_example = Range(«A2:C12»).Value

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

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

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

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

Массив (Array)

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

VBA Excel. Массивы (одномерные, многомерные, динамические)

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

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) (Упражнения)

rpilot62.ru

3.9.9. Функции для работы с массивами

Синтаксис и программные конструкции VBA

Например, вызов функции:

Choose(2, "Первый", "Второй", "Третий")

вернет "Второй".

IIf() — расшифровывается какImmediate If, т. е. "немедленныйIf". Представляет собой упрощенный вариантIf...Else, когда проверяется условие и возвращается одно из двух значений. Например:

IIf(n > 10, "Больше десяти", "Меньше или равно десяти")

Switch() — принимает неограниченное количество пар типа "выражение = значение", проверяет каждое выражение на истинность и возвращает значение для первого выражения, которое оказалось истинным. Например:

Function Language (CityName As String)

Language = Switch(CityName = "Москва", "русский", CityName = _

"Париж", "французский", CityName = "Берлин", "немецкий")

End Function

Как уже говорилось, при программной работе с приложениями Microsoft Office массивы используются редко. Вместо них применяются коллекции. Однако в VBA предусмотрены возможности и для работы с массивами.

Array() — позволяет автоматически создать массив нужного размера и типа и сразу загрузить в него переданные значения:

Dim myArray As Variant myArray = Array(10,20,30) MsgBox A(2)

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

LBound(),UBound() — возвращают соответственно информацию о нижней границе массива (номер первого имеющегося в массиве значения) и о верхней границе (номер последнего имеющегося значения).

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

3.9.10. Функции для работы с файловой системой

В VBA предусмотрен набор встроенных функций для выполнения различных операций с файлами, каталогами, дисками и прочими объектами файловой системы. Информация об этих функциях приведена далее. Но не забывайте, что помимо этих функций (общих для всех приложений, в которых используется VBA) у нас есть, во-первых,возможности, специфические для данного приложения (например, открытие и сохранение документа Word средствами объектной модели Word).Во-вторых,на любом компьютере под управлением

Windows есть объектная библиотека Microsoft Scripting Runtime, очень про-

стая и удобная для выполнения различных операций с файлами, каталогами и дисками. Можно добавить в проект VBA ссылку на нее и использовать все имеющиеся в ней возможности. Если, к примеру, мне нужно пройтись по всем файлам в данном каталоге и что-нибудьс ними сделать (например, загрузить в Excel все файлы отчетов, которые пришли из филиалов), я использую именно эту библиотеку. Справку по ней можно найти на сайте Microsoft (www.microsoft.com/scripting).

Далее приведены встроенные функции для работы с файловой системой, предусмотренные в VBA.

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

Dir() — позволяет искать файл или каталог по указанному пути на диске.

EOF() — при операции чтения или записи в файл на диске эта функция вернетTrue, если вы находитесь в конце файла.

Error() — позволяет вернуть описание ошибки по ее номеру. Генерировать ошибку нужно при помощи методаRaiseError() специального объектаErr (см. гл. 6, в которой рассказывается про перехват ошибок и отладку).

FileAttr() — позволяет определить, как именно был открыт вами файл в файловой системе: на чтение, запись, добавление, в двоичном или текстовом режиме и т. п.

FileDateTime() — позволяет получить информацию о последнем времени обращения к указанному вами файлу. Если к файлу после создания ни разу не обращались, то функция вернет время создания файла.

FileLen() — возвращает длину указанного вами файла в байтах.

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

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

Синтаксис и программные конструкции VBA

Input() — позволяет считать информацию из открытого файла. Например, считать информацию из файла C:\text1.txt и вывести ее в окно сообщений можно так:

Dim MyChar

'Открываем файл функцией Open() на чтение

Open "c:\text1.txt" For Input As #1

Do While Not EOF(1) ' Пока файл не кончился,

' получаем по одному символу и добавляем его к предыдущим

MyChar = MyChar & Input(1, #1)

Loop	'Закрываем файл
Close #1	'Закрываем файл
MsgBox MyChar	'Выводим его содержание в окно сообщения

Вариант этой функции — InputB() — позволяет указать количество байт, которые надо считать из файла.

Loc() — отLocation (местонахождение) — возвращает число, которое определяет текущее место вставки или чтения в открытом файле. Похоже работает функцияSeek(), но она возвращает информацию о позиции, с которой будет выполняться следующая операция чтения или вставки.

LOF() — отlength of file — позволяет определить длину открытого файла в байтах.

Open — это не функция, а команда VBA, но без нее операции чтения и записи с файлами на диске не произвести. Справку по ней можно найти по словосочетанию "Open Statement". Как минимум, ей нужно передать имя открываемого файла, режим открытия и номер файла (номер файла — это его идентификатор для передачи другим функциям, его назначаете вы сами). Например, чтобы открыть файл на чтение с возможностью одновременного обращения к нему других пользователей, можно использовать код вида:

Open "с:\file1.txt" For Output Shared As #1

3.9.11. Другие функции VBA

В этот раздел попали те встроенные функции языка VBA, которые я не смог отнести ни к одной другой категории.

DoEvents() — это очень важная функция. Она позволяет на время отвлечься от выполнениякакой-тооперации VBA и передать управление операционной системе, чтобы обработать накопившиеся в операционной системе события (например, нажатия клавиш пользователем). После этого продолжение операции VBA продолжается. Если у вас выполняется очень длительная операция (поиск на дисках, обработка большого объема дан-

ных и т. п.) и вы хотите дать пользователю возможность быстро прервать эту операцию, можно выполнять эту команду, например, каждый раз после обработки определенной "порции" данных.

Environ() — возвращает абсолютный путь для переменных окружения компьютера (полный список переменных, доступных на вашем компьютере, можно просмотреть, если в командной строке выполнить командуSET). Например, вам нужно записатьчто-тов файл во временном каталоге. Абсолютный путь к временному каталогу на вашем компьютере можно получить так:

MsgBox Environ("TEMP")

GetAllSettings() — позволяет получить (в виде двумерного массива) из реестра все параметры, которые относятся к указанному вами приложению. ФункцияSaveSetting() позволяет записать информацию в реестр, аDeleteSetting() — удалить.GetSetting() позволяет получить информацию об определенном параметре. Замечу, что эти методы позволяют обращаться только к одному очень далекому уголку реестра в ветвиHKEY_CURRENT_USERS. Обращаться к другим параметрам реестра при помощи этих методов бесполезно. Рекомендую для работы с реестром использовать объектную библиотеку Windows Script Host Object Model, которая также есть на любом компьютере под управлением Windows 2000, XP и 2003. Нужный объект называетсяWSHShell, методы —RegRead(),RegWrite() иRegDelete(). Справку по объектам этой библиотеки можно найти на сайте Microsoft (www.microsoft.com/scripting).

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

QBColor() — позволяет перевести обозначение цвета из старого номерного обозначения с возможными 16 значениями вRGB-код,который понимает VBA. Обычно используется при исправлении старых унаследованных программ.

RGB() — еще одна функция для работы с цветом. Позволяет вернуть RGBкод, который можно использовать для присвоения цвета, приняв три значения для цветов: красного (Red), зеленого (Green) и синего (Blue). Значение для каждого из основных цветов могут варьироваться от 0 до 255. Например, самый зеленый из возможных цветов получится, если переданные этой функции значения будут выглядеть какRGB(0, 255, 0).

Shell() — позволяет запустить из VBA внешний программный файл и вернуть информацию о его Program ID в операционной системе. Обычно

Синтаксис и программные конструкции VBA

применяется опытными разработчиками при использовании ими в программах возможностей Windows API. С практической точки зрения эту функцию можно использовать для запуска любых внешних программ из вашего приложения, хотя, с моей точки зрения, применение специальных объектов WshShell иWshExec из библиотеки Windows Script Host Object Model удобнее (можно передавать в окно клавиатурные комбинации, принимать и передавать значения через командную строку и т. п.). Эта библиотека есть на любом компьютере Windows, справку по ней можно найти на сайтеwww.microsoft.com/scripting.

TypeName() — функция, которая возвращает имя типа данных для переданной ей переменной. Очень удобна для определения типа данных для значения, полученного из базы данных или путем вызова методакакого-тообъекта.

VarType() — делает почти то же самое, но вместо имени возвращает числовой код, который обозначает тип данных. Можно использовать для программных проверок типов данных для переменных.

studfiles.net

Решение: Работа с массивами и заменой текста

Добрый день, логика следующая: я загружаю из excel файла в двухмерный массив. После чего выбирается документ и в нем прогоняется циклом поиск и замена слов. Идея в том, что необходимо сделать текст со всплывающими сносками, а в обычном ворде как я понял это реализуется гиперссылкой. Поэтому заменю на пустые гиперссылки с некоторым текстом. Проблема в чем: если искать текст из двух и более слов он почему то дублирует вот так В массиве (сто, сто 1, сто 12) а в тексте есть (сто 1, сто 12) после макроса получаю почему то (сто 1 1, сто 12 12) и почему то Application.ScreenUpdating = False не срабатывает вот код, гуру форума прошу не кидать кирпичами:Option Explicit Dim arr_ter() Private Sub CommandButton1_Click() Dim fd As FileDialog Dim fn As String Dim i As Long Set fd = Application.FileDialog(msoFileDialogFilePicker) fd.AllowMultiSelect = False fd.Title = "Выбрать файл для постановки ссылок" fd.Filters.Clear fd.Filters.Add "Word", "*.doc*;*.docx*", 1 fd.FilterIndex = 2 fd.InitialView = msoFileDialogViewDetails If fd.Show = 0 Then Exit Sub fn = fd.SelectedItems(fd.SelectedItems.Count) Application.ScreenUpdating = False Documents.Open (fn) 'поиск значений из массива в документе For i = LBound(arr_ter, 1) To UBound(arr_ter, 1) Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find.Replacement.Font .Underline = wdUnderlineNone .Color = wdColorBlack End With With Selection.Find .Text = arr_ter(i, 1) .Replacement.Text = arr_ter(i, 1) .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Do While Selection.Find.Execute = True ActiveDocument.Hyperlinks.Add _ Anchor:=Selection.Range, Address:=" ", SubAddress:="", _ ScreenTip:=arr_ter(i, 2), TextToDisplay:=Selection.Range Loop Selection.Find.Execute Replace:=wdReplaceAll Next 'изменение шрифтов для скрытия гиперссылок Selection.Find.ClearFormatting Selection.Find.Style = ActiveDocument.Styles("Гиперссылка") Selection.Find.Replacement.ClearFormatting With Selection.Find.Replacement.Font .Underline = wdUnderlineNone .Color = wdColorBlack End With With Selection.Find .Text = "" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll ActiveDocument.Save ActiveDocument.Close Set fd = Nothing Application.ScreenUpdating = True End Sub

studassistent.ru

Работа с массивами в Visual Basic

* Задание: Разработайте приложение, которое вычисляет сумму и среднее арифметическое 10 целых чисел от 0 до 100.

Выполнение:

Создайте новый проект. Расположите элементы управления на новой форме (рис. 15.1).

Рис. 15.1. Форма «Массивы»

Задайте свойству Multiline для текстового окна txtМассив значение равное true (для того, чтобы в текстовом окне можно было выводить текст в несколько строк).
Далее необходимо запрограммировать кнопку Вычислить так, чтобы в текстовое окно выводились элементы массива, их сумма, а затем их среднее арифметическое. Для этого в процедуре, описывающей событие щелчка мыши по кнопке Вычислить, опишем переменные, которые мы будем использовать при решении поставленной задачи:

Dim Массив(9) As Integer Dim i As Integer Dim Сумма As Integer

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

For i = 0 To 9 Массив(i) = Rnd() * 100 Next

Функция Rnd возвращает значение, которое меньше 1, но больше или равно нуля.

Теперь задайте начальное значение для Суммы, равное 0
С помощью следующего цикла вычислим сумму элементов массива:

For i = 0 To 9 Сумма = Сумма + Массив(i) Next

Теперь осталось вывести элементы массива, сумму и среднее арифметическое в текстовое окно.
Сначала выведите фразу Массив:

txtМассив.Text = "Массив: "

Теперь в цикле необходимо вывести элементы массива:

For i = 0 To 9 txtМассив.Text = txtМассив.Text + Str(Массив(i)) Next

Функция str представляет возвращаемое числовое значение как строковое (String).

Для того чтобы выводить текст с новой строки, добавьте символ перехода на новую строку, ASCII-код которого равен Chr(10)+Chr(13) (или значение встроенной константы vbCrLf), т.е.:

txtМассив.Text = txtМассив.Text + vbCrLf + "Сумма:" + Str(Сумма)

Самостоятельно добавьте вывод в текстовое окно среднего арифметического.
Запустите и отладьте программу.

Дополнительное задание:

Разработать приложение, которое находит максимальное и минимальное число из 15 заданных целых чисел.

Контрольное задание:

Разработайте приложение сортировки одномерного массива по возрастанию.

Вопросы для самоконтроля:

Как объявляется массив в VB?
Сколько элементов будет содержать массив, который описан с помощью следующего оператора:

Dim Девять(9) as integer

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

* При использовании материалов обязательна ссылка на источник: Майер С.Ф., «MS Visual Basic 2008» — Учебно-методическое пособие : Южный федеральный университет. – Ростов-на-Дону, 2017. – 92 с.

labs.org.ru