Solved

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

Posted on 2011-09-13
7
295 Views
Last Modified: 2012-05-12
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
Comment
Question by:dma70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36530874
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36530895
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
 
LVL 1

Author Comment

by:dma70
ID: 36531294
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 36531356
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
 
LVL 1

Author Comment

by:dma70
ID: 36531655
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36532029
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
 
LVL 1

Author Closing Comment

by:dma70
ID: 36552206
Comprehensive, easy to use solution.   Detail was amazing.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculating Sales Tax 13 68
How to turn on numlock via VBS code? 12 28
Pull first name out of cell 2 28
SQL query joining 6 tables in asp.net 4 32
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question