VBA. From a simple macro to a VBA project: Courses. Excel macro training course Excel macro courses
- Cell Collection
Course objective:...
More about the program
Module 1 "Introduction to the Object Model. Macro Recorder"
- Using the macro recorder - automatic macro recording
- Editor VBE - what to look for
- Learn and improve generated VBA code
- Object Hierarchy, Collections, Properties, Methods, and Events
Module 2 "VBA Syntax"
- Working with variables and constants,
- Working with the If...Then...Else conditional statement
- Working with For Each...Next, While...End While, Do...Loop
- Basic functions for user interaction
- Creating and calling user-defined functions
Module 3 "Working with data ranges. Handling errors"
- Properties and Methods of the Range Object
- Cell Collection
- Creating a Run-time Error Procedure
- Debugging and step-by-step code testing techniques
Practical training in a mini-group up to 5 participants, no lectures and recordings, a lot of practice "from the fields".
Course objective:
- fluently read and fix errors in VBA code,
- understand the VBA editor, debug code and edit,
- apply variables, loops, dialogs for entering values and displaying information, user-defined functions,
- step by step and consciously create your own VBA project
Your competencies after the course:
- Reading and understanding VBA code, editing
- Using the macro recorder and debugger
- Using arrays, loops, sub-loops
- Developing Decision Models in VBA
- Custom Functions and Forms
- VBA Testing and Error Handling
- Creating Your VBA Project from A to Z
Course duration: 16 academic hours of practicing VBA code in Excel (4 days for 4 academic hours - from 19 to 22 weekdays)
For each participant in the training:
- a certificate of the training center is issued on participation in practical training and confirmation of professional skills,
- a methodological guide with all course materials and a step-by-step description of all the main topics and tasks of the training,
- ready-made templates that you can easily adapt to your professional tasks,
- free support next month skype/phone/mail.
Excel's capabilities are not limited to a set of built-in functions. By writing macros, you can create your own functions to perform non-standard tasks in Excel.
For example, a self-written macro can be attached to an icon and displayed on the Menu Ribbon. Or you can create a User Defined Function (UDF) and use it just like the rest of Excel's built-in functions.
A macro is computer code written for Excel using the Visual Basic for Applications (VBA) programming language. The basic concepts of the VBA programming language are covered on our website in the VBA Tutorial. However, before you start writing VBA code, we recommend that you read the lessons that discuss the security of Excel macros and the Visual Basic editor.
Setting permission to use macros in Excel
Excel has built-in protection against viruses that can enter your computer through macros. If you want to run a macro in an Excel workbook, make sure the security settings are set correctly.
Visual Basic Editor
Macro recording
The Excel macro recording tool is a great way to efficiently perform simple repetitive tasks. It can also be used as an aid in writing more complex macros.
Excel VBA Tutorial
For those who are just starting to learn the Excel VBA programming language, we offer a small introductory course on Visual Basic for Applications.
full-time
Full-time education
Full-time education is education in one of the 13 cities of Russia where there are classes of the Softline Training Center (Moscow, St. Petersburg, Vladivostok, Yekaterinburg, Kazan, Krasnoyarsk, Nizhny Novgorod, Novosibirsk, Omsk, Rostov-on-Don, Samara, Ufa, Khabarovsk).
You arrive at the time specified by the administrator at the desired address and undergo training on our PCs. All labs and/or practice assignments will be pre-configured by our technicians and ready to go. Full-time tuition includes a notebook, pen, textbooks and manuals, tea/coffee/water/cookies and lunch. For students from other cities who wish to study in our classes full-time, we help with the selection and booking of a hotel (you arrange a business trip at your own expense).
remotely
Distance learning
If there is no class in your city, you can study online. The cost of a distance course is 10% less than a face-to-face one.
The remote format means connecting to a face-to-face group during the course (a connection link will be sent to you in advance). On your part, you need a computer or laptop with a stable internet connection and a headset with a microphone and headphones. You will see the teacher's presentation, hear him, get remote access to laboratory work (if the course involves the use of them), be able to ask questions in a group chat or voice, communicate with other students, and upon completion of the training, receive the same certificates (by e-mail) as full-time students (the certificate does not indicate in which format you were trained).
Not all users of MS Excel, even in Moscow, are aware that with the help of this program you can not only create tables, make calculations, formulas, graphs, diagrams, etc., but also program. The built-in Visual Basic for Applications (VBA) language allows you to create macros in your own way, develop them according to the parameters that the user needs. What is it for? After all, Excel has a lot of functions that satisfy the basic needs of users. But there is also that category of specialists for whom the standard set of buttons and parameters is not enough. For successful full-fledged work, sometimes you need to significantly expand the functionality of Excel, which can be done using VBA programming.
Courses from "PC Lesson" - the best option for learning to program VBA in Excel
Our training center offers VBA programming courses in Excel. Based on the wishes of the customer, we will draw up a training program, which indicates the depth of the course being studied. This can be superficial training with the development of the basics of Visual Basic programming, as a result of which you will learn how to compose the simplest macros using the macro recorder or standard commands. A more in-depth study will, of course, require more time and effort to master the course. However, the result justifies itself: professional knowledge of the VBA programming language in Excel will allow you not only to write macros for personal use, but also to do it professionally, getting a job with the appropriate requirements. Also, knowledge and skills in working with this programming language will not interfere with any Moscow accountant or economist who is forced to regularly perform the same operations, wasting their time on monotonous work. Knowing how to create unique macros with specific data selection parameters, you can safely automate any repetitive work. This will not only simplify the work of a specialist, but also increase his professional value in the eyes of employers.
Our VBA courses - Your ability to automate work in Excel
Despite the external complexity of programming, we will teach you how to work with macros quickly and without problems. After all, in fact, the Visual Basic interface is quite logical and consistent. When compiling the code, the basic principles of constructing a macro are used, VBA objects are used. We will reveal a lot of programming possibilities in Excel, introduce objects, their capabilities and limitations, properties and control methods.
Learning to program in Excel is necessary not only to automate some of the repetitive actions in the program. Thanks to the ability to create specific macros, you can develop special features that are completely new to Excel. That is, knowing how to manage the Excel programming language, you can modify it so that it ideally performs all the tasks assigned, taking you a minimum of time.
The teachers of our computer courses are ready to train Moscow clients with any initial level of training. Zero knowledge of even the Excel program itself is not an obstacle to learning VBA programming. If you need to improve your professional knowledge and delve into the depths of the Visual Basic language, we will help you acquire professional programming skills. VBA in Excel programming courses are held individually, with a visit to the client's home or office in any district of Moscow. This helps to better assimilate the material, acquire knowledge and skills only those that you need.
In addition to tips on how to learn how to write VBA macros for Excel, I will tell you my story - how I learned to program in VBA.
It all started in the last century 1993 year when I was in school. In 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 the "normal" (IBM-compatible) quite noticeably - they had a monochrome display, completely absent HDD and floppy disk drives, and the only thing that started on it (automatically, after loading) was the Basic language interpreter.
Actually, I showed interest in programming earlier - at the age of 9, when I saw in the magazine "Modeler-constructor" for 1988 (which I read regularly, from cover to cover) some machine codes for the computer "Specialist" . I was then very interested in the opportunity to drive some letters and numbers into the computer to make it do what I want. Alas, the financial situation of my parents at that time left much to be desired, and the purchase of any semblance of a computer was out of the question. So I had fun copying these codes from a journal 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 a few had Spectrums at home, and someone was lucky to get an expensive IBM PC 286), I mastered Basic exclusively at school, sometimes lingering after school. The school curriculum provided for only 2 years of studying the subject "Informatics" (grades 10 and 11), but since childhood I have been interested in all kinds of electronics, so I began to attend classes from grade 8, reading the only instruction book on the Corvette there (here is this book, with which my path to programming began, it is also in PDF), and trying to compose simple programs. Soon the teacher began to let me in front of the main computer (only it had the ability to save programs to a 5.25" floppy disk - and even then, I had to work hard for this) - and my programs became much more complicated. Once I even managed to implement the Poker game with a graphical interface ( it turned out about 2000 lines of code) - this despite the fact that the Basic interpreter allowed only characters and graphic primitives to be displayed on the screen.
As a result, by the end of school ( 1996
year) I skillfully used cycles, and had some experience in compiling algorithms.
As for algorithms, this term still makes me allergic. When I was at school, at lectures on computer science (I studied quite well, and we, the winners of all kinds of Olympiads, were sent for additional training in higher mathematics and computer science to a part-time school at the Ural State University), we were forced to write algorithms in some kind of pseudocode ( mixtures of Russian with Turbo Pascal). Since I was noticeably behind my peers in programming (they studied on IBM-compatible computers, where this same Pascal and much more was, and I, apart from BASIC, did not know or see anything), it was very difficult to master the syntax of the algorithmic language. In addition, I did not understand (and still do not understand) why you need to spend an hour compiling an algorithm for the simplest program, if you can just open an editor and write a few lines of code (for me, who knows English normally, the syntax of the BASIC language was much clearer than this mysterious algorithmic pseudo-code). There is no talk of flowcharts at all - as I remember the institute, where we were forced to draw flowcharts (on several sheets of A4 format) for the simplest programs of 10 lines, I no longer understand how the desire to learn programming can not disappear after all this .. .
After graduating from school, at the institute, I studied Turbo Pascal a little, on which my programming training stopped for a decade (there was no computer at home and at work), and I forgot almost everything that I studied in computer science classes.
However, in 2004
year, at my work, the authorities finally decided to replace the typewriter (on which I was already very skillfully pounding the keys) with a simple computer (it was a Pentium-1 with 64 megabytes of RAM and Windows 98). Given that I saw Windows for the first time, I had to learn how to work on 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 master the reinstallation of the OS and programs. The person who helped me on the first day of my acquaintance with the computer to install operating system and the main programs, was not able to help me constantly. He left me Windows and Office distributions, a couple of disks in various utilities, after which he explained to me how to drive in 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 happiness (after the typewriter) was the ability to save and edit printed documents ... and I was not at all embarrassed that Word took about 20-30 seconds to start (and CorelDRAW loaded for a couple of minutes). The computer became my main hobby - in a couple of months I mastered the main programs, six months later I was poking around in the registry with might and main, restoring the broken Windows (whoever remembers Win98 will understand me), along the way I mastered the computer architecture (I constantly had to poke memory modules, connect additional hard drives, 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 to do the same thing every day). Almost immediately, I noticed the item "Visual Basic Editor" in the menu Microsoft Word, and climbed to see what kind of animal it is. I poked around, looked at the built-in help, but understood little. This Basic was not at all like 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 could not look at usage examples.
So I would have abandoned this business, but then, one fine day, Word files began to open slowly, and when they were opened some kind of error began to pop up. I began to understand - and found that the error occurs when executing a macro of 20 lines, which mysteriously ended up in all my documents. Looking at the code, I realized that in front of me 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 macro virus - since then, thousands of Excel files and Word, and I have never seen 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 with the help of macros you can do almost anything!
And I began to master macros - I read the built-in VBA help, tried code examples from this help, wrote simple loops (which I remembered from school), drew simple shapes, etc. With might and main I used the recording of macros in Word, optimizing the resulting code. (I didn’t work with Excel then - I didn’t really understand what it could be useful for). This brought little benefit - but it was interesting to me, because. I have been into programming since high school. Gradually, my work began to be simplified, although it took more time to automate than it would take to manually perform all the actions.
One day, in 2006 year, I really needed VBA programming skills. For work, it was necessary to create (draw in CorelDRAW) a lot of the same type of 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 observe different proportions and distances between the elements. Moving the icons around the sheet, adjusting the distance from the lines and neighboring icons “by eye”, quickly got tired. And I also had to align everything, draw quickly and beautifully connect the dotted lines, prepress each time, and so on. etc. As a result, having spent 2 or 3 months on automating this process, I have achieved a reduction in the time of making a drawing by several times. In addition, it was nice to look at the faces of colleagues when miracles happened before their eyes - the lines were drawn and moved on the screen by themselves, the arrows aligned and bent, everything flickered, and in a minute 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 ready for printing)
It was summer outside 2008 of the year. I had nothing to do, and I decided to help people who seek advice on the forums with solving problems in Excel. I got involved in it quickly - it was nice to hear thanks 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 understand that I still know very, very poorly in VBA and Excel - and began to learn, understanding the solutions offered by more experienced colleagues.
It took 2 or 3 years, during which I helped everyone on the forums, to start feeling confident in Excel. At first I wrote very simple macros, then more complex ones. A year later, they began to contact me with orders - then I was ready to work for a penny (because the goal was not to make money, but to help people). The first more or less serious order, for which I took a fee, was a program for employees of the selection committee - I had to implement a database in Excel. For 3 weeks of torment and alterations (a student ordered the program, who herself did not know exactly what she needed), the program was written - and I earned about 3,500 rubles on it. Since then, I have not worked with students - there is not enough money, there are no terms of reference, a lot of requests for alteration (because the teacher didn’t like something), and in general it’s unpleasant to realize that the program will be launched only 1 time, and then no one will use it will.
IN 2009 year I made a website website. It was a stretch to call it a site - it consisted of one page (here's what it looked like at the 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 site 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 myself would not be able to master this). What to publish on the site, I had not yet figured out, so I started using it as notebook- publish useful universal macros and functions there so that you do not have to look for them every time in different files, remembering when and to whom I did this.
In less than six months, the site's traffic began to grow. It turned out that the macros I uploaded are of interest to many, and this gave me an incentive to further develop the resource - I began to upload ready-made solutions in the form of Excel files and add-ons, to 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 endless, and turned to me with a request to automate their work. But, anyway, 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 - out of inexperience, I immediately agreed on the amount, and then, out of the kindness of my soul, agreed to various improvements that were not in the original assignment for the program. As a result of this, I worked for weeks on one program, and earned very little - because it was originally supposed that there was very little to be done.
But this time (almost a year) was not wasted - I didn’t earn money, but I gained invaluable experience in developing user interfaces (I had to draw input forms with hundreds of fields), solving non-standard tasks for Excel (website processing, working with files, images , etc.), at the same time learning to understand customers and their tasks from a half-word (uh, you should have seen how some people formulate tasks for complex programs in one sentence of 10 words ...). I have not yet become a telepath, but I began to guess what the customer really wants, and offered people exactly what they need (most do not know what they want when they ask for help). And everything would be fine, if not the lack of orders (by that time, helping people on the forum became annoying - every day the same questions, no one wants to use the search, and even freeloader students overcome).
And I began to think about how to increase the number of orders - I wanted my hobby to finally start to bring tangible income. Again I had to hit the site building - to master the basics of SEO, design, and site building in general. And one day I realized what was still lacking on the site - a big button for placing an order. Without thinking twice, within 5 minutes this button (which you can now see in the upper left corner of the page) was 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.
Has come 2012 year. The number of orders grew to such a volume that it was impossible for me to master them alone. I began to transfer part 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. Along the way, 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 stopped writing macros during working hours. As a result, there was no time left for the development of the site - in the evenings I only managed to rake up part 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 - at my main job I wrote a letter of resignation, deciding to devote myself to programming (development of macros for Excel). Since then, I have more than enough free time, so I plan to expand the site again and work more actively on orders (to avoid situations where customers have to wait several weeks for their macros).
What will come out of all this - I do not know yet, time will tell. I think the demand for macros for Excel will continue for at least another 2-3 years, so I will not be left without bread and butter.
Also in the plans for the near future is the development and publication of shareware add-ins for Excel (the first attempts at writing in this field gave good results).
And then - I plan to develop databases (with a web interface), and gradually switch to working with web services.
<спустя 4 года, решил продолжить статью>
And now in the yard 2016 year. To my delight, Microsoft hasn't stopped supporting VBA in Excel yet, so I'm continuing to work in this area. These 4 years have changed my life a lot - incomes have increased several times, I began to travel a lot, there was less time for work. The number of orders for macros was constantly increasing, so I began to cooperate with several programmers, to whom I passed orders when I could not handle it 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, the other one makes complex macros using non-standard solutions, the third one deals with "muddy" bulk orders, where you need to discuss the task for a couple of weeks, and then do it for another month, the fourth one specializes in formulas, the fifth one sets up parsers, etc., - each has its own area of activity).
Finding a good team was not an easy task - although I didn’t look for anyone myself (half of them turned to me for work, the rest I wrote myself, because I knew everyone on the Excel forums as good specialists), I didn’t manage to work with everyone for a long time. Some rarely appeared on the network 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 me or the customer). One frame even managed to scam me for money (took several orders and disappeared without paying me my commission). But all this is in the past - now with me only proven guys (with whom I have been working for several years), who take orders of any complexity, and always bring the work to the end.
Since I managed to get rid of the “churn” (a lot of small orders, although they bring a good income, but take up 100% of the 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 terms of income, and is in stable demand, since everything that can be useful for users to fill out documents has already been implemented there) , PastePictures (on sale since 2012, but began to gain popularity relatively recently), Unification (3 years ago it was the most complex of my programs), Lookup (the simplest of my add-ons, which is 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). As for the parser, back in 2013 it seemed to me absolutely unrealistic to do something like this (and I refused customers, talking about the impossibility of making a universal solution for sites like Unification, a price processing program), but then the number of orders for site parsers began to increase , and I decided to try, which I never regretted - the income from solutions based on this add-on exceeded all my expectations.
But not all programs were successful. I generally 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 developing the program, returning the money to several buyers. Other programs, such as BarcodeScanner, Labels, SearchText, although they paid off the time spent on development, and continue to generate income, they cannot be compared with the top five programs.
With the development of the universal add-ons described above, the so-called "engine" of the program was formed - a set of macros and interface elements that allows you to quickly make 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 acquired protection 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 is removed in a couple of seconds). Along the way, I had to do the functionality of activating and updating programs on the side of the site - I began to understand PHP and MySQL a little, making scripts that interact 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 software buyers 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 significantly - although my code was quite high-quality and worked without failures 5 years ago, now I take a more responsible approach to code design, faced with the need for many years of support for my add-ons with many thousands lines of code. Now, along with the introduction of a new (multilingual) engine in all add-ons, I am refactoring (updating) the code, making it simpler, clearer, and more 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 in different languages), but so far I'm too lazy to do this. Maybe someday hands will reach this (and for now there are enough buyers from the CIS)
The plans for the future (as of September 2016) are to develop the direction of parsing sites 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 deal with the existing one, due to the lack of a full reference.Well, I don’t like to record videos))
Well, it's time to enter the international market, since all my programs have become multilingual (although the translation is only in English for now, and then only partially)
<продолжение следует>
