Developing Custom Functions in Excel

You can't find too many worksheets that are devoid of formulas because you need formulas to figure out new values based on your existing data. Microsoft includes a wealth of standard formulas in Excel. In fact, it's possible that you'll never need anything more than the Microsoft formulas. However, formulas are extremely important, so it's handy to know how to create one of your own. All formulas in Excel rely on functions. If you want to create a special...

Manipulating Toolbars and Menus

Office 2007 now has two interfaces, which means that Microsoft has made it more difficult for users of all types. The toolbar-and-menu interface used by previous versions of Office appears in products such as Visio and Outlook, and the new Ribbon interface appears in the core office applications, such as Word and Excel. This section of the chapter works mainly with the older toolbar and menu interface. The Working with the New Ribbon Interface section, later in this chapter, describes the newer...

Listing Hiding the Edito Cut Menu Option

Public Sub HideAndShowEditCutMenu() ' Create the menu command bar. Dim TopMenu As CommandBar Set TopMenu _ Menu Bar) Dim EditControl As CommandBarControl Set EditControl TopMenu.Controls(Edit) ' Use the control to access the command bar. Dim EditMenu As CommandBar Set EditMenu _ Set EditCut EditMenu.Controls(Cut) ' Change the visible state according to the current ' setting. EditCut.Visible Not EditCut.Visible ' Display the current setting. If EditCut.Visible Then MsgBox The Cut option is...

Using numbers for calculations

Numbers form the basis for a lot of the information computers store. You use numbers to perform tasks in a spreadsheet, to express quantities in a database, and to show the current page in a document. Programs also use numbers to count things such as loops, to determine the position of items such as characters in a string, and to check the truth value of a statement. Finally, VBA uses numbers in myriad ways, such as determining which character to display onscreen or how to interact with your...

Using the Windows collection

Excel records one Window object in the Windows collection for every file that you open. Consequently, the Windows collection doesn't tell you much about the data except at a very high level. For example, you could use the Window object to determine the names of files that you have opened. The Window object can also determine the active Sheet object (see the section Using the Sheets collection, earlier in this chapter, for details) and tell you about general settings, such as whether Excel...

Listing Moving Notes from Word to Excel

Public Sheet As Integer Public Row As String Public Column As String ' Go to the beginning of the document. .GoTo What wdGoToLine, Which wdGoToFirst .EndOf Unit wdSection, Extend wdExtend ' Get the text. NoteText .Text End With ' Create a file dialog. Dim GetFile As FileDialog Set GetFile GetFile.AllowMultiSelect False GetFile.Filters.Clear GetFile.Filters.Add Excel Files, *.XL* Set TheBook Excel.Workbooks.Open(Filename) ' Request the sheet and cell number for the note. NoteSelect.Show...

Listing Using the Registry to Work with Objects

Dim AObj As InlineShape ' Holds the BMP file class. Dim BMPClass As String ' Holds picture statistical data. Dim Output As String ' Holds the Registry key reference. Dim RegKeyRef As Long ' Holds the length of the Registry data. Dim RegLength As Long ' Get the BMP file class. ' Open the Registry key. RegOpenKey ROOT_KEYS.HKEY_CLASSES_ROOT, _ .bmp, RegKeyRef ' Determine whether the key exists. If RegKeyRef 0 Then MsgBox Couldn't open BMP file Registry setting., _...

Making a Choice by Using the Select Case Statement

You can use the If Then Else or If Then ElseIf statement to meet all decision-making needs. However, using these statements can quickly make your code hard to read when you need to make a lot of decisions in rapid succession. Using these statements is required when you want to perform complex expression checking. VBA provides the Select Case statement as an easier-to-read choice when making a single selection from a list of choices. If you know that a variable contains one of several choices...

Using the Select Case statement

The Select Case structure begins with the Select Case statement and ends with an End Case statement. You provide a variable that the Select Case statement can use for selection. Within the Select Case structure are Case clauses, or values that the Select Case structure uses for comparison. When the value of a clause matches the value of the input variable, the Select Case structure performs all tasks required by that clause. In this section, I use examples of the Select Case statement to make a...

Using the Sheets collection

The Sheets collection is the easiest method for accessing worksheets in many situations. You don't have to drill down through the Excel object hierarchy to find the worksheet that you want. However, accessing the worksheets at the top of the hierarchy means that you don't have the objects that exist at lower levels available either, so this technique is a tradeoff. You can use the Sheets collection to access all kinds of sheets, not just worksheets. Any standalone Chart objects also appear in...

Writing Your First

Most Microsoft Office products provide a Properties dialog box, similar to the one shown in Figure 3-10, that contains a Summary tab for documents. You can also find a variation of this Properties dialog box for most third-party products. The Summary tab can provide a lot of valuable information for your programs. You can find out basic statistics, such as the author's name and the company that created the document. The document information also includes statistics, such as the number of words...

Listing Designing a Rotating Chart Presentation

Set EmbeddedChart Sheet2.ChartObjects 1 .Chart ' Rotate between chart types. Change the title as ' needed to match the chart type. Select Case .ChartType Case XlChartType.xlPie .ChartType xlArea .ChartTitle.Caption More Data Case XlChartType.xlArea .ChartType xlLine .ChartTitle.Caption More Data Case XlChartType.xlLine .ChartType xlColumnClustered .ChartTitle.Caption More Data Case XlChartType.xlColumnClustered .ChartType xlPie .ChartTitle.Caption More Data End Select The code begins by...

Presenting data in a pleasing format

Most programs can use the default formatting that VBA provides for output. However, you might need some way to present the data in another way, such as a short or long date 04 24 06 or 24 April 2006 . It's important to know how to format data so that you can create astounding reports and concise analysis. The Format function is the most common way to change the appearance of data. This function accepts any valid expression as input. You can also supply an expression that defines how to format...

Using date and time Values

Tracking time and date in your program can be important. Client contact entries in Access usually require the date that the client was last contacted. A spreadsheet might require dates for each entry in a ledger. It's helpful to include dates in Word documents so that you know the last time that someone accessed or changed it. You might need to know how long a task takes or have an indicator of when time has elapsed. Both date and time variables rely on the Date data type. This data type...

Using currency Values for money calculations

Money usually requires special handling on a computer because you don't want to introduce rounding or other errors. Even small incremental errors can result in large errors if they accumulate over time. The Currency data type provides special handling for money calculations but at a slight performance hit because the Currency data type requires additional memory and processing cycles. Along with the Currency data type, VBA provides a number of special functions for calculating common monetary...

Using the CurrentDB and related objects

The latest version of Access has new and interesting ways to perform a task. The CurrentDB object originally appeared in Office 2000 as an updated version of the DBEngine.Workspaces 0 .Databases 0 object. You should use the CurrentDB object, whenever possible, to perform actual database work because this object includes a few new features that make it a better fit for multi-user environments. However, when you want to use multiple workspaces, you still need to use the DBEngine.Workspaces...

Listing Creating an Excel Chart

Public Sub BuildChart ' Create a new chart. Dim NewChart As Chart Set NewChart ' Change the name. NewChart.Name Added Chart ' Create a series for the chart. Dim TheSeries As Series NewChart.SeriesCollection.Add _ Source Worksheets My Data Set TheSeries NewChart.SeriesCollection l ' Change the chart type. TheSeries.ChartType xl3DPie TheSeries.Name Data from My Data Sheet ' Perform some data formatting. With TheSeries .HasDataLabels True .DataLabels.ShowValue True .DataLabels.Font.Italic True...

Listing Accessing the Registry with VBA

' This Windows API function opens a Registry key. Public Declare Function RegOpenKey _ Lib advapi3 2.dll _ Alias RegOpenKeyA ByVal HKey As Long, _ ByVal lpSubKey As String, _ phkResult As Long As Boolean ' Use this enumeration for the top-level keys. Public Enum ROOT_KEYS HKEY_CLASSES_ROOT amp H80000000 HKEY_CURRENT_USER amp H80000001 HKEY_LOCAL_MACHINE amp H800 000 02 HKEY_USERS amp H800 00 003 HKEY_PERFORMANCE_DATA amp H80000004 HKEY_CURRENT_CONFIG amp H80000005 HKEY_DYN_DATA amp H80000006...

Using Project Explorer

Project Explorer appears in the Project Explorer window. You use it to interact with the objects that make up a project. A project is an individual file used to hold your program, or at least pieces of it. The project resides within the Office document that you're using, so when you open the Office document, you also open the project. See Chapter 3 for a description of how projects and programs interact. Project Explorer works much like how the left pane of Windows Explorer does. Normally, you...

Listing Getting Word Document Information

' Create a variable to hold the individual documents. Dim MyDocs As Document Create a variable to hold information about selected documents. ' Look at each document in the Documents collection. For Each MyDocs In Application.Documents ' Check for the appropriate template name. If UCase MyDocs.AttachedTemplate _ NORMAL.DOT Then ' Create a list of information about the ' document. With MyDocs Output Output Name vbTab vbTab _ .Name vbCrLf _ Window Caption vbTab _ .ActiveWindow.Caption vbCrLf _...

Adding a tab group and button

One of the tasks that you commonly perform is creating your own tabs on the Ribbon. However, just creating a tab isn't enough you must also create a group and a control of some sort. Figure 12-3 shows a typical setup in the Custom UI Editor. The following steps describe the setup for this example 1. Download and install the Custom UI Editor on your system by using the information in the section Obtaining and using the Office 2007 Custom UI Editor, earlier in this chapter. 2. Create a file to...

Comparing WordML with Saved XML

When you save a Word document either manually or programmatically, you can save it as an XML document. However, not all XML documents are created equal. Figure 11-1 shows a Word Save As dialog box set up to save a document as XML. Save your document as XML by using the Save As dialog box. When you save a Word document either manually or programmatically, you can save it as an XML document. However, not all XML documents are created equal. Figure 11-1 shows a Word Save As dialog box set up to...

Using the With Statement

VBA does provide an interesting feature that makes it easier to write code for an object. The With statement tells VBA that you plan to perform a number of tasks by using the same object. Every dotted statement within the structure applies to that object. Using this technique reduces the amount of code that you have to type and can reduce the chance of typos. Listing 8-8 shows an example of the With statement in use. Listing 8-8 Using an Alternative Object-Testing Technique .Title Special...

Obtaining and using the Office Custom UI Editor

The official method for modifying the Ribbon has you changing the file extension, extracting the required files, making modifications in an editor, archiving the files again, and, finally, changing the file extension back every time you want to make any change at all. You can see this grueling and error-prone method at http msdn2.microsoft.com en-us library ms40 6046.aspx. The better way to make changes is to rely on a utility named Office 2007 Custom UI Editor, or Custom UI Editor, for aspx ....

Adding keyed data to the collection

You can normally create collections without keys, and they work fine. A collection that relies on user input is an exception. It's easier to get string input from users than to ask them to count down a row of entries to provide a number. Database collections provide opportunities to use keyed entries. In fact, many predefined collections use keyed entries to make it easier for you to develop programs with them. This example shows an Access contact database. It uses keys to make finding an entry...

Choosing options with list boxes and combo boxes

You use OptionButton controls when you want to create a static list one that doesn't ever change. The ListBox and ComboBox controls help you create dynamic lists lists that can change while the program runs or from session to session. The list of options doesn't have to change, but knowing that you have the option is helpful. Both controls require that you populate fill them with information. The easiest method is to use the AddItem method. Use this method when you populate the control with the...

Understanding the array types

You can classify arrays in several ways. The first method is by the kind of data that the array holds. A String array is different from an Integer array. An array always keeps the array data type unique. Using a Variant data type lets you mix data types within an array. You should use this technique carefully because it can lead to bugs that are difficult to debug. A second method is to define the number of array dimensions. A dimension is the number of directions in which the array holds...

Automating documents

I hate writing letters, especially if the letter contains most of the same information that I wrote for the last letter. Sometimes you can automate letters by using mail merge, but that generally doesn't work too well for individualized letters. In these situations, I set up a form that contains the common information that I include in some letters but not in others. I check off the items that I need for the current letter, and VBA automatically writes it for me. You can see my automated letter...

Copying data from one array to another

You might need to copy data from one array to another. For example, you can base a new array on the content of an existing array. It's also safer to make changes to a copy of an array rather than to change the original and potentially damage the data. Listing 9-4 shows an example of code that you can use to copy one array to another. You can find the source code for this example on the Dummies.com site at http www.dummies.com go vbafd5e. 212 Part III Expanding Your VBA Horizons _ ' Create a...