Some cool things about Vista

Recently, I made the upgrade to a new laptop running Vista Ultimate.  Some of you might remember that I tried this back in December last year on my old laptop, and it didn’t work out so well.
This trip hasn’t been without it’s share of pain either, I’ll admit, but there are a couple of cool things in Vista that are a pleasant upgrade from XP.

I should say that my wife has been running Vista Home Premium on a new desktop for a few months, and she quite likes it.  I don’t think that there is any question that Vista is by far a prettier and sexier operating system than XP, which seemed more focussed on stability and functionality.

I’ll give you the things I don’t like about it first:

  • It’s a memory pig.  512MB of RAM for the OS alone is crazy.  Especially when you can only put in 4GB in the 32bit version as a system max.  We had that same limit in XP, so what gives there?
  • Business programs still aren’t ready for it, IMHO.  I have a Property Management System at work which, while claimed to work with Vista Business, caused me four bluescreens in 20 minutes.  I haven’t seen four bluescreens in XP in the last two years, despite being pretty hard on my systems.
  • I find the UAC a little frustrating.  I get that we want to have users aware of what they are doing, but I’ve had encountered situations where I’ve had to clear at least four “Oh my god the world is going to end if you do this” prompts to change certain things on my system.  Once would have been enough.
  • The lack of option to trust a specific exe file so that I can run it without getting the UAC prompt every time would be nice.
  • I also find that the Windows Explorer window never seems to have quite the setup I’m looking for when browsing files.  It’s either got too many folders listed, the wrong top level shortcut folders, or… I don’t know.  I just have to seem to click more.

Things I do like:

  • ReadyBoost.  (The ability to stick in a USB stick to give the system a little more juice.)
  • Adding a new network printer is a snap!  In XP you had to tell it you had a local printer, set up a TCP/IP port, find your network printer, find drivers, etc…  In Vista you tell it you have a network printer and it comes back with a list of all of them.  For work, this was awesome as I could just pick from the list of 10 and I’m off to the races.  Now, to be fair, my printers all pre-date vista, so the drivers ship with it.  I’m not sure how easy this will be when we get a new printer on site.
  • Wizards for working with pictures are greatly improved.  Plug in the camera and it will download them.  You can even rate them, which is kind of neat, although I’ll be fair and say I’ve never tried to go back and look for one that’s been rated.
  • The sidebar would be kind of neat, but I’m never really looking at my desktop…
  • Speaking of the desktop, Windows Dreamscene (a Vista Ulimate add-on that allows you to play a movie as your desktop background) is kind of neat as well.  Only problem is that it seems to be pretty unstable and crashes Windows Explorer quite regularly.

I’m curious what other people’s likes/dislikes are about this OS.  My recommendation, based on trying to get it hooked up at work with Novell and Citrix, is to stick with XP for now.  For home use though, it’s not half bad.

Stripping data from an imported file

Most of us, when we start dabbling with Excel macros, probably take the following steps:

  • Record code
  • Begin to make minor edits to the code
  • Eventually learn about loops and start using them everywhere
  • Learn that loops are “expensive” and cost time to do so start looking for fast ways
  • Discover “screenupdating=false” to cut loop execution time a bit
  • Start using Excel’s built in functions to avoid loops and cut time significantly

This is pretty much the way my leaning went anyway. I now try to avoid loops wherever possible, and try to increase execution speed as I just hate waiting for my stuff to complete.

I work with a lot of ASCII files dumped out of our Property Management System, often pulling them into Excel to work with. One example of this is our Trial Balance. Because of the way the vendor built the software, it creates a Trial Balance with every account repeated for every department, even if our chart of accounts doesn’t have that combination of account/department codes. This means that when I print it to file, my trial balance is over 15,700 lines long.

I actually use this report in a few different ways, and sometimes can need to update it several times in one day. Naturally I wrote a macro to go through the file, strip out garbage lines, reformat the entry so it can be used as I need it and a couple of other things. Like a total neophyte, I opened it in Excel, then proceeded to loop through every line. The end result, based on a single timer test, was 132.5 seconds to complete the process. 2.2 minutes of staring at the screen waiting for it to complete each time I ran it.

At the MVP summit, I had a discussion with Damon Longworth about doing this kind of task using Autofilters, but he recommended streaming it in from the text file and checking the data before it ever hit Excel. I decided to give it a try.

Despite re-writing to do the task (which I had to do anyway due to a change), I actually needed more case statements and if statements to work outside Excel than I did if I pulled the data in. Regardless, the execution time cut down from over 2 minutes to 25.5 seconds. Pretty darn cool. :)

For reference, I started with (what I believe was) Bernie Dietrick’s large file import code, and modified it to do my stuff. His code, which I originally worked with is shown below:

[vb]
Sub LargeFileImport(Filename As String)
‘Bernie Deitrick’s code for opening very large text files in Excel
‘Dimension Variables
Dim ResultStr As String
Dim FileNum As Integer
Dim Counter As Double

‘Get Next Available File Handle Number
FileNum = FreeFile()

‘Open Text File For Input
Open Filename For Input As #FileNum

‘Turn Screen Updating Off
Application.ScreenUpdating = False

‘Create A New WorkBook With One Worksheet In It
Workbooks.Add Template:=xlWorksheet

‘Set The Counter to 1
Counter = 1

‘Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)

‘Display Importing Row Number On Status Bar
Application.StatusBar = “Importing Row ” & Counter & ” of text file ” & Filename

‘Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr

‘Store Variable Data Into Active Cell
If Left(ResultStr, 1) = “=” Then
ActiveCell.Value = “‘” & ResultStr
Else
ActiveCell.Value = ResultStr
End If
If ActiveCell.Row = Activesheet.Rows.Count Then
‘If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
‘If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If

‘Increment the Counter By 1
Counter = Counter + 1

‘Start Again At Top Of ‘Do While’ Statement
Loop

‘Close The Open Text File
Close
End Sub[/vb]

Breaking Tab and Enter key navigation

So the other day I got a complaint from one of my users:  “I really wish you’d fix the Excel tabbing issue in this file.”

Now I’ve sent a considerable amount of time training my users to enter data in worksheets by navigating to the right by pressing the Tab key and then pressing Enter when they are done working with the row.  For those of you who don’t know this, when you work in this way, Excel will return you to the cell just below the one you started tabbing from.  Try this:

  • Select cell B1, press Tab 3 times and you’ll be in cell E1.  Press Enter and you will be returned to cell B2.
  • Select cell B1, press Tab 3 times, then press the right arrow key and you’ll be in cell F1.  Pressed Enter and you’ll be in cell F2.  (This is because you started using different keys to navigate, so the tab caching was lost.)

Okay, so this is pretty basic navigation, but I accidentally did something that breaks it.

As a matter of general practice, I apply worksheet protection (with no password) to all of my templates.  This is just fine if you leave the default options — “select locked cells” and “select unlocked cells”– checked.  If you decide to only let users select unlocked cells, however, the keys work like this:

  • Select cell B1, press Tab 3 times and you’ll be in cell E1.  Press Enter and you will be returned to cell E2.

That is E2, not B2 as it was before!  (Note that this does assume that at least B1:E2 is unlocked.)  Personally, I found this pretty irritating.  I’ve also been able to confirm that this is an issue in Excel 2003 and Excel 2007.  I haven’t tested any further back than that.

Now, the big question that I’d like to know… Is this a feature or a bug?  Does anyone have a good reason for why this scenario would be different?

At any rate, here’s a fix:

The following code goes in the ThisWorkbook module:

[vb]Private Sub Workbook_Open()
‘ Written By: Ken Puls (www.excelguru.ca)
‘ Purpose   : Capture the Tab key to a specific event
    Application.OnKey “{Tab}”, “OnTab”
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘ Written By: Ken Puls (www.excelguru.ca)
‘ Purpose   : Cancel the Tab key override
    Application.OnKey “{Tab}”
End Sub

Private Sub Workbook_SheetSelectionChange( _
    ByVal Sh As Object, ByVal Target As Range)
‘ Written By: Ken Puls (www.excelguru.ca)
‘ Purpose   : Evaluate cell movement
    If rngFirstTab Is Nothing Then
        ‘Not in tabbing mode, do nothing
    Else
        ‘In tabbing mode
        Application.EnableEvents = False
        ‘Check if range below last tab cell was selected
        ‘(assumes that user presed Enter to get there)
        If Target.Offset(-1, 0).Address = rngLastTab.Address Then
            ‘True, so return to cell below tabbing origin
                rngFirstTab.Offset(1, 0).Select
        End If
       
        ‘Set tabbing origin and last tab to nothing
        Set rngFirstTab = Nothing
        Set rngLastTab = Nothing
        Application.EnableEvents = True
    End If
End Sub[/vb]

And the following code goes in a standard module:

[vb]Public rngLastTab As Range
Public rngFirstTab As Range

Public Sub OnTab()
‘ Written By: Ken Puls (www.excelguru.ca)
‘ Purpose   : Override tab movement
‘             For use in environements where tab returns are
‘             lost (Sheets protected with “Only select unlocked
‘             cells.)

    ‘Record where tabbing started
    If rngFirstTab Is Nothing Then _
    Set rngFirstTab = ActiveCell
   
    ‘Record where tab is going
    Set rngLastTab = ActiveCell.Offset(0, 1)
   
    ‘Activate next cell
    Application.EnableEvents = False
    rngLastTab.Select
    Application.EnableEvents = True
End Sub
[/vb]

One little note here… if you press the down arrow at the end of a string of tabs, it will be treated as if you hit the Enter key.  You’ll be sent back to the beginning of the row.  I suppose that I could have also captured the Enter key’s onKey event, but I elected not to bother with this.

Oh, the irony

As most of you have probably guessed, I have comment moderation turned on for my blog.  The first post for a user from an IP address is immediately put in the moderation queue, and I get to approve or deny it.  Most spam comments are actually filtered out before they get to the moderation queue, but not all.

Just now I got a notification asking to review the following message, which also included a link to some website:

< strong > popcorn machines… < / strong>

The funny thing is that it was posted to this entry. :)