Is there a way to enter a formula from the formula builder via the COM API?
I coded a marco for Excel VBA. It enters a formula, which I have designed using the formula editor, into a cell of the excel spreadsheet. In oreder to achive this, I am using the following command:
Cells(1, 1).FormulaLocal = formula
It works just fine. However the data retrievel won't get updated until the macro has finished, which is beginning to become a major problem. Is there any way that a formula entered in a similar fashion gets updated? I looked into the API, but I am pretty lost there.
Thank you for any help.
Andreas
Best Answer
-
Hi Andreas,
The behavior you experienced is expected. The worksheet function you paste into the worksheet and your VBA macro are executed on the same thread. The function cannot return the data into the worksheet until your macro exits and makes the thread available to the function. If I understand correctly all you're trying to do is retrieve the data and get notified when the data is ready, right? There are multiple ways you can achieve that. You can use Eikon COM APIs, which will eliminate any need for worksheet functions at all. All the data you retrieve using =TR, =RHistory or =RSearch worksheet functions can be retrieved using COM objects. This portal provides extensive documentation, code samples and tutorials to help you. A simpler option is to use MACRO parameter in the worksheet function you paste. This parameter allows the function to run a macro once the function finished retrieving the data into the worksheet. The workflow will then be: your macro will need to exit after pasting the worksheet function into a cell. The function will retrieve the data and launch another macro. In this latter macro you can process the data retrieved. For details on the syntax see Help section on function parameters for the function you use and look for the parameter named MACRO. Also see attached a quick example.
Best regards,
Alex Putkov
0
Answers
-
Thank you Alex! Your anser was of great help. I did it your way and it works as intended now.
0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛