Excel Vba Worksheets и Sheets Разница
Аннотация: В главе представлены собственные практические наработки автора по выводу информации из приложений, написанных в Visual Studio 2003/2005/2008 на C# в Excel. Все коды отлажены и неоднократно использовались. Вопросы, которые остались не затронуты, были до сих пор не востребованы в практической работе автора.
Введение алиаса позволяет не только сократить количество кода при обращении к объектам, но и дает возможность использовать весь код, который мы напишем ниже, для любого способа
Создадим обработчик нажатия для кнопки 1 и замкнем на него обработчики нажатия всех кнопок. Запишем следующий, общий для всех примеров, приводимых ниже, код:
В case 1 будем размещать код, в котором выполняется какое-либо действие по старту Excel и выводу информации, в case 2 — дополнительные действия и действия при закрытии приложения, в default — поместим код выхода из приложения.
VBA в Excel. Событие листа
Использованную часть рабочего листа можно определить через свойство Worksheet.UsedRange. Для определения пересечения областей можно использовать метод Application.Intersect(). Если Вам надо определить какие ячейки нужной Вам области заняты данными (например пользователь выделил целый столбец, определив пересение столбца и UsedRange можно сократить число анализируемых ячеек).
Worksheet_Change
После этого появится следующие начало и конец макроса, который и отвечает за срабатывание при попытке редактировании ячейки.
Для того чтобы определить какая ячейка у нас притерпела изменение можно воспользоваться следующей записью — для определения столбца и ячейки. Наверное это надо было поместить в этот урок.
Ну вот, зная координаты теперь мы точно знаем какая ячейка у нас будет обрабатываться. Теперь можно написать макрос, который нам будет сообщать о том, в какой ячейке произошли изменения. Выглядеть это будет, например, так:
MsgBox «Отредактирована ячейка Cells(» & Stroka & «,» & Stolbec & «)», _
Рассмотрим такой пример когда, после редактирования ячейки, её текст автоматически закрашивается в синий цвет, а ячейка окрашивается в жёлтый цвет.
И всё бы ничего, отредактировали ячейку, потом выпполнился какой-то макрос. Но вот беда если записать такой макрос, то мы загрустим:
А загрустим потому, что мы будем заносить в изменённую ячейку какие-то данные. То-есть мы опять её редактируем. Следовательно, как только макрос заносит данные в ячейку, он снова запускает сам себя. И у нас получается зацикливание. Для того чтобы этого не происходило мы используем дополнительную запись, которая блокирует запуск события в момент выполнения текущего.
VBA Clear Contents.
Использованную часть рабочего листа можно определить через свойство Worksheet.UsedRange. Для определения пересечения областей можно использовать метод Application.Intersect(). Если Вам надо определить какие ячейки нужной Вам области заняты данными (например пользователь выделил целый столбец, определив пересение столбца и UsedRange можно сократить число анализируемых ячеек).
Используйте метод очистки содержимого VBA для сохранения форматирования ячеек
Если вы наблюдали за двумя предыдущими методами, эти два метода не только удаляли или очищали предоставленные ячейки. Он также удалил форматирование ячеек, которое мы предоставили.
Чтобы сохранить форматирование ячеек, нам не нужно использовать ни «Удалить», ни «Очистить», но нам нужно использовать метод VBA «Очистить содержимое».
В тот момент, когда вы вводите диапазон ячеек с помощью объекта RANGE, он покажет все свойства и методы, связанные с ним.
Мы можем получить доступ к «Удалить», мы можем получить доступ к «Очистить», а также можем получить доступ к методам «ClearContents».
Теперь это очистит содержимое от ячейки A1 до C3, но у нас будет все существующее форматирование как есть.
Как вы можете видеть на картинке выше, у нас есть цвет ячеек в VBA, границы и каждое форматирование, связанное с этими упомянутыми ячейками.
Точно так же мы можем очистить содержимое других листов.
Это очистит содержимое ячеек с A1 по D10 на листе «Sheet1».
Точно так же мы можем удалить и другие открытые ячейки книги.
Понимание ячеек Excel и функций диапазона в VBA
При выполнении тех или иных действий Excel может запрашивать подтверждения. Например при удалении листа, закрытии файла с несохраненными данными и (!) при проведении операции для которой Excel’y нехватает памяти для Undo. Для подавления этих сообщений используйте Application.DisplayAlerts: Любопытный ход- можно сказать Excel что файл якобы сохранен и тогда он не станет возражать против его закрытия. Назначение макро на горячую клавишу
Обработка данных с функцией ячеек
Функция Cells наиболее полезна, когда у вас есть сложная формула, которую вы хотите выполнить в нескольких диапазонах ячеек. Эти диапазоны также могут существовать на нескольких листах.
Давайте возьмем простой пример. Допустим, вы управляете командой продаж из 11 человек, и каждый месяц вы хотите посмотреть на их эффективность.
Ты можешь иметь Лист1 который отслеживает их количество продаж и объем продаж.
На Sheet2 это место, где вы отслеживаете их отзывы за последние 30 дней от клиентов вашей компании.
Если вы хотите рассчитать бонус на первом листе, используя значения из двух листов, есть несколько способов сделать это. Вы можете написать формулу в первой ячейке, которая выполняет вычисления, используя данные на двух листах, и перетащить ее вниз. Это сработает.
Альтернативой этому является создание сценария VBA, который вы запускаете при каждом открытии листа или запускаете с помощью командной кнопки на листе, чтобы вы могли контролировать время его расчета. Вы можете использовать сценарий VBA для извлечения всех данных о продажах из внешнего файла в любом случае.
Так почему бы не запустить вычисления для бонусного столбца в одном и том же сценарии в то время?
Клетки функционируют в действии
Если вы никогда ранее не писали VBA в Excel, вам нужно включить пункт меню «Разработчик». Для этого перейдите в файл > Опции. Нажмите на Настроить ленту. Наконец, выберите Developer из левой панели, добавлять на правой панели, и убедитесь, что флажок установлен.
Теперь, когда вы нажимаете Хорошо и вернитесь на основной лист, вы увидите пункт меню Разработчик.
Вы можете использовать Вставить меню, чтобы вставить командную кнопку, или просто нажмите Посмотреть код начать кодирование.
В этом примере мы будем запускать скрипт при каждом открытии книги. Для этого просто нажмите Посмотреть код из меню разработчика и вставьте следующую новую функцию в окно кода.
Теперь вы готовы написать код для обработки расчетов. Используя один цикл, вы можете пройтись по всем 11 сотрудникам, а с помощью функции Cells ввести три переменные, необходимые для расчета.
В окончательном расчете используются следующие проценты для суммирования до 100 процентов от общего бонуса. Он основан на идеальном количестве продаж 50, объеме продаж 50 000 долларов и обратной связи 10.
Этот простой подход дает продавцам взвешенный бонус. За счет 50, объем 50 000 долларов и 10 баллов они получают весь максимальный бонус за месяц. Тем не менее, все, что находится в идеальном состоянии по любому фактору, уменьшает бонус. Все, что лучше идеального, повышает бонус.
Теперь давайте посмотрим, как можно реализовать всю эту логику в очень простом и коротком VBA-скрипте:
Если вы хотите, чтобы в столбце «Бонус» отображался фактический долларовый бонус, а не процент, вы можете умножить его на максимальную сумму бонуса. А еще лучше, поместите эту сумму в ячейку на другом листе и укажите ее в своем коде. Это упростит изменение значения позже без необходимости редактировать код.
Прелесть функции Cells в том, что вы можете создать довольно креативную логику для извлечения данных из много клеток по многим различным листам, и выполните с ними довольно сложные вычисления.
Вы можете выполнять всевозможные действия с ячейками с помощью функции Cells — например, очистка ячеек, изменение форматирования шрифта и многое другое.
Чтобы изучить все, что вы можете сделать дальше, загляните на страницу Microsoft MSDN для объекта Cells.
Основы программирования на языке VBA » Методические указания и задания
- Применить план вокруг группы
- Проверьте правильность написания текста внутри диапазона ячеек
- Очистить, скопировать или вырезать ячейки
- Поиск по диапазону с помощью метода «Найти»
- Намного больше
Метод SaveAs позволяет сохранить документ с указанием имени, формата файла, пароля, режим доступа и т. д. Данный метод, как и метод Save, присваивает свойству Saved значение true. Метод SaveAs имеет следующий синтаксис:
Просмотр содержимого документа
«VBA в Excel. Событие листа»
Событие листа
Worksheet_SelectionChange
Рассмотрим макрос, который реагирует на выделение ячейки, иногда это бывает полезным и нужным. Т.е. мы навели мышку на ячейку и при этом у нас сработал какой-то макрос. Вот и посмотрим как это делается.
Заходим в окно VB, нажимая Alt+F11. Слева мы видим названия наших листов. Щёлкаем двойным щелчком по желаемому листу. Вверху имеется два окошка в которых по умолчанию написано General иDeclarations.
Выбираем в левом окне Worksheets, а в правом SelectionChange.
Такая запись нам и будет говорить о том, что необходимо выполнять макрос, когда на листе произойдёт выделение некоторого диапазона.
И если в этот макрос добавить следующий код:
то какой бы мы диапазон не выделили на этом листе, он окрасится в синий цвет.
Хочу обратить внимание, что макрос работает только на том листе, который Вы выбрали слева в списке листов.
Рассмотрим ещё один пример, в котором будет выделяться всегда только одна ячейка на листе — активная ячейка (в видеоролике показано как это работает).
MsgBox «Курсор находится на столбце №» & a & » — это меньше 10.», _
MsgBox «Курсор находится за пределами моего понимания!», _
Существует очень много событиев на листе, которые можно интересно использовать. Все я рассматривать подробно не буду, но основные самые интересные поясню.
Рассмотрим макрос, который реагирует на выделение ячейки, иногда это бывает полезным и нужным. Т.е. мы навели мышку на ячейку и при этом у нас сработал какой-то макрос. Вот и посмотрим как это делается.
Заходим в окно VB, нажимая Alt+F11. Слева мы видим названия наших листов. Щёлкаем двойным щелчком по желаемому листу. Вверху имеется два окошка в которых по умолчанию написано General иDeclarations.
Выбираем в левом окне Worksheets, а в правом SelectionChange.
Такая запись нам и будет говорить о том, что необходимо выполнять макрос, когда на листе произойдёт выделение некоторого диапазона.
И если в этот макрос добавить следующий код:
то какой бы мы диапазон не выделили на этом листе, он окрасится в синий цвет.
Хочу обратить внимание, что макрос работает только на том листе, который Вы выбрали слева в списке листов.
Рассмотрим ещё один пример, в котором будет выделяться всегда только одна ячейка на листе — активная ячейка (в видеоролике показано как это работает).
MsgBox «Курсор находится на столбце №» & a & » — это меньше 10.», _
MsgBox «Курсор находится за пределами моего понимания!», _
Существует очень много событиев на листе, которые можно интересно использовать. Все я рассматривать подробно не буду, но основные самые интересные поясню.
После этого появится следующие начало и конец макроса, который и отвечает за срабатывание при попытке редактировании ячейки.
Для того чтобы определить какая ячейка у нас притерпела изменение можно воспользоваться следующей записью — для определения столбца и ячейки. Наверное это надо было поместить в этот урок.
Ну вот, зная координаты теперь мы точно знаем какая ячейка у нас будет обрабатываться. Теперь можно написать макрос, который нам будет сообщать о том, в какой ячейке произошли изменения. Выглядеть это будет, например, так:
MsgBox «Отредактирована ячейка Cells(» & Stroka & «,» & Stolbec & «)», _
Рассмотрим такой пример когда, после редактирования ячейки, её текст автоматически закрашивается в синий цвет, а ячейка окрашивается в жёлтый цвет.
И всё бы ничего, отредактировали ячейку, потом выпполнился какой-то макрос. Но вот беда если записать такой макрос, то мы загрустим:
А загрустим потому, что мы будем заносить в изменённую ячейку какие-то данные. То-есть мы опять её редактируем. Следовательно, как только макрос заносит данные в ячейку, он снова запускает сам себя. И у нас получается зацикливание. Для того чтобы этого не происходило используют дополнительную запись, которая блокирует запуск события в момент выполнения текущего.
Событие листа. Двойной щелчок мыши.
BeforeDoubleClick
Иногда охото сделать программу, которая приближается к настоящей, где всякие события происходят от нажатия двойного щелчка мыши. В этом занятии я расскажу как сделать макрос, который будет реагировать на двойной щелчок ЛКМ.
Что можно сделать при помощи такого макроса? Таблицы, в которых данные можно заносить при помощи двойного щелчка мыши. Например устанавливать галочки напротив выбранных наименований.
Делается это следующим образом. Заходим в окно VB, щёлкаем слева по необходимому листу. Открывается окно. В левом верхнем окошке выбираем пункт Worksheet, а правом окошке BeforeDoubleClick.
После этого появится следующие начало и конец макроса, который и отвечает за двойной щелчок мыши производимый по ячейке.
Если в этот код поместить какое-нибудь информационное сообщение, то при двойном щелчке по ячейке, будет появляться это сообщение.
MsgBox «Вы совершили двойной клик!», vbInformation, «Пример»
Но при такой записи есть один нюанс. После совершения двойного нажатия вы входите в режим редактирования ячейки, что очень неудобно. Для того чтобы этого не происходило необходимо написать команду, кооторая будет запрещать вход в режим редактирования. Макрос будет выглядеть так:
MsgBox «Вы совершили двойной клик!», vbInformation, «Пример»
Но такая запись активизирует двойной щелчёк на всём листе, и это конечно хорошо, но иногда возникает потребность задействовать только часть листа, например только столбец А. На этом этапе мы уже начинаем придумывать какие-то условия. И это можно осуществить всё при помощи того же If . End If или Select Case . End Select.
MsgBox «Вы совершили двойной клик!», vbInformation, «Пример»
При такой записи сообщение будет появляться только в том случае, если мы щёлкаем мышкой по столбцу А.
Усложним пример. Сделаем так, как показано в начале урока на левом рисунке. Попробуем Установить галочки, причём не во всём столбце, а в некотором диапазоне, например В2:В8. Ниже приведён макрос, который может это осуществить.
Target.Font.Name = «Marlett» — устанавливаем имя шрифта в ячейке, который и отвечает за галочку;
If Target = «a» Then . — если в редактируемой ячейке стоит галочка, то убираем её и закрашиваем ячейку в синий цвет. В противном случае устанавливаем галочку и закрашиваем ячейку в жёлтый цвет.
Ту же самую функцию можно осуществить через другую запись:
Тут диапазон задаётся при помощи координат столбца и строки, а также обычных логических операторов.
Вторая табличка, которая показана в начале страницы справа, выполняется по тем же принципам, только можно не указывать стиль шрифта и вместо а, писать Вкл и Выкл.
В видеоматериале показаны примеры работ приведённых макросов, а также продемонстрирована полная запись их написания.
Рассмотрим событие, которое возникает при нажатии на правую кнопку мыши. Иногда просто необходимо сделать так, чтобы заблокировалось меню появляющееся при нажатии на правую кнопку мыши. Или охото сделать своё меню. Именно это мы сегодня и изучим.
Делается это следующим образом. Заходим в окно VB, щёлкаем слева по листу, событие которого хотим отслеживать. Открывается окно. В левом верхнем окошке выбираем пункт Worksheet и правом окошкеBeforeRightClick.
После этого появится следующие начало и конец макроса, который и отвечает за отслеживание правой кнопки мыши.
И если в этот макрос вставить какое-нибудь информационное сообщение, то при нажатии на правую кнопку мыши на заданном листе, нам отобразится оно.
Но при такой записи, после выполнения макроса нам появляется меню. И для того чтобы этого не происходило необходимо добавить следующую запись, которая отвечает за появление меню.
При такой записи, у нас не появляется меню. Теперь зная это можно назначать различные макросы на правую кнопку мыши, которые будут срабатывать в зависимости от того на какую ячейку, столбец или строку Вы навели указатель мыши. Как например в следующем примере.
В данном примере показано, что в зависимости от того куда Вы поставите курсор и нажмёте на правую кнопку мыши, ячейка примет окрас в зависимости от номера столбца. Тут рассмотрены только первые 5 столбцов листа. Всё что дальше 5 столбца, при нажатии на ПКМ будет обесцвечиваться.
то правая кнопка мыши просто напросто не будет «работать», то-есть меню не будет появляться.
Добавление своего раздела в меню правой кнопки мыши
В следующем примере показано как добавить свои два раздела в меню (Окрасить ячейку и Обесцветить ячейку). При нажатии на правую кнопку мыши в меню появляется две новых команды.
При выборе «Окрасить ячейку», выделенный диапазон заливается синим цветом. При нажатии на «Обесцветить ячейку», выделенный диапазон ячеек обесцвечивается.
Работу данного кода можно посмотреть на «Листе 3» в примере. На видеоролике так же можно посмотреть его работу.
Создание своего собственного меню.
Как убрать стандартные команды в меню правой кнопки мыши?
А сейчас я покажу другой вариант создания меню — более разумный.
Создадим своё собственное меню, со своими командами, и которое работает только в нашей книге.
Для этого воспользуемся дополнительными событиями для книги — это Workbook_Activate() иWorkbook_Deactivate().
Теперь разберём последовательность создания такого меню. Добавим в книгу новый модуль, на который разместим два макроса — NewMenu и MakroPrivet.
Sub MakroPrivet() ‘Макрос, который воплняется при нажатии
MakroPrivet — как было указано выше, это макрос, который выполняется при нажатии на новое меню.
MsgBox «Привет!». — сообщение, которое появляется при выполнении кода нового меню.
Теперь сделаем так, чтобы меню правой кнопки появлялось только при активации нашей книги. Т.е. если мы переключимся на другую книгу, то меню будет стандартное.
Для этого в окне VB щёлкаем слева по пункту «ЭтаКнига». Открывается окно. В левом верхнем окошке выбираем пункт Workbook и правом окошке Activate.
После этого появится следующие начало и конец макроса, который и будет срабатывать при активации книги.
Вставим в этот макрос ссылку на макрос NewMenu, который находится в созданном модуле.
теперь при активации книги будет срабатывать макрос NewMenu, который и будет формировать наше новое меню.
В открывшийся код поместим всего одну строчку, которая и будет сбрасывать меню в настройки по умолчанию.
Ну вот, теперь при активации нашей книги будет формироваться новое меню правой кнопки мыши. Но как только книга деактивируется, то-есть мы переключаемся на другой документ, меню сбрасывается.
В этом уроке я рассмотрю сразу три события листа. Они не такие значимые как в предыдущих уроках, но иногда из них можно извлечь хорошую пользу.
Рассмотрим событие, которое возникает при активации листа. Делается это следующим образом. Заходим в окно VB, щёлкаем слева по необходимому листу. Открывается окно. В левом верхнем окошке выбираем пункт Worksheet, а правом окошке Worksheet_Activate.
После этого появится следующие начало и конец макроса, который и отвечает за активацию листа.
Если в этот код поместить какое-нибудь информационное сообщение, то при активации листа (например перейдти на лист 2, а затем опять на лист 1), нам будет появляться это сообщение (Пример: Лист 1).
MsgBox «Вы перешли на следующий уровень!», vbInformation, «Пример»
Рассмотрим событие, которое возникает при деактивации листа, т.е. когда мы уходим с него. Делается это следующим образом. В левом верхнем окошке выбираем пункт Worksheet, а правом окошкеWorksheet_Deactivate.
После этого появится следующие начало и конец макроса, который и отвечает за уход с листа — деактивацию.
Если в этот код поместить какое-нибудь информационное сообщение, то при уходе с активного листа, нам будет появляться сообщение (Пример: Лист 2).
Рассмотрим событие, которое возникает при пересчёте листа. Напрмер, у нас на листе есть таблица в которой содержится куча формул. И вам необходимо отследить всяческий пересчёт таблицы. При помощи этого макроса это можно легко выполнить.
Делается это следующим образом. В левом верхнем окошке выбираем пункт Worksheet, а правом окошкеCalculate.
После этого появится следующие начало и конец макроса, который и отслеживает пересчёт листа.
Если в этот код поместить какое-нибудь информационное сообщение, то при пересчёте формул на листе, нам будет появляться сообщение, например такое (Пример: Лист 3):
«Последние изменения внёс » & Application.UserName & «!», _
В этом уроке я рассмотрю оставшиеся события листа. Они вообще редко применяются, как я убедился на собственном опыте.
Рассмотрим событие, которое возникает при переходе по ссылке. Делается это следующим образом. Заходим в окно VB, щёлкаем слева по необходимому листу. Открывается окно. В левом верхнем окошке выбираем пункт Worksheet, а правом окошке FollowHyperlink.
После этого появится следующие начало и конец макроса, который и отвечает за переход по ссылке.
Если в этот код поместить какое-нибудь информационное сообщение, то при переходе по ссылке мы его увидим (Пример: Лист 1).
MsgBox «Вы перешли по ссылке на мой сайт!», vbInformation, «Пример»
И оставшиеся события — это события, которое происходят при работе со сводными таблицами. Делаются они аналогично предыдущим.
Делается это следующим образом. Заходим на ленте во вкладку файл/параметры:
В открывшемся окне с левой стороны выбираем Центр управления безопасностью, а справойПараметры центра управления безопасностью
В следующем окне слева выбираем пункт Надёжные расположения.
Для того, чтобы добавить какую-то свою папку в надёжное расположение необходимо нажать на кнопку«Добавить новое расположение». После этого откроется следующее окно
Нажимаем кнопку «Обзор» и выбираем ту папку в которой хотим разрешить все макросы. Обратите особое внимание на галочку «Также доверять всем вложенным папкам», если вы её не поставите, то макросы будут разрешены только в этой корневой папке, а вот если в ней имеются подпапки, то в них опять придётся разрешать и включать макросы в ручную.
Если же Вам не понравились Ваши надёжные расположения, то Вы можете их удалить или изменить.
Все папки, которые у Вас находятся в надёжных расположениях указаны в окне центра управления безопасностью в подпункте надёжные расположения, на рисунке ниже этот список показан в синем квадрате.
Работа с Excel в С#
Проблема с использованием функций Cells и Range в VBA состоит в том, что на продвинутых уровнях большинству людей трудно понять, как эти функции действительно работают. Использование их может очень запутать. Вот как вы можете использовать их так, как вы, вероятно, никогда не предполагали.
