VBA (Visual Basic for Applications) is a part of Microsoft Office Applications to support automation also known as Macro which is a native scripting language. VBA developed to build automated solutions for Microsoft Office Applications (Excel, Word, PowerPoint, Outlook, Access). It is easy to learn because of simple English syntax and recording feature but there are certain shortcomings cause VBA got degraded in demand. Let’s see couple of points which will help us understand it batter:
- Code crack: VBA code can be hacked easily
- Complex to distribute: a common solution has to be shared with original code
- Confidentiality: solutions which is created as part of database connectivity where user has to supply database or server credentials can be revealed
- Malware: macros inside Office application by default considered as malware by most of the Antivirus applications hence not recommended to build enterprise solutions
VBA offers various features which make it a robust programming language when it comes to automate any Microsoft Application. Macro recording feature which enables new learners to accomplish basic day to day tasks automation. Integrated development environment, simple coding, easy to bring external references, side by side code compilation with output verification etc.
After compilation the limitations and features of VBA, we decided to explore VSTO technology which is an advanced level of VBA and come up with followings:
- Professional programming environment
- Capable to build enterprise solutions
- Security risk can be minimized upto ZERO level using .pfx and Sign to confirm trust
- Solution built-up happens within secure DLL where credentials cannot be hacked which ensures ZERO code expose
- Flexible to adopt any other technology within solution easily
- Easy to make distributed solutions
- Advanced UI design with the help of WPF (Windows Presentation Foundation)
- Development environment which keeps code base and application separate
- Complete OOPs (Object oriented Programming) support
- Professional project management using TFS(Team Foundation Server), bitBucket etc.
When it comes to implement VSTO there are some areas which I personally found and would like to mark as limitations. In DOT NET terms the VSTO builds COM objects hence any application reference (Excel, Word, PowerPoint) turn a COM object which does not expose underlying properties or methods in debug mode. The code execution output cannot be switched unlike VBA where one line of code execution can be verified side by side to make sure thing are moving as expected.
Finally we have built a Scratch knowledge portal (www.vbaoverall.com) where you can learn VSTO from scratch to advance. It will help individual or programmer to convert or migration legacy VBA code to advanced VSTO technology.
Followings are couple of basic VSTO learning links with examples you may like to explore: