Playing MP3 Files From Excel

Category: General VBA | [Item URL]

If you create a hyperlink in a cell that points to an MP3 file, clicking the hyperlink plays the audio file in your default MP3 program. But there's a big problem: You will get at least one (probably two) annoying security messages. Microsoft has described a way to disable these messages, but it's a complicated procedure.

Here's an alternative approach that uses a simple VBA macro. The macro assumes the active cell contains a compete filepec for an MP3 file (that is, the full path and the filename).

Sub PlayMP3()
   Application.ScreenUpdating = False
   On Error Resume Next
   ActiveSheet.OLEObjects.Add(Filename:=ActiveCell.Text, Link:=True).Select
   If Err.Number <> 0 Then
     Application.ScreenUpdating = True
     MsgBox "Could not play " & ActiveCell.Text
     Exit Sub
   End If
   Selection.Verb
   Selection.Delete
End Sub

The PlayMP3 procedure creates an OLE object that links to the file in the active cell. The Verb method loads the linked file into the default MP3 player, and then the OLE object is deleted. If the active cell contains something other than a valid path to an MP3 file, a message is displayed and nothing happens. It's fast, and you'll never see a security warning message. 

By the way, this VBA macro also works with image files. If the active cell contains a path to an image file, the file is displayed in your default image viewing software.

User-Defined Function Argument Descriptions In Excel 2010

Category: VBA Functions | [Item URL]

One of the new features in Excel 2010 is the ability to provide argument descriptions for user-defined functions. These descriptions appear in Function Arguments dialog box -- which is displayed after you choose a function using the Insert Function dialog box.

Here's a simple (but very useful) user-defined function:

Function EXTRACTELEMENT(Txt, n, Separator) As String
     EXTRACTELEMENT = Split(Application.Trim(Txt), Separator)(n - 1)
End Function

Here's a VBA macro that provides a description for the EXTRACTELEMENT function, assigns it to a function category, and provides a description for each of its three arguments:

Sub DescribeFunction()
   Dim FuncName As String
   Dim FuncDesc As String
   Dim Category As String
   Dim ArgDesc(1 To 3) As String

   FuncName = "EXTRACTELEMENT"
   FuncDesc = "Returns the nth element of a string that uses a separator character"
   Category = 7 'Text category
   ArgDesc(1) = "String that contains the elements"
   ArgDesc(2) = "Element number to return"
   ArgDesc(3) = "Single-character element separator"

   Application.MacroOptions _
      Macro:=FuncName, _
      Description:=FuncDesc, _
      Category:=Category, _
      ArgumentDescriptions:=ArgDesc
End Sub

You need to run this macro only one time. After doing so, the descriptive information is stored in the workbook (or add-in) that defines the function.

Here's how the function appears in the Function Arguments dialog box:

What about compatibility with earlier versions?

If the file is opened in Excel 2007, the argument descriptions are not displayed. If you save the workbook as an XLS file, the Compatibility Checker kicks in and tells you that the function descriptions will be removed.

Dual Monitors And UserForms

Category: General VBA / UserForms | [Item URL]

 A UserForm object has a property named StartUpPosition. By default, this property is set to 1 (CenterOwner), which means that it should appear in the center of Excel's window.

This works fine, unless you have a dual-monitor system. In such a case, the UserForm is not centered in the Excel window. On my system, UserForms always display on the left side of the secondary monitor, even if Excel is maximized on the primary monitor.

To force an Excel UserForm to be centered in Excel's window, use this code to display the UserForm:

With UserForm1
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With

I was hoping this would be fixed in Excel 2010, but nothing has changed. I'm sure most of the Microsoft programmers use at least two monitors, so they are obviously aware of the problem.

Unlinking A Pivot Table From Its Source Data

Category: General / Formatting | [Item URL]

You may have a situation in which you need to send someone a pivot table summary report, but you don't want to include the original data. In other words, you want to "unlink" the pivot table from its data source.

Here's a nicely formatted pivot table in Excel 2010:

Excel doesn't have a command to unlink a pivot table, but it does have a flexible Paste Special command. Using that command, with the Value option, should do the job:

1. Select the pivot table cells and press Ctrl+C to copy the range.
2. Display the Paste Special dialog box. Pressing Alt+ES is my favorite method, and it works for all versions.
3. In the Paste Special dialog box, choose the Values option, and click OK.

The pivot table is unlinked, but if you use Excel 2007 or Excel 2010, the fancy pivot table style formatting is gone:

To get the formatting back, you need to perform two additional steps:

1. Display the Office Clipboard. In Excel 2007 and 2010, click the dialog box launcher icon in the bottom right corner of the Home - Clipboard group.
2. With the unlinked pivot selected, click the item on the Office Clipboard that corresponds to the pivot table copy operation. It will be the last item, unless you copied something else.

Now the pivot table is unlinked from its data source, yet retains all of its original formatting.

By the way, this is actually the first time I've ever done something useful with the Office Clipboard. It's actually fairly useless in Excel because it doesn't hold formulas -- just the values returned by formulas.

Old-Style Menus In Excel 2007

Category: General / CommandBars & Menus | [Item URL]

Here's a quick way to display the old-style menu in Excel 2007 or later. Execute this VBA procedure, and you'll get a toolbar that contains the old Excel 2003 menu commands.

Sub MakeOldMenus()
   Dim cb As CommandBar
   Dim cbc As CommandBarControl
   Dim OldMenu As CommandBar

'  Delete it, if it exists
   On Error Resume Next
   Application.CommandBars("Old Menus").Delete
   On Error GoTo 0

'  Create an old-style toolbar
'  Set the last argument to False for a more compact menu
   Set OldMenu = Application.CommandBars.Add("Old Menus", , True)

'  Copy the controls from Excel's "Built-in Menus" shortcut menu
   With CommandBars("Built-in Menus")
     .Controls("&File").Copy OldMenu
     .Controls("&Edit").Copy OldMenu
     .Controls("&View").Copy OldMenu
     .Controls("&Insert").Copy OldMenu
     .Controls("F&ormat").Copy OldMenu
     .Controls("&Tools").Copy OldMenu
     .Controls("&Data").Copy OldMenu
     .Controls("&Window").Copy OldMenu
     .Controls("&Help").Copy OldMenu
   End With

'   Make it visible. It appears in the Add-Ins tab
   Application.CommandBars("Old Menus").Visible = True
End Sub

After you execute this macro, the new menu appears in the Add-Ins tab:

The menu isn't perfect. A few of the command don't work, and the list of recent files in the File menu just shows placeholders.

Text Effects In Text Boxes

Category: Formatting | [Item URL]

If you use Excel 2007 or later, you may not have discovered the "special" Font dialog box that's available when you're working with a Text Box or text in a Shape.

Add a text box and some text, select the text, right-click, and choose Font. (or press Ctrl+1). Excel displays this Font dialog box that is unlike its other Font dialog boxes.

It has quite a few options that aren't normally available -- and even more are in the Character Spacing tab. Here's an example of some unusual text effects.

Is A Particular Word Contained In A Text String?

Category: Formulas / General VBA | [Item URL]

Here's a VBA function that might be useful in some situations.

The ExactWordInString functions returns True if a specified word is contained in a text string.

You might think that this function is just a variation on Excel's FIND function or VBA's Instr function. There's a subtle difference. The ExactWordInString function looks for a complete word -- not text that might be part of a different word.

The examples in the accompanying figure should clarify how this function works. Cell C2 contains this formula, which was copied to the cells below:

=ExactWordInString(A2,B2)

The function identifies the complete word trapped, but not the word trap, which is part of trapped. Also, note that a space is not required after a word in order to identify it as a word. For example, the word can be followed by a punctuation mark.

The function, listed below, modified the first argument (Text) and replaces all non-alpha characters with a space character. It then adds a leading and trailing space to both arguments. Finally, it uses the Instr function to determine if the modified Word argument is present in the modified Text argument.

To use this function in a formula, just copy and paste it to a VBA module in your workbook.

Function ExactWordInString(Text As String, Word As String) As Boolean
'   Returns TRUE if Word is contained in Text as an exact word match
    Dim i As Long
    Const Space As String = " "
    Text = UCase(Text)
'   Replace non-text characters with a space
    For i = 0 To 64
        Text = Replace(Text, Chr(i), Space)
    Next i
    For i = 91 To 255
        Text = Replace(Text, Chr(i), Space)
    Next i
'   Add initial and final space to Text & Word
    Text = Space & Text & Space
    Word = UCase(Space & Word & Space)
    ExactWordInString = InStr(Text, Word) <> 0
End Function

* Update *

Excel MVP Rick Rothstein sent me a much simpler function that produces the same result. In fact, it uses just one statement:

Function ExactWordInString(Text As String, Word As String) As Boolean
  ExactWordInString = " " & UCase(Text) & " " Like "*[!A-Z]" & UCase(Word) & "[!A-Z]*"
End Function

The Value, Formula, and Text Properties

Category: General VBA | [Item URL]

Student asks teacher. Teacher asks me:

I got an interesting question from a student in my VBA class today, one I'd never considered. I taught them that if you want to enter a formula in a cell, you use a statement like this:

Range("C1").Formula = "=SUM(A1:B1)"

Of course, that works. But the following appears to do the same thing:

Range("C1").Value = "=SUM(A1:B1)"

So the question is, Why do we need the Formula property at all?

These tw0 different properties exist because they can return different information when your code reads them.

• The Formula property returns the formula, if the cell has one. If the cell does not contain a formula, it returns the value in the cell. In the example above, the Formula property returns =(B3-B2)/B2 for cell B4.
• The Value property returns the raw, unformatted value in the cell. In the example, the Value property returns 0.213756613756614 for cell B4.

In addition, VBA provides the (read-only) Text property:

• The Text property returns the text that is displayed in the cell. If the cell contains a numeric value, this property includes all of the formatting. In the example, the Text property returns 21% (a text string) for cell B4.

Yet another relevant property:

• The Value2 property is just like the value property, except that it doesn't use the Date and Currency data types. Rather, this property converts Date and Currency data types Variants containing Doubles. If cell A1 contains the date 2/6/2009, the Value property returns it as a Date, while the Value2 property returns it as a double (i.e., 39850).

Getting A List Of Files Names - Another Method

Category: General | [Item URL]

In a previous tip, I described method of putting a list of file names from a directory into an Excel range. Today I discovered another method that's much simpler. It uses the File protocol in a Web browser.

• Note: This method does not work with Internet Explorer. I tested it with Firefox and Chrome, and it works fine with those browsers.

It's pretty simple:

1. Enter the directory path in your browser's address bar. For example, g:\music.
   
   The browser will convert this path into a URI (Uniform Resource Identifier). For example, g:\music is converted to file:///g:/music/. In addition, the files in that directory will be displayed as hyperlinks, and you can traverse the directory by clicking on directory names. Here's how the file listing appears in Firefox:
   
   
    
2. Press Ctrl+A to select all of the text, and then Ctrl+C to copy it.
    
3. Activate Excel, select a cell, and press Ctrl+V to paste.
   
   The filenames will be pasted as hyperlinks with wrapped text. You might prefer to use Paste Special (with the Text option) rather than Ctrl+V. You'll get a three-column range like this (copied from Firefox):
   
   

Google's Chrome browser is actually better for this task because it doesn't precede the file names with "File:" and it puts the date and time in a single cell.

This technique works with a single directory. If you need to get a list of files in multiple directories, you must repeat the procedure for each directory.

For more flexibility, use the Filename Lister utility in my PUP add-in.

Clearing The Text To Columns Parameters

Category: General / General VBA | [Item URL]

Have you ever imported a CSV file, or pasted data into a worksheet, only to find that Excel split up your data incorrectly? If so, the culprit is probably the Text To Columns feature. Here's Step 2 of the wizard that's used to split a single column of delimited data into multiple columns.

In this case, three delimiters are specified: tab, comma, and colon.

This is a very useful feature, and I use it a lot. The problem is, Excel tries to be helpful by remembering these settings for subsequent CSV imports and paste operations. Sometimes remembering these settings really is helpful, but often, it's not. To clear these delimiters, you must display this dialog box, clear the settings, and click Cancel.

If you're importing or pasting via a macro, there's no direct way for your macro to check these settings or reset them. The solution is to "fake" a text-to-columns operation. The procedure below does that, with the effect of clearing all of the settings from the Text To Columns dialog box (and making no changes to your workbook).

Sub ClearTextToColumns()
    On Error Resume Next
    If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
    Range("A1").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        OtherChar:=""
    If Range("A1") = "XYZZY" Then Range("A1") = ""
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

This macro assumes that a worksheet is active, and it's not protected. Note that the contents of cell A1 will not be modified because no operations are specified for the TextToColumns method.

If cell A1 is empty, the code inserts a temporary string (because the TextToColumns method will fail if the cell is empty). Before ending, the procedure deletes the temporary string.

Page 1 of 19 pages
[Next page]