Programming macros in VBA in Microsoft Excel. VBA courses in Microsoft Excel Excel programming courses

  • Cells Collection

Purpose of the course:...

More about the program

Module 1 "Introduction to the object model. Macro recorder"

  • Using the Macro Recorder - Automatic Macro Recording
  • VBE editor - what to look for
  • Study and improve the generated VBA code
  • Object hierarchy, collections, properties, methods and events

Module 2 "VBA Syntax"

  • Working with variables and constants,
  • Work with conditional operator If...Then...Else
  • Working with loop operators For Each...Next, While...End While, Do...Loop
  • Key features for user interaction
  • Creating and calling custom functions

Module 3 "Working with data ranges. Error handling"

  • Properties and Methods of the Range Object
  • Cells Collection
  • Creating a Run-time Error Handling Procedure
  • Techniques for debugging and step-by-step code testing

Practical training in a mini-group of up to 5 participants, no lectures or notes, a lot of practice “from the field”.

Purpose of the course:

  • fluently read and correct errors in VBA code,
  • understand the VBA editor, debug and edit code,
  • use variables, loops, dialogs for entering values ​​and outputting information, user 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, subloops
  • Development of decision-making models in VBA
  • Custom Functions and Forms
  • VBA Testing and Error Handling
  • Creating your VBA project from A to Z

Duration of the course: 16 academic hours of VBA code practice in Excel (4 days, 4 academic hours - from 19 to 22 weekdays)

For each training participant:

  • a certificate is issued training center on participation in practical training and confirmation of professional skills,
  • a methodological manual 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 needs,
  • free support next month skype/phone/mail.

Turnkey speaker Correction of errors in accounting and tax accounting and reporting Accounting for income tax calculations Accounting statements 2011: “missing” items and completely new accounting objects Relationships with tax authorities - the “new old” confrontation “Cautious” tax optimization For the manager about accounting and IFRS reporting “as a first approximation” 1C: Accounting 8. User modes Income tax in the configuration “1C: Accounting 8” 1C: Salary and personnel management. Regulated personnel records 1C: Salaries and personnel management. Accounts with personnel 1C: Trade management (ed. 11). Sales management 1C:Enterprise 8.2. Support for 1C:Enterprise information bases 8.2. First steps of a 1C:Enterprise developer 8.2. Query language 1C:Enterprise 8.2. Solving operational problems 1C:Enterprise 8.2. Solving accounting problems 1C:Enterprise 8.2. Solving calculation problems 1C:Enterprise 8.2. 1C:Enterprise integration and data exchange tools 8.2. New features Basics of programming in the 1C:Enterprise 8 system 1C:Enterprise 8.2. Introduction to configuration. Basic Adobe Photoshop CS5 Objects for Beginners Adobe Photoshop CS5. Retouching Adobe Photoshop CS5. Color correction Adobe Photoshop CS5. Practical application in design Adobe Illustrator CS5 Adobe InDesign CS5 Prepress layouts in accordance with CorelDRAW X5 production requirements. Vector graphics Decorating interior items using decoupage technique Basic computer training. Windows 7/Word 2010/Excel 2010 and Internet Time management in Microsoft Outlook 2010. Time and task management for a modern person Microsoft Excel 2010/2013: expanded capabilities Forecasting and optimization in Excel. Linear programming methods in Excel Data visualization in Microsoft Excel Macro programming in VBA in Microsoft Excel Formulas array and data analysis in Excel 2010/2013 Budgeting, planning, efficiency calculation and factor analysis Professional macro programming Business presentation with Microsoft Power Point and Microsoft Excel Professional tools Microsoft Excel 2010/2013. Microsoft Word 2010/2007 Extended Microsoft capabilities Word for Lawyers and Legal Secretaries MS Word Advanced Features Business Presentation with Microsoft Power Point and Microsoft Excel Planning and Tracking Deadlines in Project 2010 Microsoft Access 2013/2016 Collaboration users in SharePoint 2010 Typescript on a SketchUp computer. Basics of SketchUP Mastery. Advanced course Creating library elements using ArchiCAD 16 Autodesk AutoCAD 2013 AutoCad MEP 2013: design of engineering networks and communications Master class AutoCAD MEP 2013. Electrical Autodesk Inventor 2013. Mechanical engineering 3D - design 3D modeling in AutoCAD 2013 Architectural design in the ArchiCAD 16 environment. Basic level Architectural design in the ArchiCAD environment 16. Advanced capabilities of Autodesk Revit 2014. Basic concepts, creation of Autodesk Revit 2014 families. Modeling of engineering systems of buildings HTML5. Base JavaScript course. Basic course JavaScript and jQuery. Advanced AJAX Course Made Easy! XML and JavaScript PHP. Base PHP course. PHP Web Server Programming. Advanced PHP features. We work with XML and PHP XML databases. Application and Practice XML schemas. Data presentation design XSLT transformations XML and PHP XML and JavaScript Google Analytics 2015: Professional Web Analytics. Improving website efficiency Seminar: New tools and techniques in Google Analytics Google AdWords. Basic course Moving a site: questions, problems and solutions WebMatrix 3.0 or creating sites easily and simply CMS Joomla!: Administration and maintenance of a site CMS Joomla!: Integration of your own design layout and site security Search Engine Optimization sites on WordPress SEO 2019 - Search engine optimization of sites Seminar: New methods of website promotion SEO-2015 Search engine optimization of sites on WordPress Start an online business from scratch. Launching an online store Seminar: Start an online business from scratch. Launching an online store Time management: processes and tools for managing personal time Conducting effective presentations SCRUM: effective project management in IT Project management processes (PMI PMBoK 2012) IT project management Preparation for PMI PMP (Project Management Professional) certification MS Project as a tool effective project management MindJet MindManager in project management Document management and office work Fundamentals of secretarial work Etiquette Income tax in the “1C: Accounting 8” configuration Master class AutoCAD MEP 2013. Electrical Master class “Photography for beginners” Practical seminar on using the “Pen” tool in Adobe Photoshop, Illustrator, InDesign Seminar: New methods of website promotion SEO-2015 Seminar: New tools and techniques in Google Analytics Seminar: Start an Internet business from scratch. Launching an online store Search engine optimization of websites on WordPress Adobe Photoshop CS5 for beginners Adobe Photoshop CS5. Retouching Adobe Photoshop CS5. Color correction Creative photography Digital photography Master class “Secrets of Photography” Turnkey speaker Time management in Microsoft Outlook 2010. Time and task management for a modern person Consultations on Web courses

Excel's capabilities are not limited to the 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 in a programming language Visual Basic for Applications(VBA). The basic concepts of the VBA programming language are discussed on our website in the VBA Tutorial. However, before you start writing VBA code, we recommend that you take a look at the lessons that cover Excel macro security 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 in a book Excel macro, make sure your security settings are configured correctly.

Visual Basic Editor

Recording Macros

Excel's macro recording tools are a great way to efficiently complete simple, repetitive tasks. It can also be used as an aid when writing more complex macros.

Excel VBA Tutorial

For those who are just starting to learn a programming language Excel VBA, we offer a short introductory course on Visual Basic for Applications.

In this section we will look at what VBA is, since it is in it that macros are written.

What is VBA?

VBA- language (stands for Visual Basic for Application) was developed by the company Microsoft. This language is not independent, but is intended to automate processes in the package MS Office. VBA widely used in Excel, as well as in Access, Word and other programs in the package.

VBA is a simple programming language that anyone can learn. Once you've learned it, you'll be able to provide commands Excel, what to do with columns, rows, values ​​in cells, move/add/sort sheets, display pre-programmed messages, write your own formulas and functions, etc. The essence of language is to operate objects(which refers to it as object-oriented programming).

To work with VBA code, we need an editor that is already installed by default. You can open it by pressing the key combination " ALT+F11 ".

Objects

Let's look at what an object is. An object- is an element, a structural particle Excel, namely: workbook, sheet, range, cell. These objects have a hierarchy, i.e. obey each other. Schematic structure of the hierarchy Excel can be depicted as follows:

The main object is Application , which corresponds to the program itself Excel. Followed by Workbooks (book), Worksheets (sheet), Range (range, or individual cell). For example, to access a cell "A1" on the sheet we will need to write the following path taking into account the hierarchy:

Application.Workbooks("Archive").Worksheets("Arkush1").Range("A1").

Thus, we have learned to access the smallest object in Excel- cells.

Collections

In turn, objects have "collections". Collection is a group of objects of the same class. Individual elements of a collection are also objects. Yes, objects Worksheets are elements of an object's collection Worksheet , which also contains other collections and objects:

  • ChartObjects (object collection element ChartObject)
  • Range
  • PageSetup
  • PivotTables (object collection element PivotTable).

Properties

Each object has properties. For example, object Range has the property Value or Formula .

Worksheets(“Sheet1”).Range(“A1”).Value or else Worksheets(“Sheet1”).Range(“A1”).Formula

In this example, the property displays the value that was entered into the cell or formula entered.

Also, through the property Formula You can not only get the formula, but also write it down:

MsgBox Range(“A1”).Formula - we will receive a message with the formula in the cell " A1";

Range(“B12”).Formula = “=2+6*100” - enter the formula =2+6*100 to cell B12.

Methods

Now let's look at how we can manipulate the contents of a range or cell. For this purpose in VBA there are so-called methods(commands "what to do"). When writing code methods separated from object point, for example:

Range("A1").Select or Cells(1, 1).Select

This method tells you to select ( Select) cell "A1".
Next, let's delete the value in this cell. To do this, we will write the following code:

Selection.ClearContents

Here the program “takes” what we have selected ( Selection ) and deletes its contents ( ClearContents ).

Current specialists of St. Petersburg

Kravtsov Vladimir

Vladimir about himself:
“Do you want to switch to “you” from Excel? Welcome! The key goal of my classes is to teach material with the opportunity to apply 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. Competently structured lessons to assimilate information. Efficiency in presenting information, accessible presentation, interest in students mastering skills and in quality results of work in Microsoft Excel"
Anna, group PE219-1375

Orlov Svyatoslav

Svyatoslav about himself:
"A significant part of my professional activity was associated with corporate training. I often found myself in a situation where, in a short period of 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 practicing it with examples. Thank you Svyatoslav! I will recommend you!”
Evgenia, group PE189-1079

Pimkina Elena

Elena about herself:
“Do you like Excel? Do you love Excel as much as I do? (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 about it. Come to our courses, you will love Excel too! Or, at least, stop being afraid of it) I have been conducting Excel training for decades, I advise on real practical problems and I am happy to share my knowledge and experience with my students.”

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. I am 200% satisfied with the result. Everything was explained using specific examples and immediately worked out independently. The training was a breeze"
Anna, group PE189-1205



You might be interested