Несколько советов по работе с VBA в Excel


    Добрый день!

    Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.

    Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.


    Visual Basic


    Опции

    Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:
    OPTION BASE 0

    Так же рекомендуется прописать:
    OPTION EXPLICIT

    В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
    — VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
    — иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

    Ещё одним важным оператором является ON ERROR. Привожу варианты:
    ON ERROR RESUME NEXT ' продолжает со следующей строчки
    ON ERROR GOTO label: ' переходит, в случае ошибки, к метке label:
    ON EROR GOTO 0 ' возвращает обычое поведение.

    Возможности языка

    Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT'ах (аналог switch):
    SELECT CASE parametr
        CASE 1:
            ' do something'
        CASE 3 to 5:
            ' do something else'
        CASE 6, 8, 9:
            ' do something funny'
        CASE ELSE:
            ' do do do'
    END SELECT


    Ускорение работы макросов


    Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.
    Public Sub Prepare()
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        ActiveSheet.DisplayPageBreaks = False
        Application.DisplayStatusBar = False
        Application.DisplayAlerts = False
    End Sub
    
    Public Sub Ended()
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
        ActiveSheet.DisplayPageBreaks = True
        Application.DisplayStatusBar = True
        Application.DisplayAlerts = True
    End Sub

    По порядку:
    1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
    2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
    3. Не обрабатывать события.
    4. Отображение границ страниц, тоже почему-то помогает.
    5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
    6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

    Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:
    If Sheets("01").PageSetup.PrintArea <> "A1:D5" Then Sheets("01").PageSetup.PrintArea = "A1:D5"
    If Sheets("02").PageSetup.PrintArea <> "A1:E8" Then Sheets("02").PageSetup.PrintArea = "A1:A1:E8"


    Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
    Dim tCell As Variant
        
    For Each tCell In Sheets("01").Range("P16:Q19").SpecialCells(xlCellTypeFormulas)
        If tCell.Interior.ColorIndex = xlColorIndexNone Then
            tCell.Locked = True
            tCell.Interior.Color = RGB(220, 230, 241)
        End If
    Next tCell
    Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

    Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.

    Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.
    Sheets("01").Range("P15").AutoFill Sheets("01").Range("P15:Q15"), xlFillValues

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

    Загрузка книги и события


    При открытии книги каждый раз срабатывает процедура.
    Private Sub workbook_open()
        Dim sh as Variant
        Prepare
    
        ' Параметры печати
        For Each sh In ActiveWorkbook.Worksheets
            With sh.PageSetup
                If .Orientation <> xlLandscape Then .Orientation = xlLandscape
                If .LeftMargin <> Application.CentimetersToPoints(0.5) Then .LeftMargin = Application.CentimetersToPoints(0.5)
                If .RightMargin <> Application.CentimetersToPoints(0.5) Then .RightMargin = Application.CentimetersToPoints(0.5)
                If .TopMargin <> Application.CentimetersToPoints(1.5) Then .TopMargin = Application.CentimetersToPoints(1.5)
                If .BottomMargin <> Application.CentimetersToPoints(0.5) Then .BottomMargin = Application.CentimetersToPoints(0.5)
                If .HeaderMargin <> Application.CentimetersToPoints(0) Then .HeaderMargin = Application.CentimetersToPoints(0)
                If .FooterMargin <> Application.CentimetersToPoints(0) Then .FooterMargin = Application.CentimetersToPoints(0)
            End With
        Next sh
    
        Ended
    End Sub
    В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().

    Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.

    Защита


    Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

    Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.

    Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
    — выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
    — выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
    — а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

    Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
    myPassword = "123"
    For Each sh In ActiveWorkbook.Worksheets
       sh.Unprotect (myPassword)
       sh.EnableOutlining = True
    
       sh.Protect Password:=myPassword, _
            UserInterfaceOnly:=True, AllowSorting:=True, _
            AllowFiltering:=True, AllowFormattingRows:=True, _
            AllowFormattingColumns:=True, DrawingObjects:=False
    Next sh
    
    Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
    1. Сняли защиту.
    2. Включили группировку.
    3. Поставили защиту, при этом:
    — защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
    — разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
    — DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

    Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

    Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.

    Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:
    Application.OnKey "+{DELETE}", "ЭтаКнига.DelSelectedRow"

    Теперь процедура будет вызываться при нажатии shift+delete.
    Sub DelSelectedRow()
        If Selection.Rows.Count = 1 Then
            If Selection.Parent.Name = "01" And Selection.Cells.Count >= 1000 Then
                If Selection.row > 13 And Selection.row < 50 Then
                    Selection.Delete
                End If
            End If
        End If
    End Sub
    Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

    Заключение


    VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

    Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

    Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.

    Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 36

      –3
      Честно говоря, не понимаю, почему они до сих пор держатся за Basic. Эклектичный, тяжело читаемый язык. Когда-то очень много пришлось писать макросов для Word, и, думаю, любая альтернатива была бы _намного_ лучше.
        +6
        Быстро, просто, для выполнения нужен только пакет mso. Этого достаточно для бессмертия в его нише.
          +4
          К примеру, Javascript — отличная альтернатива.
            0
            Сомневаюсь, что пропустят JavaScript или любой другой «неродной» язык. В лучшем случае на VB или C# заменят.
              +1
              И зря.
                0
                та MS (и не только они) много чего зря не делают ;)
                  0
                  я не знаю каким точно боком, но й нас ребята прикручиваю JavaScript, причем юзают интерпритарот какой то встроенный, валяющийся в системной директории винды, имхо из IE.
                0
                C# — мечта, но это чрезмерно типизированный язык (хорош для своих целей), я бы не назвал его скриптовым. JavaScript был бы идеален, элегантный, распространённый и не такой ломанный, как VB.
              +2
              VBA уже редко используется. В основном все надстройки делаются в Visual Studio Tools for Office VSTO и пишутся либо на C# либо на VisualBasic.
              Но честно, написать прямо в Excel, нажав Alt+F11 намного проще и быстрее. Но только чтобы автоматизировать что-то не очень сложное.
                0
                О, спасибо за наводку.
                Я честно искал, как сделать в чем-то другом. Basic ладно, но ещё и редактор сам убогий, крайне неудобно работать в сравнении с современными Visual Studio!
                  0
                  VSTO есть начиная от 2005 Visual Studio. Правда я бы советовал пользоваться новыми лицензионными бесплатно по специальным программам Microsoft (как Bizspark, например).
                    0
                    Почитал, не очень понял как оно с совместимостью. Похоже сделанное в новой студии не будет работать, скажем, в MS Office 2003. У конторы, для которой я делал систему, много филиалов и нету единой IT (а зря), потому встречается всякое разное. VBA в этом смысле хорошо, а вы можете из своего опыта/знаний прокомментировать ситуацию с совместимостью VSTO-надстроек и где гарантии, что в 2014 офисе заработает то, что я сделаю для 2007?
              0
              Спасибо за статью. Кстати, программирование на VBA почему-то всегда рассматривают на примере Excel. Реже видел примеры для Word и Outlook, и совсем единичные случаи — PowerPoint.
              Если у вас есть ссылки на примеры на VBA для MS Office — поделитесь, пожалуйста! На любом языке. Нужно для написания диплома и книжки. В основном помогает только MSDN и внутренняя справка. Большинство справочников «VBA для чайников» являются «кратким излоЖением» справки VBA. Нужны практические примеры: например, TNS обрабатывает отчеты Excel и по определенному алгоритму строит презентации PowerPoint, подставляя данные в шаблоны слайдов.
                +1
                Самые лучший способ получить примеры на VBA — это записывать макросы и затем просматривать их код. Зачастую помогает лучше всякого хелпа.
                  +1
                  Не думаю, хотя для некоторых задач такой способ может подойти.
                  0
                  Набрал в поиске «excel to powerpoint vba», есть результаты, может можно и что-то уровня диплома найти.
                  Простейший пример — vbadud.blogspot.com/2006/07/vba-creating-powerpoint-presentation.html
                    0
                    Чаще всего VBA используется при написание программ на MS Access, очень удобная и простая штука для многих задач связанных с базами данных. Мне на Access удавалось написать довольно сложные базы данных.
                      0
                      При этом, что интересно, работа идет быстрее, чем в самом VB.
                      Я писал приложение генератор расписания по множеству критериев методом случайного поиска. VB.NET 2008 приложение отрабатывало за 3-5 минут, а перенесенное в Access — всего 10 секунд.
                        0
                        Это наверное зависит от способа доступа к базе данных, в MS Access идет прямой доступ к данным (чере COM интерфейсы), а в VB.NET через SQL запросы (ADO.NET), что значительно медленнее. В вашем случае нужно было все данные загрузить в память и там отработать, тогда VB.NET будет намного быстрее.
                          0
                          Все «данные» вбивались в форму настроек, а результат выводился в Grid только после того, как был расчитан.
                          Как я понял, дело тут было в лучшей работе с именно математикой, там же случайным образом генерировалось кучу-кучу всего, потом по правилам пересчитывалось всё остальное, проверки, и т.п. — плюс вывод в таблицу быстрее, Grid реально тормозной.

                          Может такое быть, на ваш взгляд? =)
                      0
                      Просто Excel многим нравится за возможность обработки большого количества данных — инженерные или бухгалтерские расчеты и т.д. В общем многие используют его как большой и мощный калькулятор. Потому под чаще всего и пишут.
                      0
                      Неплохой набор советов, спасибо!

                      >«Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.»

                      Кстати, для совсем начинающих вот ещё неплохая статья, как за 10 минут начать работать с VBA:
                      www.planetaexcel.ru/tip.php?aid=122
                        0
                        Пожалуйста :)

                        Сайт вообще хороший, читал его. Бросилось в глаза в приведенной вами статье когда пишут:
                        .Interior.ColorIndex = 6
                        Интересно, что ColorIndex — номер цвета в текущей палитре. Она вполне может отличаться от ожидаемой.
                        +1
                        Наши клиенты очень любят отчеты в экселе — делаем их на VBA, в самом макросе делаем запрос к SQL, затем данные выводим в эксель и форматируем. К стати, 1 замечание и совет:
                        1. Многие начинающие программировать в VBA в Excell запускают запись макроса и затем правят код, оставляя при этом, что то вроде
                          RANGE("A1").Select
                          Selection.Value="Привет мир"
                        

                        и если его немного поменять, то вывод будет намного быстрее
                         with  RANGE("A1")
                           .Value="Привет мир"
                         end with
                        


                        2. Небольшая хитрость для хранения SQL-запросов — создайте форму, разместите на ней textbox с установленной опцией Multiline=true, растяните его на всю форму и вставте туда запрос. Далее текст запроса можно получить так

                          SQLText=Form1.TextBox1.Text
                        

                          +1
                          Из замеченого
                          если вы захотите удалить строки из vba, то использовав при этом цикл с инкрементом:
                          for i=1 to n 
                          'удаление строки
                          next
                          

                          подсвечивает код пр
                          не приведёт к желаемому результату

                          Вместо этого используйте декремент
                          for i=n to 1 step -1 
                          'удаление строки
                          next
                          

                            0
                            А зачем удалять строки в цикле?
                            Rows("1:" & n).Delete
                            не подходит?

                            Удалять строки с шагом 1 нельзя потому, что после удаления строки i строка i+1 уже сама стала итой, таким образом можно было бы использовать цикл:
                            For i=1 to n
                                Rows(1).Delete
                            Next i

                            В данном случае переменная счетчика будет реально просто счетчиком.
                              0
                              а если нужно добавить условие, что удаляй каждую 2-й каждой 10-й строки?
                                0
                                Если каждую десятую тогда
                                For i=1 to shag*(howmany-1) step shag-1
                                    Rows(i).Delete
                                Next i
                                или около того, но для такого случая обратный отсчет будет проще, да. Я просто хотел прояснить, для каких ситуаций и почему это может пригодится, спасибо.
                                  0
                                  всем спасибо
                            0
                            Жаль, что ни один из 25-ти минусующих топик\карму не высказался и не помог мне в следующий раз написать статью лучше :(
                              0
                              Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров.


                              Это как?
                                0
                                Знаете, есть такие программы, которые водят мышкой и так автоматизируют любые приложения (часто боты для игр))? Примерно то же самое… Команды, которые доступны в VBA — это те же самые действия, которые выполняются пользователем. Пользователь нажал на ячейку — выполнился метод .Activate. Выделил — .Select. Поэтому, например, после выполнения макроса могут остаться выделнные области, листы могут переключиться (Sheets(5).Activate) и т.п.

                                При обращении к PageSetup эксель откроет окошко параметров страницы, изменит значения в полях и «нажмет» ок. Вместо того, чтобы как-то низкоуровнево поменять свойста страницы — такого нет! Надеюсь, понятно рассказал.
                              0
                              Не очень понятно, на самом деле. VBA обращается к объектной модели, для того, чтобы изменить какие-то значения, необязательно вызывать экранные формы.

                              В справке о PageSetup написано: «Returns a PageSetup object that contains all the page setup settings for the specified object. Read-only.». Вы хотите сказать, что при получении этого объекта у вас на экране рисуется форма настройки страницы?
                                0
                                Нет, форма, к счастью, не рисуется :)
                                В данном случае я ошибся, и причина жутких тормозов с PageSetup (как пишут в Интернете) в «обращении к драйверу печати» каждый раз, при каждом изменении. Но всё остальное рисуется :)
                                Если открыть файл Экселя с установленными областями печати на компе без принтера (какого-либо), вылезают ошибки. Какая-то завязка, видимо, есть.
                                +1
                                К SELECT CASE, если не ошибаюсь, ещё есть добавление:
                                CASE IS >= 5

                                Only users with full accounts can post comments. Log in, please.