Top-office11.ru

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

Vba excel columns

Using the Excel Range Columns property in VBA

The following sections explain how you can use worksheet or range object .Columns property. It begins with explaining two ways to select a column. Next, it is explained how to loop over the columns collection.

Select a column

The .Columns property returns a collection of columns. Which columns are to be included is determined by the RowIndex and ColumnIndex properties as explained below. There are three classes that support the Columns property:

  • Application.Columns : columns on the active worksheet. This is what is assumed when Columns is called without specifying the range or worksheet.
  • Worksheet.Columns : columns on the specified worksheet.
  • Range.Columns : columns in the specified range.

There are two ways to identify a single column in the Columns collection:

  1. N-th item in the columns collection
  2. Select column in the columns collection using column character

Another ways to identify a column is using the range address, e.g. Range(«B:B») , or Range(«B:D») for columns B, C and D.

N-th item in the collection

When leaving out the second optional argument ColumnIndex , the Columns property returns the nth item. The order in which items get returned is breadth-first, so in the example table the columns with value a,b,c. f.

ExpressionValueComment
Range(«B2:C3»).Columns(2)CThe second column in the range
Range(«B2:C3»).Columns(3)DEven though the range only has two columns, .
Note
Negative values in the index are not allowed.

Counting columns and cells

Range(«B2:C3»).Columns.Count returns 2 columns, Range(«B2:C3»).Columns.Cells.Count returns 4 cells.

Column in range and EntireColumn

As shown in above example, Columns applied to a range only includes the cells in that range. To get the complete column, apply EntireColumn .

Select one or more columns in the columns collection using column character

ExpressionColumnComment
Range(«B2:B3»).Columns(«B»)CColumn character interpreted as number, e.g. «B» is always 2nd column, even if not in the original range
Range(«B2:B3»).Columns(«B:C»)C and DColumn character interpreted as number, e.g. «B» is always 2nd column

Loop over columns in Range

The Columns property is particularly useful because it makes it easy to iterate over a range. The image below shows the part of the Code VBA (download) menu that lets you insert the code fragment you require.

Loop over all columns in the range

The code below shows how you can loop over all columns in the range. Here it would print values a,b,c,d from the table at the top of the page.

CODE VBA — AGORA Software BV Copyright 1997-20166

Что возвращает свойство Column объекта Range

определённый диапазон Range(«B1:C1»).Columns(2), т.е. второй столбец в диапазоне B1:C1. Значит мы должны получить номер столбца относительно данного диапазона, а получаем номер столбца относительно листа. Получается, что в справке неправильно написано? Должно быть написано, что возвращается номер столбца активного листа?

Примечание: чтобы видеть работу кода: ViewImmediate Window.

Visual Basic
21.07.2012, 20:58

При вызове объекта Range, VBA самостоятельно меняет Range на rAnge
Всем доброго дня, у меня такой вопрос. По незнанию создал функцию rAnge(), теперь при вызове.

Диапазону ячеек B2:B20 присвоить имя «Стоимость», используя свойство Cells объекта Range
1) Диапазону ячеек B2:B20 присвоить имя «Стоимость», используя свойство Cells объекта Range. 2).

Передать свойство объекта в свойство объекта в объекте что в массиве
var array = ; function func(a, b, c) < array = <>; array.a = <>; // вместо «a» — аргумент .

21.07.2012, 21:072
21.07.2012, 21:233

Выражение Range(«B1:C1»).Columns(2) возвращает объект-диапазон. У диапазона есть свойство Column, которое не зависит от того, каким способом этот диапазон был получен.

Добавлено через 6 минут

22.07.2012, 08:25 [ТС]4

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

вот я и думаю, что нужно уточнить в Справке — в отношении чего применяется Column.

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

22.07.2012, 08:555

гм. «столбец пересекается с листом»? или «столбец пересекается со столбцом»?
конечно, сильно сказано.
но, имхо, это еще больше запутает народ.

на мой взгляд, уточнения не нужны — в конце концов, существует такое понятие: «по умолчанию». но если будут — то пусть.

22.07.2012, 09:09 [ТС]6
22.07.2012, 09:337

А перевод я бы изменил так:

Возвращает номер первого столбца первой области определённого диапазона.

Visual Basic
22.07.2012, 10:26 [ТС]8

но возвращается номер не первого столбца определённого диапазона, а номер столбца рабочего листа. И номер первого столбца так и будет №1. А про область можно и не упоминать, т.к. столбцы в определённом диапазоне уникальны.

Казанский, про код в #7 пока не могу сказать, т.к. нужно отдохнуть от этой темы, а то уже всё перепуталось.

22.07.2012, 10:329

«русский язык — это тако-ой русский язык. » (с)
(чего уж там про английский. )

сравниваем: «функция возвращает номер первого девятиэтажного дома», «функция возвращает номер второго дома после большого тополя», «функция возвращает номер лучшей школы нашего района», «функция возвращает номер столбца первой синей ячейки».

должно вернуть номер дома на улице, номер школы как таковой и номер столбца листа, не правда ли?

23.07.2012, 13:33 [ТС]10

для диапазона a должен возвращаться номер столбца этого диапазона: Range(«B1:C1»), а не номер столбца рабочего листа.
В двух других случаях под определённым диапазоном понимается рабочая книга, поэтому всё правильно.

Примечание:
чтобы увидеть работу кода: ViewImmediate Window.

Объекты Excel

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.

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

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

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

Visual Basic
ОбъектОписание
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 через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2, а аргументу FileFormat – значение xlCSV:

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

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

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

Пример 1

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

Пример 2

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

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

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.

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

VBA Columns

Excel VBA Columns Property

VBA Columns property is used to refer to columns in the worksheet. Using this property we can use any column in the specified worksheet and work with it.

When we want to refer to the cell we use either the Range object or Cells property. Similarly, how you do refers to columns in VBA? We can refer to columns by using “Columns” property. Look at the syntax of COLUMNS property.

We need to mention the column number or header alphabet to reference the column.

For example, if we want to refer the second column we can write the code in three ways.

Examples

Example #1

If you want to select the second column in the worksheet then first we need to mention the column number we need to select.

Code:

Now put a dot (.) to choose the “Select” method.

One of the problems with this property is we don’t get to see the IntelliSense list of VBA.

Code:

So, the above VBA code will select the second column of the worksheet.

Instead of mentioning the column number we can also use column header alphabet “B” to select the second column.

Code:

Both the above codes will select column B i.e. second column.

Example #2 – Select Column Based on Variable Value

We can also use the variable to select the column number. Look at the below code now.

Code:

In the above, I have declared the variable as Integer and assigned the value of 4 to this variable.

For Columns property I have supplied this variable instead of column number. Since the variable holds the value of 4 it will select the 4 th column.

Example #3 – Select Column Based on Cell Value

We have seen how to select the column based on variable value now we will see how we can select the column based on cell value number. In cell A1 I have entered the number 3.

Now below code will select the column based on the number in the cell A1.

Code:

The above code is the same as the previous one but the only thing I have changed here is instead of assigning the direct number to the variable I have given variable value as “whatever the number is there in the cell A1”.

Since we have a value of 3 in cell A1 it will select the third column.

Example #4 – Combination of Range & Column Property

We can also use Columns property with Range object as well. Using Range object we can specify the specific range. For an example look at the below code.

Code:

In the above example, I have specified the range of cells as C1 to D5, then using columns property I have specified the column number as 2 to select.

Now, in general, our second column is B and the code has to select the “B” column but see what happens when I run the code.

It has selected the cells from D1 to D5.

In our perception, it should have selected the second column i.e. column B. But now it has selected the cells from D1 to D5.

The reason why it has selected these cells because before using the COLUMNS property I have specified the range by using the RANGE object as C1 to D5. Now property thinks within this range as the columns and selects the second column in the range C1 to D5. D is the second column and specified cells are D1 to D5.

Example #5 – Select Multiple Columns with Range Object

Using the Range object and Columns property we can select multiple columns. Look at the below code.

Code:

The code will select the column from the second column to the fifth column i.e. from column B to E.

We can also write the code in this way as well.

Code:

The above is too exactly the same as the previous one and selects the columns from B to E.

Like this, we can use COLUMNS property to work with worksheet.

Recommended Articles

This has been a guide to VBA Columns. Here we discuss examples of columns property in excel VBA and select multiple columns with range object and downloadable excel templates. Below are some useful articles related to VBA –

Читать еще:  Excel выделяет несколько ячеек вместо одной
Ссылка на основную публикацию
Adblock
detector