Excel Secrets Everyone Should Know

Hidden Secrets In Microsoft Excel

This ebook from Francis Hayes gives you professional tools to help you get the most out of your Excel program. Any one of these secrets could be the only one that you ever need to know for Excel, but this ebook includes bunches of those tips and tricks! Just think of all of the useful information you can get from it! If you have ever been frustrated at your lack of progress in Microsoft Excel, this guide will teach you everything that you need to know to harness the powerful functions or time-saving elements of Microsoft Excel. Excel is used by offices all over the world, but so few people take the time to teach you anything important about it. Too much time is usually wasted searching the internet for tips on how to use it more efficiently Learn the best way to master Excel in this ebook! Read more here...

101 Secrets of a Microsoft Excel Addict Overview

Rating:

4.7 stars out of 14 votes

Contents: Ebook
Author: Francis Hayes
Official Website: www.theexceladdict.com
Price: $9.97

Access Now

My 101 Secrets of a Microsoft Excel Addict Review

Highly Recommended

This is one of the best e-books I have read on this field. The writing style was simple and engaging. Content included was worth reading spending my precious time.

As a whole, this book contains everything you need to know about this subject. I would recommend it as a guide for beginners as well as experts and everyone in between.

VBA Programming in Excel

Working with Excel-related objects Accessing and using Excel objects Changing the contents of individual cells Creating your own functions to use in Excel Adding pizzazz to your Excel worksheet Defining your own special-purpose worksheets icrosoft Excel provides a wealth of opportunities for custom VBA programs. You can do everything from creating special equations to designing eye-grabbing presentations. With some effort, you can create detailed graphs and charts on the fly. It's also possible to design self-checking worksheets, where a press of a button verifies the data you entered. Of all the Office products, the third-party add-in product market for Excel is the largest and includes a vast array of product types, including add-ins that you can use with your VBA programs. Although you probably wouldn't want to write War and Peace with Excel, products are available that you can use to turn it into a simple word processor. Many products provide advanced math modeling and other...

Understanding the Excel Related Objects

Excel relies on some essential objects to provide access to the various data elements. Because of the nature of worksheets, you'll find that each of these objects can actually perform multiple duties. For example, you don't have to access a worksheet through a Workbook object unless you don't know the name of the worksheet. Most objects also include links to objects above and below the current position in the hierarchy so that you can use the object of convenience to access a data element. This section focuses on Excel object issues. The demonstrations show how the various objects interact and what you need to consider while you design and build your Excel program. For example, you need to consider where to add charts and graphs while you build them so that they're most convenient in displaying the data.

Selecting Objects within Excel

You can place any object that you want in an Excel worksheet, including pictures and sounds. These kinds of objects work very much the same in Excel as they do in Word. (See the Selecting Objects in a Word Document section of Chapter 13.) The main difference is that you use the OLEObjects collection of the sheet that holds the object. However, Excel can also embed Chart objects in a worksheet. Because this is such a special feature, I show you in this section how to work with embedded Chart objects. The same data can say different things depending on how you present it. A pie chart tells the viewer about parts of a whole, and a bar chart compares individual values. The problem with charts that you create in Excel is that they're static they continue to say the same thing unless you redesign them. Fortunately, you can control the appearance of an embedded chart just as easily as you can control a standalone chart. The code in Listing 14-7

Sending Notes from Word to Excel

You might find the need to create a utility simply because a task becomes unwieldy in the host program. For example, many of my worksheets contain extensive notes. Editing those notes in Excel can become a real problem after the note gets to a certain size. However, Word provides a great editor, and I can make the notes as long as needed. The example shown in Listing 16-7 includes part of a program that I created for moving notes between Word and Excel so that they're easier to edit. (You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e.)

Excels Data Import Tools

Excel 2007 has a rich set of data handling tools. On the Developer ribbon, you'll find quick access to Microsoft Access databases, web tables, and text files. Excel also includes data access to SQL Server and OLAP databases, XML data, and ODBC data sources. By recording macros, you can see how Excel connects us to various data sources. From there, you'll begin writing your own data access routines.

Inside the Excel XML File Format

Excel 2007 builds on its history of XML support with a new file format called the Office Open XML format. This new format improves file and data management, data recovery, and interoperability. Any application that supports XML can access and work with information in an Excel 2007 workbook. This means it is now possible to work with your Excel data in systems outside of Microsoft Office products as long as they provide XML support. Additionally, security concerns are reduced, since you now have your Excel data in what is essentially a text file. This allows data to pass through firewalls without difficulty.

Using ActiveX Components in Your Excel Projects

If you're moving your Microsoft development tools to Windows Vista, you will not have this option available to you. Given that there are still plenty of ActiveX components available (both free and for pay), and since you can still create your own if you are not moving to Vista right away, we'll take a short look at incorporating them into your Excel projects.

Excel in the NET World

We can't code directly in the Excel 2007 VBE to use .NET components, but we can download tools from Microsoft that will let us create Excel projects from within Visual Studio 2005. VSTO and VSTO SE each come with a suite of tools that allow us to access various functions within an Excel project. VSTO, which interfaces with Office 2003 applications, allows direct access to an Excel workbook and gives us programmatic control within the managed code environment. VSTO project templates include the ability to create the following Excel workbook projects Excel template projects Excel Word Word Outlook Add-in Workbook Document Template A project for creating managed code extensions behind a new or existing Excel 2003 template. Name ExcelTemplatel Name ExcelTemplatel VSTO SE provides us with two new project categories, Excel 2003 Add-ins and Excel 2007 Add-ins. It adds an InfoPath template project to the Office menu, as shown in Figure 9-6 (which shows the Windows Vista interface). VSTO is no...

Managed Code in an Excel Project

We are going to look at two examples of running managed code in Excel. In our ActiveX examples, we revisited some of the functionality we'd already built directly in Excel using VBA. The first example .NET project will duplicate some of the functionality from our previous VBA projects, but you'll see how the .NET versions require absolutely no code on the Excel client workbook. Our second example will show how to create a custom task pane in an Excel 2007 Add-in project in conjunction with the .NET version of an Excel UserForm.

Displaying Excels Builtin Dialog Boxes

You can write VBA code that performs the equivalent of selecting an Excel menu command and making choices in the resulting dialog box although Excel doesn't actually display the dialog box. In some cases, however, you may want your code to simply display one of Excel's many built-in dialog boxes and let the user make the choices in the dialog box. You can do this by using the Application object's Dialogs property. Here's an example The preceding example uses the predefined constant xlDialogFormulaGoto. This constant determines which dialog box Excel displays. To display Excel's Open dialog box, use this statement 2. In the Project Library drop-down list (the one at the upper-left corner of the Object Browser), select Excel. Unfortunately, these dialog box constants are not documented in the Help system. Therefore, you may need to use a bit of trial and error to figure out which is appropriate for your needs. You'll find that some Excel dialog boxes don't have a corresponding dialog...

Introducing the Excel Object Model

Introducing the concept of objects Finding out about the Excel object hierarchy Understanding object collections Referring to specific objects in your VBA code Accessing or changing an object's properties Performing actions with an object's methods In this chapter, I introduce you to the Excel object model, which is a hierarchy of objects contained in Excel. By the time you finish this chapter, you'll have a reasonably good understanding of what OOP is all about and why you need to understand this concept to become a VBA programmer. After all, Excel programming really boils down to manipulating Excel objects. It's as simple as that.

Changing Excel Settings

Some of the most useful macros are simple procedures that change one or more of Excel's settings. For example, simply changing the recalculation mode from automatic to manual requires numerous steps. You can save yourself some keystrokes and menu choices (not to mention time) by creating a macro that automates this task. This section presents two examples that show you how to change settings in Excel. You can apply the general principles demonstrated by these examples to other operations that change settings.

When the Normal Excel Menus Arent Good Enough

Ou may not realize it, but you can change almost every aspect of Excel's menus. Typical Excel users get by just fine with the standard menus. Because you're reading this book, however, you're probably not the typical Excel user. In this chapter I describe how to make changes to the Excel menu system. Most of the Excel applications you develop get along just fine with the standard menu system. In some cases, however, you may want to add a new menu to make it easier to run your VBA macros. In other cases, you may want to remove some menu items to prevent users from accessing certain features. If these sorts of changes seem useful, you should read this chapter. Otherwise, you can safely skip it until the need arises.

Excel searches through the selected libraries to find the reference for the object type If the type is found in more

This example opens an existing Word document from Excel. The declared variables, wdApp and wdDoc, are of Word object types. wdApp is used to create a reference to the Word Application in the same way the Application object is used in Excel. New Word.Application is used to create a new instance of Word.

Getting Started With Excel Macros

When you use shortcut keys for macros in other workbooks, you may not always receive the correct macro to execute. Excel does not let you assign a shortcut key if a macro in your current workbook uses the shortcut, but it does not check unopened workbooks. If you open workbooks with the same shortcut key, Excel does not know which macro to execute when you select the shortcut. If you use the shortcut keys for a macro and do not receive the anticipated results, you need to verify what you assigned to the shortcut keys.

Managing the Excel Application

In the beginning of this chapter, you learned that objects are organized in a special structure called the object model. The topmost object in an application's object model is the application itself. By controlling the Application object, you can perform many tasks, such as saving the way your screen looks at the end of a day's work or quitting the application. As you know, Excel allows you to save the screen settings by using the Save Workspace option from the File menu. The task of saving the workspace can be easily performed with VBA The above instruction saves the screen settings in the workspace file named Project. The next time you need to work with the same files and arrangement of windows, simply open the Project file and Excel will bring up the correct files and restore your screen to your liking. Change the title of the Excel application to My Application. Change the title of the Excel application back to Microsoft Excel. Find out the name of the folder where Excel.exe...

Posting Excel XML Data to a Web Server

Armed with your newly acquired XML skills, you can now write a useful business application that will handle the required processing on the server without human intervention. Take, for example, a common problem related to collecting employee timesheets. Each week, an employee fills in a timesheet that reports regular hours worked and overtime. The timesheets are collected by a designated person, often an administrative assistant, who must then summarize the data and perhaps rekey each timesheet into a database system. Using your Excel, XML, and ASP skills, you can now eliminate the timesheet task from the administrator's weekly routine. Each employee can be given an Excel spreadsheet with a custom-designed timesheet. When he is done reporting his time, the information can be processed automatically with the press of a button. The following exercise demonstrates how to go about extracting data from an Excel range in the XML format (this was discussed earlier in this chapter) and posting...

Who Develops in Excel

When I was younger, I remember wanting to be many different things a dolphin trainer, a professional hockey player, and a pilot, to name a few. I also went through a phase when I thought it would be cool to be a programmer (except as a kid I wanted to develop games, not boring business programs). Then I grew up and my priorities changed. In fact, in my first real career job, I was a financial analyst. At no point in my life did I plan on becoming an Excel developer it just happened. The factors that drove me to become one are interesting and, I believe, quite common in the corporate world. First, most analysts, and many other knowledge workers for that matter, live and breathe spreadsheets. I was no different in that regard. I figured the better I understood all of the features and functions available in Excel, the better I would become at my job as it would enable me to analyze data more efficiently and with fewer mistakes. The result of all of these factors was that after a few...

Exploring Other Excel Objects

To go into detail on all of the objects available would fill many more pages than I have targeted for this book. Besides, you already have Excel's detailed help files available to you. Therefore, I believe the way I can provide added value to you is to help you focus on some of the objects that you're likely to use in the course of your normal development activities and give you good advice regarding how you use these objects. So far, I've covered the most important objects Application, Workbook, Worksheet, and Range. Now I need to show you some of the more common supporting objects.

Excel Host Items and Host Controls

VSTO enhances a number of objects in the Excel object model, such as the ability to bind data to an object and to expose the object's events. These objects are defined as host items and host controls. Recall that host items are containers for host controls in the same way that a UserForm is a container for an ActiveX control. You add the host controls to the host item. Excel has three types of host items Workbook, Worksheet, and Chartsheet. There is one Workbook host item and a separate Worksheet host item for each worksheet in the workbook. These host items wrap the native Excel workbook, worksheet, and chartsheet, which are in the Microsoft.Office.Interop.Excel namespace. As we did with Microsoft.Office.Interop.Word, here we refer to objects in the Microsoft.Office.Interop.Excel namespace as Interop objects. The host items and host controls are in the Microsoft.Office.Tools.Excel namespace, and we sometimes refer to objects in this namespace as VSTO objects. Host items and host...

Adding Host Items in Excel

A VSTO solution can contain only one workbook, and the workbook that is created in a VSTO solution is automatically created as a host item. When you add a worksheet or chartsheet to a workbook at design time when Excel is hosted inside Visual Studio, VSTO automatically creates the worksheet or chartsheet as a VSTO object (a host item). VSTO does not provide a way for you to create host controls dynamically. If you add a worksheet to the workbook programmatically by adding a sheet to the Worksheets collection, it is created as an Interop worksheet rather than a worksheet host item. Keep in mind that you will not have any of the VSTO-specific functionality such as data binding capabilities and additional events of a worksheet that is added programmatically in this way. You also cannot add a host control or Windows Forms control to an Interop worksheet. Only VSTO worksheets can host these types of controls.

Why Excel Is Great for Developers

Excel is a highly programmable product, and it's easily the best choice for developing spreadsheet-based applications. For developers, Excel's key features include the following Visual Basic for Applications This macro language lets you create structured programs directly in Excel. This book focuses on using VBA, which, as you'll discover, is extremely powerful and relatively easy to learn. Easy access to controls Excel makes it very easy to add controls such as buttons, list boxes, and option buttons to a worksheet. Implementing these controls often requires little or no macro programming. Customizable user interface Developers have lots of control over the user interface. In previous versions, this involved creating custom menus and toolbars. In Excel 2007, it involves modifying the Ribbon. Changing the Excel 2007 interface is not as easy as it was in previous versions, but it's still possible. Powerful data analysis options Excel's PivotTable feature makes it easy to summarize...

Excels Role in Microsofts Strategy

Currently, most copies of Excel are sold as part of Microsoft Office - a suite of products that includes a variety of other programs. (The exact programs that you get depend on which version of Office you buy.) Obviously, it helps if the programs can communicate well with each other. Microsoft is at the forefront of this trend. All the Office products have extremely similar user interfaces, and all support VBA. Therefore, after you hone your VBA skills in Excel, you'll be able to put them to good use in other applications - you just need to learn the object model for the other applications.

Solving Problems with Excel

You might already have a good idea of the types of tasks for which you can use a spreadsheet. Traditionally, spreadsheet software has been used for numerical applications that are largely interactive. Corporate budgets are an excellent example of this. After the model has been set up (that is, after formulas have been developed), working with a budget is simply a matter of plugging in amounts and observing the bottom-line totals. Often, budgeters simply need to allocate fixed resources among various activities and present the results in a reasonably attractive (or at least legible) format. Excel, of course, is ideal for this. Budget-type problems, however, probably account for only a small percentage of your spreadsheet-development time. If you're like me, you've learned that uses for Excel can often extend well beyond the types of tasks for which spreadsheets were originally designed. Here are just a few examples of nontraditional ways that Excel can be used As a presentation device...

The users installed version of Excel

With every new release of Excel, the issue of compatibility rears its head. As I write this, Excel 2007 has just been released - yet many large corporations are still using Excel 2000 and some use even earlier versions. Unfortunately, there is no guarantee that an application developed for, say, Excel 2000 will work perfectly with later versions of Excel. If you need your application to work with a variety of Excel versions, the best approach is to work with the lowest version - and then test it thoroughly with all other versions. Things get even more complicated when you consider Excel's sub-versions. Microsoft distributes service releases (SRs) to correct problems. For example, users might have the original Excel 2000, Excel 2000 with SR-1, or Excel 2000 with SR-2. And it gets even more complicated with Excel 2003. Excel 2007 has a quite a few known problems, and it's likely that at least some of them will be corrected in a future service release.

Displaying Excels Developer tab

The Excel 2007 Ribbon does not display the Developer tab by default. If you're going to be working with VBA, it's essential that you turn on the Developer tab 1. Choose Office Excel Options. 2. In the Excel Options dialog box, click the Popular tab. After you perform these steps, Excel displays a new tab, as shown in Figure 7-1.

Excel VBA Programmers Reference

This book is aimed squarely at Excel users who want to harness the power of the VBA language in their Excel applications. At all times, the VBA language is presented in the context of Excel, not just as a general application programming language. The Primer has been written for those who are new to VBA programming and the Excel object model. It introduces the VBA language and the features of the language that are common to all VBA applications. It explains the relationship between collections, objects, properties, methods, and events and shows how to relate these concepts to Excel through its object model. It also shows how to use the Visual Basic Editor and its multitude of tools, including how to obtain help. The middle section of the book takes the key objects in Excel and shows, through many practical examples, how to go about working with those objects. The techniques presented have been developed through the exchange of ideas of many talented Excel VBA programmers over many...

The Excel Object Model

The Visual Basic for Applications programming language is common across all the Microsoft Office applications. In addition to Excel, you can use VBA in Word, Access, PowerPoint, and Outlook. Once you learn it, you can apply it to any of these. However, to work with an application, you need to learn about the objects it contains. In Word, you deal with documents, paragraphs, and words. In Access, you deal with databases, recordsets, and fields. In Excel, you deal with workbooks, worksheets, and ranges. Unlike many programming languages, you don't have to create your own objects in Office VBA. Each application has a clearly defined set of objects that are arranged according to the relationships between them. This structure is referred to as the application's object model. This section is an introduction to the Excel object model, which is fully documented in Appendix A.

Excel and the Internet

Until a few years ago, a typical Excel-based application was almost entirely contained within Excel itself the only external interaction would be with the user, from whom you obtained data and to whom you presented results. If you needed to store data, you'd use separate workbooks and try to mimic a relational database as best you could. As data access technologies developed, from ODBC drivers through DAO to the current versions of ADO (documented in Chapter 20), it became more commonplace to store data in external databases and retrieve data from (and update data in) other systems across the network. It is now quite common to see Excel used as a front-end querying and analysis tool for large corporate databases, using QueryTables and PivotTables to retrieve the data. The data available to Excel applications was, however, limited to what was available across the company network, and to those databases you could get permission to access. Starting with the release of Office 97,...

Interacting with Excel

VBA and Excel are two different programs that have had very different upbringings. VBA speaks American. Excel also speaks American. However, Excel can also speak in its users' language if they have the appropriate Windows settings and Office language pack installed. On the other hand, VBA knows only a little about Windows settings, and even less about Office 2007 language packs. So, either you can do some awkward coding to teach VBA how to speak to Excel in the user's language, or you can just let them converse in American. I very much recommend the latter. Unfortunately, most of the newer features in Excel are not multilingual. Some only speak American, and others only speak in the user's language. You can use the American-only features if you understand their limitations the others are best avoided. All of them are documented later in the chapter.

Reading Data from Excel

When reading a cell's value, using its Value property, the data type that Excel provides to VBA is determined by a combination of the cell's value and its formatting. For example, the number 3000 could reach VBA as a Double, a Currency, or a Date (March 18, 1908). The only international issue of concern here is if the cell's value is read directly into a string variable the conversion will then be done implicitly, and you may not get what you expect (particularly if the cell contains a Boolean value). As is the case when sending data to Excel, the translation between U.S. and local functions and formats occurs when reading data from Excel. This means that a cell's .Formula or .NumberFormat property is given in English, and with U.S. number and date formatting, regardless of the user's choice of language or regional settings.

Developing Excel Utilities with VBA

A utility, in general, is something that enhances software, adding useful features or making existing features more accessible. This chapter is about Excel utilities About Excel utilities and utilities in general Step-by-step details for developing a useful Excel utility to manipulate text in cells Where to go for more Excel utilities As you'll see, creating utilities for Excel is an excellent way to make a great product even better.

About Excel Utilities

A utility isn't an end product, such as a quarterly report. Rather, it's a tool that helps you produce an end product, such as a quarterly report. An Excel utility is (almost always) an add-in that enhances Excel with new features or capabilities. Excel is a decent product, but many users soon develop a wish-list of features that they would like to see added to the software. For example, some users who turn off the grid-line display want a feature that toggles this attribute so that they don't have to go through the tedious Tools Options command. Users who work with dates might want a pop-up calendar feature to facilitate entering dates into cells. And some users desire an easier way to export a range of data to a separate file. These are all examples of features that will probably never find their way into Excel. You can, however, add these features by creating a utility.

Understanding Excels Events

This chapter explains the concept of Excel events, and I include many examples that you can adapt to your own needs. As you can see, understanding events can give your Excel applications a powerful edge. Here you will find An overview of the types of events that Excel can monitor Excel is capable of monitoring a wide variety of events, and executing your VBA code when a particular event occurs. Following are just a few examples of the types of events that Excel can recognize

Event Types That Excel Can Monitor

Excel is programmed to monitor many different events that occur. These events can be classified as the following Application events Events that occur for the application (Excel). Examples include NewWorkbook (a new workbook is created), WorkbookBeforeClose (any workbook is about to be closed), and SheetChange (a cell in any open workbook is altered). To monitor Application-level events, you need to use a class module.

Using Automation in Excel

You can write an Excel macro to control other applications, such as Microsoft Word. More accurately, the Excel macro will control Word's automation server. In such circumstances, Excel is the client application, and Word is the server application. Or you can write a Visual Basic application to control Excel. The process of one application's controlling another is sometimes known as Object Linking and Embedding (OLE) or simply Automation. Some applications, such as Excel, can function as either a client application or a server application. Other applications can function only as client applications or only as server applications.

Controlling Excel from another application

You can, of course, also control Excel from another application (such as another programming language or a Word VBA procedure). For example, you might want to perform some calculations in Excel and return the result to a Word document. You can create any of the following Excel objects with the adjacent functions Application object Workbook object CreateObject(Excel.Sheet) Chart object CreateObject(Excel.Chart) Listing 20-4 shows a procedure that is located in a VBA module in a Word 2003 document. This procedure creates an Excel Worksheet object whose moniker is Excel.Sheet from an existing workbook. Listing 20-4 Producing an Excel Worksheet on a Word Document Set XLSheet GetObject(Wbook, Excel.Sheet).ActiveSheet Recalculating the worksheet updates a chart. The data and the chart are then copied from the Excel object and pasted into a new document. The results are shown in Figure 20-9. Figure 20-9 The Word VBA procedure uses Excel to create this document. Figure 20-9 The Word VBA...

Understanding Excels AddIn Manager

The most efficient way to load and unload add-ins is with Excel's Add-Ins dialog box, which you access by choosing Tools Add-Ins. This command displays the Add-Ins dialog box, as shown in Figure 21-1. The list box contains the names of all add-ins that Excel knows about, and check marks identify add-ins that are open. You can open and close add-ins from this dialog box by clearing or marking the check boxes. Beginning with Excel 2002, the Add-Ins dialog box features a new button Automation. Use this button to install a COM add-in. Although Excel 2000 supports COM add-ins, it doesn't provide a direct way to install them. You can also open most add-in files by choosing the File Open command. Because an add-in is never the active workbook, however, you can't close an add-in by choosing File Close. You can remove the add-in only by exiting and restarting Excel or by executing VBA code to close the add-in. For example When you open an add-in, you might or might not notice anything...

Few Words about Excels Menu

So how does a menu bar differ from a toolbar In general, a menu bar is displayed at the top of the Excel window, directly below the title bar. When clicked, the top-level controls on a menu bar display a drop-down list of menu items. A menu bar can also contain three window control buttons (Minimize, Restore, and These rules are definitely not cast in stone. You can, if desired, add traditional toolbar buttons to a menu bar or add traditional menu items to a toolbar. You can even move a menu bar from its traditional location and make it free-floating. Although Excel supports multiple menu bars, only one can be visible at any time. Beginning with Excel 2002, Excel's menu bar displays a Type a question for help box, which is a quick way to search the Help system. If you would prefer not to see this box, you can hide it by using the following VBA statement

What You Can Do with Excels Menus

Typical Excel users get by just fine with the standard menus. Because you're reading this book, however, you're probably not the typical Excel user. You might want to modify menus to make your life easier and to make life easier for the folks who use the spreadsheets that you develop. To modify Excel menus, you can remove elements, add elements, and change elements. In addition, you can temporarily replace Excel's standard menu bar with one of your own creation. You can change Excel's menus two ways manually, or with Visual Basic for Applications (VBA) code. When you close Excel, it saves any changes that you've made to the menu system, and these changes appear the next time that you open Excel. The information about menu modifications is stored in your XLB file.

Help Systems That Use Excel Components

Perhaps the most straightforward method of providing help to your users is to use the features contained in Excel itself. The primary advantage of this method is that In this section, I provide an overview of some help techniques that use the following built-in Excel components

Im very familiar with creating formulas in Excel Does VBA use the same mathematical and logical operators

I would like a particular workbook to be loaded every time that I start Excel. I would also like a macro in this workbook to execute automatically. Am I asking too much VBA can't do it, but Excel's old XLM language can. Fortunately, you can execute XLM from VBA. Here's a simple example that retrieves the value from cell A1 on Sheetl in a workbook named myfile.xls in the c files directory A more general solution to avoid Excel prompts is to insert the following instruction

Can I use Excels builtin worksheet functions in my VBA code

Excel's worksheet functions are accessed via the WorksheetFunction method of the Application object. For example, you could access the SUM worksheet functions with a statement such as the following Generally, if VBA includes an equivalent function, you cannot use Excel's worksheet version. For example, because VBA has a function to compute square roots (Sqr), you cannot use the SQRT worksheet function in your VBA code.

Where can I get Excel addins

You can get Excel add-ins from a number of places Excel includes several add-ins that you can use whenever you need them. Use the Tools Add-Ins command to install them. When I install my add-in from Excel's Add-Ins dialog box, it shows up without a name or description. How can I give my add-in a description Oddly, there is no way to remove unwanted add-ins from the list directly from Excel. You must edit the Windows Registry and remove the references to the add-in files that you don't want listed. Another way to do this is to move or delete the add-in files. Then when you attempt to open the add-in from the Add-Ins dialog box, Excel will ask whether you want to remove the add-in from the list. Activate any worksheet and then choose File Save As. Then select Microsoft Excel Add-in (*.xla) from the Save as Type drop-down list.

Interacting with Microsoft Excel

Microsoft Excel is a great program for playing what-if scenarios with data because it lets you plug data and formulas into cells in whatever manner you want. Excel isn't good, however, at managing large volumes of data. For large volumes of data, you need a database like Microsoft Access. Microsoft Access can certainly do any math calculations that Excel can do. Playing with what-if scenarios with data in Access isn't so easy, though, because you need to get queries and or forms involved. It's just plain difficult to experiment with what-if scenarios in Access. You could, of course, just open Excel and type in the total sales value or even copy and paste it from some form in Access. Optionally, you could automate the whole thing by creating a button on some Access form that opens the worksheet and plugs in the total sales amount for you. Look at an example of Automation that does just that. The first step is to create an Excel worksheet that contains a blank cell that gets its values...

Unleash the Power of Excel with VBA

The Power of Excel Visual Basic for Applications combined with Microsoft Excel is probably the most powerful tool available to you. This tool is sitting on the desktops of 400 million users of Microsoft Office and most have never figured out how to harness the power of VBA in Excel. Using VBA, you can speed the production of any task in Excel. If you regularly use Excel to produce a series of monthly charts, you can have VBA do the same task for you in a matter of seconds. There are two barriers to learning successful VBA programming. First, Excel's macro recorder is flawed and does not produce workable code for you to use as a model. Second, for many who learned a programming language such as BASIC, the syntax of VBA is horribly frustrating. Microsoft began to dominate the spreadsheet market in the mid-90s. Although they were wildly successful in building a powerful spreadsheet program toward which any Lotus 1-2-3 user could easily transition, the macro language was just too...

Excel File Formats Supported

As you probably know, Excel allows you to save a workbook in a format for earlier versions. In addition, you can save a workbook in a dual-version format that combines two file formats in a single file. These dual-version formats result in a larger file, but unfortunately, these dual-version formats sometimes introduce problems of their own. If your application must work with earlier versions of Excel, you need to make sure that your file is saved in the appropriate file format. The various Excel file formats that can be saved by Excel 2002 are Microsoft Excel Workbook (*.xls) The standard Excel 2003 file format. Can be opened by Excel 97, Excel 2000, Excel 2002, and Excel 2003. Microsoft Excel 5.0 95 Workbook A format that can be opened by Excel 5.0 and later versions. Microsoft Excel 97-2000 & 5.0 95 Workbook A dual format that can be opened by Excel 5 and later versions. Microsoft Excel 4.0 Worksheet (*.xls) Can be opened by Excel 4 and later versions. This format saves a single...

How VBA Works with Excel

Rich Tennant

No, that's not the icon or Excel, it's the icon tor Excuse, the database o reasons vihy you haven't learned the other programs in Oiiice* 7 he next four chapters provide the necessary foundation for discovering the ins and outs of VBA. You find out about modules (the sheets that store your VBA code) and are introduced to the Excel object model (something you won't want to miss). You also discover the difference between subroutines and functions, and you get a crash course in the Excel macro recorder.

Printing Excel Data to an Internet Browser using the Get String Method

You can use the Recordset object's GetString method to print the data contained in an Excel spreadsheet in an Internet browser. This method returns a set of records into a string and is faster than looping through the recordset. The GetString method has the following syntax Now let's see how you can use the GetString method to retrieve the data from the Excel file created in an earlier example (or simply substitute the file name with any Excel spreadsheet name you want to read). 3. Save the ASP file as C ExcelWithASP GetExcel.asp. strCon strCon & & strCon strCon & Extended Properties Excel 8.0 The above ASP script connects to the specified Excel file and retrieves the data located in Sheetl. After reading the column names from the Fields collection, the code uses the above-mentioned GetString method to pull the data

Using XML to Round Trip a Workbook from Excel to HTML and Back

Diagnosis Worksheet Template

Microsoft is thrilled that you can now save Excel 2003 and Word 2003 documents as XML files. They consider XML files to be native Office documents now. This is cool, because it means that any program that can write XML can, in theory, produce an Excel file on the fly. Round-tripping is the process of saving an Excel file as XML, and then opening the XML file back into Excel. Figure 15.5 shows the results of round-tripping an Excel file. The file on the left contains greenbar formatting, strange fonts, and a chart. The formatted spreadsheet on the left was saved as an XML Spreadsheet file, closed, and re-opened as the file on the right. Excel did warn that the chart and any VBA code would be lost. Other than the chart, all other formatting was preserved perfectly on the round-trip to XML and back. Excel 2003 now supports XML as a native file format. Excel 2003 now supports XML as a native file format. Figure 15.6 shows a subset of the XML code created when an Excel file is saved as an...

Understanding Excel menu terminology

Menu terminology is often a bit confusing at first because many of the terms are similar. The following list presents the official Excel menu terminology that I refer to in this chapter Menu bar The row of words that appears directly below the application's title bar. Excel has two menu bars One is displayed when a worksheet is active, and the other is displayed when a chart sheet is active or when an embedded chart is activated. Menu A single, top-level element of a menu bar. For example, both of Excel's menu bars have a File menu.

Excel s International Options

In the Office Menu O Excel Options dialog, the Advanced section contains Editing Options that allow the user to specify the characters that Excel uses for the thousands and decimal separators, overriding the Windows Regional Settings. These options can be read and changed in code, using Application.ThousandsSeparator, Application.DecimalSeparator, and Application.UseSystemSeparators. There is a big problem with this feature, in that while these options affect all of Excel's xxxLocal properties and functions (including the Application.International settings), they are ignored by VBA. The VBA Format function used almost every time a number is displayed to the user ignores these options, resulting in text formatted according to the Windows Regional Settings, not those used by Excel. 'Only do for Excel 2002 and greater If Val(Application.Version) > 10 Then 'Get the override separators used by Excel sXLThousand Application.ThousandsSeparator sXLDecimal Application.DecimalSeparator 'Swap...

Removing Excel menu elements

You can remove any part of the Excel menu system menu items, menus, and entire menu bars. For example, if you don't want the end users of your application fiddling with the display, you can remove the View menu from the Worksheet Menu Bar. You can also remove one or more menu items from a menu. If you remove the New menu item from the File menu, for example, users can't use the menu to create a new workbook. Finally, you can eliminate Excel's menu bar and replace it with one that you've created. You might do this if you want your application to be completely under the control of your macros.

Listing Automating Power Point Presentation Creation from Excel

CreatePresentation is the main procedure of this listing. Right off the bat, you can tell that this procedure is using early binding just by looking at the variable declarations. CreatePresentation uses two variables that represent a PowerPoint object. The first, ppt, represents the PowerPoint.Applica-tion object. Like the Application object from the Excel object model, PowerPoint.Application is at the top of the PowerPoint object model. The second variable that represents a PowerPoint object is named pres and represents a PowerPoint Presentation object. The Presentation object is roughly analogous to the Workbook object in Excel. At this point, I can copy in the data from Excel. Because I need to do a number of things when I'm copying from Excel to PowerPoint, it's a good idea to create a procedure that wraps this task up. To do so, I enter the CopyDataRange procedure. Copying from Excel to PowerPoint is a two statement process. The first step is to copy the range in Excel to the...

Importing XML in Excel

Importing XML data into an Excel workbook is a fairly straightforward process. The Workbook object has a method called XmlImport that does the work for us. C projects Excel cds.xml, ImportMap Nothing, Overwrite True, Destination Range( A 1) End Sub Member of Excel .Application I Class Workbook Member of Excel If no schema exists for this data, Excel will create one for you, as shown in Figure 3-7. Excel refers to these schemas as XML maps. Click the check box so you won't see this message any longer, and let the import continue. When the import is finished, the data should look like that in Figure 3-8. C projects Excel cds.xml, ImportMap This is due to the fact that the first time we ran the code, Excel created a map for us and bound our data table to it. To view that map, right-click anywhere in the data range and choose XML > XML Source (Figures 3-10 and 3-11). The second time the code is run, we run into trouble because Excel automatically creates a new map for the data that is...

Quattro Pro Excel 2007

Like Lotus, Borland was slow to jump on the Windows bandwagon. When Quattro Pro for Windows finally shipped in the fall of 1992, however, it provided some tough competition for the other two Windows spreadsheets, Excel 4.0 and 1-2-3 Release 1.1 for Windows. Importantly, Quattro Pro for Windows had an innovative feature, known as the UI Builder, that let developers and advanced users easily create custom user interfaces. There was a time when Quattro Pro seemed the ultimate solution for spreadsheet developers. But then Excel 5 arrived.

Excels Builtin Matrix Functions

Performing matrix mathematics with Excel is very simple. Let's begin by assuming that the matrices A and B have been defined by selecting the 3R x 3C arrays of cells containing the values shown in Figure 3-1 and naming them by using Define Name. Remember, we're simply assigning a range name to a range of cells. We usually refer to it as a range or an array the fact that we are calling it a matrix simply indicates what we intend to do with it. Addition or Subtraction. To add a constant (e.g., 3) to matrix A, simply select a range of cells the same size as the matrix, enter the formula A+3, then press COMMAND+RETURN or CONTROL+SHIFT+RETURN (Macintosh) or CONTROL+SHIFT+ENTER (Windows). When you array-enter a formula by pressing e.g., CONTROL+SHIFT+ENTER, Excel puts braces around the formula, as shown below A+3 Do not type the braces if you do, the result will not be recognized by Excel as a formula. Subtraction of a constant, multiplication or division by a constant, or addition of two...

What Is an Excel Application

First of all, I should define what I mean by an Excel application. By Excel application, I mean a spreadsheet that you have developed using Excel and VBA for use by yourself or other users. VBA stands for Visual Basic for Applications, which is essentially Visual Basic that has been modified to run within other host applications such as Excel or Microsoft Word. Excel applications contain VBA code that may perform one or more of the following tasks Automate tedious processes. Many times, once you create a useful spreadsheet, in order to maintain it, you need to perform a set of tasks in a specific order on a frequent basis. Often, you can automate such simple tasks using Excel's macro recorder. For more complex tasks you need to write VBA code. Enhance the user experience. The experience and comfort level of people using Excel varies widely. One way to spread the wealth of a useful spreadsheet is to incorporate a user interface into the workbook that can serve to guide the less...

Opening Text Files in Excel

Even if you are already familiar with the mechanics of opening a text file in Excel, it is a good idea to refresh your knowledge here because the process of opening these files programmatically depends on your familiarity with the manual process. I will demonstrate the mechanics of opening up a delimited file using the file shown in Figure 15.1. For any text file, you begin the process by selecting File Open in Excel and then you change the Files of Text drop-down box to Text Files (*prn., *.txt, *.csv) as shown in the following graphic. Usually you can tell by looking at the preview in the bottom half of Figure 15.3 whether the file's type is delimited or fixed-width. This can be tricky with tab-delimited files, however, as they can look a bit like a fixed-width file. If in doubt, you can always select delimited and move on to the next step. If you select the tab delimiter and the file does not get parsed (the field separation lines do not appear), then go back to step 1 and choose...

Loading an XML Document in Excel

After saving an ADO recordset to an XML file on disk (see Hands-On 29-13 earlier in this chapter), you can load it into a desired application and read it as if it were a database. To gain access to the records saved in the XML file, use the Open method of the Recordset object and specify the filename, including its path and the persisted recordset service provider as Provider MSPersist. The following hands-on exercise demonstrates how to open a persisted recordset and write its data to an Excel workbook.

Understanding Excel Events

Excel associates UserForm events not only with the form but also with each of the controls that exist on the form. You need to place event-handling procedures Excel activates the UserForm. Excel adds a run-time control to the UserForm. Excel detects a UserForm control error. Excel closes the UserForm. Excel removes a control from the UserForm at run-time. Excel terminates the UserForm. AUTOMATING PROCEDURES WITH EXCEL EVENTS Application events include all events that the Application object recognizes. To access an application event you create a class module to contain your application event-handling procedure code. See the section Run a Procedure when Excel Creates a The following table provides a list of the application-level events that occur within Excel. An event that occurs for the application, in this case Excel. For example, Excel triggers the NewWorkbook event when it creates a new workbook. Occurs when Excel creates a new workbook. See the section Run a Procedure when Excel...

Using XML with ExceLVBA Projects

If you have any experience with the World Wide Web, whether it's developing Web sites or just browsing, then I am sure you have heard of XML (eXtensible Markup Language). Although not a new technology, it has only been in recent years that XML has generated a lot of interest. This is partially evident by Microsoft's decision to add XML support to some of its Office programs (including Excel) starting with version 10.0 (XP) and extending that support in version 11.0 (2003). I expect the level of XML support to increase in subsequent versions of Office applications. In this chapter I will discuss the following topics Opening and saving XML files with Excel

Simply Exporting Access Data to Excel

Just as in earlier Office versions, Access offers two ways to do a quick-and-dirty export of table or query data to an Excel worksheet. You can use the Excel button in the Export group of the External Data tab of the Ribbon to export Access data without worrying about formatting, for an Office 2007 user who just wants the data. If you need to create worksheets that can be opened and edited by users running older versions of Office, or using a handheld device such as a BlackBerry, you can use the TransferSpreadsheet method to export data, selecting the desired output worksheet format. This can be useful when you work for an organization that has upgraded its software and you need to send a worksheet with client contact information to a sales representative who has not updated her laptop yet. For a quick export to the new .xlsx worksheet format, use the Excel button in the Export group of the External Data tab of the new Access Ribbon, as shown in Figure 7.1. Exporting a table to an...

Using the Excel OM to Create a New Workbook

To create a new worksheet in Excel, you will build the code in a few steps. To start you create an Excel application object. Then, you create a new worksheet object, as shown in the following code Dim xlApp As Excel.Application Dim xlWorkbook As Excel.Workbook 'Create the Excel Application object Set xlApp After creating the new worksheet, you'll want fill that worksheet with data. The next example uses a Recordset object (from DAO, see Chapter 6) to gather the data from an Access query in the database. The Recordset object enables you to get the data from any table or row returning a query for the desired records to be exported to Excel. The following code creates a new Recordset based on the selected query in the list box and uses that query's name as the name for the Excel worksheet. Once the Recordset object has been created, Excel's CopyFromRecordset method can be used to copy data from the Recordset to the Cells object of the new worksheet. The following code shows how to...

Invoking Excel Functions in VBA Instructions

If a VBA function that's equivalent to one you use in Excel is not available, you can use Excel's worksheet functions directly in your VBA code. Just precede the function with a reference to the WorksheetFunction object. For example, VBA does not have a function to convert radians to degrees. Because Excel has a worksheet function for this procedure, you can use a VBA instruction such as the following The WorksheetFunction object was introduced in Excel 97. For compatibility with earlier versions of Excel, you can omit the reference to the WorksheetFunction object and write an instruction such as the following There are no new VBA functions in Excel 2002 or Excel 2003. *Not available in Excel 97 and earlier editions

Exporting XML data from Excel

Contrary to what you might expect, you can't export an arbitrary range of data in XML format. For example, if you create a List Range on your worksheet, you can't export that List Range to an XML file unless you add an appropriate map to your worksheet first. And it's not possible to create (or modify) a map by using Excel. If you choose the Excel File Save As command,you'll notice that one of the options is XML Spreadsheet. This produces an XML file that uses the Microsoft XMLSS schema.It will not export the data to a normal XML file that is readable by other applications.

Example Listing Excels Command Bar Objects

This indicates that Excel's main menu bars are different for worksheets than for chartsheets, as is evident if you look at the menus themselves. The worksheet menu bar has different controls than the Chart menu bar. Thus, if you want to add a custom menu item to Excel's main menu bar, regardless of what type of sheet is currently active, you will need to do so for both the Worksheet Menu Bar and the Chart Menu Bar. There is a slight complication concerning the CommandBars property that we should discuss. When qualified with the Application object, as in Application.CommandBars, this property returns the collection of all available built-in and custom command bars for the application which in this case is Excel. This is why we used the fully qualified expression However, from a Workbook, the CommandBars property returns a different collection. In particular, there are two possibilities. When the workbook is embedded within another application and Excel is activated by double-clicking...

Switching Excel to Display RC Style References

Checking R1C1 reference style on the General tab of the Options box causes Excel to revert to R1C1 style in the Excel user interface. Checking R1C1 reference style on the General tab of the Options box causes Excel to revert to R1C1 style in the Excel user interface. Microsoft Excel - Chapter6.xls

Using DOM with ADO to Convert Excel Data to XML

To help demonstrate this, open the Programming XML.xlsm file, found in the XMLSampleFiles folder in this chapter's download page at www.wrox.com. In this file, you will find the following procedure. This procedure loads an Excel range (in this case range A1 D43) into an ADO recordset, and then saves the recordset into the DOMDocument object, which is then output to an XML file oMyconnection.Open & _ Data Source & oMyWorkbook & & _ Extended Properties excel 8.0 & _ Persist Security Info False An XML file generated by ADO typically contains one root element and two child nodes Schema and Data. The Schema node contains information about the recordset structure field names, data type, field length, position, and so on. The Data node contains the actual data. Although attribute-based documents are difficult for humans to read, they are well formed and pose no problem for Excel.

Excel Development Best Practices

As YOU ADVANCE YOUR Excel development skills, you'll experience dramatic increases in productivity. These increases come from knowing more about various Excel objects and their properties and methods, being able to apply the correct object to the task at hand, and realizing more efficient development methodologies.

Using an ActiveX Component in Excel

Arr_sPath(0) 2007.accdb arr_sPath(l) Public Sub PlaceData(TheWorksheet As Excel.Worksheet, WhichData As String) Dim xl As Excel.Application Set xl TheWorksheet.Application 'hook into the current Excel session 1. Open a new workbook in Excel. 0 Visual Basic For Applications 0 Microsoft Excel 12.0 Object Library 0 OLE Automation 1. Return to Excel. One benefit that we see from this example is minimal code in our project. Let's take a look now at how .NET technologies can actually take the code out of our Excel projects.

Emulating Excels SUM Function

In this section, I present a custom function called MySum. Unlike the SimpleSum function listed in the previous section, the MySum function emulates Excel's SUM function perfectly. Before you look at the code for MySum, take a minute to think about the Excel SUM function. It is, in fact, very versatile. It can have as many as 30 arguments (even missing arguments), and the arguments can be numerical values, cells, ranges, text representations of numbers, logical values, and even embedded functions. For example, consider the following formula Function MySum(ParamArray args() As Variant) As Variant ' Emulates Excel's SUM function If an argument contains an error (for example, DIV 0 ), the MySum function simply returns the error just like Excel's SUM function. Excel's SUM function considers a text string to have a value of 0 unless By the way, I hope you understand that the point of this example is not to create a new SUM function. Rather, it demonstrates how to create custom worksheet...

The Excel Input Box method

Using Excel's InputBox method (rather than VBA's InputBox function) offers three advantages The syntax for the Excel InputBox method is CODES TO DETERMINE THE DATA TYPE RETURNED BY EXCEL'S INPUTBOX METHOD Excel's InputBox method is quite versatile. To allow more than one data type to be returned, use the sum of the pertinent codes. For example, to display an input box that can accept text or numbers, set type equal to 3 (that is, 1 + 2, or number plus text). If you use 8 for the type argument, the user can enter a cell or range address manually, or point to a range in the worksheet. Yet another advantage of using Excel's InputBox method is that Excel performs input validation automatically. In the GetRange example, if you enter something other than a range address, Excel displays an informative message and lets the user try again (see Figure 12-3). Figure 12-3 Excel's InputBox method performs validation automatically. Figure 12-3 Excel's InputBox method performs validation...

Excels Standard Controls

Figure 14-3 shows two toolboxes, each of which provides access to one type of control. (Below each toolbox is a control created using that toolbox.) The controls on the Control Toolbox (on the left in Figure 14-3) are ActiveX controls. These controls can be placed either on a UserForm or directly on a worksheet (but not a chartsheet). They are the same as the controls that are accessible from the VB editor's Toolbox when designing a UserForm. ActiveX controls are very flexible and generally support a wide range of events. The Control Toolbox can be opened from within Excel (not the Excel VBA IDE) by selecting the Customize option from the Tools menu and checking the Control Toolbox toolbar in the Toolbars tab. By selecting the Customize option from the Tools menu and checking the Forms toolbar in the Toolbars tab, you open the Forms toolbox. The controls on the Forms toolbox (on the right in Figure 14-3) are referred to as standard Excel worksheet...

Linking a Microsoft Excel Spreadsheet

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method of the DoCmd object, as shown in Hands-On 11-9. Note, however, that neither the DoCmd object nor its Transfer-Spreadsheet method are members of the ADO Object Model. The DoCmd object is built into the Microsoft Access library. Hands-On 11-9 Linking an Excel Spreadsheet

Excel Ribbon Customization

Excel 2007 offers its very own way of customizing the Ribbon, which is far more complex than manipulating the CommandBars collection. The new approach involves writing XML code in a text editor, copying that XML file into the workbook file (all outside of Excel ), editing a bunch of XML files (which also are stashed away inside the new Excel file format, which is really nothing more than a zipped container of individual but related files), and then writing VBA procedures to handle the clicking of the controls you put in the XML file. Explaining all the intricate details involved in customizing the Ribbon is well beyond the scope of this book. However, I walk you through a quick example that demonstrates the steps required to (manually) add a new Ribbon group to the Home tab. The new Ribbon group is named Excel VBA For Dummies, and it contains one button, labeled Click Me. Clicking that button runs a VBA macro named ShowMessage. 1. Create a new Excel workbook, insert a VBA module, and...

Using ADO in Microsoft Excel Applications

This section combines the understanding of Excel programming that you've gained from previous chapters with the SQL and ADO techniques discussed so far in this chapter. Excel applications frequently require data from outside sources. The most common of these sources are Access and SQL Server databases. However, I've created applications that required source data from mainframe text file dumps and even Excel workbooks. As you'll see, ADO makes acquiring data from these various data sources easy. To run the code examples shown in the sections that follow, you must set a reference from your Excel project to the ADO 2.5 Object Library. To do this, bring up the References dialog by selecting the Tools O References menu item from within the VBE. Scroll down until you locate the entry labeled Microsoft ActiveX Data Objects 2.5 Library. Place a check mark beside this entry and click OK (see Figure 20-4).

The Excel Startup Folder

When Excel loads, it automatically loads any spreadsheets stored in its startup and alternate startup folders. The default location of the startup folder is usually a subfolder of the main Excel folder named XlStart. By default, there is no alternate startup folder, although one can be defined using the General tab of the Options dialog to open it, select Options from the Tools menu. Because the contents of these folders are opened at startup as ordinary workbooks, their macros are easily accessible to all other Excel workbooks. This makes them ideal as a storage location for macros. The only drawback is that Excel actually opens the spreadsheets stored in these directories to prevent this, they should be hidden by selecting the Hide option from Excel's Window menu (not the Format menu) when the spreadsheet to be hidden is active. Macros that are stored in the startup and alternate startup folders are available from the Macro dialog, and we can assign them to toolbars and menus...

Members that Affect the Current State of Excel

The following members have an effect on the current settings of Excel The CutCopyMode property can return False, indicating that Excel is in neither Cut nor Copy mode, or else one of the two values from the following enum When this property is True (which is its default value), Excel allows editing in cells. Otherwise, it does not allow editing in the cells (but you can still edit in the formula bar). When this property is set to False, Excel will block all input from the keyboard and mouse except for input to dialog boxes that are displayed by code. This will prevent the user from interfering with the currently running macro. The default value of the Interactive property is True. Of course, considerable care must be taken with this property. For instance, if you forget to reset the property to True, or if your code terminates unexpectedly, the user may need to restart Excel. Note that the Alt-F4 key combination will work to shut down Excel, but the user will not be able to save any...

Sending Data to Excel

By far the best way to get numbers, dates, Booleans, and strings into Excel cells is to do so in their native format. Hence, the following code works perfectly, regardless of locale There is a boundary layer between VBA and Excel. When VBA passes a variable through the boundary, Excel does its best to interpret it according to its own rules. If the VBA and Excel data types are mutually compatible, the variable passes straight through unhindered. The problems start when Excel forces you to pass it numbers, dates, or Booleans within strings, or when you choose to do so yourself. The answer to the latter situation is easy don't do it. Whenever you have a string representation of some other data type, if it is possible, always explicitly convert it to the data type you want Excel to store before passing it to Excel. Excel requires string input in the following circumstances In these cases, you have to ensure that the string that VBA sends to Excel is in U.S.-formatted text you must use...

Programmatically Zipping an Excel Container

In the ZipPackage procedure demonstrated here, you are creating an empty .zip file and then filling it with the contents of a source directory. Notice that you are using the Sleep API function here. This lets you pause Excel for a specified number of milliseconds. Pausing Excel allows each file to be completely compressed and saved before moving on the next file. In this procedure, you are making Excel sleep for 500 milliseconds each time you copy a file to the .zip container

Changing Excel menu elements

If you get bored with Excel's standard menu text, you can change it to something else for instance, you can change the Tools menu to the Miscellaneous menu. You can also assign your own macros to built-in menu items. You have many other options for changing menu elements, including rearranging the order of the menus on a menu bar (for example, to make the Help menu appear first instead of last). Moving Up from Excel 5 95 If you've customized menus in Excel 5 or Excel 95, you can pretty much forget everything that you ever learned. Beginning with Excel 97, menu customization has changed significantly in the following respects The Excel 5 95 Menu Editor is gone. To edit a menu manually, you choose the View Toolbars Customize command. Understand, however, that Excel 5 95 workbooks that contain menus customized by using the old Menu Editor still work in Excel 97 and later. However, to make any changes to these modified menus, you must do so in Excel 5 95. Better yet, just remove the menu...

Creating a Vanilla Pivot Table in Excel Interface

Although they are the most powerful feature in Excel, Microsoft estimates that pivot tables are used by only 7 of Excel users overall. Based on surveys at MrExcel.com, about 42 of advanced Excel users have used pivot tables. Because a significant portion of you have never used pivot tables, I will walk through the steps of building a pivot table in the user interface. If you are already a pivot table pro, jump ahead to the next section.

Run A Procedure When Excel Creates A Workbook

You use the NewWorkbook event to determine when Excel has created a new workbook. The NewWorkbook event has one parameter value that passes into the subroutine. The Wb parameter contains the new created workbook. You can access any of the methods and properties of the new workbook to customize the created workbook. For example, you can use the Name property to return the name of the new workbook. See Chapter 9 for more information on working with the Workbook object. RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK (CONTINUED) RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK (CONTINUED) _ In the Workbook_Open subroutine, type Set Test.AppEvent Excel.Application, replacing Test with the variable in step 7 and AppEvent with the variable created in step 3. , Close and reopen Excel. AUTOMATING PROCEDURES WITH EXCEL EVENTS When you open the workbook containing the code that activates the application event, the code continues to execute each time you trigger the event. You may find circumstances...

Using Excel Application Functions in VBA

Now that you know how to write functions in VBA and make them available to your spreadsheets, you are also aware that you can re-create any function already available in the Excel application. Although recreating Excel's functions would be a good way to improve your VBA programming skills, it's certainly not a practical use of your time. Why reinvent what's already been created for you It would be nice if you could use Excel's application functions in your VBA code, as they are mostly complimentary, not repetitive, to VBA's set of functions. That way, if you need a specific function performed in your program that is not already included with VBA, you don't have to write it yourself. Well, there is a method to use the Excel application functions, of course, and it is really quite simple. Replacing the call to the PowerDB() function in the TestPower() sub procedure shown earlier with the line of code above will give the exact same result. The difference is that this code uses Excel's...

Using the cExcel Setup and cData Objects

I mentioned a moment ago that neither of our helper methods made any direct VBA calls. The same is true of the GetManagers method. All of our work is being done by our objects from start to finish. The beauty of this is that we can drop these classes in any Excel project and have this functionality available instantly. In our current Excel project, we can change the database and or SQL statement and import any data we need via the cData object.

The Excel Locale Issue

When you create Excel solutions with VBA, the Excel object model expects English US formatting regardless of the user's locale settings. VSTO mimics this behavior by creating transparent proxies for the objects that always report that the locale is English US (locale ID 1033). Although the Excel object model expects locale ID 1033, it formats the data according to the user's locale settings when the data is displayed in the worksheet. The AssemblyInfo.vb file within your Excel solutions contains an attribute called ExcelLocale1033Attribute. The value of this attribute determines whether VSTO creates and uses the transparent proxies and whether or not it behaves in the same way as VBA. By default, this attribute is set to true, causing the Excel object model to always expect locale ID 1033 (the same behavior you get in VBA). If you change this attribute to false, VSTO does not automatically report the locale as English US instead, the current culture is reported. This can cause errors...

How To Draw In Vba Excel

Vba Excel Drawing

The concept of using XY charts for drawing has been perfected by Mala Singh of XLSoft Consulting in India. Mala has been able to use XY charts to draw just about anything. This blueprint is actually an XY chart in Excel (see Figure 10.21). This blueprint is actually an XY Chart in Excel. It takes about 25 seconds to draw the chart. This blueprint is actually an XY Chart in Excel. It takes about 25 seconds to draw the chart.

Using Excel Templates to Create Formatted Worksheets Filled with Access Data

If you want to produce a more formatted worksheet, you can prepare an Excel template and format it as needed for example, adding a large, centered title and column headings with appropriate text, perhaps in a larger or bolder font than the data area. Then, instead of using the Excel command on the Ribbon, use VBA code to export the Access data row by row to the data area of a new worksheet created from the template. I created a set of queries for archiving data, again using the sample Northwind data, and a dialog form (fdlgArchiveOrders) that allows the user to select a date range for archiving Orders data, as shown in Figure 3.5. Once the start date and end date have been entered or selected, clicking the Archive button runs a procedure that creates a new Excel worksheet from a template (Orders Archive.xltx) in the same folder as the database, fills it with data from tblOrders in the selected date range, and deletes the archived records. Dim appExcel As Excel.Application Dim...

An Introduction To The Excel Object Model

Designed around the ability to access and manipulate objects, VBA has access to an Object Model in each Microsoft Office product, including Excel, that enables you to interact with each application. Using the Object Model, you can access everything from the entire application to an individual cell in a worksheet. EXCEL OBJECTS The Excel Object Model provides nearly 200 different objects and more than 5,000 corresponding properties and methods for use in your VBA code. Each object represents an element of the Excel application. For example, the Application object refers to the entire Excel application, but a Worksheet object refers to an individual worksheet. Most objects have child objects. A child object is an object that is part of a larger object. For example, a Worksheet object is a child object to a Workbook object because worksheets are part of a workbook. All objects in the Excel Object Model are the children of at least one other object, except the Application object. All...

Of Excel Data and Object Orientation

Earlier in this book, I promised that we'd see object-oriented solutions to our coding problems in Excel 2007. Let's take our manager list-creation code and the code that lists a manager's staff, and convert them to classes. Normally, this is the way I would directly approach a solution, but up to this point we've been exploring some of the VBA possibilities in Excel 2007. Open DataAccessSample04.xlsm and save it as DataAccessSample06.xlsm. Open Modulel in the VBE and review the GetManagerList subroutine. We can break its functionality down to just a few items. The problem with that is it's doing a number of unrelated things. It's setting up the worksheet for data import, opening a connection to the database, getting data, putting it on the worksheet, and then formatting and cleaning up the worksheet.

Programmatically Unzipping an Excel Container

The unzip procedure is little more than a series of simple steps that duplicate the manual act of copying files out of an Excel container and saving them into a destination folder. First, load the name of your target Excel file to the TargetFile variable. The NewFileName is defined as the TargetFile string concatenated with the .zip extension. Next, use these variables in a FileCopy statement, essentially copying the target Excel file and saving it with a .zip file extension. This converts the target Excel file to a temporary .zip file while keeping the target file intact. Then create a destination folder and copy each of the XML parts located in the temporary .zip file into the destination folder. Once all XML parts have been copied, delete the temporary .zip file

Opening and Importing XML Documents into an Excel Worksheet

To open an XML document from the Excel application, select File, Open and then choose the desired XML file (.xml file extension) from the Open dialog box. After selecting a file, you will be asked if you want to open the file as an XML list, read-only workbook, or to use the XML Source Task Pane (see Figure 8.3). Typically, you load the data into a worksheet as an Excel list in order to take advantage of the data management features a list provides. If the XML file does not reference an existing schema document (.xsd file extension), Excel will automatically create one (you may be notified of this fact as shown in Figure 8.4) and store it internally with the workbook. You don't have to see the schema, or know how it describes your XML document, but you should know that it's there working in the background defining your data elements for Excel. Microsoft Office Excel X Microsoft Office Excel X When you open an XML file as a list, Excel adds the data to a worksheet and creates a list...

Vba And Excel Object Model Quick Reference

EXCEL OBJECT MODEL CONSTANTS (CONTINUED) Excel 2.0 format. Excel 2.0 format - Far East version. Excel 3.0 format. Excel 4.0 format. Excel 4.0 workbook format. Excel 5.0 format. Excel 97 format. Excel 95 - 97 format. Excel international Add-in. Excel international macro. EXCEL OBJECT MODEL CONSTANTS (CONTINUED) Excel workbook format. Microsoft Excel Workbooks.

Controlling Excel from Word

As you might expect, you can also control Excel from another application (such as another programming language or a Word VBA procedure). For example, you might want to perform some calculations in Excel and return the result to a Word document. You can create any of the following Excel objects with the adjacent functions Application object Workbook object CreateObject(Excel.Sheet) Chart object CreateObject(Excel.Chart) An Excel VBA procedure created this Word document. An Excel VBA procedure created this Word document. The example described in this section is a Word macro that creates an Excel Workbook object (whose moniker is Excel.Sheet) from an existing workbook named projections.xls. The macro prompts the user for two values and then creates a data table and chart, which are stored in the Word document. Recalculating the worksheet updates a chart. The data and the chart are then copied from the Excel object and pasted into a new document. The results are shown in Figure 23-5. The...

Topics in Learning Excel Programming

In general, the education of an Excel programmer breaks down into a few main categories, as follows. First, you need to learn a bit about the environment in which Excel programming is done. This is the so-called Visual Basic Editor or Excel VBA Integrated Development Environment (IDE for short). We take care of this in Chapter 3 and Chapter 4. Next, you need to learn a bit about the basics of the programming language that Excel uses. This language is called Visual Basic for Applications (VBA). Actually, VBA is used not only by Microsoft Excel, but also by the other major components in the Microsoft Office application suite Access, Word, and PowerPoint. Any application that uses VBA in this way is called a host application for VBA. (There are also a number of nonMicrosoft products that use VBA as their underlying programming language. Among the most notable is Visio, a vector-based drawing program.) It is also used by the standalone programming environment called Visual Basic (VB)....

Sending Information from Access to Excel

Access 2007 provides the capability to create forms and reports, which include graphs and tables. However, you may want to leverage some of the powerful Excel features, such as the new Charting and Conditional Formatting features new to Office 2007. Also, users may find it useful to be able to export their data in an Excel spreadsheet. The code samples for working with Excel can be found in the code behind the Export Report Manager form in the sample database. As with Outlook, using Excel features from VBA in an Access database solution requires a VBA reference to the Microsoft Excel 12.0 Object Model in the Visual Basic Editor's References dialog box.

VBA Excel version compatibility

When Microsoft introduced Excel 97, some radical changes to both the language and the developers interface were made. Excel 97 was the first time that Active X components could be embedded with worksheets and user forms. Compatibility with previous versions of VBA is far less likely than with versions released after Excel 97. At the time of writing this book, these include Excel 2000 and Excel XP. The VBA macros written in this book should work with versions of Excel 97 onwards. However, sometimes reference will be made to commands that were developed for Excel 5.

Overriding Excel Commands

Using VBA, it is easy to override a built-in Excel command. All you need to do is to change the OnAction property of an existing button to point to a procedure that has your modification. Of course, you need to do this for the button as well as the menu item. Listing 7.9 shows how you might override the Save button in Excel using VBA. Listing 7.9. Overriding the Save button in Excel with VBA Sub OverrideButton() You cannot do this in VSTO because the OnAction property can point only to an Excel macro, and not to a method in your VSTO code. You could create callbacks into your VSTO code from VBA, as shown earlier in this chapter. An alternative is to use VSTO to write code that replaces the existing built-in button or toolbar with your own. For an example, see Chapter 6. Although the example shows you how to replace menu items and toolbar buttons in Word, the same principle applies to Excel. There are many objects in the Excel object model that you can manipulate from VSTO. However,...

Creating Word and Excel Smart Tags with VSTO

VSTO provides two smart tag classes (Microsoft.Office.Tools.Excel.SmartTag and Microsoft.Office.Tools.Word.SmartTag) that enable you to add smart tags to your document or workbook. You enable Word or Excel to recognize certain terms by using these classes to add strings to the Terms property or to add regular expressions to the Expressions property of the smart tag. You'll learn more about regular expressions later in this chapter in Introduction to Regular Expressions. 1. Create an Excel workbook project with VSTO. 3. Add the code in Listing 9.3 to the code file after the line Public Class Workbook. This code adds a variable that has events for a Microsoft.Office.Tools.Excel.Action. WithEvents AddControlToActionsPane As _ Microsoft.Office.Tools.Excel.Action 4. Add the code in Listing 9.4 to the Startup event handler of the ThisWorkbook class. This code turns on the smart tag feature, creates a variable named ControlSmartTag for an Excel smart tag, and then adds a number of terms to...

Where To Download Hidden Secrets In Microsoft Excel

You can safely download your risk free copy of Hidden Secrets In Microsoft Excel from the special discount link below.

Download Now