Making An Exact Copy Of A Range Of Formulas, Take 2

Category: General / Formulas | [Item URL]

When you copy a range of formulas and paste them to a new location, Excel adjusts the cell references automatically. Most of the time, this is exactly what you want. Consider this simple formula:

=SUM(A2:A13)

If you copy this formula and paste it to the next column, the references are adjusted and the pasted formula is:

=SUM(B2:B13)

Making an exact copy of a single formula is easy: Press F2, highlight the formula, and press Ctrl+C to copy it as text. Then paste it to another cell. In some situations, however, you might need to make an exact copy of a range of formulas. In an older tip, I described a rather complicated way to do this. See Making An Exact Copy Of A Range Of Formulas.

Matthew D. Healy saw that tip and shared another method, which uses Notepad. Here's how it works:

1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
2. Select the range to copy.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.

Extracting An Email Address From Text

Category: VBA Functions | [Item URL]

This tip describes a VBA function that accepts a text string as input, and returns the first email address found in the text. The figure below shows this function in use. The formula in cell B2 is:

=ExtractEmailAddress(A2)

If an email address is not found, the function returns an empty string. Also, note that it only extracts the first email address.

The function is not very elegant. It just starts with the first "at symbol" it finds, and examines the characters before and after the at symbol.

Function ExtractEmailAddress(s As String) As String
    Dim AtSignLocation As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"
    
    'Get location of the @
    AtSignLocation = InStr(s, "@")
    If AtSignLocation = 0 Then
        ExtractEmailAddress = "" 'not found
    Else
        TempStr = ""
        'Get 1st half of email address
        For i = AtSignLocation - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        If TempStr = "" Then Exit Function
        'get 2nd half
        TempStr = TempStr & "@"
        For i = AtSignLocation + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    'Remove trailing period if it exists
    If Right(TempStr, 1) = "." Then TempStr = _
       Left(TempStr, Len(TempStr) - 1)
    ExtractEmailAddress = TempStr
End Function

Quantifying Color Choices

Category: Formatting / VBA Functions | [Item URL]

A companion file is available: Click here to download

I got lots of Excel workbooks via email. A significant number of them have some downright ugly color choices. Beauty is in the eye of the beholder, but there's no excuse for making color choices that result in illegible text.

The World Wide Web Consortium (W3C) has created some formulas that can help you determine if your foreground and background colors are legible: Ensure that foreground and background color combinations provide sufficient contrast when viewed by someone having color deficits or when viewed on a black and white screen.

The W3C presents two formulas, each of which returns a value:

• Color Brightness Difference: returns a value between 0 and 255
• Color Difference: Returns a value between 0 and 765

I converted their formulas into VBA functions, and formulas that use these functions are shown in Columns B and C:

To be an acceptable color combination, the Color Difference score should be 500 or greater, and the Brightness Difference score should be 125 or greater. I used conditional formatting to highlight values that exceed these minimums.

Column D has a simple formula that determines if both score meet the minimum requirement.

These formulas seem to work quite well. The color combination deemed Acceptable are all very legible. Bottom line: You can't go wrong with black text on a white background. Reserve the fancy colors for column headers, or for special areas of a worksheet that you want to be noticed.

Add The Speech Controls To The Ribbon

Category: CommandBars & Menus | [Item URL]

A companion file is available: Click here to download

For some reason, the text-to-speech controls are not included in the Excel 2007 ribbon UI. Those who want to use these tools must add them to their Quick Access Toolbar.

This tip describes how to create a simple add-in that, when installed, displays the text-to-speech controls in the Review tab of the ribbon. These controls are in a new group (labeled Text to Speech), which is positioned between the Proofing group and the Comments group.

The new ribbon group contains six controls. The first five are built-in commands, so they don't require any callbacks. The last one, Voice, is a custom control. When clicked, it executes a VBA procedure that displays the Speech Properties dialog box from the Windows Control Panel. This dialog box lets you choose a different voice and control the speed of the voice.

I used the Custom UI Editor Tool to add the following RibbonX code that displays the new ribbon group:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab idMso="TabReview">
<group id="SpeechGroup" label="Text to Speech" insertBeforeMso="GroupComments">
<control idMso="SpeakCells" size="large" />
<control idMso="SpeakStop" size="large" />
<separator id="sep1" />
<control idMso="SpeakOnEnter" size="normal" />
<control idMso="SpeakByRows" size="normal" />
<control idMso="SpeakByColumns" size="normal" />
<button id ="RunSapi" label="Voice" imageMso = "DirectRepliesTo" onAction="RunSapiApp" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

The VBA procedure that is executed when the Voice button is clicked is defined like this:

Sub RunSapiApp(control As IRibbonControl)
 ' code here
End Sub

As it turns out, displaying the Sapi.cpl Control Panel dialog box is not as simple as displaying other control panel dialogs -- and I'm not certain that it will work on all systems. If you're interested, you can download the add-in and view the code.

A Macro To Count Word Frequencies

Category: General VBA | [Item URL]

A companion file is available: Click here to download

This tip describes a VBA macro that analyzes a list of text, and counts the frequency of each word. The macro works with any text. It assumes that the data begins in cell A1 of the active sheet, and the first blank cell in the column signals the end of the text.

The general approach is:

1. Add a new worksheet for the results
2. Remove all punctuation characters from the input text
3. Convert each word to upper case, and remove excess spaces
4. Loop through the input text and put each word in the results sheet
5. Create a pivot table that uses the word list and displays a frequency count for each word.

Here's the macro:

Sub MakeWordList()
    Dim InputSheet As Worksheet
    Dim WordListSheet As Worksheet
    Dim PuncChars As Variant, x As Variant
    Dim i As Long, r As Long
    Dim txt As String
    Dim wordCnt As Long
    Dim AllWords As Range
    Dim PC As PivotCache
    Dim PT As PivotTable
    
    Application.ScreenUpdating = False
    Set InputSheet = ActiveSheet
    Set WordListSheet = Worksheets.Add(after:=Worksheets(Sheets.Count))
    WordListSheet.Range("A1") = "All Words"
    WordListSheet.Range("A1").Font.Bold = True
    InputSheet.Activate
    wordCnt = 2
    PuncChars = Array(".", ",", ";", ":", "'", "!", "#", _
        "$", "%", "&", "(", ")", " - ", "_", "--", "+", _
        "=", "~", "/", "\", "{", "}", "[", "]", """", "?", "*")
    r = 1

'   Loop until blank cell is encountered
    Do While Cells(r, 1) <> ""
'       covert to UPPERCASE
        txt = UCase(Cells(r, 1))
'       Remove punctuation
        For i = 0 To UBound(PuncChars)
            txt = Replace(txt, PuncChars(i), "")
        Next i
'       Remove excess spaces
        txt = WorksheetFunction.Trim(txt)
'       Extract the words
        x = Split(txt)
        For i = 0 To UBound(x)
            WordListSheet.Cells(wordCnt, 1) = x(i)
            wordCnt = wordCnt + 1
        Next i
    r = r + 1
    Loop
    
'   Create pivot table
    WordListSheet.Activate
    Set AllWords = Range("A1").CurrentRegion
    Set PC = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
        SourceData:=AllWords)
    Set PT = PC.CreatePivotTable _
        (TableDestination:=Range("C1"), _
        TableName:="PivotTable1")
    With PT
        .AddDataField .PivotFields("All Words")
        .PivotFields("All Words").Orientation = xlRowField
    End With
End Sub

Saving A Range As A Graphic File

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

Quite by accident, I discovered something today that I should have known a long time ago: When you copy an Excel range, a graphic image of that range is also stored on the Windows clipboard. As a result, you can paste the clipboard contents into most graphics programs.

It's pretty simple:

1. Select a cell or range.
2. Press Ctrl+C to copy.
3. Activate a graphics program and press Ctrl+V to paste the image.
4. Then you can save it to the graphics file format of your choice.

I tried this with IrfanView and Adobe Photoshop Elements, and it works great. There are, of course, many screen capture programs available that are more flexible, but this method is quick and easy.

Here's a simple little VBA macro that automates the first three steps:

Sub CopyAsGraphic()
     Const Viewer As String = "C:\Program Files\IrfanView\i_view32.exe"
     Selection.Copy
     Shell Viewer, 1
     Application.SendKeys "^v"
End Sub

Note: If you don't have IrfanView (or if it's in a different directory), you'll need to modify the Viewer constant. By the way, I highly recommend IrfanView as your default image viewer. It's free, fast, and feature-packed.

Start by selecting a range, a chart, a shape -- or anything else that's selectable in Excel. Then execute the CopyAsGraphic macro. IrfanView (or your other program) will appear with the copied data. Then you can do whatever you want with it.

By the way, copying is a what-you-see-is-what-you-get thing. For example, if the selected range contains a chart, the chart will also appear in the image.

A Quick And Dirty Slideshow Macro

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

A companion file is available: Click here to download

Here's a VBA macro that displays a full-screen slide show, using all of the embedded charts on the active worksheet.

Sub ChartSlideShow()
    Dim Cht As ChartObject
    Dim UserSheet As Worksheet
    Set UserSheet = ActiveSheet
    Application.DisplayFullScreen = True
    Application.DisplayAlerts = False

    For Each Cht In UserSheet.ChartObjects
        Application.ScreenUpdating = False
        ' Delete old chart sheet if it exists
        On Error Resume Next
        Charts("ChartTemp").Delete
        On Error GoTo 0
        ' Copy embedded chart and move it
        UserSheet.Activate
        Cht.Chart.ChartArea.Copy
        ActiveSheet.Paste
        ActiveChart.Location Where:=xlLocationAsNewSheet, _
           Name:="ChartTemp"
        ' Show the chart sheet and prompt for next one
        Application.ScreenUpdating = True
        If MsgBox("OK for next chart, Cancel to stop.", _
          vbQuestion + vbOKCancel) = vbCancel Then Exit For
     Next Cht
     ' Clean up
     On Error Resume Next
     Charts("ChartTemp").Delete
     On Error GoTo 0
     Application.DisplayFullScreen = False
     Application.DisplayAlerts = True
     UserSheet.Activate
End Sub

Just copy and paste the code to a VBA module. Then activate a worksheet that contains embedded charts and execute the ChartSlideShow macro. It makes no changes to your original charts.

Note that you can create text-only charts by deleting the chart in a chart object. Then insert a shape and add some text. You can also display pictures by deleting the chart and inserting a picture.

I tested it in both Excel 2007 and Excel 2003. It works in both versions, but it looks a bit better in Excel 2007.

The order of the slides is determined by the z-order of the charts on the worksheet. You can change the z-order by right-clicking the chart object, and using the 'send forward' or 'send backward' command. In Excel 2007, it's much easier to do this by using the Re-order buttons in the Selection and Visibility pane.

Maximize Excel Across All Monitors

Category: General VBA | [Item URL]

If you've ever worked on a computer that has more than one monitor attached, you're probably hooked. It's great for VBA developers, because they can have Excel displayed on one monitor, and the VBA Editor displayed on another monitor.

In some cases, you might want Excel's window to display across all monitors, to maximize the amount of data that you can see. When you maximize Excel's window, it fills only the current monitor. To display Excel across multiple monitors, you must do it manually:

1. Make sure that the Excel window is not maximized.
2. Drag Excel's window to the upper left corner of the first monitor
3. Drag its left and bottom borders to fill all of your virtual screens.

Here's Excel in a 3200 x 1200 window:

Here's a simple macro that eliminates the manual work and causes Excel's window to display across all monitors:

Private Const SM_CXVIRTUALSCREEN = 78
Private Const SM_CYVIRTUALSCREEN = 79

Private Declare Function GetSystemMetrics Lib "user32" ( _
ByVal nIndex As Long) As Long

Sub FillVirtualScreen()
    With Application
        .WindowState = xlNormal
        .Left = 0
        .Top = 0
        .Width = GetSystemMetrics(SM_CXVIRTUALSCREEN)
        .Height = GetSystemMetrics(SM_CYVIRTUALSCREEN)
    End With
End Sub

Note that the Excel window is not really maximized. In other words, you can drag the title bar to a different position. I don't know of any way to truly maximize Excel across multiple monitors.

Create A Drop-Down List Of Possible Input Values

Category: General | [Item URL]

If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros.

Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell.

Here's how to create a drop-down list:

1. Type the list of valid entries in a single column. If you like, you can hide this column (select Format, Column, Hide).
2. Select the cell or cells that will display the list of entries.
3. Choose Data, Validation, and select the Settings tab.
4. From the Allow drop-down list, select List.
5. In the Source box, enter a range address or a reference to the items that you entered in step 1.
6. Make sure the 'In-cell dropdown' box is selected.
7. Click OK.

If your list is short, you can skip step 1 and type the list entries directly in the Source box in step 5, separating items with a comma.

The Data Validation dialog box has two other tabs. Click Input Message to add a prompt that will appear when a user selects a cell. Click Error Alert to specify a custom error message if the user's entry is invalid.

The handy data validation feature suffers from one serious flaw. If you paste an entry into a cell that uses data validation, the validation isn't performed. And if you select that cell again, the drop-down list no longer appears. Fortunately, you can circumvent this problem by protecting the worksheet: Select Tools, Protection, Protect Sheet.

Understanding The IsDate Function

Category: General VBA | [Item URL]

VBA's IsDate function supposedly tells you if a text string can be interpreted as a date. For example, all of the following expressions evaluate to True:

IsDate("5/25/2008")
IsDate("January 16")
IsDate("12-1")
IsDate("12/1/08")
IsDate("2/30")
IsDate("30/2")

Notice, in the last two examples, that IsDate isn't picky about the order of the day and month. Both of these strings could be interpreted as a date, so IsDate returns True (regardless of your system date format settings).

Here's some information from a Microsoft Support article.

The VBA date functions IsDate, Format, CDate, and CVDate utilize a function found in OLE Automation (OleAut32.dll). This function searches all possible date formats by tokenizing each of the separated values in the string representing the date and returns a Boolean value indicating whether the input can be represented as a Date.

This is important to remember when using the function to interpret a date that contains a 2 digit year. Different Locales use various date formats (that is, mm/dd/yy, yy/mm/dd, "DD MMM YY", "YY MMM DD", and so forth) and therefore the function tries the digits in all positions until the function has found a valid date or exhausted all possibilities.

Just because IsDate recognizes a string as a date doesn't mean that the string can be reliably converted to a date. In some cases, the result is ambiguous. For example, what about this expression?

IsDate("29-Feb-01")

February 29, 2001 is not a valid date. However, this expression returns True because February 1, 1929 (and January 2, 1929) are valid dates. And so are those same dates in 2029.

If IsDate is documented somewhere, I couldn't find it. Based on my testing, IsDate accepts any of the following as separator characters: a slash (/), a hyphen (-), a comma (,), a dot (.), and a space.

Therefore, the following expressions all return True:

IsDate("5.1")
IsDate("30 6")
IsDate("30,6")
IsDate("1/2")

But then there's this anomaly. The following expressions returns True:

IsDate("5.1.5")
IsDate("5.1.05")

But, inexplicitly, this expression returns False:

IsDate("5.1.2005")

Suppose that you created a UserForm with an InputBox where the user enters a date. It should be clear that using IsDate to validate the entry isn't very reliable.

Things get even more confusing when you realize that IsDate also covers time values (there is no corresponding IsTime function). So, the following expressions all return True:

IsDate("4:45")
IsDate("4.45")
IsDate("4 45")
IsDate("4/45")
IsDate("23:59")

These expressions return False:

IsDate("4:60")
IsDate("24.45")

It's important to point out that IsDate doesn't exhibit all of these quirks when you pass a Range argument. For example:

IsDate(Range("A1"))

As far as I can tell, IsDate is perfectly reliable at identifying cells that contains a date or a time. It does not, for example, identify a cell that contains 5.1 as a date.

Page 2 of 19 pages
[Previous page]   [Next page]