Programming Visual Basic in Excel

[pianolearner]

I posted this in a VB forum but I have not had a reply yet. I thought I might try here as well.

I am not a programmer, I just write code on rare occasions for a specific purpose. I have written an application using VB in Excel 97 to change a range within a chart. The purpose is to simulate a real time price chart for a stock market index. It works, but I'd like to remove bulky, unnecessary code.

My code looks like this:

Private Sub CommandButton2_Click()

Dim PauseTime, Start


PauseTime = 0.1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlStockOHLC
ActiveChart.SetSourceData Source:=Sheets("e-mini_test").range("A1:F3"), _
PlotBy:=xlColumns

PauseTime = 0.1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop


.............

End Sub


What I have done is copied this 391 times in order to increment the range, but I'd like to know if I can use a for/next loop.

The line I'd like to increment contains the range value:

ActiveChart.SetSourceData Source:=Sheets("e-mini_test").range("A1:F3"), _
PlotBy:=xlColumns


I'd like to change the range after each pause so that it is A1:F3, A1:F4 etc


Is this possible? If so, how can I do it?

Thanks

[maul]

This is the last place you should be looking for the solution to your problem, although I do have it.

[pianolearner]

This is the last place you should be looking for the solution to your problem, although I do have it.

You think you do, but it's wrong.

[anodibu]

How about this:

Private Sub CommandButton2_Click()

Dim PauseTime, Start, Range


PauseTime = 0.1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
   
For Counter = 3 To 394  
    Range = "A1:F" & Trim(Str$(Counter))
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlStockOHLC
    ActiveChart.SetSourceData Source:=Sheets("e-mini_test").Range(Range), _
    PlotBy:=xlColumns
   
   
    PauseTime = 0.1 ' Set duration.
    Start = Timer ' Set start time.
    Do While Timer < Start + PauseTime
    DoEvents ' Yield to other processes.
    Loop
Next

End Sub

[pianolearner]

How about this:

Private Sub CommandButton2_Click()

Dim PauseTime, Start, Range


PauseTime = 0.1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
   
For Counter = 3 To 394  
    Range = "A1:F" & Trim(Str$(Counter))
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlStockOHLC
    ActiveChart.SetSourceData Source:=Sheets("e-mini_test").Range(Range), _
    PlotBy:=xlColumns
   
   
    PauseTime = 0.1 ' Set duration.
    Start = Timer ' Set start time.
    Do While Timer < Start + PauseTime
    DoEvents ' Yield to other processes.
    Loop
Next

End Sub

anodibu, you are a GENIUS! Works beautifully. Thanks very much.

You should join: https://www.xtremevbtalk.com/

They could probably use your help!

[anodibu]

Glad that it helped (maul should reconsider his opinion hehe).

But actually I don't know that much of VB, I mostly work with C++ and C#.