Creating A Usage Log

Category: General VBA | [Item URL]

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

Category: General VBA | [Item URL]

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

Category: General VBA | [Item URL]

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

Category: General VBA | [Item URL]

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.

map example

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

NOTE: My Power Utility Pak add-in includes a much more sophisticated version of this utility.

Creating A Splash Screen For An Excel Workbook

Category: Charts & Graphics / General VBA | [Item URL]

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.

  1. Create your workbook as usual.
  2. Activate the Visual Basic Editor and insert a new UserForm into the project. The code here assumes this form is named UserForm1.
  3. 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.
  4. Insert the following subroutine into the code module for the ThisWorkbook object:
    Private Sub Workbook_Open()
    End Sub
  5. Insert the following subroutine into the code module for UserForm1:
    Private Sub UserForm_Activate()
        Application.OnTime Now + TimeValue("00:00:05"), "KillTheForm"
    End Sub  
  6. 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

Category: General VBA | [Item URL]

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

Category: Charts & Graphics / General VBA | [Item URL]

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.

Background Information

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)

Example Usage

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
          MsgBox .XValues
         End If
    End With
End Sub

Chart Trendline Formulas

Category: Formulas / Charts & Graphics | [Item URL]

When you add a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This tip describes how to create formulas that generate the trendline coefficients. You can then use these formulas to calculate predicted y values for give values of x.

These equations assume that your sheet has two named ranges: x and y.

Linear Trendline

Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Logarithmic Trendline

Equation: y = (c * LN(x)) + b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline

Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)

Exponential Trendline

Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)

2nd Order Polynomial Trendline

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline

Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Higher Order Polynomial Trendline

Notice the pattern in the two preceding sets of formulas.

Removing Lines From A Surface Chart

Category: Charts & Graphics / General VBA | [Item URL]

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:

  1. Click the chart legend to select it
  2. Click the first legend key (that is the small colored square)
  3. Right-click and choose Format Legend Key
  4. In the Format Legend Key dialog box, click the Patterns tab
  5. 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.

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.

Determining The User’s Video Resolution

Category: VBA Functions | [Item URL]

How you can determine the current video resolution? There are two ways that I'm aware of:

  1. Maximize Excel's window and then access the Application's Width and Height properties
  2. Use a Windows API function

This document presents VBA code to demonstrate both of these techniques.

Getting Excel's window size

The VBA subroutine below maximizes Excel's window, and then displays the width and height.

Sub ShowAppSize()
'   Maximize the window
    Application.WindowState = xlMaximized

'   Get the dimensions
    appWidth = Application.Width
    appHeight = Application.Height

'   Show a message box
    Msg = "Excel's window size is: "

    Msg = Msg & appWidth & " X " & appHeight
    MsgBox Msg
End Sub

This subroutine is quite straightforward, and works with Excel 5 or later versions. The disadvantage is that Excel's metric system does not correspond to pixels. For example, when the video resolution is 1024 X 768 pixels, the preceding subroutine reports that the maximized window size is 774 X 582.

Using the GetSystemMetrics API function

The subroutine below demonstrates how to use a Windows API function to determine the current video resolution. The result is expressed in pixels.

' API declaration
 Declare Function GetSystemMetrics32 Lib "user32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

 Public Const SM_CXSCREEN = 0
 Public Const SM_CYSCREEN = 1

Sub DisplayVideoInfo()
    vidWidth = GetSystemMetrics32(SM_CXSCREEN)
    vidHeight = GetSystemMetrics32(SM_CYSCREEN)
    Msg = "The current video mode is: "
    Msg = Msg & vidWidth & " X " & vidHeight
    MsgBox Msg
End Sub

Page 6 of 17 pages
[Previous page]   [Next page]

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

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

© Copyright 2018, J-Walk & Associates, Inc.
Privacy Policy