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]

.

Option Explicit


Sub shwMsg()
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
'calls MyMacro every 3 seconds.
    Application.OnTime Now + TimeValue("00:00:03"), "MyMacro" 
    Application.DisplayAlerts = True
End Sub


Sub MyMacror()

'Place a call to your macro here
Call MyMacro


End Sub


Sub stopMacros()
    Exit Sub
End Sub

 

[Marcleonti]

Hi Logit. Thanks for the response. When you get a minute can you walk me through this? I'm not exactly sure how to implement it, and I'd love to know what it is actually doing.

 

[Logit]

.
Ok ... you can read the comments below. I did make a slight edit to the macro because I realized it used "MyMacro" in two different places which would cause confusion.
Hopefully that has been cleared up somewhat for you.

Let me know if you have questions....

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.
								'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

 

[Marcleonti]

Hi Logit. Thanks again, but I don't think this will work. I need my VBA to send 40 commands to another program, but the other program won't run unless the VBA stops running completely, even if it is for just a second. My macro has sub A calling sub B and sub C with a loop. While sub A is running, sub B starts and stops, then sub C starts and stops, then sub B starts and stops, then C, then B, then C, and so on. The problem is sub A never stops, so the other program won't execute the commands it receives from B and C.

Your code looks like it does pretty much the same exact thing mine does. Sub shwMsg runs and calls RunMyMacro every three seconds. Sub RunMyMacro calls MyMacro. Both of these subs start and stop repeatedly, but shwMsg continuously runs. I'm looking for a way to have ALL macros to stop running, then start back up again. Is there any way a macro can start without another macro running or without user input? I need an event that happens on its own and can be predicted and timed.

I can have a macro run every time I put a value into a certain range of cells, but I don't want the user to have to enter 40 values to get the 40 commands to send. And I can't tell VBA to write the values to the cells because when one macro triggers another macro, one of the two macros are running until the loop is complete.

I'm going to try your code in the morning, but I predict I'll get the same results I've been getting with my code.

Why is the file being saved? Is this important? I really don't want to save the file because then I'll need to write more code to undo the changes when I'm done, then save again. I want this file to open in the exact same state every day, download the info it needs, process that info, send 40 commands to the other program, then discard all changes and data and close.

Thanks for your help,
Marc

 

[Logit]

.
Whew ! The description of what you need to accomplish has overwhelmed my mind. I'll need some time to break it down piece by piece. Of course it doesn't
take much at this point to turn my grey matter to mush.

The provided code saves the workbook because I believed you were permanently storing whatever data was being inputted to the workbook. You can
remove that line if needed.

Looking at your original posted code there is the term "ticker". Usually this indicates the program is accessing a Stock Exchange or similar and downloading Stock prices.
Is this the purpose of your workbook ? If not, what is the workbook for ?

Let me study your last post and see if I can wrap my mind around it ... unless you have a more basic description ?

 

[Logit]

.
Ok .... I re-read your description .... believe I have a grasp of what you are seeking.

Everything seems to hinge on Macro A completely stopping ... then at some time later (a few seconds ?) Macro A needs to start again on its' own.

Well ... you could create ANOTHER macro that starts and stops Macro A but I presume having the other macro running in the background is going to cause the same problems
you are currently experiencing ? If so .... that leaves the only answer to having a separate process from the workbook that controls when the workbook / macros does what it
is programmed to do. And I believe that would require closing the workbook ... then re-opening it so the macros will run again. You would need to do that constantly which
most likely is going to cause some other problems in the future.

I don't understand why having your workbook macro sitting "idle" while the executable program does its' thing would be an issue. I've had other macros that ran an
executable ... the executable did its thing ... then the workbook macro closed the executable. All of that was done with a timer macro.

Let us know what happens with the timer macros provided.

 

[Marcleonti]

Yeah, that is exactly the issue. I tried your macro, it just add another layer of one sub calling another. I'm not really sure why, but that's just the way the Bloomberg terminal works - at least according to the documentation I've read. I can send the commands to the Bloomberg terminal as slowly as I want, but the Bloomberg terminal doesn't start processing anything until the last command is sent and the macro is no longer running. At that time, all the commands are processed, some in the incorrect order, and some of the screens are printed before the pages have completed loading. Opening and closing the workbook 40 times to print the documentation I need isn't an effective solution. I think I'm going to close this post and start a new one looking for someone who has had this issue before. There has got to be some way to do this. I'm guessing there must be some completely different process I'm not aware of.

 

[Logit]

I'm guessing there must be some completely different process I'm not aware of.

Me too !

I'll be watching to see what develops. Best wishes !

 

[MoshiM]

Yeah, that is exactly the issue. I tried your macro, it just add another layer of one sub calling another. I'm not really sure why, but that's just the way the Bloomberg terminal works - at least according to the documentation I've read. I can send the commands to the Bloomberg terminal as slowly as I want, but the Bloomberg terminal doesn't start processing anything until the last command is sent and the macro is no longer running. At that time, all the commands are processed, some in the incorrect order, and some of the screens are printed before the pages have completed loading. Opening and closing the workbook 40 times to print the documentation I need isn't an effective solution. I think I'm going to close this post and start a new one looking for someone who has had this issue before. There has got to be some way to do this. I'm guessing there must be some completely different process I'm not aware of.

Have you tried application.ontime to schedule the macro to run again? You would lose any stored variables/objects however unless you devised some method to store their contents.