Настройка окна vba excel

Обновлено: 04.07.2024

Что-то я никак не соображу. Подскажите плиз. При вызове формы надо свернуть окно Excel, но чтобы форма осталась видна.
.visible=false не катит
При
[vba]

[/vba]
сворачивает и окно Ecxel и форму. Причем не важно в какой последовательности выполнить WindowState и UserForm1.Show ИЛИ WindowState засунуть в UserForm_Initialize.

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

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

Сейчас обнаружил что на разных компах (офис один и тот же, и винда хр у всех) ведет себя по разному. На одних форма остается видна на других сворачивается вместе с окном. Автор - devilkurs
Дата добавления - 18.02.2016 в 13:10

devilkurs, ~~можно сначала свернуть окно, а потом форму показать:~~

ЯД: 410013299366744 WM: R193491431804

не увидела Автор - Manyasha
Дата добавления - 18.02.2016 в 13:20 Посмотрите вот так.
Ваш родной файл у меня не работал, а вот если сделать форму немодальной, то заработал. Свойство ShowModal Посмотрите вот так.
Ваш родной файл у меня не работал, а вот если сделать форму немодальной, то заработал. Свойство ShowModal _Boroda_

Грандиозно! ))))))))))))))) Про свойство ShowModal совсем забыл. На других компах тоже норм начало отображать форму.

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

Спасибо Александр. И Марине за участие )))))

Грандиозно! ))))))))))))))) Про свойство ShowModal совсем забыл. На других компах тоже норм начало отображать форму.

Спасибо Александр. И Марине за участие ))))) devilkurs

Спасибо Александр. И Марине за участие ))))) Автор - devilkurs
Дата добавления - 18.02.2016 в 13:49

В этой заметке описываются методы создания пользовательских диалоговых окон, которые существенно расширяют стандартные возможности Excel. Диалоговые окна – это наиболее важный элемент пользовательского интерфейса в Windows. Они применяются практически в каждом приложении Windows, и большинство пользователей неплохо представляет, как они работают. Разработчики Excel создают пользовательские диалоговые окна с помощью пользовательских форм (UserForm). Кроме того, в VBA имеются средства, обеспечивающие создание типовых диалоговых окон.[1]

Рис. 1. Работа процедуры GetName

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

Использование окон ввода данных

Окно ввода данных — это простое диалоговое окно, которое позволяет пользователю ввести одно значение. Например, можно применить окно ввода данных, чтобы предоставить пользователю возможность ввести текст, число или диапазон значений. Для создания окна ввода предназначены две функции InputBox: одна— в VBA, а вторая является методом объекта Application.

Функция InputBox в VBA

Функция имеет следующий синтаксис:

InputBox(запрос [, заголовок] [, по_умолчанию] [, xpos] [, ypos] [, справка, раздел])

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

Функция InputBox запрашивает у пользователя одно значение. Она всегда возвращает строку, поэтому результат нужно будет преобразовать в числовое значение. Текст, отображаемый в окне ввода, может достигать 1024 символов (длину допускается изменять в зависимости от ширины используемых символов). Если определить раздел справочной системы, то в диалоговом окне будет отображена кнопка Справка.

Обратите внимание: функция InputBox вызывается в цикле Do Until. Это позволяет убедиться в том, что данные введены в окно. Если пользователь щелкнет на кнопке Отмена или не введет текст, то переменная UserName будет содержать пустую строку, а окно ввода данных появится повторно. Далее в процедуре будет предпринята попытка получить имя пользователя путем поиска первого символа пробела (для этого применяется функция InStr). Таким образом, можно воспользоваться функцией Left для получения всех символов, расположенных слева от символа пробела. Если символ пробела не найден, то используется все введенное имя.

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

В процедуре GetWord пользователю предлагается ввести пропущенное слово (рис. 2). Этот пример также иллюстрирует применение именованных аргументов (р и t). Текст запроса выбирается из ячейки А1 рабочего листа.

Рис. 2. Использование функции VBA inputBox, отображающей запрос

Метод Excel InputBox

Метод Excel InputBox по сравнению с функцией VBA InputBox предоставляет три преимущества:

возможность задать тип возвращаемого значения;
возможность указать диапазон листа путем выделения с помощью мыши;
автоматическая проверка правильности введенных данных.

Метод InputBox имеет следующий синтаксис.

InputBox(запрос, [, заголовок], [, по_умолчанию], [, слева], [, сверху], [, справка, раздел], [, тип])

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

Рис. 3. Коды типов данных, возвращаемые методом Excel InputBox

Используя сумму приведенных выше значений, можно возвратить несколько типов данных. Например, для отображения окна ввода, которое принимает текстовый или числовой тип данных, установите код равным 3 (1 + 2 или число + текст). Если в качестве кода типа данных применить значение 8, то пользователь сможет ввести в поле адрес ячейки или диапазона ячеек. Пользователь также можент выбрать диапазон на текущем рабочем листе.

В процедуре EraseRange используется метод InputBox. Пользователь может указать удаляемый диапазон (рис. 4). Адрес диапазона вводится в окно вручную, или выделяется мышью на листе. Метод InputBox с кодом 8 возвращает объект Range (обратите внимание на ключевое слово Set). После этого выбранный диапазон очищается (с помощью метода Clear). По умолчанию в поле окна ввода отображается адрес текущей выделенной ячейки. Если в окне ввода щелкнуть на кнопке Отмена, то оператор On Error завершит процедуру.

Рис. 4. Пример использования метода InputBox для выбора диапазона

Рис. 5. Метод InputBox автоматически проверяет вводимые данные

Функция VBA MsgBox

MsgBox(запрос[, кнопки][, заголовок][, справка, раздел])

Рис. 6. Константы и значения, используемые для выбора кнопок в функции MsgBox

Рис. 7. Константы, возвращаемые MsgBox

Функция MsgBox возвращает переменную, имеющую тип Integer. Вам необязательно использовать переменную для хранения результата выполнения функции MsgBox. Следующая процедура представляет собой вариацию процедуры GetAnswer.

В файле VBA msgbox.xlsm функция ContinueProcedure в демонстрационных целях представлена в виде процедуры. Функция ContinueProcedure может вызываться из другой процедуры. Например, оператор

If Not ContinueProcedure() Then Exit Sub

Метод Excel GetOpenFilename

Если приложению необходимо получить от пользователя имя файла, то можно воспользоваться функцией InputBox, но этот подход часто приводит к возникновению ошибок. Более надежным считается использование метода GetOpenFilename объекта Application, который позволяет удостовериться, что приложение получило корректное имя файла (а также его полный путь). Данный метод позволяет отобразить стандартное диалоговое окно Открытие документа, но при этом указанный файл не открывается. Вместо этого метод возвращает строку, которая содержит путь и имя файла, выбранные пользователем. По окончании данного процесса с именем файла можно делать все что угодно. Синтаксис (все параметры необязательные):

Application.GetOpenFilename(фильтр_файла, индекс_фильтра, заголовок, множественный_выбор)

Фильтр_файла. Содержит строку, определяющую критерий фильтрации файлов (необязательный параметр).
Индекс_фильтра. Указывает индексный номер того критерия фильтрации файлов, который используется по умолчанию (необязательный параметр).
Заголовок. Содержит заголовок диалогового окна (необязательный параметр). Если этот параметр не указать, то будет использован заголовок Открытие документа.
Множественный_выбор. Необязательный параметр. Если он имеет значение ИСТИНА, можно выбрать несколько имен файлов. Имя каждого файла заносится в массив. По умолчанию данный параметр имеет значение ЛОЖЬ.

Аргумент Фильтр_файла определяет содержимое раскрывающегося списка Тип файлов, находящегося в окне Открытие документа. Аргумент состоит из строки, определяющей отображаемое значение, а также строки действительной спецификации типа файлов, в которой находятся групповые символы. Оба элемента аргумента разделены запятыми. Если этот аргумент не указывать, то будет использовано значение, заданное по умолчанию: " Все файлы (*.*),*.* " . Первая часть строки Все файлы (*.*) – то текст, отображаемый в раскрывающемся списке тип файлов. Вторая часть строки *.* указывает тип отображаемых файлов.

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

В следующем примере у пользователя запрашивается имя файла. При этом в поле типа файлов используются пять фильтров (код содержится в файле prompt for file.xlsm).

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

Рис. 11. Метод GetOpenFilename отображает диалоговое окно, в котором выбирается файл

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

Метод Excel GetSaveAsFilename

Данный метод отображает диалоговое окно Сохранение документа и дает пользователю возможность выбрать (или указать) имя сохраняемого файла. В результате возвращается имя файла, но никакие действия не предпринимаются. Синтаксис (все параметры необязательные):

Application.GetSaveAsFilename(начальное_имя, фильтр_файла, индекс_фильтра, заголовок, текст_кнопки)

Начальное_имя. Указывает предполагаемое имя файла.
Фильтр_файла. Содержит критерий фильтрации отображаемых в окне файлов.
Индекс_фильтра. Код критерия фильтрации файлов, который используется по умолчанию.
Заголовок. Определяет текст заголовка диалогового окна.

Получение имени папки

With Application . FileDialog ( msoFileDialogFolderPicker )

Объект FileDialog позволяет определить начальную папку путем указания значения свойства InitialFileName. В примере в качестве начальной папки применяется путь к файлам Excel, заданный по умолчанию.

Отображение диалоговых окон Excel

Создаваемый вами код VBA может вызывать на выполнение многие команды Excel, находящиеся на ленте. И если в результате выполнения команды открывается диалоговое окно, ваш код может делать выбор в диалоговом окне (даже если само диалоговое окно не отображается). Например, следующая инструкция VBA эквивалентна выбору команды Главная –> Редактирование –> Найти и выделить –> Перейти и указанию диапазона ячеек А1:СЗ с последующим щелчком на кнопке ОК. Но само диалоговое окно Переход при этом не отображается (именно это и нужно).

Application.Goto Reference:=Range( " А1:СЗ " )

Иногда же приходится отображать встроенные окна Excel, чтобы пользователь мог сделать свой выбор. Для этого используется коллекция Dialogs объекта Application. Учтите, что в настоящее время компания Microsoft прекратила поддержу этого свойства. В предыдущих версиях Excel пользовательские меню и панели инструментов создавались с помощью объекта CommandBar. В версиях Excel 2007 и Excel 2010 этот объект по-прежнему доступен, хотя и работает не так, как раньше. Начиная с версии Excel 2007 возможности объекта CommandBar были существенно расширены. В частности, объект CommandBar можно использовать для вызова команд ленты с помощью VBA. Многие из команд, доступ к которым открывается с помощью ленты, отображают диалоговое окно. Например, следующая инструкция отображает диалоговое окно Вывод на экран скрытого листа (рис. 12; см. также файл ribbon control names.xlsm):

Рис. 12. Диалоговое окно, отображаемое в результате выполнения указанного выше оператора

Метод ExecuteMso принимает лишь один аргумент, idMso, который представляет элемент управления ленты. К сожалению, сведения о многих параметрах в справочной системе отсутствуют.

В результате выполнения следующего оператора отображается вкладка Шрифт диалогового окна Формат ячеек:

На самом деле пользоваться объектами CommandBar не стоит, поскольку вряд ли они будут поддерживаться в будущих версиях Excel.

Отображение формы ввода данных

Многие пользователи применяют Excel для управления списками, информация в которых ранжирована по строкам. В Excel поддерживается простой способ работы с подобными типами данных с помощью встроенных форм ввода данных, которые могут создаваться автоматически. Подобная форма предназначена для работы как с обычным диапазоном, так и с диапазоном, оформленным в виде таблицы (с помощью команды Вставка –> Таблицы –> Таблица). Пример формы ввода данных показан на рис. 13 (см. также файл data form example.xlsm).

Рис. 13. Некоторые пользователи предпочитают применять встроенные формы ввода данных Excel для ввода сведений; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

Щелкните правой кнопкой мыши на панели быстрого доступа и в контекстном меню выберите параметр Настройка панели быстрого доступа.
На экране появится вкладка Панель быстрого доступа диалогового окна Параметры Excel.
В раскрывающемся списке Выбрать команды из выберите параметр Команды не на ленте.
В появившемся списке выберите параметр Форма.
Щелкните на кнопке Добавить для добавления выбранной команды на панель быстрого доступа.
Щелкните на кнопке ОК для закрытия диалогового окна Параметры Excel.

После выполнения перечисленных выше действий на панели быстрого доступа появится новый значок.

Для работы с формой ввода данных следует структурировать данные таким образом, чтобы Excel распознавал их в виде таблицы. Начните с указания заголовков столбцов в первой строке диапазона вводимых данных. Выделите любую ячейку в таблице и щелкните на кнопке Форма панели быстрого доступа. Excel отображает диалоговое окно, в котором будут вводиться данные. Для перемещения между текстовыми полями в целях ввода информации используйте клавишу <Tab>. Если ячейка содержит формулу, результат вычислений отображается в виде текста (а не в формате поля ввода данных). Другими словами, невозможно изменить формулы с помощью формы ввода данных.

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

Используйте метод ShowDataForm для отображения формы ввода данных Excel. Единственное требование заключается в том, что активная ячейка должна находиться в диапазоне. Следующий код активизирует ячейку А1 (в таблице), а затем отображает форму ввода данных.

Столкнулся с довольно нестандартной проблемой выполнения процедуры запуска обновления всех запросов в рабочей книге при открытии. Excel 2016 запускается с удалённого сервера Citrix для работы с надстройкой SAP Analysis Office, при помощи которой в рабочей книге обновляются определённые данные и выгружаются на лист. Далее необходимо создать из них запрос в Power Query, обработать и загрузить в модель данных для построения сводных отчётов, однако при нажатии "Обновить всё" Excel выдаёт модальное окно с ошибкой "microsoft.mashup.oledb.1 provider is not registered on the local machine".

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

При открытии рабочей книги выполнение процедуры вызывает следующую ошибку:

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

То есть получил странную ситуацию — код рабочий, должен решать проблему, но выполняется он только непосредственно из окна VBA. Была мысль, что открытие окна VBA тоже выполняет нужную инициализацию, пробовал процедуру, которая открывает и закрывает VBA IDE, но безуспешно, та же ошибка.

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

При открытии рабочей книги выполнение процедуры вызывает следующую ошибку:

Можете сразу выставить такие настройки, ниже описывается подробно каждая опция. Основное внимание обращаем на первые 2 параметра, остальное можно оставить по умолчанию.

Параметр Auto Syntax Check

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

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

Параметр Require Variable Declaration

Я сторонник обязательного декларирования переменных и указания их типов. Во включенном состоянии редактор кода будет включать оператор Option Explicit в создаваемые модули/классы и формы. Это означает, что перед тем как использовать переменную вам потребуется ее явно объявить.

Если явно не объявлять переменные, то они имеют тип Variant (используют больше памяти и работают несколько дольше). В целом явное декларирование переменных - хорошая привычка, которую стоит выработать. Это помогает избежать многих проблем при написании кода и его сопровождения.

Параметр Auto List Members

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

Проверить его в действие можно введя, например, текст "Application."

Вы сможете стрелками вверх вниз выбирать нужный объект и клавишей Tab вставлять в код без ошибок.

Параметр Auto Quick Info

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

Параметр Auto Data Tips

Включайте этот параметр он будет отображать значения свойств и методов при наведении на них в режиме отладки кода. Вот так:

Параметр Auto Indent

Параметр отвечает за автоматический отступ текста. Во включенном состоянии при переходе на новую строку будет использоваться тот же отступ, что и для предыдущей строки. Да, конечно не Visual Studio с автоматическим отступом в зависимости от вложенности, но тоже какой никакой плюс.

Да, отступы естественно работают только в том случае если вы используете Tab, а не пробелы. Значение Tab width - ширина одного отступа. Если работаете над кодом совместно, то выставите одинаковое значение на всех компьютерах, чтобы код был единообразным.

Параметр Drag-and-Drop Text Editing

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

Параметр Default to Full Module View

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

Параметр Procrdure Separator

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

Вкладка General

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

Параметр Error Trapping

Опция отвечает за перехват ошибок. В основном потребуется для продвинутых разработчиков, которые пишут не только модули, но также создают классы и пытаются писать в парадигме объектно-ориентированного программирования.

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

Если установить опцию Break in Class Module то в случае возникновения ошибки в классе, редактор остановится на этой строке кода и вы сможете быстрой найти причину ошибки.

Form Grid Settings

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

Прочие настройки

Show ToolTips - отображает подсказки меню редактора кода при наведении на них мышью. Отключать нет смысла.

Collapse Proj. Hides Windows - Скрывает окна проекта при сворачивании. Тоже оставляйте включенной.

Edit and Continue - уведомляет в случае если переменные теряют установленные значения. Особой полезности в этой функции не заметил, смысла включать нет.

Compile - Советую оставить включенной. Автоматическая компиляция кода происходит практически мгновенно. Проверено на проектах с несколькими десятками тысяч строк кода.

Другие вкладки

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

Вкладка Docking особого интереса не представляет так как отвечает за прикрепление панелей. Там лучше все оставлять по умолчанию.

Читайте также: