Trying to Pause Macro [Bloomberg]

I got a macro that is trying to populate a table with information from an outside source (Bloomberg), but the macro runs so fast it doesn't have time to populate.

I'm trying to find a way for the macro to pause so that the data can finish loading before it saves and exits.

I've tried application.wait and it paused, but it pauses the whole procedure of the data loading also.

Can anybody help or have any suggestions? Thanks .:confused:

Hi,

I use Bloomberg too. Are you sure that the problem isn't that the formulas (ie. DDE links) can't update while the code is running, rather than it running so fast? If I need data pulled in during runtime then I pull it in using VBA (the bloomberg API) and store it in a variant array, rather than using formulas.

Hope that helps...

That might be what the problem is. My first assumption was that they didn't have time to populate being that normally it takes 5-10 seconds for all the columns to fill in...And the macro only takes 2 seconds to run and save.

Could you explain the "variant array" if you don't mind? Thanks

Hi,

Please can you post your current macro - I assume that, amongst other things, it is refreshing the bloomberg formulas? Also, what data is being pulled into the worksheet? When we can see this hopefully we'll be able to make some good suggestions...

We are trying to make this an overnight routine. We have a batch file that will run a script that generate an .cvs file with the needed cusips and BB column fields. There will always be the same column headings but the cusips and the number of them may change. So In this VBA code I am opening the .csv sheet that the script created and running the macro on it and then saving it and closing it. It runs find until it is time for the data to load from BB.


Sub BloombergMFUND()

' Populate bloomberg-MFUND.csv Macro


Workbooks.Open Filename:="Q:\fundprice\bloomberg\test\bloomberg-MFUND.csv"

Cells.Select
Cells.EntireColumn.AutoFit
Range("B2").Select
ActiveCell.FormulaR1C1 = "=BDP(RC1,R1C)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:O2"), Type:=xlFillDefault
Range("B2:O2").Select
Range("O2").Select
Range("B2:O2").Select
Selection.AutoFill Destination:=Range("B2:O105"), Type:=xlFillDefault

'This is where I tried to pause it but it obviously didn't work
Application.Wait Now + timevalue("00:00:15")


Range("B2:O105").Select
Cells.Select
Cells.EntireColumn.AutoFit

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="Q:\fundprice\bloomberg\test\bloomberg-MFUND.csv", FileFormat:=xlCSVWindows

ActiveWorkbook.Close

Application.Quit


Here is part of the actual data that it is pulled in the worksheet

CUSIP................................LAST_TRADE..........LAST_UPDATE_D T
00141T189 Equity Cusip........#NAME?................#NAME?


its several more columns and alot of cusips

The #NAME? error suggests that the Bloomberg library containing the BDH() function is not installed.

I understand that. I'm working on the macro on a different cpu than the one I"m goin to be running the macro on.

I found something that worked for me thought.

I used the "ontime method"..I just split the macro into two.

While the first macro waited for whatever time (20 seconds) I put in the "ontime method" the fields were able to load in and then the second macro ran and saved the spreadsheet for me.

The Application.Ontime() method is one way to allow the formulas to update, but it's not very efficient and it's not 100% reliable (ie. How long a gap do you need to be 100% sure the formulas have updated?). The alternative is not to use formulas but to pull in the values into a VBA array and use that to populate the worksheet. I posted examples of both of these approaches here:

http://www.mrexcel.com/forum/showthread.php?t=414626
In case the link gets broken at a later date I will repost the important parts here.

For the Application.Ontime() method:

I added a reference to the BloombergUI library (via VBE | Tools | References) and then wrote the following code in a standard code module:

Dim xlCalc As XlCalculation
Sub Test1()
'early bound - reference to Bloomberg

'save the calculation setting and then set to automatic
xlCalc = Application.Calculation
Application.Calculation = xlCalculationAutomatic

Sheet1.Range("C2:H4").Formula = "=BDP($B2,C$1)"
BloombergUI.RefreshAllStaticData
Application.OnTime Now + TimeValue("00:00:02"), "HardCode"
End Sub

Sub HardCode()
Sheet1.Range("C2:H4").Value = Sheet1.Range("C2:H4").Value
Application.Calculation = xlCalc
End Sub

The timing is not guaranteed to work. An alternative approach which avoids formulas altogether is required.


Alternative:

Using the helpfile information in that area we can build a more robust solution to this using the Bloomberg Data Type Library. Go to Tools | References and add a reference to this library. This code can then be used to populate the cells:

Sub Test2()
Dim vResults, vSecurities, vFields
Dim objBloomberg As BLP_DATA_CTRLLib.BlpData

'fill our arrays - must be 1 dimension so we transpose from the worksheet
With Application.WorksheetFunction
vSecurities = .Transpose(Sheet1.Range("B2:B4").Value)
vFields = .Transpose(.Transpose(Sheet1.Range("C1:H1").Value))
End With

Set objBloomberg = New BLP_DATA_CTRLLib.BlpData
objBloomberg.AutoRelease = False

objBloomberg.Subscribe _
Security:=vSecurities, _
cookie:=1, _
Fields:=vFields, _
Results:=vResults

Sheet1.Range("C2:H4").Value = vResults
End Sub



Hope that helps...