How to learn to program excel. How to write a macro in Excel using the VBA programming language

In addition to tips on how to learn to write VBA macros for Excel, I’ll tell you my story - how I learned to program in VBA.

It all started in the last century, in 1993 year when I was in school. During computer science lessons, in our class, there were monster computers called “Corvette”, and we wrote simple programs on them in BASIC. These computers differed from “normal” (IBM-compatible) ones quite noticeably - they had a monochrome display, but no HDD and floppy disk drives, and the only thing that ran on it (automatically, after booting) was the Basic language interpreter.

Actually, my interest in programming showed up earlier - at the age of 9, when I saw in the magazine “Modelist-Konstruktor” for 1988 (which I read regularly, from cover to cover) some machine codes for the “Specialist” computer. . I was then very interested in the possibility of entering some letters and numbers into the computer to make it do what I wanted. Alas, the financial situation of my parents at that time left much to be desired, and purchasing any semblance of a computer was out of the question. So I amused myself by copying these codes from the magazine into a notebook, in the hope that someday I will have a computer and I will be able to control it :)

Since in those days a home computer was a luxury (only some had Spectrums at home, and some were lucky to have an expensive IBM PC 286), I learned Basic exclusively at school, sometimes staying late after school. The school curriculum provided for only 2 years of study in the subject “Informatics” (grades 10 and 11), but since childhood I was interested in all kinds of electronics, so I began to attend classes from the 8th grade, reading the only instruction book on the “Corvette” (here is this book, with which my journey into programming began, it is also in PDF), and trying to compose simple programs. Soon the teacher began to let me use the main computer (only it had the ability to save programs on a 5.25" floppy disk - and even then, it took a lot of work) - and my programs became much more complex. Once I even managed to implement the game "Poker" with graphical interface(it turned out to be about 2000 lines of code) - this despite the fact that the Basic interpreter allowed only symbols and graphic primitives to be displayed on the screen.
As a result, by the end of school ( 1996 year) I skillfully used loops, and had some experience in composing algorithms.

As for algorithms, this term still makes me allergic. While studying at school, during lectures on computer science (I studied quite well, and we, winners of all sorts of Olympiads, were sent for additional training in higher mathematics and computer science at a full-time and correspondence school at USU), we were forced to write algorithms in some kind of pseudocode ( a mixture of Russian with Turbo Pascal). Since I was noticeably behind my peers in programming (they studied on IBM-compatible computers, which had this same Pascal and much more, and I knew and saw nothing except BASIC), it was very difficult to master the syntax algorithmic language. In addition, I did not understand (and still do not understand) why it is necessary to spend an hour compiling an algorithm for the simplest program, if you can just open the editor and write a few lines of code (for me, who normally knows English, the syntax of the BASIC language was much clearer than this mysterious algorithmic pseudocode). There is no talk about flowcharts at all - when I remember the institute where we were forced to draw flowcharts (on several A4 sheets) for the simplest programs of 10 lines, I cease to understand how the desire to study programming can still remain after all this.. .

After graduating from school, at the institute, I studied a little Turbo Pascal, where my programming training stopped for a decade (there was no computer at home or at work), and I forgot almost everything I studied in computer science classes.
However, in 2004 year, at my work, the bosses finally decided to replace the typewriter (on which I was already very skillfully hitting the keys) with a simple computer (it was a Pentium-1 with 64 megabytes of RAM and Windows 98). Considering that I saw Windows for the first time then, I had to learn how to use a computer from scratch.

I mastered the computer quickly, because... Already on the second day, Windows crashed after an unsuccessful attempt to overclock the processor, and I had to urgently learn how to reinstall the OS and programs. The person who helped me install the computer on the first day of my acquaintance with the computer operating system and basic programs, was not able to help me constantly. He left me Windows and Office distributions, a couple of disks with different utilities, after which he explained to me how to enter and format text in Word, how to open and save files through the program menu, and left, wishing me good luck. I had no choice but to master the system at random.

What a blessing (after the typewriter) was the ability to save and edit printed documents... and it didn’t bother me at all that launching Word took about 20-30 seconds (and CorelDRAW loaded in a couple of minutes). The computer became my main hobby - in a couple of months I mastered the basic programs, six months later I was poking around in the registry with might and main, restoring crashed Windows (who remembers Win98 will understand me), along the way I mastered the computer architecture (I constantly had to re-plug memory modules, connect additional hard disks, and so on.)

My job was to prepare various documents(acts, orders, etc.), and I began to think about how to simplify it (I don’t like doing the same thing every day). Almost immediately I noticed the “Visual Basic Editor” item in the Microsoft Word menu, and went to see what kind of beast it was. I poked around, looked at the built-in help, but didn’t understand much. This Basic was not at all similar to the one I studied at school. I figured out that you can draw forms with buttons and text fields, but I still didn’t understand how to make it work. There was no Internet access, so I couldn’t look at examples of use.

I would have abandoned this matter, but then, one fine day, Word files began to open slowly, and some kind of error began to pop up when opening them. I started looking into it and discovered that the error occurs when executing a 20-line macro that mysteriously ended up in all my documents. After looking at the code, I realized that this was nothing more than a macro virus infecting all Word files that were opened and unsuccessfully trying to send some data over the network. (by the way, this was the first and last time when I saw a macro virus - since then thousands have passed through me Excel files and Word, and I have never encountered macro viruses in them). This interested me - if a macro can collect data from a computer, copy itself to other files, and even send something to another IP address, then almost anything can be done using macros!

And I began to master macros - read the built-in help for VBA, tried code examples from this help, wrote simple loops (which I remembered from school), drew simple forms, etc. I made full use of recording macros in Word, optimizing the resulting code. (I didn’t work with Excel at that time - I didn’t really understand why it could be useful). This brought little benefit - but it was interesting to me, because... I have been interested in programming since school. Gradually, my work began to become simpler, although automation took more time than it would have taken to perform all the actions manually.

One day, in 2006 year, I really needed VBA programming skills. As part of my work, I needed to create (draw in CorelDRAW) many similar evacuation plans (these are drawings of rooms with arrows and specific icons). It was possible to draw them manually, but in order for the result to comply with GOST, it was necessary to maintain different proportions and distances between elements. Moving icons around the sheet, adjusting the distance from lines and neighboring icons “by eye,” quickly became boring. And I also had to align everything, quickly draw and beautifully connect dotted lines, do pre-press preparation every time, etc. etc... As a result, after spending 2 or 3 months on automating this process, I achieved a reduction in the time for producing a drawing several times. In addition, it was pleasant to look at the faces of my colleagues when miracles happened before their eyes - lines were drawn and moved on the screen on their own, arrows aligned and bent, everything flashed, and a minute later I was already pulling out a floppy disk from the computer, ready to be sent to the printing house (with two copies of the file, completely ready for printing)

It was summer outside 2008 of the year. I had nothing better to do, so I decided to help people who came to the forums for advice with solving problems in Excel. I quickly got involved in it - it was nice to hear gratitude from people for whom my “magic button” saved many hours of the same type of work. In addition, the help required very little effort - sometimes it was enough to write 2-3 lines of code. Then I began to realize that my knowledge of VBA and Excel was still very, very poor, and I began to learn, understanding the solutions proposed by more experienced colleagues.

It took 2 or 3 years, during which I helped everyone on the forums, to begin to feel confident in Excel. At first I wrote very simple macros, then more complex ones. A year later, they started contacting me with orders - then I was ready to work for pennies (because the goal was not making money, but helping people). The first more or less serious order for which I charged a fee was a program for admissions officers - I had to implement a database in Excel. In 3 weeks of torment and rework (the program was ordered by a student who herself did not know exactly what she needed), the program was written - and I earned about 3,500 rubles from it. Since then I haven’t worked with students - there’s not enough money, there are no technical specifications, there are a lot of requests for rework (because the teacher didn’t like something), and in general it’s unpleasant to realize that the program will be launched only once, and then no one will use it will.

IN 2009 year I made a website website. It could only be called a website with a stretch - it consisted of one page (this is what it looked like at that time). As you understand, there could be no benefit from such a site - those who visited the site already knew what I was doing. But I wanted more - for the site to benefit both me and its visitors. Therefore, I urgently had to study website building - and, thanks to the help of a friend, a few months later the site acquired an engine in the form of CMS Drupal (I would not have been able to do this myself). I had not yet figured out what to publish on the site, so I started using it as notebook- publish useful universal macros and functions there, so that you don’t have to look for them every time different files, remembering when and to whom I did this.

Less than six months later, site traffic began to grow. It turned out that the macros I posted were interesting to many, and this gave me an incentive to further develop the resource - I began to post ready-made solutions in the form of Excel files and add-ons, and paid more attention to the functionality and structure of the site. At the same time, the number of orders began to grow - site visitors saw that the possibilities of macros were limitless, and they contacted me with a request to automate their work. But still, there were too few orders to seriously consider programming as a source of income - there were 2-3 per month small orders. Even if they ordered a relatively complex program, it did not bring joy - due to my inexperience, I immediately agreed on the amount, and then, out of the goodness of my heart, I agreed to various modifications that were not included in the original assignment for the program. As a result of this, I worked for weeks on one program, and earned very little - since it was initially assumed that there was very little to be done.

But this time (almost a year) was not wasted - I did not earn any money, but I gained invaluable experience in developing user interfaces (I had to draw input forms with hundreds of fields), solving non-standard Excel tasks(website processing, working with files, images, etc.), at the same time learning to understand customers and their tasks at a glance (eh, you should have seen how some people formulate tasks for complex programs in one sentence of 10 words... ). I had not yet become a telepath, but I began to guess what the customer really wanted, and offered people exactly what they needed (most people themselves do not know what they want when they ask for help). And everything would have been fine, if not for the lack of orders (by that time, helping people on the forum had become boring - the same questions every day, no one wanted to use the search, and even the students were freeloaders).

And I began to think about how to increase the number of orders - I wanted my hobby to finally begin to generate tangible income. Again I had to get into website building - master the basics of SEO, design, and website building in general. And one day I realized what was still missing on the site - a large button for placing an order. Without thinking twice, within 5 minutes this button (which you can now see in the left top corner pages) has been added to all pages of the site. The result was not long in coming - within a month I received more orders than in the entire previous year.

Arrived 2012 year. The number of orders grew to such a volume that there was no way for me to handle them alone. I began to transfer some of the orders to my colleagues (especially large and complex orders), because I still can’t earn all the money, and I don’t really want to sit at the monitor for days on end. At the same time, at my main job (from 8:00 to 17:00 I worked as an engineer at a local Internet provider), the workload increased noticeably, and I no longer had time to write macros in work time. As a result, there was no time left for the development of the site - in the evenings I only had time to sort out some of the orders, and unread letters began to accumulate in the mail.

It was time to change something in my life - to make a choice between work and programming. And so, in mid-July 2012, I made this choice - I wrote a letter of resignation from my main job, deciding to devote myself to programming (developing macros for Excel). Since then, I have more than enough free time, so I plan to start expanding the site again and work more actively on orders (to eliminate situations where customers waited for several weeks for their macros).
I don’t know yet what will come of all this, time will tell. I think the demand for macros for Excel will continue for at least another 2-3 years, so I won’t be left without bread and butter.

Also in plans for the near future is the development and publication of shareware add-ins for Excel (the first attempts at this field have yielded good results).
And then I plan to start developing databases (with a web interface), and gradually switch to working with web services.

<спустя 4 года, решил продолжить статью>

And now it’s already in the yard 2016 year. To my joy, Microsoft has not yet given up support for VBA in Excel, so I continue to work in this area. These 4 years changed my life a lot - my income increased several times, I started traveling a lot, and I had less time to work. The number of orders for macros was constantly increasing, so I began to collaborate with several programmers, to whom I transferred orders when I could not cope myself due to lack of time. Some of my colleagues (to whom I gave orders for work) disappeared over time, while others, on the contrary, collaborated with me more and more actively - and now I have a team that solves any problems (one colleague takes simple macros and makes them quickly, another makes complex macros using non-standard solutions, the third deals with “murky” volume orders, where you need to discuss the task for a couple of weeks, and then do it for another month, the fourth specializes in formulas, the fifth sets up parsers, etc. - each has its own area of ​​activity).

Finding a good team was not an easy task - although I myself was not really looking for anyone (half of them approached me for work, I wrote to the rest myself, because I knew from everyone on the Excel forums that they were good specialists), not everyone was able to work with them for a long time. Some rarely appeared online and did not respond quickly enough to orders, others perceived work more as a hobby (if suddenly there was no desire to work, such a person could disappear for a week, or even a month, without warning either me or the customer). One person even managed to scam me out of money (he took several orders and disappeared without paying me my commission). But all this is in the past - now I only have trusted guys (with whom I have been working for several years) who take orders of any complexity and always complete the work.

Since I managed to get rid of the “turnover” (a large number of small orders, although they bring in good income, but take up 100% of my time), I began to pay more attention to the development and promotion of universal add-ins for Excel. Now I have 9 such add-ons in my arsenal, but only 5 are successfully sold: FillDocuments (my very first work, until recently, was among the leaders in income, and is in stable demand, since everything that could be useful for users to fill out documents has already been implemented there) , PastePictures (sold since 2012, but began to gain popularity relatively recently), Unification (3 years ago this was the most complex of my programs), Lookup (the simplest of my add-ons, essentially a replacement for the VLOOKUP formula, but very popular among users) and Parser (a universal parser for collecting data from sites and files, the most sophisticated of my programs to date - 17,600 lines of code). Regarding the parser, back in 2013 the idea of ​​doing something like this seemed absolutely unrealistic to me (and I refused customers, talking about the impossibility of making a universal solution for websites like Unification - a price processing program), but then the number of orders for website parsers began to increase , and I decided to try it, which I have never regretted - the income from solutions based on this add-on exceeded all my expectations.

But not all programs were successful. I completely refused to sell one program (YandexMarket) - the site changed too often, the program required improvements, the algorithm for finding the necessary data was not obvious, then a captcha appeared - and I decided that it was not worth wasting time on further development of the program, returning the money to several buyers. Other programs, such as BarcodeScanner, Labels, SearchText, although they have recouped the time spent on development and continue to generate income, but cannot be compared with the five leading programs.

As the universal add-ons described above developed, the so-called “engine” of the program was formed - a set of macros and interface elements that allows you to quickly create a ready-to-sell solution from any idea. At first the engine was needed to support updates (so that the user could check and install an update with one click), later it gained security functionality (hackers began to pay attention to my solutions, since the demand for them grew every year, and I had to invent various schemes protection against illegal use, despite the fact that the built-in code protection in Excel is no good, because it can be removed in a couple of seconds). Along the way, I had to create functionality for activating and updating programs on the site side; I began to understand a little about PHP and MySQL, making scripts that interacted with the program engine.

For the last couple of years, most of my time has been spent on providing technical support (there are more and more buyers of programs every year), and on developing the parser add-on, as the most popular and promising of my solutions (I add new functionality, publish examples of customized parsers). My level of programming, thanks to the parser and the new program engine, has grown noticeably - even though my code 5 years ago was of quite high quality and worked without failures, but now I take a more responsible approach to the design of the code, faced with the need for many years of supporting my add-ons with many thousands lines of code. Now, along with the introduction of a new (multilingual) engine into all add-ons, I am refactoring (updating) the code, making it simpler, understandable, and universal. By the way, about multilingualism, I decided to sell my add-ons outside the CIS, and even made a separate website (to post only universal add-ons there, with an interface and description on different languages), but I’m still too lazy to do this. Maybe someday they will get around to it (but for now there are enough buyers from the CIS)

My plans for the future (as of September 2016) are to develop the area of ​​website parsing in Excel, and finally make video instructions for my programs (I’m not too lazy to add new functionality to the program for a couple of weeks in a row, while users and They can’t figure out what they have, due to the lack of a full-fledged certificate. Well, I don’t like recording videos))
Well, it’s time to enter the international market, since all my programs have become multilingual (although translation is only into English for now, and only partially)

<продолжение следует>

Book: Using macros in Excel.

Pages: 507
Format: DJVU
Size: 8.02 Mb

Despite the powerful functionality provided by the Excel user interface, there are a number of tasks that can only be performed programmatically. The book "Using Macros in Excel" is an introduction to Excel VBA programming, containing examples of solving various practical problems that arise when working in Excel.The book's material is intended for Excel users, as well as programmers who are not familiar with object model Excel. Along with the sections on developing macros for Excel 2002, it provides an introductory course on writing macros and programs in the Excel environment.

Book: Intensive Excel Programming Course Over the Weekend

Publisher: Dialectics
Pages: 421
Format: DJVU
Size: 12.6 MB
Quality: Normal
Language: Russian
Genre: programming
The year of publishing: 2004
ISBN: 5-8459-0687-3

Possibilities Microsoft Excel are not limited to working only with data tables. For processing tools spreadsheets hiding a strong programming language - VBA (Visual Basic for Applications). However, almost any user has the opportunity to learn how to write programs in VBA to solve the most diverse problems in Excel - from mechanical execution of calculations to creating a system for entering data with its own screen forms and with the possibility of revising the correctness of the entered values.

Book: Programming in VBA 2002

Quality: Normal
Language: Russian
Genre: programming

The book contains a course on programming in Visual Basic for Applications (VBA), which is the base language in Microsoft Office applications (Word, Excel, Access, PowerPoint, FrontPage, Visio, etc.). The book is intended for beginners to program in the Windows environment using Word, Excel, and PowerPoint objects.Part of the book is devoted to the development of Office applications that use databases stored in both separate files, and on remote servers.The book contains enough material to learn the basics of the Visual Basic language and create simple macros that help automate routine, repetitive work with documents, spreadsheets, charts, presentations, etc., as well as to develop quite complex database processing applications using dialog boxes that provide user with the most modern interface tools.Most of the examples in the book are devoted to current issues of commercial activity, so the book will be very useful to managers at various levels, for whom both Microsoft Office and the built-in VBA programming language are apparently intended.
The appendices at the end of the book can serve as a handy reference for working with both VBA and regular VB.

Book: Professional programming in VBA in Excel 2003
John Walkenbach
Publisher: Williams
Format: PDF
Size: 11 MB
Quality: Excellent
Language: Russian
The year of publishing: 2005
ISBN: 5-8459-0771-3
The book comes with a disc

Many books have been written on Excel. But what makes this book special is that it puts spreadsheet application development into a broader context. VBA is just one component of the development environment custom applications, although quite significant. This book will help you understand the intricacies of application development using VBA. It describes the many features of the VBA language, its capabilities and the environment for use. First, you will be offered an overview of the program's capabilities, then you will move on to defining the concepts of VBA programming, and then get acquainted with the language itself. If you are a novice VBA programmer, then in this publication you will find everything necessary information, which will be required for further work. If you already have enviable experience working with VBA, then this book will enrich and increase your knowledge, adding new techniques and real-life examples.

PDF version edited and kindly provided by the participant.

Book: :
John Walkenbach
Publisher: Wiley
Format: PDF
Pages: 1308
Size: 11.9 MB
Quality: Excellent
Language: English
The year of publishing: 2010

This book focuses on Visual Basic for Applications (VBA), the programming language built into Excel (and other applications that make up Microsoft Office). More specifically, it will show you how to write programs that automate various tasks in Excel. This book covers everything from recording simple macros through creating sophisticated user-oriented applications and utilities. This book does not cover Microsoft Visual Studio Tools for Office (VSTO). VSTO is a relatively new technology that uses Visual Basic .NET and Microsoft Visual C#. VSTO can also be used to control Excel and other Microsoft Office applications.
This is not a book for beginning Excel users. If you have no experience with Excel, a better choice might be Excel 2010 Bible, which provides comprehensive coverage of all the features of Excel. That book is meant for users of all levels.

Book: : Professional programming in VBA in Excel 2010
John Walkenbach
Publisher: Dialectics
Format: PDF
Pages:920
Size: 22.1 MB
Quality: Excellent
Language: Russian
The year of publishing: 2010 The subject of this book is language Visual programming Basic for Applications (VBA), which is built into Excel as well as other applications included with Microsoft Office. Here we describe in detail the creation of programs that automate the execution of various tasks in Excel, and also covers a wide range of other topics - from writing simple macros to creating complex applications and utilities designed for user interaction. This book does not describe the Microsoft software package Visual Studio Tools for Office (VSTO). He is the embodiment of relatively new technology using Visual Basic .NET and Microsoft Visual C#. VSTO technology can also be used to control the behavior of Excel and other Microsoft Office applications.

This book is not intended for beginner Excel users. If you have no experience with this application, then first read the book Excel 2010. The User's Bible, which explains in detail all the capabilities of Excel (it is addressed to users of all levels).

Book:VBA tutorial
Garnaev A.
Publisher: bhv
Pages: 512
Format: html with images in rar
ISBN: 5-8206-0067-3
Size: 2.22 MB

Excellent
Language: English
The year of publishing: 2009

Microsoft Excel is much more than just a spreadsheet. With the introduction of the Visual Basic Editor in Excel 97, followed by the significantly improved stability of Excel 2000, Excel became a respected development platform in its own right. Excel applications are now found alongside those based on C++, Java, and the .NET development platform, as part of the core suite of mission-critical corporate applications.
Unfortunately, Excel is still too often thought of as a hobbyist platform, that people only develop Excel applications in their spare time to automate minor tasks. A brief look at many Excel VBA books seems to confirm this opinion. These books focus on the basics of automating Excel tasks using VBA. This book is the first of its kind in providing a detailed explanation of how to use Excel as the platform for developing professional quality applications.
While most other major development platforms seem to have a de facto standard text that explains the commonly agreed best practices for architecting, designing, and developing applications using that platform, until now Excel has not. This book attempts to fill that gap. The authors are professional Excel developers who create Excel-based applications for clients ranging from individuals to the largest multinational corporations. This book explains the approaches we use when designing, developing, distributing, and supporting the applications we write for our clients.

This book was provided by a user

Excel Worksheet Functions Reference is a reference to Excel worksheet functions in Windows Help .chm format. The file contains a complete list of Excel sheet functions in Russian and English and is provided with examples of the use and application of the functions. The directory may be needed by both beginners and more experienced users.

File format:.chm

Excel Worksheet Functions Reference(532.2 KiB, 15,462 downloads)

VBA Programming in MSOffice is a very good tutorial for beginners to program in VBA. The book is written by a certified Microsoft Office teacher in an easy-to-understand language and has clear code listings. The textbook will help you master programming not only in Microsoft Excel, but also in Microsoft Word and Microsoft Project.

File format:.pdf

VBA Programming in MSOffice(4.1 MiB, 26,659 downloads)
You do not have permission to download this file. Perhaps you are not registered on the site. Register and try downloading again.

Walkenbach J. Professional programming in VBA Excel 2003 - perhaps the most popular textbook for beginners. Many people started with him. True, in the printed version of this textbook, code listings are used, which do not always need to be taken on faith - very often there are typos and Russian symbols are found instead of English ones. Therefore, I personally would advise not to copy the codes directly from the book, but to rewrite them manually in the VBA editor. This way you will remember it better and save yourself from searching for errors.

File format:.pdf

Office 2007. Tutorial - If you decide to take a closer look at the interface and basic capabilities of Microsoft Office applications, then I advise you to read this book. It describes everything you need to successfully work in applications such as: Excel, Word, Outlook, Power Point, Access. After reading, you will be able to create beautiful presentations and informative graphics, learn how to create tasks in Outlook and databases in Access.

File format:.pdf

Russian Visual Basic for Application(VBA) Help - This is the official Visual Basic for Application(VBA) Help that was included in Microsoft package Office 97. At that time the certificate was still Russified. It's no secret that now in all versions of Office, VBA help is available only at English language, regardless of localization. But not everyone can read bourgeois (even passably, as practice shows).
I want to warn you right away - because... help is intended for quite old version, not all methods and properties are described in it. But the bulk of them are still described and, I hope, this certificate will help you with initial study VBA.

File format:.help file

RUS_VBA.zip(1.1 MiB, 9,164 downloads)
You do not have permission to download this file. Perhaps you are not registered on the site. Register and try downloading again.

Shell_RegExp objects, etc. - a whole collection of examples of working with such objects as: Shell, RegExp, FileSystemObject, WshShell, Dictionary, WshNetwork, WScript, WScript constants and functions. All examples are structured into sections and are provided as a reference for the properties and methods of each object. It’s very convenient if you don’t use these objects very often in your work and have forgotten something.

Every user has experienced the fact that sometimes in Excel there are no suitable tools that meet their needs. With all the abundance of Excel capabilities, sometimes it is impossible to automate solutions to some problems, because there is no limit to perfection. The ideal solution is to allow the user to create their own specific tools. Macros were created for this purpose.

Writing macros in Excel

Code Excel macro written in Visual Basic for Application (VBA) and executed by the application tool to which it is attached. Most of these tools are not available at the Excel window level. How to write a macro.

Now let's demonstrate with an example information on how to write, edit and execute macro code.

To write a macro:

Note. If there is no “DEVELOPER” tab in the main menu, then it must be activated in the settings: “FILE” - “Options” - “Customize Ribbon”. In the right list “Main tabs:”, check the “Developer” option and click on the OK button.



Macro capabilities in Excel

Macros allow you to automate the processes of working with documents and more... They can almost simultaneously execute thousands of tools in one operation (even with one click of the mouse). This expands the possibilities for working with the program.

Using macros, the user can create his own tool, which he lacks in the Excel arsenal. For example, when you need to automatically select every second row with one click. Or you need to simultaneously create a certain number of copies of a certain worksheet. The list of user needs for automation of work processes can be continued indefinitely.

If it were not possible to create macros in all programs included in the MS Office package. In the process of routine work, users would have to perform many operations manually (select every other line by clicking on every second heading with the mouse or copy and paste one sheet at a time). Manual work, at best, leads to the loss of a huge amount of time, and at worst, it leads to errors or even the loss of valuable data.

The ability to create macros and automate workflows saves your work from wasted time and errors. Next, we’ll look at how to create a macro that will accurately and automatically perform routine work in one click. We will also consider in what places in the Excel workbook you can create and store macros. How to launch and execute them, as well as how to optimize them as much as possible to suit your needs.

Excel provides a large and abundant arsenal of tools for storing and processing huge amounts of data information. Although for the most universal analytical program Excel, storing data in itself is less interesting than the ability to process, structure and analyze it with presentation in reports. For these purposes, the most powerful analytical data processing tool, such as “Pivot Tables,” is used. But it can be further improved with the help of macros. And then the possibilities of pivot tables know no bounds.

Few people know that the first version of the popular Microsoft Excel product appeared in 1985. Since then, it has gone through several modifications and is in demand among millions of users around the world. At the same time, many people work only with a small fraction of the capabilities of this spreadsheet processor and do not even realize how the ability to program in Excel could make their life easier.

What is VBA

Programming in Excel is carried out using the Visual Basic for Application programming language, which was originally built into the most famous spreadsheet processor from Microsoft.

Experts attribute its relative ease of learning to its advantages. As practice shows, even users who do not have professional programming skills can master the basics of VBA. Features of VBA include script execution in an office application environment.

The disadvantage of the program is compatibility problems different versions. They are caused by the fact that the VBA program code accesses functionality, which are present in new version product, but missing in the old one. Also, the disadvantages include the excessively high openness of the code for changes by an outsider. However, Microsoft Office as well as IBM Lotus Symphony allow the user to encrypt the initial code and set a password to view it.

Objects, collections, properties and methods

These are the concepts that need to be understood by those who are going to work in the VBA environment. First of all, you need to understand what an object is. In Excel, these functions are sheet, workbook, cell, and range. These objects have a special hierarchy, i.e. obey each other.

The main one is Application, which corresponds to the Excel program. Then come Workbooks, Worksheets, and Range. For example, to access cell A1 on a specific worksheet, you must specify a path that takes into account the hierarchy.

As for the concept of “collection”, this is a group of objects of the same class, which in the entry has the form ChartObjects. Her individual elements are also objects.

The next concept is properties. They are a necessary characteristic of any object. For example, for Range it is Value or Formula.

Methods are commands that indicate what needs to be done. When writing code in VBA, they must be separated from the object by a period. For example, as will be shown later, very often when programming in Excel, the Cells(1,1).Select command is used. It means that you need to select a cell with coordinates

Selection.ClearContents is often used together with it. Executing this means clearing the contents of the selected cell.

How to start

Then you need to go to the VB application, for which you just need to use the key combination “Alt” and “F11”. Further:

• in the menu bar located at the top of the window, click on the icon next to the Excel icon;
• select the Mudule command;
• save by clicking on the icon with the image;
• they write, let's say, a draft of the code.

It looks like this:

Sub program()

"Our code

Please note that the line "Our code" will be highlighted in a different color (green). The reason is the apostrophe placed at the beginning of the line, which indicates that a comment follows.

Now you can write any code and create for yourself new tool in VBA Excel (see program examples below). Of course, it will be much easier for those who are familiar with the basics of Visual Basic. However, even those who do not have them can get used to it quickly enough if they wish.

Macros in Excel

This name hides programs written in Visual Basic for Application. Thus, programming in Excel is creating macros with the required code. Thanks to this feature, the Microsoft spreadsheet processor develops itself, adapting to the requirements specific user. Having figured out how to create modules for writing macros, you can begin to consider specific examples VBA Excel programs. It's best to start with the most basic codes.

Example 1

Task: write a program that will copy the value of the contents of one cell and then write it to another.

For this:

• open the “View” tab;
• go to the “Macros” icon;
• click on “Record macro”;
• fill out the form that opens.

For simplicity, leave “Macro1” in the “Macro name” field, and insert, for example, hh into the “Keyboard shortcut” field (this means that you can launch the program with the quick command “Ctrl+h”). Press Enter.

Now that the macro recording has already started, the contents of a cell are copied to another. Return to the original icon. Click on “Record Macro”. This action means the end of the program.

• go back to the “Macros” line;
• select “Macro 1” from the list;
• click “Run” (the same action is launched by starting the key combination “Ctrl+hh”).

As a result, the action that was performed while recording the macro occurs.

It makes sense to see what the code looks like. To do this, go back to the “Macros” line and click “Change” or “Login”. As a result, they find themselves in the VBA environment. Actually, the macro code itself is located between the lines Sub Macro1() and End Sub.

If copying was done, for example, from cell A1 to cell C1, then one of the lines of code will look like Range(“C1”).Select. Translated, it looks like “Range(“C1”).Select”, in other words, it goes to VBA Excel, to cell C1.

The active part of the code is completed by the ActiveSheet.Paste command. It means writing the contents of the selected cell (in this case A1) to the selected cell C1.

Example 2

VBA loops help you create various macros in Excel.

VBA loops help you create various macros. Suppose we have a function y=x + x 2 + 3x 3 - cos(x). You need to create a macro to get its graph. This can only be done using VBA loops.

The initial and final values ​​of the function argument are x1=0 and x2=10. In addition, you must enter a constant - the value for the argument change step and the initial value for the counter.

All VBA Excel macro examples are created using the same procedure as presented above. In this particular case, the code looks like:

Sub program()

step = 0.1

Do While x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Value = x1 (the value x1 is written to the cell with coordinates (i,1))

Cells(i, 2).Value = y (the y value is written to the cell with coordinates (i,2))

i = i + 1 (counter is in effect);

x1 = x1 + shag (the argument changes by the step value);

End Sub.

As a result of running this macro in Excel, we get two columns, the first of which contains the values ​​for x, and the second for y.

Then a graph is built from them in a standard way for Excel.

Example 3

To implement loops in VBA Excel 2010, as in other versions, along with the already given Do While construction, For is used.

Consider a program that will create a column. In each of its cells the squares of the corresponding row number will be written. Using the For construct will allow you to write it very briefly, without using a counter.

First you need to create a macro as described above. Next we write down the code itself. We assume that we are interested in the values ​​for 10 cells. The code looks like this.

For i = 1 to 10 Next

The command is translated into “human” language as “Repeat from 1 to 10 in steps of one.”

If the task is to obtain a column with squares, for example, of all odd numbers from the range from 1 to 11, then we write:

For i = 1 to 10 step 1 Next.

Here step is a step. In this case it is equal to two. By default, the absence of this word in the cycle means that the step is single.

The results obtained must be saved in cells numbered (i,1). Then, each time the loop is started, with i increasing by a step size, the row number will automatically increase. This way the code will be optimized.

Overall the code will look like:

Sub program()

For i = 1 To 10 Step 1 (you can simply write For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (i.e. the value of square i is written to cell (i,1)

Next (in a sense plays the role of a counter and means another start of the loop)

End Sub.

If everything is done correctly, including recording and running the macro (see instructions above), then when you call it, each time you will get a column of the specified size (in this case, consisting of 10 cells).

Example 4

In everyday life, there is often a need to make one or another decision depending on some condition. You can’t do without them in VBA Excel. Examples of programs where the further course of execution of the algorithm is chosen and not predetermined initially, most often use the If ...Then construction (for complex cases) If ...Then ...END If.

Let's consider a specific case. Suppose you need to create a macro for Excel so that the following is written in the cell with coordinates (1,1):

1 if the argument is positive;

0 if the argument is null;

-1 if the argument is negative.

Creating such a macro for Excel begins in the standard way, through the use of “hot” Alt keys and F11. Next the following code is written:

Sub program()

x= Cells(1, 1).Value (this command assigns x the value of the contents of the cell at coordinates (1, 1))

If x>0 Then Cells(1, 1).Value = 1

If x=0 Then Cells(1, 1).Value = 0

If x<0 Then Cells(1, 1).Value = -1

End Sub.

All that remains is to run the macro and get the required value for the argument in Excel.

VBA Functions

As you may have already noticed, programming in Microsoft's most famous spreadsheet processor is not that difficult. Especially if you learn how to use VBA functions. In total, this programming language, created specifically for writing applications in Excel and Word, has about 160 functions. They can be divided into several large groups. This:

• Mathematical functions. Applying them to the argument, they obtain the value of the cosine, natural logarithm, integer part, etc.
• Financial functions. Thanks to their availability and using Excel programming, you can obtain effective tools for accounting and financial calculations.
• Array processing functions. These include Array, IsArray; LBound; UBound.
• Excel VBA functions for string. This is a fairly large group. This includes, for example, the functions Space to create a string with the number of spaces equal to the integer argument, or Asc to convert characters to ANSI code. All of them are widely used and allow you to work with rows in Excel, creating applications that greatly facilitate working with these tables.
• Data type conversion functions. For example, CVar returns the value of the Expression argument by converting it to the Variant data type.
• Functions for working with dates. They significantly expand the standard ones. Thus, the WeekdayName function returns the name (full or partial) of the day of the week by its number. Even more useful is Timer. It gives the number of seconds that have passed from midnight to a specific point in the day.
• Functions for converting a numeric argument to different number systems. For example, Oct outputs numbers in octal.
• Formatting functions. The most important of them is Format. It returns a Variant value with an expression formatted according to the instructions specified in the format declaration.
• etc.

Studying the properties of these functions and their application will significantly expand the scope of Excel.

Example 5

Let's try to move on to solving more complex problems. For example:

A paper document reporting the actual level of costs of the enterprise is given. Required:

• develop its template part using the Excel spreadsheet;
• create a VBA program that will request initial data to fill it out, carry out the necessary calculations and fill in the corresponding template cells with them.

Let's consider one of the solution options.

Create a template

All actions are carried out on a standard sheet in Excel. Free cells are reserved for entering data on the name of the consumer company, the amount of costs, their level, and turnover. Since the number of companies (societies) for which the report is being compiled is not fixed, the cells for entering values ​​based on the results and the name of the specialist are not reserved in advance. The worksheet is given a new name. For example, "reports".

Variables

To write a program to automatically fill out a template, you need to select notations. These will be used for variables:

• NN - number of the current table row;
• TP and TF - planned and actual trade turnover;
• SF and SP - actual and planned amount of costs;
• IP and IF - planned and actual level of costs.

Let us denote the accumulation of the total for this column using the same letters, but with the “prefix” Itog. For example, ItogTP - refers to the table column entitled “planned turnover”.

Solving a problem using VBA programming

Using the introduced notations, we obtain formulas for deviations. If you need to calculate in %, we have (F - P) / P * 100, and in total - (F - P).

The results of these calculations can best be entered directly into the appropriate cells in an Excel spreadsheet.

For actual and forecast totals, they are obtained using the formulas ItogP=ItogP + P and ItogF=ItogF+ F.

For deviations use = (ItogF - ItogP) / ItogP * 100 if the calculation is carried out as a percentage, and in the case of a total value - (ItogF - ItogP).

The results are again immediately written to the appropriate cells, so there is no need to assign them to variables.

Before running the created program, you need to save the workbook, for example, under the name "Report1.xls".

The “Create reporting table” button only needs to be pressed once after entering the header information. There are other rules you should know. In particular, the “Add Row” button must be clicked each time after entering values ​​for each type of activity into the table. After entering all the data, you need to click the “Finish” button and then switch to the Excel window.

Now you know how to solve Excel problems using macros. The ability to use vba excel (see examples of programs above) may also be needed to work in the most popular text editor at the moment, Word. In particular, you can create menu buttons by writing, as shown at the very beginning of the article, or by writing code, thanks to which many operations on text can be carried out by pressing the duty keys or through the “View” tab and the “Macros” icon.