Hello group,

I'm completely new to Excel development, and it's 4:30 in the morning..
I may very well be missing something basic. If this is the case, I
apologize up front :-/

I need a spreadsheet to create one _utterly_ simple sector diagram. I
have to fetch some single integers (vote results) from a web resource
(for the diagram). For now, this web resource returns one single
(random) integer on each request.

My questions, as the subject indicates, is related to CPU usage. I have
an early version of this spreadsheet stored, where the macros were
recorded/modified. If I open this spreadsheet and select ANY empty
cell, Excel boosts to 100% cpu usage and stays there. If I select the
diagram, CPU usage drops to normal. I decided the document might be
corrupt, and restarted from scratch keeping only my two simple macros.

This problem was cured by starting from scratch and keeping the macro
source. The spreadsheet now works as expected, except for the
following:

When I call either one of my two macros, by button or shortcut, Excel
stays at 100% CPU usage for 7 - 10 seconds after the macro is
_finished_ (i.e. updated with new data from web resource). I thought
this to be cleanup or some other background process in QueryTables (I
know the requests are fast, from visual feedback and web server logs).

Then I discovered that this is also true for my second macro:

Sub ResetVotes()
Range("D11:D30").ClearContents
End Sub

If I put this macro in a blank spreadsheet, it's not even noticable in
terms of CPU usage. From my spreadsheet, however, it also causes a
deadlock for 7-10 seconds.

I'm at a loss as to HOW this is even possible, the most plausible
scenario I can postipulate is that QueryTables somehow hogs the
resources. Can I reuse them smarter? Does any process trigger when a
QueryTables' destination cell is cleared, or something else to support
a deadlock in the ResetVotes() macro?

Another funny thing is:

If I start either macro, it will complete quickly while cpu usage is
still rising (roughly at 50-80% by impaired visual judgement). CPU then
stays at 100% for 7+ seconds. Now if I run either one of the macros
again, while usage is up, it will DROP briefly while the macro runs and
then re-rise to 100% for 7+ seconds from the last run (i.e. it does not
seem to accumulate deadlock time).

Another, probably unrelated issue, is that the macro below clears the
contents of cell D31. Would anyone care to explain why?

My system is a Pentium-M 2,13ghz with 1gb of ram, Windows XP Pro SP2
and Excel 2003 11.5612.5606.

And the deadlock also occurs if I remove the diagram and keep only the
data.


Thanks for your time, -


here is the main macro:


Sub GetVotes()
Application.ScreenUpdating = False

For iRow = 11 To 30

' I only want to fetch this specific vote result if the user has filled
in a value in column C.
' The value of C will ultimately be shipped in the query to fetch the
correct votes.

If Trim(Range("C" & iRow).Value) = "" Then
Range("D" & iRow).ClearContents
Else
With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.random.org/cgi-bin/randnum?num=1&min=1&max=100&col=1",
Destination:=Range("D" & iRow))
.Name = "vote_" & iRow
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With
End If
Next iRow

Application.ScreenUpdating = True

End Sub