- 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.
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) ones quite noticeably - they had a monochrome display, there was no hard disk or floppy disk drives at all, 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 there was this very Pascal and much more, and I, apart from BASIC, did not know and did not see anything), it was very difficult to master the syntax of the algorithmic language. In addition, I didn’t understand (and still don’t understand) why I need to spend an hour writing an algorithm for a simple program, if you can just open an editor and write a few lines of code (for me, who knows English well, the syntax of the BASIC language was much clearer than this cryptic algorithmic pseudocode). 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 leaving 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 install the operating system and basic programs on the first day of my acquaintance with the computer 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, etc.)
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 drew attention to the "Visual Basic Editor" item in the Microsoft Word menu, and got to see what kind of beast it was. 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 some kind of error began to pop up when they were opened. 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 and Word files have passed through me, 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.
AT 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). I hadn’t figured out what to publish on the site yet, so I started using it as a notebook - publishing useful universal macros and functions there so that I didn’t have to look for them in different files every time, 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 posted were of interest to many, and this gave me an incentive to further develop the resource - I began to lay out 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 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 development of the program, returning the money to several buyers. Other programs, such as BarcodeScanner, Labels, SearchText, although they have paid back 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 the 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 the development of 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)
<продолжение следует>
Current specialists of St. Petersburg
Kravtsov Vladimir
Vladimir about himself:
“Want to switch to “you” with Excel? Welcome! The key goal of my classes is to teach material with the possibility of applying the acquired knowledge in practice, to show that working with Excel can be interesting, and the learning process can be accessible and understandable to everyone. It's great to grow and improve professionally in a light positive atmosphere"
Listener feedback:
“I want to thank Vladimir Kravtsov. Competent construction of lessons for assimilation of information. Efficiency in the submission of information, accessible submission, interest in mastering the skills of students and in high-quality results of work in Microsoft Excel»
Anna, group PE219-1375
Orlov Svyatoslav
Svyatoslav about himself:
“A significant part of my professional activity has been related to corporate training. Often I found myself in a situation where in a short time I needed to prepare new employees for work, significantly increasing their level of competence in a fundamentally new field of activity for them. Over time, I mastered the ability to explain complex things in simple language, especially since I already had over 50 groups.
Listener feedback:
“I liked the structure of the lesson - it's not just a lecture, it's an analysis of the material and then it's working out with examples. Thanks Svyatoslav! I will recommend you!”
Evgenia, group PE189-1079
Pimkina Elena
Elena about herself:
“Do you love Excel? Do you love Excel as much as I love it? (with). This entertaining and inexhaustible crossword puzzle of millions of cells, this puzzle, logic game, the longer you play it, the more new things you learn in it. Come to our courses, you will love Excel too! Or, at least, stop being afraid of it) I have been training in Excel for more than a dozen years, I advise on real practical problems and share my knowledge and experience with my listeners with pleasure”
Listener feedback:
“Many thanks to Elena Mikhailovna for her professionalism and attention to students! As a result of the training, in addition to what interested me, I learned a lot of new and useful things. Satisfied with the result by 200%. Everything was understood on specific examples and immediately worked out independently. The training went off without a hitch"
Anna, group PE189-1205
Courses on macros in VBA can be taken in person. The tutorial is suitable for those who consistently use Microsoft Excel at work or in everyday life. Professional use of the program is required by many office workers, managers, accountants. If you want to understand VBA code, it's worth taking a course that will teach you all the nuances, including programming macros on your own.
What skills does the course provide?
The main objectives of the course:
- Learning to work in the Visual Basic editor;
- The student of the courses studies Function procedures;
- Mastering the skills of programming Excel objects.
After mastering the course, the graduate will be able to use various user functions as efficiently as possible, try on automatic macro recordings, program object management, access to them, work with variables, data entry dialogs.
Course objectives also include:
- Ability to work in VBA code - find and fix errors;
- Use VBA editor, edit code;
- Create a VBA project;
- Use cycles, dialogs, apply various functions.
Learning macros in VBA will allow you to fully use all the features of the program.
What do you need to enroll in the courses?
You can enroll in a training course with a diploma of higher or secondary specialized education. If the education document is in a foreign language, its official translation into Russian is required. You also need a passport to verify your identity.
Upon completion of the course, the graduate receives a certificate of professional development.
Course Features
- The course student can choose a convenient time for attending classes - options are available on weekends and on weekdays (Monday, Wednesday, Friday).
- The course is taught by experienced professionals who have special knowledge in their field.
- The duration of the course is 24 hours.
- Training takes place stationary, which allows all students to ask questions, practice under the guidance of a teacher;
- The training program covers both theory and practice - more time is devoted to the practical application of skills.
Macros in VBA in Microsoft Excel are much easier to learn, thanks to the thoughtfulness of the program and the high professionalism of teachers.
What is included in the course program?
- Using the macro decoder and recording macros in the program;
- Using the Visual Basic editor: creating code, using modules, working with program code, studying data types, variables and stable values;
- Features of programming in Excel, working with cells, sheets;
- Studying cycles: types of cycles, working with cycles with conditions, by objects;
- Formation of user functions, problem solving through automation;
- Automation of macros;
- Setting up and testing the program.
As a final work, students perform testing.
The main advantage is the opportunity to get one trial lesson for free. What does it give a potential student?
- You can personally get acquainted with the teacher, examine the audience, equipment;
- Learn more about the Learning Center, its documentation;
- Make up your own mind about the course.
If you have any questions, you can contact the Center's specialists at the hotline number. You can order the course online and pay for it in a convenient way.
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 you will receive the same certificates (by e-mail) , as full-time students (the certificate does not indicate in which format you were trained).
