Custom excel function not updating
If you think something should be on the list, just leave a comment. Now before I get into the Macro Example and give you the VBA code, let me first show you how to use these example codes.
In case the example says that you need to paste the code in the worksheet code window, double click on the worksheet object and copy paste the code in the code window. Once you have copied the code in the VB Editor, here are the steps to run the macro: In case the code is pasted in the worksheet code window, you don't need to worry about running the code.
Plot By = xl Rows Next Next I have another problem of refeshing charts. Paste 'Reposition to original position Active Chart. All worked fine in Excel 2003, but in Excel 2007 the chart seems to lose all connection to its data, although manually changing data values in two column triggered a recalc. Refresh i Count = i Count 1 Next co End If If Not chrt Is Nothing Then chrt. On the dashboard, there are some pie charts with data series tied to cells which contain these named ranges (the problem also occurs if the data series target cells contain the UDFs directly, bypassing the named ranges).
Programatically I inserted the following into a Macro that I linked a button to, it refreshes all of my charts quick enough for a workaround to a known bug. The best explanation I have been able to find so far is this: If I understood it right, if there are more than 65536 formulas that have another cell as a reference in your file, Excel starts to optimize the calculation and in some cases graphs don't update correctly anymore. It had the Private Sub Worksheet_Calculate() 'Recalculate the charts data page to force the charts to update. Maybe this helps As i tried pretty much ALL the presented solutions and since none worked in my case, I'll add my two cents here as well. The consensus on this issue seems to be that we need to somehow force excel to redraw the graph since it is not doing it when it should. I just had the same problem, and also found that the line would only display if I put in bad data (characters instead of numbers).
' Force the charts to update Set sht = Active Sheet For Each co In sht. I found that if I went back into the "Select Data Source" msg box and unchecked/rechecked the "show data hidden in rows and columns" that the chart refreshes. After that, using radio buttons to select data to be presented on graphs did not update the graphs anymore. It then asked me to change a value and watch the percentage change, then to look at the pie chart and see the update. I looked at the data source for the pie chart, and the range was bizarre. If I closed everything and opened just the file I was having problems with the charts where dynamic like they should be. Be sure you are copying and pasting pictures before you go through all the other motions....
When generating the charts automatically, some charts appear over and cache the text in the sheet. My solution has been to make all charts on the active sheet invisible before the change in data, then make them visible again and call chart refresh for good measure. I change a cell value which contains, for example, the date range to base the dashboard on, and the named ranges and UDFs are forced to calculate.
Now, let's get into the useful macro examples that can help you automate work and save time.
I have an Excel document (2007) with a chart (Clustered Column) that gets its Data Series from cells containing calculated values The calculated values never change directly, but only as a result of other cells in the sheet changing When I change other cells in the sheet, the Data Series cells are recalculated, and show new values - but the Chart based on this Data Series refuses to update automatically I can get the Chart to update by saving/closing, or toggling one of the settings (such as reversing x/y axis and then putting it back), or by re-selecting the Data Series Every solution I have found online doesn't work None of these update or refresh the chart I do notice that if I type over my Data Series, actual numbers instead of calculations, it will update the chart - it's as if Excel doesn't want to recognize changes in the calculations Has anyone experienced this before or know what I might do to fix the problem? It happens to be a problem of refreshing the generated charts. ( It only seems to be visible charts that have this problem updating ). Visible = False i Count = i Count 1 Next co End If If Not sht Is Nothing Then i Count = 1 For Each co In sht. However, the pie charts do not update--for some reason, other types of charts do. XValues = "=tbl Ref[Secs]" End With End Sub My two cents for this problem--I was having a similar issue with a chart on an Access 2010 report.
Thank you I have run into this same issue - not sure why, and when it happens the only way I have ever gotten the chart to force update is to change something in the chart definition itself, which can easily be done via VBA as in: There may be a better answer that gets to the bottom of the problem - but I thought this might help. Formula = temp Next sc Next co This a great solution because it updates 1000 graphs at once without looping through them all and changing something individually. When I zoom in or zoom out, I can get the expected results. This works for me and also handles similar issues with charts as well as chart objects. And by the way, these are chart objects, not chart sheets. I was dynamically building a querydef, setting that as the rowsource on my report and then trying to loop through each series and set the properties of each series.
I have a macro that changes values that are the data for a chart. Basically I'm running a dashboard, and I have a bunch of named ranges that are populated with return values from some UDFs.
Left = Int Temp Left Next co End Sub Just spent half a day on this myself. Select End If End If On Error Go To 0 I had the same issue as the poster.