The majority of the time at Corality we audit financial models or develop user friendly financial models for our clients. However, in a field that develops as quickly as financial modelling we need to make sure that we stay ahead of the competition - even in the long term.
Our method to constantly re-invent and develop our skill set is to experiment with Excel to develop new methods and applications that we had not earlier thought of. Every now and then we come up with something useful that can be integrated into our day to day practice of financial modelling. This time, I am not sure if that is what happened…
Using VBA to generate a cross-cursor
I presented a VBA training course in Sydney last week and one of the applications in that course is a dynamic cross-cursor for highlighting relevant data in a matrix format. (Here is an introduction to basic conditional formatting if you need one of those, and a bit more advanced here)
VBA cross-cursor with conditional formatting
The VBA exercise shows the power of the worksheet functions and how it can be used to improve model presentation.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Generate a circle around the selected cell
After the VBA course I kept experimenting with the same concept, i.e. updating the worksheet based on the properties of the selected cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Description: Returns column and row of the selected cell 'Author: Rickard Warnelid - Corality 'When to run: Automatically returns column and row of selected cell
'Return selected column
[CursorColumn] = Target.Column
'Return selected row
[CursorRow] = Target.Row
End Sub
Convert the VBA trick into a ‘Find the hidden treasure’
This time I thought I would expand the conditional formatting to generate a circle around the selected cell by back-solving the equation c^2 = a^2 + b^2. This gives a circle with a fixed radius. The second step was to apply a scaling factor inversely related to the distance of the active cell and a defined Target Cell. The scaling factor was then used to determine the size of the highlighted circle around the active cell.
Excel conditional formatting calculations
With “O17″ as the location of the ‘hidden treasure, the conditional formatting looks like this:
=SQRT((COLUMN(F20)-CursorColumn)^2+(ROW(F20)-CursorRow)^2)<$D$14
Graphical representation of combination of VBA and conditional formatting trick
Not even close...
Getting closer...
Even closer....
Found it!
I have been scratching my brain, but just can’t come up with a useful commercial application for this rather neat VBA trick. I suppose it could be used to really prove to your friends and colleagues that there is a real geek inside you, but that’s probably about it. None the less, it is a pretty cool feature given that it is generated with only two lines of VBA code.
Download the conditional formatting and VBA example here
If you can think of a useful application for this, please let me know!
