Programming vba excel for dummies. Objects, collections, properties and methods

18.04.2019 Iron

Steve Cummings - VBA for Dummies

Steve Cummings has been programming for over 20 years using these different languages programming like assembler, COBOL and C++ as well as VBA and Visual Basic. He is the author and co-author of more than a dozen computer books, including the book Secrets of Office 97, published by Dialectika. In addition, he is the author of hundreds of articles published in renowned computer magazines, including PC World, Macworld, PC Magazine, PC/Computing and PC Week.

dedication

To my grandmother on her 97th birthday

Thanks

Thanks to all the staff at Hungry Minds, and especially to my editors:

James Russell, who has shown a great deal of attention and patience with this book, and Jade Williams and Kelly Oliver.

I am grateful to VCommunications (www.v-com.com) for providing these software products like System Commander and Partition Commander. These are wonderful utilities for configuring the operation of several operating systems on one computer, which I needed when working with beta versions Microsoft Office xp. Thanks also to Lisa Robbins of Waggener Edstrom, Division Microsoft, for help with getting to know the nuances new version VBA, and for providing trial versions Microsoft software products that I need for my work.

Begin...

Yes, this is exactly the book you need to get started with VBA (an acronym for Visual Basic for Applications, which stands for Visual Basic for Applications).

Through this book, you will learn about the basic principles of programming in VBA and gain the necessary skills to create useful programs. And most importantly, you can do it without any extra effort.

The book uses many examples, it is written in an easy and understandable way, because who knows how many boring volumes have already been published in this world! I tried to state everything in the most ordinary language, if possible excluding from use the special jargon that cuts the ear of a normal person. And from that moment on, I start to make (sometimes not the best) jokes so that you have something to direct your annoyance to.

On the other hand, the discussion of the issues under consideration is not too simplistic, otherwise it would be devoid of any value. Jokes aside, this book is a complete reference book covering all the major sections of VBA.

What is this book about

You probably already know that VBA is a programming language built into many programs, from Microsoft Office applications, Microsoft Project, Visio and AutoCAD to numerous specialized applications designed to manage production processes, accounting for financial resources or information support clients.

This book covers all the essential aspects of programming in VBA. Here you will find enough full information on the following topics:

* taking advantage of visual aids VBA programming;

* recording and editing macros;

* run VBA-programs from other applications;

* creating pleasing to the eye dialog boxes and other interface elements;

* work with objects, which will be the key to using the full power of VBAt applications.

This chapter covers VBA versions 6 through 6.3 (VBA6.3 is included with Microsoft Office XP applications). According to Microsoft, all versions of VBA, from version 6 to version 6.3, are completely identical from a programmer's point of view.

Microsoft has fixed some bugs and improved performance, but the way you write code or create dialog boxes hasn't changed. Therefore, when I talk about VBA b, it applies to both VBA 6.0 and VBA 6.3, as well as all versions in between.

Shouldn't have guessed...

But I'll do it anyway. I assume that you are not such a teapot, on the contrary, I think you should be quite comfortable with Windows. Therefore, if you do not know how to use the mouse, select from the menu and click on the buttons that appear on the screen of your monitor, it makes sense for you to first read one of the books published by the Dialectic publishing house, such as Windows for Dummies (there are also separate editions on Windows 95, Windows 98, Windows Me and Windows 2000). Further, to use VBA, you must have at least one application that has built-in VBA development tools. Among these applications, Microsoft Office, the business software market leader, is the most important, followed by a growing group of third-party products. Of all the many possibilities, for example, any of the following applications:

* any Microsoft application Office - Word, Excel, PowerPoint, Access, Outlook or FrontPage;

*Microsoft Project;

* CorelDraw versions 9 or 10 and Corel WordPerfect Office 2000;

* a series of graphic business applications iGrafx from Micrografx;

* Visio versions 4.5, 5 or 2000;

* AutoCAD R14, AutoCAD 2000 or AutoCAD 2000i for Windows;

* TurboCAD Professional;

*M.Y.O.B. Accounting Software;

* Great Plains resource accounting software package (some of the programs in this package understand VBA);

* Omni Trader, risk assessment and trade management tools.

Divide and rule

The theoretical knowledge and skills that are required for programming in VBA form something whole, an organic symbiosis of relationships, similar to life itself ... It is for this reason that I had to break the proposed material into five large parts, each of which contains at least two chapters.

Part I: Introduction to VBA

The book begins with only one dull chapter dedicated to theoretical questions, but the next three quick chapters will wake you up and make you run with VBA. You will learn how to record macros so as not to resort to programming where it is possible and justified, to run your VBA programs from other applications. You even have to create a completely finished program along the way that performs a specific practical task.

The Visual Basic Editor is, so to speak, VBA's trusted screen representative—where you write your programs, construct the windows that those programs open, and test your creations to find out why they refuse to work as they should. I will talk about this in the first chapter of the part. Then I will talk about the use of variables. The rest of the chapters discuss ways to control what happens when a program runs, as well as ways to identify and fix at least some of the bugs that always tend to cling to any program. We'll wrap up Part II with a look at things like creating windows, dialog boxes, and forms, and the programming techniques involved in doing so.

Part III. Practicing VBA programming

This part is the heart of the book. Here, in lovingly crafted chapters, I will reveal to you the secrets of the world of VBA. The first half of the chapters in this part organize the components of a VBA program and explain the structure of those components so that you always know exactly what and where you should type. Next is a workshop on proper naming in VBA and making it presentable. program code. This is followed by chapters on working with variables and objects, which is very important when setting up applications such as Word, Excel, or CorelDraw. In the last chapter, we will talk about processing data represented as arrays and sets.

Part IV. professional work with VBA

In the chapters in this part, you will learn programming techniques that are applicable to almost all Office applications such as customizing the user interface, programming the Office Assistant, and more. Then we will talk in detail about programming for Word and Excel, as well as topics of considerable interest to VBA programmers, regardless of what applications they work with.

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

What is VBA

Programming in Excel is done through the language Visual programming Basic for Application, which is originally built into the most famous spreadsheet processor from Microsoft.

Experts refer to its advantages as a comparative ease of development. As practice shows, even users who do not have the skills can master the basics of VBA professional programming. The features of VBA include the execution of a script in an office application environment.

The disadvantage of the program is the problems associated with compatibility various versions. They are due to the fact that the VBA program code refers to functionality, which are present in the new version of the product but not in the old one. Also, the disadvantages include excessively high openness of the code for changes. by an outsider. However, Microsoft Office as well as IBM Lotus Symphony allow the user to apply encryption initial code and setting a password to view it.

Objects, collections, properties and methods

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

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

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

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

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

Along with it, Selection.ClearContents is often used. Its execution means clearing the contents of the selected cell.

How to start

Then you need to go to the VB application, for which it is enough to use the key combination "Alt" and "F11". Further:

in the menu bar located at the top of the window, click on the icon next to the Excel icon;
select the Mudule command;
save by clicking on the icon with the image ;
write, shall we say, a sketch of the code.

It looks like this:

subprogram()

"Our code

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

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

Macros in Excel

This name hides programs written in Visual language Basic for Application. Thus, programming in Excel is the creation of macros with the right code. Thanks to this feature, the table Microsoft processor self-developing, adapting to the requirements specific user. Having figured out how to create modules for writing macros, you can begin to consider concrete examples VBA programs Excel. It is best to start with the most elementary codes.

Example 1

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

For this:

open the "View" tab;
go to the "Macros" icon;
click on "Record macro";
fill out the open form.

For simplicity, leave “Macro1” in the “Macro Name” field, and insert, for example, hh in the “Keyboard Shortcut” field (this means that you can run the program with the “Ctrl + h” blitz command). Press Enter.

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

again go to the line "Macros";
select "Macro 1" from the list;
click "Run" (the same action is triggered by starting the keyboard shortcut "Ctrl + hh").

As a result, the action that was carried out during the recording of the macro occurs.

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

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

The ActiveSheet.Paste command completes the active part of the code. It means recording the contents of the selected cell (in this case A1) into the highlighted cell C1.

Example 2

VBA loops help you create various macros in Excel.

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

For initial and final value function arguments take x1=0 and x2=10. In addition, you must enter a constant - the value for the step of changing the argument and initial value for counter.

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

subprogram()

step = 0.1

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

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

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

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

i = i + 1 (counter active);

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

endsub.

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

Then, a graph is built on them in a way that is standard for Excel.

Example 3

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

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

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

For i = 1 to 10 Next

The command translates into "human" language as "Repeat from 1 to 10 in increments of one".

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

For i = 1 to 10 step 1 Next.

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

The results obtained must be saved in cells with the number (i,1). Then, each time the loop is started, with an increase in i by the step value, the row number will automatically increase as well. Thus, the code will be optimized.

In general, the code will look like:

subprogram()

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

Cells(i, 1).Value = i ^ 2

Next (in a sense, it acts as a counter and means one more start of the loop)

endsub.

If everything is done correctly, including recording and running the macro (see instructions above), then each time it is called, a column will be obtained given size(in this case consisting of 10 cells).

Example 4

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

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

1 if the argument is positive;

0 if the argument is null;

-1 if the argument is negative.

The creation of such a macro for Excel begins in a standard way, through the use of "hot" Alt keys and F11. The following code is written next:

subprogram()

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

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

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

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

endsub.

It remains to run the macro and get the desired value for the argument in Excel.

VBA Functions

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

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

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

Example 5

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

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

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

Let's consider one of the solutions.

Create a template

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

Variables

To write a program to automatically fill in a template, you must choose the notation. They will be used for variables:

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

Let's designate by the same letters, but with "prefix" Itog accumulation of total on the given column. For example, ItogTP refers to a table column entitled "planned turnover".

Solving a problem using VBA programming

Using the introduced notation, we obtain formulas for deviations. If it is required to carry out the calculation in%, we have (F - P) / P * 100, and in the amount - (F - P).

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

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

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

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

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

The "Create reporting table" button needs to be pressed only 1 time after entering the header information. There are other rules to be aware of. In particular, the "Add Row" button must be clicked every time after entering values for each activity in the table. After entering all the data, you need to click the "Finish" button and then switch to the "Excel" window.

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

Steve Cummings - VBA for Dummies

Steve Cummings has been programming for over 20 years using languages as diverse as Assembly, COBOL and C++, as well as VBA and Visual Basic. He is the author and co-author of more than a dozen computer books, including the book Secrets of Office 97, published by Dialectika. In addition, he is the author of hundreds of articles published in renowned computer magazines, including PC World, Macworld, PC Magazine, PC/Computing and PC Week.

dedication

To my grandmother on her 97th birthday

Thanks

Thanks to all the staff at Hungry Minds, and especially to my editors:

James Russell, who has shown a great deal of attention and patience with this book, and Jade Williams and Kelly Oliver.

I am grateful to VCommunications (www.v-com.com) for providing such software products as System Commander and Partition Commander. These are great utilities for configuring multiple operating systems on a single computer, which I needed when working with beta versions of Microsoft Office XP. Thanks also to Lisa Robbins of Waggener Edstrom, a division of Microsoft, for helping me learn the nuances of the new version of VBA, and for providing trial versions of Microsoft software products that I need for my work.

Begin...

Yes, this is exactly the book you need to get started with VBA (an acronym for Visual Basic for Applications, which stands for Visual Basic for Applications).

What is this book about

You probably already know that VBA is a programming language that is built into a variety of programs, from Microsoft Office, Microsoft Project, Visio, and AutoCAD applications to numerous specialized applications for managing production processes, accounting for financial resources, or customer information support.

This book covers all the essential aspects of programming in VBA. Here you will find quite complete information on the following topics:

* use the advantages of visual programming tools VBA;

* recording and editing macros;

* run VBA-programs from other applications;

* creating nice-looking dialog boxes and other interface elements;

* work with objects, which will be the key to using the full power of VBAt applications.

Shouldn't have guessed...

* any Microsoft Office application - Word, Excel, PowerPoint, Access, Outlook or FrontPage;

*Microsoft Project;

* CorelDraw version 9 or 10 and Corel WordPerfect Office 2000;

* a series of graphic business applications iGrafx from Micrografx;

* Visio versions 4.5, 5 or 2000;

* AutoCAD R14, AutoCAD 2000 or AutoCAD 2000i for Windows;

Steve Cummings - VBA for Dummies

dedication

To my grandmother on her 97th birthday

Thanks

Thanks to all the staff at Hungry Minds, and especially to my editors:

James Russell, who has shown a great deal of attention and patience with this book, and Jade Williams and Kelly Oliver.

Begin...

Yes, this is exactly the book you need to get started with VBA (an acronym for Visual Basic for Applications, which stands for Visual Basic for Applications).

What is this book about

This book covers all the essential aspects of programming in VBA. Here you will find quite complete information on the following topics:

* use the advantages of visual programming tools VBA;

* recording and editing macros;

* run VBA-programs from other applications;

* creating nice-looking dialog boxes and other interface elements;

* work with objects, which will be the key to using the full power of VBAt applications.

Shouldn't have guessed...

* any Microsoft Office application - Word, Excel, PowerPoint, Access, Outlook or FrontPage;

*Microsoft Project;

* CorelDraw version 9 or 10 and Corel WordPerfect Office 2000;

* a series of graphic business applications iGrafx from Micrografx;

* Visio versions 4.5, 5 or 2000;

* AutoCAD R14, AutoCAD 2000 or AutoCAD 2000i for Windows;

* TurboCAD Professional;

*M.Y.O.B. Accounting Software;

* Great Plains resource accounting software package (some of the programs in this package understand VBA);

* Omni Trader, risk assessment and trade management tools.

Divide and rule

Part I: Introduction to VBA

The book starts with just one dull chapter on theoretical issues, but the next three quick chapters will wake you up and make you run with VBA. You will learn how to record macros so as not to resort to programming where it is possible and justified, to run your VBA programs from other applications. You even have to create a completely finished program along the way that performs a specific practical task.

Steve Cummings - VBA for Dummies

dedication

To my grandmother on her 97th birthday

Thanks

Thanks to all the staff at Hungry Minds, and especially to my editors:

James Russell, who has shown a great deal of attention and patience with this book, and Jade Williams and Kelly Oliver.

Begin...

Yes, this is exactly the book you need to get started with VBA (an acronym for Visual Basic for Applications, which stands for Visual Basic for Applications).

What is this book about

This book covers all the essential aspects of programming in VBA. Here you will find quite complete information on the following topics:

* use the advantages of visual programming tools VBA;

* recording and editing macros;

* run VBA-programs from other applications;

* creating nice-looking dialog boxes and other interface elements;

* work with objects, which will be the key to using the full power of VBAt applications.

Shouldn't have guessed...

* any Microsoft Office application - Word, Excel, PowerPoint, Access, Outlook or FrontPage;

*Microsoft Project;

* CorelDraw version 9 or 10 and Corel WordPerfect Office 2000;

* a series of graphic business applications iGrafx from Micrografx;

* Visio versions 4.5, 5 or 2000;

* AutoCAD R14, AutoCAD 2000 or AutoCAD 2000i for Windows;

* TurboCAD Professional;

*M.Y.O.B. Accounting Software;

* Great Plains resource accounting software package (some of the programs in this package understand VBA);

* Omni Trader, risk assessment and trade management tools.

Divide and rule

Part I: Introduction to VBA

Part III. Practicing VBA programming

This part is the heart of the book. Here, in lovingly crafted chapters, I will reveal to you the secrets of the world of VBA. The first half of the chapters in this part organize the components of a VBA program and explain the structure of those components so that you always know exactly what and where you should type. Next comes a tutorial on proper naming in VBA and giving a presentable look to the program code. This is followed by chapters on working with variables and objects, which is very important when setting up applications such as Word, Excel, or CorelDraw. In the last chapter, we will talk about processing data represented as arrays and sets.

Part IV. Professional work with VBA

The chapters in this part will introduce you to programming techniques that apply to almost all Office applications, such as customizing the user interface, programming the Office Assistant, and more. Then we will talk in detail about programming for Word and Excel, as well as topics of considerable interest to VBA programmers, regardless of what applications they work with.

Part V. Magnificent Tens

Your journey into the land of VBA programming ends with two chapters on a wide variety of topics. First, we'll discuss more sophisticated programming techniques. Of course, the discussion will not be too deep, but it cannot be called superficial either - it is enough for you to be able to really use these techniques. The following is a catalog of available VBA resources, covering both informational resources and software products. It's worth a look there to expand your knowledge of VBA programming, and in order to replenish your library of developer tools.

How to use the examples

Programming vba excel for dummies. Objects, collections, properties and methods

What is VBA

Objects, collections, properties and methods

How to start

Macros in Excel

Example 1

Example 2

Example 3

Example 4

VBA Functions

Example 5

Create a template

Variables

Solving a problem using VBA programming

Top Related Articles