Clearing The Advanced Filter Dialog Box
When you choose the Data, Filter, Advanced Filter command, Excel displays its Advanced Filter dialog box. You've probably noticed that Excel "remembers" the previous range specifications for the List range, Criteria range, and Copy to range. In most cases, this is useful. But if you're working with several different worksheet databases, you may prefer that Excel uses the current database rather than the previous database.
There is no direct way to clear the previous settings from the Advanced Filter dialog box. However, you can use the simple VBA procedure listed below.
Sub ShowAdvancedFilterDialog() ' Delete names On Error Resume Next With ActiveWorkbook .Names("_FilterDatabase").Delete .Names("Criteria").Delete .Names("Extract").Delete End With On Error GoTo 0 ' Display the dialog box Application.Dialogs(xlDialogFilterAdvanced).Show End Sub
How it works
Excel keeps track of the previous Advanced Filter range specifications by using three name: _FilterDatabase, Criteria, and Extract. The ShowAdvancedFilterDialog procedure simply deletes these names, and then uses the Show method to display the Advanced Filter dialog box. Because these names are not defined, it's as if you are using the Advanced Filter command for the first time.
Note: The _FilterDatabase name is a hidden name. Therefore, the only way to delete it is by using VBA code. The Criteria and the Extract names are normal names, and can be deleted using the standard Define Name dialog box.
Using the procedures
To use this procedure, copy it and paste it to a VBA module in your workbook.
Then, execute the ShowAdvancedFilterDialog macro instead of the Data, Filter,
Advanced Filter command.
Mail Merge - Without Word
A companion file is available: Click here to download
Mail merge is the process of merging information from a database into a text document, and then printing the document. The result is a series of personalized documents.
Normally, Microsoft Word is used for this type of operation. Word can perform a mail merge with data stored in an Excel workbook. The process works well, but in some cases it may be preferable to eliminate Word and do all of the work with Excel.
Using Excel to perform a mail merge requires:
- A range that contains the data to be merged. In the example, this is on the sheet named Data.
- A range that contains the text. In the example, this is on the sheet named Form.
- A cell that contains the row number of the "current record." In this example, that cell is C3.
- Formulas that use Excel's INDIRECT function to retrieve data from the current record. These formulas are in column L.
- Formulas within the text that refer to the cells in column L. The example uses several such formulas to personalize the letter.
- A simple macro that loops through the data, incrementing the current record number and printing the sheet.
This example, of course, can be adapted to many other purposes.
This example has a few additional features:
- The ability to specify the first and last records from the database
- Buttons to assist in navigating through the workbook
- An option to preview instead of print
- A Help button that displays instructions
Creating A Usage Log
This tip describes how to create a usage log that keeps track who opens a workbook, and at what time.
If a workbook's ThisWorkbook code module contains a procedure named Workbook_Open, that procedure is executed whenever the workbook is opened. Add the following procedure to the ThisWorkbook code module.
Private Sub Workbook_Open() Open ThisWorkbook.Path & "\usage.log" For Append As #1 Print #1, Application.UserName, Now Close #1 End Sub
When this procedure is executed, it appends the user name and the current date/time to a text file. The file is stored in the workbook's directory, and is named usage.log. If the text file does not exist, it is created. You can, of course, change the code so the text file is written to a different directory.
NOTE: Keep in mind that this procedure is not executed if the workbook
is opened with macros disabled.
Determining If Access To The VB Project Is Allowed
As you may know, you can write VBA code that manipulates Visual Basic project components. For example, your code can insert and delete modules, or even create a UserForm on the fly. If your Excel application modifies the Visual Basic project, you need to be aware of a security setting introduced in Excel 2002: Trust access to Visual Basic Project.
This setting is changed in Excel's Security dialog box, accessible via the Tools - Macro - Security command.
By default, this setting is turned off. And, of course, it cannot be changed via code. This means that VBA code that attempts to access the VB project will fail, and the user will be presented with an unfriendly message from Excel.
Unfortunately, there is no direct way to programmatically determine the value of this setting. The only way to do it is to trap an error and then take appropriate action. The VBA code listed below does just that.
' Make sure access to the VBProject is allowed Dim VBP As Object ' as VBProject If Val(Application.Version) >= 10 Then On Error Resume Next Set VBP = ActiveWorkbook.VBProject If Err.Number <> 0 Then MsgBox "Your security settings do not allow this procedure to run." _ & vbCrLf & vbCrLf & "To change your security setting:" _ & vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _ & " 2. Click the 'Trusted Sources' tab" & vbCrLf _ & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _ vbCritical Exit Sub End If End If
This code first checks the Excel version number. If Excel 2002 or later is in use, it attempts to create an object variable (VBP). If an error occurs, then the user is presented with instructions on how to change the setting (see below) and the procedure ends.
Ensuring That Data Validation Is Not Deleted
Excel's Data Validation feature is very useful, but it has a serious flaw: It is easy for a user to accidentally (or intentionally) delete the validation rules. For example, you copy a range of cells and then paste them to a range that contains Data Validation, the Data Validation will be destroyed.
This tip describes a technique to help prevent this.
Naming the range
Set up your Data Validation as usual. Then, select all of the cells that use Data Validation, and name this range ValidationRange. This can be a contiguous range or a non-contiguous range.
Important: Ensure that every cell in the ValidationRange actually contains Data Validation.
The VBA code
The code below use an event procedure, and it must be located in the code module for the worksheet. For example, if your worksheet is named Sheet1, the code will go in the module named Sheet1.
Private Sub Worksheet_Change(ByVal Target As Range) 'Does the validation range still have validation? If HasValidation(Range("ValidationRange")) Then Exit Sub Else Application.Undo MsgBox "Your last operation was canceled." & _ "It would have deleted data validation rules.", vbCritical End If End Sub Private Function HasValidation(r) As Boolean ' Returns True if every cell in Range r uses Data Validation On Error Resume Next x = r.Validation.Type If Err.Number = 0 Then HasValidation = True Else HasValidation = False End Function
How it works
The Worksheet_Change procedure is executed whenever a cell or range is changed on the worksheet. The code calls the HasValidation function to ensure that the range named ValidationRange still contains Data Validation. If every cell in that range contains Data Validation, the function returns True, the procedure ends, and no action is taken.
If one or more cells in the ValidationRange range no longer contain Data Validation, the function returns False. This means that the user has wiped out the Data Validation in one or more cells. In such a case, the last operation is undone, and the user sees the message below.
This procedure handles the following operations, which would normally delete the Data Validation rules:
- Cutting/pasting data
- Copying/pasting data
- The Edit - Clear - All command
- The Edit - Delete command
However, it will not detect deleting entire rows or columns that contain data validation.
Creating A Worksheet Map
In this document I describe how to create a VBA utility that generates a map of the active worksheet. The map is generated on a new worksheet, and it consists of color-coded cells that let you quickly identify values, text, and formulas.
The figure below shows an example of a such a map. Cells that contain text are green, those than contain a numeric value are colored yellow, and cells that contain formulas are colored red. Such a map can help you spot potential errors. For example, if one formula in a block of formulas has been overwritten by a value, that cell will stand out in the map view.
The QuickMap Subroutine
The subroutine that generates the worksheet map is listed below. If you'd like to use this utility, just copy the code and paste it to a VBA module. Then, activate a worksheet and execute the QuickMap subroutine.
Sub QuickMap() If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub ' Create object variables for cell subsets On Error Resume Next Set FormulaCells = Range("A1").SpecialCells _ (xlFormulas, xlNumbers + xlTextValues + xlLogical) Set TextCells = Range("A1").SpecialCells(xlConstants, xlTextValues) Set NumberCells = Range("A1").SpecialCells(xlConstants, xlNumbers) On Error GoTo 0 ' Add a new sheet and format it Sheets.Add With Cells .ColumnWidth = 2 .Font.Size = 8 .HorizontalAlignment = xlCenter End With Application.ScreenUpdating = False ' Do the formula cells If Not IsEmpty(FormulaCells) Then For Each Area In FormulaCells.Areas With ActiveSheet.Range(Area.Address) .Value = "F" .Interior.ColorIndex = 3 End With Next Area End If ' Do the text cells If Not IsEmpty(TextCells) Then For Each Area In TextCells.Areas With ActiveSheet.Range(Area.Address) .Value = "T" .Interior.ColorIndex = 4 End With Next Area End If ' Do the numeric cells If Not IsEmpty(NumberCells) Then For Each Area In NumberCells.Areas With ActiveSheet.Range(Area.Address) .Value = "N" .Interior.ColorIndex = 6 End With Next Area End If End Sub
How it Works
The subroutine first checks to make sure the active sheet is a worksheet. If not, there's a quick exit with no further action. Next, it creates three object variables by using the SpecialCells method to identify the various cell types. The SpecialCells method is very useful. If you're not acquainted with it, I urge you to check it out in Excel's online help file. Notice the use of On Error Resume Next. This is to avoid the error that occurs if no cells qualify -- for example, if the worksheet has no formulas.
Next, the subroutine adds a new worksheet, reduces the cell width, and sets the horizontal alignment to center. This step is just cosmetic. The sub then turns off screen updating to speed things up a bit.
The next three blocks of code process the cells. If no cell qualify, the object variable is Empty, so the sub tests for this. Then, the routine loops through each Area in the Range object and formats the cell. You can easily customize this part of the subroutine to apply different formatting.
Power Utility Pak add-in includes a much more sophisticated version of this
Creating A Splash Screen For An Excel Workbook
This tip describes how to create a splash screen for a Excel workbook. A splash screen appears when the workbook is opened and disappears automatically after a specific time has elapsed.
Setting it up
Follow these instructions to create a splash screen for your project.
- Create your workbook as usual.
- Activate the Visual Basic Editor and insert a new UserForm into the project. The code here assumes this form is named UserForm1.
- Place any controls you like on UserForm1. For example, you may want to insert an Image control that has your company's logo. Also, you may want to set the UserForm's Caption property to an empty string.
- Insert the following subroutine into the code module for the ThisWorkbook
Private Sub Workbook_Open() UserForm1.Show End Sub
- Insert the following subroutine into the code module for UserForm1:
Private Sub UserForm_Activate() Application.OnTime Now + TimeValue("00:00:05"), "KillTheForm" End Sub
- Insert the following subroutine into a normal VBA module:
Private Sub KillTheForm() Unload UserForm1 End Sub
How it works
When the workbook is opened, the Workbook_Open subroutine is executed. This subroutine displays the UserForm. When the UserForm is displayed, it's Activate event occurs - which triggers the UserForm_Activate subroutine. This subroutine uses the OnTime method of the Application object to execute a subroutine (named KillTheForm) at a particular time. In this case, the time is five seconds from the current time (change this interval by modifying the argument for the TimeValue function). The KillTheForm subroutine simply unloads the UserForm.
Playing Sound From Excel
Excel developers can sometimes benefit by including sound in their application. For example, you can use a sound to provide audible feedback when a specific cell exceeds a particular value. Or, you just might like to add some simple sound effects to spice things up a bit.
You can play sounds by using a simple Windows API call and some VBA code.
Are Sounds Supported?
Not all systems support sound. If you need to determine if sounds are supported, use the CanPlaySounds method. Here's an example:
If Not Application.CanPlaySounds Then MsgBox "Sorry, sound is not supported on your system." Exit Sub End If
Example: Playing a WAV File
The example below contains the API function declaration, plus a simple subroutine to play a sound file called dogbark.wav, which is assumed to be in the same directory as the workbook.
Private Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long Const SND_SYNC = &H0 Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000 Sub PlayWAV() WAVFile = "dogbark.wav" WAVFile = ThisWorkbook.Path & "\" & WAVFile Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) End Sub
In the example above, the WAV file is played asynchronously. This means execution continues while the sound is playing. If you would like code execution to stop while the sound is playing, use this statement:
Call PlaySound(WAVFile, 0&, SND_SYNC Or SND_FILENAME)
Example: Playing a MIDI File
If the sound file is a MIDI file, you'll need to use a different API call. The PlayMIDI subroutine starts playing a MIDI file. Executing the StopMIDI subroutine will stop playing the MIDI file.
Private Declare Function mciExecute Lib "winmm.dll" _ (ByVal lpstrCommand As String) As Long
Sub PlayMIDI() MIDIFile = "xfiles.mid" MIDIFile = ThisWorkbook.Path & "\" & MIDIFile mciExecute ("play " & MIDIFile) End Sub Sub StopMIDI() MIDIFile = "xfiles.mid" MIDIFile = ThisWorkbook.Path & "\" & MIDIFile mciExecute ("stop " & MIDIFile) End Sub
A Class Module To Manipulate A Chart Series
A companion file is available: Click here to download
Excel's object model has a serious flaw: There is no direct way to to use VBA to determine the ranges used in a chart. This tip contains a useful class module that can simplify the task of manipulating chart's using VBA.
The Series object is contained in a Chart object. The SeriesCollection is a collection of Series objects for a particular Chart object. If a chart plots two data series it will have two Series objects. You can refer to a particular Series object by its index number. The expression below, for example, creates an object variable that represents the first Series object in the active chart:
Set MySeries = ActiveChart.SeriesCollection(1)
A Series object has many properties, but I'll list three that seem relevant to this discussion:
- Formula property: Returns or sets the SERIES formula for the Series. When you select a series in a chart, its SERIES formula is displayed in the formula bar. The Formula property returns this formula as a string.
- Values property: Returns or sets a collection of all the values in the series. This can be a range on a worksheet or an array of constant values, but not a combination of both.
- XValues property: Returns or sets an array of x values for a chart series. The XValues property can be set to a range on a worksheet or to an array of values -- but it can't be a combination of both.
If your VBA code needs to determine the data range used by a particular chart series, it's obvious that the Values property of the Series object is just the ticket. And, you can use the XValues property to get the range that contains the x values (or category labels). In theory, that certainly seems correct -- but in practice, it doesn't work. You'll find that he XValues and Values properties return a variant array. Unfortunately, there is no direct way to get a Range object for a Series object.
Note: When you set the Values property for a Series object, you can specify a Range object or an array. But when you read this property, it is always an array. Go figure.
About a Chart's SERIES formula
Every data series in a chart has a SERIES formula that determines the data used in the series. Here's an example of a SERIES formula for a chart series:
A SERIES formula is comprised of four arguments:
- Series Name. Can be a cell reference or a literal string. Optional.
- XValues. Can be a range reference (including a non-contiguous range reference) or an array. Optional.
- Values. Can be a range reference (including a non-contiguous range reference) or an array.
- Plot Order. Must be an integer.
The ChartSeries calls module, described below, essentially parses and analyzes a chart's SERIES formula.
The ChartSeries Class Module
I created a class module named ChartSeries. When this class module is included in a workbook, your VBA code can create a new "ChartSeries" object and manipulate the following properties of this object:
- Chart (read/write)
- ChartSeries (read/write)
- SeriesName (read/write)
- XValues (read/write)
- Values (read/write)
- PlotOrder (read/write)
- SeriesNameType (read-only)
- XValuesType (read-only)
- ValuesType (read-only)
- PlotOrderType (read-only)
The simple procedure below demonstrate how to use the ChartSeries class. It starts by creating a new object called ChartSeries. It sets the Chart property to an embedded chart, and sets the ChartSeries property to 1. It then uses the XValuesType property to determine the "type" of the XValues in the chart. If the chart uses a range, it displays the address of the range. If the chart uses something other than a range (that is, a literal array), it displays the array.
Sub ExampleUsage() Dim MySeries As New ChartSeries With MySeries Chart = Sheets(1).ChartObjects(1).Chart ChartSeries = 1 If .XValuesType = "Range" Then MsgBox .XValues.Address Else MsgBox .XValues End If End With End Sub
Removing Lines From A Surface Chart
By default, Excel's surface charts display black lines between each color gradient. It's possible to remove those lines, but Excel makes you jump through quite a few hoops to do so.
Each line must be removed separately! Here's how to do it:
- Click the chart legend to select it
- Click the first legend key (that is the small colored square)
- Right-click and choose Format Legend Key
- In the Format Legend Key dialog box, click the Patterns tab
- Select None for the Border option, and click OK
Repeat those steps for every series in your chart.
As with most tedious operations in Excel, you can automate this with a VBA macro:
Sub RemoveSurfaceChartLines() Dim LE As LegendEntry If ActiveChart Is Nothing Then Exit Sub If ActiveChart.ChartType <> xlSurface Then Exit Sub If MsgBox("Remove lines from surface chart?", vbYesNo) = vbYes Then Application.ScreenUpdating = False ActiveChart.HasLegend = True For Each LE In ActiveChart.Legend.LegendEntries LE.LegendKey.Border.LineStyle = xlNone Next LE ActiveChart.ChartGroups(1).Has3DShading = True End If End Sub
To use this macro, copy it and paste it to a VBA module. Then, select a surface chart and press Alt+F8 to display the Macro dialog box. Execute the RemoveSurfaceChartLines macro, and your chart will be transformed, as shown below. Note that the final statement in this procedure turns on the chart's 3D shading option (which gives the chart a much nicer appearance.
SURFACE CHART TIP:
The number of colored gradients in a surface chart is determined by the Major Unit setting for the vertical axis. Double-click the vertical axis to display the Format Axis dialog box. Select the Scale tab and change the value for the Major Unit. The larger the number, the fewer the colors.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks