Something Gets Forced

Category: VBA Weirdness | [Item URL]

Apparently, the documentation writers weren't quite sure of what gets forced when you use the ForceFullCalculation property in your VBA code.

Here's a screen grab from an MSDN article that lists Excel 2007's New Members and Constants:

The same uncertainty is expressed in the Excel 2007 Help System.

When you click the link for an explanation, here's what you learn:

Forces a full calculation of a workbook.

You might think that ForceFullCalculation is a method. It's actually a property of a workbook. But, typical of many things in Excel 2007, Microsoft provides no useful information about it.

I set this property to True for a workbook, using this statement:

ActiveWorkbook.ForceFullCalculation = True

The only result I noticed is that the Calculate indicator in the status bar turns on and never goes away -- even when I set the ForceFullCalculation property to False. The only way to get rid of that indicator is to close Excel and restart it.

I did some Google searching and found a PDF document of the Excel 97-2007 Binary File Format Specification, where I found this:

If the workbook is in the forced calculation mode, in which case dependencies are ignored, and all worksheets are marked to calculate fully every time the calculation is triggered.

OK, that makes sense. This setting is also stored in the Office 2007 file format. I found this in the workbook.xml:

<calcPr calcId="125725" forceFullCalc="1"/>

Now the question is, why would anyone want this to be in effect? And why doesn't that Calculate indicator go away when it's no longer in effect?

What Is Truth?

Category: General Weirdness / VBA Weirdness | [Item URL]

True is true, and False is false, right?

True. Except when you use Excel, things can get a bit confusing. Take a look at the worksheet below. the Range A1:A3 contains Boolean values (TRUE or FALSE).

When you add these three cells, you can get any of three results, depending on the method you use.

Simple addition

The formula in cell A5 uses the addition operator. The sum of these three cells is 2. The conclusion: Excel treats TRUE as 1, and FALSE as 0.

The SUM function

But wait! The formula in cell A6 uses Excel's SUM function. In this case, the sum of these three cells is 0.

It is possible to "force" these logical values to be treated as values by the SUM function. It requires an array formula. Enter the formula below using Ctrl+Shift+Enter, and it will return 2.

=SUM(A1:A3*1)

Oddly, the SUM function does return the correct answer if the logical values are passed as literal arguments. The formula below returns 2:

=SUM(TRUE,TRUE,FALSE)

A VBA function

Although VBA is tightly integrated with Excel, sometimes it appears that the two applications don't understand each other. The formula in cell C7 uses a simple VBA function (listed below), and it returns -2!

Function VBASUM(rng)
    Dim cell As Range
    VBASUM = 0
    For Each cell In rng
        VBASUM = VBASUM + cell.Value
    Next cell
End Function

VBA considers True to be -1, and False to be 0.

Still more

Tom Schipper sent me a workbook that demonstrates even more logical weirdness. The figure below demonstrates. The formula being entered is:

=AND(X,Y,Z)

The names X and Z refer to empty cells (Excel considers them to be FALSE). The name Y refers to a cell that contains a 1 (Excel considers that to be TRUE). Even though two of the arguments for the AND function are FALSE, the formula evaluates to TRUE!

A Dangerous SpecialCells Bug

Category: VBA Weirdness | [Item URL]

The SpecialCells method is a handy way to work with a subset of cells. But using this method doesn't always work as it should.

Macro1, listed below, works with range C1:C16385. When executed, the macro selects all cells within this range that contain a formula which returns an error (for example, #DIV/0!). It works just fine.

Sub Macro1()
  Range("C1:C16385").SpecialCells _
    (xlCellTypeFormulas, xlErrors).Select
End Sub

Now look at Macro2. The only difference is that the macro works with range C1:C16386 (i.e., one additional cell). This macro does not work as expected. Run it, and you'll see that it selects all cells in the range.

Sub Macro2()
  Range("C1:C16386").SpecialCells _
    (xlCellTypeFormulas, xlErrors).Select
End Sub

I haven't bothered to experiment with this to find the exact conditions that trigger the bug, but the problem seems to occur with any range that exceeds 16,385 rows.

No big deal? Well, keep in mind that your code could be using the Delete method instead of the Select method.

By the way, I've been using VBA for a long time, and I had never encountered this problem. Just luck, I guess.

Submitted by Bob Umlas.

Gaps In The Excel Object Model

Category: VBA Weirdness | [Item URL]

If you use VBA long enough, you'll eventually understand the concept of an "object model," and gain an appreciation of how VBA manipulates Excel objects. But you'll also discover that the object model is not perfect. In some cases, it's as if the designers took the afternoon off and never resumed where they left off.

A few examples:

• If you want to write VBA code to insert a page header in the center position, you'll discover that the CenterHeader property (as well as the other "header" and "footer" properties) leaves a bit to be desired. These should be objects, which contain a Font object. Rather, you're forced to create a text string that uses obscure codes. The statement below, for example, inserts "XYZ Corp." as a center header, using Times New Roman font, 11 points, bold, and italic:

ActiveSheet.PageSetup.CenterHeader = _
&""Times New Roman,Bold Italic""&11XYZ Corp."

• Using VBA, it is not possible to determine the source range for data used in an Excel chart. The Values property, for example, always returns a variant array (not a Range object). The only solution is to parse the SERIES formula and extract the range address. I've encapsulated this tedious task into a class module, available here.
• Have you ever wanted to write code to determine if a chart's data labels contain formula links? If so, you've either given up, or discovered that the only way to do this is to select the data label and then use an obscure XLM macro:

ExecuteExcel4Macro("GET.FORMULA(SELECTION())")

• And, what if you are one of the 5-6 people in the world who needs to determine if a secondary chart axis contains a linked formula? You're out of luck. XLM won't even come to the rescue. Because of this flaw, it's impossible to write code that can identify all links in a workbook. A linked formula in a secondary axes can not be identified.
• Excel 2002 introduced several enhancements to worksheet protection. including the ability to prevent selection of locked and/or unlocked cells. Unfortunately, the new Protection object does not include these properties! Apparently, Microsoft assumed that developers would be satisfied with the old EnableSelection property. But the problem is, this property is not saved with the workbook.

A User-Define Function Can’t Change The Worksheet. Oh Yeah?

Category: VBA Weirdness | [Item URL]

VBA programmers know that they can create custom functions that can be used in worksheet formulas. A basic rule is:

A function used in a formula can return a value. It cannot make any changes to the workbook.

There is at least one exception to this rule. A VBA function used in a formula can change the text in a cell comment. The function below demonstrates.

Function ChangeComment(Rng1, Rng2)
   Rng1.Comment.Text Rng2.Text
End Function

If cell A1 contains a comment, the following formula will insert the contents of cell B1 into the comment for cell A1:

  =ChangeComment(A1,B1)

There may actually be a practical use for this. The function below returns the value of the cell it references, and also inserts the current date and time into the referenced cell's comment. This reflects the time at which the cell was last calculated.

Function TimeStamp(Rng1)
  TimeStamp = Rng1.Value
  Rng1.Comment.Text CStr(Now())
End Function

David Hager reports that the following Methods, when used in a custom function, can also make change to the workbook:

• Merge
• UnMerge
• AddComment
• ClearComments
• InsertIndent
  

The Elusive Formula1 Property For Conditional Formatting

Category: VBA Weirdness | [Item URL]

Excel's Conditional Formatting feature is useful, but one gets the impression that the designers were forced to implement it in a hurry. It suffers from two serious problems:

1. Copying and pasting a cell over a cell that uses conditional formatting wipes out the formatting rules. Doh!
2. There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered."

In an effort to solve #2, I created a custom VBA function. But then I discovered something very strange:

When a Conditional Formatting formula uses a relative range reference, accessing the Formula1 property via VBA will give you a different formula, depending on the active cell position!

To demonstrate... Activate cell A1 and add this conditional formatting rule: formula is =B1>0

With cell A1 activated, execute this sub:

Sub Test1() 
    Dim F1 As String 
    F1 = Range("A1").FormatConditions(1).Formula1 
    MsgBox F1 
End Sub 

You'll see the correct formula.

Now, activate any other cell and re-run the procedure. The Formula1 property will return a different formula, which is relative to the active cell.

In order to retrieve the actual Formula1 property value, you need to convert the formula to R1C1 notation using the active cells as the reference. Then convert that R1C1 formula back to A1 style. The procedure below returns the actual Formula1 value, regardless of the active cell position.

Sub Test2() 
    Dim F1 As String, F2 As String 
    F1 = Range("A1").FormatConditions(1).Formula1 
    F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell) 
    F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Range("A1")) 
    MsgBox F1 
End Sub 

XLM Rears Its Ugly Head - -And Crashes Excel

Category: VBA Weirdness | [Item URL]

Caution: This will crash Excel. So if you try it, make sure you don't have any unsaved work.

Try this useless and strange tidbit:

Enter this simple procedure in the code module for ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Stop 
End Sub 

1. Put something in the workbook (anything, so it will print)
2. Click the Print Preview button.
3. At the breakpoint, press F5. All is OK.
4. Now try it again, but this time when you get to the Stop statement, keep pressing the F8 key, even after you return from the Print Preview. Somehow, you will be thrown into some ancient XLM code which doesn't work (see the figure below) Anything you click on just turns bold! Time for the Three-Finger Salute.

Makes You Want To Say, “Hmmmmmm….”

Category: VBA Weirdness | [Item URL]

Try executing this VBA procedure. I guarantee you'll be surprised by the result.

Sub HowDidHeDoThat()
    For x = 0 To 5
        x1 = x1 & Chr(x * (x * (x * (x * (-0.75 * x + 7.2917) _
            - 22.5) + 16.708) + 28.25) + 72)
    Next x
    For x = 0 To 6
        x2 = x2 & Chr(x * (x * (x * (x * (x * (0.425 * x - 6.8667) _
            + 40.833) - 109.58) + 122.24) - 23.05) + 87)
    Next x
    MsgBox x1 & x2
End Sub

(contributed by Bob Umlas. Original author unknown).

Worse Than A Fatal Error?

Category: VBA Weirdness | [Item URL]

Be careful when you change the name of a UserForm. Here's what happened when I tried it.

Oh come on, it's just a computer program. Generally, the term catastrophic is used to describe an event such as an earthquake or an airline crash. Crashing Excel is such a common occurrence that it's hardly worthy of such a description.

Perhaps not surprisingly, clicking the Help button doesn't really tell me much about this catastrophe.

I also like the way the Help file makes it clear that Excel is not responsible for this one. Sure, blame the system or an external component!

But then again, a catastrophic failure error does convey more information than an unspecified error...

When A Sheet Turns Into A Button

Category: VBA Weirdness | [Item URL]

To experience some weirdness in a worksheet, follow these steps:

1. Start with a new workbook.
2. Press Alt+F11 to activate the VB Editor.
3. Insert a new VBA module, and enter this procedure:

  Sub SayHello()
      Msgbox "Hello!"
  End Sub

4. Press Alt+F11 to return to your workbook.
5. Right-click any toolbar and choose Forms (this displays the Forms toolbar).
6. Use the Button icon on the Forms toolbar to add a button to the worksheet. Put the button somewhere near the upper left corner, around cell B2.
7. When prompted, assign the SayHello procedure to the button. Click the button to ensure that it executes the macro.
8. Right-click the button and choose Format Control. Click the Properties tab and make sure the 'Move and size with cells' option is selected.
9. Select rows on the worksheet beginning with Row 1 to well past the button, and. choose Edit - Delete. The button will get sucked up to the top of the worksheet.  You can still detect a tiny sliver of the button if you look carefully, just below the column headings.
10. Protect the worksheet. (use Tools - Protection, and accept the defaults.)

Now we enter the Twilight Zone of Excel...

Notice your mouse cursor -- it displays a hand, just like it does when it hovers over a button. Now try clicking in any cell on the worksheet. Doing so runs the macro attached to the button. Excel seems to think the button extends over the entire worksheet!

When you scroll down to reveal more cells, those cells behave normally.

(Contributed by Dan Evens)