EXCEL MACRO – это инструмент записи и воспроизведения, который просто записывает ваши шаги Excel, и макрос будет воспроизводить его столько раз, сколько вы захотите. Макросы экономят время, поскольку они автоматизируют повторяющиеся задачи. Это фрагмент программного кода, который выполняется в среде Excel, но вам не нужно быть программистом для программирования макросов. Тем не менее, вам нужны базовые знания VBA для внесения расширенных изменений в макрос.
Посмотреть исходный код Microsoft Excel
Есть ли эквивалент для просмотра исходного кода в Microsoft Excel для всей книги Excel или, возможно, для отдельных ячеек?
В идеале я хотел бы просмотреть список всех возможных вариантов для Microsoft Excel. Похоже, многие параметры доступны в разделе «Формат ячейки».
изменён William 357
задан William 357
2 ответа 2
В Excel нет встроенной команды для отображения всех настроек содержимого / формулы / форматирования ячейки.
Это сложная тема, и есть несколько способов предоставить информацию о ячейке или листе или рабочей книге.
Don’t Drag use this Excel Tricks ||#excel
Формулы. В Excel 2013 и более поздних версиях существует функция FormulaText(), которую можно использовать для отображения формулы, используемой конкретной ячейкой.
Ctrl-`(это клавиша слева от клавиши 1 на клавиатуре США) — это переключит отображение листа в режим просмотра формул, поэтому вместо РЕЗУЛЬТАТА формул вы увидите фактические формулы внутри ячеек.
Доступен ряд инструментов аудита электронных таблиц. Некоторые из них являются коммерческими, некоторые бесплатными.
Excel MVP Дэйв Бретт является автором Mappit, который является бесплатным
Excel 2013 и выше имеет встроенную надстройку «Запрос», которую необходимо активировать с помощью надстроек Com Options Excel, прежде чем она сможет работать, и она предоставит больше информации о вашей электронной таблице, чем вы когда-либо захотите узнать. Если вы хотите знать «все возможные варианты для Microsoft Excel», то это ваша отправная точка.
Источник: poweruser.guru
Финансы в Excel
Создание программного кода для автоматизации определенных процедур открывает практически безграничные возможности по развитию и применению приложений на основе Excel.
Запись макроса
Первое средство автоматизации, с которым сталкивается пользователь – это, так называемая, запись макроса. После включения этой записи Excel автоматически создает программный код с последовательностью производимых пользователем операций.
Оценивая оригинальность решения, практическая польза данной функции, с нашей точки зрения, весьма сомнительна. Дело в том, что повторное использование записанного макроса без внесения изменений за редким исключением невозможно. Неподготовленный пользователь обычно видит просто набор команд, но не получает ожидаемый результат от записанного макроса. К тому же у пользователя складывается ошибочное мнение о простоте написания программ на VBA.
Как спрятать данные в ячейках Excel?
Реальное программирование не имеет с записью макросов ничего общего. Требуется знать как основы языка программирования, так и изучить объектную модель Excel. Кстати, как раз для последнего случая функция «запись макроса» несет определенную пользу в качестве дополнения к справочной системе. В автоматически записанном коде можно быстро найти нужные свойства или методы того или иного объекта Excel.
Макросы Excel 4.0
С этим мало, кто сталкивался на практике, но, в версии Excel 4.0 имелась возможность создавать макрокоманды и выполнять их последовательности. Для этого предназначен специальный тип листа рабочей книги. Макросы Excel 4.0 (XLM) поддерживаются во всех версиях до настоящего времени.
Попробуйте вставить «международный лист макросов» и написать в нем 2 команды (см. картинку). Затем запустить этот макрос через указание ячейки (проще всего запуск макросов вызывается через сочетание клавиш Alt+F8):
«Международный лист макросов» необходим для записи команд на английском языке, имеются также аналоги русских команд.
Макросы XLM позволяют выполнять любые команды Excel, реализованные на тот момент, а также формировать и открывать диалоговые окна. С конца 90х годов Microsoft настоятельно рекомендует переписать XLM-макросы в Excel-приложениях на программы VBA. Тем не менее, функциональность старых макросов полностью поддерживается до сих пор.
Visual Basic for Applications 5.0
Первая версия Excel, поддерживающая язык программирования Visual Basic for Applications появилась в 1993м году – Excel 5.0. Первоначально редактор кода представлял собой специальный тип листа рабочей книги.
Поддерживалась полная объектная модель Excel, пользователь мог разделять код на программные модули. Впервые появилась возможность визуального создания и изменения диалоговых форм — коллекция DialogSheets. Для данных форм существуют элементы управления с собственными обработчиками событий, сами формы могут быть открыты только в модальном режиме.
С помощью DialogSheets уже можно было создавать достаточно сложные варианты пользовательского интерфейса. В дальнейшем функциональность DialogSheets была заменена на пользовательские формы UserForms. Старые диалоговые формы в версиях, начиная с Excel 2000, поддерживаются только в целях обратной совместимости – на них даже отсутствует описание в справочной системе.
VBA 6
Excel 2000 (порядковый номер версии 9.0) включает в себя новую версию языка Visual Basic for Applications. В нем появилась поддержка пользовательских классов, форм и процедур обработчиков событий. Синтаксис языка VBA унифицирован с основными приложениями Microsoft Office (Excel, Word, Access, PowerPoint). Несколько выделяется, пожалуй, Microsoft Access, который поддерживает собственный редактор форм.
Основные новшества VBA6:
- Пользовательские классы.
- Доступ к стандартным обработчикам событий листов и рабочей книги.
- Возможность обработки событий с использованием ключевого слова WithEvents.
- Пользовательские формы UserForms с новыми элементами управления.
- Возможность использования внешних ActiveX-контролов и библиотек кода.
- Несколько новых полезных функций (например, Join и Split).
Кроме того, на основе новой версии VBA существовали специальные версии Excel для разработчиков, поддерживающие создание COM DLL. Подробнее см.статью Надстройки.
Использование внешних библиотек
Внешние библиотеки формата COM DLL (ActiveX DLL) подключаются через использование ссылок в проекте VBA (меню ToolsReferences). Визуальные элементы управления (.ocx-файлы) подключаются аналогичным способом при выборе пункта меню Tools Additional Controls.
При использовании ссылок на внешние файлы может потребоваться уделить особое внимание целостности проекта при его распространении на другие компьютеры. Стандартные библиотеки (например, MSCOMCTL.OCX) обычно корректно переподключаются на клиентских компьютерах, автоматически подбирая ссылку на последнюю версию. С нестандартными библиотеками и собственными ActiveX-контролами могут возникнуть проблемы. Для проверки подключения внешних COM DLL теоретически можно использовать коллекцию Application.VBE.ActiveVBProject.References. Но на практике объект VBE лучше не использовать в приложениях, так как доступ к нему может быть ограничен настройками Excel (начиная с версии 2007).
Технология COM позволяет обеспечить доступ к внешним библиотекам и приложениям без использования жестких ссылок. Для этого используется, так называемое, позднее связывание объектов. В качестве интерфейса IUnknown в VBA выступает тип Object; для создания экземпляра объекта используется стандартная процедура CreateObject, а для получения имеющегося экземпляра – GetObject.
Dim oWord As Object Set oWord = CreateObject(«Word.Document») oWord.Application.Visible = True
Для вызова функций и процедур динамических библиотек Windows (Win32 DLL) в любой версии VBA поддерживается конструкция Declare. Например:
Public Declare Function GetUserName Lib «advapi32.dll» Alias «GetUserNameA» (ByVal sBuf As String, ByRef iSize As Long) As Boolean
После подобного объявления функцию можно вызывать обычным способом как любую функцию VBA.
Кроме стандартных Win API процедур и функций, можно разрабатывать собственные DLL и подключать их в проекты VBA аналогичным способом.
VSTO
Microsoft предоставляет средства автоматизации Excel в других своих продуктах для разработчиков. Так, существует, например, шаблон проекта ActiveX DLL Visual Basic 6.0, адаптированный для Microsoft Office. В настоящее время Microsoft поддерживает направление разработки приложений, основанное на технологии .NET — Visual Studio Tools for Office (VSTO). VSTO представляет из себя набор шаблонов проектов и средств поддержки выполнения Visual Studio .NET, предназначенных для разработки библиотек и надстроек различных приложений Microsoft Office.
Пример исходного кода проекта .NET для Excel можно найти в разделе Прочее.
См.также статью Надстройки.
Автоматизация Excel для внешних приложений
Подобно вызову внешних COM DLL, Excel сам может являться объектом автоматизации для других программ. Например, вы можете вызывать и управлять рабочей книгой с помощью vbs-скриптов. Например, создайте текстовый файл следующего содержания:
Set oExcelApp = CreateObject(«Excel.Application») oExcelApp.Visible = True Set oWorkbook = oExcelApp.Workbooks.Add Set oWorksheet = oWorkbook.Worksheets.Add oWorksheet.Cells(1,1)=»Hello»
Сохраните этот файл с расширением vbs, а затем откройте. При успешном запуске будет открыт Excel, создана рабочая книга и лист, на котором появится текст «Hello» в верхней левой ячейке. Аналогичным образом можно создавать очень сложные программы, управляющие Excel-файлами.
При переносе кода в vbs-скрипт (или другое приложение) первоначально удобнее его создавать и отлаживать код в редакторе VBA самого Excel – в нем имеется полная информация о синтаксисе методов и свойств встроенных объектов. В связи с этим хорошей практикой при программировании на VBA является использование полных путей доступа к объектам: например, вместо ActiveWorkbook прописывать Application.ActiveWorkbook. Такой стиль программирования избавит вас от скрытых ошибок при переносе кода в любые другие приложения.
Безопасность файлов
Microsoft, проявляя заботу о пользователях, всячески пытается предотвратить случайное распространение компьютерных вирусов, написанных на VBA. Основываясь на собственном опыте, можем предположить, что такая «забота» зачастую приносит больше проблем пользователям, чем повышает безопасность их работы.
В нашей практике работы с файлами Excel (начиная с 1995го года) нам ни разу не встречались макро-вирусы, написанные специально для приложений Excel. При этом мы не исключаем потенциальную возможность написания таких вирусов (даже можем попытаться что-то подобное создать), только непонятно зачем это кому-нибудь нужно. Использование тех же скриптовых языков гораздо более серьезная угроза для операционной системы. Не говоря уже о том, что Visual Basic – это не самое подходящее средство для написания компьютерных вирусов.
Но плохо даже не то, что Microsoft перестраховывается в поиске потенциальных угроз. Огорчает непоследовательность и постоянное усложнение процедуры управления безопасностью проектов Excel-VBA. Кроме того, явно прослеживается тенденция, направленная на использование цифровой подписи надежных издателей. При этом получение права подписи является платной услугой, а сама технология имеет ошибки в реализации с Excel-файлами (подробнее см.следующий раздел).
Вот как исторически изменялись характеристики безопасности:
- Excel 4.0-7.0 – нет полной уверенности, но похоже, что не было защиты от макровирусов, макросы запускались без дополнительных предупреждений.
- Excel 97 (8.0) – появился флажок защиты в общих параметрах.
- Excel 2000 (9.0) – появился специальный диалог в меню Сервис Макросы с выбором одного из трех уровней безопасности и списком надежных сертификатов. По умолчанию установлен средний уровень безопасности. То есть при открытии файлов с макросами пользователю самому предлагается решить, насколько это безопасно. Надстройки Excel по умолчанию активизируются без дополнительных предупреждений.
- Excel 2002 (10.0) – добавился еще один «самый высокий» уровень безопасности, разрешающий запуск макросов, подписанных надежным издателем, и только из надежных источников. По умолчанию установлен средний уровень безопасности.
- Excel 2003 (11.0) – то же, что в предыдущей версии, но по умолчанию устанавливается высокий уровень безопасности. То есть макросы отключаются без предупреждений.
- Excel 2007 (12.0) – переписан интерфейс параметров, появился так называемый «Центр управления безопасностью». Уровни безопасности изменились по поведению, хотя их также осталось четыре. По умолчанию при открытии файла с макросами под лентой появляется окошко с возможностью активизировать макросы. Условно говоря, это тот же диалог подключения макросов, но открывается он немодально. Решение с одной стороны кажется привлекательным по интерфейсу, но может привести к несовместимости со старым версиями: например, пользователь может поработать некоторое время без макросов, а затем их подключить. Появилась отдельная опция возможности управления объектом VBE на уровне кода, которая по умолчанию отключена. Также отдельно настраивается безопасность надстроек Excel, которые по умолчанию пока все-таки подключаются без дополнительных вопросов.
Учитывайте эти различия в настройках при разработке и распространении собственных приложений для Excel.
С нашей точки зрения слабым моментом в настройке безопасности является то, что все параметры хранятся в системном реестре Windows в открытом виде, и теоретически не составляет особого труда изменять их при работе других приложений. Зачастую этим занимаются инсталляционные программы для приложений Excel.
Сертификаты VBA
Начиная с Microsoft Office 2000 стала доступна технология Microsoft Authenticode, позволяющая подписывать исполняемый код VBA в макросах и надстройках. Для разработчика решений это означает возможность запуска программ с установленным уровнем безопасности Высокий и Очень высокий.
Цифровые подписи обеспечивают поддержку целостности распространяемых решений (никто не может внести изменения в подписанный код, кроме владельца сертификата) и удостоверяют личность разработчика (однозначно определяют автора решения).
Существует несколько вариантов получения сертификатов:
- Самоподписанный сертификат
- Сертификат из локального центра сертификации
- Коммерческий сертификат
Возможности использования самоподписанного сертификата ограничиваются в основном персональным применением. Для того, чтобы не возникало постоянного предупреждения об ошибке сертификата, необходимо добавить его в список надежных источников.
В случае наличия локального центра сертификации (в рамках организации, например), возможно создание сертификатов, выданных этим центром. При наличии Active Directory возможно распространение корневого сертификата на компьютеры пользователей, которое позволит определять подписанный код как доверенный. Для создания локального центра сертификации можно использовать службы сертификации Windows Server (Certificate Services), либо использовать решения на базе пакета OpenSSL.
При необходимости наиболее широкого распространения решений на базе Microsoft Office, рекомендуется приобрести коммерческий сертификат для подписывания кода (Code Signing). Использование широкоизвестных центров сертификации позволит пользователям быть уверенным, что они получают продукт от вполне определенного поставщика и в неизменённом виде.
Однако, есть несколько моментов в работе с сертификатами, о которых необходимо упомянуть:
- Сертификат необходимо иметь на компьютерах всех разработчиков, вносящих изменения в проект VBA.
- Иногда возникают ситуации, когда даже без изменения кода подписанный проект считается измененным. В данном случае работа с макросами невозможна, и единственным выходом из этой ситуации является снятие подписи в настройках проекта.
Комментарии
# RE: Автоматизация в Excel — AntonFox 15.10.2015 07:42
Уже неоднократно сталкивался с командами XML на русском языке, но найти справочник по ним так и не удалось. Выше есть ссылка на англ версию, а где бы найти русскую версию и описания и команд?
Например команда
ПОЛУЧИТЬ.ДОКУМЕНТ(76) — что тут означает 76?
# how to hack 27.07.2014 10:18
Since the admin of this site is working, no uncertainty very shortly it will be well-known, due to
its feature contents.
# ddt 24.04.2012 13:09
«открывает практически безграничные возможности по развитию и применению приложений на основе Excel.»
Ни скорости, ни защищенности данных, ни интерфейса нормального нет. Это называется «безграничны возможности»?
Смотри также
» Пересчет отдельных формул в рабочей книге
Типичная проблема при работе с большими или очень большими файлами Excel — это слишком сильное замедление при выполнении любых расчетов. Стандартной рекомендацией при этом является переключение Excel на ручной режим пересчета (ленат Формулы Параметры вычислений).
Но зачастую рабочая книга построена таким образом, что хотелось бы видеть автоматическое обновление каких-то оперативных данных и сумм непосредственно при вводе. Нажимать все время F9, во-первых, неудобно, во-вторых, расчет каких-то итоговых таблиц вообще можно отложить на неопределенный срок — занимать время на их пересчет совсем не требуется. Иногда помогает пересчет не всей рабочей книги, а только активного листа — Shift+F9. Но и это не всегда удобно, и зависит от степени сложности связей между листами.Кардинальным способом решения проблемы является.
» Редактирование формул
При построении экономических моделей, обработке больших объемов данных, статистических расчетах и других задачах, решаемых при помощи Excel, требуется использование сложных формул. Под сложностью здесь понимается наличие нескольких уровней вложенности скобок (более одного), а также большой размер математического выражения.
» Класс сохранения настроек Excel
В данной статье описывается небольшой, но очень удобный класс для сохранения и автоматического восстановления таких параметров Excel.Application, как Calculation (режим пересчета) и DisplayAlerts (отображение всплывающих сообщений).
» Преимущества нового формата рабочей книги
Начиная с версии 2007, Excel позволяет использовать новый формат рабочей книги (XLSX). По сравнению с прошлым форматом (XLS), поддерживающимся, начиная с версии Excel 97, появились несомненные преимущества в работе с электронными таблицами. Однако, с точки зрения автора, имеются и ряд недоработок, а также пожеланий разработчикам на будущее.
» Проблемы Excel
Большинство статей на нашем сайте прямо или косвенно описывают возможности и достоинства Excel. Это программа используется ежедневно миллионами пользователей во всем мире, поэтому очевидно, что достоинства Excel явно перевешивают любые найденные недоработки. Однако, как известно, идеальных программ не бывает. Остановимся поподробнее на этих недоработках, ошибках и других досадных моментах при работе в Excel различных версий. Сразу хочется отметить, что многие умозаключения носят субъективный характер.
Источник: www.excelfin.ru