Системный и Бизнес-анализ | Microsoft Excel

2021-11-17 13:00:52 Переменные

В VBA, как и во всех языках программирования, можно использовать переменные. Но, в отличие от некоторых других языков, в VBA не обязательно объявлять переменные в явном виде перед их использованием в программе (хотя явное описание используемых переменных является признаком хорошего стиля программирования).
Например, переменной Ставка присваивается значение из ячейки A1 листа Лист1:

Ставка = Worksheets(“Лист1”).Range(“A1”).Value

Если в верхней части модуля VBA находится оператор OptionExplicit, то вы обязаны объявить все переменные, используемые в этом модуле. Необъявленные переменные приведут к ошибке компиляции, и ваша процедура не будет выполняться.

#VBA Открыть/Комментировать

2021-11-16 17:00:52 Объект Range

Объект Range (Диапазон) во многих отношениях является весьма специфическим. Легко понять, что это центральный элемент в объектной модели приложения Excel, поскольку рабочие книги и рабочие листы существуют только для того, чтобы хранить значения рабочих ячеек. Даже одна-единственная ячейка уже представляет собой объект Range. При этом диапазон ячеек также представляется объектом Range, а вовсе не объектом коллекции Ranges. Это один из немногих объектов, для которого правило именования коллекций с использованием множественного числа названий тех объектов, которые в них включены, не соблюдается.
Большинство объектов коллекций имеют свойство Item, выбираемое по умолчанию. Это позволяет обращаться к их отдельным элементам очень просто: использовать Workbooks(1) вместо полной записи:

Workbooks.Item(1)

В общем случае, если объект имеет свойство Item, именно оно выбирается при обращении к объекту по умолчанию. Для тех объектов, которые не являются коллекциями, по умолчанию выбирается свойство Value, если таковое у них имеется. Например, следующие два оператора кода VBA будут давать идентичные результаты, поскольку свойство Value для объекта CheckBox (Флажок) выбирается по умолчанию:

If Sheet1.CheckBox1.Value = True Then

If Sheet1.CheckBox1 = True Then

Объект Range имеет как свойство Item, так и свойство Value. И главная его особенность состоит в том, что в одном контексте для него по умолчанию выбирается свойство Item, тогда как в иной ситуации по умолчанию для него будет выбрано свойство Value. Однако это не должно вызывать у вас беспокойство, поскольку компилятор языка VBA точно знает, когда следует выбрать первый вариант, а когда - второй.

#VBA Открыть/Комментировать

2021-11-16 10:01:31 III ежегодная конференция по Excel и Power Bi

4 и 5 декабря приглашаем всех принять участие в III-ей ежегодной бесплатной онлайн конференции по построению отчетов в Excel и Power Bi!

Состав спикеров, подробная программа выступлений и регистрация =>

https://akademia-excel.ru/conf/?utm_source=telegram&utm_medium=excel_microsoft&utm_campaign=2021_11_15

Ключевые моменты конференции в 2021 году
— Конференция пройдет в выходные дни:
4 декабря (сб) с 09:30 до 21:00 Мск.
5 декабря (вс) c 10:00 до 21:00 Мск
— Звёздный состав спикеров (Зеленский М., Колоколов А., Салостей С., Базалева О., Ожиганов С., Шелегин И. и другие). Выступления друг за другом в одном потоке.
— Каждое выступление на 1 час (5 мин. приветствие + 45 минут основная часть + 10 минут ответы на вопросы)
— Доклады на сайте отмечены по уровню сложности материала. Каждый сможет выбрать материалы под себя.
— Запланированы перерывы между выступлениями на 10-15 минут и один обеденный перерыв на 1 час. Открыть/Комментировать

2021-11-15 13:00:56 Методы

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

Range("А1:С12").Clear

Метод Clear (Очистить) предполагает изменение значений сразу нескольких свойств объекта Range. Так, значение свойства Value (Значение) изменяется на Empty (Пусто), в результате чего содержимое диапазона очищается, свойство Bold (Полужирный) объекта Font (Шрифт) заменяется на False (Ложь), что означает очистку всех форматов, а свойство Comments(Комментарий) получает значение Nothing (Ничего), в результате чего удаляются и все комментарии к ячейкам диапазона. Также выполняется еще несколько других операций, связанных с очисткой содержимого диапазона.
Если программе необходимо взаимодействовать с файлами на дисках, с принтерами или любыми другими компонентами компьютера, внешними по отношению к программе Excel, вам, скорее всего, потребуется воспользоваться соответствующим методом. Каждый объект рабочей книги Workbook имеет свойство Name (Имя), которое доступно только для чтения. Поэтому невозможно изменить значение свойства Name, воспользовавшись оператором, подобным следующему:

Workbooks(1).Name = "xyz.xlsx"

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

Workbooks(1).SaveAs "xyz.xlsx"

Помимо значения свойства Name, метод SaveAs изменяет значения нескольких других свойств объекта Workbook, а также обеспечивает запись файла на жесткий диск.
В коде VBA методы легко перепутать со свойствами, поскольку они также отделяются от объекта точкой. Однако на самом деле это совершенно разные концепции.

#VBA Открыть/Комментировать

2021-11-15 10:01:28 Сочная подборка свежих каналов для вас:

@crazy_printing - Василиса из типографии поможет не сойти с ума!

@DesignRess - секретный рецепт коктейля из дизайнерских исследований.

@designinterview - все дизайнеры объединяются здесь!

@CGIOwl - бдительная сова на страже модиза и анимации. Всё видит, обо всем говорит. Открыть/Комментировать

2021-11-12 12:00:36 Свойства

Объекты, с которыми требуется работать, имеют свойства, которые можно понимать как различные их атрибуты. Например, объект диапазона Range обладает такими свойствами, как Column (Столбец), Row (Строка), Width (Ширина) и Value (Значение). Объект диаграммы Chart имеет такие свойства, как Legend (Легенда), ChartTitle (Заголовок диаграммы) и др. В то же время заголовок диаграммы - это тоже объект, называемый ChartTitle и имеющий свойства Font (Шрифт), Orientation (Ориентация) и Text (Текст). В Excel имеется множество объектов, и каждый из них обладает собственным набором свойств. С помощью VBA-кода можно выполнить следующие действия:
Узнать текущее значение свойства объекта и, исходя из полученного результата, далее выполнить те или иные действия.
Изменить значение свойства объекта.

Чтобы обратиться к свойству, нужно поместить его имя после имени объекта и воспользоваться точкой в качестве разделителя. Например, приведенный ниже оператор языка VBA устанавливает для свойства Value (Значение) диапазона Частота значение 15 (т.е. в результате его выполнения во всех ячейках этого диапазона будет находиться число 15):

Range("Частота").Value = 15

Некоторые свойства являются неизменяемыми (read-only), т.е. доступными только для чтения. Значение свойства можно узнать, но нельзя изменить. Для диапазона (объект Range), состоящего из одной ячейки, свойства Row (Строка) и Column (Столбец) являются неизменяемыми. Другими словами, можно узнать, в какой строке и в каком столбце находится ячейка, но изменить ее положение путем изменения значения этих свойств нельзя.

Объект самого верхнего уровня Application соответствует самой программе Excel. Этот объект имеет несколько полезных свойств, позволяющих определить, с чем именно в данный момент работает пользователь:
Application.Activeworkbook. Возвращается имя активной в данный момент рабочей книги Excel (объект Workbook).
Application.ActiveSheet. Возвращается имя активного листа (объект Sheet) текущей рабочей книги.
Application.ActiveCell. Возвращается адрес активной ячейки (объект Range) текущего рабочего листа.
Application.Selection. Возвращается объект, выбранный в текущий момент в активном окне объекта-приложения Application. Это может быть диапазон (объект Range), диаграмма (объект Chart), фигура (объект Shape) или любой другой объект, который можно выделить в окне приложения.

#VBA Открыть/Комментировать

2021-11-12 10:00:41 Приглашаем на бесплатный интенсив "Анализ дебиторской задолженности в Power BI" от онлайн школы IQBI.

17 и 18 ноября, с 19:00 до 20:30

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

Мероприятие будет особенно интересно финансистам и аналитикам, которые хотят перейти из Excel и других сервисов отчетности в Power BI.

А всем, кто досмотрит интенсив до конца, ждут дополнительные бонусы от IQBI в виде уроков из курсов.

Спикер и автор интенсива - Ольга Солонович - сертифицированный тренер Microsoft, практикующий аналитик и тренер в BI проектах. Образование: магистратура по специальности Финансы в университете Невшателя, Швейцария.

Регистрируйтесь на интенсив по ссылке: https://bit.ly/3kt4Oy2 Открыть/Комментировать

2021-11-11 10:00:33 Объекты и коллекции

Помимо объекта Application, в объектную модель Excel входят сотни других объектов, такие как Range, Chart или Shape. Все эти объекты упорядочены в иерархическую структуру, на вершине которой находится объект Application.
Объекты некоторых типов могут быть объединены в коллекции. (При этом сами коллекции также представляют собой объекты.) Объекты коллекций именуются с использованием множественного числа названий тех объектов, которые в них включены. Каждая открытая в приложении рабочая книга будет представлена объектом Workbook, а все открытые в данный момент рабочие книги образуют коллекцию, представленную объектом Workbooks. Аналогичным образом объект Shapes (Фигуры) представляет собой коллекцию, включающую все объекты типа Shape (Фигура).
Есть всего несколько исключений, когда соглашение о множественном числе в именах объектов коллекций не соблюдается. Самым важным из них является объект Range, который будет далее.
Для обращения к конкретному объекту нужно указать его положение в иерархии объектов, используя точку в качестве разделителя. Так, для ссылки на ячейку A1 можно воспользоваться следующей записью:

Application.Workbooks.Item("Моя_книга. xlsx").Worksheets.Item(1).Range("A1")

Для упрощения этой задачи язык VBA предоставляет определенные сокращения. Поскольку объект Application находится на вершине иерархии, его можно опускать, при этом транслятор языка VBA и так поймет, что именно вам требуется. Кроме того, в языке VBA для некоторых объектов определены свойства, выбираемые в них по умолчанию. Все коллекции имеют выбираемое по умолчанию свойство с названием Item, которое используется для доступа к одному из объектов коллекции. В результате приведенную выше запись можно сократить:

Workbooks.Item("Моя_книга. xlsx").Worksheets.Item(1).Range("A1")

При получении доступа к свойству Item коллекции можно обращаться к нему по имени нужного объекта или по его номеру. Для коллекции Workbooks в этой записи используется обращение по имени требуемой рабочей книги и программа возвращает ссылку на объект именно той книги, которая имеет указанное название. Однако к объекту в коллекции Worksheets в этой записи обращение выполняется по номеру требуемого рабочего листа, - нам нужен именно первый рабочий лист этой книги, независимо от его имени.

#VBA Открыть/Комментировать

2021-11-10 12:00:28 ​Объектная модель программы Excel

Язык VBA разработан как язык программирования, манипулирующий объектами. Некоторые из этих объектов содержатся непосредственно в самом языке, но большинство объектов, которые могут использоваться в программах на языке VBA, предназначенных для выполнения в среде приложения Excel, принадлежат объектной модели этого приложения.
На вершине объектной модели приложения находится объект Application (Приложение). Этот объект представляет саму программу Excel, а все остальные объекты в иерархии модели находятся под ним. Один из способов разобраться, как писать код - задать себе вопрос, какой объект следует изменить и какое его свойство или метод отвечает за интересующий аспект этого объекта. Например, если вы хотите заставить пользователей вводить данные только пользуясь строкой формул, вместо того чтобы вводить их непосредственно в ячейки рабочего листа, то можете просто соответствующим образом изменить значение свойства EditDirectlyInCell объекта Application.
Если вам неизвестно, какой именно объект или свойство следует изменить можете воспользоваться инструментом записи макросов. Запишем макрос, выбрав команду Файл => Параметры => Дополнительно и сняв флажок Разрешить редактирование в ячейках, получим следующий код:

Sub Макрос1()
'
' Макрос1 Макрос
'
'
Application.EditDirectlyInCell = False
End Sub

Теперь вы точно знаете, что свойство EditDirectlyInCell объекта Application - это то место, где хранится информация о выбранном режиме ввода и редактирования данных в приложении, а значит, можете с уверенностью использовать его в своих программах.

#VBA Открыть/Комментировать

2021-11-10 10:00:31 Хотите улучшить свое портфолио и получить продвижение по работе всего за 4 месяца? Тогда курс «Excel + Google-таблицы с нуля до PRO» как раз для вас. Спешите занять место со скидкой!
Курс подойдет тем, кому нужно быстро делать расчеты, планировать продажи, разрабатывать маркетинговый план и вычислять стоимость инвестиционных объектов.

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

Каждый, кто купит в ноябре курс, который участвует в ЧП, получит три скидки от партнеров:
Asus - скидка 10% на ноутбуки.
Литрес - -25% на все книги + 2 книги в подарок из подборки.
Clinique - скидка 40% на все, кроме новогодних коллекций.

Переходите: https://clc.to/SA6H_w и приобретайте курс со скидкой до 60% Открыть/Комментировать