Remkomplekty.ru

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

Cells value vba excel

Spreadsheets Made Easy

VBA Ranges – Getting and Setting Cell Values

In the previous post, we introduced the VBA Range object. This gave us the foundation of working with Ranges in VBA. In today’s post, I’d like to discuss how to get and set cell values through VBA. This will continue to build up our understanding of the VBA Range object and how to use it. There are several ways you can get and set cell values with VBA and I’ll do my best to cover all the necessities, but at the same time keeping it short and to the point. Let’s get started.

Getting Cell Values

To get a cell’s value in VBA, we need to refer to it with the Range object and then call the .Value property.

We’ll use the following spreadsheet for our example. It’s a simple table with some names in it.

To get the value from cell A2 you can use this code snippet:

This will take cell A2 and put it in the variable val . Then we print out the value in the Immediate Window (which the value in our example is Joseph ).

You can also set the range to a variable and access the value from that variable as well:

What happens if you use .Value on a set of cells?

Let’s change our previous code snippet to the following:

If you run this code, you will get an error stating that there is a type mismatch.

What’s going on here?

The problem is that when you work with a set of cells, .Value can only return a single value. So when we ask VBA to return .Value on our variable (which refers to multiple cells), the .Value property doesn’t know which cell we are referring to.

How do you get a single cell from a set of cells?

In order to use .Value to get a value from a cell, we need to refer to a single cell from the range of cells in our variable. The way we do that is with the Cells() VBA function.

The Range.Cells Function

The Cells() function is a way to take a range of cells and return a single cell from the set. Here is the function defined:

Take a look at the following code:

Here we took the range of A2:A5 and referred to row 1 column 1. Since the range variable cellRange refers to A2:A5 , the first row is row 2 and the first column is A .

Be careful!

When using the Cells() function, remember that row 1 and column 1 represent the top-left most cell within the range that the Cells() function is working on. If your range is A1:D5 , then Cells(1, 1) will refer to A1 , but if your range is B2:D6 , then Cells(1, 1) refers to B2 .

Ok, that covers getting cell values from range objects, now let’s discuss setting cell values with range objects.

Setting Cell Values

In order to set a cell’s value, you can use the same .Value property when referring to a cell. In this example, we’ll take A2 ‘s value and change it from Joseph to John :

First we set the variable cellRange to A2 . Then we said cellRange.Value = «John» which changes the variable’s .Value property. Remember, though, that the variable is a reference to cell A2 , so whatever you do to that variable, you also do to cell A2 in the worksheet. Finally, we output the value of A2 into the Immediate Window to see that it changed.

We can also see the value changed in the worksheet after we run this code:

How do you set multiple cells’ values?

Remember how I said that you can only read from one cell using .Value ? Well, when setting values, you can actually set multiple cells at one time by using .Value . Take a look at the following code:

If you ran this code, it would set all A2:A5 ‘s cells to John :

Well, maybe you’d actually want to do this for some other scenarios, like when you want a bunch of cells to repeat a value.

Let’s take a real example for a second. Let’s say we have two columns, First Name and Last Name . We want to take the Last Name column and place its value after the First Name ‘s value; essentially combining the values to make a single Name column.

Here’s our sample data:

Our task is to combine the first and last name columns and place the result in column A . How do we do that?

One solution is to loop through cells A2 through A5 and then set that cell’s value to its own value, plus a space, plus the last name of the cell right next to it.

Sounds easy enough, let’s code it up:

Let’s step through the code.

  • First, we create a variable called names . Then, we set that to range A2:A5 .
  • Next, we create a variable called cell . This is going to be a temporary variable that will change with each iteration of the loop.
  • Then, we create the loop. Here, we’re looping through the names range object and setting the current item to the cell variable. This means that each time we run through the loop, cell represents a single range object.
    • The first time the loop is run, cell is set to A2 . Then, A3 , next A4 , and finally A5 . After that, there are no more cells to go through in the names variable, so the loop ends.
    • I’ll go over how to loop through ranges in a future post since this post is already long enough!
  • Now we’re ready to combine the first and last names. How we do that is with another Range function called Offset(rows, columns) . The idea with this function is that if you’re on a cell like A2 and you say cell.Offset(0, 1) what we’re really saying is “move over one column to the right”. This puts us on cell B2 . That’s how we’re able to get the last name in our example.
    • I’ll discuss how to use the Offset() function in more detail in a future post. Again, this post has gone on long enough

Here are the results of the code after we run it:

From here, we could change the A1 cell to just Name and delete column B altogether.

Getting and Setting Cell Values from a Named Range or Table Name

One last thing I’d like to touch on is when you use the Range() function, you can use a named range or table name instead of a range like A2:A5 . In our first example, our data is in a table named Table1 . To refer to the data of the table, we could use the following:

Читать еще:  Vba excel округление до целого

And to refer to the entire table, we can leverage structured references like so:

This will return A1 ‘s value “Name” since the table starts in A1 .

Also, if you’re new to Excel Tables, click here to learn more.

What’s next?

Honestly, there is so much to discuss with range objects in VBA. I’ll be touching on many more topics regarding ranges in VBA in upcoming posts such as:

  • Modifying cell colors
  • Finding cells by their text values
  • Filtering data
  • Getting the last row in a range (you need this more often than you think)

I’ll come back to this post and put links to these posts as I create them.

If you enjoyed this content, please share and subscribe!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

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

Spreadsheets Made Easy

VBA Ranges – Getting and Setting Cell Values

In the previous post, we introduced the VBA Range object. This gave us the foundation of working with Ranges in VBA. In today’s post, I’d like to discuss how to get and set cell values through VBA. This will continue to build up our understanding of the VBA Range object and how to use it. There are several ways you can get and set cell values with VBA and I’ll do my best to cover all the necessities, but at the same time keeping it short and to the point. Let’s get started.

Getting Cell Values

To get a cell’s value in VBA, we need to refer to it with the Range object and then call the .Value property.

We’ll use the following spreadsheet for our example. It’s a simple table with some names in it.

To get the value from cell A2 you can use this code snippet:

This will take cell A2 and put it in the variable val . Then we print out the value in the Immediate Window (which the value in our example is Joseph ).

You can also set the range to a variable and access the value from that variable as well:

What happens if you use .Value on a set of cells?

Let’s change our previous code snippet to the following:

If you run this code, you will get an error stating that there is a type mismatch.

What’s going on here?

The problem is that when you work with a set of cells, .Value can only return a single value. So when we ask VBA to return .Value on our variable (which refers to multiple cells), the .Value property doesn’t know which cell we are referring to.

Читать еще:  Vba excel создать файл excel

How do you get a single cell from a set of cells?

In order to use .Value to get a value from a cell, we need to refer to a single cell from the range of cells in our variable. The way we do that is with the Cells() VBA function.

The Range.Cells Function

The Cells() function is a way to take a range of cells and return a single cell from the set. Here is the function defined:

Take a look at the following code:

Here we took the range of A2:A5 and referred to row 1 column 1. Since the range variable cellRange refers to A2:A5 , the first row is row 2 and the first column is A .

Be careful!

When using the Cells() function, remember that row 1 and column 1 represent the top-left most cell within the range that the Cells() function is working on. If your range is A1:D5 , then Cells(1, 1) will refer to A1 , but if your range is B2:D6 , then Cells(1, 1) refers to B2 .

Ok, that covers getting cell values from range objects, now let’s discuss setting cell values with range objects.

Setting Cell Values

In order to set a cell’s value, you can use the same .Value property when referring to a cell. In this example, we’ll take A2 ‘s value and change it from Joseph to John :

First we set the variable cellRange to A2 . Then we said cellRange.Value = «John» which changes the variable’s .Value property. Remember, though, that the variable is a reference to cell A2 , so whatever you do to that variable, you also do to cell A2 in the worksheet. Finally, we output the value of A2 into the Immediate Window to see that it changed.

We can also see the value changed in the worksheet after we run this code:

How do you set multiple cells’ values?

Remember how I said that you can only read from one cell using .Value ? Well, when setting values, you can actually set multiple cells at one time by using .Value . Take a look at the following code:

If you ran this code, it would set all A2:A5 ‘s cells to John :

Well, maybe you’d actually want to do this for some other scenarios, like when you want a bunch of cells to repeat a value.

Let’s take a real example for a second. Let’s say we have two columns, First Name and Last Name . We want to take the Last Name column and place its value after the First Name ‘s value; essentially combining the values to make a single Name column.

Here’s our sample data:

Our task is to combine the first and last name columns and place the result in column A . How do we do that?

One solution is to loop through cells A2 through A5 and then set that cell’s value to its own value, plus a space, plus the last name of the cell right next to it.

Sounds easy enough, let’s code it up:

Let’s step through the code.

  • First, we create a variable called names . Then, we set that to range A2:A5 .
  • Next, we create a variable called cell . This is going to be a temporary variable that will change with each iteration of the loop.
  • Then, we create the loop. Here, we’re looping through the names range object and setting the current item to the cell variable. This means that each time we run through the loop, cell represents a single range object.
    • The first time the loop is run, cell is set to A2 . Then, A3 , next A4 , and finally A5 . After that, there are no more cells to go through in the names variable, so the loop ends.
    • I’ll go over how to loop through ranges in a future post since this post is already long enough!
  • Now we’re ready to combine the first and last names. How we do that is with another Range function called Offset(rows, columns) . The idea with this function is that if you’re on a cell like A2 and you say cell.Offset(0, 1) what we’re really saying is “move over one column to the right”. This puts us on cell B2 . That’s how we’re able to get the last name in our example.
    • I’ll discuss how to use the Offset() function in more detail in a future post. Again, this post has gone on long enough

Here are the results of the code after we run it:

From here, we could change the A1 cell to just Name and delete column B altogether.

Getting and Setting Cell Values from a Named Range or Table Name

One last thing I’d like to touch on is when you use the Range() function, you can use a named range or table name instead of a range like A2:A5 . In our first example, our data is in a table named Table1 . To refer to the data of the table, we could use the following:

And to refer to the entire table, we can leverage structured references like so:

This will return A1 ‘s value “Name” since the table starts in A1 .

Also, if you’re new to Excel Tables, click here to learn more.

What’s next?

Honestly, there is so much to discuss with range objects in VBA. I’ll be touching on many more topics regarding ranges in VBA in upcoming posts such as:

  • Modifying cell colors
  • Finding cells by their text values
  • Filtering data
  • Getting the last row in a range (you need this more often than you think)

I’ll come back to this post and put links to these posts as I create them.

If you enjoyed this content, please share and subscribe!

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

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

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

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

Читать еще:  Vba excel поиск подстроки в строке

Установка значений свойств – это один из способов управления объектами. Синтаксис установки значения свойства объекта следующий:

Объект. Свойство = Выражение

Основным свойством объектов 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.

Синтаксис чтения свойств объекта следующий:

Переменная = Объект. Свойство

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

Нужно отметить следующее:

· Можно не писать имя объекта Application,так как это подразумевается по умолчанию.

· При работе с подобъектом уже активизированного объекта нет необходимости указывать содержащий его объект.

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

ActiveCell.Value = » Да».

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

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

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

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

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

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

Хотя семейства представляют собой группы объектов, сами семейства также являются одиночными объектами. Такой собирательный объект-семейство представляет собой всю совокупность входящих в семейство объектов, и имеет собственные свойства и методы, с помощью которых можно изменять разом состояние всех объектов семейства. Например, оператор 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;

Cells value vba excel

������� ���������� ��������� �� ����� VBA – �������, ����� �������� �� ���������� ������, � ����������� �� ��������� ����������. ���� ���������� �������� ���������, ������� ����� ����������� ������������ ���������� ��� ��������, ����������� �������������� ����������� ������������ ���������� Microsoft Office, �� ���������� ������� ����������� ������. ��� ���������� ���� ��������� ����� ��������� � ������� ������� ���������� ������, ������� ������� ����� �������� ���������� ���������. ��� ����� � ���������� ���������� �������� ������ ������������ � ������� ������� ��� ������ ������������ �������� ����������, � ����� ������� ������ � ��������������� ����������� �����. ���� ��������� ��������� � ������� ������� ������ ������ �������.

���������� “�����������” ��������� (��� ���������� ������� ��������� ��������� ����, � ���������� ���������� ����������) ����� �������� ��� �����. ������ ���� – ���� ����������� ����������������, �� ������� ��������� ���� (�����) ���������, ��� ������������� ����������� �������� ����������. ������ – ���� ����������������, �� ������� ��������� ����� ��������� (���������), ������������� � ����� �� ������������ �������. �������� ��������, ��������, ������ ����� ������� ���� �� ��������� ������ (������� Click), ������� ������� �� ���������� (������� KeyPress) � �.�. ������������ ����� ���������� ����� �������� ������ – «������ �������».

Range(“�����”)

Cells(i, j)

Rows(� ������)

Columns(� �������)

Sheets(“���”)

Sheets(� �����)

WorkSheet

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.

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.

Application.Quit — ���������� ������ � Excel.

Ссылка на основную публикацию
Adblock
detector