Excel macro question?

Question

I am running the following piece of code to record the value of cell A1 every second, to create a list of values. I also want to chart the values in real time, ie have the chart update whilr the code is running. However while the code is running, this currently doesn't occur, and I can't even use excel at all until I break the running macro. Is it possible to have the macro "pause" to allow this?

Sub Log()
For a = 2 To 240 Step 3
For b = 1 To 1000
Cells(b, a) = Time()
Cells(b, a - 1) = Cells(1, 1)
PauseTime = 1
Start = Timer
Do While Timer < Start + PauseTime
Loop
Next b
Next a
End Sub

Answer 1

Just add 'Range("A1").select'

in the line above 'Loop'



Do While Timer < Start + PauseTime

Range("A1").select

Loop



Else Excel will not update the screen until the full macro is executed.

Answer 2

I added a doevents command in the do-while loop, but that still didn't work because it stopped anytime cell(1,1) got changed...the only way I think you can get this to work is sort of convoluted: you need use the ontime command. First I added a command button, and added this to its click event:



Private Sub CommandButton1_Click()

If Sheet1.CommandButton1.Caption = "Start" Then

Sheet1.CommandButton1.Caption = "Stop"

Trigger = True

Call Log

Else

Sheet1.CommandButton1.Caption = "Start"

End If

End Sub



and then I modified your function as such:





Sub Log()

Static a As Integer

Static b As Integer



If Trigger Then

a = 2

b = 0

Trigger = False

End If



If a > 240 Then Exit Sub



b = b + 1



If b = 1001 Then

b = 1

a = a + 3

End If



Cells(b, a) = Time

Cells(b, a - 1) = Cells(1, 1)



If Sheet1.CommandButton1.Caption = "Stop" Then

Application.OnTime Time + TimeValue("00:00:01"), "Log"

End If



End Sub



you'll also need to add a global variable before your log subroutine:

Public Trigger as Boolean

Answer 3

If the A1 values are being stored in a list that is expanding and not overwriting you could change your graph to pickup the range change automatically. You basically have to name a range that counts non blank cells in the list. Define a seperate range with =OFFSET(a1,0,0,,nonblankcount) where a1 is first cell to start range from and nonblankcount is the count range name. On the graph data series, in the values box change the range after the filename and ! to the range name with the offset function in and the graph should update automatically.

Answer 4

Hi,



There is a newsgroup entirely devoted to programming Excel. It's microsoft.public.excel.programming.



I recommend you post your question to that group. You can access it via google or other newsreader programs.



-Jim Gordon

Microsoft Mac MVP



MVPs are independent and do not work for Microsoft

http://mvp.support.microsoft.com/