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

Thanks Blackened,
I haven't been doing this VBA programming for long, and "public events as boolean" is beyond my understanding, although from what I have read, won't this result in me having to press the start button continuously?

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/