Home and learn EXCEL VBA programming step by step

Télécharger Home and learn EXCEL VBA programming step by step

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :

Home and learn EXCEL VBA programming step by step


About Excel
Excel is, by far, the most commonly used spreadsheet product in the world. Because you're reading this book, you are probably familiar with Excel and have used the product for several years. But even a veteran user sometimes needs a refresher course — especially if your experience is mostly with Excel 2003 or earlier versions.
In this chapter, I provide a quick overview of Excel and introduce the concept of objects — an essential component in mastering VBA programming.
Thinking in Terms of Objects
When you're developing applications with Excel (especially when you're dabbling with Visual Basic for Applications — VBA), it's helpful to think in terms of objects, or Excel elements that you can manipulate manually or via a macro. Here are some examples of Excel objects:
-    The Excel application
-    An Excel workbook
-    A worksheet in a workbook
-    A range or a table in a worksheet
-    A ListBox control on a UserForm (a custom dialog box)
-    A chart embedded in a worksheet
-    A chart series in a chart
-    A particular data point in a chart
You may notice that an object hierarchy exists here: The Excel object contains workbook objects, which contain worksheet objects, which contain range objects. This hierarchy makes up Excel's object model. Excel has more than 200 classes of objects that you can control directly or by using VBA. Other Microsoft Office products have their own object models.  
Controlling objects is fundamental to developing applications. Throughout this book, you find out how to automate tasks by controlling Excel's objects, and you do so by using VBA. This concept becomes clearer in subsequent chapters.
The most common Excel object is a workbook. Everything that you do in Excel takes place in a workbook, which is stored in a file that, by default, has an XLSX extension. An Excel workbook can hold any number of sheets (limited only by memory). There are four types of sheets:
-    Worksheets
-    Chart sheets
-    Excel 4.0 XLM macro sheets (obsolete, but still supported)
-    Excel 5.0 dialog sheets (obsolete, but still supported)
You can open or create as many workbooks as you like (each in its own window), but only one workbook is the active workbook at any given time. Similarly, only one sheet in a workbook is the active sheet. To activate a sheet, click its sheet tab at the bottom of the screen. To change a sheet's name, double-click the tab and enter the new text. Right-clicking a tab brings up a shortcut menu with additional options for the sheet, including changing its tab color and hiding the sheet.
You can also hide the window that contains a workbook by using the View’‘Window’‘Hide command. A hidden workbook window remains open, but it isn't visible to the user. Use the
View’‘Window’‘Unhide command to make the window visible again.
A single workbook can display in multiple windows (choose
View’‘Window’‘New Window). Each window can display a different sheet or a different area of the same sheet.
The most common type of sheet is a worksheet, which is what people normally think of when they think of a spreadsheet. Worksheets contain cells, and the cells store data and formulas.
How big is a worksheet?
Stop and think about the actual size of a worksheet. Do the arithmetic (16,384 × 1,048,576), and you'll see that a worksheet has 17,179,869,184 cells. Remember that this is in just one worksheet — a single workbook can hold more than one worksheet.
If you're using a 1920 x 1200 video mode with the default row heights and column widths, you can see 29 columns and 47 rows (or 1,363 cells) at a time — which is about
.0000079 percent of the entire worksheet. In other words, more than 12.6 million screens of information reside in a single worksheet.
If you entered a single digit into each cell at the relatively rapid clip of one cell per second, it would take you over 500 years, nonstop, to fill up a worksheet. To print the results of your efforts would require more than 36 million sheets of paper — a stack about 12,000 feet high. (That's ten Empire State Buildings stacked on top of each other.)
As you might suspect, filling an entire workbook with values is impossible. Even if you use the 64-bit version of Excel (which accommodates much larger workbooks), you'd soon run out of memory, and Excel would probably crash.
Excel 2013 worksheets have 16,384 columns and 1,048,576 rows. You can hide unneeded rows and columns to keep them out of view, but you can't increase or decrease the number of rows or columns.
Versions prior to Excel 2007 used the XLS binary format, and worksheets had only 65,536 rows and 256 columns. If you open such a file, Excel 2013 automatically enters compatibility mode to work with the smaller worksheet grid. To convert such a file to the new format, save it as an XLSX or XLSM file.
Then close the workbook and reopen it.
The real value of using multiple worksheets in a workbook isn't access to more cells. Rather, multiple worksheets enable you to organize your work better. Back in the old days, when a file comprised a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently. Now you can store information on any number of worksheets and still access it instantly by clicking a sheet tab.
A worksheet cell can hold a constant value — a number, a date, a Boolean value (True or False), or text — or the result of a formula. Every worksheet also has an invisible drawing layer, which enables you to insert graphic objects, such as charts, shapes, SmartArt, UserForm controls, pictures, and other embedded objects.
You control the column widths and row heights — you can even hide rows and columns (as well as entire worksheets). You can specify any font size, and you control the colors. You can display text in a cell vertically (or at an angle) and even wrap it around to occupy multiple lines. In addition, you can merge a group of cells to create a single larger cell.
  In the past, Excel was limited to a palette of 56 colors. Beginning with Excel 2007, the number of colors has been virtually unlimited. In addition, Excel 2007 introduced document themes. A single click lets you apply a new theme to a workbook, which can give it an entirely different look.
Chart sheets
A chart sheet holds a single chart. Many users ignore chart sheets, preferring to store charts on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to print a chart on a page by itself and are especially useful for presentations. Figure 1-1 shows a pie chart on a chart sheet.  
Figure 1-1: A pie chart on a chart sheet.
XLM macro sheets
An XLM macro sheet (also known as an MS Excel 4 macro sheet) is essentially a worksheet but with some different defaults. More specifically, an XLM macro sheet displays formulas rather than the results of formulas. In addition, the default column width is larger than in a normal worksheet.
As the name suggests, an XLM macro sheet is designed to hold XLM macros, which were used in Excel 4.0 and earlier. Excel 2013 continues to support XLM macros for compatibility purposes. This book doesn't cover the XLM macro system.
Excel 5 and 95 dialog sheets
In Excel 5 and Excel 95, you created a custom dialog box by inserting a special dialog sheet. Excel 97 and later versions still support these dialog sheets, but a much better alternative is available: UserForms. You work with UserForms in Visual Basic Editor (VBE).
If you open a workbook that contains an Excel 5 or 95 dialog sheet, you can access the dialog sheet by clicking its tab. I don't discuss Excel 5 and Excel 95 dialog sheets in this book.
 What’s new in Excel 2013?
When a new version of Microsoft Office is released, Excel sometimes gets lots of new features and other times gets few new features. In the case of Office 2013, Excel got quite a few new features — but nothing truly earth-shattering.
Here's a quick summary of what's new in Excel 2013, relative to Excel 2010:
-      Cloud storage: Excel is tightly integrated with Microsoft's Skyview web-based storage.
-      Support for other devices: Excel is available for other devices, including touchsensitive Windows RT tablets and Windows phones.
-      New aesthetics: Excel has new “flat” look and displays an optional graphic in the title bar. Color schemes are limited to white, light gray, and dark gray.
-      Single document interface: Excel no longer supports the option to display multiple
workbooks in a single window. Each workbook has its own top-level Excel window and Ribbon.
-  New types of assistance: Excel provides recommended pivot tables and recommended charts.
-  Fill Flash: This feature is a new way to extract (by example) relevant data from text strings. You can also use this feature to combine data in multiple columns.
-  Support for Apps for Office: You can download or purchase apps that can be embedded in a workbook file.
-  Improved Slicer option: The Slicer feature, introduced in Excel 2010 for use with pivot tables, has been expanded and now works with tables.
-  Timeline filtering: Similar to Slicers, a Timeline makes it easy to filter pivot tabledata by dates.
-  Quick Analysis: This feature provides single-click access to various data analysis tools.
-  Enhanced chart formatting: Modifying and fine-tuning charts is significantly easier.
-  Increased use of task panes: Task panes play a larger role in Excel 2013. For example, every aspect of a chart can be modified using task panes.
-  New worksheet functions: Excel 2013 supports dozens of new worksheet functions, most of which are esoteric or special-purpose.
-  Restructured Backstage: The Backstage screen has been reorganized and is easier to use.
-  New add-ins: Office Professional Plus has three new add-ins: PowerPivot, Power View, and Inquire.
Excel's User Interface
A user interface (UI) is the means by which an end user communicates with a computer program. Generally speaking, a UI includes elements such as menus, toolbars, dialog boxes, and keystroke combinations.
The release of Office 2007 signaled the end of traditional menus and toolbars. The UI for Excel consists of the following elements:
-    Ribbon
-    Quick Access Toolbar
-    Right-click shortcut menus
-    Mini toolbar
-    Dialog boxes
-    Keyboard shortcuts
-    Task pane
Excel 2013 can also be run on touch-enabled devices. This book assumes that the reader has a traditional keyboard and mouse, and it does not cover the touch-related commands.
About the Ribbon
In Office 2007, Microsoft introduced a new UI for its product. Menus and toolbars were replaced with a tab and Ribbon UI. Click a tab along the top (that is, a word such as Home, Insert, or Page Layout), and the Ribbon displays the commands for that tab. Office 2007 was the first software in history to use this new interface; a few other companies have incorporated this new UI style in their products.
The appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the window is too narrow to display everything, some commands may seem to be missing, but they are still available. Figure 1-2 shows the Home tab of the Ribbon as it appears for three different window widths.
On the top Ribbon, all controls are fully visible. The middle Ribbon is when Excel's window is narrower. Note that some descriptive text is gone, but the icons remain. The bottom Ribbon appears when the window is very narrow. Some groups display a single icon; click that icon, and all the group commands become available.  
If you'd like to hide the Ribbon to increase your worksheet view, just double-click any tab. The Ribbon goes away, and you'll be able to see about four additional rows of your worksheet. When you need to use the Ribbon again, just click any tab, and the Ribbon comes back. You can also press Ctrl+F1 to toggle the Ribbon display or use the Ribbon Display Option control, located in the window's title bar.
Figure 1-2: The Home tab of the Ribbon, for three window widths.
Contextual tabs
In addition to the standard tabs, Excel includes contextual tabs. Whenever an object (such as a chart, a table, a picture, or SmartArt) is selected, tools for working with that specific object are made available on the Ribbon.
Figure 1-3 shows the contextual tabs that appear when an embedded equation is selected. In this case, Excel displays two contextual tabs: Format (for working with object) and Design (for working with the equation). Notice that the contextual tabs contain a description (Drawing Tools and Equation Tools) in Excel's title bar. When contextual tabs are displayed, you can continue to use all the other tabs.  
Figure 1-3: When you select an object, contextual tabs contain tools for working with that object.
Types of commands on the Ribbon
For the most part, the commands on the Ribbon work just as you'd
expect them to. You'll encounter several different styles of commands on the Ribbon:
-  Simple buttons: Click the button, and it does its thing. An example of a simple button is the Increase Font Size button in the Font group of the Home tab. Some buttons perform the action immediately; others display a dialog box so that you can enter additional information. Button controls may or may not be accompanied by text.
-  Toggle buttons: A toggle button is clickable and also conveys some type of information by the color it displays. An example is the Bold button in the Font group of the Home tab. If the active cell isn't bold, the Bold button displays in its normal color. But if the active cell is already bold, the Bold button displays a different background color. If you click this button, it toggles the Bold attribute for the selection.
-  Simple drop-downs: If the Ribbon command has a small downward-pointing arrow, the command is a drop-down list. An example is the Orientation control in the Alignment group of the Home tab. Click the control and additional commands appear below it.
-  Split buttons: A split button control combines a one-click button with a drop-down. If you click the button part, the command is executed. If you click the drop-down part, you choose from a list of related commands. An example of a split button is the Paste command in the Clipboard group of the Home tab. Clicking the top part of this control pastes the information from the Clipboard. If you click the bottom part of the control, you get a list of pasterelated commands (see Figure 1-4).
-  Check boxes:A check box control turns something on or off. An example is the Gridlines control in the Show/Hide group of the View tab. When the Gridlines check box is selected, the sheet displays gridlines. When the control isn't selected, the sheet gridlines aren't displayed.
-  Spinners: An example of a spinner control is in the Scale to Fit group of the Page Layout tab. Click the top part of the spinner to increase the value; click the bottom part of the spinner to decrease the value.
Refer to Chapter 20 for information about customizing Excel's Ribbon.
Some Ribbon groups contain a small icon in the lower-right corner, known as a dialog launcher. For example, if you examine the Home’‘Alignment group, you'll see this icon (refer to Figure 1-5). Click it, and it displays the Format Cells dialog box, with the Number tab preselected. This dialog box provides options that aren't available on the Ribbon.
Figure 1-4: The Paste command is a split button control.  
Figure 1-5: This small dialog launcher icon, when clicked, displays a dialog box that has additional options.
The Quick Access toolbar
The Quick Access toolbar is a place to store commonly used commands. The Quick Access toolbar is always visible, regardless of which Ribbon tab you select. Normally, the Quick Access toolbar appears on the left side of the title bar. Alternatively, you can display the Quick Access toolbar below the Ribbon by right-clicking the Quick Access toolbar and choosing Show Quick Access Toolbar Below the Ribbon.
By default, the Quick Access toolbar contains three tools: Save, Undo, and Redo. You can customize the Quick Access toolbar by adding other commands that you use often. To add a command on the Ribbon to your Quick Access toolbar, right-click the command and choose Add To Quick Access toolbar.
Excel has quite a few commands that aren't available on the Ribbon. In most cases, the only way to access these commands is to add them to your Ribbon or Quick Access toolbar. Figure 1-6 shows the Quick Access toolbar section of the Excel Options dialog box. This area is your one-stop shop for Quick Access toolbar customization. A quick way to display this dialog box is to right-click the Quick Access toolbar and choose Customize Quick Access toolbar.