ИТМ_13 / UserForms_примеры. Vba offset пример

Как обратиться к диапазону из VBA

Хитрости » 27 Июль 2013 Дмитрий 183740 просмотров

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

Dim rR as Range Set rR = Range("D5")

Тоже самое можно сделать сразу для нескольких ячеек:

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

Range("Диапазон1").Select

Диапазон1 - это имя диапазона/ячейки, к которому надо обратиться в коде. Указывается в кавычках, как и адреса ячеек.Но в VBA есть и альтернативный метод записи значений в ячейке - через объект Cells:

Cells(1, 1).Value = "Привет"

Синтаксис объекта Range:Range(Cell1, Cell2)

Cell1 - первая ячейка диапазона. Может быть ссылкой на ячейку или диапазон ячеек, текстовым представлением адреса или имени диапазона/ячейки. Допускается указание несвязанных диапазонов(A1,B10), пересечений(A1 B10).
Cell2 - последняя ячейка диапазона. Необязательна к указанию. Допускается указание ссылки на ячейку, столбец или строку.

Синтаксис объекта Cells:Cells(Rowindex, Columnindex)

Rowindex - номер строки
Columnindex - номер столбца

Исходя из этого несложно предположить, что к диапазону можно обратиться, используя Cells и Range:

'выделяем диапазон "A1:B10" на активном листе Range(Cells(1,1), Cells(10,2)).Select

и для чего? Ведь можно гораздо короче:

Иногда обращение посредством Cells куда удобнее. Например для цикла по столбцам(да еще и с шагом 3) совершенно неудобно было бы использовать буквенное обозначение столбцов.Объект Cells так же можно использовать для указания ячеек внутри непосредственно указанного диапазона. Например, Вам необходимо выделить ячейку в 3 строке и 2 столбце диапазона "D5:F56". Можно пройтись по листу и посмотреть, отсчитать нужное количество строк и столбцов и понять, что это будет "E7". А можно сделать проще:

Range("D5:F56").Cells(3, 2).Select

Согласитесь, это гораздо удобнее, чем отсчитывать каждый раз. Особенно, если придется оперировать смещением не на 2-3 ячейки, а на 20 и более. Конечно, можно было бы применить Offset. Но данное свойство именно смещает диапазон на указанное количество строк и столбцов и придется уменьшать на 1 смещение каждого параметра для получения нужной ячейки. Да и смещает на указанное количество строк и столбцов весь диапазон, а не одну ячейку. Это, конечно, тоже не проблема - можно вдобавок к этому использовать метод Resize - но запись получится несколько длиннее и менее наглядной:

Range("D5:F56").Offset(2, 1).Resize(1, 1).Select

И неплохо бы теперь понять, как значение диапазона присвоить переменной. Для начала переменная должна быть объявлена с типом Range. А т.к. Range относится к глобальному типу Object, то присвоение значения такой переменной должно быть обязательно с применением оператора Set:

Dim rR as Range Set rR = Range("D5")

если оператор Set не применять, то в лучшем случае получите ошибку, а в худшем(он возможен, если переменной rR не назначать тип) переменной будет назначено значение Null или значение ячейки по умолчанию. Почему это хуже? Потому что в таком случае код продолжит выполняться, но логика кода будет неверной, т.к. эта самая переменная будет содержать значение неверного типа и применение её в коде в дальнейшем все равно приведет к ошибке. Только ошибку эту отловить будет уже сложнее.Использовать же такую переменную в дальнейшем можно так же, как и прямое обращение к диапазону:

Вроде бы на этом можно было завершить, но...Это как раз только начало. То, что я написал выше знает практически каждый, кто пишет в VBA. Основной же целью этой статьи было пояснить некоторые нюансы обращения к диапазонам. Итак, поехали.

Обычно макрорекордер при обращении к диапазону(да и любым другим объектам) сначала его выделяет, а потом уже изменяет свойство или вызывает некий метод:

'так выглядит запись слова Test в ячейку А1 Range("A1").Select Selection.Value = "Test"

Но как правило выделение - действие лишнее. Можно записать значение и без него:

'запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"

Теперь чуть подробнее разберем, как обратиться к диапазону не выделяя его и при этом сделать все правильно. Диапазон и ячейка - это объекты листа. У каждого объекта есть родитель - грубо говоря это другой объект, который является управляющим для дочернего объекта. Для ячейки родительский объект - Лист, для Листа - Книга, для Книги - Приложение Excel. Если смотреть на иерархию зависимости объектов, то от старшего к младшему получится так:Applicaton => Workbooks => Sheets => RangeПо умолчанию для всех диапазонов и ячеек родительским объектом является текущий(активный) лист. Т.е. если для диапазона(ячейки) не указать явно лист, к которому он относится, в качестве родительского листа для него будет использован текущий - ActiveSheet:

www.excel-vba.ru

Работа с диапазонами в VBA

Настоящая заметка продолжает знакомство с VBA, в ней описана работа с диапазонами в VBA.[1]

Рис. 1. Пример, демонстрирующий, как выделять диапазоны различной формы в VBA$ чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)

Копирование диапазона

Функция записи макросов Excel используется не столько для создания хорошего кода, сколько для поиска названий необходимых объектов, методов и свойств. Например, при записи операции копирования и вставки можно получить код:

Sub Макрос() Range("A1").Select Selection.Copy Range("B1").Select ActiveSheet.PasteEnd Sub

Обратите внимание, что данная программа выделяет ячейки. Однако в VBA для работы с объектом не обязательно его выделять. Данную процедуру можно заменить значительно более простой — применить метод Сору, который использует аргумент, представляющий адрес места вставки копируемого диапазона.

Sub CopyRange() Range("А1").Copy Range("В1")End Sub

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

Sub CopyRange2() Workbooks("File1.xlsx").Sheets("Лист1").Range("A1").Copy _ Workbooks("File2.xlsx").Sheets("Лист2").Range("A1")End Sub

Еще одним подходом к решению этой задачи является использование для представления диапазонов объектных переменных:

Sub CopyRange3() Dim Rngl As Range, Rng2 As Range Set Rngl = Workbooks("File1.xlsx").Sheets("Лист1").Range("A1") Set Rng2 = Workbooks("File2.xlsx").Sheets("Лист2").Range("A1")Rngl.Copy Rng2 End Sub

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

Sub CopyRange4 () Range("А1:С800").Copy Range("D1")End Sub

Для перемещения диапазона ячеек вместо метода Сору используется метод Cut.

Если размер копируемого диапазона не известен используется свойство CurrentRegion, возвращающее объект Range, который соответствует прямоугольнику ячеек вокруг заданной ячейки:

Sub CopyCurrentRegion2() Range("A1").CurrentRegion.Copy Sheets("Лист2").Range("A1")End Sub

Метод End имеет один аргумент, определяющий направление, в котором увеличивается выделение ячеек. Следующий оператор выделяет диапазон от активной ячейки до последней непустой ячейки внизу:

Range (ActiveCell, ActiveCell.End(xlDown)).Select

Три остальные константы имитируют комбинации клавиш при выделении в других направлениях: xlUp (вверх), xlToLeft (влево) и xlToRight (вправо).

В прилагаемом Excel-файле определено несколько распространенных типов выделения ячеек (см. рис. 1). Код любопытен тем, что является также примером создания контекстного меню.

Запрос значения ячейки

Следующая процедура запрашивает значение у пользователя и вставляет его в ячейку А1:

Sub GetValuel() Range("A1").Value = InputBox("Введите значение")End Sub

Однако при выполнении этой процедуры возникает проблема. Если пользователь щелкнет на кнопке Отмена в окне ввода данных, то процедура удалит данные, которые находились в текущей ячейке. Модифицированная версия процедуры адекватно реагирует на щелчок на кнопке Отмена и не выполняет при этом никаких действий:

Sub GetValue2() Dim UserEntry As Variant UserEntry = InputBox("Введите значение") If UserEntry <> "" Then Range("A1").Value = UserEntryEnd Sub

Во многих случаях следует проверить правильность данных, введенных пользователем. Например, необходимо обеспечить введение только чисел в диапазоне от 1 до 12 (рис. 2). Это можно сделать при помощи процедуры GetValue3(), код которой приведен в Модуле1 приложенного Excel-файла. Некорректные данные игнорируются, и окно запроса значения отображается снова. Этот цикл будет повторяться, пока пользователь не введет правильное значение или не щелкнет на кнопке Отмена.

Рис. 2. Проверка данных, введенных пользователем

Ввод значения в следующую пустую ячейку

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

Sub GetData() Dim NextRow As Long Dim Entry1 As String, Entry2 As StringDo' Определение следующей пустой строки NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1' Запрос данных Entry1 = InputBox("Введите имя") If Entry1 = "" Then Exit Sub Entry2 = InputBox("Введите сумму") If Entry2 = "" Then Exit Sub' Запись данных Cells(NextRow, 1) = Entry1 Cells(NextRow, 2) = Entry2LoopEnd Sub

Рис. 3. Макрос вставляет данные в следующую пустую строку рабочего листа

Это бесконечный цикл. Для выхода из него (щелкните на кнопке Cancel) использовались операторы Exit Sub. Обратите внимание строку, в который определяется значение переменной NextRow. Если вам трудно ее понять, проанализируйте содержимое ячейки: перейдите в последнюю ячейку столбца А и нажмите <End> и <↑>. После этого будет выделена последняя непустая ячейка в столбце А. Свойство Row возвращает номер этой строки; чтобы получить расположенную под ней строку (следующую пустую строку), к этому номеру прибавляется 1.

Приостановка работы макроса для определения диапазона пользователем

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

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

Sub GetUserRange() Dim UserRange As Range Prompt = "Выберите диапазон для случайных чисел." Title = "Выбор диапазона"' Отображение поля ввода On Error Resume Next Set UserRange = Application.InputBox( _ Prompt:=Prompt, _ Title:=Title, _ Default:=ActiveCell.Address, _ Type:=8) 'Выделение диапазона On Error GoTo 0' Отменено ли отображение поля ввода? If UserRange Is Nothing Then MsgBox "Отменено." Else UserRange.Formula = "=RAND()" End IfEnd Sub

Окно ввода данных показано на рис. 4. Важный момент в этой процедуре – определение аргумента Туре равным 8 (в этом случае InputBox вернет диапазон; подробнее см. Application.InputBox Method).

Рис. 4. Использование окна ввода данных с целью приостановки выполнения макроса

Оператор On Error Resume Next игнорирует ошибку, если пользователь не выберет диапазон, а щелкает Отмена. В таком случае объектная переменная UserRange не получает значения. В этом случае отобразится окно сообщения с текстом «Отменено». Если же пользователь щелкнет на кнопке OK, то макрос продолжит выполняться. Строка On Error Go То указывает на переход к стандартной обработке ошибки. Проверка корректного выделения диапазона необязательна. Excel позаботится об этом вместо вас.

Обязательно проверьте, включено ли обновление экрана при использовании метода InputBox для выделения диапазона. Если обновление экрана отключено, вы не сможете выделить рабочий лист. Чтобы проконтролировать обновление экрана, в процессе выполнения макроса используйте свойство ScreenUpdating объекта Application.

Подсчет выделенных ячеек

Работая с макросом, который обрабатывает выделенный диапазон ячеек, можно использовать свойство Count, чтобы определить, сколько ячеек содержится в выделенном (или любом другом) диапазоне. Например, оператор MsgBox Selection.Count демонстрирует окно сообщения, которое отображает количество ячеек в текущем выделенном диапазоне. Свойство Count использует тип данных Long, поэтому наибольшее значение, которое может храниться в нем, равно 2 147 483 647. Если выделить лист целиком, то ячеек будет больше, и свойство Count сгенерирует ошибку. Используйте свойство CountLarge, которое не имеет таких ограничений.

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

CellCount = Range("data").Count

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

Selection.Columns.Count

Следующий оператор пересчитывает количество строк в диапазоне с названием data и присваивает это количество переменной RowCount.

RowCount = Range("data").Rows.Count

Просмотр выделенного диапазона

Вы можете столкнуться с трудностями при создании макроса, который оценивает каждую ячейку в диапазоне и выполняет операцию, определенную заданному критерию. Если выделен целый столбец или строка, то работа макроса может занять много времени. Процедура ColorNegative устанавливает красный цвет для ячеек, которые содержат отрицательные значения. Цвет фона для других ячеек не определяется. Код процедуры можно найти в Модуле4 приложенного Excel-файла.

Усовершенствованная процедура ColorNegative2, создает объектную переменную WorkRange типа Range, которая представляет собой пересечение выделенного диапазона и диапазона рабочего листа (рис. 5). Если выделить столбец F (1048576 ячеек), то его пересечение с рабочим диапазоном В2:I16) даст область F2:F16, которая намного меньше исходного выделенного диапазона. Время, затрачиваемое на обработку 15 ячеек, намного меньше времени, уходящего на обработку миллиона ячеек.

Рис. 5. В результате пересечения используемого диапазона и выделенного диапазона рабочего листа уменьшается количество обрабатываемых ячеек

И всё же процедура ColorNegative2 недостаточно эффективна, поскольку обрабатывает все ячейки в диапазоне. Поэтому предлагается процедура ColorNegative3. В ней используется метод SpecialCells, с помощью которого генерируются два поднабора выделенной области: один поднабор (ConstantCells) включает ячейки, которые содержат исключительно числовые константы; второй поднабор (FormulaCells) включает ячейки, содержащие числовые формулы. Обработка ячеек в этих поднаборах осуществляется с помощью двух конструкций For Each-Next. Благодаря тому, что исключается обработка пустых и нетекстовых ячеек, скорость выполнения макроса существенно увеличивается.

Sub ColorNegative3()' Окрашивание ячеек с отрицательными значениями в красный цвет Dim FormulaCells As Range, ConstantCells As Range Dim cell As Range If TypeName(Selection) <> "Range" Then Exit Sub Application.ScreenUpdating = False' Создание поднаборов исходной выделенной области On Error Resume Next Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers) Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers) On Error GoTo 0' Обработка ячеек с формулами If Not FormulaCells Is Nothing Then For Each cell In FormulaCells If cell.Value < 0 Then cell.Interior.Color = RGB(255, 0, 0) Else cell.Interior.Color = xlNone End If Next cell End If' Обработка ячеек с константами If Not ConstantCells Is Nothing Then For Each cell In ConstantCells If cell.Value < 0 Then cell.Interior.Color = RGB(255, 0, 0) Else cell.Interior.Color = xlNone End If Next cell End IfEnd Sub

Оператор On Error необходим, поскольку метод SpecialCells генерирует ошибку, если не находит в диапазоне ячеек указанного типа.

Удаление всех пустых строк

Следующая процедура удаляет все пустые строки в активном рабочем листе. Она достаточно эффективна, так как не проверяет все без исключения строки, а просматривает только строки в так называемом «используемом диапазоне», определяемом с помощью свойства UsedRange объекта Worksheet.

Sub DeleteEmptyRows() Dim LastRow As Long Dim r As Long Dim Counter As Long Application.ScreenUpdating = False LastRow = ActiveSheet.UsedRange.Rows.Count + _ ActiveSheet.UsedRange.Rows(1).Row — 1 For r = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete Counter = Counter + 1 End If Next r Application.ScreenUpdating = True MsgBox Counter & " Пустые строки удалены."End Sub

Первый шаг — определить последнюю используемую строку и присвоить этот номер строки переменной LastRow. Это не так просто, как можно ожидать, поскольку текущий диапазон необязательно начинается со строки 1. Следовательно, значение LastRow вычисляется таким образом: к найденному количеству строк используемого диапазона прибавляется номер первой строки текущего диапазона и вычитается 1.

В процедуре применена функция Excel СЧЁТЗ, определяющая, является ли строка пустой. Если данная функция для конкретной строки возвращает 0, то эта строка пустая. Обратите внимание, что процедура просматривает строки снизу вверх и использует отрицательное значение шага в цикле For-Next. Это необходимо, поскольку при удалении все последующие строки перемещаются «вверх» в рабочем листе. Если бы в цикле просмотр выполнялся сверху вниз, то значение счетчика цикла после удаления строки оказалось бы неправильным.

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

Дублирование строк

Пример, рассматриваемый в этом разделе, демонстрирует использование возможностей VBA для создания дубликатов строк. На рис. 6 показан пример рабочего листа, используемого организаторами лотереи. В столбце А вводится имя. В столбце В содержится количество лотерейных билетов, приобретенных одним покупателем. В столбце С находится случайное число сгенерированное с помощью функции СЛЧИС. Победитель определяется путем сортировки данных в третьем столбце (выигрыш соответствует наибольшему случайному числу).

Рис. 6. Дублирование строк на основе значений в столбце В

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

Sub DupeRows() Dim cell As Range' 1-я ячейка, содержащая сведения о количестве билетов Set cell = Range("B2") Do While Not IsEmpty(cell) If cell > 1 Then Range(cell.Offset(1, 0), cell.Offset(cell.Value _ — 1,0)).EntireRow.Insert Range(cell, cell.Offset(cell.Value — 1, — 1)). _ EntireRow.FillDown End If Set cell = cell.Offset(cell.Value, 0) LoopEnd Sub

Объектная переменная cell была инициализирована ячейкой В2, первой ячейкой, в которой находится числовая величина. Вставка новых строк осуществляется в цикле, а их копирование происходит с помощью метода FillDown. Значение переменной cell увеличивается на единицу, после чего выбирается следующий участник лотереи, Цикл выполняется до тех пор, пока не встретится пустая ячейка. На рис. 7 показан рабочий лист после выполнения этой процедуры.

Рис. 7. В соответствии со значением в столбце В добавлены новые строки

Определение диапазона, находящегося в другом диапазоне

Функция InRange имеет два аргумента, оба — объекты Range. Функция возвращает значение True (Истина), если первый диапазон содержится во втором.

Function InRange(rng1, rng2) As Boolean‘ Возвращает True, если rng1 является подмножеством rng2 InRange = False If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then If rng1.Parent.Name = rng2.Parent.Name Then If Union(rng1, rng2).Address = rng2.Address Then InRange = True End If End If End IfEnd Function

Возможно, функция InRange кажется сложнее, чем того требует ситуация, поскольку в коде должна быть реализована проверка принадлежности двух диапазонов одной и той же книге и рабочему листу. Обратите внимание, что в процедуре используется свойство Parent, которое возвращает объект-контейнер заданного объекта. Например, следующее выражение возвращает название листа для объекта rng1:

rng1.Parent.Name

Следующее выражение возвращает название рабочей книги rng1:

rng1.Parent.Parent.Name

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

Определение типа данных ячейки

В состав Excel входит ряд встроенных функций, которые могут помочь определить тип данных, содержащихся в ячейке. Это функции ЕНЕТЕКСТ, ЕЛОГИЧ и ЕОШИБКА. Кроме того, VBA поддерживает функции IsEmpty, IsDate и IsNumeric.

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

Рис. 8. Функция CellType, возвращающая тип данных ячейки

Function CellType(Rng)' Возвращает тип ячейки, находящейся в левом верхнем углу диапазона Dim TheCell As Range Set TheCell = Rng.Range("A1") Select Case True Case IsEmpty(TheCell) CellType = "Пустая" Case TheCell.NumberFormat = "@" CellType = "Текст" Case Application.IsText(TheCell) CellType = "Текст" Case Application.IsLogical(TheCell) CellType = "Логический" Case Application.IsErr(TheCell) CellType = "Ошибка" Case IsDate(TheCell) CellType = "Дата" Case InStr(1, TheCell.Text, ":") <> 0 CellType = "Время" Case IsNumeric(TheCell) CellType = "Число" End SelectEnd Function

Обратите внимание на использование оператора SetTheCell. Функция CellType получает аргумент-диапазон произвольного размера, но этот оператор указывает, что функция оперирует только левой верхней ячейкой диапазона (представленной переменной TheCell).

[1] По материалам книги Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA. – М: Диалектика, 2013. – С. 325–342.

baguzin.ru

Работа с объектом Range (часть 2) - Макросы и программы VBA - Excel - Каталог статей

Продолжаем наш разговор про объект Excel Range, начатый в первой части. Разберём ещё несколько типовых задач и одну развлекательную. Кстати, в процессе написания второй части я дополнил и расширил первую, поэтому рекомендую её посмотреть ещё раз.

Перебор ячеек диапазона (вариант 4)

Для коллекции добавил четвёртый вариант перебора ячеек. Как видите, можно выбирать, как перебирается диапазон - по столбцам или по строкам. Обратите внимание на использование свойства коллекции Cells. Не путайте: свойство Cells рабочего листа содержит все ячейки листа, а свойство Cells диапазона (Range) содержит ячейки только этого диапазона. В данном случае мы получаем все ячейки столбца или строки.

Sub Handle_Cells_4_by_Columns(parRange As Range) For Each columnTemp In parRange.Columns For Each cellTemp In columnTemp.Cells Sum = Sum + cellTemp Next Next MsgBox "Сумма ячеек " & Sum End Sub Sub Handle_Cells_4_by_Rows(parRange As Range) For Each rowTemp In parRange.Rows For Each cellTemp In rowTemp.Cells Sum = Sum + cellTemp Next Next MsgBox "Сумма ячеек " & Sum End Sub

Должен вас предупредить, что код, который вы видите в этом цикле статей - это код, написанный для целей демонстрации работы с объектной моделью Excel. Тут нет объявлений переменных, обработки ошибок и проверки условий, так как я специально минимизирую программы, пытаясь акцентировать ваше внимание целиком на обсуждаемом предмете - объекте Range.

Работа с текущей областью

Excel умеет автоматически определять текущую область вокруг активной ячейки. Соответствующая команда на листе вызывается через Ctrl+A. Через ActiveCell мы посредством свойства Worksheet легко выходим на лист текущей ячейки, а уже через него можем эксплуатировать свойство UsedRange, которое и является ссылкой на Range текущей области. Чтобы понять, какой диапазон мы получили, мы меняем цвет ячеек. Функция GetRandomColor не является стандартной, она определена в модуле файла примера.

ActiveCell.Worksheet.UsedRange.Interior.Color = GetRandomColor

Определение границ текущей области

Демонстрируем определение левого верхнего и правого нижнего углов диапазона текущей области. С левым верхним углом всё просто, так как координаты этой ячейки всегда доступны через свойства Row и Column объекта Range (не путать с коллекциями Rows и Columns!). А вот для определения второго угла приходится использовать конструкцию вида .Rows(.Rows.Count).Row, где .Rows.Count - количество строк в диапазоне UsedRange, .Rows(.Rows.Count) - это мы получили последнюю строку, и уже для этого диапазона забираем из свойства Row координату строки. Со столбцом - по аналогии. Также обратите внимание на использование оператора With. Как видите, оператор With, помимо сокращения кода, также позволяет отказаться от объявления отдельной объектной переменной через оператор Set, что очень удобно.

With ActiveCell.Worksheet.UsedRange strTemp = "Верхняя строка " & vbTab & .Row & vbCr & _ "Нижняя строка " & vbTab & .Rows(.Rows.Count).Row & vbCr & _ "Левый столбец " & vbTab & .Column & vbCr & _ "Правый столбец " & vbTab & .Columns(.Columns.Count).Column End With MsgBox strTemp, vbInformation

Выделение столбцов / строк текущей области

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

With ActiveCell.Worksheet.UsedRange .Rows(1).Interior.Color = GetRandomColor .Rows(.Rows.Count).Interior.Color = GetRandomColor End With With ActiveCell.Worksheet.UsedRange .Columns(1).Interior.Color = GetRandomColor .Columns(.Columns.Count).Interior.Color = GetRandomColor End With

Сброс форматирования диапазона

Для возвращения диапазона к каноническому стерильному состоянию очень просто и удобно использовать свойство Style, и присвоить ему имя стиля "Normal". Интересно, что все остальные стандартные стили в локализованном офисе имеют русские имена, а у этого стиля оставили англоязычное имя, что неплохо.

ActiveCell.Worksheet.UsedRange.Style = "Normal"

Поиск последней строки столбца (вариант 1)

Range имеет 2 свойства EntireColumn и EntireRow, возвращающие столбцы / строки, на которых расположился ваш диапазон, но возвращают их ЦЕЛИКОМ. То есть, если вы настроили диапазон на D5, то Range("D5").EntireColumn вернёт вам ссылку на D:D, а EntireRow - на 5:5.

Идём далее - свойство End возвращает вам ближайшую ячейку в определенном направлении, стоящую на границе непрерывного диапазона с данными. Как это работает вы можете увидеть, нажимая на листе комбинации клавиш Ctrl+стрелки. Кстати, это одна из самых полезных горячих клавиш в Excel. Направление задаётся стандартными константами xlUp, xlDown, xlToRight, xlToLeft.

Классическая задача у Excel программиста - определить, где кончается таблица или, в данном случае, конкретный столбец. Идея состоит в том, чтобы встать на последнюю ячейку столбца (строка 1048576) и, стоя в этой ячейке, перейти по Ctrl+стрелка вверх (что на языке VBA - End(xlUp)).

With ActiveCell.EntireColumn .Cells(.Rows.Count, 1).End(xlUp).Select End With

Поиск последней строки столбца (вариант 2)

Ещё один вариант.

With ActiveCell.Worksheet .Cells(.Rows.Count, ActiveCell.Column).End(xlUp).Select End With

Поиск "последней" ячейки листа

Тут показывается, как найти на листе ячейку, ниже и правее которой находятся только пустые ячейки. Соответственно данные надо искать в диапазоне от A1 до этой ячейки. На эту ячейку можно перейти через Ctrl+End. Как этим воспользоваться в VBA показано ниже:

ActiveCell.Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Select

Разбор клипо-генератора

Ну, и в качестве развлечения и разрядки взгляните на код клипо-генератора, который генерирует цветные квадраты в заданных границах экрана. На некоторых это оказывает умиротворяющий эффект :)

По нашей теме в коде обращает на себя внимание использование свойства ReSize объекта Range. Как не трудно догадаться, свойство расширяет (усекает) текущий диапазон до указанных границ, при этом левый верхний угол диапазона сохраняет свои координаты. А также посмотрите на 2 последние строчки кода, реализующие очистку экрана. Там весьма показательно использован каскад свойств End и Offset.

Sub Indicator(start As Range) ' выясняем границы "экрана" (чёрная рамка с ячейками =1) LimitUp = start.End(xlUp).Row LimitDown = start.End(xlDown).Row LimitRight = start.End(xlToRight).Column LimitLeft = start.End(xlToLeft).Column ' бесконечный цикл, пока пользователь не нажал кнопку "Стоп" Do While Not PleaseStop ' размер клипа может меняться счётчиком, поэтому обновляем из ИД MinSize = Range("rngSize") iColor = GetRandomColor ' получаем случайный цвет для "клипа" ' верхний угол "клипа" iTop = LimitUp + Int(Rnd * (LimitDown - LimitUp - MinSize)) + 1 ' левый угол "клипа" iLeft = LimitLeft + Int(Rnd * (LimitRight - LimitLeft - MinSize)) + 1 ' выводим "клип" на экран, закрашивая фон диапазона ' размер диапазона - квадрат со стороной MinSize Cells(iTop, iLeft).Resize(MinSize, MinSize).Interior.Color = iColor ' Задержка между клипами зависит от MinSize, 5 - в миллисекундах Sleep 5 * MinSize DoEvents ' даём операционной системе и приложениям нормально функционировать Loop ' после завершения цикла переходим в верхний левый угол экрана start.End(xlUp).End(xlToLeft).Offset(1, 1).Select ' выделяем "экран" и очищаем его через изменния стиля ячеек Range(Selection, Selection.End(xlToRight).End(xlDown).Offset(-1, -1)).Style = "Normal" End Sub

perfect-excel.ru

UserForms_примеры

Excel включает 15 различных элементов управления, которые можно использовать на UserForms. Этот раздел содержит различные примеры использования этих элементов управления программными средствами. Примечание Код VBA, эта статья не содержит примеры, которые влияют на свойства и события для элементов управления. Если необходимо, можно использовать окно Свойства для просмотра списка свойств, доступных для элемента управления. Чтобы просмотреть список свойств, в меню Вид , щелкните Окно свойств.

Использование режима конструктора для редактирования элементов управления

При использовании редактора Visual Basic для создания диалогового окна в режиме конструктора. В режиме конструктора можно редактировать элементы управления, и можно изменить свойства элемента управления на пользовательской форме в окне «Свойства». Чтобы открыть окно Свойства, в меню Вид выберите пункт Окно свойств. Примечание Находясь в режиме конструктора элементов управления не отвечают на события. При запуске диалогового окна для отображения, пользователи видят его как программа находится в режиме выполнения. Когда UserForm выгружается из памяти не сохраняются изменения, внесенные в свойства элемента управления в режиме выполнения. Примечание Элементы управления реагировать на события в режиме выполнения.

Обращение к элементам управления на пользовательской форме

При обращении к элементам управления программными средствами зависит от типа листа модуля Visual Basic, где выполняется код. Если код выполняется из общего модуля, имеет следующий синтаксис:

UserFormName.Controlname.Property = Значение

Например если необходимо установить свойство Text элемента управления TextBox с именем TextBox1 на пользовательской форме с именем UserForm1 значение Bob, используйте следующий код:

UserForm1.TextBox1.Text = "Bob"

Если код в процедуре, которая инициируется событие элемента управления или UserForm, нет необходимости ссылаться на имя пользовательской формы. Вместо этого используйте следующий код:

TextBox1.Text = "Bob"

При присоединении к объекту код, код присоединен к одному из этого объекта события. Во многих примерах в данной статье добавьте код в событие Click элемента управления CommandButton объекта.

Элементы управления Label

Элементы управления Label в основном используются для описания других элементов управления на пользовательской форме. Элемент управления Label не может редактироваться пользователем во время выполнения пользовательской формы. Используйте свойство заголовка для установки или для возврата текста в элементе управления Label . Другие часто используемые свойства форматирования элемента управления Label включают свойства шрифта и цвет текста .

Как использовать оператор WITH для форматирования элемента управления Label

Чтобы использовать оператор WITH для изменения свойств элемента управления Label , выполните следующие действия.

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

Добавьте элемент управления CommandButton пользовательской формы.

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

В окне кода введите следующий код для события CommandButton1 Click :

Private Sub CommandButton1_Click()

With Label1

' Set the text of the label.

.Caption = "This is Label Example 1"

' Automatically size the label control.

.AutoSize = True

.WordWrap = False

' Set the font used by the Label control.

.Font.Name = "Times New Roman"

.Font.Size = 14

.Font.Bold = True

' Set the font color to blue.

.ForeColor = RGB(0, 0, 255)

End With

End Sub

В меню выполнить выберите команду Run Sub/UserForm.

Нажмите CommandButton.

Текст "это пример 1" появляется в элементе управления Label в полужирный шрифт Times New Roman с размером шрифта 14.

Элементы управления TextBox

Элементы управления TextBox часто используются для ввода данных пользователем. Свойство Text содержит записи, сделанные в элементе управления TextBox .

Использование элемента управления TextBox для проверки пароля

Если значение свойства PasswordChar элемента управления TextBox , становится управления «замаскированы edit». Символ, который указывается визуально заменяется каждый символ, который пользователь введет в элемент управления TextBox . Чтобы использовать элемент управления TextBox для проверки пароля, выполните следующие действия.

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

В меню Вид выберите команду Свойства для отображения окна «Свойства».

Введите в поле свойства PasswordChar элемента управления TextBox *. Примечание Изменения значения звездочку.

Добавьте элемент управления CommandButton пользовательской формы.

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

В окне кода введите следующий код для события CommandButton1 Click :

Private Sub CommandButton1_Click()

If TextBox1.Text <> "userform" Then

MsgBox "Password is Incorrect. Please reenter."

TextBox1.Text = ""

TextBox1.SetFocus

Else

MsgBox "Welcome!"

Unload Me

End If

End Sub

В меню выполнить выберите команду Run Sub/UserForm.

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

Щелкните элемент управления CommandButton.

Например пароль — «userform». При вводе неверного пароля, появляется окно сообщения, которое указывает, что указан неверный пароль установлен элемент управления TextBox и затем повторно введите пароль. При вводе правильного пароля появится приветственное сообщение и Пользовательская форма закрывается. Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт:

213555

(http://support.microsoft.com/kb/213555/)

XL2000: Отсутствует свойство проверки данных для UserForm текстовые поля

Элементы управления CommandButton

Для запуска процедуры VBA можно использовать элемент управления CommandButton . Процедуры VBA обычно присоединяется к событию Click элемента управления CommandButton . Чтобы использовать элемент управления CommandButton , процедуры, запускаемую при возникновения события Click , выполните следующие действия.

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

Добавьте элемент управления CommandButton пользовательской формы.

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

В окне кода введите следующий код:

Private Sub CommandButton1_Click()

red = Int(Rnd * 255)

green = Int(Rnd * 255)

blue = Int(Rnd * 255)

CommandButton1.BackColor = RGB(red, green, blue)

End Sub

В меню выполнить выберите команду Run Sub/UserForm.

Цвет фона элемента управления CommandButton1 изменяется каждый раз, щелкните его. Дополнительные сведения об элементе управления CommandButton щелкните следующие номера статей базы знаний Майкрософт:

213572

(http://support.microsoft.com/kb/213572/)

XL2000: Нажать кнопку "Отмена" не может выйти из пользовательской формы

213743

(http://support.microsoft.com/kb/213743/)

XL2000: Как установить по умолчанию кнопке на пользовательской форме

Элементы управления ListBox

Элемент управления ListBox предназначен для предоставить пользователю список элементов для выбора. Можно хранить список элементов для элемента управления ListBox на листе Excel. Чтобы заполнить элемент управления ListBox с диапазона ячеек на листе, используйте свойство Источник строк . При использовании свойства MultiSelect , можно настроить элемент управления ListBox принять множественный выбор.

Получение текущего выделенного элемента из элемента управления ListBox

Свойство Value элемента управления ListBox используется для возврата текущего выделенного элемента. Для возврата текущего выделенного элемента в одиночный выбор элемента управления ListBox , выполните следующие действия.

Запустите Excel и откройте новую книгу.

В ячейках ячейки a1 Dan: a5 на листе Sheet1 введите значения, которые требуется использовать для заполнения элемента управления ListBox .

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

Дважды щелкните элемент управления ListBox для отображения окна кода для элемента управления ListBox .

В окне кода введите следующий код для события ListBox1 Click :

Private Sub ListBox1_Click()

MsgBox ListBox1.Value

End Sub

В меню выполнить выберите команду Run Sub/UserForm.

При выборе элемента в списке появится сообщение с текущего выбранного элемента.

Получение выбранных элементов в нескольких выбора элемента управления ListBox

Для определения элементов, выбранных в выделить несколько элементов управления ListBox , необходимо перебрать все элементы в списке и затем запросить свойство Selected . Для возвращения элементов, выделенных в выделить несколько элементов управления ListBox , выполните следующие действия.

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

В меню Вид выберите команду Свойства , чтобы открыть окно свойств.

Введите значения, которые определены для следующих свойств элемента управления ListBox :

Property Value

----------- -----------------------

MultiSelect 1 - frmMultiSelectMulti

RowSource Sheet1!A1:A8

Добавьте элемент управления CommandButton пользовательской формы.

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

В окне кода введите следующий код для события CommandButton1 Click :

Sub CommandButton1_Click ()

' Loop through the items in the ListBox.

For x = 0 to ListBox1.ListCount - 1

' If the item is selected...

If ListBox1.Selected(x) = True Then

' display the Selected item.

MsgBox ListBox1.List(x)

End If

Next x

End Sub

В меню выполнить выберите команду Run Sub/UserForm.

Выберите один или несколько элементов в списке.

Нажмите кнопку CommandButton1.

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

Как с помощью свойства Источник строк для заполнения элемента управления ListBox ячейками на листе

Использование свойства Источник строк для заполнения элемента управления ListBox из диапазона ячеек на листе, выполните следующие действия.

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

Добавьте элемент управления CommandButton пользовательской формы.

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

В окне кода введите следующий код для события CommandButton1 Click :

Private Sub CommandButton1_Click()

ListBox1.RowSource = "=Sheet1!A1:A5"

End Sub

В меню выполнить выберите команду Run Sub/UserForm. ПримечаниеListBox1 не содержит никаких значений.

Нажмите кнопку CommandButton1.

Значения в ячейки a1: a5 ячеек на листе Sheet1 заполняется ListBox1 .

Как заполнить элемент управления ListBox значениями в массиве

В этом примере показано, как заполнить элемент управления ListBox с переменной массива. Необходимо назначить значения из массива один элемент управления ListBox одновременно. Как правило этот процесс требует использования циклические структуры, например для...Далее цикла. Чтобы заполнить элемент управления ListBox с переменной массива, выполните следующие действия.

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

В меню Вставка выберите пункт модуль для вставки в лист модуля.

В окне кода введите следующий код:

Sub PopulateListBox()

Dim MyArray As Variant

Dim Ctr As Integer

MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")

For Ctr = LBound(MyArray) To UBound(MyArray)

UserForm1.ListBox1.AddItem MyArray(Ctr)

UserForm1.Show

End Sub

В меню Сервис пункт макросы, нажмите кнопку PopulateListBoxи выберите команду выполнить.

PopulateListBox процедура построения простого массива и затем добавляет элементы в массиве элемент управления ListBox с помощью метода AddItem . После этого появится пользовательской формы.

Как использовать горизонтальный диапазон ячеек на листе для заполнения элемента управления ListBox

Если значение свойства Источник строк элемента управления ListBox в горизонтальный диапазон ячеек, только первое значение отображается в элементе управления ListBox . Чтобы заполнить элемент управления ListBox в горизонтальный диапазон ячеек с помощью метода AddItem , выполните следующие действия.

Запустите Excel и откройте новую книгу.

В A1:E1 ячеек на листе Sheet1 введите значения, которые требуется использовать для заполнения элемента управления ListBox .

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

В меню Вставка выберите пункт модуль для вставки в лист модуля.

В окне кода введите следующий код:

Sub PopulateListWithHorizontalRange()

For Each x In Sheet1.Range("A1:E1")

UserForm1.ListBox1.AddItem x.Value

UserForm1.Show

End Sub

В меню Сервис пункт макросы, нажмите кнопку PopulateListWithHorizontalRangeи выберите команду выполнить.

Процедура макрос просматривает ячейки a1: E5 на листе Sheet1, добавления значений в ListBox1 одно одновременно. ПримечаниеНа листе Лист1 ListBox1 не привязан к ячейки a1: E5.

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

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

Запустите Excel и откройте новую книгу.

В ячейках, которые указаны на листе Лист1 введите следующие данные:

Свернуть эту таблицуРазвернуть эту таблицу

A1: год	B1: область	C1: продажи
A2: 1996	B2: Северная	C2: 140
A3: 1996	B3: Южная	C3: 210
A4: 1997	B4: Северная	C4: 190
A5: 1997	B5: Южная	C5: 195

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

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

Щелкните списокправой кнопкой мыши и выберите команду Свойства.

Введите или выберите значения, которые определены для следующих свойств элемента управления ListBox , перечисленные в следующей таблице:

Property Value

----------------------------

BoundColumn 1

ColumnCount 3

ColumnHeads True

RowSource Sheet1!A2:A5

Дважды щелкните элемент управления ListBox для отображения окна кода для элемента управления ListBox .

В окне кода введите следующий код:

Private Sub ListBox1_Change()

Dim SourceData As Range

Dim Val1 As String, Val2 As String, Val3 As String

Set SourceRange = Range(ListBox1.RowSource)

Val1 = ListBox1.Value

Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value

Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value

Label1.Caption = Val1 & " " & Val2 & " " & Val3

End Sub

В меню выполнить выберите команду Run Sub/UserForm.

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

Удаление всех элементов из элемента управления ListBox, который связан с листом

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

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

Щелкните правой кнопкой мыши элемент управления ListBox и нажмите кнопку Свойства.

В свойстве Источник строк введите Лист1!Ячейки a1: a5.

Добавьте элемент управления CommandButton пользовательской формы.

Дважды щелкните элемент управления CommandButton , чтобы открыть окно кода для элемента управления CommandButton .

В окне кода введите следующий код для события CommandButton1 Click :

Private Sub CommandButton1_Click()

ListBox1.RowSource = ""

End Sub

В меню выполнить выберите команду Run Sub/UserForm. Элемент управления ListBox, добавленного в пользовательской форме заполняется значениями, введенными на листе Sheet1.

Нажмите кнопку CommandButton1.

Все элементы удаляются из ListBox1.

Удаление всех элементов из элемента управления ListBox, который не связан с листом

Нет одной команды VBA, удаляет все элементы из элемента управления ListBox , если список не связана с листа. Чтобы удалить все элементы из элемента управления ListBox , который заполняется из массива Visual Basic, выполните следующие действия.

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

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

В меню Вставка выберите пункт модуль для вставки в лист модуля.

В окне кода введите следующий код:

Sub PopulateListBox()

Dim MyArray As Variant

Dim Ctr As Integer

MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")

For Ctr = LBound(MyArray) To UBound(MyArray)

UserForm1.ListBox1.AddItem MyArray(Ctr)

UserForm1.Show

End Sub

Добавьте элемент управления CommandButton пользовательской формы.

Дважды щелкните элемент управления CommandButton , чтобы открыть окно кода для элемента управления CommandButton .

В окне кода введите следующий код для события CommandButton1 Click :

Private Sub CommandButton1_Click()

For i = 1 To ListBox1.ListCount

ListBox1.RemoveItem 0

Next I

End Sub

В меню Сервис пункт макросы, нажмите кнопку PopulateListBoxи выберите команду выполнить. Заполнение элемента управления ListBox , а затем появится пользовательской формы.

Нажмите кнопку CommandButton1.

Все элементы удаляются из ListBox1. Дополнительные сведения об элементе управления ListBox щелкните следующие номера статей базы знаний Майкрософт:

161598

(http://support.microsoft.com/kb/161598/)

OFF: Как добавить данные в элемент управления ComboBox или ListBox в Excel или Word

211446

(http://support.microsoft.com/kb/211446/)

XL2000: TextColumn свойство отображает только первый столбец

211896

(http://support.microsoft.com/kb/211896/)

XL2000: Как имитировать пользовательские формы, а сочетание управления списком

211899

(http://support.microsoft.com/kb/211899/)

XL2000: Проблемы с установкой заголовки столбцов в элементе управления ListBox

213721

(http://support.microsoft.com/kb/213721/)

XL2000: Как удалить все элементы из списка или поля со списком

213722

(http://support.microsoft.com/kb/213722/)

XL2000: Как с помощью свойства TextColumn

213723

(http://support.microsoft.com/kb/213723/)

XL2000: Как возвращаемые значения из списка, отображающего несколько столбцов

213746

(http://support.microsoft.com/kb/213746/)

XL2000: Как заполнить элемент управления списком нескольких диапазонов

213748

(http://support.microsoft.com/kb/213748/)

XL2000: Как для заполнения одного списка на основании другого списка

213752

(http://support.microsoft.com/kb/213752/)

XL2000: Использование метода AddItem вызывает ошибку, когда RowSource привязан к данным

213756

(http://support.microsoft.com/kb/213756/)

XL2000: Используя метод RemoveItem с элементом управления ListBox или ComboBox

213759

(http://support.microsoft.com/kb/213759/)

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

Элементы управления ComboBox

Элемент управления ComboBox можно использовать раскрывающийся список или поле со списком, где можно выбрать значение в списке или введите новое значение. Свойство Style определяет, если элемент управления ComboBox действует как раскрывающийся список или поле со списком. Примечание В примерах выше для элемента управления ListBox могут также применяться к элементу управления ComboBox , за исключением в примере «Как получить выделить элемент управления ListBox несколько выделенных элементов».

Как добавить новый элемент списка, если элемент управления ComboBox не связана с листом

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

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

Добавьте элемент управления поля со списком UserForm.

В меню Вставка выберите пункт модуль для вставки в лист модуля.

В окне кода введите следующий код:

Sub PopulateComboBox()

Dim MyArray As Variant

Dim Ctr As Integer

MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")

For Ctr = LBound(MyArray) To Ubound(MyArray)

UserForm1.ComboBox1.AddItem MyArray(Ctr)

UserForm1.Show

End Sub

Добавьте элемент управления CommandButton пользовательской формы.

Дважды щелкните элемент управления CommandButton , чтобы открыть окно кода для элемента управления CommandButton .

В окне кода введите следующий код для события CommandButton1 Click :

Private Sub CommandButton1_Click()

Dim listvar As Variant

listvar = ComboBox1.List

On Error Resume Next

' If the item is not found in the list...

If IsError(WorksheetFunction.Match(ComboBox1.Value, listvar, 0)) Then

' add the new value to the list.

ComboBox1.AddItem ComboBox1.Value

End If

End Sub

В меню Сервис пункт макросы, нажмите кнопку PopulateListBoxи выберите команду выполнить. Заполнение элемента управления ComboBox , а затем появится пользовательской формы.

В элементе управления ComboBox введите Mangoes (или любое значение, которое еще нет в списке).

Нажмите кнопку CommandButton1.

Теперь новое значение отобразится в конце списка.

Как добавить новый элемент списка, если элемент управления ComboBox связан с листом

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

Запустите Excel и откройте новую книгу.

В ячейках ячейки a1 Dan: a5 на листе Sheet1 введите значения, которые требуется использовать для заполнения элемента управления ComboBox .

Выделите ячейки a1: a5 ячеек на листе Sheet1.

В меню Вставка выберите пункт Имя и нажмите кнопку Определить. В поле имя введите ListRange и нажмите кнопку ОК. Это создает определенное имя ListRange. Определенное имя ListRange можно использовать для привязки элемента управления ComboBox свойства Источник строк листа.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

Добавьте элемент управления поля со списком UserForm.

В окне Свойства для ComboBox1тип Лист1!ListRange как свойства Источник строк .

Добавьте элемент управления CommandButton пользовательской формы.

Дважды щелкните элемент управления CommandButton , чтобы открыть окно кода для элемента управления CommandButton .

В окне кода введите следующий код для события CommandButton1 Click :

Private Sub CommandButton1_Click()

Dim SourceData As Range

Dim found As Object

Set SourceData = Range("ListRange")

Set found = Nothing

' Try to find the value on the worksheet.

Set found = SourceData.Find(ComboBox1.Value)

' If the item is not found in the list...

If found Is Nothing Then

' redefine ListRange.

SourceData.Resize(SourceData.Rows.Count + 1, 1).Name = "ListRange"

' Add the new item to the end of the list on the worksheet.

SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value _

= ComboBox1.Value

' Reset the list displayed in the ComboBox.

ComboBox1.RowSource = Range("listrange").Address(external:=True)

End If

End Sub

В меню выполнить выберите команду Run Sub/UserForm. Пользовательская форма появится на листе Sheet1.

В элементе управления ComboBox введите значение, которое уже не находится в списке.

Нажмите кнопку CommandButton1.

В список добавляется новый элемент, введенный в элемент управления ComboBox , а список, к которому привязан элемент управления ComboBox расширяется для включения ячейки a1: a6.

Отображение списка элемента управления ComboBox при отображении пользовательской формы

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

Запустите Excel и откройте новую книгу.

В меню Сервис выберите пункт Макрос и нажмите кнопку Редактор Visual Basic.

В меню Вставка выберите команду UserForm UserForm вставить в книгу.

Добавьте элемент управления поля со списком UserForm.

В окне Свойства для ComboBox1тип Лист1!Ячейки a1: a5 как свойства Источник строк .

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

В окне кода введите следующий код для события CommandButton Click :

Private Sub UserForm_Activate()

ComboBox1.DropDown

End Sub

В меню выполнить выберите команду Run Sub/UserForm.

studfiles.net