Wednesday, November 30, 2005

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

Friday, November 25, 2005

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