Hi! I have a macro that keeps hanging. The macro is basically a very long
loop so I am not surprised. I am told that you can use something called
DoEvents in combo with GetQueueStatus API function. However I have no idea
how to use that stuff and nothing comes up in the vba help. If anyone can
quickly describe how to use or knows a good webpage I would be very grateful.
I have tried to goole it but without any realy success. Many thanks in
advance!

Post the macro, or preferably a shorten ed version that hangs.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Arne Hegefors" wrote in message
...
Hi! I have a macro that keeps hanging. The macro is basically a very long
loop so I am not surprised. I am told that you can use something called
DoEvents in combo with GetQueueStatus API function. However I have no idea
how to use that stuff and nothing comes up in the vba help. If anyone can
quickly describe how to use or knows a good webpage I would be very
grateful.
I have tried to goole it but without any realy success. Many thanks in
advance!

Arne,
According to Help, DoEvents "Yields execution so that the operating system
can process other events.". But read the entry for more info.
So unless your loop requires other events to have been processed in order to
function correct, DoEvents may not be what you are looking for and the error
lies in your code.

Not that I've had cause to use it, but GetQueueStatus is used

<From API-Guide , http://www.allapi.net/
The GetQueueStatus function indicates the type of messages found in the
calling thread's message queue.

Private Const QS_HOTKEY = &H80
Private Const QS_KEY = &H1
Private Const QS_MOUSEBUTTON = &H4
Private Const QS_MOUSEMOVE = &H2
Private Const QS_PAINT = &H20
Private Const QS_POSTMESSAGE = &H8
Private Const QS_SENDMESSAGE = &H40
Private Const QS_TIMER = &H10
Private Const QS_ALLPOSTMESSAGE = &H100
Private Const QS_MOUSE = (QS_MOUSEMOVE Or QS_MOUSEBUTTON)
Private Const QS_INPUT = (QS_MOUSE Or QS_KEY)
Private Const QS_ALLEVENTS = (QS_INPUT Or QS_POSTMESSAGE Or QS_TIMER Or
QS_PAINT Or QS_HOTKEY)
Private Const QS_ALLINPUT = (QS_SENDMESSAGE Or QS_PAINT Or QS_TIMER Or
QS_POSTMESSAGE Or QS_MOUSEBUTTON Or QS_MOUSEMOVE Or QS_HOTKEY Or QS_KEY)

Private Declare Function GetQueueStatus Lib "user32" (ByVal fuFlags As Long)
As Long
Dim bCancel As Boolean

Private Sub Command1_Click()
'initialize this boolean to 'false'
bCancel = False
'start the loop
Do
'check whether there are mouse-button or keyboard messages
'in the message queue. If there are, call DoEvents
If GetQueueStatus(QS_ALLINPUT) Then DoEvents
Loop Until bCancel = True
MsgBox "After loop..."
End Sub
</From API-Guide , http://www.allapi.net/

If that would prove useful knowledge is another question.

NickHK

"Arne Hegefors" wrote in message
...
Hi! I have a macro that keeps hanging. The macro is basically a very long
loop so I am not surprised. I am told that you can use something called
DoEvents in combo with GetQueueStatus API function. However I have no idea
how to use that stuff and nothing comes up in the vba help. If anyone can
quickly describe how to use or knows a good webpage I would be very
grateful.
I have tried to goole it but without any realy success. Many thanks in
advance!

Hi Bob!

I give you an idea of how my code works, it is too long to submit.

Ok my code works like this: I read in a long list of ISIN codes form the
worksheet (basically an ID-number for a financial instrument).

For pos = 2 To xlDown
ISIN = Worksheets(Kalkylblad).Cells(pos, 1).Value

If ISIN = "" Then
Exit For
End If

I then want to check the ratings (ie the creditworthiness) for all these
instruments. I do this by sending a question to the Bloomberg professional (a
provider of financial info). The problem is that there are many different
types of ratings. I have a priority list that guides me when searching for
the correct rating. That is I check and see if a rating from a certain firm
exists, if I cannot find that I check with another firm, etc. This means that
for instruments that are rated only by rating firms long down in my priority
list will take some time to get.

Below I ask the first question to Bloomberg. The actual call to the
Bloomberg is made in a class module. I then use a function IsValid() to see
if I have gotten an answer or not.

varBByntax = ISIN & " Corp"
vtField = "RTG_SP"
vtData = BlpObject.BlpSyncRequest(varBByntax, vtField)
Rating = vtData(0, 0)
Rated = IsValid(Rating)

I then keep going in the code and if I have not gotten a rating for the
instrument I must ask for a rating with lower priority. So the rest of the
code is basically a long list of If..end if.

If Rated = False Then
varBByntax = ISIN & " Mtge"
vtField = "RTG_SP"
vtData = BlpObject.BlpSyncRequest(varBByntax, vtField)
Rating = vtData(0, 0)
Rated = IsValid(Rating)
End If

€¦€¦
Finally I check for the next instrument:
Next pos
And the code runs through all these if-end if again for a new instrument.


"Bob Phillips" skrev:

Post the macro, or preferably a shorten ed version that hangs.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Arne Hegefors" wrote in message
...
Hi! I have a macro that keeps hanging. The macro is basically a very long
loop so I am not surprised. I am told that you can use something called
DoEvents in combo with GetQueueStatus API function. However I have no idea
how to use that stuff and nothing comes up in the vba help. If anyone can
quickly describe how to use or knows a good webpage I would be very
grateful.
I have tried to goole it but without any realy success. Many thanks in
advance!

You ahave the right idea, but you shoule format the request as an array. At
the moment you request one data item at a time, this is slow. It's a bit
like this - when you go shoppoing you load up your trolley or cary with items
and go home at the end. In your code you are buying one item, taking it home
and then returning to the shop to buy the next item.

Doevents will not help you, it's a very low level command that is not
designed for this sort of problem. For 90-something percent of Excel users
there is no reason to use doevents.


--
www.alignment-systems.com


"Arne Hegefors" wrote:

Hi! I have a macro that keeps hanging. The macro is basically a very long
loop so I am not surprised. I am told that you can use something called
DoEvents in combo with GetQueueStatus API function. However I have no idea
how to use that stuff and nothing comes up in the vba help. If anyone can
quickly describe how to use or knows a good webpage I would be very grateful.
I have tried to goole it but without any realy success. Many thanks in
advance!