Excel макрос cells — Мир ПК

Атанас Йонков Блоггер, Веб-разработчик
yonkov.atanas@gmail.com

В этом уроке я покажу Вам самые популярные макросы в VBA Excel, которые вы сможете использовать для оптимизации своей работы. VBA — это язык программирования, который может использоваться для расширения возможностей MS Excel и других приложений MS Office. Это чрезвычайно полезно для пользователей MS Excel, поскольку VBA может использоваться для автоматизации вашей работы и значительно увеличить Вашу эффективность. В этой статье Вы познакомитесь с VBA и я вам покажу некоторые из наиболее полезных, готовых к использованию примеров VBA. Вы сможете использовать эти примеры для создания собственных скриптов, соответствующих Вашим потребностям.

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

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

Table of Contents

Как включить макросы в Excel

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

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

2. Отображение скрытых строк

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

3. Удаление пустых строк и столбов

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

4. Нахождение пустых ячеек

13. Создание сводной таблицы

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

18. Защита данных в MS Excel

Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

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

CELLS() and RANGE() – Are they same? Are they Excel VBA Object?

Many of you who are interested in excel Macro or if you have ever tried to read and understand a VBA code, you would have seen two ways of referring a cell in Excel Macro:

Using Range()

To refer value of Cell D4, you can use Range(“B4”).Value. To know more about RANGE() you can read my previous article about Range Object.

Using Cells()

Value of same Cell B4 can be referred using Cells like this: Cells(4, 2).Value where 4 : is Row Number and 2 : is the column number. It accepts two numeric value to represent row and column where first number represents row and second one as column as shown in the above example.

Though from the above example it looks like both are same because they are used to refer a cell in excel sheet, but CELLS and RANGE, are not at all same. Find the main differences below:

CELLS is a property but RANGE is an Object

CELLS is a property of a RANGE or WorkSheet or Application Objects while RANGE itself is an Object. Cells returns a Range Object. Cells can be used without any parameter passed or a single parameter passed in it. Since Cells can be a property of WorkSheet or Range object, hence it gives the results based on what is object.

Range.Cells

Suppose I have a Range Range(“C3:F10”).

Range(“C3:F10”).Cells

This will return all the cells of the Range(C3:F10) from C3 to F10.

Range(“C3:F10”).Cells(1, 2)

This will return the cell in First Row (1) and second column (2) of the Range(“C3:F10”). Therefore (“C3:F10”).Cells(1, 2) will return Range(“D3”). 3 – First Row of the range and D – second column of the range (starting column is C).

Range(“C3:F10”).Cells(3)

In Cells when there is only one parameter then by default it considers as First row and parameter passed is the column. Therefore Range(“C3:F10”).Cells(3) is equal to Range(“C3:F10”).Cells(1, 3)

WorkSheet.Cells

Suppose I have a WorkSheet named “Sheet 1”. Or you can use ActiveSheet Object as well. Cells is property of ActiveSheet Object as well.

ActiveSheet.Cells or WorkSheets(“Sheet1”).Cells

This will return all the cells of the Active WorkSheet. For example, if you want to clear the contents of whole WorkSheet then you can use the below statement:

ActiveSheet.Cells(1, 2) or WorkSheets(“Sheet1”).Cells(1, 2)

This will return the Range(“B1”). Row 1 and Column 2 of the WorkSheet.

I have passed the same parameter as I have passed in the above example with RANGE Object. There in that example, it had returned the Cell as Range(“D3”) while here in this example it has returned the cell Range(“B1”) because here the whole sheets is considered as a single Range Object and Cells is providing the property on the whole sheet.

.Cells Property – Where is it used more often?

As you know RANGE Object refers a cell or cell range with Row Number and Column Name. Cells is the most useful to use when you have to refer Cells/Range in a Loop then Cells is useful because both the parameters are numeric here and you can use them for loop.

Examples:

Code to traverse cells in Row and Columns both

Above code will display in Sheet something like below:

Excel макрос cells

There is no Cell object nor is there a Cells collection.
Individual cells are treated as Range objects that refer to one cell.

Contents of a Cell

The easiest way to find what the contents of a cell are is to use the Visual Basic TypeName function

Text Property

You can assign a value to a cell using its Value property.
You can give a cell a number format by using its NumberFormat property.
The Text property of a cell returns the formatted appearance of the contents of a cell.

Range Object

The Range object can consist of individual cells or groups of cells.
Even an entire row or column is considered to be a range.
Although Excel can work with three dimensional formulas the Range object in VBA is limited to a range of cells on a single worksheet.
It is possible to edit a range either using a Range object directly (e.g. Range(«A1»).BackColor ) or by using the ActiveCell or Selection methods (e.g. ActiveCell.BackColor )

Cells Property

When the cells property is applied to a Range object the same object is returned.
It does have some uses though:

Range.Cells.Count — The total number of cells in the range.

Range.Cells( row, column ) — To refer to a specific cell within a range.

To loop through a range of cells

Cells automatically refer to the active worksheet.
If you want to access cells on another worksheet then the correct code is:

Range Property

When Range is not prefixed and used in a worksheet module, then it refers to that specific worksheet and not the active worksheet.

Selection Property

Selection will return a range of cells
Be aware that the Selection will not refer to a Range object if another type of object, such as a chart or shape is currently selected.
Using the Selection object performs an operation on the currently selected cells.
If a range of cells has not been selected prior to this command, then the active cell is used.

It is always worth checking what is currently selected before using the Selection property.

Relative References

It is important to remember that when a Cells property or a Range property is applied to a Range object, all the references are relative to the upper-left corner of that range.

ActiveCell returns a reference to the currently active cell
This will only ever return a single cell, even when a range of cells is selected.
The active cell will always be one of the corner cells of a range of cells. — will it what if you use the keyboard ??

Total number of populated cells

Window Object Only

This property applies only to a window object
This will enter the value 12 into the range that was selected before a non-range object was selected.

Window.RangeSelection property is read-only and returns a Range object that represents the selected cells on the worksheet in the active window.
If a graphic object is active or selected then this will returns the range of cells that was selected before the graphic object was selected.

Counting

Range of a Range

It is possible to treat a Range as if it was the top left cell in the worksheet.
This can be used to return a reference to the upper left cell of a Range object
The following line of code would select cell «C3».

Remember that cells are numbered starting from A1 and continuing right to the end of the row before moving to the start of the next row.

An alternative to this is to use the Offset which is more intuitive.

Range.Address

The Address method returns the address of a range in the form of a string.

Using the parameters allows you to control the transformation into a string (absolute vs relative).

RowAbsolute — True or False, default is True
ColumnAbsolute — True or False, default is True
ReferenceStyle — xlReferenceStyle.xlA1
External — True to return an external reference, default is false
RelativeTo — Range representing a relative to cell. Only relevant when ReferenceStyle = xlR1C1

Range.AddressLocal

This is similar to Address but it returns the address in the regional format of the language of the particular country.