Excel Scripting for Dummies

As part of my New Year's Resolutions, I would like to pick up a bit of light Excel scripting. I'm a complete and utter noob when it comes to programming of any kind though.

What I would like to do is create scripts to automatically edit reports. One would be to reformat the report from our old CRM system to fit with the new reports (change of font size, columns, ...). Another would be to trim an inventory report until only the relevant info is left.

There's so much out there though that I'm kind of lost. Do you know of a good place for a dummy to start?

I audited a visual basic course at my local community college when I was in your same position. While that may not be an option for you, if you were to get a beginner's guide to VB or VBA, it may help to work through it to get use to the VB structure. Once you have a feel for that, the best thing I can recommend for Excel is recording macros with you doing stuff, and then reviewing them to see what it recorded. While it is not optimized code by any stretch, it will give you an idea of the options you have available for controling Excel using VBA. Many of my scripts start out this very way, although the end result is heavily modified.

If you're totally new to programming, you might want something like VBA for Dummies. (All the Office apps use Visual BASIC for Applications, VBA, thus the name of the book.) This should get you over the early conceptual hurdles of how BASIC works. It's not very hard, but if you're totally green, you'll probably want some help to get the earliest ideas.

From there, the idea of recording macros and looking at the code is a very good one.

It's been said that learning programming with BASIC results in brain damage, however. That's how I learned, and I've never been a very good programmer. It could be the truth.

I'll also say that recording macros then looking at the code generated is a good place to start. The recorder creates very verbose code that can nearly always be pared down for readability and performance enhancements, but it's a great place to learn the hierarchy of objects that Excel uses in its programming model. The language itself is fairly straightforward, though it has a stigma among programmers, and the built-in editor is less than stellar.

I didn't even know something like recording macros existed. :-O Definitely going to try that for my next renewal report!

I got me that dummy book too.

Pressing Alt+F11 in Excel will bring up the VBA editor, and in Excel 2007 and up you can go into the options for Excel to display the Developer tab in the Ribbon. You'll be using both of those extensively.

I was going to recommend VBA for Dummies too, good call Malor.

I've spent far too much of my professional career writing VBA for Excel. Feel free to ask questions here as well as on Stack Overflow. It's a pretty solid resource.

Like the others said, one of the best ways to start is to record macros that do some of what you are trying to do and then go in and review/edit them.

That said, I've got a lot of VBA experience as well, so feel free to ask here for more specifics.

I started in the VBA for Dummies book, and it's turning out to be quite an entertaining read so far. The author sometimes tries a bit too hard to be funny, but still a fun read so far. I'm not in too much of a hurry, as I don't want to burn myself. But I'll be sure to take you guys up on your offer. If I need any pointers I'll be sure to ask!

Thanks again guys. This community rocks and/or rolls.

I commend your desire to update your personal skills. As a guy who manages a reporting team for a living, you just gave me the shivers. Unless you ARE your IT organization, you are heading down a bad, dark, scary path labeled "Shadow IT".

I'd say go ahead and do that, but keep your real IT organization in the loop as to what you are doing and why.

That's also good advice. A lot of that depends on your IT culture, though. Some IT teams think their job is to make sure nothing ever breaks, and to do that, they to freeze everything in amber and never let anything change, except through hyper-controlled testing. I think of those guys as "IT NO" -- the people who try to stop you from solving your actual problems, so they can avoid fallout from management. If you have a team like that, doing things like writing macros yourself is often the only reasonable way to get your job done, and you may have to actively hide what you're doing.

The good teams understand that they exist to make your life better, and they'll work hard to let you do what you need. ("IT YES") If your macros spread past more than just you, into your department, keep IT in the loop, and try to write up documentation on what they do, where they live, and what resources they need. That last is probably just your specific version of Office. If the department ends up really using them a lot, taking the time to check that they work on earlier and later versions of Office can make the IT team's jobs much easier.

If the macros end up being REALLY important, creating a test document with sample cases and expected results can be a real blessing. If you ever think that the company might lose money if the macros fail to work correctly, then it's probably worth writing a test document for them.

If they're evaluating a new version of the software, a single test document that tells them whether your department will be okay on the new version is sooooooo useful. They can tell in five minutes whether it's going to be okay, instead of finding out halfway through a deployment.

You don't need to worry about that, yet, though. Only if your stuff goes into real production with other people.

Check out this website http://www.teachexcel.com/free-excel...

There's a good forum on there too where you can post code your struggling with and you can get help for your specific problem. It's also worth getting a free Skydrive account with Hotmail so you can upload your spreadsheets you need help with and the kind people on there can help. I've used it before a few times and now is a proud member of my permanent bookmarks