 |
| |||
 |
|
Основано на FAQs by Михаил Землянуха
Дополнено, частично изменено by Acidigital Содержание
Выключение отображения изменений на экране Если Ваш макро интенсивно что-то отображает на экране (например в цикле выделает ячейки, вставляет данные, etc.) Вы можете увеличить скорость работы макро выключив обновление экрана: Application.ScreenUpdating = False. В этом письме есть также советы как писать макро так чтобы они работали побыстрее. Грабли при использовании ScreenUpdating в функциях в Excel 97 В Excel 97 возможны проблемы при установке свойства Application.ScreenUpdating из пользовательской функции. Да что там из пользовательской - теперь оно несбрасывается автоматическикак раньше. Почему на листе модуля В качестве разделителя используется символ ';' а не ',' Как определить разделитель целой и дробной части и прочие международные установки. Это зависит от настроек Excel в меню Сервис/ Параметры/ Модуль/ РазделительСписков. Для извлечения текущих значений используйте свойство Application.International. Можно определить десятичный разделитель, разделитель тысяч и пр. Как определить есть ли комментарии на рабочем листе Как узнать есть ли хоть один Notes (комментарий) в рабочем листе, кроме как перебором по всем ячейкам? . Без этого не работает. В Excel'97 эта проблема может быть решена вот как: ' Function IsCommentsPresent ' Возвращает TRUE, если на активном рабочем листе имеется хотя бы ' одна ячейка с комментарием, иначе возвращает FALSE ' Public Function IsCommentsPresent() As Boolean IsCommentsPresent = ( ActiveSheet.Comments.Count <> 0 ) End Function Как защитить лист от пользователя, но не от макро А для этого есть специальная фича UserInterfaceOnly. Чем отличается ThisWorkBook от ActiveWorkbook На листе модулей открытой рабочей книги присутствует процедура, которая копирует некий лист из другой (не активной) рабочей книги. В этом листе в некоторых ячейках находятся определенные пользователем формулы. Процедура работает без проблем. Попробуйте заменить ссылки типа ActiveWorkbook.WorkSheets(".. на ссылки вида ThisWorkBook.WorkSheets(".. Дело в том, что когда выполняется код надстройки, активной книгой в Excel'е является не сама надстройка! Конструкция ThisWorkbook позволяет сослаться на книгу, в которой в настоящий момент выполняется код Excel VBA. Примечание: Это общий принцип создание надстроек Excel. Как вычислить формулу в макро Если у Вас есть строка с формулой, Вы можете вычислить ее при помощи функции VBA Evaluate. Как определить использованную область листа Как определить пересечение областей Использованную часть рабочего листа можно определить через свойство Worksheet.UsedRange. Для определения пересечения областей можно использовать метод Application.Intersect(). Если Вам надо определить какие ячейки нужной Вам области заняты данными (например пользователь выделил целый столбец, определив пересение столбца и UsedRange можно сократить число анализируемых ячеек). Но это работает не совсем верно. Дело в том что Excel запоминает максимальный размер UsedRange, и после удаления строк / столбцов уменьшения UsedRange не происходит. Заставить пересчитать UsedRange можно записав и открыв заново файл. А это не есть рулез. Кое-кто имеет свои макросы для определения реального "UsedRange". Еще более правильные макросы можно найти на Unofficial Excel Page, в разделе Spreadsheet Techniques , Getting the real UsedRange. Использование именнованной области в качестве базы данных Использование именнованных областей является стандартной практикой при работе в Excel. Вы можете использовать их например как таблицы базы данных. Вы можете обращаться к ним в функциях рабочего листа оперирующих с массивами - такими как ВПР(), ИНДЕКС(), ПОИСКПОЗ() и пр. Имена также можно применять в элементах управления. Например в ListBox. В дальнейшем при изменении размеров области (например при добавлении новой строки в БД руками) Excel автоматически отследит это изменение и Вам не надо будет писать код для изменения соответствующих атрибутов ListBox. Имена - это мощный инструмент в Excel . Через имена очень удобно осуществлять связь с данными на рабочем листе, которыми манипулирует пользователь. Это например может быть некоторая форма пользовательского ввода. Вас интересуют только значения в определенных полях формы, но не ее внешний вид и прочие "красивости". При использовании имен вы можете "отдать на откуп" пользователю (или дизайнеру этой формы) ее внешний вид, не ограничивать пользователя в перемещению по форме с использованием стандартных навигационных средств Excel. Можно даже говорить о новом стиле программирования в Excel (назовем его инструментальный стиль - instrumental style :) пользователь получает значительно большую степень свободы чем в стандартном классическом варианте который Вы можете наблюдать при обычном программировании. Воспринимайте Excel как среду в которой работает пользователь, и Ваша программа должна не заменять эту среду а являтся ее расширением, предоставляя пользователю дополнительные возможности. Тем самым Вы не противопоставляете свою программу Excel (и пользователю) а являетесь продолжением Excel и "садитесь на его конек". Примеры использования Instrumental Style Вы можете увидеть в моей программе (программа ? мне кажется это название уже не очень подходит для продуктов такого рода) печати платежного поручения . И еще более интересный в этом плане вариант для учета времени работы консультантов с клиентами. Так как сам файл не содержит документации вкратце опишу его здесь: Каждый консультанты работают с книгой Excel, в которой находится:
Файлы примера находится здесь.. Эти шаблоны должны быть брошены в каталог шаблонов офиса. Для начала работы создайте книгу из шаблона ConsultantBook.xlt Да, так о чем это я ? По поводу имен - при изменении размеров базы (именнованной области) из макро хорошо использовать метод Resize :
with range("YourBase")
.resize(.rows.count+1, .columns.count).name = "YourBase"
end with
Этот фрагмент увеличивает область "YourBase" на 1 строку. Поистине Excel неисчерпаем ...
Еще один hintпо поводу использования именнованных областей в качестве базы данных. Макро для отслеживания появления новых данных в диаграммах Этот файлсодержит код на VBA для отслеживания изменений в диаграммах. Макрос просматривает не появились ли новые строки данных для диаграмм. Если да, то расширяет диаграмму для включения новых данных. Если данные были убраны то соотвественно уменьшает серии в дианграмме. Диаграмма может быть также перестроена через Paste - выделите новый диапазон данных и бросте его на диаграмму. Можете записать макрос и посмотреть код. Как вызывать функции рабочего листа из VBA Как правило их можно вызывать в виде rez=Application.FuncName(), где FuncName - имя функции (например sin()). В Excel 8 используется специальный объект WorksheetFunction. Я же вызываю их через RUN("FuncName",...). Так как в VBA надо указывать анклийское наименование функции, а на рабочем листе используются русские названия то очень полезным бывает файл funcs.xls, содержащий таблицу соотвествия имен. Располагается как правило в каталоге \Excel. Как получить список файлов в каталоге Для этого существует функция VBA dir. Если Вас неудовлетворяет скорость ее работы на больших каталогах можно попробовать использовать функцию ДОС dir, обрабатывая файл с результатами ее работы. Почему Excel не печатает из окна диалога Функции Print и PrintPriview не работают при открытом пользовательском окне диалога. Как установить фокус ввода в окне диалога Для этого существует свойство объекта типа Dialog.Focus="Название". Можно также изменить направление обхода элементов. Как дождаться завершения программы запущенной функцией Shell() Функции Shell запускает указанное приложение не дожидаясь его завершения (асинхронно). Для проверки завершения запущенного приложения можно использовать оператор AppActivate. Формулы, вставленные из VBA, нерасчитываются при вставке. Их приходится пересчитывать. Что делать ? Макрос для пересчета ячеек только выделенной области Приходится делать F2, Enter :-) Некоторые додумались использовать "замену". Это конечно не решает проблему, но все-таки ... Я лично использую специальный макрос который апдейтит выделенное, выполняя для каждой ячейки метод Calculate: sub SelectionCalculate() set ss = Selection for each cl in selection.cells cl.Select cl.Calculate next cl ss.Select end subЭтот макрос полезен и тем что он показывает текущую пересчитываемую ячейку. Это нагляднее чем прогресс-бар в строке состояния при пересчете ячеек Excel'om. Я использую этот макрос в основном в книгах где выключен пересчет ячеек (там где много долгоиграющих формул). Иногда у Excel "съезжает крыша" и он начинает пересчитывать ячейки при любом изменении данных на листе. Приходится вырубыть автоматический пересчет ячеек (меню Сервис-Параметры-Вычисления-В ручную). И пользоваться этим макросом для пересчета того что надо. Как показала практика, если этот макрос не работает - (не пересчитывает ячейки или пишет нечто вроде #знач), то у Вас обнаружена циклическая ссылка и Excel "не хочет" пересчитывать значения. Ищите ссылку и уберите ее. Существуют специальные утилиты для поиска циклических ссылок, можете воспользоваться ими (ищите на авторских страницах, я видел кажется кажется у Stephen Bullen) По поводу невставляемости формул из в VBA - я _очень_ думаю что это результат наличия циклических ссылок. Потому что в нормальном случае все работает. Как передать диапазон в функцию VBA ? В функции VBA параметр должен быть объявлен как Variant, тогда на вход функции будет передан объект типа Range . Один способ установить add-in не используя Excel Ваша программа может бросить XLS файл в директорию автозагрузки Excel (там где живут разные personal.xls). А этот файл уже может сделать все что хочешь, например установить дополнение средствами VBA и подтереть себя ... Хитро? Как вставить свое изображение для кнопки в toolbar Для этого у объекта ToolbarButton есть метод PasteFace. Доступ к Excel через OLE из Visual Basic Смотри пример Андрея Кириенко - запуск из Visual Basic Excel'a и создания в нем рабочей книги. Доступ к Access из Excel Вот фрагмент кода, который создаёт таблицу "BalanceShifr" базе данных MS Access:
Примечание: Не забудьте выставить в Excel ссылки на объекты DAO! Доступ к Access из Excel через ini-файлы API для для взаимодействия Access и Excel'a через ini-файлы имени Emil Sildos. Собственно mLIni.bas файл здесь. Управление Excel'om через DDE Есть утилита для управления EXCEL из командной строки (или командного файла) через DDE. Команды можно записать в текстовом файле и выполнить его. Команды DDE соответствуют макро языку Excel 4 и из них нельзя вернуть значения. Но лучше использовать OLE, если это возможно. Как организовать Прогресс-Бар При длительной работе макро хочется как-то (желательно красивее) отобразить ход процесса - прогресс бар. Штатных средств в Excel для этого нет, поэтому каждый извращается как может ;). Вот несколько образцов:
Как избежать сообщений Excel при удалении листов и т.п. При выполнении тех или иных действий Excel может запрашивать подтверждения. Например при удалении листа, закрытии файла с несохраненными данными и (!) при проведении операции для которой Excel'y нехватает памяти для Undo. Для подавления этих сообщений используйте Application.DisplayAlerts: Application.DisplayAlerts = false .... Application.DisplayAlerts = trueЛюбопытный ход- можно сказать Excel что файл якобы сохранен и тогда он не станет возражать против его закрытия. Назначение макро на горячую клавишу Для это используется метод Application.OnKeys. Насколько мне известно такоим образом можно "сбросить" стандартные назначения клавиш, переопределив их на свой (пустой) макрос. Как определить последнюю запись в таблице Excel? Найти последнюю запись в электронной таблице можно из VB следующим способом: Application.SpecialCells(xlLastCell) Как отменить выделение диапазона ячеек ? После прекращения работы макроса диапазон остается выделенным. Как это выделение убрать? Можно попробовать следующее: Selection.Cells(1).SelectФокус ввода попадёт после этого на первую ячейку ранее выделенного диапазона. Вообще, решение состоит в прермещении выделения на какую-либо ячейку. Маленькой хитростью является выделение скрытой ячейки :) Удаление листов в зависимости от даты. Вот кодфункции на Excel VBA, который решает данную проблему. Подавление "горячих" клавиш. Как подавить доступ по "горячим" клавишам, имеется ввиду предопределенные в Excel клавиши типа Ctrl-O и т.д.? Попробуйте... Подсказки к Toolbar Как сделать к «само нарисованным» кнопочкам на Toolbar’е подсказки? (Ну, те, что после 2-х секунд молчания мышки появляются) Как определить адрес активной ячейки Как в макросе узнать и использовать текущее положение курсора (не мышиного, естественно)? Очень просто: Подсказки к Toolbar (Excel'95) Как сделать свой собственный Toolbar с tooltip’ами на кнопках в Excel’95? Вот фрагмент кодадля Excel'95, который создаёт toolbar с одной кнопкой с пользовательским tooltiр'ом. Нажатие кнопки приводит к выполнению макроса NothingToDo() Запуск Excel с поиском ячейки Как запустить Excel, чтобы оказаться на ячейке содержимое которой известно заранее? Можно Вот так. Как задать имя листу, который будет вставлен ? Хочy через Excel VBA задать имя листу, который будет вставлен. Но у команды Sheets.Add нет такого параметра! Как бороться ? Как проверить существует ли лист? Можно решить проблему следующим способом. Как обратиться к ячейке по ее имени ? Как обратиться к ячейки по ее имени? Т.е. есть Лист1 и в нем ячейки с именем Дебет и Кредит. Хочy подсчитать Дебет-Кредит средствами Excel VBA. Попробовал Range(Дебет)-Range(Кредит), ругается, что не описаны переменные. Если я правильно понял, нужно разыменовать ячейку из кода Excel VBA. Вот фрагмент кода, который решаеттакую задачу:
© Acidigital , 2001
|
|
|
|
|
