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

Posted in Uncategorized

4 thoughts on “Functions that do things

  1. 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

  2. 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

  3. 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

  4. If I have the following formula in a worksheet to bring up a message box

    =IF((F4-E4)>=15,PromptPayment()+F4-E4,F4-E4)

    How can I use a function to transfer some information from different cells to a new worksheet
    if the formula above works?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *