Learning VBA

[dubz229]

Hi guys, I noticed there were a few threads already started but I didn't want to impose, plus my question is slightly different so I started a new one.

I'm a competent Excel and Access user, among others in the suite. My goal is to be able to take full-advantage of the capabilities that these two products offer. However, I find programming difficult. Would VBA be a good first language to learn? Is it object oriented? Are there any key concepts I should focus on to build a strong foundation in it?

Thanks,

J

 

[SydneyGeek]

Would VBA be a good first language to learn?

If you're working with Office applications, yes. It ships with every Office application (unless you have the Basic version).

Is it object oriented?

Yes, but not in the "classic" sense that C++ is object oriented. Every Office application has its own collections of objects (Workbooks, Worksheets, Ranges, Charts, Pivot Tables in Excel, form example) and you interact with those objects. But you don't need to build your own objects to get started, although many people do once they have a bit more experience.

Are there any key concepts I should focus on to build a strong foundation in it?

That's not so easy to provide a simple answer. Maybe...
Learn the syntax. You can get the basics pretty quickly.
Learn the Object Model. This shows you how to refer to the various objects. You can get this by recording code; by using the Object Browser (in the code, place your cursor in a keyword and press F2); and looking at the Help (F1 in the code).
Come back here often. Search, try examples, ask questions.

For any of this to work for you, it needs to be a REAL project. Pick something you need to do, and use it as your learning project. It will take a few months to get fluent but it pays off.

Denis

 

[dubz229]

Hi Denis,

Real projects are a great suggestion and is actually how I learned Access and SQL. It will definitely help keep me focussed and within scope. I'm still in school so I'm going to try and apply it to a Crystal Ball assignment in order to test a model, or at least a portion of it.

Thanks for the quick and helpful/detailed reply! I really appreciate it!

All the best!

J

 

[Johnny C]

Bear in mind that there is no macro recorder in Access (nor powerpoint any more)

What will bear you in best stead is to find a generic programming course that teaches you how to design a program, so you know what you're going to do and how before you even touch a keyboard.

My degree was in Computer Science back in the 1980's; we had 2 years about programming design and numerous 24-36 programming marathons near to deadlines which really hammered into us how to do it properly. Since then I've been progamming in assorted languages, VBA for the last 12. Much as I hated slaving over a hot keyboard through the night finding where I'd made an error, I thank goodness that I've got the discipline to design and plan properly hard-wired into me now.

 

[dubz229]

Hey Johnny,

Thanks for the advice. Since I don't have the time at the moment, or the money, is it safe to assume that practicing writing pseudo-code simultaneously learning fundamentals of VBA would accomplish that in the mean time?

I appreciate your advice. I'm excited to learn but it's best to approach it properly. :D

 

[dubz229]

Couldn't find the edit button, last line - wasn't meant to sound rude:
I appreciate your advice. I'm excited to learn and I want to find the best approach.

Thanks

 

[Johnny C]

Writing your code is far and away the best way to learn. Not pseudocode, proper code; do the pseudocode first, but then implement it into VBA. That's where you will really learn, because you'll make all the mistakes and you will learn from them.

How I got into VBA; I used to be a programmer, but in languages like Cobol, Fortran, Mumps. I got bored of programming, I worked on some fascinating projects (nuclear power plant, RAF graphics modelling for Tornado aircraft). But then I went to work in a bank, where it was just dull. So I became an accountant (which is a bit perverse) but from a business aspect it's more interesting. Anyway didn't program anything fora few years. Started using Excel, and realised one day I could record macros. I'm essentially lazy when it comes to repetative tasks. As I had some big finance reports on plain paper where it was hard to see the detail, I wanted the report to print out in 'pyjama paper' style; in the old 'pre laserjet' days, computer reports came out on wide paper with alternate lines colured light green (hence the name as it was stripy). Made it easy to read though, and I wanted to do that with the Excel reports. So I recorded a macro to shade a few alternate lines, saw how it worked, and with the Excel help function and a bit of guessing I wrote the macro to do it. The internet didn't exist outside universities in those days, and Excel 4.0 was fairly new so there weren't any Macro books, so you just had to rely on XL Help, guessing and asking colleagues. From there I started to write other macros to make my life easier, and add buttons to the toolbar to use them. Simple things, like 'paste special/formula'. But the simple things became more complex, and here I am.

After a couple of years I got the John Walkenbach book which introduced me to userforms, then I discovered MrExcel.com, and now I'm essentially a contractor specialising in writing VBA (Excel, Access) solutions for Finance departments. My current project is about just shy of 2,000 lines of code.

I've never been on an Excel course (though I've delivered a few), but my core programming skills have stood me in much better stead.

As I said at the top, I've worked with a number of computer languages. You realise after the first few that, really they're mostly the same thing, it's only the way they do the same things that differs.

One thing if you're going to write pseudocode, I would recommend looking up 'Nassi Schneiderman' charts;
"http://www.sombers.com/Structured Design Nassi Schneiderman.doc"
is a good overview.
Its a tool for designing pseudocode, and once you're familiar with it, it makes it very hard to design bad code.

If you're going to get into modular or structured design (where procedures call other procedures) then it's far and away the best design tool I've come across, and it makes life very easy.

 

[dubz229]

Yeah, I'm about to start a project that uses crystal ball, so I plan on supplementing it with VBA.

I think I would have given up if I didn't have the internet. Regarding the finance projects and contracting, what are typical projects people request using vba? What is the extent of vba's application and capabilities? Can you, essentially, design a system (gui/forms, relatively small database etc) in excel, for example?

Sorry for my late reply.

Thanks for taking the time to share your knowledge and advice with me.

Regards,

Jay

 

[Johnny C]

Impossible to say what a 'typical' contract would be. Sometimes I pick up small applications already written - usually by people with limited VBA skills - and redevelop them. More often than not though, I get 'business analyst' contracts where there are reports they need writing, and it's up to me to recommend the most appropriate solution and implement it.

Two recent things that spring to mind.
1 - a large report for fixed-term deposit forecasts in a bank. The solution in place was a fag-packet model (20 rows of primitive calculations). I built a model which worked with a few pivot tables, but as usual when they saw it they said 'Fine, can it do this as well' and it rapidly became much larger to the point where the model became so unstable because of the number of pivot tables and data. So I recommended that it be put into Access using VBA (because the eprson who would inherit it couldn't do Access). So I built a small Access Db and used Excel VBA to load data copied into a workbook into Access, build the queries and run an Access VBA macro to get the data back out, so Excel could manipulate it and churn it out into a powerpoint presentation. It worked very well.

2 - I was brought in to do amongst other things, look after a small Access Db that loaded data from Excel reports. It turned out the data in the Excel reports was crap, hence the Db was a load of crap. So I offered to write a small VBA app that collated the data from 2000 spreadsheets to go into the Access Db. That's currently just shy oof 2,000 lines of VBA. The Access Db has been canned and they are building a bespoke SQL Server Db, so I'm now rewriting the VBA to feed that.

That's the sort of stuff that seems to be out there in my field (financial services). There's enough to pay the bills - and a lot more than I'd get in a permanent job. Suits me! You don't get the office politics, don't worry about all the corporate BS that goes on, don't bother with team meetings, come in at 9, go at 5, no appraisals or any of that stuff. Just come in, do the work, go home and as soon as I get out the door at night I've forgotten I was there, so it's more or less stress-free apart from getting a new contract at the end of each contract. Which is a relatively small amount of stress, as so far I've not had a gap between contracts.

In fact I've worked more days during this recession than I did in my perm job - with a 60% hike in pay. Having fewer holidays doesn't bother me (you only get paid for the days you work), having less stress at work means you don't need as many holidays.

 

[stefankemp]

There's enough to pay the bills - and a lot more than I'd get in a permanent job. Suits me! You don't get the office politics, don't worry about all the corporate BS that goes on, don't bother with team meetings, come in at 9, go at 5, no appraisals or any of that stuff. Just come in, do the work, go home and as soon as I get out the door at night I've forgotten I was there, so it's more or less stress-free apart from getting a new contract at the end of each contract. Which is a relatively small amount of stress, as so far I've not had a gap between contracts.

In fact I've worked more days during this recession than I did in my perm job - with a 60% hike in pay. Having fewer holidays doesn't bother me (you only get paid for the days you work), having less stress at work means you don't need as many holidays.

Hi Johnny, very interesting thread. Re the above, I am in a perm job at the moment but would love to move to the contracting environment you describe above. Any advice on how to do that? How do you find your next contract, through agencies?

Cheers.