Remkomplekty.ru

IT Новости из мира ПК
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba примечание

Как получить текст примечания в ячейку?

Есть таблица по платежам. В ячейках определенных столбцов в примечаниях(вкладка РецензированиеСоздать примечание) записывается дополнительная информация по платежу. Например, номер договора, на основании которого был произведен платеж. И теперь необходимо отобрать записи только по определенным договорам. Ячеек несколько сотен, просматривать и выписывать договора из комментариев вручную похоже на одну из разновидностей древнеримских пыток. Однако при помощи VBA сделать это совсем просто.

Function Get_Text_from_Comment(rCell As Range) On Error Resume Next Get_Text_from_Comment = rCell.Comment.Text End Function

Синтаксис вызова функции с листа Excel:
=Get_Text_from_Comment( A1 )
A1 — ячейка с примечанием, текст которого необходимо получить. Если комментарий в ячейке отсутствует, то функция вернет пусто.
Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: необходимо скопировать текст кода выше, перейти в редактор VBA( Alt+F11 ) -создать стандартный модуль(InsertModule) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций, отыскав её в категории Определенные пользователем (User Defined Functions) .

Текст из примечания без автора примечания
Слегка доработанная функция, в которой можно отсечь имя пользователя, создавшего комментарий:

Function Get_Text_from_Comment(rCell As Range) As String Dim sTxt As String On Error Resume Next sTxt = rCell.Comment.Text Get_Text_from_Comment = Mid(sTxt, InStr(sTxt, «:») + 2) End Function

Синтаксис вызова с листа Excel такой же, как и в функции выше. Просто указываете внутри функции ссылку на ячейку.
=Get_Text_from_Comment( A1 )

Код записи текста примечаний в выделенных ячейках
Если текст из примечаний необходимо записать в те же ячейки одним махом и удалить после этого все примечания, можно использовать следующий код:

‘————————————————————————————— ‘ Author : The_Prist(Щербаков Дмитрий) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ http://www.excel-vba.ru ‘ Purpose: Процедура записи текста из примечаний в ячейки выделенного диапазона ‘————————————————————————————— Sub CommentsToCell() Dim sTxt As String, res As String, rc As Range, rr As Range Dim IsDelAuthor As Boolean, IsDelComment As Boolean, IsReplaceCellVal As Boolean ‘запрашиваем параметры If MsgBox(«Оставлять автора комментария?», vbQuestion + vbYesNo, «www.excel-vba.ru») = vbNo Then IsDelAuthor = True End If If MsgBox(«Заменять значение, если в ячейке с комментариями уже есть текст?» & vbNewLine & _ «ДА(Yes) — значения ячеек будут заменены текстом комментариев» & vbNewLine & _ «НЕТ(No) — к имеющимся значениям будет добавлен текст комментария», vbQuestion + vbYesNo, «www.excel-vba.ru») = vbYes Then IsReplaceCellVal = True End If If MsgBox(«Удалять комментарии после обработки?», vbQuestion + vbYesNo, «www.excel-vba.ru») = vbYes Then IsDelComment = True End If On Error Resume Next ‘получаем в выделенном диапазоне только ячейки с комментариями Set rr = Selection.SpecialCells(xlCellTypeComments) If rr Is Nothing Then MsgBox «В выделенном диапазоне нет ячеек с комментариями», vbCritical, «www.excel-vba.ru» Exit Sub End If On Error GoTo 0 Application.ScreenUpdating = False ‘цикл по всем ячейкам с комментариями For Each rc In rr.Cells sTxt = rc.Comment.Text If IsDelAuthor Then res = Mid(sTxt, InStr(sTxt, «:») + 2) Else res = sTxt End If If IsReplaceCellVal Then rc.Value = res Else rc.Value = rc.Value & Chr(10) & res End If Next If IsDelComment Then rr.ClearComments End If Application.ScreenUpdating = True MsgBox «Комментарии записаны», vbCritical, «www.excel-vba.ru» End Sub

Код необходимо так же скопировать и вставить в стандартный модуль(Переходим в редактор VBA( Alt+F11 ) —InsertModule).
Выделить диапазон ячеек, комментарии из которых необходимо перенести, нажать Alt+F8 и выбрать код CommentsToCell . Код содержит несколько параметров:

  • Сначала необходимо будет выбрать оставить ли автора комментария при считывании текста из комментария. Если выбрать да — весь текст примечания будет перенесен как есть. Если выбрать Нет — то из комментария будет отсечена первая строка до символа двоеточия(:). Именно так по умолчанию Excel обозначает автора.
    Этот параметр нужен, если в ячейках нет автора.
  • Далее будет запрос: оставить значение в ячейках и дописать к ним текст примечания или заменить существующие значения в ячейке на текст комментария. Может пригодиться, если в ячейках записаны суммы платежей и надо добавить к ним из комментария номер договора, не убирая сами суммы.
  • И последний запрос будет: удалять комментарии из ячеек после записи текста из них в ячейки или оставить. Если выбрать да — то после обработки всех выделенных ячеек комментарии будут удалены. Это может пригодиться, если комментариев много. Их удаление может существенно облегчить файл.

Статья помогла? Поделись ссылкой с друзьями!

Оформление кода VBA

Начиная практиковаться в написании кода VBA, очень важно с самого начала выработать хорошие привычки в оформлении кода, чтобы в дальнейшем написанный код было легко читать и понимать, как он работает.

Читать еще:  Работа в excel для начинающих

В процессе написания кода, программист может иметь совершенно чёткое представление о том, что за код он пишет и как этот код должен работать. Но нужно позаботиться и о том, чтобы, вернувшись к работе спустя полгода, не пришлось ломать голову, пытаясь понять, что должен делать этот код. Ещё более неприятная ситуация – когда кто-то другой станет продолжать Вашу работу над кодом и не сможет понять, как он работает.

Эта статья посвящена комментариям, отступам в коде и переносам строк – элементам, которые делают код аккуратным и понятным.

Комментарии в VBA

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

Комментарии не участвуют в процессе выполнения программы и не влияют на результат работы макроса. Каждая строка, начинающаяся апострофом (‘), будет считаться в VBA комментарием. Редактор VBA в Excel выделит такую строку зелёным цветом шрифта, чтобы с первого взгляда было понятно, что это комментарий, который не будет выполняться.

Ниже продемонстрировано, как при помощи комментариев поясняется работа простой процедуры Sub:

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

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

Отступы в коде VBA

Другой приём, делающий написанный код более читаемым – правильно расставлять отступы. В приведённом выше примере видно, что отступ сделан для кода внутри главной процедуры Sub и далее отступ увеличивается для каждого вложенного блока кода. Такие увеличенные отступы помогают понять, где каждый отдельный блок кода начинается и заканчивается.

Переносы строк в VBA

Ещё один способ сделать код более читаемым и облегчить работу с ним – делать переносы и разбивать одну длинную строку кода на несколько коротких. В VBA, чтобы разбить строку, нужно вставить символы ” _” (пробел+подчёркивание) непосредственно перед переносом строки. Это сообщает компилятору VBA, что текущая строка кода продолжается на следующей строке.

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

Посмотрите на этот оператор If:

При помощи переносов строк тот же оператор If может быть записан вот так:

Если рассмотренный оператор If разбит на четыре строки, то составляющие его блоки c условиями видны гораздо более наглядно. Этот пример иллюстрирует, как аккуратное оформление может сделать код более читаемым и привести в результате к меньшему количеству ошибок и путаницы.

VBA Excel. Начинаем программировать с нуля

Первое знакомство с редактором VBA Excel, создание процедур (подпрограмм) и написание простейшего кода, работающего с переменными и ячейками рабочего листа.

Знакомство с редактором VBA

  1. Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
  2. Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

В левой части редактора VBA расположен проводник проекта, в котором отображены все открытые книги Excel. Верхней строки, как на изображении, у вас скорее всего не будет, так как это – личная книга макросов. Справа расположен модуль, в который записываются процедуры (подпрограммы) с кодом VBA. На изображении открыт модуль листа, мы же далее создадим стандартный программный модуль.

  1. Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

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

  1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

  1. Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.
Читать еще:  Как почистить excel файл от мусора

Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.

  1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox «Привет» .

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

  1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.


Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений:

Макрос на VBA Excel – Формируем документы по шаблону

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

Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

Далее, нам необходимо присвоить имена полей для вставки, так более удобней к ним обращаться чем, например, по номеру ячейки.

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

  • ФИО – fio;
  • № — number;
  • Должность – dolgn;
  • Адрес проживания – addres;
  • Тел. № сотрудника – phone;
  • Комментарий – comment.

Код макроса на VBA Excel

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

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».

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

Читать еще:  Excel vba шрифт

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

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

Чтение и запись значения ячейки в VBA

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

Обращение к конкретной ячейке

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

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

  • С помощью Range
  • С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:

  • Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
  • Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
  • Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

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

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×