Useful Excel and VBA References

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Useful Excel and VBA References

Post by Rudi »

I thought this might be a useful idea to compile a list of references for members who need guidance with Excel or VBA, like putting macros into Excel modules, or for those times where a member requests for reference material like online tutorials or books about Excel or VBA. The following post contains references to various links and favourite locations for these topics. Included are other topics like where to find the offline VBA Helpfiles, writing VBA functions, creating Custom User Forms, using VBA to manipulate the VBA Editor, great Add-In's and of course our very own Random Data Generator :grin: (...had to squeeze that in here!!!).

Quick Reference: How to integrate macros into Excel

====================================================================================================================================
Standard Macro
Copy the code in the post
Open Excel and the workbook that requires the code
Press ALT+F11
From the Insert Menu of the VBA window, select Module
Paste the code onto this new module
Close the VBA window
In Excel, press ALT+F8
Select the macro you pasted from the list
Click Run

For a more detailed process (with screen shots) to integrate you macro, see this page.
Or see step by steps at Contextures: Excel VBA -- http://www.contextures.com/xlvba01.html" onclick="window.open(this.href);return false;

Sheet/Workbook Event Macro
Copy the code in the post
Open Excel and the workbook that requires the code
Right click on the sheet tab on which the macro should run
Select View Code
Paste the macro on the module that appears on the right
(For a Workbook Event Macro – Double click on This Workbook in the projects window and paste code on this)
Close the VBA window
The macro will now run in the sheet based on the trigger event

For a more detailed process (with screen shots) to integrate you macro, see this page.
Or see step by steps at Contextures: Excel VBA -- http://www.contextures.com/xlvba01.html" onclick="window.open(this.href);return false;

====================================================================================================================================

VBA Online Tutorials (FREE)
There are good books that you can purchase, but there is a wealth of free tutorials on the web that can give you a good introduction into VBA. If you work through some of these tutorials and put into practice what you learn, you will very quickly get the just of coding in Excel. It's really just a matter of "playing" with the code, recording macros and learning of the objects and hierarchies and exercise.

Start with these online tutorials:
-- http://www.homeandlearn.org/index.html" onclick="window.open(this.href);return false;
-- http://excelvbatutor.com/vba_tutorial.html" onclick="window.open(this.href);return false;
-- http://www.excel-easy.com/vba.html" onclick="window.open(this.href);return false;
-- http://www.excel-vba.com/excel-vba-contents.htm" onclick="window.open(this.href);return false;
-- http://www.excel-pratique.com/en/vba.php" onclick="window.open(this.href);return false;
-- http://www.anthony-vba.kefra.com/vba/vbabasic1.htm" onclick="window.open(this.href);return false;

VBA Books
You cannot go wrong with John Walkenbach's books. They are very good, and there is a wide selection from intro's to Excel and VBA to high end programming in VBA, tips and tricks books, the Excel Bible, Excel for Dummies and books focusing only on formulas. I'd definitely start there if you want an actual "hand held" book. See this page for a listing of his books, and here on his website...

Excel Online Tutorials (FREE) and YouTube Videos
See: Microsoft's Official Excel Online Training Site
and: Learn Excel Without Spending a Penny: 40 Free Excel Tutorials & Reference Sites
and: Free Online Education: Word & Excel Training
and: http://www.gcflearnfree.org/excel" onclick="window.open(this.href);return false;
and: Excel Exposure
and: Wise Owl VBA Tutorial Videos

====================================================================================================================================
Additional Resources

Offline help for VBA in the Office 2013 applications.
Download: Download Office 2013 VBA Documentation.

What is a Visual Basic Module?
http://www.emagenit.com/VBA%20Folder...vba_module.htm" onclick="window.open(this.href);return false;

Writing VBA Function in Excel
http://office.microsoft.com/en-us/ex...117011033.aspx" onclick="window.open(this.href);return false;
http://www.exceltip.com/st/Writing_Y...Excel/631.html" onclick="window.open(this.href);return false;

User Form Creation
http://www.contextures.com/xlUserForm01.html" onclick="window.open(this.href);return false;

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx" onclick="window.open(this.href);return false;

Excel 4 Macro Help in .CHM Format
File and quoted description below supplied by our very own AlanMiller
There is occasionally a reference made to Excel 4 macros. The old help file for these is in the Windows .HLP format, which requires a special viewer on modern Windows versions. The link below contains my own attempt at converting this to a .CHM file. While I think all the contents translated across, some organization features are lost. If there is a demand, I can redo it more thoroughly.
Download the file here.

Useful VBA Add-Ins
MZTools 3.0 (VBA Enhancer): http://www.mztools.com/v3/mztools3.aspx" onclick="window.open(this.href);return false;
Rob Bovey's VBA Code Cleaner: http://www.appspro.com/Utilities/CodeCleaner.htm" onclick="window.open(this.href);return false;
Smart Indenter (Auto Indent Code Blocks): http://www.oaltd.co.uk/indenter/indentpage.asp" onclick="window.open(this.href);return false;

Useful Excel Add-Ins
Name Manager by Jan Karel Pieterse: http://www.jkp-ads.com/officemarketplacenm-en.asp" onclick="window.open(this.href);return false;
XY Chart Labeler by Rob Bovey: http://www.appspro.com/Utilities/ChartLabeler.htm" onclick="window.open(this.href);return false;
Filter Highlighter by Rory: HiLite Filter
Generate Random Test Data: Eileen’s Random Data Generator
ASAP Utilities for Excel: http://www.asap-utilities.com/" onclick="window.open(this.href);return false;

=======================================
See this thread for links to Word Resources:
Last edited by Rudi on 03 Aug 2015, 09:20, edited 5 times in total.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

srinivasanyadhav
StarLounger
Posts: 81
Joined: 21 Apr 2014, 10:45
Location: Chennai, India

Re: Useful Excel and VBA References

Post by srinivasanyadhav »

Many Thanks Rudi.

I am really impressed and get interested by you and Hans Knowledge.
I already refer few sites, you people guided.
This adds my list of reference pages and we are very thankful for what you are doing to us.
Regards,
Srinivasan

BenCasey
4StarLounger
Posts: 495
Joined: 13 Sep 2013, 07:56

Re: Useful Excel and VBA References

Post by BenCasey »

Great Stuff, Rudi.
Well done and a very useful set of links as well. :clapping:
Regards, Ben

"Science is the belief in the ignorance of the experts."
- Richard Feynman

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Useful Excel and VBA References

Post by Rudi »

TX guys....
The idea was to prevent the need to backtrack through older posts or the search feature to find previous references to these common type questions. Now it is very simple to just point a member to this post or reference it quickly for them to peruse....
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

PJ_in_FL
5StarLounger
Posts: 1090
Joined: 21 Jan 2011, 16:51
Location: Florida

Re: Useful Excel and VBA References

Post by PJ_in_FL »

Rudi,

Excellent list! Can this be added to the "Global Announcements" pane of the portal?

I'd suggest considering the following additions:

Charting Examples:
http://www.andypope.info/charts.htm Andy Pope's Excel Charting techniques, solutions and examples

Examples and snippets of VBA code:
http://dailydoseofexcel.com/ Daily Dose of Excel
http://www.jpsoftwaretech.com/excel-vba/ JP Technologies

Templates, examples, free and paid:
http://www.ozgrid.com/ MICROSOFT EXCEL SITE BY OZGRID
PJ in (usually sunny) FL

PJ_in_FL
5StarLounger
Posts: 1090
Joined: 21 Jan 2011, 16:51
Location: Florida

Re: Useful Excel and VBA References

Post by PJ_in_FL »

Ulp! Almost overlooked another excellent charting resource:

http://peltiertech.com/Excel/Charts/ChartIndex.html Jon's Excel Charts and Tutorials - Index
PJ in (usually sunny) FL

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Useful Excel and VBA References

Post by Rudi »

Hi PJ

The post is a sticky, so it will be easily available for reference in the future.

Thanks for the additional links. The initial post focuses on tutorials for VBA specifically (though a few Excel tutorials are also mentioned). I did add some additional reference links pointing to add-ins and the like since these came up in initial searches when compiling the links. I know I missed out on probably MANY other excellent web sites and material that I could have added...but the idea was just for a basic list that we could refer members to if they inquired about training material or tutorials in VBA or Excel.

You (and anyone else) are welcome to add further references to your favourite sites. Please fell free to since this is an open thread. Anyone looking for good books, tutorials or references will be able to peruse the links uploaded and get some great material to educate themselves.

TX again for those additional links. Some excellent sites for sure. :smile:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

Re: Useful Excel and VBA References

Post by chamdan »

Excellent links you provided!

:thankyou: :cheers:

Chuck

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Useful Excel and VBA References

Post by dasadler »

Good job Rudi, thanks for posting this.

A couple of references that I have very useful deal with:

Excel Index and Match Functions
and
Excel INDIRECT Function

For some reason, I have a hard time really understanding and using these functions so it helps to have a ready reference.
Don

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Useful Excel and VBA References

Post by Rudi »

Thanks for the comments and additional links Don and Chuck.
Appreciate the contributions.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

cecil
StarLounger
Posts: 98
Joined: 09 Sep 2010, 16:01

Re: Useful Excel and VBA References

Post by cecil »

I like the list of links provided. Some I use often. I hope you don't mind if I add one
http://spreadsheetpage.com/" onclick="window.open(this.href);return false;

John Walkenbach has done some great stuff. Over the years I have learned more from John and Jan Karel Pieterse than anyone. I have learned from others, but those are the two big ones for me.

User avatar
HansV
Administrator
Posts: 78153
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Useful Excel and VBA References

Post by HansV »

Yes, I've learnt a lot from both of them too! :thumbup:
Best wishes,
Hans

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: Useful Excel and VBA References

Post by PRADEEPB270 »

Rudi,many thanks for the great knowledge sharing with us.It will be work as a master knowledge for me.Definitely, we will learn more and more concept,perhaps we are unknown.Excellent Rudi.

Regards

Pradeep
Regards

Pradeep Kumar Gupta
INDIA

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Useful Excel and VBA References

Post by Rudi »

TX for your kind words Pradeep. :smile:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
geecee
3StarLounger
Posts: 357
Joined: 02 Jun 2013, 05:55
Location: Australia - SOUTH MORANG - A northern suburb of the city of MELBOURNE in the state of Victoria

Re: Useful Excel and VBA References

Post by geecee »

I found Excel Macro Mastery The Missing VBA Handbook here at http://excelmacromastery.com/Blog/index.php/products/. It's going to be very useful to me I know that so I've pinned the tab to Mozilla Firefox.
George

When we're gaun up the hill o’ fortune, may we ne'er meet a frien' comin' doun!
(When we are going up the hill of fortune, may we never meet a friend coming down!)

:smile: Don't cry because it's over...Smile because it happened.l :smile:

:note: At the end of the day it's midnight. :note:


LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Useful Excel and VBA References

Post by LisaGreen »

OMG!!!!

Rudi!!!

This is genius!!!

Thank You!!!!

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Useful Excel and VBA References

Post by Rudi »

It turned out to be a useful post, at least for myself!!!! :grin:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Useful Excel and VBA References

Post by LisaGreen »

Personally I think he's a maverick and not a good one ... sorry SNB... because of the totally minimalist approach... but that's another story.... But SNBs site is actually worth a look.

Lisa

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Useful Excel and VBA References

Post by Rudi »

I am aware of that site TX, but its not for a novice, hence not listed above.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

Diana van den Berg
4StarLounger
Posts: 582
Joined: 06 May 2012, 20:05

Re: Useful Excel and VBA References

Post by Diana van den Berg »

WOW, Rudi and the people who have added more!!! What a TREASURE CHEST! I CAN'T WAIT (but will have to, lol) to find the random moments to dip into it from time to time to time to time! THANK YOU! :clapping: :cheers: :chocciebar: :thankyou: