CAGR

Microsoft Excel comes with a lot of inbuilt functions that can be used in cell formulae and there are also a number of add-ins that provide specialised sets of functions to support statistical, numerical and financial analysis e.g. The Analysis ToolPak.

As an amateur investor, I am often interested in the Compound Annual Growth Rate (CAGR) calculation for comparing the smoothed rate of return of different investments. Surprisingly enough, Excel doesn't have this in its kitbag, so I wrote my own. Below is my version of a user-defined function (UDF) that can be used in Excel (or any other VBA friendly product).

Public Function CAGR( _
   ByVal StartValue As Double, _
   ByVal EndValue As Double, _
   ByVal StartDate As Date, _
   ByVal EndDate As Date) _
   As Double

   ' Compute Compound Annual Growth Rate according to formula
   ' CAGR = (FV / PV ) ^ 1/n - 1 where n is number of years
   ' Developed by Warren Bain of Thought Croft Pty Ltd

   CAGR = (EndValue / StartValue) _
          ^ (1 / ((EndDate - StartDate) / 365.25)) - 1
End Function

SetTextEditMode

I like to use the Locked and Enabled properties of text-based controls - combo boxes, check boxes, list boxes and text boxes - to control whether they can be changed or entered. But for the life of me, I can never remember which combination of true and false values gives me the look I am after. For example, Enabled=Yes and Locked=Yes means the text field can be entered but can't be changed. Change this to Enabled=No and Locked=Yes and you won't be able to enter or edit the field. Make it Enabled=Yes, Locked=No and you can enter and edit and so on.

To make it easier on myself, I wrote this function to do the remembering for me. As you can see, a lot of the work is done by the enumerated constants definition - that's why I like to use them apart from the fact that the VBA compiler (or whatever) also reminds me what values I can use when I am coding. I also like to use these constants as bitwise comparison flags by making them different powers of 2 - easier to look at the example than try and explain! The net effect is that some sensible constant design simplifies coding to two statements rather than a string of nested IFs.

Public Enum TextEditMode
   temcInvalid = 0
   temcLockedTrue = 2 ^ 1
   temcLockedFalse = 2 ^ 2
   temcEnabledTrue = 2 ^ 3
   temcEnabledFalse = 2 ^ 4
   temcEnterWithEdit = temcLockedFalse + temcEnabledTrue
   temcEnterNoEdit = temcLockedTrue + temcEnabledTrue
   temcNoEnterNormal = temcLockedTrue + temcEnabledFalse
   temcNoEnterDimmed = temcLockedFalse + temcEnabledFalse
End Enum

Public Function SetTextEditMode( _
   ByRef ctl As Control, _
   Optional ByVal temMode As TextEditMode) As TextEditMode

   ' Set or return the value of Enabled and Locked properties
   ' in a text based control to manage how it looks as follows:
   ' Enabled? Locked? Result?
   ' Yes Yes Can enter, can't edit, normal
   ' Yes No Can enter, can edit, normal
   ' No Yes Can't enter, can't edit, normal
   ' No No Can't enter, can't edit, dimmed
   ' If no mode requested then returns the current settings

   ' Developed by Warren Bain on 21/10/2005
   ' Copyright (c) Thought Croft Pty Ltd
   ' All rights reserved.

   ' Check we can do this for this type of control
   Select Case ctl.ControlType
      Case acComboBox, acCheckBox, acListBox, acTextBox
         If IsMissing(temMode) Then
            ' Let them know what is set
            SetTextEditMode = IIf(ctl.Enabled, temcEnabledTrue, temcEnabledFalse) + _
               IIf(ctl.Locked, temcLockedTrue, temcLockedFalse)
         Else
            ' Set the controls parameters
            ctl.Enabled = temMode And temcEnabledTrue
            ctl.Locked = temMode And temcLockedTrue
            SetTextEditMode = temMode
         End If
      Case Else
         SetTextEditMode = temcInvalid
      End Select
End Function

GetSubFormControlName

I'm currently developing a Microsoft Access based system and found myself continually needing to work out how to access a property of the control that contains a subform from code running in the subform (for example to get at the Tag property).

This function will do that by walking the controls collection of the subform's parent form looking for any subforms and then compares the hWnd (basically Windows internal "handle" for that window) of that control with the hWnd of our subform.

Once found, we construct the name of the control using the appropriate name format. If we want to use the name in code then the short format is fine but if it is to be used in a query then we need the long version which may necessitate walking up the hierarchy if in fact the parent form is itself a subform (forms can be nested to three levels). This is achieved by calling the function recursively on the parent.

Public Enum ControlNameFormat
   cnfcShortPropertyName
   cnfcLongHierarchicalName
End Enum

Public Function GetSubFormControlName( _
   ByRef frm As Form, _
   Optional ByVal NameFormat As ControlNameFormat = _
      cnfcShortPropertyName) As String

' Tells a subform the name of the control that
' it has been opened in on the main form. Used
' to modify the base subform's source or to
' retrieve special values from its Tag property.
' The NameFormat tells us whether to just provide
' the ctl.Name property or the fully qualified
' form controls collection item name.

' Example:
' Form "MainForm" holding "1stSubForm" in control "fsub1"
' holding "2ndSubForm" in control "fsub2"...
' GSFCN(1stSubForm,Long) = "[Forms]![MainForm]![fsub1]"
' GSFCN(2ndSubForm,Long) = "[Forms]![MainForm]![fsub1].Form![fsub2]"
' GSFCN(2ndSubForm,Short) = "fsub2"

' Developed by Warren Bain on 26/09/2005
' Copyright (c) Thought Croft Pty Ltd.

   Dim ctl As Control
   Dim strResult As String

   On Error Resume Next

   ' Loop through all controls on the parent and test for the
   ' handle of the window of the subsidiary form. If it
   ' matches ours, then we have found the control it opened in
   If Not IsSubForm(frm) Then
      ' We are at the top of the tree, so return name
      If NameFormat = cnfcShortPropertyName Then
         strResult = frm.Name
      ElseIf NameFormat = cnfcLongHierarchicalName Then
         strResult = "[Forms]![" & frm.Name & "]"
      End If
   Else
      For Each ctl In frm.Parent.Controls
         If ctl.ControlType = acSubform Then
            If ctl.Form.hWnd = frm.hWnd Then
               ' Found the right one
               If NameFormat = cnfcShortPropertyName Then
                  ' Just return the name of the control
                  strResult = ctl.Name
               ElseIf NameFormat = cnfcLongHierarchicalName Then
                  ' Add parent plus fully qualified control
                  strResult = GetSubFormControlName(frm.Parent, NameFormat) & ".Form![" & ctl.Name & "]"
               End If
               Exit For
            End If
         End If
      Next ctl
   End If
   GetSubFormControlName = strResult
End Function

Private Function IsSubForm(frm As Form) As Boolean
   ' Is the form currently loaded as a subform?
   Dim strFormName As String
   On Error Resume Next
   strFormName = frm.Parent.Name
   IsSubForm = (Err.Number = 0)
   Err.Clear
End Function