Операторы ветвления VBA: сложный выбор. Безусловный переход. Язык программирования Visual Basic. Программирование ветвлений Операторы цикла. Вложенные циклы

O.K., нет никакого способа сделать то, что вы хотите. Вы не можете использовать ничего, кроме синтаксиса Excel внутри формулы, поэтому вещи, такие как "A1 = от 1 до 9", просто невозможно.

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

Cases("{A1="""",""there is nothing""},{else,A1}")

(обратите внимание на экранированные кавычки), Excel не будет обновлять вашу ссылку A1 при ее перемещении или скопировать формулу. Поэтому отбросьте весь параметр "синтаксис".

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

Public Function arr(ParamArray args()) arr = args End Function

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

Arr(A1=42, A1=99)

и вернуть массив логических значений.

С помощью этого небольшого UDF теперь вы можете использовать регулярные формулы для "выбора случаев". Они выглядели бы так:

CHOOSE(MATCH(TRUE, arr(A1>5, A1<5, A1=5), 0), "gt 5", "lt 5", "eq 5")

Что происходит, так это то, что "arr" возвращает логический массив, "MATCH" находит позицию первого TRUE, а "CHOOSE" возвращает соответствующий "случай".

Вы можете эмулировать предложение "else", обернув все это в "IFERROR":

IFERROR(CHOOSE(MATCH(TRUE, arr(A1>5, A1<5), 0), "gt 5", "lt 5"), "eq 5")

Если это слишком много для вас, вы всегда можете написать еще один VBA UDF, который приведет к MATCH, CHOOSE и т.д. внутри и вызовет его следующим образом:

Cases(arr(A1>5, A1<5, A1=5), "gt 5", "lt 5", "eq 5")

Это недалеко от вашего предложенного синтаксиса и намного, гораздо проще.

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

Итак, вот "UDF" дел:

Public Function cases(caseCondResults, ParamArray caseValues()) On Error GoTo EH Dim resOfMatch resOfMatch = Application.Match(True, caseCondResults, 0) If IsError(resOfMatch) Then cases = resOfMatch Else Call assign(cases, caseValues(LBound(caseValues) + resOfMatch - 1)) End If Exit Function EH: cases = CVErr(xlValue) End Function

Он использует небольшую вспомогательную процедуру, "assign":

Public Sub assign(ByRef lhs, rhs) If IsObject(rhs) Then Set lhs = rhs Else lhs = rhs End If End Sub

Процедура "назначать" просто упрощает рассмотрение того факта, что пользователи могут вызывать UDF с любыми значениями или ссылками на диапазон. Поскольку мы хотим, чтобы UDF наших "случаев" работал, как Excel "CHOOSE", мы хотели бы возвращать ссылки при необходимости.

В основном, в новом UDF "случаях" мы сами делаем "выбор", индексируя в массив параметров значений case. Я удалил обработчик ошибок там, поэтому основные вещи, такие как несоответствие между результатами условия случая и значениями case, приведут к возвращаемому значению #VALUE!. Вероятно, вы добавили бы больше проверок в настоящую функцию, например, чтобы убедиться, что результаты условия были логическими и т.д.

Я рад, что вы достигли еще лучшего решения для себя! Это было интересно.

БОЛЬШЕ О "assign":

В ответ на ваш комментарий, здесь больше о том, почему это является частью моего ответа. VBA использует другой синтаксис для назначения объекта переменной, чем для присвоения простого значения. Посмотрите на справки VBA или посмотрите этот вопрос stackoverflow, а другим понравятся: Что действительно делает ключевое слово Set в VBA?

Это имеет значение, потому что, когда вы вызываете функцию VBA из формулы Excel, параметрами могут быть объекты типа Range, в дополнение к номерам, строкам, логическим значениям, ошибкам и массивам. (См. Может ли Excel VBA UDF, вызванный из листа, когда-либо передаваться экземпляр любого класса объектной модели Excel VBA, отличного от "Range" ?)

Ссылки на диапазон - это то, что вы описываете, используя синтаксис Excel, такой как A1: Q42. Когда вы передаете один в Excel UDF в качестве параметра, он отображается как объект Range. Если вы хотите вернуть объект Range из UDF, вам нужно сделать это явно с помощью ключевого слова VBA "Установить". Если вы не используете "Установить", Excel вместо этого примет значение, содержащееся в пределах диапазона, и вернет его. В большинстве случаев это не имеет значения, но иногда вам нужен фактический диапазон, например, когда у вас есть именованная формула, которая должна оцениваться в диапазоне, потому что она используется в качестве источника для списка проверки.

При создании сложных программ один из ключевых моментов - возможность предусмотреть несколько вариантов развития событий. Самый простой и классический пример - оператор "If ... Then ... Else ... End ", который позволяет выбрать одно из двух действий в зависимости от результатов проверки каких-либо значений. Бывает, что в результате такой проверки необходимо выбрать из множества вариантов. Один из выходов: добавить множество "... ElseIf ...", что несколько усложняет синтаксис программы (лёгкость её чтения). Однако это очень мощный оператор, открывающий большие возможности. Подробнее о нём можно узнать .

Альтернативой оператору "If ... End " служит оператор "Select Case " (с английского "Select Case " можно перевести как "Выбор Ситуации"), который упрощает восприятие кода "на глаз". И если "If ... End " оператор в каждом своём "ElseIf " вынужден обращаться к проверяемым значениям снова и снова (допустим, выражение каждый раз одинаковое), то "Select Case " делает это только один раз, что позволяет последнему на больших массивах данных работать быстрее. Этот оператор позволяет удобно задать ветвление программы из одной точки в большое количество веток. То есть в основном применяется при множественных условиях проверки, когда проверяемых условий больше двух.

Структура оператора "Select Case".

Давайте посмотрим, как выглядит обобщённая структура оператора и разберём, что есть что (разные примеры частного использования кода будут приведены в конце статьи):

Select Case [Проверяемое Значение] Case [Конкретное Значение] [Некоторое Действие] Case Else [Некоторое Действие Х] End Select

В качестве куска [Значение] можно вставить любую переменную или свойство, значение которой или которого Вы можете проверить. Можно также проверять значение конкретной ячейки. При этом работать можно не только с числами, но и с текстами. И даже с булевыми значениями TRUE/FALSE («Правда» и «Ложь»), о чем знают не все.

[Конкретное Значение] - это то, с чем сравнивается [Проверяемое Значение] . И, если одно удовлетворяет другому, то выполняется [Некоторое Действие] . Есть несколько вариантов записи для блока [Конкретное Значение] . Для текстовых и числовых значений можно записывать разные значения через запятую:

Case 3, 4, 5, "да", "нет"

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

Case 3 to 10 "От 3-х до 10-ти, включая сами 3 и 10.

Также для чисел можно использовать логический оператор сравнения вместе с частицей "Is ":

Case Is < 2 "Меньше 2, НЕ включая 2 Case Is = 3 "Равно 3-м. Избыточная запись, достаточно Case 3 Case Is >= 4 "Больше либо равно 4 Case Is <> 0 "Не равно нулю

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

Case ... And ... Case Not ...

[Некоторое Действие] может быть абсолютно любым. Если вы его пропускаете - то для данного случая программа будет бездействовать. «Case [Конкретное Значение] » вместе с частью [Некоторое Действие] складываются в один блок:

Case [Конкретное Значение] [Некоторое Действие]

Таких блоков может быть любое количество, которое уложится в предельные размеры процедуры (она должна весить не более 64 килобайт). Полезно знать, что VBA просматривает соответствие [Конкретного Значения] и [Проверяемого Значения] вдоль по блокам сверху вниз. То есть, у Вас может быть два блока с одинаковым "Case ", но выполнится только тот, который будет раньше найден программой при просмотре кода сверху вниз.

Case Else - это все другие случаи, которые не подошли ни под одно другое [Конкретное Значение] во всех блоках оператора "Select Case ". Если блок "Case Else " отсутствует и ни один другой блок не подошёл, то программа делает логичное "ничего". Case Else должен быть последним проверяемым случаем среди всех блоков проверки в операторе. После него других блоков быть не должно, иначе получим синтаксическую ошибку "Case without Select Case ".

В конце оператора должен стоять "End Select ", который служит "точкой" в "предложении" оператора.

Примеры использования.

Рассмотрим несколько примеров использования кода и начнём с самого простого. В первом примере в зависимости от значения Х выводится сообщение.

Sub SelectCase_example_1() Dim X As Long X = 1 "Можете изменять эту цифру и смотреть, что получится. Select Case X Case 1 MsgBox "Один" Case 2 MsgBox "Два" Case 3 MsgBox "Три" Case Else MsgBox "Выбрано что-то другое" End Select End Sub

Второй пример показывает некоторые виды записи проверяемого значения. В зависимости от количества листов в книге с макросом выводится разное сообщение. Обратите внимание, что если листов в книге 7, то первым сработает “Case 7 ”, хотя условие “Case 5 to 12 ” тоже подходит, но стоит позже.

Sub SelectCase_example_2() "Введём переменную и посчитаем количество листов в текущей книге: Dim X As Long X = ThisWorkbook.Sheets.Count Select Case X "В зависимости от количества листов в книге выведем сообщение. Case 1 "Если 1 лист, то... MsgBox "Один лист в книге" Case 2, 3, 4 "Если листов 2 или 3 или 4 MsgBox "Несколько листов в книге" Case 7 "Если листов 7 MsgBox "Красивое количество листов" Case 5 To 12 "Если листов от 5 до 12 MsgBox "Почти брошюра" Case Is >= 14 "Если листов больше либо равно 14 MsgBox "Листов как в фолианте" Case Else "Все остальные случаи, а именно 13 MsgBox "Чёртова дюжина листов" End Select End Sub

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

Sub SelectCase_example_3() "Введём переменную и привяжем её к последнему листу в книге: Dim shtX As Worksheet: Set shtX = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Select Case shtX.Visible "Проверим, скрыт ли лист или нет Case True: MsgBox "Последний лист в книге доступен" "Если последний лист виден Case False: MsgBox "Последний лист в книге скрыт" "Если последний лист скрыт End Select End Sub

Четвёртый пример показывает, что «Case » может ориентироваться и на другие переменные. В данном случае будем проверять равенство трёх переменных с помощью логического оператора «And »:

Sub SelectCase_example_4() "Введём несколько переменных: Dim X%, Y%, Z% "Приравняем всех к тройке: X = 3: Y = 3: Z = 3 Select Case True "Проверим равенство всех переменных Case Z = X And Y = X: MsgBox "Все равны" "Если все равны Case Else: MsgBox "Кто-то отличается" "Если хоть кто-то отличается End Select End Sub

Пятый пример показывает, как через запятую в проверяемом значении для «Case » можно указать целый набор чисел. Допустим, есть некоторая функция и мы проверяем, может ли наше число в этой функции использоваться. По условию, нас устраивают числа в диапазоне от 5 (не включая 5) до минус бесконечности, от 12 до 15 включая концы и от 20 (включая 20) до плюс бесконечности.

Sub SelectCase_example_5() "Введём переменную и дадим ей значение вручную Dim X As Double X = InputBox("Введите числовое значение переменной Х") Select Case X "Проверим, подходит ли некоторой воображаемой функции наше значение Case Is < 5, Is >= 20, 12 To 15 "Диапазон подходящих значений MsgBox "Действительное значение для некоторой функции" Case Else "Не подходящие значения MsgBox "Значение не может быть использовано в некоторой функции" End Select End Sub

Подводя черту, замечу, что оператор «Select Case » по структуре довольно прост и удобен в использовании. Он менее гибок по сравнению с «If … End », если по ходу проверок требуется менять проверяемое значение, но значительно выигрывает при разнообразных проверках одного и того же выражения. Для чего собственно и был создан.

Спасибо за внимание.

Статью c примерами составил Роман «Rioran» Воронов для www.сайт

VBAимеет условный оператор перехода для использования в случаях, когда необходимо выбирать из большого количества различных ветвей кода: операторSelect Case (с его помощью очень удобно реализовывать структуру множественного выбора).Он работает во многом так же, как множе­ство независимых операторовIf ,но он более понятен для того, кто пишет код, и того, кто читает этот код. Этот оператор более эффективен, чем операторIf … Then … Else .

Ключевые слова Select Case используются со мно­гими операторамиCase ,где каждый операторCase проверяет появление дру­гого условия и выполняется только одна из ветвейCase .ВетвьCase может со­держать один, несколько или ни одного оператораVBA.

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

Select Case Выражение_выбора

[ Case Список_выражений_1

[Блок_операторов_1]

[Case Список_выражений_2 ]

[Блок_операторов_2]]

……………………………………………………………………………

[Case E lse

[Блок_операторов_N]]

E nd S elect

– Выражение_выбора – любое числовое или символьное выражение;

– Список_выражений – каждый из списков представляет собой список логических выражений, отделенных запятыми; имеют тот же тип, что и выражение_выбора;

– Блок_операторов – содержит любое количество операторов VBA.

При выполнении оператора Select Case VBA сначала оценивает Выражение_выбора, а затем сравнивает результат этого выражения с каждым выраже­нием, перечисленным в каждом Списке_выражений. Если значение, представлен­ное с помощью Выражение_выбора совпадает с выражением в Списке_выражений для одного из Case ,VBA выполняет Блок_операторов для этого предложения Case .Если значение Выражение_выбора совпадает более, чем с одним оператором Case ,VBA выполняет только операторы в первом совпадающем предложении Case .Часто Выражение_выбора – это просто имя одной переменной, математическое или чис­ленное, а не логическое выражение. Выражения в Списке_выражений – это обыч­но логические выражения.

После завершения выполнения операторов в первом совпадающем опера­торе Case VBA продолжает выполнение кода с первого оператора после ключе­вых слов End Select ,которые обозначают конец Select Case .

Если значение Выражение_выбора не совпадает ни с каким из Case ,а необяза­тельный Case Else присутствует, VBA выполняет операторы, представленные с помощью Блок_операторов_N перед переходом к оператору после Select Case . Блок операторов Case E lse выполняется только в том случае, если Выражения_выбора не удовлетворяет ни одному из условий Case . Обычно используется для обработки нежелательных значений. Действие оператора Select Case поясняется блок-схемой, приведенной на рис. 5.

Рис 5. Блок-схема конструкции Select Case

Элементы Списка _ выражений должны иметь одну из следующих трех форм:

Выражение_1,Выражение_2, …, Выражение_N

Выражение Т o Выражение

I s Выражение с операцией

– Выражение_ – любое числовое, символьное или логическое выражение того же типа, что и выражение выбора;

– Выражение с операцией – выражение, содержащее любую из следующих операций: <, <=, >, >=, < >, =.

Если используется ключевое слово Тo для определения пределов выражения, то меньшее значение должно быть первым. Например, операторы блока Case -1 To -5 не выполняются, если Выражение _ выбора равно -4. Эта строка должна быть написана как Case -5 To -1 .

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

Можно использовать несколько выражений или пределов в каждом условии Case . Например, Case 1 TO 4, 7 TO 9, 11, 13, Is > n % .

Пример 4. Программа, рассчитывающая скидку в зависимости от суммы покупки.

Sub skidka()

" Определение скидки (в %) в зависимости от

" количества продаваемого товара

Dim skidka As Integer

Dim summa As Single

summa = InputBox ("Введите сумму покупки", "Расчет скидки", 0)

If summa > 0 Then

Select Case summa

Case Is > 1000

Case Is > 500

Case Else

End Select

MsgBox "Скидка" & skidka & "%"

MsgBox "Сумма покупки не указана"

Оператор Select Case VBA языка предназначен для формирования выбора операции в зависимости от значения, по сути, данный оператор выбора является гибридом условного оператора vba if then. Все довольно просто – в сценарии есть переменная, значение которой будет изменяться, и в зависимости от значения нужно выбрать, какой блок кода (выражение) выполнить.

Как и в условном операторе , тут происходит проверка значения, например: есть текстовое поле, в которое нужно ввести данные, допустим, возраст. Далее следует проверить введенное значение, если значение меньше 18 – вывести один текст, если значение находится между 19 и 30 – другой текст, в противном случае – вывести третий текст. Как уже говорилось, для таких целей можно использовать условный оператор, однако, оператор выбора VBA Select Case является более компактным варинтом.

Синтаксис оператора VBA Select Case:

Select Case значение
Select условие 1
Набор операторов

Select условие 2
Набор операторов

Select условие 3
Набор операторов

….

Select условие N
Набор операторов N

Case Else
Другие операторы

End Select

Вначале происходит проверка параметра “значение”, который может быть как строкового, так и числового типа, после проверки параметра, происходит поочередное сравнение (начиная сначала) с каждый условием, которые прописаны в операторах Select Case VBA. Если совпадение произойдет для “условие 1”, то будет выполнен “набор операторов 1”, после чего произойдет выполнения кода, находящегося после End Select . Оператор Case Else формально означает “В противном случае”, то есть, если ни одно из условий не подходит, то выполнится набор операторов, прописанных для Case Else. Само выражение VBA - Case Else не является обязательным, как и “другие операторы”, при необходимости его можно опустить, оно всегда прописывается в конце.

Давайте рассмотрим такой пример кода:

В данном примере у нас есть процедура CommandButton1_Click , которая отвечает за обработку нажатия по кнопке. Переменная a будет хранить значение текстового поля TextBox1 (свойство Text). Далее следует оператор выбора Select case VBA языка, в котором происходит проверка значения переменной a, после чего, значение сравнивается с условиями:

• Case Is < 100 – если значение переменной a меньше числа 100
• Case 101 To 150 – если значение находится в диапазоне от 101 до 150
• Case 151 To 200 – диапазон проверки от 151 до 200
• Case Else – собственно, всю другие значения.

В зависимости от результат проверки, в свойство Caption, объекта , будет записанное информативное сообщение.

Процедура UserForm_Activate содержит строки кода для начальной настройки компонентов формы – цвет, размер, начальный текст и так далее.

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

Тут OBModule – имя модуля (макроса), а OBForm – название формы.

Стоит обратить внимание, что даже при совпадении условие, например: меньше 100 и больше 99, в операторе выбора Select Сase произойдет выполнения лишь для первого подходящего условия, а все остальные будут проигнорированы.

Основные управляющие структуры VBA:

Sub TestForEachNextRangeChanges()

For Each iCell In Range("A1:C5")

i = i + 1: iCell.Value = i

If iCell.Value 5 Then
iCell.Font.Size = 15
iCell.Interior.ColorIndex = 50
ElseIf iCell.Value = 13 Or iCell.Value = 5 Then
iCell.Font.Size = 20
iCell.Interior.ColorIndex = 3
Else
iCell.Clear
End If

Обратите внимание на:
- в этом VBA коде для проверки значений находящихся в ячейке мы применили уже известную нам инструкцию If Then Else.
- в зависимости от того какое условие прошло проверку, выполняется определённый фрагмент кода, который использует различные свойства об"екта Range.

Пример с семейством WorkSheets:

Sub TestForEachNextSheet()

For Each iList In Worksheets
i = i + 1
iNameList = iNameList & Chr(10) & iList.Name
Next

MsgBox "Число рабочих листов: " & i & Chr(10) & _
"Имена рабочих листов: " & iNameList

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