Search This Blog

Saturday, April 7, 2012

VBA Macros, Client Scripts » VBA to Invert Case for several Apps

Oops CAPS LOCK -- damn I *hate* retyping!
How many times have you accidentally pressed the caps lock key, or forgotten about it being on until after typing some amount of text? I bet I've done it 10 times just today! d'oh! What a bummer to have to retype it -- especially when it's so avoidable!

Recently it occurred to me how easily such mistakes could be fixed by a bit of code. The first place I wanted an InvertCase hot key was Visual Studio 6. I got it to work inside of 10 minutes -- cool! I decided to map the macro-function to CTRL-\, easy to hit with one hand, and all the more intuitive combinations were taken.

Encouraged by quick success, I thought about all the other places it would come in handy. The various apps that make up MS Office came quickly to mind. It would've been nice to have a single source that worked in many host applications, but sadly that's a pipe dream. As I examined some of the potential host apps for this, I realized that a.) the core code for this is highly portable, and b.) each host app has its quirks.

So I isolated the core functionality into a portable function, shown below...


' The implementation core, which of course will need to be
' defined in each host app environment, but the source is the same
' in all of them.
Function InvertCaseCore(inbuf)

   Dim l, c, outbuf, i
   l = Len(inbuf)
   For i = 1 To l
        c = Mid(inbuf, i, 1)
        If (c >= "A") And (c <= "Z") Then
            c = LCase(c)
        ElseIf (c >= "a") And (c <= "z") Then
            c = UCase(c)
        End If
        outbuf = outbuf & c


    InvertCaseCore = outbuf
End Function

Now onto the practical side, here are wrappers for some host apps that I use, first stop, VS6...


' macro in Visual Studio 6
Sub InvertCase()
   Dim outbuf
   outbuf = InvertCaseCore(ActiveDocument.Selection.Text)
   ActiveDocument.Selection.Text = outbuf
End Sub

Next I moved on to MS Word. In this case, the ActiveDocument object exists in Word's VBA model but it doesn't expose a Selection property, as it does in VS6. Also, in VS6, the Selection object is not global, as it is in Word. So ActiveDocument.Selection in VS6 became merely Selection, as is shown below.


' VBA macro in MS Word
Sub InvertCase()
   Dim outbuf
   outbuf = InvertCaseCore(Selection.Text)
   Selection.Text = outbuf
End Sub

Both of the above act on the currently selected text when the hot key is pressed. Next I did MS Excel, which was a slight departure because apparently its VBA macro facility is disabled while text in a cell is selected. When I clicked the hot key, the selected text disappeared. So in Excel it acts on the contents of the active cell... which suits me well enough.


' VBA macro in MS Excel
Sub InvertCase()
   ActiveCell.FormulaR1C1 = InvertCaseCore(ActiveCell.FormulaR1C1)
End Sub

To make this fly in Outlook, I had to use Outlook Redemption (an excellent tool, btw.) I also had to create a commandbar button in the Inspector; I named it &\InvertCase, which gives me ALT+\ as a hot key to invoke it.
Public Sub InvertCase()
   Dim oInspector As Outlook.Inspector
   Set oInspector = Application.ActiveInspector
   If oInspector Is Nothing Then Exit Sub

   Dim oSafeInspector As Redemption.SafeInspector
   Set oSafeInspector = New Redemption.SafeInspector
   oSafeInspector.Item = oInspector
   Dim buf As String

   buf = InvertCaseCore(oSafeInspector.SelText)
   oSafeInspector.SelText = buf
   Set oInspector = Nothing
   Set oSafeInspector = Nothing
End Sub

Not sure where else I'll want this, but when I think of someplace, I'll post the wrapper here.

That's it for now, enjoy!

No comments:

Post a Comment