by Thomson Course Technology PTR. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or. Microsoft Excel VBA Programming for the Absolute Beginner As a scientist, I never really gave business-oriented Excel a chance. The earliest versions of Excel. TE AM FL Y 00 XLVBA chronanreareeko.ga 2/25/03 AM Page i. Microsoft ® Excel VBA Programming DUANE BIRNBAUM 00 XLVBA chronanreareeko.ga 2/25/03 AM Page ii.
|Language:||English, Spanish, Dutch|
|Genre:||Business & Career|
|Distribution:||Free* [*Register to download]|
Microsoft Access VBA Programming for the Absolute Beginner - Free Microsoft Excel VBA Programming for Dummies pdf - DASCO. once this microsoft excel vba programming for the absolute beginner, but stop up in harmful downloads. Rather than enjoying a good PDF with a mug of coffee. Programming For The Absolute Beginner [PDF] [EPUB] Learn Excel VBA Programming & Macros with free step by step tutorials, download PDF.
I had a plan to make a course on Excel VBA. Mark has been using Excel since version 1. And here are some reviews in his course. Started writing a VBA code and did forget the syntax of a constructor? Just go to Google and convert your problems in some phrases and hit Enter. Millions of results will appear to you immediately. Searching in a book for some problems?
It will take time. Go to the Index page, then count a, b, c, d, find out your relevant topic, go to that page, and get an idea of your findings.
A long-term process really and there is no guarantee that you will find your problem topic in your book. Then why every expert says that you need a book on your topic? My advice is same to you. You need one or two books in Excel VBA. It might be in PDF or it can be a hardcover book preferable. Typically, an abbreviated word telling us the type of control the cmd at the beginning of the name above denotes a Command Button and its function in the program will work well.
The Name property of an ActiveX control should be changed if you will refer to it in your program. A meaningful name will help you remember it, as well as make the code more readable.
The Editor In the upper-left corner of the code window is a dropdown list box containing the names of all objects contained within the selected worksheet. The name of the Command Button control is displayed because the cursor in the editor is within an event procedure of this Command Button control. Event procedures are self-contained blocks of code that require some type of stimulus in order to run.
The stimulus often comes directly from the user for example, a mouse click , but may also result from another piece of code. Event procedures are pre-defined for ActiveX controls and other Excel objects, such as workbooks and worksheets.
All event procedures for the selected object are listed in the upperright corner of the code window in a dropdown list box.
I will discuss event procedures in more depth in Chapter 3. For now, just take a look at the Click event. The Click event is a very common event procedure that is built into most ActiveX controls.
The procedure is defined as listed in Figure 1. You cannot change the name of a predefined event procedure without changing the Name property of the object. If you do change the name of the event procedure, the code within the procedure will not run when you want it to.
The keyword Sub is required and is used as the defining opening of any procedure—event-type or programmerdefined. The second line End Sub, is always used to close a procedure. Now type the following lines of code within the Click event procedure of the Command Button control named cmdColorChange. Range "A1". Select Cells. This is the equivalent of a user first selecting all the cells in a worksheet and then changing the fill color from the formatting toolbar in the Excel application.
The color of the cells is chosen randomly and will change with each click of the Command Button control because the above code will run once with each click event. So the entire procedure now looks like the following. Now test the program by clicking on the Command Button control.
The color of all cells in the worksheet will change color with each click. You can save the workbook as you would an Excel workbook. The Command Button control and event procedure code will be saved with the workbook. The help system, accessed by pressing the F1 key, provides fast access to solutions for any programming problems you have with your project. Often, all you need to see is a simple example of how to use a particular function or other keyword; the help system does contain documentation on every keyword, programming construct, and object you might use in your project.
This will certainly be a welcome change that eliminates switching back and forth between multiple help windows to research a question. To access VBA development help specifically, simply change the search filter by selecting Developer Reference, also shown in Figure 1.
The advantage you have when gathering a requirement list is that the source document can be used to build a protocol for testing the program. I will follow this procedure for the chapter projects including the Colorful Stats project that follows.
Requirements of the Colorful Stats Program The purpose of the Colorful Stats program as it relates to this book is to give you a demonstration of ActiveX controls, event procedures, and using VBA to interact with an Excel worksheet.
The practical purpose of the Colorful Stats program is to allow a user to immediately calculate basic statistics by selecting a set of data on the worksheet. Corresponding labels shall be written to cells C2 through C7. Designing the Colorful Stats Program When designing a program, I consider the user interface, program inputs and outputs, the location of the code for example, event procedures of ActiveX controls , and the use and configuration of other programming components that I have not yet discussed.
Since this is the first chapter project, I have kept it short and simple to make it easier to follow the design procedure. I start by making the very simple user interface for the Colorful Stats program. The interface will use a single Command Button control placed on a worksheet to activate the program.
All program inputs and outputs are from, and to, the current active worksheet. The data used in the calculation of the statistical values must come from the cells that are selected by the user. I will write the program to output cell formulas to the desired worksheet cells so that Excel calculates the statistical values. I must also output labels to the cells adjacent to the statistical values for clarity. I will also format all output as described in the requirements.
Coding the Colorful Stats Program As stated in the previous section, all the code is to be placed in the Click event procedure of the Command Button control. You can also select the appropriate object cmdCalculate from the object dropdown list in the code window for the worksheet on which the ActiveX control was placed refer to Figure 1.
As you can see, the following code was placed in the Click event procedure of the cmdCalculate Command Button control. The very first and last lines define the type of procedure as a Click event, as described earlier in this chapter. Immediately following the opening line of code is a comment. HIN T Comments or remarks are notes left in the code by the programmer to help describe the function of the program. Comments make it easier to find problems with the code, or add different features to the code at a later time.
Enter comments into the code by beginning the line with an apostrophe or Rem. You must enter another apostrophe for each new comment line; the VBA text editor will color each comment line green default color; change by selecting Tools, Options, Editor Format, and Comment Text from the list of Code colors. Comments are not part of the program, and are ignored when the program runs; thus, comments do not decrease the execution speed of a program.
Select End With ''Format the labels and stats. Select End Sub I will discuss code structures, Excel objects, and object syntax in subsequent chapters.
Next, the statistical labels are written to the corresponding cells in the adjacent column C2 through C7. The last part of the program formats the font, border, and color of cells C2 through D7 before selecting cell A1.
This code will run once each time the Command Button control is clicked. You also learned how to add ActiveX controls to a worksheet, change their properties, and add code to their event procedures.
After a brief look at using the help system and installing the VBA help files, you developed a small project that used a Command Button control on a worksheet to initiate a program that calculated statistical values from user-selected data.
Your program then formatted the output with color, a new font, and a border. In Chapter 2 you will learn about some basic programming concepts and tools, variables, and data types. In particular, note the SelectionChange event procedure of any worksheet.
While in the Excel application, add an ActiveX Label control to a worksheet. Change the Name property of the Label control to lblCellAddress. Add the following line of code to the SelectionChange event procedure of the worksheet to which you added the Label control. Address 4. Return to the worksheet, exit Design Mode, and click on any cell in the worksheet containing the Label control.
What happens? Place the cursor within the word Caption and press F1. Repeat with the Address keyword. The next three chapters are devoted to these basic programming structures that, although they may not be that exciting, are essential for developing VBA projects. This program demonstrates the use of several variable types, including numbers, text, and dates. You know that you can enter numbers and text in any spreadsheet cell in Excel.
Also, you may or may not know that the format of a spreadsheet cell can be changed to one of several possibilities.
For example, a number can be formatted such that the value is displayed with or without digits to the right of the decimal point.
Numbers can also be formatted as currency or as a percentage along with a few other options. Text can be displayed as entered or can be automatically converted to a date or time.
The content or value of a spreadsheet cell can be changed or deleted at any time. You have already seen in the Chapter 1 project, and will continue to see throughout this book, the use of the Value property to access or change the contents of a spreadsheet cell. In essence, spreadsheet cells are temporary storage containers for numbers and text that can be displayed and used in a number of different formats.
This also describes a variable in any programming language. You can use variables in programs for temporary storage of data.
For example, any data input by a user possibly from a Text Box control , can be stored in a variable and used later in the program. In the Colorful Stats project from Chapter 1, the following line of code acts a lot like a variable.
Range "C6". I could have just as easily copied the text into a program variable first and then copied the contents of the variable to the cell C6. The value of spreadsheet cell C6 is then assigned the value stored in the variable myString. Declaring Variables To declare a variable is to tell the computer to reserve space in memory for later use. To declare a variable, use a Dim short for Dimension statement. The name must begin with an alphabetic character and cannot exceed characters or contain any spaces.
As a convention, the variable name should be descriptive of the value it will hold. My preference is to begin a variable name with a lowercase letter and then capitalize the first letter of any subsequent words appearing in the name. TIP Use Option Explicit in the general declarations section of a module window to force explicit variable declarations see Figures 2. Otherwise, variables can be dimensioned the act of declaring variables and allocating storage space implicitly without a Dim statement as they are required in code.
This is not good programming practice because of performance reasons and it makes your code harder to interpret, and subsequently more difficult to debug.
Following the variable name, the data type is specified for the variable. In the previous code example, the variable is declared as an integer data type. This tells VBA what kind of data can be stored in this variable and how much memory must be reserved for the variable.
I will discuss data types in detail later in this chapter. Object and Standard Modules Modules refer to a related set of declarations and procedures. Each module will have a separate window in the VBA IDE and, depending on the origination of the module, it will have different behavior with regard to variable declarations. I will refer to the module window shown in Figure 2.
This module will automatically contain all event procedures associated with the worksheet Sheet1, and any ActiveX controls added to this worksheet. Each worksheet will have a separate code window as will the workbook. Standard modules are contained within a separate folder in the Project Explorer and may be renamed in the Properties window see Figure 2.
Standard modules contain variable declarations and programmer-defined procedures. Compartmentalization is the process of breaking a large programming problem into several smaller problems and then solving each of these smaller problems separately.
Compartmentalization is vital in the development of software applications. Variable Scope Scope, in the context of variables, refers to the time when a variable is visible or available to the program.
When a variable is out of scope, it is unavailable—essentially invisible to the program. A variable declared within the code block of a procedure such as the Click event procedure of the Command Button control , is a procedural level variable. Procedural level variables are only available while program execution occurs within the procedure that the variable was declared.
In Figure 2. When program execution is triggered by the Activate event, the variable myVar4 is dimensioned in memory. Program execution proceeds through the event procedure until reaching the End Sub line of code, after which the variable is released from memory and is no longer available.
Each time the procedure executes, the variable is created and destroyed. Thus, myVar4 will not retain its value between calls to the procedure. If necessary, the Static keyword can be used to tell VBA to remember the value of the variable between calls to a procedure.
If you replace the Static keyword with Dim, myVar4 will never exceed a value of 1. TIP Integer variables are initialized to a value of 0 at declaration. Declaring a variable outside of a procedure with a Dim statement makes it a module level variable. The scope of a module level variable depends on the keyword used in the declaration.
The area outside of any defined procedure is known as the general declarations section of a module object or standard. This area can only be used for declarations. These three variables are declared with the Dim, Private, and Public keywords. The Private and Public keywords are only allowed for variable declaration in the general declarations section of a module. Each of the three variables, myVar, myVar2, and myVar3 are visible to any procedure within this module.
In addition, the variable myVar3 is visible to any procedure in any module of this project. Variables declared in the general declarations section of a module object or standard with the Public keyword are commonly referred to as global. TIP When declaring a variable with the Public keyword in the general declarations section of an object module, it must be referenced in other modules of the project by first identifying the name of the object module.
For example, to reference and assign a value to the variable myVar3 seen in Figure 2.