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.

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

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.

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.

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.

Excel 2007 Upgrade FAQ: Macros

Category: General VBA | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q. How do I record a macro?

A. Click the little square icon in the bottom left of the status bar.

Q. How do I run a macro?

A. Choose Macros in the Code group of the Developer tab.

Q. I don't have a Developer tab.

A. Display the Excel Options dialog box, click Popular, and then enable 'Show Developer tab in the Ribbon.'

Q. I recorded a macro and saved my workbook. When I reopened it, the macros were gone!

A. By default, Excel proposes that you destroy your macros when you save the workbook. When you save the file, read the warning very carefully, and don't accept the default "Yes" button.

Q: I recorded a macro while formatting a chart, and the macro was empty.

A: The VBA macro recorder ignores formatting applied to individual chart elements. Maybe this will be fixed in a future service pack.

Q: Using VBA to modify Shapes is very tricky, so I tried to record a macro while working with a Shape. The macro was empty.

A: Maybe this will be fixed in a future service pack.

Q: I'm trying to automate creating a simple SmartArt diagram. Recording a macro produces an empty macro.

A: Maybe this will be fixed in a future service pack.

Q: How do I use VBA to add a simple button to the ribbon?

A: You can't. You must write XML code and insert the document into a workbook file using 3rd party tools. Or, if you're a glutton for punishment, you can do it by unzipping the document and making the edits manually.

Q: How do I use VBA to activate a particular tab.

A: Sendkeys is your only choice. Press the Alt key to find out the keysroke(s) required. For example, to switch to the Page Layout tab, use this: Application.SendKeys "%w{F6}"

Q: I'm trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.

A: Maybe this will be fixed in a future service pack.

Q: Can I use the VBA Application.Help method to display a particular Excel 2007 help topic?

A: No, but you can use Application.Assistance.ShowHelp method. First, navigate the local Help system and identify the topic ID. Right-click, and select the "Copy xxxxxxxx" option (this copies the topic ID to the clipboard. Then use a VBA statement like this: Application.Assistance.ShowHelp "HP10062493". The text in quotes is the topic ID pasted from the clipboard.

Page 1 of 6 pages
[Next page]