How to get started with Visual Basic for Applications?

Question

Hello Everybody,

I'm a little intimidated by the wealth of knowledge displayed on the front page of this website, but it gives me hope that I'm talking to the right people. I had a pretty rotten experience with one of our programmers today, and I'm hoping that you might be able to offer some advice.

I'm an office admin (primarily clerical), and one of our macros in Microsoft Excel broke today (I apologize if that description makes me sound like a "dumb user"). Our manager called our IT department and they sent up 2 programmers. The first guy was nice and asked if I'd be interested in watching him fix the problem -- but the 2nd guy spoke up and said "She's just clerical, she'll never get it."

Yeah, it hurt -- but to make a long story short, I asked my husband (who is a "perl" programmer) what I could do to show these guys what I'm made of, and he recommended something called "VBA" because I work with Microsoft Office a lot. He wasn't quite sure if "VBA" was right, so he recommended I get involved with the community at this website (he says he finds a lot of answers to questions here).

I've researched "VBA" on google, but there are literally THOUSANDS of results. I just want to be able to record and edit macros for Microsoft Office products, and possibly get good enough to show that programmer I'm not just a "stupid female admin". Can any of you pro's please give me your advice on the following items?

1. Is "VBA" actually what I'm looking for to create Macros and automation?
2. If not "VBA", what is a more suitable starting point?
3. A good book or website with lessons?
4. Anything else you think would be helpful? Do they offer college classes for things like this?

Thank you very much for taking the time to help me!

April

Answer 1

VBA stands for Visual Basic for Applications. I recommend reading the Wikipedia article on it, then checking out some video tutorials like this one. I also like the "For Dummies" series when you want to learn about something you've never touched before. When you have specific questions, you can post them on this site.

Answer 2

VBA (Visual Basic for Application) is what you are looking at, incase you are interested in macros...

In my opinion it is far more easier to understand if you have good experience in Ms-Office, though for mastering it, you might need some programming skills.

The easy way to learn is start with a problem. Take a problem in hand and try to record the macro for automating your task... Then you can take a look at what got recorded in the Visual Basic Editor of the Ms-Office Suite...

Try to read and understand each line of code that got generated as a response to your actions... At first everything will be greek and latin... But if you continue to do it, you will easily understand it in no time...

Then you can search for materials and all those search results will be of great meaning to you.

Have a nice time..

Answer 3

1. Yes. VBA stands for Visual Basic For Applications.
2. VBA is what you want, if you're interested in learning how to automate office applications.
3. I recommend a book by John Walkenbach. In particular, Power Programming with VBA.
4. Don't know about college classes, but you probably don't need one: I'm pretty sure Power Programming assumes zero programming experience. (But it's been a long time since I read it.)

Keep in mind, it may take some time before you grasp enough of the concepts to impress your coworkers. Good luck. And remember, there are always jerks like that. Don't let them get to ya. :)

Answer 4

In answer to your questions...

1. VBA Is the language which is used to drive office automation. As Jordan said, it stands for Visual Basic (for) Applications
2. VBA is a suitable starting point if you just want to impress the office Dev. If you actually want to learn how to program (as your husband does) then start out learning another language called C++ but be warned... its hectic stuff. I suggest you google the phrase 'STarting out with VBA' or 'VBA for beginners' and the like. You will find some helpful tutorials
3. Instead of trying to find a book and learn it all, set yourself a task, such as 'Create a macro that will find all cells in a spreadsheet that have my name in them and replace them with 'I WIN'. You will rapidly come up with more specific questions as you try that
4. Classes are offered, most classes in Excel and other office courses will offer an advanced section for macro programming
5. Ask specific questions. Instead of asking 'how do i write this program' ask 'how do i find a particular cell in a spreadsheet'. You get much better responses that way

Answer 5

Note that there are many styles of VBA, as code from Excel/VBA is going to look different from code in Access/VBA. The reason is the usage is different (cells vs databases). Look at vb-helper for books, links and code.

Answer 6

Jordan's answer is a fine one, but your problem runs deeper than whether or not you know VBA. Basically, you have rude coworkers and, if you hope to impress them, becoming a neophyte in an area in which they are highly skilled is unlikely to help.

The better way (IMO) to deal with this sort of thing is to tell the rude one "leave my office [/cubicle/whatever], now. No discussion", then have a word with his boss about how he interacts with his coworkers.

If his boss won't give you a satisfactory answer, take it up with HIS boss. Etc., until you get to the CEO, if that's what it takes.

If nobody up your chain of command things that the behaviour you describe from #2 programmer is a problem, the you have to consider whether or not the company you work for is worthy of your particular skill set.

And if you don't think you bring any particularly valuable skills to the company, then you probably shouldn't worry too much about whether or not the others respect you, although (a) you still shouldn't have to take the sort of abuse you describe and (b) your idea to extend your skills is admirable.

...But, if you're not a "natural born programmer", don't try to force your round peg into a square hole.

If it were me, I'd focus on being the kick-ass-est office admin anyone had ever seen. Oh, another tack: if #2's boss doesn't give you a satisfactory response, ask YOUR boss to take it up with him. Explain that you don't think that people should be subjected to that sort of behaviour in civilized society. Again, if he's not open to your complaint, you have to decide if you're going to work for that kind of company.

Good luck!

Leave us comments letting us know how it comes out.

Answer 7

Actually I started out programming by recording macrosin Excel and reviewing the code created by the recorder. Figure out a simple task and then record a macro. Recording macros in Excel 2007 can be found under the view tab under the Macros button on the far right. One of my first tasks I figured out was to filter several rows of data with autofilter then print the filtered section. After printing I remove the filter and select cell A1.

Before recording a macro figure out each click of the mouse in order. Everytime you click or type the macro records it. So reduce the chatter code recorded, by rehearsing the macro. You might even write out, by hand, a play script like in broadway play would have . Don't worry about how long it takes to record. The recorder has no concept of time. To best understand the VBA, record the macro several times. Throw in an extra click and see what it does. Compare the code in each macro. To get to the code view the macro with the same button mentioned above. select the macro and edit it. When you feel adventerous modify the code in the editor. ALWAYS record and play around on a copy a document not the real thing. This way you can delete the file if it gets to messed up. FYI - the stop button for the macro is often in the bottom left corner of the Excel window.

Happy learning, --J Man

Answer 8

Lots of useful resources mentioned in above articles. Yeah! VBA is the thing in which you can actually code logic of macros. I would just like to add an article of mine about MS Word/Excel macros. Some of the basic and tricky tutorials are mentioned there.

Answer 9

I thought I would share my experience in how I learned VBA macros within Excel (mainly) and Access.

I needed to automate a lot of tasks in Excel and I knew all about recording macros. You can record macros from the Tools|Macros menu in Excel 2003 (I'm not sure what it is in 2007/2010).

Then I decided I needed to alter them slightly, so I thought I'd have a look at the recorded code. Pressing Alt-F11 brings you into the VBA Integrated Development Environment (IDE). You can then look at the macros you have recorded and see what they do.

I would then edit them slightly and see what happened. If I needed to do somthing and I wasn't sure how, I would search the help or google and find excellent answers all over the web.

I now work as a VBA developer and I often still have things that I need to find out. Having a strong background in computers and Office applications (especially Excel) will help. Having a husband that does Perl will mean you can ask him about general programming things like syntax and variables and programming concepts that might need some extra explaining. I had been programming before on and off so these concepts weren't new to me.

Good luck and remember to ask heaps of questions on Stack Overflow and you'll be a Pro in no time.

Answer 10

Many useful resources here for VBA, one of the most useful points though is that when you turn on the Macro Recorder in Excel the Macros created are VBA code. Excel is huge, trying to learn all the nuances of VBA will take a fair time, OTOH recording a macro takes seconds and you'll get a quick idea of how the language works.

Answer 11

you better get hold copy of MS Office and start using MS Excel. Ms Excel as well as MS Word allows you manage spreadsheets and documents using VBA.

Open MS Excel and do your desired task while recording a Macro. Macros are great place to start learning VBA. In the background all these Macros are stored in VBA format. You can record as many as Macros on your various day to day tasks. Thus you will be more efficient in getting the task done thru MS Excel as well as you will get to learn more about macros and VBA. You will soon learn to tinker this macros for better results.

Answer 12

If you get reasonably proficient with VBA, you won't have to call IT for these matters. That may be against your company's policy, but I'll leave that ethical dilemma to you. If I wanted to learn VBA, I would study these three books in order

VBA for Excel for Dummies (Walkenbach)
Power Programming for Excel VBA (Walkenbach)
Professional Excel Development (Bullen, Bovey, et al)

Concurrent with that study, you need to start solving real-world problems. Go to social.msdn.microsoft.com and start reading the VBA forums. Answer whatever questions you can. The ones you can't, wait for someone else to answer it then reproduce the problem and solution so you know it. Do the same with stackoverflow questions by following the vba tags. If any question seems too complicated, just skip and go to the next one.

Read Excel VBA blogs http://www.dailydoseofexcel.com/dicks-blogroll/ Start with http://datapigtechnologies.com/blog/ and http://blog.contextures.com/ for sure. They're not just VBA, but they're very well written.

Here's what will happen: In 3-6 months you will have discovered a whole new world. You will have written simple macros for yourself and your coworkers that eliminates some of the drudgery from your day. You'll start looking for wasteful process you can eliminate with simple code. In 6-12 months you will have become reasonably proficient. You won't need IT to fix minor problems for you. You will be the go-to Excel person in your office (learning VBA will make you better at Excel). You will have helped thousands of people here and on the msdn forums all while helping yourself.

After 2 years you'll probably start being recognized by MS and the community as an expert. You'll get sick of answering the same questions over and over on the forums so you'll start a blog and direct people there. You'll have arguments with your husband about whether VBA is a "real" language, but he'll be secretly proud of you for what you've accomplished. After 5 years you'll have co-authored an Excel book, quit your job to be an Excel consultant, and learned C#. YMMV of course.

Answer 13

Excellent question!

VBA stands for Visual Basic for Applications and is the macro / automation tools that comes with Office (the macro that you talk about in your post will be written in VBA).

VBA sounds like a perfect match:

• VBA is targeted at non-techies and so is designed to be forgiving and easy to pick up.
• Google contains a wealth of information on the subject, all targeted people in your situation (people without a technical background who simply want to produce an Office macro that helps them)
• Using Office every day will give you an opportunity to use and practice what you learn.
• VBA has a fantastic macro recording function which is incredibly useful (especially when you are trying to learn VBA)

Like I said, Google has a wealth of information and introductory articles on the subject (e.g. Getting Started Using VBA, to pick a good looking example). I recommend reading through one of those to get a quick overview and then just have a go and see what you can get it to do:

• Use the macro recorder! If you can't work out how to do something then just record a macro of you doing it and see if you can figure out how to do it from the code it generates.
• Give yourself problems to solve.
• Ask questions here! :-)

Also, many places will offer courses on VBA which I'd definitely recommend - guides on Google will be excellent for showing you the syntax and how to do do certain actions (like how to select a certain cell, or how to do loops), however they won't be as good at teaching the problem solving aspects of programming - for this you really need to learn from a person which is where a course will be very helpful.

As a final note, I would advise against trying to understand macros that other people have written (for example the macro you mention in your question) - understanding code is far more difficult that writing it, besides if those programmers coding skills are anything like their social skills its probably not code you want to be learning from anyway...

Good luck!