Application programs, including word processors, spreadsheets, presentation software and database frontends, are end-user oriented software. As such, they are not meant to be programming platforms. What is the rationale of supporting programming in the environment of an application program?
Programming (also known as scripting) makes it possible to automate tasks and provide an end-user friendly interface. As a result, an application program becomes a solution to the end-user, rather than just a tool.
Here is an example. Jane is the supervisor of a college department. The department uses a database to maintain information regarding instructors, classes, rooms, students and etc. Mario is a skilled programmer who has written reports in access regarding enrollment data, instructor sick days, evaluation results and etc. Everything that Jane and department faculty needs is implemented.
However, there is a small problem. Not everyone is an Microsoft Access expert, and certainly not everyone should have full access to the database. To tackle these challenges, Mario uses VBA to create a front-end. Although the application program is still Microsoft Access, VBA makes it possible to create an illusion of custom application. Furthermore, Mario can also include the logic to control access to the forms and reports.
When it is all done, launching the associated Access file starts up a custom user interface that requires a log in for access control. Once logged in, the application is entirely form driven. There is no need to understand Access, at all. This would not have been possible without VBA.
Besides create custom applications, VBA also extends application programs. For example, although formulae in Excel is a power feature, there are certain logic that cannot be expressed by cell formulae. VBA complements formulae so that a spreadsheet can do more. Similarly, SQL (structured query language) is very powerful for database operations. Nonetheless, there is a limit to what SQL can do. As a result, VBA complements SQL in Microsoft Access to perform operations that cannot be expressed by SQL.