Darbe.ru

Быт техника Дарби
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Создание макроса и работа с ним

Создание макроса и работа с ним

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

В Microsoft Word 2007 это осуществляется следующей последовательностью действий:

нажать кнопку Office в верхнем левом углу, нажать кнопку "Параметры Word";

В Microsoft Word 2010/2013/2016:

Начало создания макроса

Макрос для отчетов формата Word

Для того, чтобы создать или просмотреть макрос для отчета формата Word необходимо:

Если макроса там не будет, его можно создать самостоятельно, введя в данном окне строки начала и конца макроса, а между ними — сам текст макроса:

Sub ПослеВыполненияОтчета(ob As Variant, app As Variant)

Внимание! В Word 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:

нажать кнопку Office в верхнем левом углу, нажать кнопку "Параметры Word";

Макрос для отчетов формата Excel

Для того, чтобы создать или просмотреть макрос для отчета формата Excel, необходимо:

Внимание! В Excel 2007 закладка «Разработчик» по умолчанию скрыта. Чтобы вывести ее на показ необходимо:

нажать кнопку Office в верхнем левом углу, нажать кнопку "Параметры Excel";

Макрос "ПослеВыполненияОтчета"

Любой отчет изначально содержит следующий макрос:

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

Переменные, которые передаются в макрос:

Внимание! Просмотреть объектную модель Word для разработки макросов в редакторе Visual Basic можно по клавише F2.

Название привязки для макроса

При решении ряда задач средствами программирования бывает удобно оперировать данными привязок отчета. Для этого необходимо:

Ниже описано, как это это делается для привязок разных типов.

Тип привязки: Объект

В отчете такие привязки представлены полями.

Использование названия привязки в коде:

Тип привязки: Список и Фильтр

В отчете такие привязки представлены таблицей.

Использование названия привязки в коде:

Работа с таблицами

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

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

Работа с таблицей через название привязки

Работа с таблицей через её номер в документе

При работе с таблицами следует помнить ряд моментов:

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

Определение направления формирования отчета

Один и тот же отчет Business Studio может рассматриваться из разных мест:

Также могут быть и разные задачи по обработке отчета кодом в зависимости от направления формирования отчета. Для определения направления формирования отчета используются параметры: BSInThread, BSHtml и BSPortal.

Направление формирования отчетаBSInThreadBSHtmlBSPortal
Одиночный файл (Word, Excel)FalseFalseFalse
Пакетное формирование (Word, Excel)TrueFalseFalse
HTML -публикацияTrueTrueFalse
Business Studio PortalTrueTrueTrue

Пример использования в коде:

Работа со ссылками в отчетах для HTML

Для "нормализации" отображения данных следует сначала использовать следующий код:

Работа с копией отчета, содержащей макрос

При копировании отчета названия всех привязок изменяются.

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

Читайте так же:
Как вернуть пск в автокаде

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

Пример проверки наличия привязки типа "Список". В документе это закладки.

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

Пример проверки наличия привязки типа Объект

Определяем название привязки типа "Объект", которая представляет нужное поле в отчёте (см. статью Руководство пользователя → Пример определения названия привязки типа "Объект").

Обращение к данным модели бизнес-архитектуры

Из отчета можно напрямую обращаться к данным модели бизнес-архитектуры. Это делается двумя способами:

Обращение к параметрам объекта

Обращение к параметрам объекта, от которого был вызван отчет, происходит таким образом:

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

Использование механизма OLE

Механизм OLE предоставляет более широкие возможности по обращению к данным модели бизнес-архитектуры и выводу этих данных в отчетах по сравнению с обращением просто как данным объекта, от которого был вызван отчет.

Описание механизма OLE подробно описано в главе Руководство технического специалиста → Работа с Business Studio через OLE. Для задействования OLE в отчетах конкретной базы нет необходимости во включении опции "Разрешено использование OLE".

Внимание! В отчетах при работе с OLE недоступны все методы класса "Система.OleApplication", поскольку в макросе "ПослеВыполненияОтчета" есть аргумент "app", который является приложением Business Studio, а не "Система.OleApplication".

Несколько задач в одном макросе

Бывают ситуации, когда:

В таких случаях каждую решаемую задачу удобно оформлять отдельным макросом. А в ключевом макросе "ПослеВыполненияОтчета" лишь вызывать по очереди нужные макросы. Все эти макросы оформляются в том же окне, где и ключевой макрос и делается так:

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

Использование этой функции из макроса может быть таким:

Отладка кода

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

Примеры решения задач с использованием макросов

Примеры работы с отчетами при использовании макросов приведены в статье Примеры решения задач.

Open Notes

Пополняемый список полезных отрывков кода VBA для выполнения часто востребованных действий в MS Excel.

Служебные команды для ускорения скорости выполнения макроса:

‘Отключение отображения выполняемых действий Application.ScreenUpdating = False ‘Предотвращение появления предупреждающих сообщений Application.DisplayAlerts = False ‘Предотвращение появления предупреждения об обновлении связей данных Application.AskToUpdateLinks = False ‘Очистка буфера обмена Application.CutCopyMode = False

Проверка имени пользователя, запустившего макрос:

Чтобы проверить, какой пользователь открыл книгу Excel можно использовать один из следующих вариантов:

If Application.UserName = "Имя_автора_документа" Then .

Поиск последней строки таблицы:

Set myWSheet = ThisWorkbook.Sheets("Имя_листа") With myWSheet ‘Определение индекса последней строки таблицы lastRow = .Cells(Rows.Count, 1).End(xlUp).Row ‘Определение значения в ячейки последней строке столбца A lastARow = .Range("A" & lastRow).Value End With

Читайте так же:
Как в ворде сделать сетку клетки

Замена формулы на значение:

Добавление нового листа с именем после всех существующих:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "Имя_листа"

Как узнать последний день предыдущего месяца:

LastMonthDay = DateAdd("d", -1, DateSerial(Year(dtDate), Month(dtDate), 1))

Определение оставшихся дней месяца:

dToEndOfMonth = DateDiff("d", dFrom, DateAdd("d", -1, _ DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))

Номер текущего дня в неделе (воскресенье — первый день):

DayOfWeek = DatePart("w", dToday)

Создание нового файла из текущего:

pathNewBook = "C:Temp" nameNewBook = "Имя_нового_файла.xls" Workbooks.Add ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook ActiveWorkbook.Close True

Сохранить текущий файл в формате CSV

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

ActiveWorkbook.SaveAs FileName:="Name.csv", FileFormat:=xlCSV, _ CreateBackup:=False, Local:=True ActiveWorkbook.Saved = True ActiveWorkbook.Close True

Копирование данных из одного файла в другой:

wbPath = "C:Temp" wbName = "Имя_файла_откуда_копируем.xls" Workbooks.Open (wbPath & wbName) Set WB = Workbooks(wbName) WB.Sheets("Лист 1").Range("A1:С10").Copy Sheet("Лист_в_текущем_файле").Range("A2").PasteSpecial xlPasteValues

Чтобы открыть файл только для чтения, следует использовать:

Workbooks.Open (Filename:=wbPath & wbName, ReadOnly:=True)

Предотвращение ошибки при неудачном поиске значения в таблице:

Set DateRowObj = WB.Sheets("Имя_листа").Range("A:A")._ Find(What:=dtToAsDate, LookIn:=xlFormulas) If (DateRowObj Is Nothing) Then WB.Close False MsgBox "Данные не найдены." Else DateRow = DateRowObj.Row ‘Номер строки с искомым значением End If

Как получить имя активной книги Excel без его расширения (без .xls либо без .xlsx):

wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") — 1)

Проверка существования файла:

fPath = "C:Temp" fName = "Файл.txt" If Dir(fPath & fName) = "" Then MsgBox "Файл не найден:" & Chr(13) & fPath & fName Exit Sub End If

Кнопка, скрывающая/разворачивающая часть таблицы:

Private Sub tbVid_Click() Application.ScreenUpdating = False If tbVid Then tbVid.Caption = "Скрыть" ActiveSheet.Rows("2:29").Hidden = False Else tbVid.Caption = "Развернуть" ActiveSheet.Rows("2:29").Hidden = True End If End Sub

Обновление сводной таблицы:

currPath = ThisWorkbook.Path currWBName = ThisWorkbook.Name ListName.PivotTables("СводнаяТаблица1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & "[" & _ currWBName & "]Лист1!R1C1:R10C5")

Обращение к элементам Frame:

Замена #ДЕЛ/0! в диапазоне:

Selection.Replace What:="#DIV/0!", Replacement:="", LookAt:=xlPart,_ SearchOrder:=xlByRows, MatchCase:=False,_ SearchFormat:=False, ReplaceFormat:=False

Количество строк в отфильтрованной таблице:

Быстро убрать лишние пробелы в диапазоне:

Программно снять защиту с листа:

Работа с диапазоном

Умножить диапазон на число:

ThisWorkbook.Sheets(1).Range("A1:A10") = _ ThisWorkbook.Sheets(1).Evaluate("A1:A10" & "*80")

Добавить ко всем значениям диапазона строку:

Сортировка выбранного столбца в сводной таблице

Col = Selection.Column ‘Номер выбранного столбца ColMax = ActiveSheet.PivotTables("СводнаяТаблица").PivotColumnAxis. _ PivotLines.Count If Col — 1 <= ColMax And Col 1 Then ActiveSheet.PivotTables("СводнаяТаблица").PivotFields("Label").AutoSort _ xlDescending, " ", ActiveSheet.PivotTables("СводнаяТаблица"). _ PivotColumnAxis.PivotLines(Col — 1), 1 End If

Счетчик времени выполнения процедуры

‘Счётчик, ставится в начале процедуры StartUpdDate = Now ‘Сообщение, выводится в конце процедуры MsgBox "Данные обновлены за " & Fix(1440 * (Now – StartUpdDate)) & " мин. " & 86400 * (Now – StartUpdDate) Mod 60 & " сек."

Функция транслитерации с русского на английский

Поиск файлов в папке

Dim strDirPath, strMaskSearch, strFileName as String strDirPath = "C:/test/" ‘Папка поиска strMaskSearch = "*.xls*" ‘Маска поиска ‘Получаем первый файл соответствующий шаблону strFileName = Dir(strDirPath & strMaskSearch) Do While strFileName <> "" ‘До тех пор пока файлы "не закончатся" MsgBox strFileName strFileName = Dir ‘Следующий файл Loop

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

VBA циклы — For Next и For Each в Excel

vba цикл excel for each for next

Цикл For Loop в VBA – один из самых популярных циклов в Excel. Данный цикл имеет две формы – For Next и For Each In Next. Данные операторы используются для последовательного перемещения по списку элементов или чисел. Для завершения цикла мы можем в любой момент использовать команду выхода. Давайте подробнее рассмотрим каждый из этих циклов.

Читайте так же:
Можно ли запустить видеокарту без дополнительного питания

VBA цикл For Next

Цикл For Next имеет следующий синтаксис:

То что мы делаем здесь, по существу, это создаем цикл, который использует переменную счетчик как хранитель времени. Устанавливаем его значение равным начало_счетчика, и увеличиваем (или уменьшаем) на 1 во время каждого витка. Цикл будет выполняться до тех пор, пока значение счетчик не станет равным конец_счетчика. Когда оба эти значения совпадут, цикл выполнится последний раз и остановится.

Последнее значение переменной счетчик будет равным 11

VBA обратный цикл For Loop с инструкцией STEP

Если у вас появилась необходимость перемещаться от большего значения к меньшему – вы можете использовать цикл в обратном направлении. Вот пример обратного цикла:

Последнее значение переменной счетчик будет равным 1.

Как вы могли заметить, мы можем использовать инструкцию Step n для работы цикла как вперед, так и в обратном направлении. По умолчанию значение Step равно 1, но оно может быть изменено, если необходимо пропускать какие-либо значения, тогда значение n будет больше одного, или перемещаться в обратном направлении, тогда n будет отрицательным.

VBA цикл For Each … Next

Цикл For Each … Next имеет следующий цикл:

Здесь переменная элемент_группы принадлежит к группе_элементов (железная логика. ). Я имею в виду, что объект группа_элементов должен быть коллекцией объектов. Вы не сможете запустить цикл For Each для отдельно объекта (Microsoft сразу оповестит вас об этом 438-й ошибкой).

vba циклы excel for each for next

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

Ниже представлен пример, как можно воспользоваться циклом For Each для просмотра всех листов книги:

… либо всех сводных таблиц на листе

Прерывание цикла VBA

Если вам необходимо выйти из цикла до момента, как будет достигнуто условие завершения цикла, воспользуйтесь командой End For в связке с инструкцией IF. В примере, приведенном ниже, мы выйдем из цикла до момента достижения условия завершения, в данном цикле выход будет осуществлен при условии, когда переменная счетчик будет равна 3.

Пропуск части цикла в For Each

Пропускать часть цикла, а затем возвращаться назад – плохая практика. Тем не менее, давайте рассмотрим пример:

Здесь мы пропустили одну итерацию (когда j = 3). Как вы думаете, какой результат выдаст программа? 3? 5? Ну… на самом деле, ни один из вариантов не верный. Цикл будет выполняться бесконечно, пока память компьютера не переполнится.

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

Но опять же, это плохая практика написания кода, и может привести к нежелательным последствиям при написании кода в будущем. Вместо этого, при необходимости пропуска некоторых итераций, попробуйте использовать функцию If или Select Case.

Вам также могут быть интересны следующие статьи

5 комментариев

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

Читайте так же:
Маркер заполнения в excel

>Последнее значение переменной счетчик будет равным 1.
Вообще-то 0

Ron de Bruin
Excel Automation

You see a lot of old SaveAs code that does not specify the FileFormat parameter. In Excel versions before Excel 2007, code without this parameter will not cause too many problems because Excel will use the current FileFormat of the existing file and the default FileFormat for new files is a (xls) in 97-2003 because there are no other Excel file formats before Excel 2007.

But because there are so many new file formats in Excel 2007-2016, we shouldn’t use code like this that does not specify the FileFormat parameter. In Excel 2007-2016, SaveAs requires you to provide both the FileFormat parameter and the correct file extension.

For example, in Excel 2007-2016, this will fail if the ActiveWorkbook is not an xlsm file
ActiveWorkbook.SaveAs "C:ron.xlsm"

This code will always work
ActiveWorkbook.SaveAs "C:ron.xlsm", fileformat:=52
‘ 52 = xlOpenXMLWorkbookMacroEnabled = xlsm (with macro’s in 2007-2016)

These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +1

51 = xlOpenXMLWorkbook (without macro’s in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

Note: I always use the FileFormat numbers instead of the defined constants in my code so that it will compile OK when I copy the code into an Excel 97-2003 workbook (For example, Excel 97-2003 won’t know what the xlOpenXMLWorkbookMacroEnabled constant is).

Examples

Below are two basic code examples to copy the ActiveSheet to a new Workbook and save it in a format that matches the file extension of the parent workbook. The second example use GetSaveAsFilename to ask you for a file path/name. Example 1 you can use in Excel 97-2016 , Example 2 you can use in Excel 2000-2016.

If you run the code in Excel 2007-2016 it will look at the FileFormat of the parent workbook and save the new file in that format. Only if the parent workbook is an xlsm file and if there is no VBA code in the new workbook it will save the new file as xlsx. If the parent workbook is not an xlsx, xlsm or xls then it will be saved as xlsb.

If you always want to save in a certain format you can replace this part of the macro:

With one of the one liners from this list

FileExtStr = ".xlsb": FileFormatNum = 50
FileExtStr = ".xlsx": FileFormatNum = 51
FileExtStr = ".xlsm": FileFormatNum = 52

Or maybe you want to save the one worksheet workbook to csv, txt or prn.
(you can use this also if you run the code in Excel 97-2003)

FileExtStr = ".csv": FileFormatNum = 6
FileExtStr = ".txt": FileFormatNum = -4158
FileExtStr = ".prn": FileFormatNum = 36

Developing.ru

Автоматический запуск макроса при изменении значений в ячейке

  • Версия для печати
  • Цитата
  • Цитата
  • Цитата

я что-то так и не понял как делать, я уж и так и эдак вставлял не получается. Может кто-нибудь сможет видоизменить мой макрос, чтобы он запускался при внесении числа в ячейку, то есть если в одном листе excel-евского файла в какой-то ячейке ввести число например 543 то макрос переходит не визуально, а в памяти, на другой лист файла, а там таблица из нескольких столбцов с числами, так вот макрос находит в определенном солбце это (543) число, далее сравнивает рядом с ним стоящее в одной строке число с нулем, если оно меньше нуля то макрос возвращает в ту ячейку куда вводили число 543 значение того отрицательного числа которое стоит рядом в одной строке с числом 543. Ну а если число которое стоит в таблице рядом с 543 больше нуля то макрос ни чего не делает, а оператор вводит данные в другую ячейку. Ну в общем-то как сравнивать числа и выводить число в какую-то ячейку с этим я сам справлюсь, а вот как написать чтобы макрос начинал работу после введения данных в ячейку — вот это я не знаю. Кто знает подскажите пожалуйста?

Читайте так же:
Как в ворде сделать шаблон бланка

Sub сравнение()

‘ сравнение Макрос
‘ Макрос записан 13.02.2010 (ккк)

‘ Сочетание клавиш: Ctrl+s

Dim a As Integer ‘ — это номер строки в таблице где происходит сравнение
Dim n As Integer ‘ — это переменная которой присваивается значение числа после того как его ввели
Dim x As Integer ‘ — это номер строки на которой находиться ячейка в которую вводиться число
Dim y As Integer ‘ — это номер столбца в котором находиться ячейка в которую вводиться число

a = 1 ‘задается номер строки с которой начинаем

‘ ВОТ ТУТ КАК РАЗ В МЕСТО ЭТИХ ТРЕХ СТРОЧЕК И ДОЛЖНА ПО ИДЕЕ БЫТЬ КОМАНДА
‘ КОТОРАЯ ЗАПУСКАЕТ МАКРОС ПОСЛЕ ВВОДА ЧИСЛА В ЯЧЕЙКУ
‘ Range(«B2»).Select
‘ ActiveCell.FormulaR1C1 = «543»
‘ n = введенному в ячейку числу

x = 2 ‘ для того чтобы макрос хоть как то работал задаем умышленно фиксированное значение
y = 2 ‘ для того чтобы макрос хоть как то работал задаем умышленно фиксированное значение
n = Sheets(«лист-данные»).Cells(x, y) ‘присваиваем переменной n значение введенного в ячейку числа
2 If n = Sheets(«лист-таблица»).Cells(a, 1) Then GoTo 1 ‘ находим такое же число как оператор вводил но уже в эталонной таблице
a = a + 1
If a <= 50 Then GoTo 2
1 If Sheets(«лист-таблица»).Cells(a, 2) > 0 Then GoTo 3 ‘ Число найдено, а сдесь выясняем отрицательное ли число которое стоит рядом в эталонной таблице

Sheets(«лист-данные»).Cells(x, y) = Sheets(«лист-таблица»).Cells(a, 2) ‘присваивается здачение отрицательного числа ячейке в которую изначально был ввод
Sheets(«лист-данные»).Select ‘ну тут ниже меняетс цвет шриф для яркости
Cells(x, y).Select
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
With Selection.Font
.Name = «Arial Cyr»
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
pause (20 = cek) ‘ то же не знаю как писать паузу в работе, а она нужна чтобы оператор увидел результат
4 r = r + 1
If r < 50000000 Then GoTo 4 ‘ пришлось вместо паузы вот такой ерундой заниматься
Selection.ClearContents ‘очистка ячейки куда был ввод
Selection.Font.ColorIndex = 1

Естественно макрос до коца не проработан, да это не главное сейча для меня, проработаю, мне важно начало его написать.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector