AnsweredAssumed Answered

Using VBA to search for Event Frames always returns "resize to show all values"

Question asked by Bakerr on Mar 16, 2020

I made an excel document that grabs timestamps out of PI and fills in an operator's daily log automatically.  The sheet became so large that it would take several minutes to calculate.  I started over using VBA to only write in the values instead of having thousands of PIARC formulas all over the workbook.  I have most of the workbook completed and it can recalculate in under 2 seconds.  However, I have one more section to complete that uses Event Frame timestamps.  When I use VBA to evaluate the Event Frames formula, I always get back "resize to show all values."  I understand that I am trying to place an array within 1 cell and this probably is impossible.  Since I have already solved the slow re-calculate problem, having a 12 PI formulas within the workbook will be fine.  However, even when I leave the formula within the cell, I still get the "resize to show all values" error unless I manually select each cell and hit apply on the Explore Events window that pops up.  I am trying to get this to report every morning automatically.  My VBA runs most of the code, then I need the Event Frame formula to search within timestamps that my code found, then the rest of the VBA will finish.  Just running Application.Calculate will not update the Event Frame formulas.  I have tried using VBA to select the cell then SendKeys to hit apply, turning off Display Alerts, and changing the settings of DataLink to "Full Calculate."  None of this has worked.  Can you help me figure out how to force the formula to "Resize?"

Outcomes