как открыть код в экселе

Как в Excel 2010 или 2013 вставить и запустить код VBA – руководство для начинающих

Это краткое пошаговое руководство предназначено для начинающих пользователей и рассказывает о том, как вставлять код VBA (Visual Basic for Applications) в книгу Excel, и как запускать вставленный макрос для выполнения различных задач на этом листе.

Большинство пользователей не являются гуру Microsoft Office. Они могут не знать всех тонкостей работы той или иной функции, и не смогут ответить на вопрос, как отличается скорость выполнения макроса VBA в Excel 2010 и 2013. Многие просто используют Excel, как инструмент для обработки данных.

Предположим, нужно изменить данные на листе Excel определённым образом. Мы немало погуглили и нашли макрос VBA, который решает эту задачу. Однако, наше знание VBA оставляет желать лучшего. Вот тут-то и придёт на помощь пошаговая инструкция, с помощью которой мы сможем использовать найденный код.

Вставляем код VBA в книгу Excel

В этом примере мы будем использовать VBA макрос, который удаляет переносы строк из ячеек текущего листа Excel.

В самом начале кода Вашего макроса VBA должны содержаться строки:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Если таких строк нет, то обязательно добавьте следующие строки в свой макрос, чтобы он работал быстрее (см. рисунок выше):

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Запускаем макрос VBA в Excel

Чтобы запустить только что добавленный макрос, нажмите Alt+F8. Откроется диалоговое окно Макрос (Macro). В списке Имя макроса (Macro name) выберите нужный макрос и нажмите кнопку Выполнить (Run).

Источник

Как записать макрос в Excel? Пошаговая инструкция.

Что такое макрос?

Для начала немного о терминологии.

Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.

Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.

Отображение вкладки «Разработчик» в ленте меню

Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку «Разработчик». Для этого выполните следующие шаги:

В результате на ленте меню появится вкладка «Разработчик»

Запись макроса в Excel

Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например «Excel».

Вот шаги для записи такого макроса:

Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.

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

Вы увидите, что как только вы нажмете кнопку «Выполнить», текст «Excel» будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.

Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.

Что записывает макрос?

Теперь перейдем к редактору кода и посмотрим что у нас получилось.

Вот шаги по открытию редактора VB в Excel:

Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.

Когда мы записали макрос «ВводТекста», в редакторе VB произошли следующие вещи:

Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

Вот код, который записан макрорекодером:

Теперь давайте пробежим по каждой строке кода и опишем что и зачем.

Надеюсь, что у вас есть некоторое базовое понимание того, как записывать макрос в Excel.

Абсолютная и относительная запись макроса

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст «Excel» и затем перейдет к ячейке K5.

Теперь давайте запишем макрос в режиме относительных ссылок:

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

Вот код, который записал макрорекодер:

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Что нельзя сделать с помощью макрорекодера?

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

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

Источник

Приёмы отладки VBA-кода (на примере MS Excel)

Многие из тех, кто часто работает в MS Excel, используют книги или надстройки с VBA-макросами. Зачастую требуется доработать существующее решение, добавив новую функциональность или исправив найденный баг. В поисках места, где скрывается баг или производится нужное вычисление, можно провести немало времени, особенно если проект содержит десятки тысяч строк кода (увы, такие макросы иногда встречаются). Можно прибегнуть к текстовому поиску (например по тексту сообщения, вшитому в код), но иногда удобнее запустить макрос и прибегнуть к пошаговой отладке.

В этом посте описаны несколько приёмов, которые могут быть полезны при отладке макросов на VBA в приложениях MS Office на примере MS Excel.

1. Стандартные точки останова (breakpoint). Step-into, step-over

2. Окна Immediate («REPL-терминал»), Locals (локальные переменные), Watches (наблюдатели)

3. Условные точки останова на базе watch expression

4. Обработка ошибок

появится такая отметка (можно установить и кликом мышью по области слева, где красный кружок):

Теперь как только поток исполнения доберётся до этой строки, он будет приостановлен, а Вы увидите желтый указатель, указывающий на следующую инструкцию:

Многие не знают, что этот указатель можно перемещать на любой statement в пределах функции, просто перетащив мышью желтую стрелку!

Когда Вы приостановили исполнение кода, можете продолжить его исполнение пошагово, с помощью команд step-into, step-over и step-out:

Теперь немного о том, как узнать значения переменных и иную полезную информацию.

2. В нижней части экрана VBE (Visual basic editor) Вы можете увидеть три окна:

* Immediate (терминал для выполнения кода и вывода текстовой информации)

* Locals (локальные переменные)

Если Вы не видите эти окна, включите их отображение в меню View

Разберём подробнее, какие возможности открывают нам эти инструменты.

Окно Locals позволяет увидеть значения переменных, доступных в текущем контексте:

Выделите выражение и выполните команду Add watch. в контекстном меню (доступном с помощью правой кнопки мыши), или введите нужно выражение вручную в окне добавления наблюдателя:

Осторожно: выражение d.Item(«key1») при вычислении может добавить элемент к словарю, если такой ключ в словаре отсутствует. Поэтому не забудьте удалить наблюдатель, когда он станет ненужным, иначе в дальнейшем можно провести немало времени, выясняя откуда взялся лишний элемент в словаре.

3. Условные точки останова, которые можно реализовать с помощью наблюдателей.

4. Напоследок хотел бы немного рассказать об обработке ошибок в VBA.

Break on All Errors

Break in Class Module

Break on Unhandled Errors

Указанные опции обработки ошибок устанавливаются на уровне настроек пользователя, и не сохраняются в VBA-проекте.

Надеюсь, что эта статья окажется полезной как начинающим пользователям VBA, так и бывалым разработчикам, которые смогут узнать что-то новое.

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

Это мой первый пост на Пикабу, буду рад конструктивной критике в комментариях и ЛС.

Найдены возможные дубликаты

MS, Libreoffice & Google docs

442 поста 12.5K подписчик

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Dim ws as Worksheet

Set ws = ActiveWorkbook.Worksheets(0)

и дальше обращаться уже через него: ws.Cells

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

Спасибо за теплые слова!) В следующем посте хочу опубликовать туториал по созданию VBA-надстройки с кастомной панелью инструментов (Ribbon custom UI). Помню, в своё время когда делал, пришлось по крупицам собирать в интернете информацию

с удовольствием читаю посты про EXCEL, люблю в нём работать и что-то новое узнавать. а куда бы написать вопрос с помощью по экселю, если, конечно, не трубно помочь

Пишите тут, если вопрос не слишком большой для комментариев. Может быть, будет полезен и остальным.

ок, суть такова: работаю в офисной базе, делаю отчёт по прибыли по всем клиентам, к примеру, копирую таблицу с результатами в эксель, хочу сделать сортировку по убыванию прибыли, но цифры сортируются неправильно, от девятки к единице, а должно быть от большего числа к меньшему, я подозреваю, что в таблице суммы содержат ненужные пробелы, поэтому и не происходит правильная сортировка, но корректировать 500-800 строк это муторно, пробовал менять формат таблицы на разные значения, не помогает. есть ли какой способ быстро отформатировать таблицу для правильной сортировки? для примера прикладываю как сейчас эксель делает сортировку

Действительно, зеленые флажки в каждой ячейке указывают на то, что данные отформатированы как текст.
Первый вариант, ручной. Выделить нужный диапазон или столбец (чтобы не затронуть остальные данные на листе), Ctrl-H (найти и заменить), и заменить все пробелы на «ничего» (оставить второе окно пустым). В зависимости от региональных настроек на Вашем компьютере, также может понадобиться заменить запятые на точки (смотря какой десятичный разделитель).

https://github.com/navferty/NavfertyExcelAddIn
Там среди прочих функций есть и парсинг чисел из текстового формата.

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

А я до сих пор пишу программы на VB ’98 и мне не стыдно.

Полезные советы, спасибо

Кто шарит, есть что-нибудь лучше, чем закладки в документе OOXML для работы с ним программно?

Да, то, что нужно правильно понял. Только это нужно реализовать в таблице docx (Word) файла. Суть в том, что есть пул значений. В ячейке таблицы может быть значений не более, чем в пуле (уникальны на уровне ячейки таблицы). Количество определяет юзер (хз как сделать удобно) и значения юзер должен выбирать из выпадашки, которая фильтрует доступные значения на основе того, что уже было выбрано. Вопрос уже не актуален, потому что отдельной приблудой реализовываю, но все равно интересно. Чуть-чуть.

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

2. Налить в него вискаря

4. Повторять п. 2-3 до просветления

А вы батенька знаете толк в извращениях!

Макросы. Изучаем редактор VBE. Академия Excel

Давайте разбираться где происходит написание макросов? В Excel существует встроенный редактор Visual Basic Editor (VBE), в котором собственно и происходит создание макросов с нуля, редактирование существующий, удаление ненужных.

Посмотрев урок, Вы научитесь настраивать редактор под себя, познакомитесь с новым понятием Модуль (узнаете основные операции с ними).

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

Если в двух словах: отправляем принтеру команду с определённым идентификатором, получаем ответ.

На получение результата может уйти несколько минут.

Далее остаётся скопировать результат в блокнот (увы, поиск в SnmpB не работает) и найти текстовое значение счётчика, которое нужно предварительно подсмотреть либо через веб-интерфейс принтера, либо ещё как.

Дальше скучно: берём VBA, пишем

On Error Resume Next
Set o = CreateObject(«OlePrn.OleSNMP»)
o.Open IPaddress, «public», 2, 1000
Name = o.get(«.1.3.6.1.2.1.25.3.2.1.3.1»)
TotalPrintCounter = o.get(«.1.3.6.1.4.1.1347.43.10.1.1.12.1.1»)
TotalScanCounter = o.get(«.1.3.6.1.4.1.1347.46.10.1.1.5.3»)
o.Close
On Error GoTo 0

Источник

Автоматизация рутины в Microsoft Excel при помощи VBA

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Чуть-чуть подготовки и постановка задачи

Итак, поехали. Открываем Excel.

Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

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

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

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

Кодим

Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Напишем Hello World:

Sub FormatPrice()
MsgBox «Hello World!»
End Sub

И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i

Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x

On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne

ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок

‘ Циклы бывает, какие захотите
Do While True
Exit Do

Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

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

Кодим много и под Excel

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function

Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

Глобальные переменные

Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice()
Dim I As Integer ‘ строка в data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String

Теперь надо заполнить массив Groups:

На месте многоточия

И создать заголовки:

На месте многоточия в предыдущем куске

For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2

Не забудем про процедуру AddHeader:

Перед FormatPrice

Теперь надо перенести всякую информацию в result

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets( «Result» ).Activate
Columns.AutoFit

Всё. Можно любоваться первой версией.

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 ‘ Производитель
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub

Осталось лишь добится пропусков перед началом новой группы. Это легко:

В начале FormatPrice

Dim I As Integer ‘ строка в data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String

В цикле расстановки заголовков

If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer

В точности то, что и хотели.

Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

Источник