All articles in 'VBA Macros' Category
Here is a common problem. Imagine you are looking a complex spreadsheet, aptly titled “Corporate Strategy 2020.xlsx” which as 17 tabs, umpteen formulas and unclean structure. Whoever designed it was in insane hurry. The workbook has formulas like this, =SUM(Budget!A2:A30, 3600)+7925 .
It was as if Homer Simpson created it while Peter Griffin oversaw the project.
So how do you go about detecting all cells containing formulas with hard-coded values?Continue »
We analysts like to compare. If you ever want to keep an analyst busy, just give her 2-3 options. She won’t return to your desk until the cows come home. My wife uses this trick all the time. Picture this:
[In late 2013]
Me: I want to buy a new phone
She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?
Its late 2014 and I am not done comparing.
So today, let’s talk about an interesting comparison scenario.
Comparing by letter or word
See above demo to understand the concept. Read more to learn how to do this.Continue »
Did you know you can Calculate Pi by throwing Frozen Hotdogs !
Learn how to calculate Pi by throwing Frozen Hotdogs with this Excel Simulation of Buffon’s Needle.
Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.
Suitable structure spawns simple solutions
Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.
Structure is everything
So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.Continue »
Howdy folks. Jeff here, bringing you a Public Service Announcement: Thanks to the magic of VBA , Structured PivotTable References are coming to a PivotTable near you! Structured References for PivotTables? So what? Well, because PivotTables are the best bit of ‘old’ Excel, and Tables are the best thing about ‘new’ Excel, and it’s about […]Continue »
In the 22nd session of Chandoo.org podcast, lets do some macros.
What is in this session?
VBA (or macros, automation) is a mystery for many of us. So in this podcast, lets unravel the mystery behind it and get you started with the awesome world of automation.
In this podcast, you will learn,
- What is a macro?
- What is VBA then?
- Reasons for using VBA Macros
- Extending Excel’s capabilities
- How to get started with VBA Macros?
- Using Recorder
- Example Macro
- Going beyond recorder – Learning VBA
This is a guest post by Krishna, a football lover & one of our readers.
The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you.
A sneak-peek at the final dashboard
Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.Continue »
On July 4th this year, I published an animated fireworks chart for you. Many of you liked it. Quite a few wanted to know how its made.
So here is a video explaining the construction of fireworks.
(You can see this video on our YouTube Channel too)Continue »
To all our readers & friends from USA,
I wish you a happy, fun & safe 4th of July.
For the last 4th of July (2013), we (Jo, kids & I) were in USA. We went to Washington DC to meet up a few friends for that weekend. And we had one of the most memorable evenings of our lives when we went to national mall area in the evening to watch beautifully choreographed fireworks. Kids really loved the amazing display of fire-crackers and enthusiasm.
While we all are back in India this time, it doesn’t mean we cant celebrate 4th of July. So I made some fireworks. In Excel of course.
Here is a little Excel animation I made for all of us.
4th of July Fireworks – Excel animation
First watch this quick demo (<15 secs)Continue »
Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.Continue »
Hello everyone. Stop reading further and go fetch your helmet. Because what lies ahead is mind-blowingly awesome.
About a month and half ago, we held our annual dashboard contest. This time the theme is to visualize state to state migration in USA. You can find the contest data-set & details here.
We received 49 outstanding entries for this. Most of the entries are truly inspiring. They are loaded with powerful analysis, stunning visualizations, amazing display of Excel skill and design finesse. It took me almost 2 weeks to process the results and present them here.
Click on the image to see the entries.Continue »
This article is written by Michael Hutchens from Best Practice Modelling.
In the first article on Modular Spreadsheet Development, we got a high level overview of Modular Spreadsheet Development principles. This article discusses the practical implementation of these principles in Excel.
From my experience using Modular Spreadsheet Development over the past decade, there are three increasingly-efficient methods of implementation in Microsoft Excel:
1. Manual implementation;
2. VBA automated implementation; and
3. Commercial add-in implementation.
This article provides a comprehensive overview of each of these methods and a summary of their advantages and disadvantages.Continue »
Today, lets learn how to make a simple timer app using Excel. First some background…,
Recently, I learned how to solve Rubik’s cube from my nephew. As a budding cuber, I wanted to track my progress. Initially I used the stopwatch in my iPhone. But it wont let me track previous times. So I thought, “Well, I can use Excel for this”.
So I made a small timer app using Excel. Its quite minimalistic. It has a single button. I press it and it tracks the start time (date & time stamp). If I press the button again, it records the duration.
This way, I can see my progress over next few weeks and may be plot the trend.Continue »
In the 5th session of Chandoo.org podcast, we are going to demystify form controls.
I am very happy and excited to interview my good friend, fellow Excel MVP, author, blogger and virtual mentor – Debra Dalgleish about this topic.
In this podcast, you will learn,
- What are form controls
- When you would use them?
- Example form control – Combo box
- How form controls differ from active-x controls
- How to enable form controls in your Excel?
- Various important form controls
- Special bonus & how to obtain it
Today we take a look at an exercise in how to develop a simple Calendar selection tool for your worksheets without using complex VBA Calendar Controls.Continue »