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
