I’ve been publishing my ExcelTips newsletter for quite a long time now, approaching 17 years. Every week, without fail, I’ve published tips on how to use almost every aspect of Excel.
During that time, I’ve also conducted various seminars on teaching people how to use Excel. I’ve also developed a good number of e-books and real books about how to use Excel.
One thing that I haven’t done, however, is to create any video-based training materials about Excel. I’ve had people ask about them, but I’ve always shied away from them because of, I guess, being a harsh critic of myself.
Well, I’m thinking of changing that situation and coming up with a new online course about how to create macros. The course would be geared toward beginners—those who haven’t ever created their own macros before. (I may do a more advanced macro course later, but it is generally best to start at the beginning.)
Like I said, I’m still toying with the idea; trying to get my head around what the course would (or should) contain. This is always the toughest part for me—figuring out what to include and what to leave out.
Do you think offering such a course would be a good idea? Do you have thoughts as to what such a course should cover? If so, I’d love to have a bit of feedback. Please, let me know below what you think.
Great idea! I am currently taking an online class is Excel but I use it so much, I feel a course on just macros would be very helpful. Start with the basics, I suggest. First course, when is a macro appropriate and when can you accomplish the same thing with tools you already have!
Couple of suggestions. Check out your competition on YouTube. I’m still learning to use that site for stuff like various household repairs, computer and telephone operation, cooking lessons, etc. Also, consider how you’ll have to adjust your course organization to account for the multitude of Excel versions. For example, because I only have a need for straightforward personal spreadsheets, I’m still using Excel 2002. Do you have some info about the proportions of which Excel versions your e-mail tips readers are using? That could guide your efforts, ’cause I don’t think you’d want to make 5 or 6 versions of each video.
Good luck! Looking forward to the first release!
I would love to be able to watch the process of writing macros – right from the basics through to more in depth. Reading the instructions is one thing, seeing it is totally different.
Bring it on
Of course a good idea. The crucial point is to get people interested. I have seen a lot of people doing the same nasty tasks again and agian without knowing how easy it is to use macros. These people would be a good audience. But I don’t know how to get their interest. Another issue is quality. Doing tasks (semi)-automated could increase data quality, if macros are written in a good documented way. Maybe their could be an option to upload macros and have them ‘quality checked’ by the community?
Awesome idea. I know a little about Excel, but absolutely nothing about macros. I would be quite interested.
It’s a great idea. It’s always better to see the process in action than just cold, finished text. Though I know it takes much more time to set up, please make the examples you use realistic. On the subject of your competition, I’ve found a number of the Mr Excel / Mike Girvin type of quick podcast presentations very useful.
Instant gratification on a training video. That is what our society had put on when watching a video, it could be from “you tube” or any other video source around.
Your training video should show a spreadsheet with the original data and then what you want to accomplish with your macro. You also tell how much time it takes to do it manually from a real hands on work done at company ABC. Videos should last only 30 seconds to 2 minutes tops.
You run the macro on the video and then you got the results. This is what always amazes non excel developers how fast they get their results. You also put a clock next to it on how long it takes to run the macro. Then you put a link at the end of the video to the code that is run by the macro.
Of course, only those that are really interested will look at how to develop that macro…. I had work with macros since lotus 123 (1989) and then excel (1994); but people that are not programmers nor developers will never be interested on learning the amazing things macros can do… They just get amazed and go on with their daily manual work.
On the linked code you could explain each piece of code and also why you put or explain that piece of code thru another link, this way only those that do not understand that piece of code will follow the secondary link; otherwise, those that know the answer can continue reading the remaining of the texted macro.
Videos of 30 seconds makes it more palatable to see and doesn’t make you feel that you are “wasting your time”. I had sometimes been directed to videos of 40 minutes which then I just scrolled the frames to see if they were really worth my time.
As to what to make training videos on, I would say that making a video of the tips you had shown and checking those that had the highest and more positive feedback from your audience would be those that should get the prime time slot of your effort.
Thanks for your weekly tips posting, I really enjoy them.
I know that your next website with the video training contents will be a total success, given the records of your past experience and the good track each weekly tips are on.
For me, would completely depend on cost. There are so many ‘great’ courses it’d be fantastic to do, but cost is usually prohibitive for professional development.
It’s fine, but I NEVER use macros, because I’ve heard how they can be a security issue. Because of this, any time anything about macros comes up in your newsletter, Allen, I ignore it.
What I’d like to know about FIRST, is all about this aspect of them. How are macros a security issue, and how can the Excel (or other Office Product) user know that they have the security issue tightly buttoned down?
Cheers.
I agree with Chris; I tried using a macro, but some people in our office couldn’t open the file with the macro in it (our portal didn’t let them, so I got rid of it. I t was a simple macro so the file always opened to the first worksheet. So not only why macros are security issues, but how can they be used and not appear to be a risk to the system?
Great idea as there are a lot of “talking heads” or voice-over material that could be greatly improved upon.
Absolutely
I use Excel all the time but have never ventured near the use of macros. I would be very interested in learning the process from the beginning and when it is best to use them.
Me, too. Please alert us when this comes out.
I think a video outlet for your work would be very useful, but I would try to do some things just a bit different than other macro trainers. For example, I am self-trained in macro development and there are some topics that are never well covered in the available Youtube videos. Things like:
* How to use the IDE effectively (use of immediate window, event driven macros, etc).
* How to setup a dialog box
* When and how to use class modules
* Referencing libraries
* Efficient ways of loading in variable length text data from a file
Great idea
How would you differentiate yourself from other providers?
An excellent idea, Allen. As suggested in earlier comments, walk before you run! In other words, don’t forget to include familiarisation with the environment before you write anything but the most basic code. I write a lot of macros, some of them complicated, but there are many elements of the environment that I don’t use and would be keen to know how they could help me.
Good luck with the project.
It’s a good idea, especially if it’s going to be free, like your tips newsletter. a lot of people interested in writing macros may never have written a program before so I’d suggest including basic programming techniques like controlling loops, conditional statements, error handling, user input/displaying messages. Maybe do these as seperate items so that programmers (like myself) can ignore them.
Great idea. Staff tend to avoid using macros, as there is not much or little help available. Keep up the good work !
I think this is an excellent idea.
I think you should definitely do it. As someone who has read macro beginners books and occassionally reads macros others have written and has to adjust them, I have never got beyond the beginner stage because VBA is not something I have to use very often: couple of times a year really, and onyl very basic stuff like looping boring tasks. The result is that the syntax is bever embedded in my memory and that can cause problems.
I think I am the kind of user who especially benefits from your blog and emails: intermediate to advanced excel user still learning some of the tips and tricks who understands the basics but struggles to get comfortable with lots of VBA code.
As such, one thing I would really concentrate on, as I dont find it is covered in enough depth amongst beginners courses and not revised enough when doing more intermediate stuff, is referring to ranges, and the best way to do this when performing slightly more complicated operations than the stuf they get you to do at the beginners level. SO if I want to define a range of uinknown size depending on the data, should I be using the cells method, should I be using current region to definte the range and then a for each loop within my defined range, should I try a combination of xldown, then count rows and columns to definte the number of loops, and if so should I do that via the cells function or offset etc.
This is stuff I have had lots of trouble with, as I find, possibly depending on the xl version I use, as combining cells method with xl down does not always seem to work, and I think it is because some of the subtlety of referring to ranges is lost on me, and I expect a lot of people who are beginners but want to do more than just create a macro that gives a message box sayin “hello” when looping (ie fill in field values based if else functions, format and filter cells depending on other cells within a defined range, output results onto another defined range, all using code that can account for different size of the range depending on the data.
A saw someone else said check out what your competition does, well I recommend this to you because I think there is a gap for this kind of advice that sits between absolute beginners and the lower intermediate level.
Perhaps start with simple every day tasks:
How to take a regular report and show different things that you can do – 1 part at a time – in the 30 seconds to 2 min formats suggested above: e.g.
Deleting / adding columns (totals)
Formatting columns and rows (dates / new column for month / quarter)
Sorting
Creating a template which can be shared / having an end product where macros are no longer active (to answer security issues)
How it works with a fixed report format (perform the actions rather than code) vs reports where the number of rows or columns varies.
Only once beginners (like me) can see it do something useful and quickly ( I like the timer idea) will the interest be there to try out more “advanced” macros.
I enjoy reading through many of your tips – and also find the discussions underneath informative – having alternative ways of doing the same task or dealing with unexpected problems
Keep up the good work.
A great idea and a real opportunity to get people new to programming into good habits too
o comment, comment, comment
o Hungarian notation from day one
o use functions and don’t put it all in one big routine
You know them all!
Good luck
A great teaching concept Allen, but definitely not new. Don’t let that put you off though, there is always room for quality presentations and I believe you would be able to pull that off.
As suggested by Steve G, look at your opposition on Youtube and other Excel related sites – There are some good productions and you will need to be at least as good and preferably better.
Low quality/resolution, out of focus videos are in abundance. Serious users will appreciate and hi-res, quality productions and your user base will quickly grow.
I use simple macros a great deal, often to perform the simple task of sorting a spread sheet, and create graphic “buttons” that I assign to the macros. This I would consider a very useful macro usage to include in a “beginners” tutorial.
Best of luck, looking forward to seeing the results!
Allen. I have been using and enjoying your weekly newsletter for excel for the last couple of months. Definitely the expectations from your video tutorials are high. Small videos explaining step by step would be well received as the currently available though many of them are good speak about a very narrow spectrum functionality of Excel.
Also you need not make video courses for very simple tools as your newsletters cover them explicitly. Instead, stuff that gives hiccups with a read and learn approach would be well received. Examples, macros, pivottables, charts, filters etc. Good luck.
VBA. But would only take if affordable – say $39.95 (partly because I’m extremely good, but would still take because I realize that others may do things differently and always pick-up things from different methods). Make sure that you provide a certification (for certification-chasers like me). Lot of competition out there and free stuff. Could also consider using Coursera.org or EdX.org as a platform – making your own little “MOOC” (massive open online courses).
Interesting ideas, Mark. I’d thought of a MOOC, but didn’t want to introduce the complexity that goes with them. (Recent studies show that MOOCs have developed to be used, predominantly, by educators, not by non-educators. I suspect it is because the platform was created by educators in the first place.)
I’ve not looked into official certifications. Any thoughts on how that should be done? Are there organizations that I need to work with?
-Allen
Absolutely a good idea and starting with basics would be best for the most popular/in use versions of Excel perhaps with callouts for what would be different in older versions.