Programmer for connecting programs with Excel. Linear Programming in Excel

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

Writing macros in Excel

Code Excel macro written in language Visual Basic for Application (VBA), and it is executed by the application tool to which it is attached. Most of these tools are not available at the window level Excel programs. 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. Users would have to perform many operations in the process of routine work manually (select every other line by clicking on every second heading with the mouse or copy and paste one sheet at a time). Handmade V best cases lead to loss huge amount time, and at worst, leads to errors or even 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 execute error-free and automatically. routine work in one click of the mouse. We will also consider in what places workbook Excel 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 pivot tables know no boundaries.

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 buying 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 :)

Because in those days 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. School program only 2 years of instruction in the subject “Informatics” were provided (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 there the only instruction book on the “Corvette” (this is the book with which my journey into programming began, it’s in PDF), and trying to compose simple programs. Soon the teacher began to let me in main computer(only it had the ability to save programs to 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, at lectures on computer science (I studied quite well, and we, the winners of all sorts of Olympiads, were sent to extra education higher mathematics and computer science to a part-time school at USU), we were forced to write algorithms in some kind of pseudocode (a mixture of Russian and Turbo Pascal). Since I was noticeably behind my peers in programming (they studied at IBM-compatible computers, where there was this same Pascal and much more, and I, except BASIC, knew and saw nothing), it was very difficult to master the syntax of the 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 distributions and Office, 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 Microsoft menu Word, and went to see what kind of animal 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 given up on this matter, but then, one fine day, Word files They began to open slowly, and when they opened, some kind of error began to pop up. 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 I saw a macrovirus - 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 manual execution all actions.

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 required to produce a drawing by 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, pay 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, all the same, there were too few orders to seriously consider programming as a source of income - there were 2-3 small orders per month. 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 do.

But this time (almost a year) was not wasted - I didn’t earn any money, but I gained invaluable experience in development 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 perfectly (uh, 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 it SEO basics, design, and website building in general. And one day I realized what was still missing on the site - big button to place 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 does 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 their own field of activity).

Pick up good team was not an easy task - although I myself wasn’t really looking for anyone (half of them turned to me for work, I wrote to the rest myself, because I knew from everyone on the Excel forums that they were good specialists), I wasn’t able to work with everyone 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 “routine” ( big amount small orders, although they bring in a 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 VLOOKUP formulas, - 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, saying that it was impossible to do universal solution for sites like Unification - price processing programs), but then the number of orders for site 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 program, returning money to several customers. 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. The engine was initially 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, I publish examples of configured 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 full-fledged help. 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)

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

To simplify the collection of information, automate data input and output, and much more, programming in Excel is used.

Video on writing (programming) macros in VBA in Excel

VBA (Visual Basic for Applications) is a type of Visual Basic programming language included in the Microsoft excel package

Programming in excel speeds up the execution of various tasks on the computer.

When writing codes in excel, the programmer uses:

There are two main ways to write code: write inside a sheet; inside the book. Any program in work environment excel starts with keyword“SUB”, followed by any name using letters or numbers, after which we open and close the brackets () and press “ENTER”, a working environment for writing a program is created. In the work environment, type Application, indicate ThisWorkBooks - this book, then Sheet - sheets, then Cells (5,1) - cell number, first write the row then the column. The programming will look like this: Application - ThisWorkBooks - Sheets - Cells (5,1). We assign this address the value 10 through the key assignment operator “=” and launch the program for execution by pressing the “RunSub” button or pressing the “F5” key. The assigned value 10 will appear at the specified location.

When working with this object in Excel, the “development environment” is used. The “Development Environment” is called up by pressing Ctrl+F11, after which you need to select the “View” tab, then “Project Browser”, which will display three elements, two sheets of the workbook and the Excel workbook itself.

DOM - Document object model (object model document) - independent software interface, allowing scripts and programs to find access to content, XHTML and HTML documents and change the design and structure of these documents. The DOM does not limit the structure of the document, but represents it as a tree of nodes, any of which is an element, attribute, graphic or text object.

“Working with variables” are words or letters that contain some meaning, for example: X=5, Y=10, you can perform any mathematical operations with them. Suppose, to find the sum of these values using programming, in the working environment we enter next program: Cells(2,1) = X+Y press “RunSub” or “F5” in line 2, column 1 will display 15 - the sum of the programmable values. In addition to numbers, values can contain words, the only difference is that programmable words must be placed in quotes.

Programming is easy to learn and can be used ordinary users. Developing macros in VBA automates the workflow and increases its productivity.

Function Reference Excel sheet is a reference for Excel worksheet functions in the format Windows Help.chm. 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 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 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 main features Microsoft applications Office, I recommend reading it 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.

To write computer program Firstly, you need to know some programming language. Secondly, you must have installed on your computer special program, which will compile the code you write. Thirdly, you will need to develop and write down on paper the algorithm of your program so that there is always a clear goal in front of your eyes of what we are going to (at least, this is what I always do when writing more complex programs).

Programming language Any will do, but when working in Excel and writing programs for it, I recommend using Visual Basic for Application (VBA), since Excel already has a built-in special editor for creating programs in Excel.

I think you and I can easily master the programming language, I succeeded, and you can do it too. Believe me, programming in Excel is much easier than in some C++ or java. Because it is created for everyone, no matter if you are an engineer or an accountant, VBA was created for people who use Excel. And if you have mastered Excel, then the next logical step is to master VBA.

I will show you with an example how a program is created in VBA.

We are not inventing any algorithm; instead, we will write the following phrase ““Our code”.

1. Launch Excel.

2. Execute the blitz command (hold down two keys) “Alt” and “F11”.

3. Create a template and save it.

4. Then we write down three lines, and the line that begins with an apostrophe is a comment. The word "Sub" indicates the beginning of the program, "program()" is the name of the program, which can be changed, for example, to "Macro()".

The phrase “End Sub” indicates that the program ends here. Instead of the line ““Our code”, you can write our algorithm in the future.

Programming in Excel is not as difficult as it might seem, you need to have a clear algorithm, preferably written down on paper, which we will soon learn how to develop, and some VBA basics (input and output of data into the program, conditions and loops), this will be enough to get started .