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...

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., _...

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...

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...

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...

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...

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...

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...

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...