Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


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

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

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

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)



'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)
    ActiveWorkbook.ForceFullCalculation = _
        Not (ActiveWorkbook.ForceFullCalculation)
End Sub 

The UDF code...


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
' =AddrToVal(cell address)
    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
    GoSub Evaluate
    Exit Function
    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)
        AddrToVal = AddrToVal & eval & Mid(Form, i, 1)
    End If
    On Error GoTo 0
    p1 = i + 1

End Function 

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

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.

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

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:


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

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.

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

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.

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

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.

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

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.

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

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)
End Sub

Private Sub myAppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
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."
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


White Paper - The Criticality of the E/E Architecture
Modern vehicles are highly sophisticated systems incorporating electrical, electronic, software and mechanical components. Mechanical systems are giving way to advanced software and electronic devices, driving automakers to innovate and differentiate their vehicles via the electric and electronic (E/E) architecture. As the pace of change accelerates, automotive companies need to evolve their development processes to deliver and maximize the value of these architectures. Download Now
White Paper - Model Based Engineering for Wire Harness Manufacturing
Modern cars, trucks, and other vehicles feature an ever-increasing number of sophisticated electrical and electronic features, placing a larger burden on the wiring harness that enables these new features. As complexity rises, current harness manufacturing methods are struggling to keep pace due to manual data exchanges and the inability to capture tribal knowledge. A model-based wire harness manufacturing engineering flow automates data exchange and captures tribal knowledge through design rules to help harness manufacturers improve harness quality and boost efficiency. Download Now
White Paper - Modeling and Optimizing Wire Harness Costs for Variation Complexity
This paper will focus on the quantification of the complexity related costs in harness variations in order to model them, allowing automated algorithms to optimize for these costs. A number of real world examples will be provided as well. Since no two businesses are alike, it is the aim of this paper to provide the foundational knowledge and methodology so the reader can assess their own business to model how variation complexity costs affect their business. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close