Mironov. Ready-made macros in VBA Excel. VBA Excel: sample programs. Macros in Excel

Macro for highlighting cell A1 on each sheet in the active workbook. This also causes the screen to move.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Select Next Sheets(1) .Select Application.ScreenUpdating = True End Sub

Macro for copying the current sheet a specified number of times. Useful for testing some macros - made edits, checked them on a copy of the data. We ran out of copies—run the macro again

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Enter the number of copies of the current sheet") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets.Count) ActiveSheet .Name = "Copy" & j Next j Application.ScreenUpdating = True End Sub

Create sheets with titles from a specified range on a sheet

Sub CreateFromList() Dim cell As Range For Each cell In Selection Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Next cell End Sub

Markros for sending a letter with a delay. Modified macro from John Walkenbach's book Professional VBA Programming

Sub SendLetter() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " [email protected]" .Subject = "Sales report" .Attachments.Add "C:\Test.txt" .Body = "Email text" .DeferredDeliveryTime = Replace(Date, ".", "/") & " 11:00:00 " .send ".Display to generate a letter and open it End With On Error GoTo 0 Set OutMail = Nothing cleanup: Set OutApp = Nothing End Sub

Slightly modified table of contents macro from Nikolai Pavlov.
If a “Table of Contents” sheet already exists in the book, the macro prompts you to delete it. If not, creates a “Table of Contents” sheet and inserts links with sheet names

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False With ActiveWorkbook For Each Worksheet In ActiveWorkbook.Worksheets If Worksheet.Name = "Table of Contents" Then Answer = MsgBox("The workbook has a sheet with the name Table of Contents. Delete it?", vbYesNo) If Answer = vbNo Then Exit Sub If Answer = vbYes Then Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array(1)).Select Sheets.Add Sheets(1).Name = "Table of Contents" With ActiveWorkbook For Each sheet In ActiveWorkbook.Worksheets If sheet.Name<>"Table of Contents" Then Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name End If Next sheet End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub

Sorting Sheets from the VBA Wizards. The macro also sorts hidden sheets. Will not work if the book has a structure protected

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " remember the visibility state of each sheet and do everything visible For Each iSht In ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " sorting visible sheets For i = 1 To .Sheets.Count - 1 For j = i + 1 To .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Then .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With " restore the original visibility state of each sheet For Each iSht In ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Next Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Importing columns “Field1” and “Field2” from sheet “Sheet1” of Excel file “C:\Manager.xls” via ADODB connection and inserting content starting from cell A1 of the current sheet

The manual contains macros on the following topics:
Running a macro to search for a cell.
Run a macro when opening a workbook.
Run a macro when you enter “2” in cell.
Run a macro when you press Enter.
Add your own “Add-Ins” (Cell Format) tab to the panel.
Working with files (i.e. data exchange with TXT, RTF, XLS, etc.).
Checking the presence of a file at the specified path.
Search for the required file.
Automation of file deletion.
Free text in the status bar.
Restoring the status bar.
Creeping line in the status bar.

Quickly change the window title.
Changing the window title (hiding the file name).
Return to original title.
What is open at the moment.
Working with text files.
Writing and reading a text file.
Processing multiple text files.
Determining the end of a line in a text file.
Copying from a text file to Excel.

Copy content to a text file.
Export data to txt.
Export data to html.
Import data that requires more than 256 columns.
Create backup copies of valuable files.
Counting the number of times a file has been opened.
Output the file path to the active cell.
Copying the contents of an RTF file into Excel.
Copying data from a closed workbook.
Extracting data from a closed file.
Search for a word in files.
Create a text file and enter text into the file.
Create a text file and enter text (end of file detection).
Create Word documents based on an Excel table.
Commands for creating and deleting directories.
Get the current directory.
Change directory.

View all files in a directory.
View all files in a directory.
Microsoft Excel workspace.
Workbook.
Number of workbook names.
Workbook protection.
Banning the printing of books.
Opening a book (or text files).
Open the book and add text to cell A1.
How many books are open?
Closing all books.
Closing a workbook only when a condition is met.
Saves the workbook with a name that represents the current date.
Is the workbook saved?
Create a workbook with one sheet.
Create a book.
Removing unnecessary names.
Quick reproduction of a workbook.
Sorting sheets.
Finding the maximum value on all sheets of the workbook.
Worksheet.
Checking whether the worksheet is protected.
List of sorted sheets.
Create a new sheet.
Create a new sheet.
Removing sheets based on date.
Copying a sheet in a book.
Copying a sheet to a new workbook (created).
Moving a sheet in a book.
Move multiple sheets to a new workbook.
Replace an existing file.
"Flipping" the book.
Insert a header and footer with the name of the workbook, sheet and current date.
Does the sheet exist?
Does the sheet exist?
Displays the number of sheets in the active workbook.
Displays the number of sheets in the active workbook in the form of hyperlinks.
Displays the names of active sheets one by one.
Displays the name and sheet numbers of the current book.
Make the sheet invisible.
How many pages are there on all sheets?
Cell and range (columns and rows).
Copy rows to another sheet.
Copy columns to another sheet.
Counts the number of cells that contain the specified values.
Counts the number of cells in a range that contain the specified values.
Counts the number of visible cells in a range.
Determining the number of cells in a range and the sum of their values.
Counting the number of cells.
Automatic recalculation of table data when its values ​​change.
Entering data into cells.
Entering data using formulas.
Serial data input.
Entering text data into cells.
Displays the book name, sheet and number of sheets in the cells.
Removing empty lines.
Removing empty lines.
Removing empty lines.
Deleting a row by condition.
Removing hidden rows.
Remove used hidden or zero-height rows.
Removing duplicates using a mask.

Selects a range above the current cell.
Select a cell and place a number there.
Highlighting negative values.
Selecting a range and using absolute addresses.

Selecting cells at intervals.
Selecting multiple ranges.
Movement through cells.
Finds the nearest empty cell in a column.
Finding the maximum value.
Search and replace by pattern.
Search for a value and display the result in a separate window.

Search with highlighting of found data.
Search by condition in a range.
Finds the last non-empty cell in a range.
Finds the last non-blank cell in a column.
Finds the last non-blank cell in a row.
Search for a blue cell in a range.
Finding a negative value in a range and highlighting it in blue.
Finding the presence of a value in a column.
Finding matches in a range.
Search for a cell in a range.
Search for a cell in a range.
Finding an approximate value in a range.
Finds the beginning and end of a range containing data.
Finding the beginning of the data.
Automatic replacement of values.
Fast filling of a range (array).
Filling through interval(array).
Fills the specified range (array).
Fill the range(array).
Calculation of the sum of the first values ​​of the range.
Placement in a cell of an electronic watch.
"Alarm".
Design of the upper and lower boundaries of the range.
Active cell address.
Coordinates of the active cell.
Active cell formula.
Getting a formula from a cell.
Cell data type.
Output the address of the end of the range.
Obtaining information about the selected range.
Take the word with the 13th character in the cell.
Creating a editable list (table).
Check for empty value.
Cell intersection.
Multiplying the selected range by.
Simultaneously multiply all data in a range.
Divide the range into.
Squaring each cell in the range.
Summarizes data from visible cells only.
The sum of cells with numeric values.
When summing, the cursor is inside the range.

Interest accrual depending on the amount.
Interest accrual depending on the amount.
Summary example of commission calculation.
Movement along the range.
Offset from the selected cell.
Iterate through cells down the column.
Creating a range fill.
Selecting a cell parameter.
Range splitting.
Merge range data.
Merge range data.
Find out the maximum column or row.
Limiting the possible range values.
Testing the speed of reading and writing ranges.
Open MsgBox when a cell is selected.
Hiding a row.
Hiding multiple rows.
Hiding a column.
Hiding multiple columns.
Hiding a row by cell name.
Hiding multiple rows by cell addresses.
Hiding a column by cell name.
Hiding multiple columns by cell addresses.
Cell flashing.
Working with notes.
Displays all the notes in the worksheet.
Comment extraction function.
List of notes for protected sheets.

List of notes in a separate list.
List of notes in a separate list.

Counting the number of comments.
Counting notes.
Select cells with comments.
Display all notes.
Change the color of notes.
Adding notes.
Adding notes to a range based on condition.
Move a comment to a cell and back.

Transferring values ​​from a cell to a comment.
Custom tabs on the ribbon.
Toolbar addition.
Adding a button to the toolbar.
One button panel.
Panel with two buttons.
Creating a panel on the right.

Creating a custom menu (option 1).
Creating a custom menu (option 2).
Creating a custom menu (option 3).
Creating a custom menu (option 4).
Creating a custom menu (option 5).
Creating a custom menu (option 6).
Creating a list of Excel main menu items.
Creating a list of context menu items.
Display the toolbar under a certain condition.
Hiding and showing toolbars.
Create a tooltip for my buttons.
Create a menu based on worksheet data.
Creating a context menu.
Blocking the context menu.
Adding a command to the Tools menu.
Adding a command to the View menu.
Creating a list panel.
A cartoon starring an assistant.
Add text, title, button, and icon to the assistant.
New assistant options.
Using the assistant to select a fill color.
DIALOG WINDOWS.
INPUTBOX function (via value input).
Call preview.
Setting up data entry in the dialog box.

Opens the (Open File) dialog box.
Opens the (Print) dialog box.
Other dialog boxes.
Calling the browser from Excel.
Data entry dialog box.
Font settings dialog box.
Default values.
Text formatting. Tables. BORDERS AND FILL.
Displays a list of available fonts.
Selecting all numbers from the text.
Capitalize only at the beginning of the text.
Counting the number of repetitions of the search text.
Selecting an arbitrary element from the text.
Display text backwards.
English text is in capital letters.
Launching a symbol table from Excel.
information about the user, computer, printer, etc.
Get username.
Monitor resolution output.
Get information about the printer you are using.
View information about your computer's drives.
USERFORMS.
DIAGRAMS.
Building a chart using a macro.
Saving the diagram in a separate file.
Build and delete a chart with the click of a button.
Display a list of diagrams in a separate window.
Applying a random color palette.
Chart transparency effect.
Construct a chart based on data from multiple worksheets.
Create captions for chart data.
DIFFERENT PROGRAMS.
Program for composing crossword puzzles.
Create a DVD cover.
Game "Minefield".
Game "Guess the animal."
Calculation based on cells of a certain color.
OTHER FUNCTIONS AND MACROS.
Calling function keys.
Calculation of the arithmetic mean.
Converting numbers into “money”.
Search for the nearest Monday.
Counting the number of complete years.
Calculation of the weighted average.
Converting the month number to its name.
Using relative links.
Converting an Excel table to HTML format.
Random number generator.
Random numbers - based on range.
Applying a function without entering it in a cell.
Counting named objects.
Enabling an autofilter using a macro.
Creating a creeping line.
Creating a running picture.
Rotating autoshapes.
Calling the color table.
Creating a calculator.
Declension of last name, first name and patronymic.
DATE AND TIME.
Output date and time.
Output date and time.
Getting the system date.
Retrieving date and hours.
Function DateFull. The version of MS Office used is not specified.

VBA is considered the standard scripting language for Microsoft applications, and it is now included with all Office applications and even applications from other companies. Therefore, once you master VBA for Excel, you can immediately move on to creating macros for other Microsoft software products. Moreover, you will be able to create full-fledged software products that simultaneously use the functions of a wide variety of applications.

How to enable macros in Excel

By default, the tab responsible for managing and navigating macros in Excel is hidden. To activate this option, go to the tab File to the group Options. In the dialog box that appears OptionsExcel go to the tab Ribbon customization, in the right combo box, place a marker opposite the tab Developer. These steps are relevant for versions of Excel 2010 and older.

A new tab will appear on the ribbon Developer with Excel automation controls.

Writing macros in Excel

In the tab Developer in Group Code, click the button Record a macro. A dialog box will appear Record a macro, which requests some information about the future code being written. If this is your first time creating a macro, you can simply click the button OK. From now on, Excel will record every user action in a VBA module, be it data entry, formatting, or creating charts. To stop recording a macro, click the button Stop recording which is in the same group Code.

You can also take advantage of an alternative option for recording macros by using the button Record a macro, which is located in the lower left corner of the Excel workbook (to the right of the status Ready).

Now you can view a list of all created macros by clicking on the button Macro, located in the group Code. In the dialog box that appears, you can give more descriptive names to your codes or set keyboard shortcuts that would run a particular macro. An alternative option to launch this window is to press Alt + F8.

Editing Macros

Congratulations! You've written your first macro. It would be logical to check now what code Excel generated for us. The generated code is written in VBA (Visual Basic for Applications). You have to open it to see it. EditorVB(VBE), which is launched by pressing Alt + F11 or the button VisualBasic on the tab Developer.

To avoid confusion in the editor, you can work with only one tab in a workbook, sheet, or module. This is what the editor looks like in real life.

At this stage, I propose to study in more detail the various windows and menus of the VBA editor. This will help you save a lot of time in the future.

To view the code, click on the thread Modules in the projects window and double-click on the branch that appears Module1 . The editor will open a window with the code, as shown in the picture.

Here you can edit the generated code that was written while working in Excel. For example, you need to fill a certain column with values ​​from 1 to 10. You already have the first three steps, which enter the values ​​1, 2 and 3 in the first three cells of column A. We need to complete the remaining seven steps.

If you look at the code above, you will see that the macro is structured in a certain way. The application first moves the cursor to the cell using the Range("A1").Select command, then edits its contents using ActiveCell.FormulaR1C1 = "1". So for the remaining steps we can repeat these steps, changing the cell address and the value you want to write to that cell. For example, to set cell A4 to 4, you would write:

And repeat similar steps for the remaining values.

Once you're done editing, save your book. You can run the macro by pressing the F5 button, or, returning to the Excel workbook, go to the tab Developer to the group Code -> Macros and select from the list the macro you are interested in.

Take a few minutes to carefully study the code that Excel generated. If you're a beginner, investing a few minutes in learning the code will yield amazing results in getting to know VBA objects later on. Please note that the example we discussed is just an illustration. There are faster and more effective ways to achieve similar results, which we will discuss next.

Increase the speed of Excel macro execution

So far so good. Let's look at a couple of tricks that will help speed up macro execution. Let's take the code snippet above as an example. Modern computers will run the code in question so quickly that you won't even notice it. But what if you need to perform the operation 50,000 times. This will take some time. If the macro you write is hundreds of lines long, you can speed up code execution by trimming the part of the processes that is not used during macro execution.

Using the Application.ScreenUpdating Command

The first trick is to avoid updating the screen while the macro is running. This will allow Excel to save the computer's processing power and update the screen with fresh values ​​only after all the code has been executed. To do this, you need to add a command to disable screen refresh at the beginning of the macro and a command to enable screen refresh at the end of the macro.

The Application.ScreenUpdating command tells Excel to stop displaying the recalculated data on the screen and return the finished values ​​at the end of the code execution.

Using the Application command. Calculation

The second trick is to disable automatic calculations. Let me explain. Every time a user or process updates a cell, Excel tries to recalculate all the cells that depend on it. So let's say if the cell that the macro is trying to update affects 10,000 other cells, Excel will try to recalculate them all before the code finishes executing. Accordingly, if there are a number of influencing cells, recalculation can significantly slow down code execution. To prevent this from happening, you can install the Application command. Calculation at the beginning of the code, which will switch the recalculation of formulas to manual mode, and then return automatic calculation at the end of the macro.

Be careful not to forget to switch this option back to automatic mode at the end of the macro. Otherwise, you will need to do this in Excel itself by clicking on the tab Formulas to the group Calculation and choose Calculation options –> Automatic.

Avoiding selecting cells and ranges

In automatic macro recording mode, you may notice that Excel very often uses the cell selection command, for example, Range("A1").Select. In our example, we used this command several times to select a cell and change its value. You can avoid this by simply specifying the cell address and giving it the required value (The macro recorded the cursor movement from one cell to another, hence inserting these steps. However, they are not necessary). So, a more efficient code would look like this.

In this case, we simply referenced the cell and gave it the required value, without selecting it at all. This method is faster than the previous one.

Excel Macro Examples

Below are some sample VBA codes that will help you automate the most common tasks.

To automate repetitive tasks, you can record a macro using the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply one format to all of them. This can be done using a macro. You can record a macro using the desired format and play back the macro if necessary.

When you record a macro, all necessary actions are recorded as Visual Basic for Applications (VBA) code. These actions can include entering text or numbers, selecting cells or commands on the ribbon or menu, formatting cells, rows, or columns, and even importing data from an external source such as Microsoft Access. Visual Basic for Applications (VBA) is part of the Visual Basic programming language. It is available in most Office applications. Although VBA allows you to automate processes within and between Office applications, you don't need to be able to program or know the VBA language because the macro recorder does everything you need.

It's important to know that when you record a macro, almost everything you do is recorded. So if you make a mistake, such as pressing the wrong button, the macro recorder will record that action. In this case, you can write the entire sequence again or change the VBA code. Therefore, before recording the process, you should work it out well. The more accurately you record the sequence, the more efficiently the macro will work.

Developer, which is hidden by default, so you need to enable it first. For more information, see Display the Developer Tab.

Record a macro

On the tab Developer click Macros to view the macros associated with the workbook. Alternatively, you can press the keys ALT+F8. This will open a dialog box Macro.

Attention:

Working with Recorded Code in the Visual Basic Editor (VBE)

With the Visual Basic Editor (VBE), you can add your own variables, control structures, and other elements to recorded code that the macro recorder does not support. Since the macro recorder captures almost every step performed during recording, you may also need to remove unnecessary code. Reviewing recorded code is a great way to learn VBA programming or hone your skills.

An example of changing the recorded code can be found in the article Getting started with VBA in Excel.

Record a macro

Before recording macros, it is useful to know the following:

A macro written to work with an Excel range will only run on cells in that range. Therefore, if you add a new row to the range, the macro will not apply to it.

If you need to record a long sequence of tasks, we recommend using several smaller macros instead.

A macro can also contain non-Excel tasks. The macro process can span other Office applications and other programs that support Visual Basic for Applications (VBA). For example, you can record a macro that first updates a table in Excel and then opens Outlook to email it.

Macros and VBA tools are located on the tab Developer, which is hidden by default, so you need to enable it first.

Select Excel > Options> Tape and panel.

To record a macro, follow the instructions below.

Working with macros recorded in Excel

On the tab Developer click Macros to view the macros associated with the workbook. This will open a dialog box Macro.

Note: Macros cannot be canceled. Before you run a recorded macro for the first time, save or create a copy of the workbook to prevent unwanted changes. If you are not satisfied with the results of the macro, you can close the workbook without saving it.

Here's more information about working with macros in Excel.

additional information

You can always ask a question from the Excel Tech Community, ask for help in the Answers community, or suggest a new feature or improvement to the website

Good afternoon

I want to devote this article to such a huge section of MS Excel as macros, or rather, let’s start from the beginning and look at how to create a macro in Excel, what it is for and how to use it in your work.

As you know from your own experience, when working with a macro there is a lot of “routine”, that is, the same operations and actions are performed that are necessary to obtain the result, this can be filling out the same type of tables or forms, processing data, similar as twins weekly, monthly reports, etc. But using macros will allow you to perform these actions automatically, using the capabilities of Excel to the fullest, throwing these routine and monotonous operations onto the powerful shoulders of Excel. Also, the reason for using macros may be to add the necessary capabilities that are not yet implemented in standard Excel functions (for example, output, collecting data on one sheet, etc.).

If you've never heard of a macro, the most accurate definition of it would be: these are actions that are programmed for a specific sequence and written in the programming environment in Visual Basic for Applications (VBA). Running a macro can be done multiple times and this will force Excel to perform any sequence of actions we need that we simply don’t like or don’t want to do manually. Despite the great variety of programming languages ​​for the entire Microsoft Office complex, VBA is the standard and it works in any application in the office suite.

So, there are 2 ways to create a macro in Excel:

Create a macro in Excelusing a macro recorder

First, let's clarify what a macro recorder is and what a macro has to do with it.

Macro recorder is a small program built into Excel that interprets any user action in VBA programming language codes and writes commands that were obtained during the work process into the program module. That is, if we create the daily report we need with the macro recorder turned on, the macro recorder will record everything in its commands step by step and, as a result, create a macro that will create a daily report automatically.

This method is very useful for those who do not have the skills and knowledge of working in the VBA language environment. But such ease of execution and recording of a macro has its disadvantages, as well as its advantages:

• A macro recorder can only record what it can touch, which means it can record actions only when buttons, icons, menu commands and everything like that are used; such options are not available to it;
• If an error was made during the recording period, it will also be recorded. But you can use the undo button to erase the last command that you wrote incorrectly in VBA;
• Recording in the macro recorder is carried out only within the boundaries of the MS Excel window, and if you close the program or turn on another one, the recording will be stopped and will no longer be performed.

To enable the macro recorder for recording, you must perform the following steps:

The next step in working with the macro recorder will be to configure its parameters for further recording of the macro; this can be done in the window "Record a Macro", Where:

• Macro name field- you can write a name that you understand in any language, but it must begin with a letter and not contain punctuation marks or spaces;
• Keyboard shortcut field- will be used by you in the future to quickly start your macro. In case you need to register a new one, this option will be available in the menu “Tools” - “Macro” - “Macros” - “Run” or on the tab "Developer" pressing a button "Macros";
• "Save to..." field— you can specify the location where the macro text will be saved (but not sent), and there are 3 options:
  • "This book"— the macro will be recorded in the module of the current workbook and can only be executed when this Excel workbook is open;
  • "A new book"— the macro will be saved in the template on the basis of which an empty new workbook is created in Excel, which means that the macro will become available in all workbooks that will be created on this computer from that moment on;
  • "Personal Macro Book"- is a special Excel macro workbook called "Personal.xls" and is used as a special storage library of macros. At startup, macros from the “Personal.xls” workbook are loaded into memory and can be launched in any workbook at any time.
• "Description" field- here you can describe what the macro should do and how, why it was created and what functions it has, this is a purely informative field, which is called memory.

After you have launched and recorded your macro, having completed all the necessary actions, you can stop recording with the command "Stop recording" and your macro will be created using the macro recorder.

Create a macro in Excelin the Visual Basic editor.

In this method, we will look at how to create a macro in Excel through the VBA program editor which, as I said above, is built into any version of Excel.

Launching the VBA program editor occurs differently, depending on the version of your Excel program:

• in versions of Excel 2003 and later, we need in the menu "Service", select item "Macro" and press "Visual Basic Editor";
• in versions of Excel 2007 and later, we need on the tab "Developer" press the button "Visual Basic Editor". If you do not find this tab, you need to activate it by selecting the menu item “File” - “Options” - “Customize Ribbon” and in the dialog box, use the checkbox to activate the tab "Developer".

In the window that opens, you can see the interface of the VBA editor, everything here is written in English and there are no localizations, you don’t have to look for it, but just accept it and work, especially since it’s only incomprehensible at first, and then everything will be familiar.

So, how to work in the VBA editor, where is what is stored and how to create a macro in Excel. Questions of this nature arise immediately as soon as you see the editor, and now we will consider all of them.

All our macros will be stored in so-called software modules. In any of the books of any version of Excel, we can create any program modules in any quantity and place all the macros we have created in them. One module can contain any number of macros you need or create. Modules are available in the window "Project Explorer" and are located in the upper left corner of the macro editor (you can also call it with the key combination CTRL+R).

Program modules in the VBA editor exist in several types and are used for different options and situations:

Actually, the macro itself, when working in a standard module, looks like this:

Let's look at an example of a working macro:

• All macros will necessarily begin with the operator Sub, followed by the name of your macro and a list of arguments in parentheses. In cases where there are no arguments, the parentheses should be left empty;
• All macros must end with the operator End Sub;
• Data that is between operators Sub And End Sub, is the body of the macro that will run when the macro is run. In the example, the macro checks and, when entering data, finds it in the database list and displays the value specified by the criteria.

As you can see, the second method is more difficult to use and understand if you have no experience in programming in general or in VBA in particular. It is very difficult to understand and figure out what commands are entered and how, what arguments it uses in order for the macro to start doing its work automatically. But the one who walks will master the road, as the ancient sages said, and therefore you should not give up, but follow the commandments of grandfather Lenin...

Creating a button to run macros in the toolbar

As I said earlier, you can call a macro procedure with a hotkey combination, but it is very tedious to remember which combination is assigned to whom, so your best bet is to create a button to run the macro. Buttons can be created of several types, namely:

This method is available for any version of MS Excel and consists in the fact that we will place the button directly on our worksheet as a graphic object. To do this you need:

• In MS Excel 2003 and older go to menu "View", choose "Toolbar" and press the button "Forms".
• In MS Excel 2007 and later you need on the tab "Developer" open dropdown menu "Insert" and select an object "Button".

After all this, you must draw a button on your sheet while holding down the left mouse button. After the drawing process is completed, a window will automatically turn on, where you will need to select the macro that is required to be executed when you click on your button.

How to Create Custom Functions in VBA

In principle, creating so-called user-defined functions is not very different from creating a regular macro in a standard program module. The difference between these concepts is that the macro will perform the actions contained in it with book or sheet objects (these are formulas, cells, etc.), but the user function only works with the values ​​​​that it receives from us and these are the arguments initial data for calculations.

For example, to create a custom function for value added tax, also known as VAT, we need to open our VBA editor and add a new module, select from the menu "Insert" paragraph "Module" and enter the text for our function there: It should be noted that the main difference between a function and a macro is the header Function replaced Sub and there is a completed list of arguments, in our example this is Summa. After our code has been entered, it will become available in the standard Function Wizard window, which is located in the menu "Formulas", paragraph "Insert Function".
And select a category "User Defined" in which our written function will be displayed "NDS".
After selecting our function, you can place the cursor on the cell with the argument, which will contain the amount for which we calculate VAT, everything happens as with a regular function.
And that's all for me! I really hope that article on how to create a macro in Excel was clear and useful to you. I would be very grateful for your comments, as this is an indicator of readability and inspires me to write new articles! Share what you read with your friends and like it!