Выключение отображения изменений на экране
Если Ваш макро интенсивно что-то отображает на экране (например в цикле
выделает ячейки, вставляет данные, etc.) Вы можете увеличить скорость работы
макро выключив обновление экрана: Application.ScreenUpdating
= False. В этом письме есть также советы как писать макро так чтобы они
работали побыстрее. Грабли при
использовании ScreenUpdating в функциях в Excel 97
В Excel 97 возможны проблемы при установке свойства
Application.ScreenUpdating из пользовательской
функции. Да что там из пользовательской - теперь оно несбрасывается
автоматически как раньше. Почему на
листе модуля В качестве разделителя используется символ ';' а не ','
Как определить разделитель целой и дробной части и прочие
международные установки.
Это зависит от настроек Excel в меню Сервис/ Параметры/
Модуль/ РазделительСписков. Для извлечения текущих значений используйте
свойство Application.International.
Можно определить десятичный разделитель, разделитель тысяч и пр.
Как определить есть ли комментарии на рабочем
листе
Надо использовать коллекцию comments..
Как защитить лист от пользователя, но не от
макро
А для этого есть специальная фича UserInterfaceOnly.
Чем отличается ThisWorkBook от
ActiveWorkbook
Когда выполняется код настройки активной книгой является не сама надстройка.
Как вычислить формулу в макро
Если у Вас есть строка с формулой, Вы можете вычислить ее при помощи
функции 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, в которой находится:
- Лист формы, заполняемый консультатом при совершении акта консультации
клиента :)
- Лист реестра всех работ проделанных консультантом
- Лист реестра работ по конкретному клиенту всех консультантов
При
нажатии на кнопку "Сохранить" происходят некоторые действия :) Поведение
программы при этом полностью определяется именами ячеек:
- Происходит проверка на непустые значения (имена "nonempty")
- Проверка наличия баз куда будут копироваться записи (одна форма может
вставлять записи в несколько таблиц). Используются имена "bd_name"
- Копирование данных из полей формы на листы баз. Данные вставляются в
столбцы базы в соотвествии с именами "bd_scol".
- Листы баз представляют собой некоторым образом "отчеты", имеющие
заголовок, тело и footer (не знаю как это будет по
русски). записи добавляются в "тело", при этом для форматирования вставляемой
записи используется скрытая область "OneRow". Копирование производится в
область листа начало которой помечено как "StartDB".
- Лист формы переходит в "архивное" состояние, для чего ему присваивается
уникальное имя (содержимое именнованных ячеек "name"). Лист покрывается
защитой от изменений. Из листа формы вычищаются все имена для "облегчения"
книги (зачем хранить ненужную информацию).
- Последние использованные данные сохраняются в "невидимых" именах для их
последующего дефолтного вставления в новый шаблон формы.
Для
добавления новых листов формы и пр. созданы соотвествующий шаблоны. Используются
в подавляющем своем большинстве локальные имена, что приводит к тому что в одной
книге можно иметь произвольное число форм и баз.
Файлы примера находится здесь.. Эти
шаблоны должны быть брошены в каталог шаблонов офиса. Для начала работы создайте
книгу из шаблона 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 через
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.
Насколько мне известно такоим образом можно "сбросить" стандартные назначения
клавиш, переопределив их на свой (пустой) макрос.
Страница 1 2 3 4 • <<< Предыдущая • Следущая >>>
|