Author: Rob van Gelder

Selection Offset

Posted on by Rob van Gelder

I had a worksheet table with blank rows separating the groups.
I needed to add another column – a formula – but wanted to retain the blank rows for formatting tidiness.

The table after adding the formula column:

Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and hit delete, but for thousands of rows that would be time consuming.

Here’s how I did it:
– Select column C.
– From the Edit menu, select Go To…, then click Special…
– Select Blanks, then click OK

Then I ran a macro which allows me to move the selection over one column.
In this example, I typed 0, 1 for the Input to SelectionOffset.
After the Selection was moved, I hit the delete key.

Sub SelectionOffset()
    Dim strInput As String, str As String, i As Long, bln As Boolean
    Dim strRows As String, strCols As String
 
    strInput = “”
    Do
        bln = False
        strInput = InputBox(“Selection offset by rows, cols” & vbNewLine & _
                “eg. 12, 2”, “Selection offset”, strInput)
        str = Replace(strInput, ” “, “”)
        If str <> “” Then
            i = InStr(str, “,”)
            If i = 0 Then strRows = str Else strRows = IIf(i = 1, “0”, Left(str, i – 1))
            If i = 0 Or i = Len(str) Then strCols = “0” Else strCols = Mid(str, i + 1)
 
            If IsNumeric(strRows) And IsNumeric(strCols) Then
                On Error Resume Next
                Selection.Offset(strRows, strCols).Select
                If Err.Number <> 0 Then
                    MsgBox “Invalid selection offset”, vbExclamation, “Error”
                    bln = True
                End If
                On Error GoTo 0
            Else
                MsgBox “Selection offset is not numeric”, vbExclamation, “Error”
                bln = True
            End If
        End If
    Loop While bln
End Sub

Subtotals to summarize data

Posted on by Rob van Gelder

El Says:
“I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day.”

This is where the Subtotals feature is quite useful.
It can add a variety of footers to appear at breaks in the group. You get outlining too.
Select your range, then from the Data menu, choose Subtotals…
At each change in StartDate, Use function Sum, Add subtotal to Duration.

Unprotect all Worksheets in all Workbooks

Posted on by Rob van Gelder

Here is one for the Code Library.

Somehow I end up misplacing this bit of code. So every time I need to do it, I end up re-writing it.
Perhaps I’ll save someone the same frustration along the way.

This code snippet will loop through each file in your folder (and subfolders).
For each workbook opened, it will unprotect each worksheet using the supplied password.

Const cStartFolder = “D:MySecretSpreadsheets” ‘no slash at end
Const cFileFilter = “*.xls”
Const cPassword = “trustno1”
 
Sub UnprotectAllWorksheets()
    Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet
 
    ExtractFolder cStartFolder, arr()
 
    On Error Resume Next
    j = -1: j = UBound(arr)
    On Error GoTo 0
 
    For i = 0 To j
        Set wkb = Workbooks.Open(arr(i), False)
        For Each wks In wkb.Worksheets
            wks.Unprotect cPassword
        Next
        wkb.Save
        wkb.Close
    Next
End Sub
 
Sub ExtractFolder(Folder As String, arr() As String)
    Dim i As Long, objFS As Object, objFolder As Object, obj As Object
 
    Set objFS = CreateObject(“Scripting.FileSystemObject”)
    Set objFolder = objFS.GetFolder(Folder)
 
    For Each obj In objFolder.SubFolders
        ExtractFolder obj.Path, arr()
    Next
 
    For Each obj In objFolder.Files
        If obj.Name Like cFileFilter Then
            On Error Resume Next
            i = 0: i = UBound(arr) + 1
            On Error GoTo 0
            ReDim Preserve arr(i)
            arr(i) = objFolder.Path & Application.PathSeparator & obj.Name
        End If
    Next
End Sub

Inconsistent Formulas

Posted on by Rob van Gelder

From time to time, I am asked to audit a spreadsheet.

One of the common mistakes I see is an inconsistent formula inside a group of cells.
For example:
– A1 has the formula =G1 * 0.105
– A1 has been formula auto-filled to A1:E5
– Then some time later, cell A3 has been changed to =G3 * 0.107

So the assumption is that A1 can be safely auto-filled to the region of cells. However, we have an exception which should be noted.

It’s nice to be able to highlight these exceptions. Here are 2 ways.

1. If you have Excel XP and above, you can use the Error Checking options. A little green triangle appears telling you that “something strange” is going on.
One downfall of the Error Checker is that it wont trigger if the inconsistent formula is on the corner of the checked region. In our example above, if the changed formulas was A5 instead of A3, it would have ignored that inconsistency.
I assume that this is to accommodate subtotals and grand totals?

2. Write a bit of VB code using the idea that the R1C1 version of the formula should be identical for all cells in the selection, so it should be a simple loop to check all of the formulas.
Here is an example:

Sub test()
    Dim strFormula As String, rng As Range
 
    strFormula = Selection(1).FormulaR1C1
 
    For Each rng In Selection
        If rng.FormulaR1C1 <> strFormula Then rng.Interior.ColorIndex = 6
    Next
End Sub

To use it, select the range A1:E5 then run the macro. It would colour the inconsistent formulas yellow (in the first example, cell A3).

Macro Shortcut Keys

Posted on by Rob van Gelder

I often use Excel’s macro recorder to perform repetitive tasks.
It’s usually a list of cells with a handful of exceptions. That is, I cant just run the macro from start to finish – I have to give each item a brief glance before the macro runs.
At the end of the macro, it’s handy to position the selected cell as the start of the next item in the list.
I’ll assign a shortcut key to the macro, such as ctrl+w, then use it on demand.
The process becomes simple: look at the item, is it ok? yes, press ctrl+w, next item, is it ok? yes, press ctrl+w… over and over.

You can reassign your shortcut keys from the Macros window:
From Excel’s menu: Tools, Macro, Macros… (or hit Alt+F8)
Highlight a macro, then click Options.

More often than not, the macro recorder will give me a good first draft but I’ll have to edit it some more from within the VB editor.
While coding the changes, I wondered where Excel stores the shortcut key.

Could it be that Excel recognises the code comment?

So I deleted the comments to be sure. No, it wasn’t the comment.

It turns out that the shortcut key is stored in the Code Module, but it’s hidden from sight.

Export the Code Module (right-click the Module, click Export File) then open it in Notepad.

You will notice a line that looks like this:
Attribute Macro1.VB_ProcData.VB_Invoke_Func = “w
14?

Disabled Items

Posted on by Rob van Gelder

I had a strange one last week while assisting a user reported problem.

I’ve got a workbook and a VBA referenced XLA.
The workbook loaded fine, but produced errors when you tried to execute macros.
The debug point was on the “Left” function, which is a classic symptom of a broken reference.

Checking the VBA Project, I noticed that the referenced XLA was not loaded at all, even though it was referenced.
The XLA was in the right spot. Security and permissions were OK.
No matter how many times I closed and re-opened Excel, it still would not load that referenced XLA.
I even tried rebooting the computer.

It turns out that the user had suffered a “serious crash” just hours earlier. The next time he started Excel is came up with all sorts of recovery questions, which went something like ‘this workbook suffered a serious crash, are you sure you want to open it?’.
Excel does this for all the suspect workbooks – even referenced XLAs – except that with referenced XLAs, that question is not presented to the user, it just assumes ‘no, you dont want to open this corrupt workbook’ (though it wasn’t corrupt at all).

The fix:
I opened the XLA file by itself, answered the recovery question and all was good next time Excel ran.

That’s not the only way to do it though.
It turns out that Excel maintains a list of Disabled Items that you can enable by going into Excel’s menu:
Help > About Excel > Disabled Items…
Then highlight the items and click Enable.

A fairly strange place to manage disabled items. I guess since it’s an Office-wide feature they might have struggled for UI consistency – who knows?

I spent some time trying to simulate a crash, but couldn’t. It must have been pretty serious!

Months of the World

Posted on by Rob van Gelder

While putting together the last post, something caught my eye. Something that I’d never noticed about cell formatting.

It was a custom cell format that was created without my action: [$-1409]dddd, d mmmm yyyy
It looked like a date format, but what was that [$-1409] ?!
Recognising 1409 as the New Zealand locale ID, I guessed it had something to do with Regional Settings.

You can actually change your date format for any recognised Country.
Time to play! Fancy your dates in Dutch?

Click your date cell.
From the Format menu, select Cells.
From the Number tab, select Date.
Choose the Locale (location) from the drop down box.

I wanted to see them all at once, so I copied a List of Locale ID (LCID) Values as Assigned by Microsoft, pasted them into Excel and wrote a formula for displaying all of the translations:

Date Separator and Regional Settings

Posted on by Rob van Gelder

I’m a New Zealander and that’s what I’ll be writing on 7 March for NZ’s Census 2006.
There has been a bit of news in this country about that issue. Until now, you could not record your ethnicity as “New Zealander” in New Zealand! Strange but true. But that’s all about to change.

<shakes head> Back to Excel… where was I?

If you live in a non-US country, you might have been caught by some applications using mm/dd as the default date format.
It has often led to problems for me.
Take the date: 7th of March. It could be written 07/03 (as we do in New Zealand) or 03/07 as it is in the US.

It’s really quite annoying that the first 12 days of the month could be mistaken for dd/mm or mm/dd.
There’s no use crying about it… I just find a way to deal with it.

The way I go is to always format the month as text. In other words dd-mmm-yyyy which appears as 07-Mar-2006
I have done it this way for a long time.
Over the years I’ve noticed an unfortunate problem surfacing. Some computers format my date cells differently!
Instead of the format 07-Mar-2006, it comes out as 07/Mar/2006. Those slashes look out of place and really ugly!

It turns out to be an Excel vs. Regional Settings quirk.

Scenario 1:

    You format a cell using the same date separator as that specified in Regional Settings. The date separator will, from then on, always inherit from Regional Settings.

    Excel stores the date separator as a forward slash.
    eg. Regional Settings = dd-MMM-yyyy then I format a cell as dd-mmm-yyyy. Excel stores the format as dd/mmm/yyyy.

Scenario 2:

    You format a cell using a different date separator to that specified in Regional Settings. The cell will then never inherit from Regional Settings.
    Internally Excel will prefix the date separator with a hidden backslash. ActiveCell.NumberFormat will not show the hidden blackslash.

Since my computer’s Regional Settings has a hyphen (-) for a date separator, I activated the “inherit” mode for the date separator.

The general rule is, when you want to enforce date separators, prefix them with a backslash.
In my case, I just need to change my cell formats to: dd-mmm-yyyy