Functions that do things

You’ll often read that user-defined-functions (UDFs) can’t do anything – they can only return a value to the cell that called them. While that’s true, they can schedule a task to be performed when the sheet is next calculated. The general method is shown below, where I’ve created a function to update a chart’s Y axis scale.

We start with a global variable declared in a standard module to hold the set of scheduled actions to perform:

‘A public collection to hold our scheduled actions
Public goFunctionCommands As Collection

We then have a function in a standard module that adds an item to that collection. In this example, we’ll add an array, where the first element identifies the action to perform and subsequent elements provide any extra information we need to perform the action:

‘Function to change the Y axis maximum value on a chart
‘rngCell is a reference to a range on the worksheet containing the chart
‘sChart is the name of the chart object to update
‘dNewMax is the value for the Y axis maximum value.
Public Function SetChartYAxisMax(ByRef rngCell As Range, ByVal sChart As String, _
ByVal dNewMax As Double) As Double

‘Instantiate the collection if cleared
If goFunctionCommands Is Nothing Then
Set goFunctionCommands = New Collection
End If

‘Add a command to the collection, as an array of
‘all the information we need to do the action.
‘In this case, we need the sheet, the name of the
‘chart object and the new Y axis value
goFunctionCommands.Add Array(“SetChartYAxisMax”, rngCell.Parent, sChart, dNewMax)

‘Return the name being set to
SetChartYAxisMax = dNewMax

End Function

That is the function we’ll use from the worksheet, such as the following, where B10 might contain a formula to calculate the new maximum:

=SetChartYAxisMax($A$1,”chtTheChart”,B10))

Lastly, we need to respond to the _Calculate event in the ThisWorkbook code module. In this procedure, we iterate through all the scheduled tasks, identify which they are and perform them:

‘Perform the scheduled tasks when the workbook is calculated
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Dim vInfo As Variant

On Error Resume Next

If Not goFunctionCommands Is Nothing Then

‘Loop through all the actions to perform
For Each vInfo In goFunctionCommands

‘Which action should we perform?
‘Add a new Case for each ‘command’ function
Select Case vInfo(0)
Case “SetChartYAxisMax”

‘For this action,
‘vInfo(0) is the name of the action
‘vInfo(1) is the worksheet containing the embedded chart
‘vInfo(2) is the name of the chart object
‘vInfo(3) is the new axis scale
vInfo(1).ChartObjects(vInfo(2)).Chart.Axes(xlValue) _
.MaximumScale = vInfo(3)
End Select
Next

‘Clear the collection once we’ve processed all the commands
Set goFunctionCommands = Nothing
End If

End Sub

The only caveat with this technique is that the tasks to perform shouldn’t trigger another recalc. To implement additional functions, you just need to add the new function to schedule the task, then add another Case in the Worksheet_Calculate event.

Happy Holidays!

Stephen Bullen
http://www.oaltd.co.uk

4 thoughts on “Functions that do things”

Hi Stephen
nice trick. If such tricks are part of your next book I’m even more looking forward getting it
:-)

Re: “The only caveat with this technique is that the tasks to perform shouldn’t trigger another recalc”
Not testes but would disabling application events solve this caveat. Probably would do this anyway in the sheet calculate event

Re: “user-defined-functions (UDFs) can’t do anything”
IITC you can change/add comments within a UDF

Frank

Neat trick Stephen.

Maybe good to include a static boolean in the calc event to avoid recursion:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Static bNoCalc as Boolean
If bNoCalc Then Exit Sub
bNoCalc=True
‘….
‘….
bNoCalc=False
End Sub

Frank: Yes, there are lots of ‘neat tricks’ in the book, though not this one. We had to draw the line somewhere (we do show how to add parameter help texts to UDFs for the Function Wizard, though!). Switching off events doesn’t prevent the recursion in this case, as Excel just schedules the calcuations to happen serially instead.

JK: That’s a good thing to include if you need to do something that will cause continuous recalcs, thanks.

Regards

Stephen Bullen