• Архив

    «   Декабрь 2018   »
    Пн Вт Ср Чт Пт Сб Вс
              1 2
    3 4 5 6 7 8 9
    10 11 12 13 14 15 16
    17 18 19 20 21 22 23
    24 25 26 27 28 29 30
    31            

Новая статья: Запуск макроса по времени

Весьма частый случай на практике: вам нужно запускать один или несколько ваших макросов в заданное время или с определенной периодичностью. Например, у вас есть большой и тяжелый отчет, который обновляется полчаса и вы хотели бы запускать обновление за полчаса до вашего прихода на работу утром. Или у вас есть макрос, который должен делать автоматическую рассылку сотрудникам с заданной периодичностью. Или, работая со сводной таблицей, вы хотите, чтобы она обновлялась "на лету" каждые 10 секунд и т.д.

Реализовать такое можно двумя способами:
  • макросом, используя метод OnTime из VBA
  • использовать Планировщик Задач (Task Scheduler), встроенный в Windows


У каждого метода есть свои плюсы и минусы. Давайте рассмотрим их подробно.

Читать статью полностью

Новая статья: Как создать свою надстройку в Excel



Запилил большую и подробную статью + аж получасовое видео с пошаговым подробным разбором всего процесса создания своей собственной надстройки в Microsoft Excel. Основные этапы:
  • создание файла надстройки
  • наполнение его макросами и пользовательскими функциями
  • различные способы запуска макросов надстройки
  • создание отдельной вкладки с кнопками для надстройки
Так что теперь вы можете запросто сделать свой вариант PLEX ;)

Читать статью полностью

Новая статья: Последнее слово

 Среди задач работы с текстом, которые мы неоднократно уже разбирали, немного особняком стоит вопрос извлечения из текста последнего слова или фрагмента по заданному разделителю. Вытащить первое слово - не проблема, а вот с последним все не так легко и очевидно, т.к. количество слов в каждой ячейке может различаться. Давайте разберем несколько принципиально разных способов это сделать, а именно:
  • формулы
  • макросы
  • обновляемый запрос через Power Query



Читать статью полностью

Новая статья: Личная Книга Макросов в Microsoft Excel

Написал подробную статью о том, как создать и использовать Личную Книгу Макросов - удобное хранилище персональной коллекции макросов, рано или поздно образующейся у каждого уважающего себя пользователя Excel :)



Читать статью полностью

Новая статья: Генерация дубликатов строк

В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!


Сделать это можно двумя основными способами: при помощи макросов и через Power Query.

Читать статью полностью

Новая статья: Поиск совпадений в двух списках

Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов - подсчет количества и вывод совпадающих значений в двух списках. Подробно разберем три основных подхода для решения этой распространенной задачи:
  • формулы
  • макросы
  • Power Query


Читать статью полностью

Новая статья: Независимая группировка сводных таблиц

Что делать, если вы построили несколько сводных таблиц на основании одного источника и теперь не получается группировать их отдельно - группировка одной сводной влияет на все остальные и наоборот. Как отвязать сводную от общего кэша? Подробно разобрал три варианта:
  • Построить сводную с помощью Мастера, кнопку для которого нужно сначала вытащить в интерфейс
  • Отвязать уже имеющуюся сводную от общего кэша руками
  • Использовать готовый макрос


Читать статью полностью

Новая статья: Запрет распечатки книги Excel

Если, по какой-то причине, вам необходимо запретить вывод на печать информации из книги Excel, то можно воспользоваться простым макросом.



Читать статью полностью

Новая статья: Копирование настроек печати с листа на лист

Если вы когда-нибудь мучились вопросом, как перенести настройки печати (параметры страницы) с одного листа на другие в книге Excel, а не воспроизводить их вручную для каждого листа, то самое время прочитать эту короткую статью. Там делов-то на полминуты, если знать как :)



Чуть сложнее придется, если нужно скопировать области печати и сквозные строки-столбцы - потребуется простой макрос, который тоже приведен в статье.

Читать статью

Новая статья: Преобразование формул в значения

Думаю, подавляющее большинство читателей этого сайта и блога сталкивались с одной из самых частых задач в повседневной работе любого пользователя Excel - превращение формул в ячейках в значения. Выложил статью с описанием 4 способов это сделать - выбирайте на свой вкус :)



Читать статью целиком

Новая статья: Слишком много форматов ячеек

Написал статью про то, как бороться с ошибкой "Слишком много различных форматов" в Excelи со "стилевым адом", когда после многочисленного копирования фрагментов из других книг в ваш файл в списке стилей накапливается стилевой "мусор":


Такие вещи здорово замедляют вашу книгу и утяжеляют ее, но их можно легко победить с помощью небольшого макроса.

Читать статью полностью

Открытый тренинг по программированию макросов на VBA



12-14 ноября 2014 г. буду вести открытый тренинг по программированию макросов на VBA в Excel в "Центре Мастеров" на Бауманской.

Если вы:
  • слышали про макросы много хорошего, но никогда не создавали их сами
  • записывали простые макросы рекордером, но на сложных дело стопорилось
  • используете чужие макросы в режиме "нажми на кнопку - получишь результат", но всегда хотели уметь их менять
  • давно хотели наконец разобраться с тем, что такое макросы и научиться их программировать
то - приходите!

В программе:
  • подробный разбор основ программирования на Visual Basic - простыми и понятными словами
  • ознакомление с внутренней объектной моделью Excel, чтобы легко ей управлять
  • изучение методов, свойств и событий всех основных объектов (книг, листов, ячеек) на живых примерах
  • способы организации проверок условий и различных видов циклов в макросах
  • настройка ввода-вывода и диалога макроса с пользователем
  • обработка событий, чтобы уметь "вешать" макросы на автоматический запуск при срабатывании определенных условий
Посмотреть подробную программу тренинга

Программа тренинга идентична дорогим корпоративным тренингам, которые я читал для сотрудников Рольф, Beeline, МТС, Globus и других компаний.

Тренинг будет проходить три дня с 12 по 14 ноября с 10 до 17 часов в компьютерном классе "Центра Мастеров" недалеко от м.Бауманская
Стоимость - 24 тыс.руб.
Осталось 5 мест.

Узнать подробности и забронировать себе место можно по тел. +7 (495) 775-31-94

Новая статья: История изменения ячейки в примечаниях

На одном из тренингов недавно спросили, как можно сохранять историю изменения отдельных ячеек на листе. Встроенный в Excel глючный режим совместного доступа с отслеживанием изменений всех ячеек был не нужен, так что пришлось импровизировать и реализовать все небольшим макросом:



Кому интересно - почитайте.

Новая статья: Склеивание текста по условию

Про то, как можно быстро склеивать текст из нескольких ячеек в одну и, наоборот, разбирать длинную текстовую строку на составляющие я уже писал. Теперь же давайте рассмотрим близкую, но чуть более сложную задачу - как склеивать текст из нескольких ячеек при выполнении определенного заданного условия. Что-то вроде СУММЕСЛИ, но только для текста, чтобы получить на выходе что-то похожее на:



Читать статью целиком

 

Открытый тренинг по программированию макросов на VBA в Excel

На следующей неделе буду вести открытый тренинг по программированию макросов на VBA в Excel на базе "Центра Мастеров" недалеко от м.Бауманская.



В программе - превращение за три дня всех участников в полноценных программистов на Visual Basic, способных с помощью VBA решать сложные задачи автоматизации (половину PLEX разберем прямо на занятиях точно).

Разберем:
  • Теоретические основы программирования
  • Объектную модель Excel, чтобы грамотно им управлять
  • Основные функции VBA
  • Концепцию событий, чтобы их перехватывать
Научимся:
  • писать процедуры и функции
  • использовать различные объекты Excel
  • оперировать с диапазонами, листами и книгами
Подробная программа тренинга тут.

Занятия пройдут три дня подряд 16-18 апреля с 10 до 17 часов по адресу ул.Бакунинская, 71, стр.10, 3 этаж (карта тут).
Стоимость 24000 р. (для физлиц) и 28500 р. (для корпоративных участников).

Узнать подробности и записаться можно по телефону +7 (495) 775-31-94

Хак: скажите, что от меня - получите скидку ;)

Update

Тренинг прошел на ура, большое спасибо всем участникам и организаторам! А вот как все было:





Документация по Office 2013 VBA

Любой VBA-программист подтвердит, что основная клавиша во время работы на клавиатуре - это F1. Она вызывает встроенную справку и позволяет получить подробную информацию о любой выделенной команде или объекте в коде (которых - сотни и помнить их все просто невозможно).

В последних версиях Office встроенная справка по Visual Basic начала заменяться онлайн-справкой с сайтов www.office.com и www.msdn.com. В Excel 2010 еще можно было выбрать - откуда брать справку, а в Excel 2013 такого выбора уже нет - он по умолчанию при нажатии F1 лезет в интернет за содержимым справочных материалов. А интернет, как легко догадаться, не всегда имеется. Да и содержимое такой онлайн справки в 2013 было существенно беднее, чем для Excel 2010 и 2007.

Так вот буквально вчера Microsoft выложила в своем Download Center в открытый доступ новую документацию (оффлайн-справку в формате chm) по Office 2013. Для скачивания доступны на выбор файлы по Excel, Access, OneNote, Outlook, Word и т.д.

Вот так, например, выглядит файл по Excel(9.4 Мб):



В левой панели, как видите, есть удобное многоуровневое оглавление, индекс, поисковая система и т.д.

Только не забудьте после скачивания щелкнуть правой кнопкой мыши по файлу и выбрать Свойства (Properties) и разрешить доступ к файлу с помощью кнопки Разблокировать (Unblock), иначе в правой панели будет пусто:



Скачать offline-справку по Office VBA

Новая статья: Фильтр исходных данных в сводной таблице

Выложил новую статью с видео про то, как отфильтровать именно те строки в исходных данных, которые участвуют в вычислении заданной ячейки результата сводной таблицы. Всем подгоняльщикам оптимизаторам посвящается ;)

Новая статья: 3D в Excel

Как при помощи простого макроса сделать необычный подароксвоей половине на День Святого Валентина :)

Игрушки на макросах в Excel

Наткнулся в сети на неплохую реализацию судоку на макросах в Excel:



Причем код всех макросов открыт, так что можно совместить полезное с приятным - и поиграться, и поизучать VBA.
Скачать можно отсюда - http://www.psawyer.de/VBA/SudokuExcel.zip

Кстати, игр в Excel через поисковики находится много - есть даже Doomи SuperMario. Но подавляющее большинство из них -  совершенно стандартные игрушки на flash, просто вставленные на лист. Неспортивно. А вот настоящих, написанных на VBA, да еще и с открытыми кодами макросов - по пальцам пересчитать.

Update: Вот еще простенький морской бой нашел - http://www.gamesexcel.com/download/battleships.rar
 

Pacman на макросах в Excel

Нашел приятное - старый добрый Pacman, реализованный на VBA в Excel:

6548a8b6501fd10e53b7fff1c2bdf442.png

Сделал David Sobrinski.
Хороший способ вспомнить молодость, убить время или попрактиковать программирование макросов на VBA в Excel (коды открыты - изучайте на здоровье).
Скачать можно тут

Мерзкий глюк с выпадающими списками

При работе над очередным проектом обнаружил весьма неприятный глюк с выпадающими списками в Excel.

Если при создании списка в качестве источника (Source) задавался не диапазон ячеек, а текст, то его длина (оказывается!) ограничена 8192 (2 в 13-й степени) символами. Причем при превышении этого значения файл (или макрос, если это делалось в нем) вылетает с ошибкой, а после перезапуска - умирают все выпадающие списки во всех ячейках листа, где было превышение. Нифига себе подарочек.
Вот простой макрос для желающих испытать на себе. Вылетает четко на 8192 символе любого списка и рушит все другие списки листа:
Sub Test_Dropdown_Limit()   
   For i = 1 To 10000   
      With [a1].Validation   
         l = l & "A,"  
         Debug.Print i & " " & Len(l)   
        .Delete   
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, Formula1:=l   
       End With  
   Next i   
End Sub  

Игры для Microsoft Excel 2010

В любой работе есть место празднику. Классика жанра - игры Missle Command и Tower Defense для Excel 2010 на макросах от разработчиков Excel из команды Microsoft:



Скачать игрушки можно здесь (150 Кб)

Короткая запись Range в коде VBA

Оказывается при написании макросов в коде VBA можно вполне себе успешно использовать сокращения для очень распространенного оператора Range, определяющего диапазон листа для выполнения с ним каких-либо действий. Так, например, стандартная конструкция вида:
Range("F3").Value=45

может быть коротко записана всего лишь как:
[F3]=45

Т.е. квадратные скобки заменяют оператор Range, скобки и кавычки.
Такая форма записи работает во всех вариантах, где работает стандартный оператор Range, т.е. например:

  • [a1:b3, f5, h:h].ClearContents - очищает содержимое диапазона A1:B3, ячейки F5 и столбца H,
  • Worksheet("Лист1").[D3].Font.ColorIndex = 3 - делает текст в ячейке D3 первого листа красным,
  • MsgBox [SalesData].Address - выводит на экран сообщение с адресом именованного диапазона SalesData и т.д.
Удобно.
Сколько лет программирую, а не перестаю удивляться таким вот периодически встречаемым малоизвестным фишкам. Нет предела совершенству.
Наверх