Visio Shape Positioning Annoyance

In technical diagrams, I align the positions and dimensions of shapes with a grid so that the spacing and sizes are consistent. Using Visio 2007, after I resize a page using File, Page Setup, Page Size, Size to fit drawing contents, every shape's position is slightly modified. For example, if I position a shape at x = 40mm, y = 30mm, the shape's position becomes something like x = 40.0847mm, y = 30.1051mm.

My workaround is to manually resize the page size by holding the Ctrl key and using the pointer to move the horizontal or vertical edge of a page; then the shapes' positions aren't altered.

Vista Ctrl+Space IME Toggle Workaround

If you start using Windows Vista Input Method Editor (IME) for Chinese, you may find that you can no longer use Ctrl+Space in some applications (e.g. to select a column in Excel, to activate autocomplete in IDEs such as VBA, Netbeans or Visual Studio, or to activate Firefox Ubiquity). One workaround is to define a different hotkey, such as Ctrl+Shift+F12, to toggle the IME.

If you have only one keyboard service and find that Ctrl+Space mysteriously starts to act as a toggle for an unselected IME (in my case, Chinese (Traditional, Taiwan)), the workaround is to add a keyboard service and define a different IME hotkey. You must define a hotkey for toggling the IME, otherwise the IME is activated using the default Ctrl+Space.

Reference

• No CONTROL over the SPACE being filled with CTRL+SPACE

VBA Is*() Functions Only Apply To Variant Types

VBA has several informational functions prefixed with "Is", such as IsNull(), IsMissing() and IsEmpty(), that only work with Variant types. I didn't realise it until I tried to apply them to non-Variant variables and noticed that they didn't work as I expected or always returned the same result.

For instance, if you define optional parameters for a procedure, you can only test if they are used in the procedure call using IsMissing() if the parameter is a Variant type. Using any other type of parameter with IsMissing() always returns False because all other parameter types have a default value supplied by the compiler or by the programmer.

Similarly, only Variant variables can have a Null value so that they can be tested by the IsNull() function. Object types can have a Nothing value, not Null, and can only be tested using the Is operator.

In the same vein, only Variant variables can be uninitialised and tested with IsEmpty(). All other variable types are initialised to some value (e.g. Objects are initialised to Nothing, Integers are initialised to 0).

Simple cache in Excel using VBA static variables

Static variables in VBA functions, like their counterparts in C, retain their values between function calls. A common use for static variables is to maintain a running total or count. This article describes how static variables can be used for cacheing in Excel VBA, compares the performance of a simple approach versus a cache in Excel, and some discussion for using this approach.

Excel applications often have read or write data in specific cells, columns or rows, which are identified by cells containing specific strings. A simple approach is to write a function that returns a cell reference given a string, as below.

Function GetHeaderFind(ByVal str As String) As Range
  Set GetHeaderFind = Sheet1.Range("A:A").Find(What:=str, SearchOrder:=xlByRows, SearchDirection:=xlNext)
End Function

If we know that the cells referenced do not change during the execution of the program, then we could find them when they are first required and cache their values in a Collection. The next time the same string is presented, we just return a reference from the Collection:

Function GetHeaderStatic(ByVal str As String) As Range
  Static colRange As New Collection
  Dim rng As Range
  On Error Resume Next
  Set rng = colRange(str)
  If Err.Number <> 0 Then
    Set rng = Sheet1.Range("A:A").Find(What:=str, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    colRange.Add key:=str, Item:=rng
  End If
  On Error GoTo 0
  Set GetHeaderStatic = rng
End Function

Does caching the results make a difference? We test it by searching for three values 100000 times and writing the time elapsed between the two approaches:

Public Sub TestCache()
  Dim sStart As Single, sTimeFind As Single, sTimeCache As Single
  Dim rng As Range, wks As Worksheet
  Dim rngHeader As Range
  Const iCOUNT As Long = 100000
  Dim i As Long, j As Integer
  Dim aKey(3) As String
  aKey(0) = "x"
  aKey(1) = "y"
  aKey(2) = "z"
  
  'Find implementation
  sStart = Timer
  For i = 1 To iCOUNT Step 1
    For j = 0 To 2 Step 1
      Set rng = GetHeaderFind(aKey(j))
    Next j
  Next i
  sTimeFind = Timer - sStart
  
  'Cache implementation
  sStart = Timer
  For i = 1 To iCOUNT Step 1
    For j = 0 To 2 Step 1
      Set rng = GetHeaderStatic(aKey(j))
    Next j
  Next i
  sTimeCache = Timer - sStart
  
  Debug.Print "Find, Cache times = " & sTimeFind & "," & sTimeCache
  Debug.Print "Ratio = " & sTimeFind / sTimeCache
  End 'Dispose of static variables
End Sub

The results below show that the cache is about 29 times faster than the simple implementation.

Find, Cache times = 16.92969,0.5859375
Ratio = 28.89333
Find, Cache times = 16.85156,0.578125
Ratio = 29.14865
Find, Cache times = 16.84375,0.578125
Ratio = 29.13514

Of course, we could have simply declared and initialised a module-level Collection, and saved ourselves the effort of writing a cache. Below are some reasons why creating a cache in a function is useful:

• We are refactoring an existing function and do not want to (or cannot) change the function's interface.
• Unlike a VBA module-level variable which can be inadvertently changed by any function, we ensure that the Collection can only be changed in a one location because only the function can modify its static variable.

References

• Visual Basic for Applications Reference, Static Statement