Vba excel сохранение переменной
Часто при работе с файлами и написании кодов начинающие "кодить" в VBA сталкиваются с необходимостью предоставить пользователю возможность самостоятельного выбора файлов: либо всех в указанной папке, либо каких-то отдельных. Конечно, можно жестко в коде написать нечто вроде: "C:\Documents\Files\Книга1.xls" , но это требует не только наличия именно диска С, но и полной структуры папок и имен файлов. Это очень неудобно в большинстве случаев и куда чаще необходимо дать пользователю возможность самому указать имя файла. Записывать в ячейку листа полный путь и имя весьма непрактично и часто для неискушенного пользователя вызывает только "отторжение" от программы. В статье Просмотреть все файлы в папке я приводил пример кода, который просматривает все файлы в указанной папке и папка при этом выбирается сами пользователем из привычного по работе с Windows диалога. Там используется диалог выбора папок. Именно на этом я и хочу сделать акцент в этой статье - рассказать про некоторые способы вызова подобных диалогов для выбора файлов или папки. Так же обращу внимание на некоторые вещи, которые следует учитывать при использовании того или иного типа диалогов.
Диалог выбора файлов Applicaton.GetOpenFileName
Параметры:
Application.GetOpenFilename([FileFilter], [FilterIndex], [Title], [ButtonText], [MultiSelect])
По сути я часто использую именно его, т.к. это универсальный метод и в нем есть все, что лично мне необходимо: выбрать определенные типы файлов позволяет, возможность запрета выбора нескольких файлов сразу есть.
avFiles = Application.GetOpenFilename _ ("Excel files(*.xls*),*.xls*,Text files(*.txt),*.txt", 2, _ "Выбрать текстовые или Excel файлы", , True)
- True - можно будет выбрать более одного файла для обработки(через Shift или Ctrl или простым выделением мышью внутри окна)
- False - можно будет выбрать только один файл
По умолчанию принимает значение False
Выбора только одного файла:
avFiles = Application.GetOpenFilename _ ("Excel files(*.xls*),*.xls*,Text files(*.txt),*.txt", 2, _ "Выбрать текстовые или Excel файлы", , False)
Выбор нескольких файлов:
avFiles = Application.GetOpenFilename _ ("Excel files(*.xls*),*.xls*,Text files(*.txt),*.txt", 2, _ "Выбрать текстовые или Excel файлы", , True)
Пример применения диалога Application.GetOpenFilename
Sub ShowGetOpenDialod() Dim avFiles 'по умолчанию к выбору доступны файлы Excel(xls,xlsx,xlsm,xlsb) avFiles = Application.GetOpenFilename _ ("Excel files(*.xls*),*.xls*", 1, "Выбрать Excel файлы", , False) If VarType(avFiles) = vbBoolean Then 'была нажата кнопка отмены - выход из процедуры Exit Sub End If 'avFiles - примет тип String MsgBox "Выбран файл: '" & avFiles & "'", vbInformation, "www.excel-vba.ru" End Sub
В данном случае совершенно неважно указан ли выбор только одного файла или нескольких. Может поменяться только способ обработки полученного результата. Если параметр MultiSelect установлен в False, то переменная avFiles примет тип String, т.е. это будет одна строка. Предположим, что была выбрана книга Excel. Тогда открыть её можно будет как обычно это делается при использовании переменной:
Если же параметр MultiSelect установлен в True, то переменная avFiles примет тип Array - массив строк, в котором будут записаны все пути и имена выбранных файлов. Обрабатывать в таком случае следует циклом:
'avFiles - примет тип Array For Each x In avFiles Workbooks.Open x Next
В приложенном к статье файле приведены две процедуры с использованием этого типа диалога и обработкой файлов с параметром MultiSelect , установленным в True и False.
Диалог выбора файлов FileDialog(msoFileDialogFilePicker)
У этого диалога тоже есть параметры и они очень схожи с таковыми в Application.GetOpenFilename:
Ниже в статье примера кода с применением всех описанных параметров
- True - можно будет выбрать более одного файла для обработки(через Shift или Ctrl или простым выделением мышью внутри окна)
- False - можно будет выбрать только один файл
- Description - описание типа файлов. Произвольный текст, указывающий тип файлов. Например "Рисунки" или "Файлы Excel".
- Extensions - расширения файлов. Непосредственно перед расширением обязательно должна стоять звездочка и точка: *.xls . Иначе диалог выдаст ошибку. Для перечисления нескольких расширений используется разделитель в виде точки-с-запятой: "*.xls*;*.xla*" или "*.xls;*.xlsx;*.xlsm" . Звездочка после расширения заменяет любой набор символов или ни одного. Например, при указании "*.xls*" будет возможным выбрать любые файлы, расширение которых начинается на .xls: .xls,.xlsx,.xlsm,.xlsb и т.д. , но нельзя будет выбрать файлы с расширением .xla,.xlam и тем более .doc или .txt . Если необходимо осуществить выбор любого типа файлов, то необходимо просто очистить фильтр и не добавлять никакие типы: .Filters.Clear
- Position - указывает, каким по счету в списке будет тип файлов. На рисунке ниже первым идет тип "Excel files", а вторым "Text files":
Каждый новый тип файлов добавляется новым Add:
.Filters.Add "Excel files", "*.xls*;*.xla*", 1 'добавляем возможность выбора файлов Excel .Filters.Add "Text files", "*.txt", 2 'добавляем возможность выбора текстовых файлов
Если при этом еще добавить имя файла, то в поле диалога Имя файла будет так же отображено это имя:
- msoFileDialogViewDetails
- msoFileDialogViewLargeIcons
- msoFileDialogViewList
- msoFileDialogViewPreview
- msoFileDialogViewProperties
- msoFileDialogViewSmallIcons
- msoFileDialogViewThumbnail
- msoFileDialogViewTiles
- msoFileDialogViewWebView
For Each x In .SelectedItems Workbooks.Open x Next
Так же можно отбирать только отдельные файлы по индексам или организовать цикл иначе:
For lf = 1 to .SelectedItems.Count x = .SelectedItems(lf) Workbooks.Open x Next
- -1 - выбор файлов был сделан и нажата кнопка Открыть
- 0 - была нажата кнопка отмены
Это можно(точнее нужно!) использовать, чтобы не продолжать выполнение кода, если нажата кнопка Отмены:
If .Show = 0 Then Exit Sub 'была нажата кнопка отмены
Пример вызова диалога выбора файлов:
Sub ShowFileDialog() Dim oFD As FileDialog Dim x, lf As Long 'назначаем переменной ссылку на экземпляр диалога Set oFD = Application.FileDialog(msoFileDialogFilePicker) With oFD 'используем короткое обращение к объекту 'так же можно без oFD 'With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Title = "Выбрать файлы отчетов" 'заголовок окна диалога .Filters.Clear 'очищаем установленные ранее типы файлов .Filters.Add "Excel files", "*.xls*;*.xla*", 1 'устанавливаем возможность выбора только файлов Excel .Filters.Add "Text files", "*.txt", 2 'добавляем возможность выбора текстовых файлов .FilterIndex = 2 'устанавливаем тип файлов по умолчанию - Text files(Текстовые файлы) .InitialFileName = "С:\Temp\Книга1.xlsx" 'назначаем папку отображения и имя файла по умолчанию .InitialView = msoFileDialogViewDetails 'вид диалогового окна(доступно 9 вариантов) If oFD.Show = 0 Then Exit Sub 'показывает диалог 'цикл по коллекции выбранных в диалоге файлов For lf = 1 To .SelectedItems.Count x = .SelectedItems(lf) 'считываем полный путь к файлу Workbooks.Open x 'открытие книги 'можно также без х 'Workbooks.Open .SelectedItems(lf) Next End With End Sub
Диалог выбора папки
Диалог выбора папки необходим в случаях, когда файлов в папке много и обработать нужно все эти файлы. Пример такой обработки я уже выкладывал в статье Просмотреть все файлы в папке. Здесь проще всего использовать появившийся в 2002 Excel диалог Application.FileDialog. Его параметры практически такие же, как у Application.FileDialog(msoFileDialogFilePicker) только их меньше доступно для применения:
- msoFileDialogViewDetails
- msoFileDialogViewLargeIcons
- msoFileDialogViewList
- msoFileDialogViewPreview
- msoFileDialogViewProperties
- msoFileDialogViewSmallIcons
- msoFileDialogViewThumbnail
- msoFileDialogViewTiles
- msoFileDialogViewWebView
- -1 - папка выбрана и нажата кнопка Открыть
- 0 - была нажата кнопка отмены
Это можно(точнее нужно!) использовать, чтобы не продолжать выполнение кода, если нажата кнопка Отмены:
If .Show = 0 Then Exit Sub 'была нажата кнопка отмены
Пример вызова диалога выбора папки:
Sub ShowFolderDialog() Dim oFD As FileDialog Dim x, lf As Long 'назначаем переменной ссылку на экземпляр диалога Set oFD = Application.FileDialog(msoFileDialogFolderPicker) With oFD 'используем короткое обращение к объекту 'так же можно без oFD 'With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Выбрать папку с отчетами" '"заголовок окна диалога .ButtonName = "Выбрать папку" .Filters.Clear 'очищаем установленные ранее типы файлов .InitialFileName = "C:\Temp\" '"назначаем первую папку отображения .InitialView = msoFileDialogViewLargeIcons 'вид диалогового окна(доступно 9 вариантов) If oFD.Show = 0 Then Exit Sub 'показывает диалог 'цикл по коллекции выбранных в диалоге файлов x = .SelectedItems(1) 'считываем путь к папке MsgBox "Выбрана папка: '" & x & "'", vbInformation, "www.excel-vba.ru" End With End Sub
Диалог выбора папки через Shell
Диалог Application.FileDialog(msoFileDialogFolderPicker) всем хорош и удобен, кроме одного: как я уже упоминал, он стал доступен из VBA только начиная с 2002 Excel. Плюс, описанные выше диалоги не работают в Outlook - он просто лишен хоть какой-либо реализации выбора папок или файлов. Поэтому дополню статью еще одним вариантом показа диалога выбора папки - с помощью объекта Shell. Этот вариант выбора папки будет работать и в Outlook и в любом другом приложении.
Shell.BrowseForFolder([Hwnd], [sTitle], [iOptions], [vRootFolder])
Hwnd | Дескриптор окна, к которому будет относится диалог. Как правило указывается 0 |
sTitle | Поясняющий текст, который будет отображен в диалоге. Подобие заголовка окна. Может быть любым текстом, например "Выбрать папку с отчетами" |
iOptions | Дополнительные параметры для диалога. Рекомендуется использовать 0. Но можно попробовать и пару других вариантов. Например, если указать 20, то в диалоговом окне появится дополнительное текстовое поле, в котором будет отображено имя выбранной папки. |
vRootFolder | Аналогично InitialFileName в рассмотренных выше диалогах. Задает начальную папку, на которой диалог будет открыт после запуска. |
Пример вызова диалога выбора папки через Shell:
Sub GetFolderDialog_Shell() On Error Resume Next Dim objShellApp As Object, objFolder As Object, ulFlags Dim x As String Set objShellApp = CreateObject("Shell.Application") 'ulFlags - числовой код, определяющий вид отображаемого окна и некоторые параметры ' ulFlags = 0 - наиболее часто применяемый. Лучше использовать всегда именно 0 ' ulFlags = 1 - не отображать Корзину ' ulFlags = 2 - не включать сетевые папки ' ulFlags = 20 - добавляется тестовое поле с отображением имени выбранной папки ' ulFlags = 16 - отображать EditBox для ввода полного пути с клавиатуры ' ulFlags = 16384 - можно так же выбирать файлы. 'Некоторые константы можно комбинировать. Например если указать 1 + 16384 - то можно будет выбирать файлы ulFlags = 0 Set objFolder = objShellApp.BrowseForFolder(0, "Выбрать папку с отчетами", ulFlags, "C:\Temp\")'" x = objFolder.Self.Path 'записываем в переменную путь к папке If Err.Number <> 0 Then MsgBox "Папка не выбрана!", vbInformation, "www.excel-vba.ru" Else MsgBox "Выбрана папка: '" & x & "'", vbInformation, "www.excel-vba.ru" End If End Sub
Конечно, диалог подобный выглядит довольно убого, особенно на современных операционных системах. Но он работает в любых версиях офиса и в любом приложении, в том числе в Outlook. Порой это бывает полезней красоты.
Tips_Macro_GetOpenFileFolder.xls (100,0 KiB, 3 529 скачиваний)
Диалог сохранения файла SaveAs
Еще один вид диалогового окна - запрос имени и места сохранения файла.
Параметры:
Application.GetSaveAsFilename([InitialFileName], [FileFilter], [FilterIndex], [Title], [ButtonText])
Универсальный диалог, работающий во всех версиях Excel, начиная с 2000
Показываем диалог со стартовой папкой на той книге, в которой сам макрос и именем сохраняемой книги "SaveAs.xlsm" :
sToSavePath = Application.GetSaveAsFilename(InitialFileName:="SaveAs.xlsm", FileFilter:="Excel files (*.xlsm), *.xlsm")
avFiles = Application.GetSaveAsFilename _ (InitialFileName:=ThisWorkbook.Path, FileFilter:="Excel files(*.xls*),*.xls*,Text files(*.txt),*.txt", FilterIndex:=2)
Что еще важно знать: сам по себе вызов диалога GetSaveAsFilename ничего не сохраняет - он только создает путь для сохраняемого файла. Сохранять придется принудительно после выбора места и имени.
Пример применения диалога Application.GetSaveAsFilename
Sub ShowGetSaveAsDialod() Dim sToSavePath sToSavePath = Application.GetSaveAsFilename( _ InitialFileName:=ThisWorkbook.Path, _ FileFilter:="Excel files(*.xls*),*.xls*,Text files(*.txt),*.txt", _ FilterIndex:=2, _ Title:="Сохранить файл") 'если нажали Отмена - завершаем процедуру ничего не сохраняя If VarType(sToSavePath) = vbBoolean Then Exit Sub End If 'непосредственно сохранение файла ThisWorkbook.SaveAs Filename:=sToSavePath, FileFormat:=ThisWorkbook.FileFormat End Sub
Здесь тоже есть нюанс - метод SaveAs имеет два важных аргумента:
1. Filename - путь и имя сохраняемого файла. Здесь должно быть все понятно. Указываем то, что выбрали в диалоге.
2. FileFormat - формат сохраняемого файла. При этом не текстовое представление(как в диалоге "xls" или "txt"), а одна из предустановленных констант формата файла. Вот основные константы:
Пример использования констант в диалогах Application.GetSaveAsFilename
Сохраняем файл с форматом xlsm - файл с поддержкой макросов. Для этого ищем в таблице выше расширение xlsm и берем либо константу Excel либо числовую константу:
Sub ShowGetSaveAsDialod() Dim sToSavePath sToSavePath = Application.GetSaveAsFilename( _ InitialFileName:=ThisWorkbook.Path & "\Report.xlsm", _ FileFilter:="Excel files(*.xlsm),*.xlsm") 'если нажали Отмена - завершаем процедуру ничего не сохраняя If VarType(sToSavePath) = vbBoolean Then Exit Sub End If 'непосредственно сохранение файла 'используем встроенную константу Excel ThisWorkbook.SaveAs Filename:=sToSavePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled 'используем числовую константу 'ThisWorkbook.SaveAs Filename:=sToSavePath, FileFormat:=52 End Sub
Любой метод: либо числовая константа, либо встроенная работают одинаково. Вопрос лишь в том, что лично для Вас будет удобнее и нагляднее.
Когда макрос заканчивается, значения, хранимые в его переменных, автоматически не сохраняются на диске. Если макрос должен сохранить значение, он должен хранить это значение за пределами себя до завершения выполнения макроса. В этом разделе описаны пять местоположений, в которых можно легко хранить и извлекать значения макроса.
Переменные документа
Переменные документов позволяют хранить значения в составе документа или шаблона. Например, можно хранить значения макроса в документе или шаблоне, где находится макрос. Вы можете добавить переменные в документ или шаблон с помощью метода Добавить коллекции Переменные . В следующем примере сохраняется переменная документа в том же расположении, что и запущенный макрос (документ или шаблон) с помощью свойства ActiveDocument .
В следующем примере свойство Value с объектом Variable возвращает значение переменной документа.
Примечания
Используйте поле DOCVARIABLE, чтобы вставить переменную документа в документ.
Свойства документов
Как и переменные документов, свойства документа позволяют хранить значения как часть документа или шаблона. Свойства документа можно просмотреть в диалоговом окне Свойства .
Объектная модель Word разбивает свойства документа на две группы: встроенную и настраиваемую. Свойства настраиваемого документа включают свойства, показанные на вкладке Custom в диалоговом окне Свойства . Встроенные свойства документов включают свойства всех вкладок в диалоговом окне Свойства , за исключением вкладки Custom .
Чтобы получить доступ к встроенным свойствам, используйте свойство BuiltInDocumentProperties для возврата коллекции DocumentProperties , которая включает встроенные свойства документов. Используйте свойство CustomDocumentProperties объекта Document или объекта Template , чтобы вернуть коллекцию DocumentProperties , которая включает настраиваемые свойства документа. В следующем примере создается настраиваемая свойство документа с именем "YourName" в том же расположении, что и запущенный макрос (документ или шаблон).
Встроенные свойства документов не могут быть добавлены в коллекцию DocumentProperties , возвращаемую свойством BuiltInDocumentProperties объекта Document или Объекта Template . Однако можно получить содержимое встроенного свойства документа или изменить значение свойства документа чтения и записи.
Используйте поле DOCPROPERTY для вставки свойств документа в документ.
Элементы автотекста
Записи AutoText можно использовать для хранения сведений в шаблоне. В отличие от переменной документа или свойства, записи AutoText могут включать элементы за пределы макро-переменных, таких как форматированный текст или графический. Используйте метод Добавить в коллекцию AutoTextEntries для создания новой записи AutoText. В следующем примере создается запись AutoText с именем "MyText", которая содержит содержимое выбора. Если следующая инструкция является частью макроса шаблона, новая запись AutoText хранится в шаблоне, в противном случае запись AutoText хранится в шаблоне, присоединенном к документу, в котором находится инструкция.
Используйте свойство Value с объектом AutoTextEntry для получения содержимого объекта входа AutoText.
Параметры файлы
Вы можете установить и получить сведения из файла параметров с помощью свойства PrivateProfileString объекта System . Структура файла параметров Windows то же самое, что и Windows 3.1 WIN.INI файла. В следующем примере ключ DocNum устанавливается в разделе DocTracker в Macro.ini файле.
После запуска вышеуказанной инструкции файл Macro.ini содержит следующий текст.
Свойство PrivateProfileString имеет три аргумента: FileName, Section и Key. Аргумент FileName используется для указания пути файла параметров и имени файла. Аргумент Раздела указывает имя раздела, которое отображается между скобками перед связанными ключами (не включайте скобки с именем раздела). Аргумент Key указывает имя ключа, за которым следует равный знак (=) и параметр.
Используйте то же свойство PrivateProfileString для получения параметра из файла параметров. В следующем примере извлекаем параметр DocNum в разделе DocTracker в Macro.ini файле.
Windows реестра
Вы можете установить и получить сведения из реестра Windows с помощью свойства PrivateProfileString. В следующем примере извлекает каталог программ Word 2007 из Windows реестра.
Свойство PrivateProfileString имеет три аргумента: FileName, Section и Key. Чтобы вернуть или задать значение для записи реестра, укажите пустую строку ("") для аргумента FileName . Аргумент Раздела должен быть полным путем к подкайке реестра. Ключевым аргументом должно быть имя записи в подкайке, указанной в разделе.
Вы также можете установить сведения в реестре Windows с помощью следующего синтаксиса PrivateProfileString.
Значение System.PrivateProfileString (FileName, Section, Key_)_ =
В следующем примере в подкайке Параметры для Office Word 2007 в реестре Windows doc-PATH устанавливается запись "C:\My Documents".
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
В этой статье описано, как использовать переменные Microsoft Excel под процедурах в Microsoft Visual Basic для приложений.
Дополнительные сведения
Переменные в под процедуре
Мощной функцией языков программирования является возможность хранить данные в переменной, чтобы содержимое переменной можно было использовать или менять позже. В этом документе обсуждается следующее использование переменных в Visual Basic:
Как объявляются переменные.
Процедуры и проекты, которые могут использовать переменную.
Время жизни переменной.
Объявление переменной в макросе
Самый простой способ объявить переменную в макросах — использовать отчет Dim. В следующей строке две переменные объявлялись как integers(integers).
Dim x As Integer, y As Integer
Если для переменных x и y заданы целые числа, вы указываете Visual Basic выделить достаточно памяти для переменной целого числа (по 2 на x и y) и что данные, хранимые в x или y, являются целым числом в период между -32768 и 32767.
ПРИМЕЧАНИЕ: Если вы объявляете несколько переменных с помощью одной выписки Dim, необходимо указать тип данных для каждой переменной.
Если не указать тип данных для каждой переменной, как в следующем коде Visual Basic, то переменная y будет настроена как переменная с типом данных типа integer. Переменная x будет иметь тип типа variant:
Dim x, y As Integer
Дополнительные сведения см. в приведенной ниже таблице Variant.
Чтобы выполнить проверку переменной, выполните следующие действия:
Сохраните и закроите все открытые книги, а затем откройте новую книгу.
Запустите редактор Visual Basic (нажмите ALT+F11).
В меню Вставка выберите пункт Модуль.
Введите следующий код:
Sub Variable_Test()
Dim x As Integer, y As Integer
x = 10
y = 100
MsgBox "значение x — " & x & _
Chr(13) & "значение y — " & y
End Sub
the value of y is 100
В макро Variable_Test изменить следующую строку:
Запустите Variable_Test макрос.
Вы получите ошибку во время запуска, так как "ошибка" не является integer, и вы пытаетесь назначить это строковую величину для переменной x.
Сводка по типам данных
Это распространенные типы данных переменных:
тип данных Variant
Если не указать тип данных при объявлении переменной или вообще не объявлять переменную, Visual Basic автоматически определяет тип данных переменной типа переменной. Ниже следующую информацию о преимуществах переменных, объявленных в качестве этого типа данных:
Переменные могут содержать строку, дату, время, boolean или числовую величину.
Переменные могут автоматически преобразовывать содержащиеся в них значения.
Недостатком является то, что для переменных требуется не менее 16байт памяти. 16 bytes of memory can be significant in large procedures or in complex modules.
Чтобы узнать, как это работает в макросе Variable_Test, выполните следующие действия:
Измените код макроса Variable_Test на:
Запустите Variable_Test макрос.
ПРИМЕЧАНИЕ: Вы также можете не использовать следующую строку, и макрос будет по-прежнему работать, так как переменные x и y будут рассматриваться как типы данных Variant:
Область действия переменной
Когда вы объявляете переменную, она может быть видна другими макросами в том же модуле, в других модулях или в других проектах. Такое доступность переменной в модулях называется областью действия. Область действия может быть трех типов: на уровне процедуры, на уровне частного модуля и на уровне общего модуля. Область действия зависит от того, как и где объявлять переменные или переменные.
Область на уровне процедуры
Переменная с областью на уровне процедуры не видна за пределами процедуры, в которой она объявлена. Если установить значение переменной, которая имеет область действия на уровне процедуры, ее содержимое не будет видно другими макросами.
Чтобы убедиться, что переменная с областью на уровне процедуры не видна за пределами процедуры, в которой она объявлена, выполните указанные ниже действия.
Вставка нового модуля в проект.
Введите в этот модуль оба макроса:
Sub Macro1() Dim x As Integer x = 10 MsgBox "x, as seen by Macro1 is " & x 'next line runs Macro2 Macro2 End Sub Macro2() MsgBox "x, as seen by Macro2 is " & x End Sub
x, as seen by Macro1 is 10
x, as seen by Macro2 is
Макрос2 не отображает значение переменной x, так как переменная x является локальной для параметра Macro1.
Объем личных и общедоступных модулей
Вы можете определить переменные в разделе объявлений модуля (в верхней части модуля, над всеми под процедурами) и настроить область действия переменной с помощью открытой выписки, затемной или закрытой выписки. Если перед переменной поместить открытое заявление, переменная будет доступна всем макросам во всех модулях проекта. Если перед переменной поместить перед переменной утверждение Dim или Private, переменная будет доступна только макросам в модуле, в котором она объявлена.
Чтобы увидеть разницу между общедоступным заявлением и заявлением Dim, выполните следующие действия:
Сохраните и закроите все открытые книги, а затем откройте новую книгу.
Запустите редактор Visual Basic редактора.
Вставка модуля в проект.
Вставка другого модуля в проект.
Введите следующий код в этот модуль:
Sub Macro2() x = x * 3
MsgBox x
End Sub
Запустите Macro_1a макрос в первом модуле.
Измените строку объявления в первом модуле из:
Public x As Integer
Dim x As Integer
Запустите Macro_1a макрос.
Измените строку объявления в первом модуле из:
Dim x As Integer
Private x As Integer
Запустите Macro_1a макрос.
ПРИМЕЧАНИЕ: Если вы хотите ограничить область действия переменной модулем, в котором она объявлена, используйте privatestatement вместо dim. Оба этих эффекта будут одинаковыми, но при использовании закрытой выписки область действия будет более четкой.
Срок жизни переменной
Время, в течение которого переменная сохраняет значение, называется сроком жизни. Значение переменной может изменяться в течение ее жизненного времени, но при этом сохраняется значение. Кроме того, если переменная теряет область действия, она теряет значение.
Инициализация значения переменной
При запуске макроса все переменные инициализируются со значением. Числовая переменная инициализирована до нуля, строка переменной длины — в нулевую строку (""), а строка фиксированной длины заполняется кодом ASCII 0. Переменные Variant инициализируются как пустые. Пустая переменная представлена нулем в числовом контексте и пустой строкой ("") в строковом контексте.
Переменные уровня процедуры
Если переменная объявляется в макрос с помощью макроса Dim, значение переменной сохраняется до тех пор, пока работает макрос. Если этот макрос вызывает другие макрос, значение переменной сохраняется (но не доступно другим макросам), пока работают и другие макросы.
Чтобы продемонстрировать работу переменных на уровне процедуры, выполните указанные ниже действия.
Вставка нового модуля в проект.
В этом модуле введите оба макроса:
Sub Macro1()
'set x as a procedure level variable
Dim x As Integer
MsgBox "инициализированное значение x — " & x
x = 10
MsgBox "x is " & x
'следующая строка запускает Макрос2
Макрос2
MsgBox "x is still " & x
End Sub
Sub Macro2()
MsgBox "x, as seen by Macro2 is " & x
End Sub
the initialized value of x is 0
x, as seen by Macro2 is
Запуск макроса1.
Статическое ключевое слово
Если переменная на уровне процедуры объявлена с помощью статического ключевого слова, переменная сохраняет значение до сброса проекта. Таким образом, если у вас есть статическая переменная, то при следующем вызове процедуры статическая переменная инициализирована до последнего значения.
Чтобы узнать, как работает статическое ключевое слово, выполните следующие действия:
Запустите Макрос1, и вы получите следующее
ПРИМЕЧАНИЕ: Если у вас есть переменная уровня модуля, ее время существования будет таким же, как если бы она была статической переменной на уровне процедуры.
Чтобы проверить срок жизни переменной уровня модуля, выполните указанные ниже действия.
the initialized value of x is 0
the initialized value of x is 10
Сброс проектов для сброса переменных
Если вы хотите сбросить значение статической переменной или переменной на уровне модуля, нажмите кнопку Сброс на
Стандартная панель инструментов или нажмите кнопку Сброс в меню Выполнить.
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Здравствуйте, дорогие прогеры! У меня встал вопрос сохранения переменной после закрытия файла- для того, чтобы она сохраняла свое значение и при нажатии кнопки дальше продолжала увеличиваться, например
Как сохранить последнюю запись в TextBox после закрытия книги?
КАК сохранить последнюю запись в TextBox после закрытия книги.Как сохранить измененные названия CommandButton и Label после закрытия формы?
Как сохранить измененные названия CommandButton и Label после закрытия формы.Как сохранить состояние TreeView после открытия закрытия формы?
На форме расположено "TreeView". Node_1 -- Node_1_1 -- Node_1_2 Node_2 -- Node_2_1 --.Если можно, пожалуйста поподробнее объясните -мой макрос увеличивает значение на 1 при каждом щелчке мыши и вот хочется чтобы после закрытия excel последнее значение сохранялось
mishapelevin, можно применить вместо Value значение ячейки Font.Color или Borders.Color для сохранения переменной.
Тогда можно использовать любую удобную ячейку,даже если она занята какими либо данными.И в одной ячейке сохранить до 8 переменных.mishapelevin, можно применить вместо Value значение ячейки Font.Color или Borders.Color для сохранения переменной.
Тогда можно использовать любую удобную ячейку,даже если она занята какими либо данными.И в одной ячейке сохранить до 8 переменных.Как сохранить состояние кнопки (цвет) после закрытия программы?
на форме накидано несколько кнопок, при нажатии на кнопку она выделяется цветом, как сделать так.Как сохранить свойство Text элемента TextBox после закрытия программы?
Собственно. Ну то ест программа работает, текст правится вручную. Программу закрываем, текст.Как создать переменную, которая будет сохранять свое значение , даже после закрытия приложения?
Собственно вопрос. Нужно создать переменную, которая будет получать данные и сохранять их в себе.Как сохранить содержимое выбранного файла в переменную?
char txt;cin>>txt; ofstream fout(txt);как сохранить содержание выбранного файла в переменную.Как сохранить переменную или объект после авторизации в приложении
Здравствуйте! Заранее прошу прощения, если некорректно выражусь. Подскажите, пожалуйста, как.Сохранить backgroundImage после закрытия проги
Люди добрые подскажите, пожалуйста, как сохранить в settings или в xml очень надо. То есть.Читайте также: