Marius, The Excel VB function Wait must be used in the following format: Application.Wait (Now + TimeValue("0:00:01")) Wait uses a TimeValue to define the time you would like to wait or pause. TimeValue returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). In the above example, the time to "Wait" is 1 second.
Very Informative and a detailed account of how one can bring in such a transformation to numbers. It is truly said of pictures; they speak a lot than words!
Hi- very useful thanks for posting! Any ideas on how to work with the offset and dates (months)? Would like to display monthly developments and can't figure out how to make excel read the offset as an additional month and get the year to change after 12 months. (Have tried using the date format, but am not getting far).
True, and that's the problem; relative size to other values means the largest bubble's pop represents 100% and the others are scaled accordingly. What happens when another country takes lead in pop, the original largest country appears to lose pop (i.e. bubble shrinks) even if the pop stayed the same, or even if it increased. This renders the size representation of a bubble useless and misleading in a dynamic bubble chart. This is especially true when adding new data points midway within it
Great video. Very informative. Relative to the PP animation question from Ambar, I have animated radiation particle transport calc results in PP just by copying the output files to individual PP sheets and shorting the display time for the selected frames until I get the effect I want.
i am using excel 365. to easily change the colors for each bubble, at 8:13 of your explanation, you mentioned tp right-click any one bubble, click Format Data Series, Fill, Vary colors by point. the "Vary colors by point" option is not available in Excel 365. i don't seem to be able to locate it by checking all the available options. is it not available in excel 365?
Thank you for your clear presentation. One suggestion, can you add some tag/comment to relate your video to the key term of excel? So people might find your video easier.
Hi Jose Zaldivar, glad you liked it, thanks for commenting! Here's a playlist of all types of charts that can be created in Excel ua-cam.com/video/TjxL_hQn5w0/v-deo.html
I am using the macro program for a similar animation bar chart. However, in the "SLEEP" program part, I am getting an error as "COMPILE ERROR. SUB OR FUNCTION NOT DEFINED". Removing the "SLEEP" part, it works fine. Pl. help
Hi! Great video but I'm having trouble with running the macro button. I copied the code, corrected the range...but get the message "Sub or Function not defined", highlighting the word 'Sleep'... any idea what I did wrong? :(
hi Doug thanks for the great tutorial, I just tried it (with a slight variation to fit my data) and everything works fine except that at the end of each macro run, I end up with the values of #N/A in each of the cells where the chart gets its values from. Not sure what's going on I double-checked all the absolute cell references and lookup parameters and everything seems to be fine, but I'm sure I missed something. Not that huge of a deal except because I end up with those undefined values in the cell, sometimes it messes up the chart axes. any suggestions? thanks
Hi Doug, I was able to replicate the whole thing, my macro's working just fine (I can see the data table changing as the offset cell changes) however, my bubble chart does not animate along with the change in data. I also used the scroll bar and it works just fine (the chart is moving along with it). I wonder what could be the problem?
The chart which I had created two years back was working but now bubbles are not moving. On using the slider, the bubbles work. What is the reason for this problem?
Doug - although my current issue cropped up on a different Excel issue it applies to this one also - how do you put this into a PowerPoint slide so that the code runs? Here's my current problem - I have created a cost counter in Excel that counts up the lost cost - minutes multiplied by £ per min and I want to put it in the corner of all my slides so that audience can see the "cost" of the time taken during the presentation. However, I can't get it to run/update/tick along when in PowerPoint :-(
Sorry Doug! I was so excited about your project that I did not notice that it was designed for Excel 2007. So, would you be so kind to tell me what can I do to make it to work slowly in Excel 2010? Thank you very much. Marius
haven't seen this b4, but you can try some of the tip from the microsoft community page answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2016/visual-basic-not-appearing/40743851-6fc2-4494-b2e7-aeb8b92c5365
This is awesome. Can you please tell me how I can export this motion bubble to ppt or google slides? Everyone is not excel savvy and enabling macros is difficult for everyone. So, if there's a way to share this in a ppt or google slide, it can be handy for anyone to use. Thanks!
I am following your video to add animation to a bubble chart I'm making. But when I click the button, I get a compile error stating the Sub or Function was not defined. I followed your steps exactly, and made sure the code was copied properly. (My button numbers were different when I created the button, but I just clicked New after creating the button like you did.)
Hi Doug, great tutorial. Thanks! However after I updated to Excel 2016, my chart doesn't refresh. The series values and the text boxes in the chart are updating, but the bubbles are not moving. The graphs only changes at the last set of data.
Jasper, Click on text boxes in the chart which linked to the offset, the chart will update but I don't know why. I just happened to click on it and it worked
Hi Doug, Great video as always. Could you please let me know how to do it if my motion variable ("year" in your case) is not numerical? In my case, I have geographies as the motion variable, and brands as the point variable ("country" in your example). I am not sure how to set the "offset" because it is not numerical. Thank you very much for your help! Hailey
Great! Super helpful! One question though: Is there a way to make the size of the bubble relative to the actual population? For Ex. let's say I wanted to add a country to the other 4. Now let's say prior to 1990 the country didn't even exist (therefore NO data until 1990). In the current set up, if it had a population 2x of the largest country already charted, the diameter of said former largest country would be halved, even if the population didn't change. Is there a way to prevent this?
Hello Sir! Please include the following line in the beginning of your module (before the Sub declaration): Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) If you are working with a public module: Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) If you do so, the sleep command will start working. This happens because the VBA compiler does not recognise the command "sleep". But by making the above declarations, is like telling it where to find the source code for this command. So it finds it and works.
Thank you Doug for this magnificent video. :) I just try my own similar project and works well, but the "Sleep" does not do its job. Specifically, without the "Sleep" function the project runs, but at a rate too high. With the function "Sleep (100) 'project does not work. It displayed the following message "Compile error: Sub or Function not defined" and the word "Sleep" from the code is marked with blue background. What can I do to reduce the speed of displaying the data on the chart?
Hi everybody. This tutorial works well in Excel 2010, but the chart doesn't move in Excel 2016. Can you explain why? How can one insert motion to the chart in Excel 2016? Thank you/
Hi Doug. Thanks for your kindly response. Unfortuntly my Excel version doesn't include Power view. As I understand it, I can't use this feature anymore. What a shame... Boaz
Hi, thanks for the video! really helps a lot. I have one question, how can you do the offset when you have data by month? for example (01/01/2007) so the next offset should be (01/02/2007) (I'm using DD/MM/YYYY). In you example years are easy to offset, in s-anand he has the day continuos, so again not difficult to make the offset. I'm stuck trying find a solution, I need to run this for monthly sales since 2007 :( !!!
10 years after posting, this video is still helping people. Thanks Doug, great job!!!
You're welcome! BTW can also do this in Power BI ua-cam.com/video/r5m-Gl7C9ec/v-deo.html
I saw Hans presentations many years ago and since then i've been looking for a good tutorial that explains that. Thank God I found you sir!!!!
Hi luz martinez, thanks for the kind words! There also another non VBA way to do this with Excel. See ua-cam.com/video/5jWWA-lvfQM/v-deo.html
I love the calm, clear way you presented everything in this video. I also like your voice
Thanks for sharing a cool visual way to represent time series data on bubble charts.
Greetings! Thanks from a Brazilian living in Panamá.
Obrigado!
This really helped me a lot to clarify certain unknown areas in advance chart processing. Great work keep it up.....
Doug, you're a great Excel teacher. Thanks a lot for sharing the tip.
I really like the alt key drag tip for drawing the scroll bar! Thanks!
You're welcome!
Doug, thanks for your time and your video again.
Marius,
The Excel VB function Wait must be used in the following format:
Application.Wait (Now + TimeValue("0:00:01"))
Wait uses a TimeValue to define the time you would like to wait or pause. TimeValue returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). In the above example, the time to "Wait" is 1 second.
Very Informative and a detailed account of how one can bring in such a transformation to numbers. It is truly said of pictures; they speak a lot than words!
Hi Vikas Kant, glad you liked it, thanks for commenting!
Well done. Thanks for providing the code knowledge for dynamic bubble graphs.
Cheers,
Dani
Hi- very useful thanks for posting! Any ideas on how to work with the offset and dates (months)? Would like to display monthly developments and can't figure out how to make excel read the offset as an additional month and get the year to change after 12 months. (Have tried using the date format, but am not getting far).
True, and that's the problem; relative size to other values means the largest bubble's pop represents 100% and the others are scaled accordingly. What happens when another country takes lead in pop, the original largest country appears to lose pop (i.e. bubble shrinks) even if the pop stayed the same, or even if it increased. This renders the size representation of a bubble useless and misleading in a dynamic bubble chart. This is especially true when adding new data points midway within it
Great video. Very informative. Relative to the PP animation question from Ambar, I have animated radiation particle transport calc results in PP just by copying the output files to individual PP sheets and shorting the display time for the selected frames until I get the effect I want.
you present it in a very smart way. thanks!!
Love it! This kind of tips can make us earn some bonus points at work. Thanks a lot!
i am using excel 365. to easily change the colors for each bubble, at 8:13 of your explanation, you mentioned tp right-click any one bubble, click Format Data Series, Fill, Vary colors by point. the "Vary colors by point" option is not available in Excel 365. i don't seem to be able to locate it by checking all the available options. is it not available in excel 365?
This was excellent - a perfect fit to the problem I was trying to solve!
Thanks, Doug. Very helpful, clearly explained video.
HELP ME PLEASE! when im about to click the button, its not moving and the computer shows a problem about the macro thing.... help me please :'(
Looks good, I may just use this for tracking some inventory movement. Thanks for the information.
You're Welcome!
Hi Doug! This video was very helpful. Thanks a lot
Thank you for your clear presentation. One suggestion, can you add some tag/comment to relate your video to the key term of excel? So people might find your video easier.
This is very helpful and educational! Liked it very much!
+Doug H do you sell that template?
very helpful, and well done with explaining and showing. thank you
Is there a way to add a pause button so when you click on it the animation will stop at the current offset?
This is great, clear and very helpful!!! Thanks!
Great job, Doug!
thanks for the video. It is like revealing a magic trick. If you have more like these, please share them!
Hi Jose Zaldivar, glad you liked it, thanks for commenting!
Here's a playlist of all types of charts that can be created in Excel ua-cam.com/video/TjxL_hQn5w0/v-deo.html
Thanks in a million. Great stuff. A++
Glad you liked it!
I am using the macro program for a similar animation bar chart. However, in the "SLEEP" program part, I am getting an error as "COMPILE ERROR. SUB OR FUNCTION NOT DEFINED". Removing the "SLEEP" part, it works fine. Pl. help
That's a really great idea, thanks again for the help!
nice video about Excel functions and macros.
Hey , Thanks for the video.
But in my excel after the scripting gets over then only it updates the value of Graph . pls help me fix it
Hi KRISHNA MALHOTRA, sorry 🙁....but try a post on the mrexcel.com forum!
Thanks very much. Very helpful.
GRACIAS!! Thank you so much for your accurate help...
Clear and helpful. I even learned something new about Vlookup. Didn't realise you could concatenate the lookup_value. Thank you!
Hi TPPembabulous, glad you liked it, thanks for commenting!
Very Helpful !! Thanks a mil !!
i didn't know we can you ampersand in the lookup, great video man!
Great tutorial! Thank you so much!
Hi acm90, you're welcome!
Great work here Doug.
Thanks so much. That was fantastic
hi Doug. Why my code shows sub or function does not define?
Hi Douh! Very informative and easy... Thanks you so much!... :)
Very good presentation. Thanks
This is brilliant, thanks Doug~~
Excellent work Doug!
Thats a great work.. Thanks for the video..
ah the program keeps on saying ambiguous name for the click and it couldnt work...any suggestions?
this is a very good learning.... thank you so much:)
Like this very much, thanks for sharing Doug.
Hi! Great video but I'm having trouble with running the macro button. I copied the code, corrected the range...but get the message "Sub or Function not defined", highlighting the word 'Sleep'... any idea what I did wrong? :(
hi Doug
thanks for the great tutorial, I just tried it (with a slight variation to fit my data) and everything works fine except that at the end of each macro run, I end up with the values of #N/A in each of the cells where the chart gets its values from. Not sure what's going on I double-checked all the absolute cell references and lookup parameters and everything seems to be fine, but I'm sure I missed something. Not that huge of a deal except because I end up with those undefined values in the cell, sometimes it messes up the chart axes.
any suggestions?
thanks
hey Doug just figured it out, I had the counter set one unit too high so it was referencing a cell with no data...cheers
Really cool. Thanks for showing that.
Hi Lack of Focus...you're welcome, glad you liked!
Very helpful! Thanks!
Buena chino...te felicito...excelente!....
Good one, bro!
thanks for the explanation, is absoute usefull
any updated code to work for VBA 64-bit? thanks!
Marius, Sleep() function does not work in 2010 version. Instead try using Application.Wait("100").
Hi Doug, I was able to replicate the whole thing, my macro's working just fine (I can see the data table changing as the offset cell changes) however, my bubble chart does not animate along with the change in data. I also used the scroll bar and it works just fine (the chart is moving along with it). I wonder what could be the problem?
Don't know about your situation. Macros are usually troublesome so this one might be an alternative ua-cam.com/video/5jWWA-lvfQM/v-deo.html
@@DougHExcel Thank you!
Hi, Doug, thanks for the tutorial! I am not managing to make the bubbles move though... for some reason, the code does not work...
Can we insert this chart in Powerpoint ? If yes, can you please let know how ?
Yes it can be done. Record and save as a mp4 and then add object. By default Ppt doesn't run VB code... Or you can even embed the xlsx file.
Okay, I'll ask them. Thanks!
I wonder can you copy the animated bubble chart to ppt?
Very nice. Bravo.
The chart which I had created two years back was working but now bubbles are not moving. On using the slider, the bubbles work. What is the reason for this problem?
Hi Raghuram JC, thanks for the comment! Another consideration...If you have PowerBI, it is also an option. See www.youtube.com/watch?v=5jWWA...
@@DougHExcel Thanks. Let me review and revert.
Excellent video!!
Doug - although my current issue cropped up on a different Excel issue it applies to this one also - how do you put this into a PowerPoint slide so that the code runs? Here's my current problem - I have created a cost counter in Excel that counts up the lost cost - minutes multiplied by £ per min and I want to put it in the corner of all my slides so that audience can see the "cost" of the time taken during the presentation. However, I can't get it to run/update/tick along when in PowerPoint :-(
Sorry Doug! I was so excited about your project that I did not notice that it was designed for Excel 2007. So, would you be so kind to tell me what can I do to make it to work slowly in Excel 2010?
Thank you very much.
Marius
Doug - just followed the steps and as soon as I checked "Developer" my entire Excel screen went blank? Any ideas/suggestions? Thanks!
haven't seen this b4, but you can try some of the tip from the microsoft community page answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2016/visual-basic-not-appearing/40743851-6fc2-4494-b2e7-aeb8b92c5365
THANK YOU SO MUCH
This is awesome. Can you please tell me how I can export this motion bubble to ppt or google slides? Everyone is not excel savvy and enabling macros is difficult for everyone. So, if there's a way to share this in a ppt or google slide, it can be handy for anyone to use. Thanks!
Maybe recording it as a video and then embedding it to PPT/GOOG slides. Latest version of PPT does have a simplified screen recorder.
I have “Microsoft Office 2010” and the operating system is “Windows 7 Professional” (32 bit)
Thank you
Hey great video but you could use a match formula to get the column index number dynamically. Just a suggestion.
If you have PowerBI, it is also an option. See ua-cam.com/video/5jWWA-lvfQM/v-deo.html
I am following your video to add animation to a bubble chart I'm making. But when I click the button, I get a compile error stating the Sub or Function was not defined. I followed your steps exactly, and made sure the code was copied properly. (My button numbers were different when I created the button, but I just clicked New after creating the button like you did.)
Finding that the macro version troublesome? If you have PowerBI, it is also an option. See ua-cam.com/video/5jWWA-lvfQM/v-deo.html
Thank you Tony.
I tried to use the function "Wait (100)", but the message is the same:
"Compile error: Sub or Function not defined"
:-( :-(
Well, that's a good one! like it very much. Will use it soon (-:
great! but the code didn't work in excel 2010 :(
Hi Doug, great tutorial. Thanks! However after I updated to Excel 2016, my chart doesn't refresh. The series values and the text boxes in the chart are updating, but the bubbles are not moving. The graphs only changes at the last set of data.
The Same Problem. :(
Jasper, Click on text boxes in the chart which linked to the offset, the chart will update but I don't know why. I just happened to click on it and it worked
Hi Doug,
Great video as always.
Could you please let me know how to do it if my motion variable ("year" in your case) is not numerical? In my case, I have geographies as the motion variable, and brands as the point variable ("country" in your example). I am not sure how to set the "offset" because it is not numerical.
Thank you very much for your help!
Hailey
Dear br thank you very much ..........
Bravo!
thanks!
Great! Super helpful!
One question though:
Is there a way to make the size of the bubble relative to the actual population?
For Ex. let's say I wanted to add a country to the other 4. Now let's say prior to 1990 the country didn't even exist (therefore NO data until 1990). In the current set up, if it had a population 2x of the largest country already charted, the diameter of said former largest country would be halved, even if the population didn't change. Is there a way to prevent this?
Hi Doug, is it possible to slow down the movements of the bubbles?
Try using Power BI ua-cam.com/video/5jWWA-lvfQM/v-deo.html
Hi Doug! This video was very helpful. Thanks a lot, but Sleep (100) not work. It is necessary to change the speed of the animation. Please tell me
Hello Sir!
Please include the following line in the beginning of your module (before the Sub declaration):
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
If you are working with a public module:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
If you do so, the sleep command will start working. This happens because the VBA compiler does not recognise the command "sleep". But by making the above declarations, is like telling it where to find the source code for this command. So it finds it and works.
This might be easier If you have PowerBI, it is also an option. See www.youtube.com/watch?v=5jWWA...
Thank you Doug for this magnificent video. :)
I just try my own similar project and works well, but the "Sleep" does not do its job.
Specifically, without the "Sleep" function the project runs, but at a rate too high.
With the function "Sleep (100) 'project does not work. It displayed the following message "Compile error: Sub or Function not defined" and the word "Sleep" from the code is marked with blue background.
What can I do to reduce the speed of displaying the data on the chart?
Amazing video!!! Just wondering if it's possible to embed it into powerpoint?
Hi its very very good
it realy helped me to start the dash board
Hi everybody.
This tutorial works well in Excel 2010, but the chart doesn't move in Excel 2016.
Can you explain why?
How can one insert motion to the chart in Excel 2016?
Thank you/
You could try an non VBA approach. See video here ua-cam.com/video/5jWWA-lvfQM/v-deo.html
Hi Doug.
Thanks for your kindly response.
Unfortuntly my Excel version doesn't include Power view.
As I understand it, I can't use this feature anymore.
What a shame...
Boaz
How can I send you a display capture?
Hi, thanks for the video! really helps a lot. I have one question, how can you do the offset when you have data by month? for example (01/01/2007) so the next offset should be (01/02/2007) (I'm using DD/MM/YYYY).
In you example years are easy to offset, in s-anand he has the day continuos, so again not difficult to make the offset. I'm stuck trying find a solution, I need to run this for monthly sales since 2007 :( !!!
Fantastic Motion chart. How do you slow down the motion, using the sleep value???
If you have PowerBI, it is also an option. See ua-cam.com/video/5jWWA-lvfQM/v-deo.html
thank you very good
Hi Jay Patel, you're welcome!
Does PowerPoint have the same application?
John Effrein there's a function in powerpoint named "screen recording", that might help.
Thanks for the video. Can you export it to Power Point?
good
so much easier w/ google docs