Lecture №2. Working in the Visual Basic Editor
Syrdariya » university « Natural sciences and mathematic » faculty « Software » department
LECTURE NOTES (THESES)
Module code, name PL-Programming Languages
Subject code, name:VB 4303-Visual Basic
Form: daytime classes
Specialty, code: 5B060200 - "Informatics" and 5B070400 - "Computer Engineering and Software"
Course: 3
Semester: 5
Zhetysay, 20__
Lecture №1. What Is VBA?
1. Getting a conceptual overview of VBA
2. Finding out what you can do with VBA
If you’re anxious to jump into VBA programming, hold your horses. This chapter is completely devoid of any hands-on training material. It does, however, contain some essential background information that assists you in becoming an Excel programmer. In other words, this chapter paves the way for everything else that follows and gives you a feel for how Excel programming fits into the overall scheme of the universe. It’s not as boring as you might think, so please try to resist the urge to jump to Chapter 2. Okay, So What Is VBA? VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft — you know, the company that tries to get you to buy a new version of Windows every few years. Excel, along with the other members of Microsoft Office, includes the VBA language (at no extra charge). In a nutshell, VBA is the tool that people like you and me use to develop programs that control Excel. Imagine an intelligent robot that knows all about Excel. This robot can read instructions, and it can also operate Excel very fast and accurately. When you want the robot to do something in Excel, you write up a set of robot instructions by using special codes. Then you tell the robot to follow your instructions, while you sit back and drink a glass of lemonade. That’s kind of what VBA is all about — a code language for robots. Note, however, that Excel does not come with a robot or lemonade.
What Can You Do with VBA?
You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples:
|
|
|
✓ Analyzing scientific data
✓ Budgeting and forecasting
✓ Creating invoices and other forms
✓ Developing charts from data
✓ Keeping lists of things such as customers’ names, students’ grades, or holiday gift ideas (a nice fruitcake would be lovely)
✓ Yadda, yadda, yadda
The list could go on and on, but I think you get the idea. My point is simply that Excel is used for a wide variety of tasks, and everyone reading this book has different needs and expectations regarding Excel. One thing virtually every reader has in common is the need to automate some aspect of Excel. That, dear reader, is what VBA is all about. For example, you might create a VBA program to import some numbers and then format and print your month-end sales report. After developing and testing the program, you can execute the macro with a single command, causing Excel to automatically perform many time-consuming procedures. Rather than struggle through a tedious sequence of commands, you can click a button and then hop on over to Facebook and kill some time while your macro does the
work.
Inserting a bunch of text
If you often need to enter your company name, address, and phone number in your worksheets, you can create a macro to do the typing for you. You can extend this concept as far as you like. For example, you might develop a macro that automatically types a list of all salespeople who work for your company.
Automating a task you perform frequently
Assume you’re a sales manager and you need to prepare a month-end sales report to keep your boss happy. If the task is straightforward, you can develop a VBA program to do it for you. Your boss will be impressed by the consistently high quality of your reports, and you’ll be promoted to a new job for which you are highly unqualified.
Automating repetitive operations
If you need to perform the same action on, say, 12 different Excel workbooks, you can record a macro while you perform the task on the first workbook and then let the macro repeat your action on the other workbooks. The nice thing about this is that Excel never complains about being bored. Excel’s macro recorder is similar to recording live action on a video recorder. But it doesn’t require a camera, and the battery never needs to be recharged.
|
|
|
Creating a custom command
Do you often issue the same sequence of Excel menu commands? If so, save yourself a few seconds by developing a macro that combines these commands into a single custom command, which you can execute with a single keystroke or button click. You probably won’t save that much time, but you’ll probably be more accurate. And the guy in the next cubicle will be really impressed.
✓
 |
VBA module consists of Sub procedures. A Sub procedure has noth- ing to do with underwater vessels or tasty sandwiches. Rather, it’s a chunk of computer code that performs some action on or with objects (discussed in a moment). The following example shows a simple Sub procedure called AddEmUp. This amazing program displays the result of 1 plus 1:
A Sub procedure that doesn’t perform properly is said to be substan- dard.
✓
 |
A VBA module can also have Function procedures. A Function proce- dure returns a single value. You can call it from another VBA procedure or even use it as a function in a worksheet formula. An example of a Function procedure (named AddTwo) follows. This Function accepts two numbers (called arguments) and returns the sum of those values:
A Function procedure that doesn’t work correctly is said to be dysfunc- tional.
✓ VBA manipulates objects. Excel provides dozens and dozens of objects that you can manipulate. Examples of objects include a workbook, a worksheet, a cell range, a chart, and a shape. You have many more objects at your disposal, and you can manipulate them by using VBA code.
✓ Objects are arranged in a hierarchy. Objects can act as containers for other objects. At the top of the object hierarchy is Excel. Excel itself is an object called Application. The Application object contains other objects such as Workbook objects and Add-In objects. The Workbook
object can contain other objects, such as Worksheet objects and Chart objects. A Worksheet object can contain objects such as Range objects and PivotTable objects. The term object model refers to the arrangement of these objects. (Object model mavens can find out more in Chapter 4.)
|
|
|
✓ Objects of the same type form a collection. For example, the Worksheets collection consists of all the worksheets in a particular workbook. The Charts collection consists of all Chart objects in a work- book. Collections are themselves objects.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Lecture №2. Working in the Visual Basic Editor
1. Understanding the Visual Basic Editor
2. Discovering the Visual Basic Editor parts
Аs a more experienced-than-average Excel user, you probably now good deal about workbooks, formulas, charts, and other Excel goodies. Now it’s time to expand your horizons and explore an entirely new aspect of Excel: the Visual Basic Editor. In this chapter, you find out how to work with the Visual Basic Editor, and you get down to the nitty-gritty of writing some VBA code.
What Is the Visual Basic Editor?
I’m going to save some wear and tear on my fingers and refer to the Visual Basic Editor as the VBE. The VBE is a separate application where you write and edit your VBA macros. It works seamlessly with Excel. By seamlessly, I mean that Excel takes care of opening the VBE when you need it.
In Excel 2013, every workbook displays in a separate window. However, there is only one VBE window, and it works with all open Excel windows.
You can’t run the VBE separately; Excel must be running in order for the VBE to run.
|
|
|
Activating the VBE
The quickest way to activate the VBE is to press Alt+F11 when Excel is active. To return to Excel, press Alt+F11 again. Or, you can just click the Close button in the VBE’s title bar. When the VBE window closes, Excel is activated. You can also activate the VBE by using the Developer➪Code➪Visual Basic command. If you don’t have a Developer tab at the top of your Excel window, refer to the beginning of Chapter 2 where I explain how to get that handy Developer tab to show up.
 |
Menu bar
The VBE menu bar works just like every other menu bar you’ve encountered. It contains commands that you use to do things with the various components in the VBE. You also find that many of the menu commands have shortcut keys associated with them.
The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.
Toolbar
The Standard toolbar, which is directly under the menu bar by default (refer to Figure 3-1), is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you’re so inclined, use the View➪Toolbars command to work with VBE toolbars. Most people (including me) just leave them as they are.
Project window
The Project window displays a tree diagram that shows every workbook cur- rently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them within the outline. I discuss this window in more detail in the upcoming “Working with the Project Window” section.
If the Project window is not visible, press Ctrl+R or use the View➪Project Explorer command. To hide the Project window, click the Close button in its title bar. Or right-click anywhere in the Project window and select Hide from the shortcut menu.
Code window
A Code window is where you put your VBA code. Every object in a project has an associated Code window. To view an object’s Code window, double- click the object in the Project window. For example, to view the Code window for the Sheet1 object in Book1, double-click Sheet1 in the VBAProject for Book1. Unless you’ve added some VBA code, the Code window will be empty.
You find out more about Code windows later in this chapter’s “Working with a Code Window” section.
Immediate window
The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or use the View➪Immediate Window command. To close the Immediate window, click the Close button in its title bar (or right-click any- where in the Immediate window and select Hide from the shortcut menu).
Working with the Project Window
When you’re working in the VBE, each Excel workbook and add-in that’s open is a project. You can think of a project as a collection of objects arranged as an outline. You can expand a project by clicking the plus sign (+) at the left
of the project’s name in the Project window. Contract a project by clicking the minus sign (–) to the left of a project’s name. Or you can double-click the items to expand and contract them.
If a project is password-protected, you’re prompted for the password when you double-click a project name. If you don’t know the password, you can’t expand the project — which means that you can’t view or modify any part of the project.
Figure 3-2 shows a Project window with three projects listed: an add-in named pup7.xlam, a workbook named investments.xlsm, and the Personal Macro Workbook (which is always named PERSONAL.XLSB). Of the three, only the investments.xlsm project is expanded to show all of its objects.
Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each sheet in the work- book (each sheet is considered an object) and another object called ThisWorkbook (which represents the Workbook object). If the project has
any VBA modules, the project listing also shows a Modules node. And, as you see in Part IV, a project may also contain a node called Forms, which con- tains UserForm objects (which hold custom dialog boxes).
The concept of objects may be a bit fuzzy for you. However, I guarantee that things become much clearer in subsequent chapters. Don’t be too concerned if you don’t understand what’s going on at this point.
Removing a VBA module
Sometimes you need to remove a VBA module from a project. For example, it may contain code that you no longer need, or it’s empty because you inserted the module and then changed your mind. To remove a VBA module from a project:
Дата добавления: 2019-03-09; просмотров: 221; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!