Макросы Visual Basic в Excel 97 - полезно знать
Отличительной чертой бизнес-продуктов Microsoft является обязательное присутствие макроязыка (в MS Office 97 это Visual Basic for Applications - VBA) и поддержка технологии OLE. Это означает, что если Вам недостаточно стандартно предлагаемых возможностей программы, то Вы можете самостоятельно их расширить с помощью макросов, а с помощью OLE - использовать все это в других приложениях.
Чтобы использовать макросы VBA, понадобятся две панели - "Visual Basic" и "Элементы управления". Включите их через меню "Вид/Панели инструментов" и перетащите мышью в удобное место.
Продолжая тему предыдущей статьи "Использование Excel 97 - не сядьте на мель" в прошлом выпуске "КГ", поставим следующую задачу: необходимо автоматизировать копирование результатов расчетов из различных листов книги в дополнительный лист и сохранить его в другом формате.
"В лоб" задача решается примитивно просто: открываем нужную книгу, на панели "Visual Basic" нажимаем кнопку "Записать макрос" (либо меню "Сервис/Макрос/Начать запись"), в диалоговом окне записываем понятное имя нового макроса (вроде "МойЛичныйМакрос") и жмем. Теперь Excel готов запомнить все последующие действия. Переходим в нужный лист, выделяем блок ячеек с результатами, нажимаем "Копировать". Переходим в дополнительный лист, указываем начальную ячейку, нажимаем "Вставить". Таким образом, повторяя сей процесс многократно, заносим требуемые данные в дополнительный лист, переходим в него и выбираем пункт меню "Файл/Сохранить как". Указываем тип файла "*.DBF" и нужное имя и жмем . Теперь можно нажать кнопку "Остановить запись" (либо меню "Сервис/ Макрос/ Остановить запись").
Чтобы вызвать полученный макрос, выбираем пункт меню "Сервис/ Макрос/ Макросы", в списке выбираем записанный макрос (" МойЛичныйМакрос"), нажимаем "Выполнить" и ... естественно, наблюдаем свои действия в ускоренном режиме. Если таблицы имеют значительные размеры, то, конечно, приходится их прокручивать в разные стороны и многократно переключаться между листами, перенося несмежные блоки ячеек. Тогда выполнение этих действий макросом создает неприятное мельтешение таблиц и катавасию на экране. Одна дама даже испугалась, увидав такое на своем компьютере, поэтому рекомендуется либо проводить предварительную психологическую подготовку малоопытным пользователям, либо корректировать макросы вручную.
Вот и подошли к самому интересному.Выбираем пункт меню "Сервис/ Макрос/ Макросы", в списке выбираем записанный макрос (" МойЛичныйМакрос"), нажимаем "Изменить", и в запущенном редакторе Visual Basic откроется окно с исходным текстом макроса в виде подпрограммы на языке Basic. Только кроме операторов Basic'а используются вызовы методов объектов Office.
Для несведущих - имена объекта и его метода или свойства при написании разделяются точкой. Метод объекта - функция, принадлежащая только этому объекту и обрабатывающая только его свойства. Свойства объекта - переменная внутри объекта. Объекты могут быть вложенными, т. е. находиться внутри других объектов, тогда их имена при написании также разделяются запятой. Когда внутри метода глобального объекта используем его же вложенные объекты (так происходит в большинстве случаев), тогда имя этого глобального объекта опускаем, т. к. оно подразумевается по умолчанию.
Сразу можете закомментировать (поставить в начале строки апостроф) команды типа ActiveWindow.SmallScroll Down:=6 и ActiveWindow.LargeScroll ToRight:=-1, т.к. они управляют только прокруткой содержимого окна.
Перенос данных производят следующие команды:
Range("A3:C12").Select 'Выделение блока ячеек, начиная с A3 и заканчивая C12
Selection.Copy 'Копирование выделенной области
Sheets("Лист2").Select 'Переключение на лист Лист2
Range("C2").Select 'Выделить ячейку С2
ActiveSheet.Paste 'Вставить, начиная с выделенной ячейки
Когда такие операции повторяются несколько раз, содержимое окна многократно перерисовывается, это и создает неприятный эффект. Если не получается перенести данные за один прием, тогда можно попробовать переписать макрос вручную.
Закомментируйте строки, сгенерированные Excel, и введите свои, примерно следующего содержания:
'Однократное переключение на дополнительный лист
Sheets("Лист2").Select
'Присваивание значения одной ячейке
Range("C2").Value= Range("'Лист1'!A3").Value
'Присваивание одного значения группе ячеек
Range("C3:C7").Value= Range("'Лист1'!B7").Value
Если одинаковых операций для смежных ячеек слишком много, то их можно объединить в циклы, например:
From=2 'Начальный номер строки источника данных
Into=11 'Конечный номер строки источника
xFrom=7 'Начальный номер строки назначения данных
xInto= xFrom + Into - From 'Конечный номер области назначения
For i=1 to XX
Ranger= Str(i)
Ranger= Right(Ranger, Len(Ranger)-1)
j= i - From + xFrom
xRanger= Str(j)
xRanger= Right(xRanger, Len(xRanger)-1)
Range("C"+ xRanger).Value= Range("'Лист1'!A"+ Ranger).Value
Next i
Присваивать ячейкам можно не только значения, но и формулы в виде строк. Например, Range("A2").FormulaR1C1 = "=A1+1". Значением ячейки будет результат вычисления этой формулы. Следует отметить, что при сохранении таблицы в формате какой-нибудь БД будут сохранены именно значения.
Еще один важный положительный момент - вы можете указать способ доступа при сохранении таблицы в параметре AccessMode. Дело в том, что Excel использует по умолчанию эксклюзивный (однопользовательский) режим доступа к данным. Но если нужно использовать данные в разделяемом (многопользовательском) режиме, вы должны вручную дописать значение параметра. Например, следующая команда сохранит лист в формате *.DBF в режиме разделяемого доступа:
ActiveWorkbook.SaveAs FileName:= _
"C:\Мои документы\Makovchik\Example.dbf", FileFormat:=xlDBF4, _
CreateBackup:=False AccessMode:=xlShared
Дополнительно, для удобства, создадим простейшую кнопку вызова макроса. На панели "Элементы управления" нажимаем кнопку "Кнопка", переводим указатель мыши в рабочую область таблицы, нажимаем левую кнопку мыши и, удерживая ее, очерчиваем размеры кнопки, а затем отпускаем кнопку мыши. Появится кнопка "CommandButton1", и одновременно Excel перейдет в режим конструктора. Щелкаем правой кнопкой мыши по кнопке на таблице и в меню выбираем "Исходный текст". Откроется окно редактора с пустой процедурой, которая будет вызываться при нажатии на эту кнопку. Внутри ее (между строк "Private Sub..." и "End Sub") в отдельной строке пишем имя записанного ранее макроса и закрываем редактор VBA. На панели "Элементы управления" нажимаем кнопку "Режим конструктора" для возврата ее в нормальное состояние. Теперь при нажатии на кнопку "CommandButton1" подпрограмма обработки событий вызовет наш макрос. Не нравится название - нажимаем "Режим конструктора", щелкаем правой кнопкой мыши по экранной кнопке и выбираем "Свойства". В списке выбираем свойство "Caption" и пишем любое другое слово. Вот и все.
Пример книги с подобными макросами можно будет скачать по адресу www.chat.ru/~vic_mak/ecology.zip.
В дополнение ко всему вышесказанному весьма и весьма полезным применением Visual Basic является доступ к объектам Office 97 из внешних программ, поддерживающих механизм OLE, например Visual FoxPro. Вы можете создать объект-наследник от "Excel.Application" и вызывать методы этого объекта в своей программе. Это можно применить, например, для передачи данных и формирования отчетов своей программой внутри Excel. Но подробнее об этом в следующий раз.
Виктор Маковчик
Чтобы использовать макросы VBA, понадобятся две панели - "Visual Basic" и "Элементы управления". Включите их через меню "Вид/Панели инструментов" и перетащите мышью в удобное место.
Продолжая тему предыдущей статьи "Использование Excel 97 - не сядьте на мель" в прошлом выпуске "КГ", поставим следующую задачу: необходимо автоматизировать копирование результатов расчетов из различных листов книги в дополнительный лист и сохранить его в другом формате.
"В лоб" задача решается примитивно просто: открываем нужную книгу, на панели "Visual Basic" нажимаем кнопку "Записать макрос" (либо меню "Сервис/Макрос/Начать запись"), в диалоговом окне записываем понятное имя нового макроса (вроде "МойЛичныйМакрос") и жмем. Теперь Excel готов запомнить все последующие действия. Переходим в нужный лист, выделяем блок ячеек с результатами, нажимаем "Копировать". Переходим в дополнительный лист, указываем начальную ячейку, нажимаем "Вставить". Таким образом, повторяя сей процесс многократно, заносим требуемые данные в дополнительный лист, переходим в него и выбираем пункт меню "Файл/Сохранить как". Указываем тип файла "*.DBF" и нужное имя и жмем . Теперь можно нажать кнопку "Остановить запись" (либо меню "Сервис/ Макрос/ Остановить запись").
Чтобы вызвать полученный макрос, выбираем пункт меню "Сервис/ Макрос/ Макросы", в списке выбираем записанный макрос (" МойЛичныйМакрос"), нажимаем "Выполнить" и ... естественно, наблюдаем свои действия в ускоренном режиме. Если таблицы имеют значительные размеры, то, конечно, приходится их прокручивать в разные стороны и многократно переключаться между листами, перенося несмежные блоки ячеек. Тогда выполнение этих действий макросом создает неприятное мельтешение таблиц и катавасию на экране. Одна дама даже испугалась, увидав такое на своем компьютере, поэтому рекомендуется либо проводить предварительную психологическую подготовку малоопытным пользователям, либо корректировать макросы вручную.
Вот и подошли к самому интересному.Выбираем пункт меню "Сервис/ Макрос/ Макросы", в списке выбираем записанный макрос (" МойЛичныйМакрос"), нажимаем "Изменить", и в запущенном редакторе Visual Basic откроется окно с исходным текстом макроса в виде подпрограммы на языке Basic. Только кроме операторов Basic'а используются вызовы методов объектов Office.
Для несведущих - имена объекта и его метода или свойства при написании разделяются точкой. Метод объекта - функция, принадлежащая только этому объекту и обрабатывающая только его свойства. Свойства объекта - переменная внутри объекта. Объекты могут быть вложенными, т. е. находиться внутри других объектов, тогда их имена при написании также разделяются запятой. Когда внутри метода глобального объекта используем его же вложенные объекты (так происходит в большинстве случаев), тогда имя этого глобального объекта опускаем, т. к. оно подразумевается по умолчанию.
Сразу можете закомментировать (поставить в начале строки апостроф) команды типа ActiveWindow.SmallScroll Down:=6 и ActiveWindow.LargeScroll ToRight:=-1, т.к. они управляют только прокруткой содержимого окна.
Перенос данных производят следующие команды:
Range("A3:C12").Select 'Выделение блока ячеек, начиная с A3 и заканчивая C12
Selection.Copy 'Копирование выделенной области
Sheets("Лист2").Select 'Переключение на лист Лист2
Range("C2").Select 'Выделить ячейку С2
ActiveSheet.Paste 'Вставить, начиная с выделенной ячейки
Когда такие операции повторяются несколько раз, содержимое окна многократно перерисовывается, это и создает неприятный эффект. Если не получается перенести данные за один прием, тогда можно попробовать переписать макрос вручную.
Закомментируйте строки, сгенерированные Excel, и введите свои, примерно следующего содержания:
'Однократное переключение на дополнительный лист
Sheets("Лист2").Select
'Присваивание значения одной ячейке
Range("C2").Value= Range("'Лист1'!A3").Value
'Присваивание одного значения группе ячеек
Range("C3:C7").Value= Range("'Лист1'!B7").Value
Если одинаковых операций для смежных ячеек слишком много, то их можно объединить в циклы, например:
From=2 'Начальный номер строки источника данных
Into=11 'Конечный номер строки источника
xFrom=7 'Начальный номер строки назначения данных
xInto= xFrom + Into - From 'Конечный номер области назначения
For i=1 to XX
Ranger= Str(i)
Ranger= Right(Ranger, Len(Ranger)-1)
j= i - From + xFrom
xRanger= Str(j)
xRanger= Right(xRanger, Len(xRanger)-1)
Range("C"+ xRanger).Value= Range("'Лист1'!A"+ Ranger).Value
Next i
Присваивать ячейкам можно не только значения, но и формулы в виде строк. Например, Range("A2").FormulaR1C1 = "=A1+1". Значением ячейки будет результат вычисления этой формулы. Следует отметить, что при сохранении таблицы в формате какой-нибудь БД будут сохранены именно значения.
Еще один важный положительный момент - вы можете указать способ доступа при сохранении таблицы в параметре AccessMode. Дело в том, что Excel использует по умолчанию эксклюзивный (однопользовательский) режим доступа к данным. Но если нужно использовать данные в разделяемом (многопользовательском) режиме, вы должны вручную дописать значение параметра. Например, следующая команда сохранит лист в формате *.DBF в режиме разделяемого доступа:
ActiveWorkbook.SaveAs FileName:= _
"C:\Мои документы\Makovchik\Example.dbf", FileFormat:=xlDBF4, _
CreateBackup:=False AccessMode:=xlShared
Дополнительно, для удобства, создадим простейшую кнопку вызова макроса. На панели "Элементы управления" нажимаем кнопку "Кнопка", переводим указатель мыши в рабочую область таблицы, нажимаем левую кнопку мыши и, удерживая ее, очерчиваем размеры кнопки, а затем отпускаем кнопку мыши. Появится кнопка "CommandButton1", и одновременно Excel перейдет в режим конструктора. Щелкаем правой кнопкой мыши по кнопке на таблице и в меню выбираем "Исходный текст". Откроется окно редактора с пустой процедурой, которая будет вызываться при нажатии на эту кнопку. Внутри ее (между строк "Private Sub..." и "End Sub") в отдельной строке пишем имя записанного ранее макроса и закрываем редактор VBA. На панели "Элементы управления" нажимаем кнопку "Режим конструктора" для возврата ее в нормальное состояние. Теперь при нажатии на кнопку "CommandButton1" подпрограмма обработки событий вызовет наш макрос. Не нравится название - нажимаем "Режим конструктора", щелкаем правой кнопкой мыши по экранной кнопке и выбираем "Свойства". В списке выбираем свойство "Caption" и пишем любое другое слово. Вот и все.
Пример книги с подобными макросами можно будет скачать по адресу www.chat.ru/~vic_mak/ecology.zip.
В дополнение ко всему вышесказанному весьма и весьма полезным применением Visual Basic является доступ к объектам Office 97 из внешних программ, поддерживающих механизм OLE, например Visual FoxPro. Вы можете создать объект-наследник от "Excel.Application" и вызывать методы этого объекта в своей программе. Это можно применить, например, для передачи данных и формирования отчетов своей программой внутри Excel. Но подробнее об этом в следующий раз.
Виктор Маковчик
Компьютерная газета. Статья была опубликована в номере 27 за 1999 год в рубрике soft :: субд
