Как сделать ячейку активной в excel vba?

Итог: Изучите два разных способа быстрого объединения ряда ячеек. Это включает в себя метод Ctrl + щелчок левой кнопкой мыши и бесплатный макрос VBA, который позволяет быстро и легко создавать формулы объединения или Ampersand.

Уровень мастерства: Средний

Сцепление: хорошее и плохое

Функция CONCATENATE может быть очень полезна для объединения значений нескольких ячеек в одну ячейку или формулу. Одно из популярных применений — создание формул VLOOKUP на основе нескольких критериев.

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

Вариант № 1: Ctrl + щелчок левой кнопкой мыши, чтобы выбрать несколько ячеек

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

На следующем снимке экрана показано, как использовать сочетание клавиш Ctrl + щелчок левой кнопкой мыши. Вам не нужен макрос для этого, он встроен в Excel.

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

Вариант № 2: CONCATENATE макроса диапазона

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

Макрос Concatenate использует InputBox, который позволяет
вам выбирать диапазон ячеек. Затем он создает формулу Concatenate или
Ampersand, создавая аргумент для каждой ячейки в выбранном диапазоне.

Вы можете назначить макрос кнопке на ленте или комбинации
клавиш. Макрос позволяет очень быстро создавать формулы.

Как работает макрос?

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

1. Выберите ячейку, в которую нужно ввести формулу, и запустите макрос.
2. Появляется InputBox и предлагает вам выбрать ячейки, которые вы хотите объединить. Вы можете выбрать диапазон ячеек с помощью мыши или клавиатуры.
3. Нажмите ОК
4. Макрос разделяет диапазон на ссылки на одну ячейку, поэтому эта ссылка на диапазон (A2: C2) превращается в (A2, B2, C2).
5. Формула Concatenate или Ampersand создается в активной ячейке.

Опции макроса Concatenate

1. Тип формулы. Макрос «Concatenate» позволяет создать формулу «Concatenate» или «Ampersand».
2. Символ разделителя — Вы также можете добавить символ разделителя между каждой ячейкой. Это удобно, если вы хотите добавить запятые, пробелы, тире или любой символ между соединенными ячейками.
3. Абсолютные ссылки — макрос также дает вам возможность сделать ссылки на ячейки абсолютными (привязанными). Это добавит знак $ перед буквой столбца или номером строки. Это удобно, если вы копируете формулу в определенном направлении и не хотите, чтобы относительные ссылки на ячейки менялись.

Функция Concatenate или формулы Ampersand

Клетки также могут быть объединены с помощью символа Ampersand (&). Это альтернатива использованию функции CONCATENATE. Следующие две формулы приведут к одному и тому же результату.

= CONCATENATE(А2,В2,С2)

= А2&В2&С2

Тот, который вы используете, — это вопрос личных предпочтений. Функция Concatenate может иметь небольшое преимущество, поскольку вы можете использовать трюк Ctrl + щелчок левой кнопкой мыши, чтобы быстро добавить несколько ячеек в формулу.

Опять же, макрос позволяет вам создать либо Concatenate , либо формулу Ampersand.

Код VBA

Вы можете скачать рабочую книгу, которая содержит код ниже.

Concatenate Macro.xlsm (97.3 KB)

Concatenate Macro Other Sheets.xlsm (98.5 KB)

Вот код для макросов Concatenate и Ampersand .

Option Explicit

' Следующие 4 макроса используются для вызова макроса Concatenate_Formula.
' Макрос Concatenate_Formula имеет различные параметры, и эти 4 макроса
' запустите макрос Concatenate_Formula с различными параметрами. Ты 'захочешь
' назначить любой из этих макросов кнопке ленты или сочетанию клавиш.

Sub Ampersander()
' Создает базовую формулу Ampersander без параметров
Call Concatenate_Formula(False, False)
End Sub

Sub Ampersander_Options()
' Создает формулу Ampersander и предлагает пользователю варианты
' Опции — это абсолютные ссылки и символ-разделитель.
Call Concatenate_Formula(False, True)
End Sub

Sub Concatenate()
' Создает базовую формулу CONCATENATE без опций
Call Concatenate_Formula(True, False)
End Sub

Sub Concatenate_Options()
' Создает формулу CONCATENATE и предлагает пользователю варианты
' Опции — это абсолютные ссылки и символ-разделитель.
Call Concatenate_Formula(True, True)
End Sub
'
Sub Concatenate_Formula(bConcat As Boolean, bOptions As Boolean)

Dim rSelected As Range
Dim c As Range
Dim sArgs As String
Dim bCol As Boolean
Dim bRow As Boolean
Dim sArgSep As String
Dim sSeparator As String
Dim rOutput As Range
Dim vbAnswer As VbMsgBoxResult
Dim lTrim As Long
Dim sTitle As String

' Установить переменные
Set rOutput = ActiveCell
bCol = False
bRow = False
sSeparator = «»
sTitle = IIf(bConcat, «CONCATENATE», «Ampersand»)

' Предложите пользователю выбрать ячейки для формулы
On Error Resume Next
Set rSelected = Application.InputBox(Prompt:= _
«Select cells to create formula», _
Title:=sTitle & » Creator», Type:=8)
On Error GoTo 0

' Запускать только в том случае, если были выбраны ячейки и кнопка 'отмены не была нажата
If Not rSelected Is Nothing Then

' Установить разделитель аргументов для конкатенации или 'формулы Ampersander
sArgSep = IIf(bConcat, «,», «&»)

' Запрашивать у пользователя абсолютные ссылки и параметры 'разделителя
If bOptions Then

vbAnswer = MsgBox(«Columns Absolute? $A1», vbYesNo)
bCol = IIf(vbAnswer = vbYes, True, False)

vbAnswer = MsgBox(«Rows Absolute? A$1», vbYesNo)
bRow = IIf(vbAnswer = vbYes, True, False)

sSeparator = Application.InputBox(Prompt:= _
«Type separator, leave blank if none.», _
Title:=sTitle & » separator», Type:=2)

End If

' Создать строку ссылок на ячейки
For Each c In rSelected.Cells
sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
If sSeparator «» Then
sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
End If
Next

' Обрезать дополнительный аргумент разделитель и разделитель 'символов
lTrim = IIf(sSeparator «», 4 + Len(sSeparator), 1)
sArgs = Left(sArgs, Len(sArgs) — lTrim)

' Создать формулу
' Предупреждение — вы не можете отменить этот ввод
' Если требуется отменить, вы можете скопировать строку формулы
' в буфер обмена, затем вставьте в активную ячейку, используя Ctrl + V
If bConcat Then
rOutput.Formula = «=CONCATENATE(» & sArgs & «)»
Else
rOutput.Formula = «=» & sArgs
End If

End If

End Sub

Как использовать код

Добавьте кнопки
макроса на ленту

В приведенных выше примерах я добавил этот код в свою книгу
личных макросов. Затем я добавил кнопки макросов на ленту для каждого из 4
макросов. Я создал новую группу на вкладке Формулы и добавил к ней кнопки.

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

Назначить сочетание
клавиш для макросов

Вы также можете назначить сочетание клавиш для макроса,
чтобы сделать этот процесс очень быстрым. Когда InputBox открыт, фокус
возвращается на рабочий лист, что означает, что вы можете выбрать диапазон
ввода с помощью клавиатуры. В следующем скриншоте я создаю объединенную формулу
только с помощью клавиатуры.

Я
запускаю макрос, помещая кнопку макроса на панель быстрого доступа, а затем
нажимаю сочетание клавиш Alt + Button Position для QAT. Ознакомьтесь с этой
статьей о том, как использовать сочетания клавиш QAT для получения более подробной информации.

Дополнительные ресурсы

Concatenate с разрывами строк — Дейв Брунс из ExcelJet имеет отличную статью и видео о том, как добавить разрывы строк в формулу конкатенации. Отличный совет для присоединения почтовых адресов.

Комбинируйте ячейки без сцепления — Дебра Далглиш в Contextures объясняет, как создавать формулы Ampersand.

Concatenate нескольких ячеек с использованием Transpose — Chandoo имеет интересный подход к этой проблеме с помощью функции TRANSPOSE.

Заключение

Этот инструмент должен значительно ускорить и упростить создание формул Concatenate или Ampersand. Это может быть не то, что вы используете каждый день, но это здорово иметь в вашем наборе инструментов.

Пожалуйста, оставьте комментарий ниже с любыми вопросами.

Источник: https://excelpedia.ru/makrosi-v-excel/kak-sozdat-ryad-yacheek-vba

VBA в Excel

Программирование в Excel с помощью VBA.

Язык VBA позволяет писать макросы в Excel. Как это делать рассматривается ниже.

Application

Application – это объект, занимающий вершину иерархии объектов в Excel. Application – это и есть Excel. Вот примеры использования Application:

• окно нормальных размеров
• Application.WindowState = xlNormal
• окно максимальных размеров
• Application.WindowState = xlMaximized

Workbooks

Workbooks – это коллекция рабочих книг, которые открыты. Каждая рабочая книга представлена объектом Workbook. Узнать сколько рабочих книг сейчас в коллекции можно так:

Dim bookCount As Integer bookCount = Workbooks.Count

MsgBox («Opened books = » & bookCount)

Обатиться к нужной рабочей книге в коллекции Workbooks можно обратиться по его номеру или по имени:

Workbooks.Item(2).Activate

1. здесь мы активизировали второй элемент коллекции Workbooks.
2. Другие примеры мы увидим ниже.

Workbook

Workbook – это рабочая книга.

Сохранить рабочую книгу:

Workbooks.Item(1).Save

И где же в этом примере рабочая книга Workbook? Здесь: Workbooks.Item(1), это выражение возвращает первый элемент коллекции Workbooks, а это и есть Workbook.

Закрыть рабочую книгу:

Workbooks.Item(1).Close

Sheets

Sheets – это все листы рабочей книги. Листы рабочей книги представлены двумя типами: рабочие листы – это обычные листы Excel и второй тип – это листы диаграмм. Те и другие и составляют коллекцию Sheets.

Charts

Charts – это только диаграммы рабочей книги.

Chart

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

Worksheets

• Worksheets – это только рабочие листы рабочей книги.
• Добавим рабочий лист в коллекцию Worksheets:
• Worksheets.Add

Worksheet

1. Worksheet – это один лист рабочей книги Excel.
2. Все ячейки рабочего листа Worksheet:
3. Worksheets(«Лист1»).Cells

Range. Работа с ячейками в Excel

Range – это одна ячейка листа или несколько ячеек. Этот объект рассмотрим подробнее. Установим кнопку на рабочий лист Excel.

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

Правой кнопкой мыши по нашей кнопке — > Свойства. Установите значение свойства Name – CommandButton, а свойства Caption — Range Test.

Закройте окно свойств кнопки, и кликаем по нашей кнопке правой кнопкой мыши, из выпавшего меню выбираем «Исходный текст». Открывается окно редактора VBA, оно называется Microsoft Visual Basic. В нём уже есть заготовка обработчика нажатия на кнопку CommandButton:

Private Sub CommandButton_Click()

End Sub

• Кстати, помотрите на эту заготовку, ключевое слово Sub говорит, что это процедура, а слово Private указывает, что эта процедура видна лишь в данном модуле.
• Всё у нас готово для начала изучения работы с ячейками рабочего листа Excel.

Как задать активную ячейку?

Сделаем активной ячейку A2:A2:

Private Sub CommandButton_Click()     Range(«A2:A2»).Activate

End Sub

Далее сохраняем, далее Debug — > Compile VBA Project. Если нет ошибок, то запускаем нашу программу: Run — > Run Sub/UserForm.

Выделим любую другую ячейу и нажимём на кнопку Range Test. Вновь выделится ячейка «А2». Это значит, что обработчик нажатия на кнопку работает исправно.

Как выделить ряд ячеек?

Выделим интервал ячеек «A2:D2». Вносим в нашу заготовку макроса новый код:

Private Sub CommandButton_Click()     Range(«A2:D2»).Select

End Sub

Далее сохраняем, далее Debug — > Compile VBA Project. Переходим на рабочий лист Excel и жмем на кнопку Range Test.

Как установить значение в ячейку?

Внесем число 123 в ячейку «A2:A2» для этого изменим код макроса так:

Private Sub CommandButton_Click()     Range(«A2:A2»).Value = 123

End Sub

Далее сохраняем, далее Debug — > Compile VBA Project. Переходим на рабочий лист Excel и жмем на кнопку Range Test.

Это результат работы.

Источник: http://www.sbp-program.ru/vba/sbp-vba-excel.htm

VBA Excel. Ячейки (обращение, запись, чтение, очистка)

Обращение к ячейке на листе Excel из кода VBA. Запись информации в ячейку. Чтение информации из ячейки. Очистка значения ячейки. Метод ClearContents объекта Range.

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

Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.

В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:

Workbooks(«Книга2.xlsm»).Sheets(«Лист2»).Range(«C5»)
Workbooks(«Книга2.xlsm»).Sheets(«Лист2»).Cells(5, 3)
Workbooks(«Книга2.xlsm»).Sheets(«Лист2»).Cells(5, «C»)

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

ActiveCell
Range(«A1»)
Cells(1, 1)
Cells(1, «A»)

Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере — это ячейка «A1».

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

'по основному имени листа
Лист2.Cells(2, 7)
'по имени ярлыка
Sheets(«Имя ярлыка»).Cells(3, 8)

Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках — имя ярлыка.

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

Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе — зависит от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее. Для примера, Cells(4) та же ячейка, что и Cells(1, 4).

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

Запись информации в ячейку

Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:

Cells(2, 4).Value = 15
Cells(2, 4) = 15
Range(«A1») = «Этот текст записываем в ячейку»
ActiveCell = 28 + 10*36

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

Чтение информации из ячейки

Считать информацию из ячейки в переменную можно также при помощи оператора присваивания «=»:

Sub Test()
Dim a1 As Integer, a2 As Integer, a3 As Integer
Range(«A3») = 6
Cells(2, 5) = 15
a1 = Range(«A3»)
a2 = Cells(2, 5)
a3 = a1 * a2
MsgBox a3
End Sub

Точно также можно обмениваться информацией между ячейками:

Cells(2, 2) = Range(«A4»)

Очистка значения ячейки

Очищается ячейка от значения с помощью метода ClearContents. Кроме того, можно присвоить ячейке значение нуля или пустой строки:

Cells(10, 2).ClearContents
Range(«D23») = 0
ActiveCell = «»

Источник: https://vremya-ne-zhdet.ru/vba-excel/yacheyki-obrashcheniye-zapis-chteniye-ochistka/

Объекты Excel — Microsoft Excel для начинающих

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel.

Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе.

Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

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

Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг.

Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги.

Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.

В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).

Объект
Описание

Application	Приложение Excel.
Workbooks	Коллекция всех открытых в данный момент рабочих книг в текущем приложении Excel. Доступ к какой-то конкретной рабочей книге может быть осуществлён через объект Workbooks при помощи числового индекса рабочей книги или её имени, например, Workbooks(1) или Workbooks(“Книга1”).
Workbook	Объект Workbook – это рабочая книга. Доступ к ней может быть выполнен через коллекцию Workbooks при помощи числового индекса или имени рабочей книги (см. выше). Для доступа к активной в данный момент рабочей книге можно использовать ActiveWorkbook. Из объекта Workbook можно получить доступ к объекту Sheets, который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets, который представляет из себя коллекцию всех рабочих листов книги Excel.
Sheets	Объект Sheets– это коллекция всех листов рабочей книги. Это могут быть как рабочие листы, так и диаграммы на отдельном листе. Доступ к отдельному листу из коллекции Sheets можно получить при помощи числового индекса листа или его имени, например, Sheets(1) или Sheets(“Лист1”).
Worksheets	Объект Worksheets – это коллекция всех рабочих листов в рабочей книге (то есть, все листы, кроме диаграмм на отдельном листе). Доступ к отдельному рабочему листу из коллекции Worksheets можно получить при помощи числового индекса рабочего листа или его имени, например, Worksheets(1) или Worksheets(“Лист1”).
Worksheet	Объект Worksheet – это отдельный рабочий лист книги Excel. Доступ к нему можно получить при помощи числового индекса рабочего листа или его имени (см. выше). Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns, которые являются коллекцией объектов Range, ссылающихся на строки и столбцы рабочего листа. А также можно получить доступ к отдельной ячейке или к любому диапазону смежных ячеек на рабочем листе.
Rows	Объект Rows – это коллекция всех строк рабочего листа. Объект Range, состоящий из отдельной строки рабочего листа, может быть доступен по номеру этой строки, например, Rows(1).
Columns	Объект Columns – это коллекция всех столбцов рабочего листа. Объект Range, состоящий из отдельного столбца рабочего листа, может быть доступен по номеру этого столбца, например, Columns(1).
Range	Объект Range – это любое количество смежных ячеек на рабочем листе. Это может быть одна ячейка или все ячейки листа. Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1). По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(“A1:B10”) или Worksheet.Range(“A1”, “B10”) или Worksheet.Range(Cells(1,1), Cells(10,2)). Обратите внимание, если в адресе Range вторая ячейка не указана (например, Worksheet.Range(“A1”) или Worksheet.Range(Cells(1,1)), то будет выбран диапазон, состоящий из единственной ячейки.

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

Workbooks(«Книга1»).Worksheets(«Лист1»).Range(«A1:B10»)

Присваивание объекта переменной

В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:

Dim DataWb As Workbook
Set DataWb = Workbooks(«Книга1.xlsx»)

Активный объект

В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.

Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.

Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:

Смена активного объекта

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

Sub ActivateAndSelect()

Workbooks(«Книга2»).Activate
Worksheets(«Лист2»).Select
Worksheets(«Лист2»).Range(«A1:B10»).Select
Worksheets(«Лист2»).Range(«A5»).Activate

End Sub

Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.

Свойства объектов

Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других.

Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name.

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

Dim wbName As String
wbName = ActiveWorkbook.Name

Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:

Workbooks(«Книга1»).Worksheets(«Лист1»)

Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.

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

ActiveSheet.Name = «Мой рабочий лист»

Методы объектов

Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.

Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:

Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.

csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2, а аргументу FileFormat – значение xlCSV:

ActiveWorkbook.SaveAs «Книга2», xlCSV

Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:=” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:

ActiveWorkbook.SaveAs Filename:=»Книга2″, [FileFormat]:=xlCSV

В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.

Рассмотрим несколько примеров

Пример 1

Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each.

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

Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.

'Пролистываем поочерёдно все рабочие листы активной рабочей книги
'и выводим окно сообщения с именем каждого рабочего листа

Dim wSheet As Worksheet

For Each wSheet in Worksheets
MsgBox «Найден рабочий лист: » & wSheet.Name
Next wSheet

Пример 2

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

В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.

'Копируем диапазон ячеек из листа «Лист1» другой рабочей книги (с именем Data.xlsx)
'и вставляем только значения на лист «Результаты» текущей рабочей книги (с именем CurrWb.xlsm)

Dim dataWb As Workbook

Set dataWb = Workbooks.Open(«C:Data»)

'Обратите внимание, что DataWb – это активная рабочая книга.
'Следовательно, следующее действие выполняется с объектом Sheets в DataWb.

Sheets(«Лист1»).Range(«A1:B10»).Copy

'Вставляем значения, скопированные из диапазона ячеек, на рабочий лист «Результаты»
'текущей рабочей книги. Обратите внимание, что рабочая книга CurrWb.xlsm не является
'активной, поэтому должна быть указана в ссылке.

Workbooks(«CurrWb»).Sheets(«Результаты»).Range(«A1»).PasteSpecial Paste:=xlPasteValues

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet.

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

Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.

Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.

'С помощью цикла просматриваем значения в столбце A на листе «Лист2»,
'выполняем с каждым из них арифметические операции и записываем результат
'в столбец A активного рабочего листа (Лист1)

Dim i As Integer
Dim Col As Range
Dim dVal As Double

'Присваиваем переменной Col столбец A рабочего листа «Лист2»

Set Col = Sheets(«Лист2»).Columns(«A»)
i = 1

'Просматриваем последовательно все ячейки столбца Col до тех пор
'пока не встретится пустая ячейка

Do Until IsEmpty(Col.Cells(i))

'Выполняем арифметические операции со значением текущей ячейки

dVal = Col.Cells(i).Value * 3 — 1

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

Cells(i, 1).Value = dVal
i = i + 1

Loop
Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/obekty-excel-466.html

excel 2010 с помощью vba сделать ячейку активной

Я пытаюсь написать макрос Excel, который будет принимать столбец данных и редактирование ошибок форматирования. Фон:

Spreadsheet gets sent out to company with three name columns — LName, FName, MI

Company sends it back, usually with combined FName and MI or with full middle name

The state throws a fit and rejects the entire list if a single name comes across incorrectly — e.g. MI is a full name, there is a space in FName, the MI is included in the FName, MI is a zero instead of a letter, etc.

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

be able to loop

pull the MI if it's in the FName column and paste it into the next column

«trim» or delete the space and any following text in the FName column

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

Проблема:

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

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

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

Not selecting/activating the right cell(s).

If statement is returning a positive even when it shouldn't.

If statement is therefore breaking the whole stupid thing.

Абы. Вот код, и пока я не могу разделить таблицу для HIPAA причинам, это безопасный допущений:

Колонки F имеет фамилии, столбец G должен иметь имена, но часто включает в себя имя, место и отчество (например, Боб с вместо Боба) и, наконец, столбец H должна быть только отчество, но часто имеет полной отчества или ноль, если у человека нет отчества (например, Чарльз вместо C или просто 0). Я получу вокруг меняется нулей «» и уравновешивать полные отчества с инициалами в той или иной функции позже.

Sub ReduceToInitial()Dim strInit As StringDim strName As StringDim r As Excel.RangeDim rCell As Excel.RangeDim lr As LongDim oSht As WorksheetSet oSht = Application.ActiveSheetlr = Cells(Rows.Count, «G»).End(xlUp).RowSet r = oSht.Range(«G2:G» & lr)Range(«G2»).SelectRange(«G2″).ActivateOn Error Resume NextFor Each rCell In rRange(rCell).SelectRange(rCell).Activate If rCell.Find(» «, rCell) 0 Then strInit = Right(rCell, 1) ActiveCell.Offset(0, 1).Select ActiveCell.Formula = strInit ActiveCell.Offset(0, -1).Select strName = rCell.Left(rCell, rCell.Find(» «, rCell) — 1) ActiveCell.Formula = strName End IfNext rCellEnd Sub

Пожалуйста, дайте мне знать, если я не объяснил себе очень хорошо, и я буду стараться делать лучше.

Источник: http://otzovio.ru/Question/Index/1333633/excel-2010-vba-make-cell-active

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

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

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

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

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

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

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

Workbooks(«Книга1.xls»).Sheets(«Лист1»).Range(«A3») ' Обратиться к ячейке A3
Workbooks(«Книга1.xls»).Sheets(«Лист1»).Cells(3, 1) ' Обратиться к ячейке в 3-й строке и 1-й колонке (A3)

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

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

Range(«A1»)
Cells(1, 1)

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

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

With Workbooks(«Книга1»).Sheets(«Лист2»)
' Вывести значение ячейки A1, которая находится на Листе2
MsgBox .Range(«A1»)
' Вывести значение ячейки B1, которая находится на Листе2
MsgBox .Range(«B1»)
End With

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

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

Application.ActiveCell ' полная запись
ActiveCell ' краткая запись

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

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

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

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

Пример 5

Источник: http://www.codernotes.ru/articles/vba/chtenie-i-zapis-znacheniya-yachejki-v-vba.html

Вопрос 14. VBA Excel. Объекты, свойства и методы

Одним из основных понятий VBA является объект. Объект – это то, чем вы управляете с помощью программы на языке VBA, например, форма, кнопка, рабочий лист или диапазон ячеек MS Excel.

Каждый объект обладает некоторыми свойствами. Например, форма может быть видимой или невидимой в данный момент на экране.

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

Объект содержит также список методов, которые к нему применимы. Методы – это то, что вы можете делать с объектом. Например, показать форму на экране или убрать её можно с помощью методов Show и Hide.

Таким образом, объект – это программный элемент, который имеет свое отображение на экране, содержит некоторые переменные, определяющие его свойства, и некоторые методы для управления объектом. Например, в MS Excel имеется много встроенных объектов:

Range(“Адрес”)	— диапазон ячеек (может включать только одну ячейку).
Cells(i, j)	— ячейка, находящаяся на пересечении i-й строки и j-го столбца рабочего листа MS Excel (i и j – целые числа).
Rows(№ строки)	— строка с заданным номером.
Columns(№ столбца)	— столбец с заданным номером
Sheets(“Имя”)	— лист с указанным именем.
Sheets(№ листа)	— лист с указанным номером.
WorkSheet	— рабочий лист.

• Установка значений свойств – это один из способов управления объектами. Синтаксис установки значения свойства объекта следующий:
• Объект. Свойство = Выражение
• Основным свойством объектов Cells и Range, является Value(значение), которое, однако, можно не указывать. Например:

Range(“A5:A10”). Value = 0или Range(“A5:A10”) = 0– в диапазон ячеек A5:A10 заносится значение 0.

Cells(2, 4). Value = n или Cells(2, 4) = n – в ячейку, находящуюся на пересечении 2-й строки и 4-го столбца (ячейка с адресом “D2”), заносится значение переменной n.

1. Синтаксис чтения свойств объекта следующий:
2. Переменная = Объект. Свойство
3. Например:

Xn = Cells(1, 2).Value или Xn = Range(“B1”).Value– переменной Xn присваивается значение из ячейки B1 текущего рабочего листа.

• Синтаксис применения методов к объекту:
• Объект. Метод
• Например:

Sheets(2).Activate –сделать активным лист с №2.

Sheets(“Диаграмма”).Delete –удалить лист с именем “Диаграмма”.

Range(«A5:A10»).Clear –очистить диапазон ячеек A5:A10.

Range(«A2:B10»).Select – выделить диапазон ячеек A2:B10.

В MS Excel имеются объекты, которые содержат другие объекты. Например, рабочая книга содержит рабочие листы, рабочий лист содержит диапазон ячеек и т.д. Объектом самого высокого уровня является Application(приложение). Если вы изменяете его свойства или вызываете его методы, то результат применяется к текущей работе MS Excel. Например:

Application.Quit — завершение работы с Excel.

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

Application.Workbooks(«Отчет»).Worksheets(«Май»).Rows(2).Delete

1. Нужно отметить следующее:
2. · Можно не писать имя объекта Application,так как это подразумевается по умолчанию.
3. · При работе с подобъектом уже активизированного объекта нет необходимости указывать содержащий его объект.

· VBA использует некоторые свойства и методы, которые возвращают объект к которому они относятся (это позволяет быстро указывать нужный объект). Примеры таких свойств: ActiveCell (активная ячейка), ActiveSheet (активный лист), ActiveWorkBook (активная рабочая книга). Так, установить значение активной ячейки можно следующим образом:

ActiveCell.Value = » Да».

Вопрос 15. VBA Excel. Ссылки на одиночные объекты и объекты семейств

Семейства объектов

Семейством (Collection) в VBA называется совокупность однотипных объектов. Например, в Excel семейство Worksheets является совокупностью всех рабочих листов — объектов Worksheet — в данной рабочей книге, а семейство Lines — совокупностью прямых линий, нарисованных на данном рабочем листе.

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

Workbooks(«ПОДПИСКА.xls»).Close

закрывает рабочую книгу под именем ПОДПИСКА.xls.

А вот пример использования индекса элемента семейства. Оператор

ActiveSheet.Lines(1).Select

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

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

Например, оператор ActiveSheet.Lines.Deletеудаляет все нарисованные на активном рабочем листе прямые линии.

Как правило, индивидуальные объекты, являющиеся элементами семейств, имеют гораздо больше свойств и методов, чем соответствующий собирательный объект-семейство. Например, объект-семейство Workbooks в Excel имеет всего пять свойств (Application, Count, Creator, Item, Parent) и четыре метода (Add, Close, Open, OpenText), то время как объект Workbook имеет 59 свойств и 42 метода.

Не все объекты приложений могут группироваться в семейства — для некоторых индивидуальных объектов не существует соответствующих семейств.

Вопрос 16. Объект Range. Назначение и особенности.

ОБЪЕКТ RANGE.

Range (объект «Диапазон»)позволяет изменять свойства диапазона ячеек, например, используемый шрифт, проверять или изменять содержимое ячеек, вырезать или копировать указанный диапазон, и многое другое. Это наиболее часто используемый в

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

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

• Назначение:
• · если вам нужно ввести данные в ячейку или отформатировать ее, то вы должны получить объект Range, представляющий эту ячейку;
• · если вы хотите сделать что-то с выделенными вами ячейками, вам необходимо получить объект Range, представляющий выделение;
• · если вам нужно просто что-то сделать с группой ячеек, первое ваше действие — опять-таки получить объект Range, представляющий эту группу ячеек.

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

где: объект — это объект типа Worksheet или Range, к которому применяется метод Range. Этот объект может быть опущен, и тогда предполагается, что метод Range применяется к активному рабочему листу — объекту ActiveSheet;

Range(«A1:B10»).ClearContents

Рекомендуемые страницы:

Воспользуйтесь поиском по сайту:

Источник: https://megalektsii.ru/s51137t7.html