Hey workpeter, would you be willing to elaborate more about how this could be done? Im using switesh's sheet which tends to freeze up and eat up resources. I'm a novice at VBA but am learning and getting better.
How to record data from Bet Angel spreadsheet
Select the chart, then on the ribbon under "Chart Tools", select "Format"the chart just gets pushed down the sheet as another row of data appears.
On the far right hand side you'll see a box giving the height and width. In the bottom right hand corner is a tiny box/arrow.
Click on this and you'll get a pop up box. Choose the properties tab and select "don't move or size with cells"
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Thanks NIgel this fixed the problem of the chart moving but unfortunately the range I've selected is still moving down under the data. I'm guessing it must be something to do with how the sheet is coded or something
Excel updates any formulas/named range references when you insert/delete any rows/columns.
To overcome this when a new row/column is inserted, you need to update the chart so it still points to the original range.
On the attached on sheet 1 is a routine that calls the code in module 1 everytime a new row is inserted (row 2 only, change to suit or use the column ref instead).
You'll probably need to change the chart name if, like me, you've had half a dozen or more previous charts that you've deleted. Can easily get up to "Chart 20" or thereabouts!
If you're not sure of the name, select it and look at the name box (left of the formula bar below ribbon)
To overcome this when a new row/column is inserted, you need to update the chart so it still points to the original range.
On the attached on sheet 1 is a routine that calls the code in module 1 everytime a new row is inserted (row 2 only, change to suit or use the column ref instead).
You'll probably need to change the chart name if, like me, you've had half a dozen or more previous charts that you've deleted. Can easily get up to "Chart 20" or thereabouts!
If you're not sure of the name, select it and look at the name box (left of the formula bar below ribbon)
You do not have the required permissions to view the files attached to this post.
The worksheet change event will trigger everytime BA communicates with excel. Even with streaming I don't get 20ms, more like 70ms, but thats still good. Within this routine use a conditional statement to compare the current system time to the captured system time. Pseudo code:
Begin Sub routine change event
IF CurrentSystemTime >= oldSystemTime + 100ms
{
//Do some code every 100 ms
//Then update global variable
OldSystemTime = CurrentSystemTime
}
End Sub Routine
I'm not at home so cannot open your spreadsheet fully. However our approaches are somewhat different. If you spend some time investigating my solution you will find it will not have the issues you describe. In a nutshell it's overwriting the same cells so nothing moves around distorting formula and positioning. Also there's nothing preventing you using a blend of both solutions. Your solution of adding rows of data will ensure you have an entire history and them elsewhere on the spreadsheet you use my solution which focuses on the last X number of iterations to which your graph points to.RonObvious wrote: ↑Mon Aug 14, 2017 11:58 amHi Workpeter,
I've been trying to do something similiar with the Bet Angel spreadsheet. I attached a copy of it that another forum user modified so that when its connected to excel and the 'Tick to record' function on the data sheet is enabled it copies data from Row 5 every second. I've tried to capture this data as its updating and populating rows below using fixed ranges and populate a line chart in real time but it wont work, the chart just gets pushed down the sheet as another row of data appears.
I thought something like this would be relatively simple but the only video I could find that shows what I'm trying to do is this one : https://youtu.be/xLTEnd1u5Eo
And that seems to suggest its very complex
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
nigelk wrote: ↑Mon Aug 14, 2017 3:40 pmExcel updates any formulas/named range references when you insert/delete any rows/columns.
To overcome this when a new row/column is inserted, you need to update the chart so it still points to the original range.
On the attached on sheet 1 is a routine that calls the code in module 1 everytime a new row is inserted (row 2 only, change to suit or use the column ref instead).
You'll probably need to change the chart name if, like me, you've had half a dozen or more previous charts that you've deleted. Can easily get up to "Chart 20" or thereabouts!
If you're not sure of the name, select it and look at the name box (left of the formula bar below ribbon)
Hi Nigel,
I'm unable to open the file, nothing happens when I double click it.
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
I've had to open excel and try to open the file from within Excel but I'm getting the attached pop up box.
Not sure what the issue is but I've tried re-installing MS Office and still nothing.
Not sure what the issue is but I've tried re-installing MS Office and still nothing.
You do not have the required permissions to view the files attached to this post.
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Not sure if the file is corrupted or not but when I open it as you can see in the preview box of the screen grab I sent its just random symbols.
- mjmorris335
- Posts: 180
- Joined: Mon Jun 06, 2016 11:29 am
Have you tried unzipping it? Right click the file in Windows File Explorer and select Extract All.RonObvious wrote: ↑Tue Aug 15, 2017 1:20 pmNot sure if the file is corrupted or not but when I open it as you can see in the preview box of the screen grab I sent its just random symbols.
Mike