General Question

2davidc8's avatar

Can you recommend a good book for learning Excel programming and macros?

Asked by 2davidc8 (4984 points ) July 15th, 2014

As asked.

Is VBA still used for this?

Thanks!

Observing members: 0 Composing members: 0

10 Answers

CWOTUS's avatar

Yes, (at least in Office 2010), VBA is still part of the installation package, and highly useful.

I would suggest that you start to learn coding on your own, before buying a book and going that route. And that’s easier to do than say (as opposed to the way that saying usually goes), because you can learn VBA syntax by recording macros and then learning how to edit them to make them more universal, more interactive and more powerful.

Open a new workbook in Excel.

Go to the “Developer” tab (you may need to activate this as part of the ribbon, which you can do through the “customization” menus).

At “Developer” click “record macro” and accept the default option to save “in this workbook”. (Later, when your macros become more general and more useful, you’ll want to save them to a file called Personal.XLSM, which should open every time Excel opens, making your useful macros available to you all the time. But I digress.

Record yourself doing some things in Excel. At this point it hardly matters what. Navigate to a new worksheet, enter a formula or something, and just generally mess around a bit. Or do something very intentional. At this point it doesn’t matter.

Click the button at the bottom of the screen to “stop recording”.

Go back to the Developer tab and click “View Code” navigate as needed to review the VBA code that has been recorded.

Now you’re ready to start learning VBA.

I advise a site such as MrExcel.com (there are loads of them out there) to browse, ask general and specific questions, and even more importantly: answer questions from people who know even less than you.

I didn’t start to really learn Excel until I tried to answer others’ questions, which led me down paths that I hadn’t even considered. You’d be surprised. You’d be surprised how quickly you start to learn VBA just by reading and tweaking what you’ve recorded, too, and then learning the steps to write IF… THEN… ELSE… ENDIF and DO WHILE and other looping and branching statements. Before long you’ll be answering my questions there, I suppose.

They can also give excellent advice about books, other websites, and even some of the Microsoft MVP websites that many of them maintain.

jaytkay's avatar

I like reference books, where I can look up specific questions when I need help. I carried the book VB & VBA in a Nutshell around daily for a couple of years.

You can find used copies online for a few dollars.

And I agree with @CWOTUS, you can learn a lot by recording macros and then reading the VBA code created by Excel.

That’s how I learned. I had a lot of daily tasks using Excel. For example, open text documents from different companies, sort and extract lists of products and prices. Then save the lists in a standard format and compile them all together in a master list.

First I would manually perform the tasks. Then I discovered macros and automated parts of the work. Then I looked at the macro code and figured out how to write enough VBA to make my 1-hour daily task into a single macro. I could schedule it and it would do the job for me, or make at least make it easy enough to delegate the job to a non-technical person.

CWOTUS's avatar

Exactly. One of the best jobs I ever had occurred because I was hired to be a temporary Quality inspector at a company during the maternity leave of the woman who held the job. She showed me before she left what she did on a daily basis – most of which took her nearly six hours of the day to complete one primary process. The first day I did “her job”, I recorded a few macros to do the same things step-wise, and then edited and linked the steps to make a documented, interactive and editable process. Before two weeks were up I had automated her entire day into a five-minute macro (on a slow computer and with inconsistently formatted inputs) that produced her exact outputs, and I started looking for more work.

So they gave it to me, and I was permanent after that. (The replaced engineer had a lovely little girl, and months later she came back to work and found more work for herself, too. There’s always work for Quality.)

2davidc8's avatar

Thank you, @CWOTUS and @jaytkay, for your suggestions. Actually, I have been a programmer in the past, so I can handle coding. What I am thinking of doing is to build an “app” to help me learn foreign languages. I put “app” in quotes because it will by no means be a full-fledged app. But it will need to be able to handle foreign characters. Maybe this is where the forums could help me. @CWOTUS, can you please list a few more Q&A sites like MrExcel? The “app” is just for my own benefit, and I don’t intend to sell it.

Also, I’m asking about VBA because I’m not sure if it is still the language of choice under MS Office 2013 and for Microsoft products going forward. I don’t want to spend time learning a language that’s going to be phased out soon and I won’t be able to use it the next time I upgrade Office.

CWOTUS's avatar

In no particular order:
http://www.thespreadsheetguru.com/blog/
http://www.excelfox.com/forum/f22/
http://xldynamic.com/source/xld.SUMPRODUCT.html

There are others that I know of but don’t retain bookmarks for, such as Chip Pearson, who writes some pretty amazing stuff from time to time, OzGrid, an Excel forum that I haven’t visited in some time but used to be a favorite before MrExcel, and others that I’m not thinking of right now.

Really, without putting you off, you can find more than you’d have time for – and some that you’d like better than my suggestions – with a search of “Excel forum” in google.

2davidc8's avatar

Thanks, @CWOTUS. Those sites are a great start! Since I get the impression that you know quite a bit about Excel and those sites came to your mind first, they’ll carry extra weight with me and I’ll start with them first. Much appreciated!

2davidc8's avatar

@CWOTUS @jaytkay BTW, what do you think of StackExchange ?

CWOTUS's avatar

I barely know the rudiments of VBA, and have never studied it. There are a lot of things that completely flummox me, and anyone who can understand and competently use array formulas in Excel I look upon as a god.

I know enough to audit fairly complex files, troubleshoot a wide range of problems, create “just enough” formulas and code to get done the things that I need to do, and manipulate strings like a puppeteer. But I’m no expert. I do not know StackExchange at all.

dappled_leaves's avatar

@CWOTUS “anyone who can understand and competently use array formulas in Excel I look upon as a god”

I don’t know anyone who can do this – but mainly because they would be using different tools than VBA for it. In my line it would be Matlab, C++, or even Fortran before VBA.

CWOTUS's avatar

Not even VBA… people can do it in native Excel spreadsheets through array formulas only. I am amazed at what can be done in Excel sometimes without even having to start coding.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther