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

 

[Johnny C]

Networking is the key route. any ex-colleagues, good agents (there are more bad than good, it's the individual agent rather than the company that makes the difference).

Agencies tend to like contractors, they make more money from them. For a permanent member of staff agencies charge about 30%, but there's a clawback if the perm leaves, if they stay more than 2 years the agency loses out against a contractor. For contractors they charge about 20% year in year out. It's also quicker to get a contractor in - 1 or 2 short interviews, perm jobs can need upto 7 but rarely less than 3 (I've had 6 for a job which I turned down in the end when they asked me in for a 7th)

However, ex-colleagues are also a good source of work. If you know any who've become contractors, get in touch with them. My current job I got because a chap I used to work with who's a contractor heard I was looking and there was a job going here. As he knew me (and it turned out I'd been interviewed by the boss before for a different job) I had a 25min interview and was offered the job by the time I got back to work.

Companies like direct 'referrals'; it's like getting a reference before you apply, a manager says 'I'm looking for someone, do you know anyone?' and the person you know says 'yes, I worked with someone wgo'd be ideal' - boss gets you in to make sure they like you and if so, bob's your uncle. It also means they save out on the agency fee )

You'll also find once you're on the contractor 'circuit' you meet lots of other contractors, and when you go to new jobs you'll find you meet someone you've worked with before. So your network grows more quickly than in a permanent job.


The most important thing if you are going contracting is to decide how you'll get paid. You need to make that decision beforehand, as option #3 (the most lucrative) needs about 3 months to set up.
1 Agencies offer PAYE, you pay tax and NI as if you were permanent.
2 There are 'umbrella companies' - who let you charge all sorts of 'expenses' to save tax, and pay you a minimal amount in wages and the rest as dividends to save tax, but they charge an admin fee. You need to choose carefully, and be aware that 'some' offer salary calculators but will bombard you mercialessly with spam and ring you at home at all hours. Ask contractors you know for decent companies.
3 Go 'Ltd company' so you manage your own money - decide how much to pay yourself, what to take as dividends, what you charge as expenses. There are costs associated with that - professional indemnity insurance for e.g., and potentially accountants costs (though as a chartered accountant myself I do my own books). The big advantage is that you can plan your tax. For example, this year I've taken dividends only up to the higher tax limit, no salary, I pay no tax on the dividends, just corporation tax at 21% on the company income less esxpenses. So, my total tax raet is about 18%. I register for VAT too, it's dead simple, and use the 'Flat Rate Scheme' whereby you charge VAT at 20% and only pay the VATman about 15.5% - so you make an extra 4.5% on VAT.

If you do decide #3, you have to plan in advance. Set up a company 3 months beforehand (half an hour tops online), set up a bank account (half an hour or less to fill in an online form, but to get it set up takes about 4-6 weeks - amazing in this day and age but it does), register for VAT asap (one phone call). Then 'opt-in' to the Flat rate Scheme (2 page form). it takes about 3 months to sort everything out, so by doing it beforehand you're ready to invoice and pay yourself as soon as you start work.

It's worth getting a book about tax planning, things like company years ends are important. Set everything up right and it's a doddle. A VAT return takes about 3 mins once every 3 months - log onto the VAT site, enter 2 numbers and hit 'Submit'. Annual Corporation tax and accounts aren't that difficult or timeconsuming. There are simple spreadsheets to work things out if you need, but if you've got a decent tax planning book it will explain in words of one syllable how to do it.

If you're unsure about the legal/tax side, it might be worth getting an accountant for the first year and sussing things out.

 

[stefankemp]

I've sent you a pm. Thanks again.

 

[dubz229]

That sounds ridiculously complex. I don't know if I'd ever have the patience for that. I found database design and development in Access, with basic VBA code (can't remember it anymore), dried my patience quick. The only thing that kept me going was the fact that I was continuously learning something new and aiming to improve the systems efficiency primarily through functionality, as the design was already optimized.

For now though, I'm aiming to supplement Excel (eg. models, formulas, macros and intuitive forms), and access 2003 db's. I didn't know that you could do so well off of it. That is good news! Hopefully, I will enjoy it.

Have you found a lot of demand for decision models and simulation using vba? For example, a company calls you to either revise, test, or simply model decisions (eg. forecasting, optimization etc).

Thanks for sharing all that info with us regarding networking etc. We really appreciate it!

All the best.

Regards,

Jay

 

[Johnny C]

There isn't a huge amount of demand for forecasting models. Which is a shame because my 'specialisation' is behavioural forecasting models.

The problem is tht most companies aren't clever enough to understand that an accurate forecast is worth it's weight in gold. For most managers, it's just a target they have to achieve, so they make it simple and easy to beat. Most managers also don't have much experience of what Excel can do in the right hands, they just don't realise.

And in larger companies where they do, they are more likely to use Cognos Planning or Hyperion, a proper forecasting tool. And to be honest I agree; an Excel forecasting model is probably going to need constant revision. Properly developed Cognos models don't, they are so flexible and quick to develop that you can build the potential for change in from the start.

I worked for an American credit card company which had hard targets; 20% growth in balances and revenue each year. To achieve that in a mature/saturated market like the UK you've got to box clever, hence I had the opportunity to set up some complex Excel models that used behavioural analysis, and they worked. But most British companies don't have the balls or drive to use growth targets like that.

Maybe they will, as the recession continues companies that work 'smart' will be the ones to survive. But the management need the vision to see that that is how to succeed, and precious few senior managers do.

Many years back, I implemented the first Windows version of Adaytum Planning (what is now Cognos Planning) in the UK. A visionary FD asked me to do a Finance Systems strategy, and I knew from then start that a best-of-class forecasting system was at the heart of it, and at the time Adaytum was that system. It still took me 2 years to get the cost agreed by the board.

 

[dubz229]

Hi Johnny,

Sorry for my late reply. I have been really busy with school.

I appreciate your honesty. You've had quite the experience! I've heard of Cognos a few times yet never knew what it was. Maybe this is something I should look into learning.

Also, my decision modeling professor showed me a research project he was part of that resulted in a DSS prototype built in excel. Are DSS or even dashboard systems for executives something that are popular in excel? Seems like this would be similar to decision modeling and forecasting unless companies get "smart" any time soon.

Again, thanks for taking the time to answer my question! It helps a lot and is appreciated very much.
J

 

[Johnny C]

It depends on the culture whether execs like dashboards or DSS systems. Software companies love them, they're easy to produce and look flashy. It depends on the use as to whether they're any use or not.

Some execs, in operational environments, do need dashboard type data. For example a COO in a manufacturing company needs to know stock levels, output/waste rates, things that need jumping on straightaway.

However the real decisions in companies that the exec need to make are the long-term ones. You need to look at metrics over a period of time, historically and forecast based on experience, hidden trends in data, market expectations. It's way too complex to present in a dashboard, as the key information the exec needs to glean is not the 'what' or 'how much' but the 'why'; what's happening in the customer base, in the market, that shows how things will go in the future. You can't display that in a few fancy charts now matter how pretty they are. The exec has to make decisions now that will affect how things will be in a years time, 5 years time. A good exec will want to be ahead of the game, to see the hidden trends that no-one else sees so they can be the first to take advantage.

Those trends change all the time, DSS are invaluable for that, to pull the data but a human still needs to interpret things; to see where the facts don't quite fit the picture any more which suggests something has changed, and the DSS will be used to mine the data for that.

Excel is useful in analyzing the data because it's so quick to prototype something. A smart business analyst will build spreadsheets that are flexible enough to look at data in many different ways quickly, using form controls (combo/list boxes for e.g.). You don't want to re-invent the wheel each time.

There's a reason that Excel is still the number #1 tool for analyzing data, the tool that is on just about everyone's desk in the business world; it's good. Not just a bit good, it's **** good. it has it's faults but I can't think of any other single tool that would bring businesses to their knees if for some reason it stopped working one day. It's absolutely intergral to the workings of most companies of any size. I don't think many people realise that; it's a major part of what lots of people do; for example accountants, I'd say most 'non-processing' accountants spend 95% of their time on Excel. Download data from the acccounting system, upload some maybe, but the rest of the time it's all going on in Excel. Access is becoming more involved but Excel is still the primo numero uno of analysis.

 

[dubz229]

Hi Johnny,

Sorry for my late reply. I just finished exams yesterday and when I got home, I turned off my alarm and slept hah.

You bring up some great points once again. Especially the fact that businesses, sometimes unknowingly, have a huge dependency on Excel.

Johnny, I really appreciate the time you have taken to discuss and share your experience with me from VBA to DSS to employment. It has helped a lot and offered me great insight.

All the best to you.

Best Regards,

Jay

 

[Johnny C]

Aah, exams, I remember those! My accountancy ones were a slog, once I'd done them each stage (there were 4 stages) I never knew what to do with myself for a week or so as every spare hour had been spent revising for the month before.

I still keep my hand in with exams, but of the 'adult educational' type, languages; exams aren't nearly so fraught as they're a lot easier plus your career doesn't depend on them.