Paste Formula as Value (Using Exacta Server)

[Bart196]

Hi Guys,

I'm new to VBA. I'm working on my first macro. I want to paste a formula (using the Exacta Server - Company Server) as a value (instead of a formula)... The caveat is excel doesn't calculate the formula UNTIL the macro ends. Is there a way to FORCE excel to calculate the formula while VBA is running?


ActiveCell.FormulaR1C1 = "=RTD(""exacta.server"",, ""NBBO_EQUITY"", RC9, ""AskPrice"", RC3, RC2)"


The formula (shown above) pastes as a formula. It shows the results as "N/A". After the macro finishes, excel will calculate the formula (using Exacta Server - Company server). Traditionally I would write the next line of code to paste as special value... but because the calculation ISN'T complete... I can't do this.

Thoughts?

 

[Coding4Fun]

Not positive this will work but is an idea.

'Calculate and pause for 10 seconds
Sheets(1).Calculate
Application.Wait (Now() + TimeSerial(0, 0, 10))