Remkomplekty.ru

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

Excel vba value

Чтение и запись значения ячейки в 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 активного листа.

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

VBA Value

Excel VBA Value Property

Value is a property in VBA which is mostly used with the range method to assign a value to a specific range, it is an inbuilt expression in VBA, for example, if we use range(“B3”).value = 3 this will assign cell B3 a value of 3, not necessarily that value property is to be used with only range method we can use it with other functions as well.

Early on in our learning with VBA we are so curious to learn about how to store the data in the cells. If you are that curious then you need to understand “Value” property. In this article, we will explain to you about “Value” property, how to insert or set values, how to get value from the cell and many other things.

In one of the earlier articles, we have discussed “VBA Range Cells”. Range object can help us to refer to a single cell as well as multiple cells. To use the RANGE object first we need to decide for which cell we need to insert the value and what is the value we are going to insert.

How to use Value Property in VBA?

Example #1 – Range Object to Assign Values to Cells

For example, if you want to insert a value to the cell A1 then you should refer the cell A1 like this Range(“A1”)

Code:

After referring the cell using RANGE object now put a dot (.) to see the IntelliSense list of all the properties and methods associated with this object.

Code:

Form this variety of options select the property “VALUE”.

Code:

Once the “VALUE” property selected we need to set the value to the cell A1 by putting the value in equal sign.

Code:

Ok, this will insert the value “Welcome to VBA” to the cell A1.

If you want to insert the same value to multiple cells then refer the cells like below code.

Code:

This will insert the value from the cell A1 to A5.

If you want to insert values to different cells but not for series of the cell then we need to use code and the cell address in separate arguments like the below.

Code:

This will insert the text “Welcome to VBA” to the cells A1, A5, B4, and C2 cells.

Example #2 – Insert Value using CELLS Property

Not through RANGE object but also using VBA CELLS property we can insert values. But one of the problems with the CELLS object is we don’t get access of the IntelliSense list as we got for RANGE object.

Here we need to mention the row & column numbers we need the insert the value. For an example if you want to insert the value to the cell A1 then the code is CELLS(1,1), if you want to insert the value to the cell B5 then the code is CELLS(5,2) i.e. equal to B5 cell.

We cannot insert values to multiple cells by using CELLS property, this is unlike our RANGE object.

Example #3 – Get Cell Value

We have seen how to insert values to the cells, now we will see how to get values from cells.

Читать еще:  Макрос excel содержимое ячейки

Step 1: Define a variable as String.

Code:

Step 2: For this variable “k” we will assign the value of the cell A1. In cell A1 I have entered the value “Welcome to VBA”.

So the code will be k = Range(“A1”).Value

Code:

Step 3: Show the result of the variable “k” in the VBA message box.

Code:

By running the code we should the result of cell A1 value in the message box.

We can also use the RANGE object to get the data of the cell A1, below code will show you the same.

Code:

This should also get the value of the cell A1 in the message box.

Example 4 – Error Value if More than One Cell Value Requires

For an example look at the below code.

Code:

If you run the above code we will get the “Type Mismatch” error.

The reason why we get this error because when the object variable set to more than one cell “value” property doesn’t really understand which cell value to be given, so it can get a single cell value at a time.

Recommended Articles

This has been a guide to VBA Value Property. Here we learn how to get the VBA object cell value and range to assign values to cells along with examples and downloads to excel template. Below are some useful excel articles related to VBA –

Excel vba value

������� ���������� ��������� �� ����� 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.

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:

Читать еще:  Функция left в excel

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!

How to correct a #VALUE! error

#VALUE is Excel’s way of saying, «There’s something wrong with the way your formula is typed. Or, there’s something wrong with the cells you are referencing.» The error is very general, and it can be hard to find the exact cause of it. The information on this page shows common problems and solutions for the error. You may need to try one or more of the solutions to fix your particular error.

Fix the error for a specific function

  • Which function are you using?
  • AVERAGE
  • CONCATENATE
  • COUNTIF, COUNTIFS
  • DATEVALUE
  • DAYS
  • FIND, FINDB
  • IF
  • INDEX, MATCH
  • SEARCH, SEARCHB
  • SUM
  • SUMIF, SUMIFS
  • SUMPRODUCT
  • TIMEVALUE
  • TRANSPOSE
  • VLOOKUP
  • * None of the above

Don’t see your function in this list? Try the other solutions listed below.

Problems with subtraction

If you’re new to Excel, you might be typing a formula for subtraction incorrectly. Here are two ways to do it:

Subtract a cell reference from another

Type two values in two separate cells. In a third cell, subtract one cell reference from the other. In this example, cell D2 has the budgeted amount, and cell E2 has the actual amount. F2 has the formula =D2-E2.

Or, use SUM with positive and negative numbers

Type a positive value in one cell, and a negative value in another. In a third cell, use the SUM function to add the two cells together. In this example, cell D6 has the budgeted amount, and cell E6 has the actual amount as a negative number. F6 has the formula =SUM(D6,E6).

If you’re using Windows, you might get the #VALUE! error when doing even the most basic subtraction formula. The following might solve your problem:

First do a quick test. In a new workbook, type a 2 in cell A1. Type a 4 in cell B1. Then in C1 type this formula =B1-A1. If you get the #VALUE! error, go to the next step. If you don’t get the error, try other solutions on this page.

In Windows, open your Region control panel.

Windows 10: Click Start, type Region, and then click the Region control panel.

Windows 8: At the Start screen, type Region, click Settings, and then click Region.

Windows 7: Click Start and then type Region, and then click Region and language.

On the Formats tab, click Additional settings.

Look for the List separator. If the List separator is set to the minus sign, change it to something else. For example, a comma is a common list separator. The semicolon is also common. However, another list separator might be more appropriate for your particular region.

Open your workbook. If a cell contains a #VALUE! error, double-click to edit it.

If there are commas where there should be minus signs for subtraction, change them to minus signs.

Repeat this process for other cells that have the error.

Subtract a cell reference from another

Type two dates in two separate cells. In a third cell, subtract one cell reference from the other. In this example, cell D10 has the start date, and cell E10 has the End date. F10 has the formula =E10-D10.

Or, use the DATEDIF function

Type two dates in two separate cells. In a third cell, use the DATEDIF function to find the difference in dates. For more information on the DATEDIF function, see Calculate the difference between two dates.

Читать еще:  Vba excel сортировка таблицы

Make your date column wider. If your date is aligned to the right, then it’s a date. But if it’s aligned to the left, this means the date isn’t really a date. It’s text. And Excel won’t recognize text as a date. Here are some solutions that can help this problem.

Check for leading spaces

Double-click a date that is being used in a subtraction formula.

Put your cursor at the beginning and see if you can select one or more spaces. Here’s what a selected space looks like at the beginning of a cell:

If your cell has this problem, proceed to the next step. If you don’t see one or more spaces, go to the next section on checking your computer’s date settings.

Select the column that contains the date by clicking its column header.

Click Data > Text to Columns.

Click Next twice.

On Step 3 of 3 of the wizard, under Column data format, click Date.

Choose a date format, and then click Finish.

Repeat this process for other columns to ensure they don’t contain leading spaces before dates.

Check your computer’s date settings

Excel uses your computer’s date system. If a cell’s date isn’t entered using the same date system, then Excel won’t recognize it as a true date.

For example, let’s say that your computer displays dates as mm/dd/yyyy. If you typed a date like that in a cell, Excel would recognize it as a date and you’d be able to use it in a subtraction formula. However, if you typed a date like dd/mm/yy, then Excel wouldn’t recognize that as a date. Instead, it would treat it as text.

There are two solutions to this problem: You could change the date system that your computer uses to match the date system you want to type in Excel. Or, in Excel you could create a new column and use the DATE function to create a true date based on the date stored as text. Here’s how to do that assuming your computers date system is mm/dd/yyy and your text date is 31/12/2017 in cell A1:

Create a formula like this: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

The result would be 12/31/2017.

If you want the format to appear like dd/mm/yy, press CTRL+1 (or + 1 on the Mac).

Choose a different locale that uses the dd/mm/yy format, for example, English (United Kingdom). When you’re done applying the format, the result would be 31/12/2017 and it would be a true date, not a text date.

Note: The formula above is written with the DATE, RIGHT, MID, and LEFT functions. Please notice that it is written with an assumption that the text date has two characters for days, two characters for months, and four characters for year. You may need to customize the formula to suit your date.

Problems with spaces and text

Often #VALUE! occurs because your formula refers to other cells that contain spaces, or even trickier: hidden spaces. These spaces can make a cell look blank, when in fact they are not blank.

1. Select referenced cells

Find cells that your formula is referencing and select them. In many cases removing spaces for an entire column is a good practice because you can replace more than one space at a time. In this example, clicking the E selects the entire column.

2. Find and replace

On the Home tab, click Find & Select > Replace.

3. Replace spaces with nothing

In the Find what box, type a single space. Then, in the Replace with box, delete anything that might be there.

4. Replace or Replace all

If you are confident that all spaces in the column should be removed, click Replace All. If you’d like to step through and replace spaces with nothing on an individual basis, you can click Find next first, and then click Replace when you are confident the space isn’t needed. When you’re done, the #VALUE! error may be resolved. If not, go to the next step.

5. Turn on the filter

Sometimes there are hidden characters other than spaces that can make a cell appear blank, when it’s not really blank. Single apostrophes within a cell can do this. To get rid of these characters in a column, turn on the filter by going to Home > Sort & Filter > Filter.

6. Set the filter

Click the filter arrow , and then deselect Select all. Then, select the Blanks checkbox.

7. Select any unnamed checkboxes

Select any check boxes that don’t have anything next to them, like this one.

8. Select blank cells, and delete

When Excel brings back the blank cells, select them. Then press the Delete key. This will clear any hidden characters in the cells.

9. Clear the filter

Click the filter arrow , and then click Clear filter from. so that all cells are visible.

10. Result

If spaces were the culprit of your #VALUE! error then hopefully your error has been replaced by the formula result, as shown here in our example. If not, repeat this process for other cells that your formula refers to. Or, try other solutions on this page.

Note: In this example, notice that cell E4 has a green triangle and the number is aligned to the left. This means the number is stored as text. This may cause more problems later. If you see this problem, we recommend converting numbers stored as text to numbers.

Text or special characters within a cell can cause the #VALUE! error. But sometimes it’s hard to see which cells have these problems. Solution: Use the ISTEXT function to inspect cells. Please note that ISTEXT doesn’t resolve the error, it simply finds cells that might be causing the error.

Example with #VALUE!

Here’s an example of a formula that has a #VALUE! error. This is likely due to cell E2. There is a special character that appears as a small box after «00.» Or as the next picture shows, you could use the ISTEXT function in a separate column to check for text.

Same example, with ISTEXT

Here the ISTEXT function was added in column F. All cells are fine except the one with the value of TRUE. This means cell E2 has text. To resolve this, you could delete the cell’s contents and retype the value of 1865.00. Or you could also use the CLEAN function to clean out characters, or use the REPLACE function to replace special characters with other values.

After using CLEAN or REPLACE, you’ll want to copy the result, and use Home > Paste > Paste Special > Values. You might also have to convert numbers stored as text to numbers.

Formulas with math operations like + and * may not be able to calculate cells that contain text or spaces. In this case, try using a function instead. Functions will often ignore text values and calculate everything as numbers, eliminating the #VALUE! error. For example, instead of =A2+B2+C2, type =SUM(A2:C2). Or, instead of =A2*B2, type =PRODUCT(A2,B2).

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