• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What are the best ready-made multiple-use macros from the web

jeffreyweir

jeffreyweir

Active Member
Howdy folks. I'm writing up a blog post for Chandoo's site called "What would James Bond stash in his Personal Macro Workbook?"

In this post, I'm making the point that you don't have to know how to code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste.

I want to showcase some of the absolute best ready-made multiple-use macros there are out there. And I'd like your help to compile a killer list of some killer code.

The type of thing I'm after is routines that save significant time, or really bolster a data-analyst's toolkit. They can be simple. And they can be complex. But they must be really really useful really really often. And they must be able to be used with no modification. And they must be the type of thing that you yourself would put in your personal macro workbook.

Here's a couple of examples of the kinds of things I had in mind. Note that in many of these cases, the routines are greatly expanded upon in the comments of those posts, thanks to to hive mind.
http://peltiertech.com/WordPress/label-last-point-for-excel-2007/
http://datapigtechnologies.com/blog/index.php/auto-format-pivottables-to-match-source-data/
http://dailydoseofexcel.com/archives/2010/06/18/formatting-pivot-tables/

Ohh, and links to add-ins are welcome too, if they have very wide/broad appeal. For instance:
http://www.appspro.com/Utilities/ChartLabeler.htm
http://www.jkp-ads.com/officemarketplacenm-en.asp (Name Manager)
...although these I will cover off in a separate post.


So what do I need to add to this short list? I'm only after the best of the best - so if you can think of 10 things, but 2 are absolute standouts that you would gladly take to a desert island, then I'm more interested in the 2 than the other eight.

Note that I don't need links to large repositories of code (for instance Deb Dalgleish's excellent downloads page at http://www.contextures.com/excelfiles.html ) but rather I need votes and links to specific routines that you use time and time again, and that can be pulled directly from the net (including at Deb's page) and used with little or no modification to complete VBA novices.

I intend the post on Chandoo to be a 'living' list. That is, as I keep stumbling across new prefabricated code, I might do the occasional 'update' post on Chandoo's blog, and point back to the updated list at the first post. So if you are reading this in say 2052 then keep on adding things to the comments below.

Have at it, good people. And thanks. I really appreciate it.
 
Hi Jeff,
In my opinion, for novices (and others), a strategy that seems to be working well, based on the posts in this forum, is to just ask for help, and experts readily jump in and solve the problems for them! (No need to bother with macros and stuff themselves!)

One does not even have to ask a complete question, since the forum regulars have mastered the art of mind-reading, and are able to offer solutions that the posters did not even realize they needed!

So, whether you are a novice, or advanced-user who is feeling lazy, you can leverage advanced VBA (and other stuff) very easily, just with a question in this forum. It is the best crowd-sourcing strategy that I have seen anywhere!

Regards,
Sajan.
 
Very good point, Sajan. And indeed it is one I will make. However that approach requires someone to first realize that they have a problem. Whereas the point of my post will be "Here's some macros you probably never even thought about using, but are incredibly useful".
 
Hi Jeff

This will be a good point of reference once completed. There is a lot of good stuff out there? I want to firstly comment on this;

In this post, I'm making the point that you don't have to know how to code in order to highly leverage VBA. All you need to know is how to Google, Cut, and Paste.
Click to expand...
This is sadly true but there is a twist. I say sadly because I know how much hard work it takes to get your head properly around vba. There is no magic bullet and it is not easy. I say a twist and this is the saving grace for mine, if you continually rip Google for code and don’t take the time to fully understand the mechanics behind the code, then you are more than likely going to have XL files with less than optimal vba. These are vba developers I refer to as “PinYin Coders”. Why PinYin, because PinYin would never appear in a written from scratch piece of vba.

The coders who code on the big forums have a bit of a brand and if you read them often you start to see their style. This style is not evident with 'PinYin Coders' of the world. This is the way I see it, when you drop code onto a forum like Chandoo you are telling a story and if you are the author, your story is consistent. Over the years I have thoroughly enjoyed seeing peoples style come out to the point where I can almost pick who wrote the code on certain posts without seeing the user name. At the same time I know the people who don’t have a Scooby. Picture this, a regular lays down a bit of code that has inefficiency at its very core, then a few days/weeks/months later drops in some of the most masterful code you have ever seen. Nope – learning takes a lot longer than that and you can’t go from Rubbish to Golden Child in a short amount of time.

Sorry I got off topic, I have a lot of time for Deb Dalglesh’s website. Ron DB has a good site but his code needs to be updated. I am enjoying reading Stack Overflow as they have some ball tearing coders helping (sorry no files). However as I finish my morning coffee with muffin chaser the most mesmerising vba templates I have come across online are the ones in the Interactive Dashboard Series by Chandoo. The vba is available, the interactivity is startling and the simplicity is repeatable. Check this link out;

http://chandoo.org/wp/2012/05/09/interactive-sales-chart-in-excel/

Absolutely Priceless!!! I am not blowing smoke up this site, in my opinion it is true, I honestly would not be posting on Chandoo if I had not been so utterly impressed with the interactive dashboards available above. There was a couple which simply knocked me off my seat and that is not easy to do.

Anyways not one to shirk an opinion piece that was my 2 cents.

Take care

Marcus
(The Smallman)
 
THanks fro the links, Debraj. What I'm specifically interested in is hearing from people about macros from sources such as these that they use day in, day out. Kind of a 'Greatest Hits' compilation.
 
Hiya Smallman. I absolutely agree that if you continually rip Google for code and don’t take the time to fully understand the mechanics behind the code, then you are more than likely going to have XL files with less than optimal vba. That said, you may well be less sub-optimal than you would otherwise be had you never learned how to put a macro in your personal macro workbook.

And I'm thinking that the personal macro workbook may well be the gateway drug that gets users hooked on the hard-stuff later on in life. Worked for me. 8 years ago, I didn't know what the dollar signs in $A$2 meant. Now I'm a hardened VBA addict. If coding was a crime, I'd be on death row :)

Hey, that's a good link, and reminds me that another post I'm going to write in future: "What particular blog posts changed your life?". I'll start another thread right away regarding that one.

Thanks again.
 
Deb

Thanks for sharing snb’s site. I have been following him for some time. He typically doesn’t declare any variables in his code(which I don’t agree with) and is all about minimalism and I didn’t realise he had a site. Some of his solutions send a shudder through me and make me question my own capabilities as he really has an ability to crystalise a problem. Also I have seen him rip a big hole through the guy I see as the best coder in the world. Not easily done!!!!

Thanks again for sharing.

Smallman
 
Hi Jeff

This;
If coding was a crime, I'd be on death row
Click to expand...
Pricelesss!!!:rolleyes:

I may have this very wrong. I thought you were a formula writing juggernaut. I see some of your solutions and they are spot on. I have not seen you jumping into the vba scene very much at all. On the Chandoo face of it you and I appear like Excel polar opposites. On Chandoo I don’t do formulas very often and you don’t do vba very often. You are the Ben to my Jerry. I think I shared the story about getting torn to shreds by a coding megatron for the longest time. We all start somewhere and be it the macro recorder or Google it is the end product that you want polished. You just hope that what gets polished is a diamond and not that other well worn analogy.

Smallman
 
@Smallman, jeffreyweir
Hi!

I agree absolutely regarding code styles, one self coding, googled code, etc. But I think we should keep an eye on the correct scope of these forums.

Let me appeal Peter Drucker and his phrase about management & leadership:
"Management is doing things right, leadership is doing the right things."

I'm gonna speak about this site members, excluding Ninjas and active members, i.e., the typical poster, either frequent or sporadic. Have you noticed that I didn't talk about people with other badges? :rolleyes:

95%, 97%, (more?, maybe) of the people that come here to ask for advices or to post questions or to pose unsolvable problems, they just need to do things, and sometimes not up to the extent of doing them right, they're happy having things done. If they have to choose between a ordinary but understandable formula and a state of the art but somehow obfuscated formula, they'll choose the 1st one! And I'd do the same myself if I were them. Only a few of them have the time/desire/skill/leitmotiv to change their mind and try to deal with -for them- an advanced Russian course; many of them will have time but not desire, or skill but not time, and their life is ruled by doing their things as right as they need. And that need doesn't include being a right thing.

A very little minority of the people, that 3% or less, actually come here to learn from what it appears as an elite to their eyes, they read everything that's posted by the guys whom they think are their wow!, they learn from lots of sources, here, there and everywhere (Google) -sounds like a Revolver's McCartney song-, they're like dry sponges eager to get wet. Most of them choose an icon, adapt their style to adopt his (never a her in this website, sheet!, yes with double e) style. I rather prefer the very few of them that, having or not a yet defined style, they try to incorporate the best of each source. These guys are those who will evolve and surpass their teachers, becoming themselves -and maybe without noticing of planning it- part of the next teacher's generation. All of them but specially this last few, are those who want to do the right things, those who need to do the right things, those who won't accept not doing the right things.

Back to the point again, almost all of the members want solutions but not technically perfect ones if they have to pay more than the value that we give to that technique, and just a very few of the members are willing to pay whatever it costs them to get "the" techniques.

I like driving, but I don't have the requisites needed to use a Bugatti and even I love it I neither suffer nor consider it, a BMW or an Audi will do the job perfectly suiting my needs. Of course Sebastian Loeb will appreciate it better than me. Our expectations are different, and it's healthy. Well, I think that the same happens with Excel, not just focusing on macros but on formulas as well.

BTW, I used to have a Swiss army knife (aka Victorinox) full featured. Later I changed it by a bigger one, no matter that I didn't use the half of the tools of the 1st one. And this process was repeated many times more, always with identical result. Nowadays I'm using one that's half of my 1st one, and I'm absolutely fine.

The same happened to my personal macro workbook, it used to have anything that would be needed (yes, in the Moon), then it got downsized dramatically, and it was more than enough too: today it's absolutely minimalistic and I'm very happy. Do you know why? It's because I always have Google at a few fingertips. And I firmly believe that I could survive with the same efficiency if from minimalistic it becomes empty. So why would I judge people (whom I agree that maybe the don't have the same skills as I have) that go thru the same process and methods of acquiring code/formulas than me? Even more: with refined search criteria (given by experience) code/formulas are yet a commodity, so why not deal with them as we do with other commodities? Evaluate, buy, sell, gain, lose, but not die nor live for them. Unless we want to become specialized traders.

Hope it helps.

Regards!

PS: My answer to your very first question is... a link to Google search! :p
 
Hold on Masters!!!

I am a member with 100+ posts and have read all your comments twice before I dare to write up here.

Yes... I agree with you all that google god grants almost everything you ask for and makes you wiser overtime, if you have a desire, passion and time to learn on. But, as a intermediate VBA user (I can code a bit myself and can understand and debug others also) its not easy to find all the sites you guys following and mentioned above. Honestly, I hit some of these sites only after I started seeing Chandoo.

I was in a situataion many times for coding myself or trying to resolve others posts on Chandoo, that if I get a ready code to do this X functionality, I can modify this to meet my requirements/others posts. Also, very often i say myself that "I have faced a similar problem in the past and resolved it but cannot recall, how did I do that?" (I have started adding the useful links to my IE favorites to reduce this syndrome). This might have happened to you also and the first thing we do is to hit search on Chandoo/other blogs, find the link and update the OP. Otherwise, goto Google. Though we will find what we want after few search, It will be good if we have one link to start on before googling. Thats what I guess Jeff is aiming at. I stand with Jeff here as this might turn up a link or a personal workbook which has almost all the stuff (for about 70-80% of Chandoo users) to refer on before they ask a question. The other side of it is that the post counts might come down.

Though I claim myself Intermediate VBA user, I am still at the basic level with the Excel formulas.I envy at the Excel formula masters here, who can melt any hard structures to the results. I am following the formula challenges, trying to resolve (failed so far) but cannot find all the formulas cracked on Chandoo under one shelter. Repeated posts on the similar issue seeking formula solutions is very common and obviously the answers will be the links to the previous posts (like extracting firstname/lastname, numbers from a text string etc.,).So, can we have something on Jeff's idea to have a vault with all the commonly used Formulas to refer on before anyone post? We beat the drums for sometime and am sure it will become a practice overtime.

We never know,it will become an Exceloogle.
 
I love the discussion. I've been following this forum quite some time now and I've learned a lot. Thank you guys for everything. Your contributions on this forum made me love XL even more.
Thanks and keep it coming!!:):)
 
Jeff
"Here's some macros you probably never even thought about using, but are incredibly useful".
Click to expand...

As alluded to by Sajan and SirJB7 most of the posters do not really understand the problem they have and it is only after encouragement from members that they start to give more information and this slowly leads to an answer.
And if you hit the majority in the face with a bucket load of VBA they would not know what to do with it and from previous posts where to put it.

The vault was an idea where formula/functions and VBA that was complete and working could be placed but it has become another general question section with posts growing as things are added and changed.

If members have code that does what the description/where to put/how to run and the concise comments say it does ( and comments would most definitely be needed) then why not use the vault.

I do think that placement in the vault should be done by those invited to have access to do so, this way only working tried and tested formulas/functions and code would get there and not all the amendments that are not really warranted or needed..
 
Hi Bobhc. And if you hit the majority in the face with a bucket load of VBA they would not know what to do with it and from previous posts where to put it. Agreed. That's why in the post I'm writing for Chandoo's blog I'm giving step by step instructions on how to set up personal macro workbook and switch on developer tab.

why not use the vault. Because I rather embarrassingly never thought to look there, despite answering questions posted there. I'll have a browse through. Maybe this thread should be moved there? Maybe I'm duplicating a thread that already is in the vault. Oops. :oops:
 
@alphamax: Thanks, very helpful. In my draft post I'm going to give some suggestions as to what types of routines you might consider putting into a personal macro workbook. THis is a great example: macros that make it easier to write formulas, or help do away with convoluted formulas altogether.
 
@SmallMan: I just discovered that I'm a PinYin Coder. I used it in some very efficient code that is a work in progress. Which makesme think that maybe "PinYin" isn't a great identifier of unstructured code, but is merely an identifier of someone who didn't 100% know what every property/method they come across does, but does know that including something like PinYin doesn't make 100% of a difference in run time, if any.

How did I end up with this in my code? Well, like any other coder without total recall, I ofter use the Macro Recorder to identify what the names are of the various properties and methods of the many many objects in Excel. So sometimes I fire up the macro recorder and see what it spits out. Sometime I don't know exactly what all those properties do. And sometimes if something looks like it might be useful, I'll leave it in without checking, because I know that at best it might be important or perhaps even more efficient, and at worst it might make little or no difference at all.

In fact I still don't know what PinYin does. Sure, I could Google every property before I leave it in, but sometimes I have quite a few other things that I'm trying to hold in my mental RAM regarding a big or complicated routine, so I just say "Well I think these properties can go, and these other ones I'll leave in for now because they look like they might mean something". And the 'for now' ends up being 'for ever'.

PinYin obviously fell into that category when I was writing up some very very efficient code to filter large pivottables quickly based on an external range.

Did leaving it in make any discernible difference to my routine? Nope, I don't think so...In fact I think my routine perhaps the fastest in the world at filtering very large Pivots based on an external range. It does something very very novel that I think only I have leveraged in the way that I do.

Does the presence of PinYin detract from my 'salt' as a coder? I don't think so, because I believe that despite it being there, my code is still the fastest in the world at what it does.

Could removing PinYin make it faster? I doubt it would make much difference...this particular sorting routine is a very small player in what is a very complicated but ingenious approach. Which I'm still tweaking, so not quite ready to share.

I'll have a Google sometime and see what PinYin does.
 
To crystalise what I'm trying to say above: I guess it's true that PinYin would never appear in a written from scratch piece of vba. But plenty of code not written from scratch is actually efficient, and plenty of code that IS written from scratch is actually inefficient.
 
Jeff

My term is starting to gain some traction. It is mine and I am patenting it. I like it!!!! The guys at work who write code day in day out do it very badly IMO. Google or the recorder can't always get the job done in a bespoke project. PinYin coders - shakes head.

But plenty of code not written from scratch is actually efficient, and plenty of code that IS written from scratch is actually inefficient.
Click to expand...

Agree - It is not the Wand but the Wizard behind it.

I always get nervous when people say some thing they have created is this or that. Your VBA work should be out there for us all to behold. Drop some of it in this thread so we can see its speed and dexterity. Start with your little PinYin number :)

Like your Formula Challenges many minds improve on what was once the most wonderful instance in existence.


Take care

Smallman
 
I'm currently tweaking the code further to make it bulletproof, and will post on either Chandoo or DDOE fairly soon. Agreed that the hive mind can take stuff further than it will ever go in isolation.
 
I'm currently tweaking the code further to make it bulletproof
Click to expand...

It won't be bullet proof. Google is no match for the collective power of people. I honestly believe 95% of the most stunning vba ideas are locked up inside peoples heads rather than in the global encyclopaedia we call Google. There will be lots of wiggle room you have not even thought of yet. Guarantee it.

Next time

Smallman
 
You must log in or register to reply here.
Back
Top