Inputbox Validation

Here’s a little goodie I find myself re-writing every once in a while.
It’s a simple “Here is a question, give me a valid answer” type routine.

Note the use of the infinite Do .. Loop.
Just make sure your exits are covered (Cancel button)

Sub test()
    Dim str As String, bln As Boolean
 
    ‘Set up a default value
   str = “Blue”
    Do
        str = InputBox(“Favourite Colour?”, “Colour?”, str)
        ‘check for Cancel
       If str = “” Then
            ‘Cancelled – exit from do loop with failure
           bln = False
            Exit Do
        Else
            ‘Check the entry against valid answers
           Select Case LCase(str)
                Case “black”, “red”, “green”, “yellow”, “blue”, “magenta”, “cyan”, “black”
                    ‘Match found – exit from do loop with success
                   bln = True
                    Exit Do
                Case Else
                    ‘oops – not a valid answer
                   MsgBox “Not a recognised colour”, vbExclamation, “Error”
            End Select
        End If
    Loop    ‘infinite loop

    If bln Then
        MsgBox “Your favourite colour is “ & str
    End If
End Sub

Using Volatile Functions

Volatile functions are a type of function that will always recalculate.
That means whenever Excel needs to calculate any part of the workshet, those cells containing volatile functions will also calculate.

There are a limited set of volatile functions – these are:
RAND, NOW, TODAY, OFFSET, CELL, INDIRECT

To test their behaviour, I’ve created a User Defined Function which returns the current time.

Function CurrDate() As Date
    CurrDate = Now()
End Function

My worksheet contains formulas in column A and the formula description (escaped with a single quote) in column B

Notice which cells in column A get updated after I write something in cell B11
Only the cells containing volatile functions were recalculated.

I’ve seen very big worksheets where all of the cells refer to cell A1 – the formula being =NOW()
Users then wonder why there are noticable delays while performing data entry! This can be even more noticable when the dependent cells are XLL functions querying external data sources – requerying each time the user enters some more data.
In this case you could either manually enter the current time (or by a macro button) to save from volatile hell
-or-
ensure Excel has Calculation turned off – Tools > Options > Calculation > Manual

Calculation mode is saved with the workbook.

Be aware, Excel has a rule that it follows with regard to the workbook calculation mode:
If the workbook is the first workbook opened in that application, calculation mode is set to that saved with the workbook, otherwise it is ignored.

User Defined Functions can also be made Volatile by including the line: Application.Volatile
I read somewhere that it must be the first line of your User Defined Function. I don’t know if that’s true, but the first line is nice and visible so it’s a good idea.

Function CurrDate() As Date
    Application.Volatile
    CurrDate = Now()
End Function

Funky Comments

If you’re keen to add some snaz to your Cell Comments then read on!

Cell Comments are just Shapes underneath and you can treat them that way!

Ensure you have the Drawing Toolbar visible. From Excel’s menu: View > Toolbars > tick Drawing.

Add a comment to a cell.
While editing the comment, you’ll notice there is a thick striped border around the shape. While you’re typing comment text, you’ve actually got the Textbox selected, not the Shape itself. So you need to select the Shape.

Click the border or simply hit the Tab key. Notice how the thick striped border changes to a dotted pattern.

Now that you’ve got the Shape selected, you can change many properties of the appearance.
eg. From the Drawing toolbar: Draw > Change Autoshape > Stars and Banners > 16 point Star.

Everything you need to alter the appearance of the comment is available from the Drawing toolbar – Shadows, 3D, Colour, etc…

AutoShape Charting

With some effort it is possible to draw Shapes and AutoShapes to act like charts.

If you’ve never played with AutoShapes before, I encourage you to look. I’ve wasted hours on it… well… that and playing Tetris.
Make sure the Drawing toolbar is visible: From the View menu select Toolbars, Drawing should be ticked.
It usually docks itself to the bottom of the screen.

AutoShapes have differences from regular Shapes. One difference is that you can set Transparency on them.
In this example I’ve configured two plain “Can” AutoShapes (From the Basic Shapes category).
The can in the foreground is transparent and gives that “coffee in a cup” look.

You’ll notice that I’ve attached a scrollbar. That’s just for fun.
If you want the scrollbar, run the Setup procedure to set it all up. Otherwise just run SetupContainer.

Put this code in a standard code module:

Const cLeft = 50, cTop = 20, cWidth = 118, cHeight = 120
Const cDifference = 6, cRadialHeight = 8
Const cContainerName = “MyContainer”
Const cContentName = “MyContent”
Const cContainerColor = &H808080    ‘RGB(128, 128, 128)
Const cContentColor = &H80          ‘RGB(128, 0, 0)
Const cContentFormula = “=A1”
 
Sub Setup()
    Dim ole As OLEObject
 
    ‘Adds a Scrollbar for fun
   Set ole = ActiveSheet.OLEObjects.Add(ClassType:=“Forms.ScrollBar.1”, Left:=cLeft – 20, Top:=cTop, Width:=16, Height:=cHeight)
    ole.Object.Min = 100
    ole.Object.Max = 0
    ole.LinkedCell = cContentFormula
 
    SetupContainer
End Sub
 
Sub SetupContainer()
    ‘Creates the Content first
   With ActiveSheet.Shapes.AddShape(msoShapeCan, cLeft + cDifference, cTop + cDifference, cWidth – cDifference * 2, 1)
        .Name = cContentName
        .Adjustments(1) = cRadialHeight * 2 / cHeight
        .Fill.ForeColor.RGB = cContentColor
    End With
 
    ‘Creates the Container last (for transparency)
   With ActiveSheet.Shapes.AddShape(msoShapeCan, cLeft, cTop, cWidth, cHeight)
        .Name = cContainerName
        .Adjustments(1) = cRadialHeight * 2 / cHeight
        .Fill.ForeColor.RGB = cContainerColor
        .Fill.Transparency = 0.75
        .Select
        Selection.Formula = cContentFormula
        .TextFrame.Characters.Text = “”
        .TextFrame.Characters.Font.Size = 20
        .TextFrame.HorizontalAlignment = xlCenter
        .TextFrame.VerticalAlignment = xlCenter
    End With
    NewContentHeight Evaluate(cContentFormula)
End Sub
 
Sub NewContentHeight(sngHeight As Single)
    Dim shp As Shape
 
    Set shp = ActiveSheet.Shapes(cContainerName)
    With ActiveSheet.Shapes(cContentName)
        .Top = shp.Top + shp.Height – sngHeight – cDifference
        .Height = sngHeight
        If sngHeight = 0 Then
            .Adjustments(1) = 0
        Else
            .Adjustments(1) = cRadialHeight * 2 / sngHeight
        End If
    End With
End Sub

Put this code in the worksheet code module:

Private Sub ScrollBar1_Change()
    NewContentHeight Range(“A1”).Value
End Sub
 
Private Sub ScrollBar1_Scroll()
    NewContentHeight Range(“A1”).Value
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(“A1”)) Is Nothing Then NewContentHeight Range(“A1”).Value
End Sub

Excel Formula Formatter

A few weeks ago I came across a post on the blog of Eric W. Bachtal. He has written an Excel Formula Parser in Javascript. All I could think was.. WOW!
If you haven’t seen it yet, you should click here

I’ve always wanted to build a fully blown expression parser but didn’t really know where to start.
There is some great information in that post – the extra reading was helpful too.

So, I set about writing my own in VBA using the points and methods learned from that post. There sure are a lot of things to think about. Here is some of the trickery I encountered…

  • A space is not always whitespace, sometimes it’s an Intersect operator
  • The text either side of a colon are not always cell references. Sometimes they are numbers (eg. $25:26)
  • A plus is not always a plus, sometimes it’s a unary operator, sometimes a binary operator, sometimes the significant figure in scientific notation. eg. 12E+20
  • A formula parser-tokeniser is not very useful unless you do something with it – so I’m starting with something simple:
    An add-in for formatting and editing a formula. I think they call it a ‘beautifier’.
    Do a search on this website for “ugly formula” and you’ll see where this tool might be handy.

    I intend to build more around the add-in as time permits. Perhaps an advanced search/replace tool? FindLinks? MultiWkb/MultiSheet Precedents grapher? Who knows.

    I tried my best to accept International Regional Settings – no guarantees there.
    F9 will evaluate a portion of the formula too.

    It’s still beta as version 0.9.

    Download AudXL.zip from my website: http://www.vangelder.co.nz/excel

    Charting Goal Seek

    It is possible to change a Chart’s Source Data by resizing individual Chart Points.

    Let’s say you’ve got a Column Chart and you want to change one of the column sizes.
    Click once on the column. This selects the Series. Click again on the column. This selects the single column (Series Point).
    It’s not a double-click… but a click-wait-click action.

    You’ll notice some blobs appear at the corners of the column.
    An extra one is also positioned at the top of the column – this blob is special.
    You can click-drag it to resize the column and it will change the Source Data.

    In the following example, there are some people who work a number of hours per week, of which a portion of that time is devoted to projects.
    I’ve graphed the formula column non-project work. The formula in D2 is =B2-C2

    I can hear you saying:
    Hey! When you resize that column, it’s going to overwrite your formulas!

    Excel is pretty smart and knows it’s a formula, so it throws up Goal Seek.

    In this case I’ve told Goal Seek to keep changing the “Project hours” until “Non-Project hours” becomes 30.
    Project hours gets set to 5 and we’re happy!

    More Fill Handle Secrets

    It’s pretty handy to right-click, drag on the Fill Handle to get a secret list of options.
    Use this Site’s Search link for “Secrets of the Fill Handle” – a previous post covers right-clicking.

    Here are MORE of the goodies the Fill Handle allows:

    Hold down the Ctrl key while dragging the Fill Handle. The mouse cursor gets a baby plus indicating Copy mode.
    It performs an AutoFill Copy.

    Hold down the Shift key while dragging the Fill Handle down. The mouse cursor changes to a double line with double arrows pointing outward indicating Insert mode.
    It performs Cell Insert.

    Hold down the Shift key while dragging the Fill Handle overtop of the selected cells. The mouse cursor changes to a double line with double arrows pointing inward indicating Delete mode.
    It performs Cell Delete.

    Of course, these actions work when the entire Row or Column are selected too!
    (Though, if you’ve got entire Rows or Columns selected, you might find it quicker to hit the shortcut Ctrl+NumKeypad_Plus or Ctrl+NumKeypad_Minus)