Cube formulas either don't refresh complete or excel hangs when called from VBA

I first set up a report in an Excel sheet. This report calls an ID from another sheet and should then refresh the links to a SSAS cube (these are in cubeformulas). Due to the cube architecture and report design, the refreshing takes anywhere from 10 to 100 sec.

When I call RefreshAll from a macro, the macro continues before all data is synchronized with the cube. Making the report look way more similar to one another than they should.

Browsing the fora and the web, my eye fell on the Application.CalculateUntilAsyncQueriesDone object. I tried to implement it, but I think the sheet is sending batches to the cube such that when the first batch is done the flag goes up and the macro continues.

Somebody can help me out with this?

code is below. I really hope it's something easy to solve (I'm quite new to VBA and cubes)

With kind regards, LM

Please Login or Register  to view this content.