CRTL+SHIFT Condrum: How MS dicked power users in Vista/Win7 with Regional/Language settings

After how many months work has finally upgraded my computer from XP to Win7 ('bout fucking time in my opinion, and the upgrade only came after a system crash resulting in loss of data).

Now I have Win7 on my personal computer, but quite frankly I don't use XL on it that much, namely because Dell poorly executed the keyboard layout for their XPS series, but I digress (oh, and fuck you Dell for your shitty keyboard layout.  You have to try to fuck a keyboard up.  Seriously.)

Any who, while trying to unhide columns in a worksheet one day at work I noticed the unhide columns shortcut combo no longer worked (for the novice: CTRL+SHIFT+) )  I assumed either: 1) my keyboard was FUBAR'd, which is entirely possible given how much coffee and other shit I spill on it, or 2) my AutoHotKey scripts were overriding Excel.  Closed AHK, and double checked the keyboard.  Shortcut combo still didn't work.  Fuck.  Time to hit the GOOG.

After a period of searching I stumbled upon this post at SuperUser, which references this MS KB article re the "Input method editor keyboard shortcut (CTRL+SHIFT+0)".  Essentially any program that relies on the CTRL+SHIFT keyboard combo for shortcuts gets fucked, and overridden by MS's default implementation of their shitty language switching feature.  To correct this, and allow you to run shortcut combos as normal across ALL programs (not only MSOffice programs), go through the following steps:

1. Click Start, and then click Control Panel.
2. Double-click Regional and Language Options.
3. Click Keyboards and Languages, and then click Change keyboards.
4. Click Advanced Key Settings, and select Between input languages.
5. Click change Key Sequence.
6. For Switch Keyboard Layout, select Not Assigned.
7. Click OK to close each dialog box.

Now you're back to normal.  Why MS activated this feature by default is beyond me.  Sometimes I wonder if they're all mouth breathers in Redmond.  That is all.

Learn something new everyday - why one blank cell causes a count in pivot tables (Excel)

Picked this up from Pivot Table Data Crunching Microsoft Excel 2010 (pg. 63):

If all cells in a column contain numeric data, Excel chooses to sum.  If just one cells is either blank or contains text, Excel chooses to count.

I have definitely encountered this before when building pivot tables, and just shrugged it off a quirk of Excel.  Interesting! Now I know why Excel Pivot Tables would default to a Count of Values instead of Sum.  Next time I'll ensure my base data doesn't contain any blanks. 

Any other pivot table tips out there waiting to be discovered?

Finding home - Synchronizing all sheets in a workbook [Exel VBA]

I often work in large workbooks of 50+ sheets, and I typically set every worksheet to cell A1 before I distribute the file to other bears.

Problem: Needless to say the last thing I want to do before I close out of my file is to manually sync each and every sheet.

Solution: Macro that goes through every sheet and sets the view to your activeview (e.g. cell L29 is the Top-Left cell visible in your worksheet) and sets the activecell on every worksheet to your current activecell (e.g. cell N51).

I use the ScrollRow and ScrollColumn properties to determine the Top-Left most visible cell, and the Goto method to set the activeview.

To prevent errors if there's a ChartSheet in the workbook, there's a custom function in the code below that traps this condition.

'---------------------------------------------------------------------------------------
' Procedure : Synchronize_Worksheets
' Author    : Business Bear
' Date      : 2/24/2012
' Purpose   : Synchronizes all visible worksheets to selected cell.  Note, does not
'             sync hidden worksheets and chart sheets.  If range of multiple cells is
'             selected at run time, syncs to activecell
'---------------------------------------------------------------------------------------
'
Sub Synchronize_Worksheets()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim sSetCell As String
    Dim wsStart As Worksheet
    Dim sShtName As String
    Dim sGotoCellAddy As String
    Dim lCounter As Long
    Dim lScrRow As Long
    Dim lScrCol As Long

    Application.ScreenUpdating = False
    
    'Prevent errors if a workbook's not open
    On Error GoTo End_Sub
    Set wb = ActiveWorkbook
    sShtName = ActiveSheet.Name
    On Error GoTo 0
    
    'Use custom function to check if sheet is Chart sheet
    'Prevents the code from adjusting chart sheet / throwing errors
    If IsChartSheet(sShtName) Then
        Exit Sub
    End If
    Set wsStart = wb.ActiveSheet
    
    'Find visible area of activewindow; this will align each worksheet
    lScrRow = ActiveWindow.ScrollRow
    lScrCol = ActiveWindow.ScrollColumn
    'Top-Left most cell in the visible range
    sGotoCellAddy = Cells(lScrRow, lScrCol).Address(False, False, xlA1)
        
    'Activecell from users selection
    sSetCell = ActiveCell.Address(Rowabsolute:=False, Columnabsolute:=False)
    
    lCounter = 0
    'Loop through each worksheet in the active workbook
    For Each ws In wb.Worksheets
        'Check is sheet is visible, if not skip; prevents errors
        If ws.Visible Then
            'Sets the visible range
            Application.Goto Reference:=ws.Range(sGotoCellAddy), Scroll:=True
            'Selects the cell the user wanted to sync
            ws.Range(sSetCell).Select
            'For the dialog box at end of code; counts sheets synced
            lCounter = lCounter + 1
        End If
    Next ws
    wsStart.Select
    'Display dialog box
    MsgBox Prompt:=lCounter & " worksheets syncronized to cell " & sSetCell, _
        Title:="Process Complete"

End_Sub:
    Application.ScreenUpdating = True

End Sub

'---------------------------------------------------------------------------------------
' Procedure : IsChartSheet
' Author    : Business Bear
' Date      : 2/24/2012
' Purpose   : Boolean; Tests if sheet is a chart sheet, returns true if true
'---------------------------------------------------------------------------------------
'
Public Function IsChartSheet(ChtName As String) As Boolean
    Dim chrtTest As Chart

    On Error Resume Next
    Set chrtTest = Charts(ChtName)
    On Error GoTo 0
    IsChartSheet = IIf(Not chrtTest Is Nothing, True, False)
End Function

Fun with Mnemonics...$ is for...

A little birdie told me the following today:

If you have trouble remembering what the $ symbol means in the address bar (e.g. $A$1), just remember that $ is for stay.

That is, the $ symbol indicates an absolute reference and the reference won't move when copied.

Bring 2003 and earlier docs up to the newest format [Excel VBA]

I often work with data exports from Oracle SAP, Business Warehouse, and Discoverer Plus.  The front-ends for each of these save, when selecting the option to export to a spreadsheet, the data to ".xls".

When opening these ".xls" files in Excel 2007 or later, you're still restricted to the row/column limitations of older versions of Excel (read: < 2003) as Excel enables compatibility mode.  Compatibility mode also limits some of the functions available to newer versions of Excel (I often get mad when structured references from tables are not available...grr).  

What did I do to solve this?  Save to a newer Excel format (typically ".xlsx" since no macros were in the original data files), close the file (to exit compatibility mode), delete the old ".xls" file, and finally reopen my new ".xlsx" file.  Get's tedious when you're pulling a metric ton of data daily.

What do I do know?  I use the code below.  It takes your file, saves it with the same file name but with a ".xlsx" extension to the directory the files currently in, closes the old file, deletes the file, and then reopens the new file.  All in a split second.  

It's the little things.

P.S. Make sure you turn on the reference to the "Windows Script Host Object Model"

'---------------------------------------------------------------------------------------
' Procedure : Save_To_XSLX_File
' Author    : Business Bear
' Date      : 1/20/2012
' Purpose   : Saves file to an XLSX file.  Good for updating 2003 files to later formats.
'---------------------------------------------------------------------------------------
'
Sub Save_To_XSLX_File()
Dim sFilename As String
Dim ws As Workbook
Dim Pos As Long
Dim sDir As String
Dim FSO As FileSystemObject
Dim sOldFileName As String

Const ext As String = ".xlsx"

DisplayAlerts = False
EnableEvents = False
Set wb = ActiveWorkbook
'Save directory and file names for future use
sDir = wb.FullName
sFilename = wb.Name
'Trim dir name to remove file name
sDir = Left(sDir, Len(sDir) - Len(sFilename))
sOldFileName = sDir & sFilename
Pos = InStrRev(wb.Name, ".") - 1
'Remove old extension from name
sFilename = Left(sFilename, Pos)
sFilename = sFilename & ext
'Simple solution in case user tries to save over a newer file
'and decides not to complete the process
On Error GoTo Exit_Me
'Save new file
wb.SaveAs Filename:=sDir & sFilename, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'Close old workbook
wb.Close
Set FSO = New FileSystemObject
'Warning - DELETES OLD FILE
FSO.DeleteFile FileSpec:=sOldFileName
'Opens workbook in new format
Workbooks.Open (sDir & sFilename)
Exit_Me:
DisplayAlerts = True
EnableEvents = True
End Sub

Finding the last row/column in a worksheet [Excel VBA]

I often stumble across blog posts where people are trying to discover the last cell in a column or row. Typically the example at hand is filled with non-contiguous data.  Often bloggers/commenters/arctic creatures other than polar bears will present the following solution:

Note: The following examples assumes the data range starts in Cell "A1" or Cell(1,1).

For Rows:

Set MyRng = Range("A1", Range("A65536").End(xlUp))

Or 

Set MyRng = Range(Cells(1,1), Cells(65536,1).End(xlUp))

For Columns:

Set MyRng = Range("A1", Range("IV1").End(xlToLeft))

Or 

Set MyRng = Range(Cells(1,1), Cells(1,256).End(xlToLeft))

I don't know why people hard code the last row/column values; the practice makes for code that can be difficult to edit/troubleshoot later on. Plus, it's not really backwards compatible. Sure the above examples work for Excel 2003 and earlier, but if you're working with Excel 2007 or later you potentially miss capturing a large amount of data.  (1,048,576 Rows and 16,384 Columns are available in Excel 2007 and late).

To avoid hardcoding values, and making the code compatible with Excel 03 and later, I do this:

Dim lMaxRows as Long
Dim lMaxCols as Long

lMaxRows = Rows.Count
lMaxCols = Columns.Count

So defining your range now looks like this:
For Rows:

Set MyRng = Range("A1", Range("A" & lMaxRows).End(xlUp))

Or 

Set MyRng = Range(Cells(1,1), Cells(lMaxRows,1).End(xlUp))

For Columns:

Set MyRng = Range(Cells(1,1), Cells(1,lMaxCols).End(xlToLeft))

Non-sequitur: Some people don't like using the "Cells" property to reference ranges or cells, but I say poo on that (admittedly it does make reading code more difficult). Using the Cells property makes coding loops easier. So there.

Takeaway: Avoid hardcoding - it makes debugging more difficult.

Mitigating % Complete and Duration Headaches (Project VBA)

Most of our projects are setup as Fixed Unit, non-effort driven tasks in MSP (Note: We also setup our schedules with the Updating task status updates resource status, located under the Calculation tab, checked).

When entering status for a task, we update (primarily) two fields: % Complete and/or Duration.  Changing the Duration field will affect the % Complete field in 2 ways:

1. Decreasing the Duration will increase the % Complete field;
2. Increasing the Duration will decrease the % Complete field.

Often we do not want to change % Complete, but adjust the Duration and maintain our current % Complete. We track performance using % Complete (for better or for worse, and yes I'm aware of using % Work Complete to track actual track progress...), so we are extra cautious with any negative changes in % Complete.  Why?  Well, let's just say reporting negative performance to the customer is not a positive experience.

Although we take extra steps to ensure we don't inadvertently decrease performance (though we do make the downward adjustment if appropriate, god forbid), we all have a habit of falling into the debacle that is colloquially known as "month end swirl".  Only the strong survive, and those that do are permanently scarred. 

I wrote the code below to speed up entering status and reduce schedule rework.  I have the macro set to my Ctrl-A shortcut in MSP.  To run the code select your task you want to update, and run the macro.  The code will pop-up an input box asking for the task's new duration.  The code will adjust your the task to your new duration while maintaining the previous % Complete.

Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NewDuration
' Author    : Business Bear
' Date      : 1/23/2012
' Purpose   : http://www.yunolikeexcel.com
'             This code will adjust your the task to your new duration
'             while maintaining the previous % Complete.
'             Setting the finish date to a date prior to the start date
'             will turn the task into a milestone.
'---------------------------------------------------------------------------------------
'
Sub NewDuration()

    Dim dCurStart As Date
    Dim vFinishDate As Variant
    Dim dNewFinish As Date
    Dim dCurFinish As Date
    Dim pj As Project
    Dim lNewDuration As Long
    Dim ac As Cell
    Dim lPctCompl As Long
    Dim vMsgBoxResp As Variant
   
    'Adjust conlMinutesInDay constant to reflect the number of minutes/day
    'in your schedule.  We use 8hrs/day so 8Hrs*60min = 480min/day
    Const conlMinutesInDay As Long = 480
    Const convMsgBoxButtons As Variant = vbYesNo + vbSystemModal + vbExclamation
    Const consMsgBoxTitle As String = "Warning"
    Const consDateFormat As String = "mm/dd/yyyy"
    Const consInputBxTitle As String = "New Finish Date?"
   
    'prevent code from running if no active project
    On Error GoTo EndMacro
    Set pj = ActiveProject
    On Error GoTo 0
    Set ac = ActiveCell
    Set t = ac.Task

    'prevent code from running if no task selected
    If t Is Nothing Then GoTo EndMacro
    dCurStart = t.Start
    dCurFinish = t.Finish
    lPctCompl = t.PercentComplete

    'loop until user enters a valid date
    'if user enters nothing exit procedure
    Do
        vFinishDate = InputBox(consInputBxTitle)
        If vFinishDate = vbNullString Then
            GoTo EndMacro
            Exit Do
        End If
    Loop Until IsDate(vFinishDate)

    '=== DateDifference returns minutes; divide by 480 for days
    lNewDuration = Application.DateDifference(StartDate:=dCurStart, _
            FinishDate:=vFinishDate) / conlMinutesInDay
    dNewFinish = Application.DateAdd(StartDate:=dCurStart, _
            Duration:=lNewDuration * conlMinutesInDay)
    If Format(dNewFinish, consDateFormat) <> vFinishDate Then
        If dNewFinish < vFinishDate Then
            lNewDuration = lNewDuration + 1
        ElseIf dNewFinish > dCurFinish Then
            lNewDuration = lNewDuration - 1
        End If
    End If

    '=== Set New finish date for testing against old finish date
    dNewFinish = Application.DateAdd(StartDate:=dCurStart, _
            Duration:=lNewDuration * conlMinutesInDay)
    If dCurFinish > dNewFinish Then
        vMsgBoxResp = MsgBox(Prompt:="Old finish date (" & Format(dCurFinish, _
                consDateFormat) & ") is after selected Finish Date (" & Format(dNewFinish, _
                consDateFormat) & ")." & vbCrLf & vbCrLf & "Continue?", _
                Buttons:=convMsgBoxButtons, Title:=consMsgBoxTitle)
        If vMsgBoxResp = vbYes Then
            'User wants to update even though date is in past
            GoTo Continue
        Else
            'User opted to exit
            GoTo EndMacro
        End If
    End If

Continue:
    t.Duration = lNewDuration * conlMinutesInDay
    t.PercentComplete = lPctCompl

EndMacro:
    Set pj = Nothing
    Set vFinishDate = Nothing
End Sub