Wednesday, July 9, 2014

Excel Needs a Custom Formula Sandbox

Today I found myself in need of an Excel function to search a string from right to left (the opposite of the built-in FIND function).  I wasn't sure if Excel had a function for that, so I Googled it to find that Excel does not have that function, but it can be done using a complicated formula.

Of course it can be also done with a macro, which is what I ended up doing.  Since I have a personal macro workbook with a lot of custom functions, so I'm not adverse to creating new ones whenever I need to.

A lot of people are adverse to this approach, as the StackOverflow thread I linked to above indicates.  And I can understand why, since macros come with a lot of overhead (compatibility, security, etc.).

That gave me an idea.  MS should create a "formula sandbox" area in Excel where users can create custom formulas.  For the language, I would suggest limited-functionality versions of C#.NET and VB.NET.  The limited functionality would be necessary to ensure that the formulas don't present any security risk.

The custom formula sandbox would be linked to the user, any any custom formulas that he/she uses in the spreadsheet would be saved with that spreadsheet, so that the spreadsheet works for any user.

I think a lot of Excel users would welcome something like that.

Thursday, July 3, 2014

Dual Monitors and Excel

Windows could really use a "super maximize" button to maximize a window across all monitors.  I know that software packages made for managing multiple monitors have this functionality, but I would like it baked into Windows.

In any case, the program where I most often find myself manually resizing the window to span my two monitors is Excel.  A while ago I got sick of doing that, so I wrote a simple macro to do it.  Here's the code:

'the control parameter can be omitted.  I included it in my macro because that signature is needed for macros
'invoked from the ribbon
Sub MaximizeAcrossTwoMonitors(control As IRibbonControl)

    Dim TargetWidth As Integer
    TargetWidth = 2880 'fine-tune as needed for your specific setup
    Dim TargetHeight As Integer
    TargetHeight = 780 'fine-tune as needed for your specific setup
   'if the window is already maximized across two monitors, we're going to assume the user wants to return to regular maximized window state
    If Application.Width = TargetWidth And Application.Height = TargetHeight Then
        Application.WindowState = xlMaximized
        With Application
            .WindowState = xlNormal
            .Left = 1
            .Top = 1
            .Width = TargetWidth
            .Height = TargetHeight
        End With
    End If

End Sub

You can put that macro in your personal macro workbook and assign it to your quick access toolbar for easy access.

Once Excel is maximized across your workspace, you may want to have two or more workbooks displayed side-by-side.  You probably know that you can do that by going to View --> Arrange All --> Vertical.

However that's three steps I found myself struggling to recall the specifics of.  So, another simple macro for your quick access toolbar would be:

Sub ArrangeWindowsVertically(control As IRibbonControl)
    Windows.Arrange ArrangeStyle:=xlVertical
End Sub

I hope someone finds this useful.