• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

How can I create a real time graph from a value that changes from real time add-ins in Microsoft Excel 2007?

I have a spreadsheet that updates a value in real time.   I would like to store these values on a periodic basis (say every 5 minutes) and plot the values vs time.   Would consider outside software if easily obtained, but would prefer to start with the graphics available in Excel.  
0
dma70
Asked:
dma70
  • 4
  • 3
1 Solution
 
dlmilleCommented:
You can use the OnTime function, to schedule a macro to run every 5 minutes (which could copy/paste and keep a chart's history.  The chart could be setup to look at a dynamic range of this ever-growing dataset.

Here's more on the OnTime function.  Its a similar tip that I worked on - copying data to another sheet every 5 minutes.  Hopefully, this should get you going!

http:/Q_27227948.html

dave

0
 
dlmilleCommented:
Here's the code:
Public runWhen As Double

Sub startTimer()
Dim refreshTime As Double
Dim waitMins As Long

    Call myMacro
    waitMins = 5
    refreshTime = 15 ' after testing at 15 seconds, then change to -> waitMins * 60 'time in seconds
    runWhen = Now() + refreshTime / 86400 '# seconds in 24 hours
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=True

End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=False
    
End Sub
Sub myMacro()
  'code goes here, to copy/paste the values into an area that the chart is looking at.
End Sub

Open in new window


Paste this code into a public module.

Use startTimer() routine to get it going.  Be sure to use the stopTimer() routine when you close the workbook!

On that link I sent, page down until you get to the end, where I've posted the solution with an attached file.  You can see how that one was setup, for a bit more "drilldown" on the solution.

Cheers,

Dave
0
 
dma70Author Commented:
Thanks Dave -- looks like what I need.  May take some time to try it.    Might save me some time if you know the cut and paste functions to use in vb, and how to save both a time stamp and a value, so I have an x and y for my graph.    

Also need to think about how the graph might refresh each day when and only start once the spreadsheet is brought up.    If the graph accesses a specific area in the spreadsheet will the graph update as new points are added automatically?  Any thought on this are welcome.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dlmilleCommented:
I can do that.  Can you can mock up what you want? I can better assist you to get what you need, at the end of the day.

E.g., lay out your data, show an area where you have new data coming in, and make a graph.  Just put some comments on the sheet.  I think I can help you from there.  Otherwise, I'd be building the whole thing, which may not be meeting what you need, etc.

Dave
0
 
dma70Author Commented:
I may have trouble by the end of the day myself.   I can give you a location on sheet1 and build a graph template on sheet2 which houses the data each day.
0
 
dlmilleCommented:
Ok - here's your example.  All on one sheet.  The startTimer() macro runs on workbook open, closes before workbook close.  There are macro buttons for start/stop.  I have it set to 60 seconds for your testing, but the documentation in the code instructs how to change it to 5 minutes.

On the sheet, I have a place for you to put your chart function (you've not told me alot about this, so I made assumptions).  Below that, is the chart "history".  I premised 150 data points (5 minutes over 12 hour day is about that).  The startTimer macro runs "myMacro" which runs the updateHistory routine, which shifts the data down one row, then copies the data from the data feed into the top, cleaning up after 150 rows.

As a result, you now have a constant feed of last 150 points to the chart.

Take some time to look at it.  I'm happy to answer any questions.  I think this should get you going!

code:
Sub updateHistory()
Dim wkb As Workbook
Dim sht As Worksheet
Dim historyRng As Range, outRng As Range, newData As Range
Dim fRange As Range

    Application.Calculate
    
    
    Set wkb = ThisWorkbook
    Set sht = ThisWorkbook.Sheets("ChartUpdate")
    
    Set outRng = sht.Range("A7:B7")
    Set newData = sht.Range("A2:B2")
    
    Set fRange = sht.Range("A:B").Find(what:="*", searchdirection:=xlPrevious)
    
    If fRange.Row > 6 Then
        Set historyRng = sht.Range("A7:B" & fRange.Row)
        historyRng.Copy historyRng.Cells(1, 1).Offset(1, 0)
        sht.Range("A150:B" & Rows.Count).Clear
    End If
    
    outRng.Value = newData.Value
    
    
End Sub

Sub clearHistory()
Dim wkb As Workbook
Dim sht As Worksheet
Dim historyRng As Range
Dim fRange As Range

    Set wkb = ThisWorkbook
    Set sht = ThisWorkbook.Sheets("ChartUpdate")
    
    Set fRange = sht.Range("A:B").Find(what:="*", searchdirection:=xlPrevious)
    
    If fRange.Row = 6 Then Exit Sub
    
    Set historyRng = sht.Range("A7", sht.Range("B" & fRange.Row))
    
    If historyRng.Rows.Count > 1 Then
        historyRng.ClearContents
    End If
End Sub

Open in new window


timer code:
 
Public runWhen As Double

Sub startTimer()
Dim refreshTime As Double
Dim waitMins As Long

    Call myMacro
    waitMins = 5
    refreshTime = 60 ' after testing at 30 seconds, then change to -> waitMins * 60 'time in seconds
    runWhen = Now() + refreshTime / 86400 '# seconds in 24 hours
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=True

End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=False
    
End Sub
Sub myMacro()
  'code goes here, to copy/paste the values into an area that the chart is looking at.
  Call updateHistory
End Sub

Open in new window


code in ThisWorkbook: for workbook_open and _close events:
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

Private Sub Workbook_Open()

    Application.Calculation = xlCalculationAutomatic
    Call startTimer
End Sub

Open in new window


See attached.

Enjoy!

Dave
timerChart-r1.xlsm
0
 
dma70Author Commented:
Comprehensive, easy to use solution.   Detail was amazing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now