• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Animating a chart over time|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Animating a chart over time|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosAnimating a chart over time
sp_PrintTopic sp_TopicIcon
Animating a chart over time
Avatar
Alistair Verth

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 3, 2020
sp_UserOfflineSmall Offline
1
June 3, 2020 - 11:15 am
sp_Permalink sp_Print

Hi,

I am looking to animate a chart in excel that shows the change in potential (volts) of a pipeline over time at test points along the pipeline length.  So the x asis will be the KP or kilometre point (fixed) from the start of the pipeline and the y axis will be the pipeline potential (variable).  I data log the pipeline potential over a 24 hour period sampling once every 30 seconds so roughly 2800 reads and can have up to 30 test locations along the pipeline section that I install data loggers at.

I imagine the animation will take probably ~2 mins to show the 24 hour logging period so the frequency of each read in the animation would be ~0.05 seconds?  No idea how to do that or whether that is achievable.

I've attached a sample sheet for reference.

Would be great to hear about any easy way to do this.

Cheers

PS pipeline potential is supposed to be negative

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
June 3, 2020 - 5:53 pm
sp_Permalink sp_Print

Hi Alistair,

See attached file.

I've made the potential positive so the bars are above the x-axis.

This requires Office 365 as it uses dynamic arrays.  Not sure what version of Excel you have.

regards

Phil

sp_AnswersTopicAnswer
Answers Post
Avatar
Alistair Verth

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 3, 2020
sp_UserOfflineSmall Offline
3
June 4, 2020 - 11:04 am
sp_Permalink sp_Print sp_EditHistory

Hi Phil,

That is exactly what I am after - thank you.

The question now becomes how can I replicate this going forward with any new data I get or use it on old data I have?  I'd like to understand how to do it myself so happy to hear how we go about this and costs etc.

Cheers,
Alistair

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
June 4, 2020 - 12:33 pm
sp_Permalink sp_Print sp_EditHistory

Hi Alistair,

What is your experience of VBA?  Can you look through my code and see what it is doing?

It boils down to:

1) Reading a row of data and copying that to O4

2) The formula in O2 then multiplies that row by -1 to make it positive and the results are in O2:Z2

3) The chart just plots the data in O2:Z2

4) Go to 1.

If you need to add or remove data points (sensor readings) then you'll have more/less columns so adjusting the chart and steps 1-3 should be straight forward.

Regards

Phil

Avatar
Alistair Verth

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 3, 2020
sp_UserOfflineSmall Offline
5
June 5, 2020 - 10:42 am
sp_Permalink sp_Print

Hi Phil,

Have been able to do that so much appreciated - thank you.

Silly question time:

- is there a way to stop the animation before the end time?!

- can I move the chart to its own sheet instead of imbedded in the sheet as is now?

 

Cheers,
Alistair

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
June 5, 2020 - 12:17 pm
sp_Permalink sp_Print

Hi Alistair,

Yes you can stop the animation.  I've added a stop button and a new sub that stops it.

To move the chart, select it, cut, paste to new sheet.

I've had to rewrite the code a little to take this into account and if you move the chart you will need to make sure the VBA is referencing the sheet where the chart is.

Regards

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31888
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.