Wednesday, November 19, 2008

Twitter and Facebook

I just signed up for a Twitter account. I can't say that I'll be updating it that often (heck, look at my blogging frequency), but I thought I'd give another "Web 2.0" app a try...

Wednesday, July 9, 2008

Visual Basic (VBA) code to find text in any Excel worksheet

I've always been baffled that Excel's Find function only works on the current Worksheet. How hard would it be for Microsoft to search across all sheets in the Workbook?

It shouldn't be too hard, because I figured out how to do it in Visual Basic. You'll need to understand some VBA to get this inserted into your environment (recommendation: record a macro to your Personal Workbook, open the VB Editor, and then paste this subroutine into the Modules section of your Personal Workbook), but it should work as is. I tested it in Excel 2003 for XP and Excel version "X" for Mac OS X.

Sub FindEverywhere()
' Prompt the user for the searchText
Dim searchText As String
searchText = InputBox("Find what:", "Search All Worksheets")

' If the searchText is NOT an empty string, begin the search
If Not searchText = "" Then
Dim r As Range
Dim findNext As Integer
findNext = vbYes

Dim i As Integer
' Use For loop, not a For Each loop, as it's the only way to handle hidden/macro sheets!
For i = 1 To Sheets.Count
' Loop through each sheet in the workbook. This does NOT change the activate/visible sheet.
' That will come later if we're still searching (findNext=vbYes). I'd prefer a method to
' "break" out of the For loop, but VBA doesn't have one?
If Sheets.Item(i).Visible = xlSheetVisible Then
' This sheet is visible, so let's work with it (you HAVE to skip macro sheets, so just skip all hidden sheets)
Dim sheet As Worksheet
Set sheet = Sheets.Item(i) ' don't do this until you're sure this sheet is Visible!

' The firstFind and looped vars are used to determine if we've looped thru every find on this page.
Dim firstFoundCell As String
firstFoundCell = ""
Dim looped As Boolean
looped = False

Do While findNext = vbYes And Not looped
' This is necessary to search on the right sheet (?), but it also means we'll switch to every sheet,
' including ones which do NOT contain searchText. So, if you search the whole Workbook, you'll end up looking
' at the last sheet, whether or not it contains the searchText. :-(

' Here's the search!
Set r = Cells.Find(searchText, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False)
If r Is Nothing Then
' Nothing was found. Exit this WHILE loop (the FOR loop will still go to the next sheet).
Exit Do
r.Activate ' Activate (select) the cell that was found
' Check/Set the firstFoundCell and the looped variable.
If firstFoundCell = "" Then
firstFoundCell = r.Address
' We've found at least one cell already. Check to see if we've looped back to the firstFoundCell.
If r.Address = firstFoundCell Then
looped = True
End If
End If ' end of checking for firstFoundCell

' If we haven't looped around, prompt the user to findNext. (If we have looped, the WHILE loop will finish, causing the FOR loop to go to the next sheet.)
If Not looped Then
findNext = MsgBox("Find Next?", vbYesNo)
End If
End If
Loop ' end of Do While loop
End If
Next i

If findNext = vbYes Then
MsgBox ("No more matches found!")
End If
End If
End Sub

As always, let me know if you have any comments or questions!

Friday, April 11, 2008

Make a Mac keyboard easier to use with a Windows PC

I know there aren't too many reasons to use a Mac keyboard with a PC, but if you're reading this article, perhaps you found yourself in the same situation as I did. I have a dual-input monitor (a rather nice HP LP2065), so I decided to hook my G4 MDD Mac and my Dell D620 laptop up to same monitor. The max resolution on the laptop's built-in LDC is 1440x900, but the external HP LCD gets 1600x1200, so it's substantially more real estate. However, it's just not practical to type on the laptop's keyboard and look over at the HP LCD, nor did I really want 2 keyboards cluttering up my limited desk space.

So, I bought a simple USB switch, plugged the Mac keyboard into the input and the Mac and laptop into the outputs. I press 2 buttons (one on the monitor and one on the USB switch), I can switch between Mac and PC very quickly!

That left me with my next problem, and the reason for this post. While the Mac keyboard can map to all main Windows keys, I was not happy with the default mapping. Take a look at standard Mac and PC keyboards:

The Mac keyboard puts the Alt key right next to the Control key, but a Windows keyboard puts the Windows key (opens the Start menu) between Control and Alt. I'm not really a touch-typist, but this throws me off something fierce. So, I start looking at ways to re-map keys in Windows, and the easiest way I found was KeyTweak. Here's a screenshot (click to see the full-size image):

I won't try to explain all of KeyTweak's features here, but it's pretty easy to re-map "normal" keys like Alt and Windows just using the GUI. If you have both keyboards available (as I did by using the laptop's keyboard), you can also use a "teach" mode to re-map some of the harder keys, like Volume Up/Down, Mute, and PrintScreen (one of my favorite keys - use Alt-PrintScreen to take a screenshot of the active window and store it in Windows' clipboard).

My exact setup is shown in this "raw map" view:

Feel free to download my Mac keyboard mapping for KeyTweak to use as-is or as a starting point for your own custom mapping.

P.S. If you're looking for the opposite functionality, MacWorld has a nice article on using a Windows keyboard with a Mac or you can try DoubleCommand.

Monday, March 10, 2008

A "fix" for the barrage of Deleted Items created by Google's Outlook Calendar sync

This article was written when Google's Outlook Calendar Sync tool was first made available to the public. Since then, Google has fixed this problem in their tool. I'm leaving the post here, as the general principle of having Outlook take specific actions (that couldn't be accomplished in a Rule) when new items show up in any Outlook location is still valid.

Google's new Outlook Calendar sync is a terrific idea. Unfortunately (but not unexpectedly) it has some problems. The first thing most people notice is that Outlook Calendar items that involve anyone besides yourself (i.e., you Invited others or someone else Invited you) don't show up in Google Calendar. The FAQ is suppposed to contain a "fix" for this, but people don't seem too happy with this.

The other problem takes longer to notice. The sync tool creates TONS of Appointments on 12/31/1979 at 7:00 pm - and then deletes them. When I say "tons", I mean it. At one point, I had >12,000 items in my Deleted Items, most of which were these 12/31/1979 appointments.

I don't know how to stop the sync from making these Appointments (I assume they're used as some temporary scratchpad (or someone really messed up their code)), but I do know how to keep all those items out of my Deleted Items.

This does require that you turn on Visual Basic (VB) for Applications (VBA) macros in Outlook. This is NOT a security risk that I know of, as I think you're the only one you can add macros in this manner.

In Outlook, go to Tool -> Macro -> Visual Basic Editor. In the left-hand column, there should be a hierarchy that looks like Project1 -> Microsoft Office Outlook Objects -> ThisOutlookSession. (If that's not there, you might need to record a macro and then delete it. This should create the ThisOutlookSession item. Click on ThisOutlookSession in the left-hand column, and then paste this into the main window:

Private WithEvents DeletedItems As Items

Sub Application_Startup()
Dim objNS As NameSpace
Set objNS = Application.GetNamespace("MAPI")
Set DeletedItems = objNS.GetDefaultFolder(olFolderDeletedItems).Items
Set objNS = Nothing
End Sub

Private Sub DeletedItems_ItemAdd(ByVal myItem As Object)
If myItem.Subject = "" Then
' Google's Outlook Calendar sync causes a bunch of Appointments to be create on 12/31/1979 at 7:00pm.
' After the sync, they get "deleted", but you can have THOUSANDS of these in your Deleted Items.
' This will permanently get rid of them (as well as any other items with no Subject).
End If
End Sub

BTW, the same idea can be extended to create an event handler to handle items getting added to a folder that collects all of your automated or junk email. Add a "Private WithEvents" line, and then add this to Application_Startup():
  Set AutomatedItems = objNS.GetDefaultFolder(olFolderInbox).Folders.item("Automated").Items

And then add a new handler to do something like:
Private Sub AutomatedItems_ItemAdd(ByVal myItem As Object)
myItem.UnRead = False
End Sub