UDF not recalculating when switching b/t worksheets...?

Question

UDF not recalculating when switching b/t worksheets...?

justhumm (Structural)

(OP)

31 Oct 18 18:38

I threw together this UDF (code below), based on a previous (closed) thread's discussion...

I have been running it through a XLAM add-in for a few months, now. And it's generally working the way I want it to. The only major issue I've been running into is that sometimes when switching between worksheets that contain cells that use the UDF, the UDF is not recalculating automatically when switching worksheets. For example, input UDF in cell on Worksheet1, switch to Worksheet2, do something, switch back to Worksheet (image results below)...

I have been forcing the workbook to recalculate, for the time being, but can anyone suggest a better way of dealing with it? (besides making it volatile)

Thanks!

CODE --> VBA

'Callback for customButton1 onAction
Sub Macro1(control As IRibbonControl)
    ' as of March 2018, the AddrToVal UDF sometimes needs to be kickstarted
    ' .ForceFullCalculation is the rough equivalent of Ctrl + Alt + Shift + F9
    ' https://fastexcel.wordpress.com/2015/05/19/excel-forcefullcalculation-trading-off-editing-speed-vs-calculation-speed/
    ActiveWorkbook.ForceFullCalculation = _
        Not (ActiveWorkbook.ForceFullCalculation)
    Application.Calculate
    ActiveWorkbook.ForceFullCalculation = _
        Not (ActiveWorkbook.ForceFullCalculation)
End Sub

The UDF code...

CODE --> VBA

Option Explicit     'Require that all variables are explicitly defined.

Public Function AddrToVal(rCell As Range) As String
'==================================================================
' This UDF is similar to the standard Excel FORMULATEXT() function,
' but it displays the cell values/contents instead of the cell address
'==================================================================
' USE / CELL ENTRY
' =AddrToVal(cell address)
'==================================================================
    'Application.Volatile
    Dim i As Integer, p1 As Integer, p2 As Integer
    Dim Form As String, eval As String
    Dim r As Range
    
    Form = rCell.Formula
    Form = Replace(Form, "$", "")
    AddrToVal = "="
    
    p1 = 2
    For i = 2 To Len(Form)
        Select Case Mid(Form, i, 1)
            'Case "(", ")", ",", "+", "-", "*", "/", ":", "&", "^"
            Case "'*'!", "(", ")", ",", "+", "-", "*", "/", ":", "&", "^"
                GoSub Evaluate
        End Select
    Next
    GoSub Evaluate
    Exit Function
    
Evaluate:
    p2 = i - 1
    eval = Mid(Form, p1, p2 - p1 + 1)
    On Error Resume Next
    Set r = Range(eval)
    If Err.Number = 0 Then
        'AddrToVal = AddrToVal & Range(eval).Value & Mid(Form, i, 1)
        AddrToVal = AddrToVal & Range(eval).Text & Mid(Form, i, 1)
    Else
        AddrToVal = AddrToVal & eval & Mid(Form, i, 1)
        Err.Clear
    End If
    On Error GoTo 0
    p1 = i + 1
    Return

End Function

Agent666 (Structural) · Answer 1 · 2018-10-31 20:39:48

If the forced recalculate works, an interim solution to automate it (rather than pressing a button in the ribbon) would be to wrap up the essence of the ribbon button code into a Workbook.Activate Event. You might be able to isolate just the changing workbook selection (vs other changes) by storing name of current workbook in a global variable or writing it to memory and then comparing it to the newly selected workbook to only then initiate the recalculation, and avoid the recalculate on simply changing sheets within the same workbook (which I gather is not an issue).

See Link, for info on the workbook activate event.

See Link, for how to store variable in memory and retrieve them.

I have come across a similar issue with some of my macros, when they are finished certain UDF's don't recalculate. I simply solved it by adding an Application.Calculate within the subs that cause an issue.
Check for other routines that change values on the sheet on which the UDF in question relies on, its an excel 'feature' that the UDF doesn't update if you change values of inputs on the sheet using VBA.

I know its not a solution, but it may take out some of the issues with having to manually recognise and deal with the issue each time it occurs.

justhumm (Structural) · Answer 2 · 2018-11-09 20:31:23

I had been thinking of something along the lines of an event change trigger, but am not sure how I would go about implementing it properly.

I would think that I'd need to have a Workbook_Activate (for instance) sit inside of my XLAM (ThisWorkbook), but that doesn't seem to be getting triggered when switching sheets in another workbook (XLSX).

...in the very little amount of time that I've spent playing with it...

I haven't had a chance to look into this, yet, but it sounds like it MIGHT be something along the right line:

http://www.cpearson.com/excel/AppEvent.aspx

Agent666 (Structural) · Answer 3 · 2018-11-10 10:34:01

Your worksheet activate event needs to be in the actual workbook being activated. I'm not aware of any way to place an event procedure within an addin and have it run on a separate workbook. The reason it's not working is you are not activating the Addin, because its simply in the background.

So you would need to turn workbook into a .XLSM and add the code there.

justhumm (Structural) · Answer 4 · 2018-11-10 18:28:48

I was playing around for a little while after work and found a couple of pages about event handlers.

I put an event handler and a connector macro (whatever that is) in the XLAM add-in. And I was able to trigger them by events (changing between worksheets, etc.) in a separate workbook (XLSX) and automatically run a simple macro residing in the XLAM.

The "issue" that I kept running into was that I had to manually run the connector macro every time I closed and restarted Excel.

So I want to look into that more when I have the time and ambition.

Agent666 (Structural) · Answer 5 · 2018-11-10 19:33:00

Mind sharing the connector macro?

Have you tried calling the 'connector macro' from the Addins workbook_open event? That will run it every time you open excel.

justhumm (Structural) · Answer 6 · 2018-11-11 00:32:35

Sure. I'll post an Excel file or a copy of the code when I'm back in the office on Monday.

I haven't played with the connector, yet. I was just happy and surprised that I got the events triggering, but I'll have to look into the workbook_open route.

justhumm (Structural) · Answer 7 · 2018-11-12 15:37:40

I added a code module and CLASS module to my XLAM project, but I imagine you would get the same results from putting them in you PERSONAL.XLSB.

I added the events that I want to trigger in a new class module.

CODE --> module

Option Explicit
' ==========================
' VBA events
' https://powerspreadsheets.com/excel-vba-events/
' https://support.microsoft.com/en-us/help/213566/how-to-create-application-level-event-handlers-in-excel
'
' Application level events are not available by default.
' The Application event can be created in a Dedicated Class Module
' or in an existing object module like ThisWorkbook.
'
' Make sure, every time you add an event, that you go to the EVENTS module and RUN the connector
' ==========================
'
Public WithEvents myAppEvent As Application

Private Sub myAppEvent_SheetActivate(ByVal Sh As Object)
    MsgBox "HOLY CRAP! THE SHEET JUST CHANGED!!!"
End Sub

Private Sub myAppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    MsgBox "HOLY CRAP! WE'RE ABOUT TO PRINT SOMETHING!!!"
End Sub

And then I added connector macro in a typical code module. For some reason, so far, this macro needs to be run every time excel is started in order for the events to trigger in the class module.

CODE --> module

Option Explicit
' ==========================
' VBA events
' https://powerspreadsheets.com/excel-vba-events/
' https://support.microsoft.com/en-us/help/213566/how-to-create-application-level-event-handlers-in-excel
' ==========================
Dim myObject As New Event_Handler

' "If this statement isn't executed,
' the Application-level event-handler procedures won't work appropriately."
'
' YOU MANUALLY HAVE TO "RUN" THIS MACRO SUB (AT LEAST ONCE)
' TO CONNECT TO THE EVENT HANDLER!!!...so far...
'
Sub connectMyAppVar()
    Set myObject.myAppEvent = Application
End Sub

Once you create the event application (I called it myAppEvent) in the class module, you can select it from the LEFT DROP-DOWN menu and then all of the available procedures can be selected from the RIGHT DROP-DOWN menu.

Come Join Us!

Posting Guidelines

UDF not recalculating when switching b/t worksheets...?