- Home /
- Eikon Data APIs /

Hello,

Application.Run "PLRefreshWorksheetEventHandler"

This line of code does not refresh Matrix Formula so my question is the following one : How do I recalculate/refresh reuters Matrix Formula with VBA ?

Matrix formula for exemple :

=FxCalcPeriod($P$4;S9;M9;"FROM:MMTRADE")

Kindly,Dorian

Comment

Best Answer

I reproduced the behavior you described. This behavior is expected. FxCalcPeriod function is not recalculated on F9 in step 5 because Excel sees that the values of none of its arguments changed, hence it thinks there's no need to recalculate it. When you reenter the formula in P4 Excel treats it as an update to the argument of FxCalcPeriod and recalculates the latter. You could force Excel to recalculate the whole calculation tree by hitting Ctrl+Alt+F9 (or in VBA use Application.CalculateFull). In other words replacing F9 with Ctrl+Alt+F9 in step 5 will ensure FxCalcPeriod function in your spreadsheet gets recalculated. Or you could replace the formula in P4 with =RtToday()+RtNow(). Then FxCalcPeriod will be recalculated on F9 in step 5 because the value in P4 will change on F9. Adding current time to today's date in P4 ensures that the value returned by the formula when it's recalculated on F9 is different from the previous value, which will ensure all the dependences of P4 will be recalculated on F9 as well. Yet another option is to replace =RtToday() in P4 with =TODAY(). The latter is a built-in Excel function. It is volatile, which means Excel treats it as if its value cannot be assumed to be the same from one moment to the next even if none of its arguments changed. In this case again, hitting F9 in step 5 will have FxCalcPeriod function dependent on P4 recalculated.

Long story short, "refresh" of calculation functions such as FxCalcPeriod is controlled by Excel recalculation.

Hello this ain't exact, in fact I got the following :

Excel calculate does not refresh formula and populate value, the only way to recalculate all is to go in the cells on the right of "Trading Date", click the formula ( =RtToday() ) and press ENTER. I don't manage to do it thanks to vba and thats very annoying to do it every day with hand ... Do you have any idea of how to populate calculation after I login reuters into excel ...

Thanks in advance ..

1620286414164.png
(33.4 KiB)

My previous response is indeed accurate, although it may not specifically address your particular use case. RtToday function is refreshed by Excel recalculation.

Excel recalculation is a very complicated subject. From the screenshot you provided I cannot see the full picture and cannot tell you what may be wrong. The only way I can do the latter is if I can replicate the issue on my end. One thing that may be affecting you is that quite commonly Excel recalculation tree becomes corrupt. In this case you can try rebuilding the calc tree using Ctrl+Alt+F9 shortcut. So, if you recalculate a worksheet and, contrary to expectations, some of the functions fail to update, try rebuilding the calc tree and see if that solves the problem.

If you'd like me to try reproducing the behavior you experience, please share the spreadsheet I could use for replication and provide detailed replication procedure I need to follow.

@Alex.Putkov12 I just added the way to reproduce the example below, thanks in advance

@Alex Putkov.sure,

1 : Open the following excel (Here to download : https://we.tl/t-TTshCaMnX0 )

2 : Go in the reuters tab in ribbon the click signout

3 : Click in Cells P4 then click at the end of the formula and then press ENTER ( you should get message in excel as shown on the last screenshot

4 : Go in the reuters tab in ribbon then click offline

5 : Press F9 and "Not Signed" In will stay, do as step 3 and the "Not Signed in" will disappear and leave place to value ...

1620322480136.png
(13.6 KiB)

1620323036978.png
(122.5 KiB)

Add to your watch list to receive emailed updates for this question. Too many emails?
Change your settings >

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Alpha
- App Studio
- Block Chain
- Bot Platform
- Connected Risk APIs
- DSS
- Data Fusion
- Data Model Discovery
- Datastream
- Eikon COM
- Eikon Data APIs
- Electronic Trading
- Elektron
- Intelligent Tagging
- Legal One
- Messenger Bot
- Messenger Side by Side
- ONESOURCE
- Open Calais
- Open PermID
- Org ID
- PAM
- ProView
- ProView Internal
- Product Insight
- Project Tracking
- RDMS
- Refinitiv Data Platform
- Rose's Space
- Screening
- Side by Side Integration API
- TR Knowledge Graph
- TREP APIs
- TREP Infrastructure
- TRKD
- TRTH
- Thomson One Smart
- Transactions
- Velocity Analytics
- Wealth Management Web Services
- Workspace SDK
- World-Check Data File
- 中文论坛
- Explore
- Tags
- Questions
- Badges