History of VBA

Visual Basic For Applications arrived in 1995 for use in non critical office grade applications – yes, MS Word, Excel and the like.

It is a “subset” of a “proper” coding language , Visual Basic.

Think of it as one step up from macros in Word and Excel . There is a vast range of “objects” which the user manipulates in code type language – but it is slow by comparison to VB so really it still produces “macros” and is not mainstream IT.

Structure

You manipulate Objects (eg a range in excel, a paragraph in word) with statements / commands. We can change both properties (colours etc) and create actions / use methods (copy or paste contents).

In the case of Excel, functions (in VBA language as well as on the face of a spreadsheet) then exist to perform calculations and return values, stored in variables.

Thats it in a nutshell. Although there is a lot of detail to hand, the user requires only a few key statements (eg if.. then ..else) , objects (ranges), methods (eg file open, copy, paste) and properties (eg bold) in simple macros.

However it can go on to produce some gnarly routines and crunch big amounts of download data.

Use in accounting – Conclusion

There is no doubt VBA has its place and many accounts departments became XL havens long ago. Reliant on it is a risk. It was never intended to be a mission critical element.

Tips

Stay tuned to the blog for some curated code snippets.