Need a macro to completely stop running, then start again on its own.

[Marcleonti]

Knowledge of how to use the Bloomberg terminal is not required to solve this problem, but if you do know how to control Bloomberg with Excel then feel free to tell me if there is a completely better way to accomplish my goals.

I have a list of about 20 tickers from B19 to B40 (the list has a few securities added and removed each day, so it's not always the same number of rows). I need the Bloomberg terminal to go to the company news page (CN) for each ticker, print the screenshot, go to the corporate actions page (CACS), print that screenshot, then go to the next ticker and repeat until all 40 screenshots have been printed. I also want to print six screens per page, so it is important the screens are printed in the correct order. Yes, we are going old-school here, printing on real paper with ink and stuff.

I'm can successfully send a command to the Bloomberg terminal to bring up the Print Settings dialog, but I don't know how to control it through VBA, so the print settings dialog appears then my macro displays a MsgBox telling the user to change the screens per page to six, then click OK to continue the macro. No problems so far (although if you know how to change the print settings without user interaction, I'm all ears). Next is my code that I need help with, then followed by the code to set the print setting back to 1 screen per page.

In the spreadsheet, each row that contains a ticker in column B also has the formula in column W to send a command to Bloomberg. Like this...

[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Column B[/TD]
[TD]Columns C - U...[/TD]
[TD]Column V[/TD]
[TD]Column W[/TD]
[/TR]
[TR]
[TD]Row 19[/TD]
[TD]AAPL US EQUITY[/TD]
[TD]............[/TD]
[TD]
[/TD]
[TD]=BTCRUN(V19, 2, "<"GO"><"PRINT"><go>"<go>, B19)</go></go>[/TD]
[/TR]
</tbody>[/TABLE]


<go><go><go><go>(ignore all the quotation marks, I couldn't get the greater than and less than signs to show up)

The formula runs as soon as it has all the inputs, so my macro has a loop that changes the value of the column V cells to CN, then back to "", then CACS, then back to "" again, then move to the next row, looped until both screens are printed for each ticker. I figured this way I can ensure each command goes to the terminal in the correct order and I could add Application.Wait commands as needed. It does not work reliably. Sometimes the screens are not printed in the correct order (especially the first few screens) and many of the screens are printed before they are finished loading. I added pretty lengthy wait commands but they don't help. I also modified the formulas to add pauses as recommended by Bloomberg, but that doesn't help either. It looks like this:
</go></go></go></go>
[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD]=BTCRUN(V19, 2, "<"GO"><"PAUSE">"0002"<"PAUSE"><"PRINT">"<go>, B19)</go>[/TD]
[/TR]
</tbody>[/TABLE]

This should add a two second pause, presumably after the GO <go>and before printing the screen. The macro definitely takes longer to run, but my prints aren't any better.</go>

Some of the documentation I found said the Bloomberg terminal doesn't like to run commands while the macro is still running. I thought a workaround for this would be to have my macro call a private sub to change the values of the cells in column V, but that doesn't do any better. I think the way to get Bloomberg to do what I want is to create a macro that changes the value of V19 to CN then stop. Completely. No macro running at all. Then somehow the macro will start itself up again, change V19 to CACS, then stop completely. Then a couple seconds later the macro starts again and changes the value of V20 to CN, then stops. Then CACS, stop. Then V21. Then V22. And so on.

So my question is, how do I make a macro send a command to Bloomberg, exit the macro so Bloomberg will execute the command, then start back up again on its own two seconds later?

I tried using a macro that runs whenever a cell in column V is changed, but it turns into a loop that never actually ends. As soon as it changes a cell value it jumps back up to the second line of the code and keeps looping until it gets to the last row of data (I used an if statement so it would only change the value in column V if there was a ticker in column B).

Here's my code that doesn't work.

Private Sub NewsAndCACS()
    Application.Goto ActiveWorkbook.Worksheets("Tracker").Range("A1")           'Ensure the correct tab is active
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row                                'Calculate how many rows of securities there are
    BBrun = Application.run("BTCRUNCmd", "PSET", 2)                             'Change Bloomberg print settings
    MsgBox "Set Bloomberg to print 6 screens per page, then click OK."

' Activate the BTC formulas for each ticker listed by copying CN then CACS into column V.
' As each formula is completed, it will automatically send the command to the Bloomberg terminal.
' This is where the problem is
    For i = 18 To LastRow                                                   'Starting with row 18 and continuing to the last row
        Call PrintCN(i)
        Application.Wait (Now + TimeValue("00:00:02"))
        Call PrintCACS(i)
        Application.Wait (Now + TimeValue("00:00:02"))
        Next i
    MsgBox "When the Bloomberg terminal is finished printing, click OK."

    BBrun = Application.run("BTCRUNCmd", "PSET", 2)
    MsgBox "Set Bloomberg print settings 
 to one screen per page."

Application.ScreenUpdating = True
End Sub

Private Sub PrintCN(xi As Integer)
    Cells(xi, 22).Value = "CN"
    Cells(xi, 22).Calculate
    Cells(xi, 22).Value = ""
End Sub

Private Sub PrintCACS(xxi As Integer)
    Cells(xxi, 22).Value = "CACS"
    Cells(xxi, 22).Calculate
    Cells(xxi, 22).Value = ""
End Sub

Note: I don't think the Cells().Calculate lines accomplish anything, but it was worth a try. I have been thinking I might try to put a time value three seconds into the future into some random cells, say Z1 and Z2, then my macro would have an Application.OnTime("Z1") or Application.OnTime("Z2") to trigger it to run again three seconds after it stopped. But I feel like that macro would have to be running while it waits for the times in Z1 or Z2 to occur, so that wouldn't work. Would it? Something like this...

Option Explicit
Public Answer as Integer, i as Integer

Sub NewsAndCACS()
    Answer = MsgBox "Want to print News and CACS?", vbYesNo
    If Answer = vbNo exit sub
    Range("Z1").Value = (Now + TimeValue("00:00:03"))
End Sub

'Three seconds later...

Sub TriggerCN()
    Application.OnTime("Z1") "PrintCN"
    Range("Z2").Value = (Now + TimeValue("00:00:03"))
End Sub

'Three seconds later...

Sub TriggerCACS()
    Application.OnTime("Z2") "PrintCACS"
    Range("Z1").Value = (Now + TimeValue("00:00:03"))
End Sub

'and here are the Subs that make the magic happen, but now I'm not sure how to
'pass i into them... I'm pretty sure I could just use cell Z3 as a placeholder and have
'the macro check for data in column B and exit the sub if column B is blank.

Private Sub PrintCN(xi As Integer)
    Cells(xi, 22).Value = "CN"
    Cells(xi, 22).Value = ""
End Sub

Private Sub PrintCACS(xxi As Integer)
    Cells(xxi, 22).Value = "CACS"
    Cells(xxi, 22).Value = ""
End Sub

Someone please help...

 

[Logit]

.
Hmmm ... the suggested macro utilizes Application.OnTime

 

[Marcleonti]

Hi MoshiM. Does that work differently than application.wait? If I put the line Application.OnTime now+timevalue("00:30:00") in my code, when the macro gets to that line does it actually stop running? I may not understand it completely, but I thought the macro was just "paused" in the background waiting for a certain time to occur before continuing (in this case thirty minutes from now). While the macro is waiting, can I do other things on Excel or any other application on the computer? Would it be possible to open a file at 7am, which auto-runs a macro which has Application.OnTime ("09:30:00") as the first line of code, then use my computer as normal while that file waits for 9:30 am to occur and run the rest of the macro?

 

[Marcleonti]

.
Hmmm ... the suggested macro utilizes Application.OnTime

Which suggested macro? Does this work fundamentally different than application.wait?

 

[MoshiM]

Hi MoshiM. Does that work differently than application.wait? If I put the line Application.OnTime now+timevalue("00:30:00") in my code, when the macro gets to that line does it actually stop running? I may not understand it completely, but I thought the macro was just "paused" in the background waiting for a certain time to occur before continuing (in this case thirty minutes from now). While the macro is waiting, can I do other things on Excel or any other application on the computer? Would it be possible to open a file at 7am, which auto-runs a macro which has Application.OnTime ("09:30:00") as the first line of code, then use my computer as normal while that file waits for 9:30 am to occur and run the rest of the macro?

No it wouldn't stop running, but if you follow the statement with End then all execution will stop.

Yes it would be possible to schedule Macros to run while still being able to interact with the workbook [With the Workbook Open Event .
https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime
http://www.snb-vba.eu/VBA_Application.OnTime_en.html

and if the Windows task scheduler is needed then
https://www.thespreadsheetguru.com/blog/how-to-automatically-run-excel-vba-macros-daily


TRY this

The following requires a worksheet named Variable_Storage or some sheet to store values on.

Private Sub NewsAndCACS()

Dim ACS As Worksheet, Skip_Procedures As Boolean, iLoop As Long, _
VAB As Worksheet, Skip_P_Range As Range, i As Integer

Set VAB = ActiveWorkbook.Worksheets("Variable_Storage") 'Fictional Worksheet used to store variables

With VAB

    Set Skip_P_Range = .Range("A1")
    Set iLoop_Range = .Range("A2")
    
    Skip_Procedures = Skip_P_Range.Value2   'Boolean
    
    iLoop = iLoop_Range.Value2             'Integer/Long
    
    Skip_P_Range.Value2 = True
    
End With

If Skip_Procedures = False Then

    Set ACS = ActiveWorkbook.Worksheets("Tracker")
    
    If Not ActiveWorkbook.ActiveSheet Is ACS Then ACS.Activate 'Ensure the correct tab is active
      
    BBrun = Application.Run("BTCRUNCmd", "PSET", 2)        'Change Bloomberg print settings
    
    MsgBox "Set Bloomberg to print 6 screens per page, then click OK."
    
End If

lastrow = Cells(Rows.Count, 2).End(xlUp).Row           'Calculate how many rows of securities there are
' Activate the BTC formulas for each ticker listed by copying CN then CACS into column V.
' As each formula is completed, it will automatically send the command to the Bloomberg terminal.
' This is where the problem i

If 18 + iLoop <= lastrow Then

    For i = 18 + iLoop To lastrow                 'Starting with row 18 and continuing to the last row
     
        If iLoop Mod 2 <> 0 Then 'If odd numbered loop
        
           Call PrintCN(i)
           
           iLoop_Range.Value2 = iLoop_Range.Value2 + 1
           
           Application.OnTime Now + TimeValue("00:00:02"), "NewsAndCACS"
           
           End ' Stop all execution
           
        Else 'If even numbered loop
        
           Call PrintCACS(i)
           
           iLoop_Range.Value2 = iLoop_Range.Value2 + 1
           
           Application.OnTime Now + TimeValue("00:00:02"), "NewsAndCACS"
           
           End 'Stop all execution
           
        End If
     
    Next i
   
End If

    Skip_P_Range.Value2 = False
    iLoop_Range.Value2 = 0
    
    MsgBox "When the Bloomberg terminal is finished printing, click OK."

    BBrun = Application.Run("BTCRUNCmd", "PSET", 2)
    MsgBox "Set Bloomberg print settings to one screen per page."

Application.ScreenUpdating = True
End Sub

 

[Logit]

Which suggested macro? Does this work fundamentally different than application.wait?

Post #4 .

 

[Logit]

.
My apologies .... there is one line of code omitted from the macro. This is the corrected macro :

Option Explicit    '<-- this statement at the top of everything makes you declare all your
        'variables. Doing so helps prevent coding errors that might not otherwise
        'be known.




Sub shwMsg()
    Application.DisplayAlerts = False                '<-- disables all those nasty little
                                ' alert messages you receive when the code
                                ' wants to do something.
    ActiveWorkbook.Save                        '<-- Obviously it saves the workbook


'calls MyMacro every 3 seconds.
    Application.OnTime Now + TimeValue("00:00:03"), "RunMyMacro"     'runs your macro every 3 seconds. Leave this name as is.
    Application.DisplayAlerts = True                
End Sub




Sub RunMyMacro()    


'Place a call to your macro here
Call MyMacro                            'Change the name here to the name of your
                                'macro that should be run every 3 seconds.
[B][COLOR=#ff0000][FONT=Verdana]shwMsg[/FONT] [/COLOR][/B]                               'Or you can simply paste all of your macro
                                'code here instead.
End Sub




Sub stopMacros()   '<-- if you want, you can create a command button on your worksheet connected to this
           ' macro. When you click the button it will stop all of these from running.
    Exit Sub
End Sub

 

[MoshiM]

BTW have you tried using DoEvents?

 

[boyko]

Did you ever find a solution to this problem?