In a recent LinkedIn post by George Mount, a compelling question was raised: “How do you compare VBA with Office Scripts?” George shared a four-page PDF, sparked some significant thoughts regarding the comparability of these two programming languages. Can they truly be compared? Here’s my perspective.
The Origins of VBA and Office Scripts
To start, let’s look at where these two languages come from. VBA, or Visual Basic for Applications, originates from a language called BASIC (Beginners All-purpose Symbolic Instruction Code). Created in the 1960s, BASIC was designed for those with logical thinking skills but without access to the hardcore programming languages of the time, like COBOL or FORTRAN. BASIC’s design principle was simplicity, using English keywords that made programming accessible to a broader audience.
In contrast, Office Scripts is rooted in JavaScript, one of the most popular programming languages today, especially for those aiming to become professional programmers. JavaScript was never designed with beginners in mind; instead, it’s a language that appeals to a more technically inclined audience, with its syntax and structure tailored towards professionals.
User Friendliness and Accessibility
Given their origins, it’s clear that VBA and Office Scripts were built with different users in mind. VBA, by design, is user-friendly, using straightforward English terms that lower the barrier to entry for beginners. JavaScript, on the other hand, lacks this inherent simplicity, making it less accessible to those without a programming background. Therefore, expecting a VBA user to easily transition to Office Scripts (JavaScript) is unrealistic. They appeal to different kinds of people—one to beginners and the other to professional programmers.
The “Scariness” Factor
When Office Scripts were first introduced, there was a wave of enthusiasm, with many claiming that it would replace VBA. However, if VBA intimidated you, JavaScript might be even more daunting. The hype around Office Scripts often came from a place of ignorance, with many failing to realize the complexities involved in learning and effectively using JavaScript. For those without a programming mindset, both VBA and Office Scripts might seem challenging, but VBA still offers a more accessible entry point.
The Environments They Operate In
Another crucial difference lies in the environments where these languages operate. VBA is built on the COM (Component Object Model) Object Library, designed to work primarily with the desktop version of Excel. It runs locally on your machine, whether it’s a PC or Mac.
In contrast, Office Scripts are designed to work with Excel on the Web, running on Microsoft’s servers and integrating with cloud-based systems like OneDrive. This difference in environment means that the two are not directly comparable; they serve different purposes based on where and how they’re intended to operate.
The Cloud vs. Desktop Paradigm
The paradigms of cloud-based and desktop-based solutions are fundamentally different. Excel on the Web, where Office Scripts operate, is geared towards collaborative, shared spreadsheets, a very different scenario from the client-server architecture that desktop Excel (and thus VBA) is built for (even though, confusingly for the uninitiated, both have ‘cloud capability’ as a common term!). This difference in architecture further emphasizes that VBA and Office Scripts are tools for different tasks and environments, making a direct comparison less meaningful.
The Reach of the Languages
Office Scripts operates within a Microsoft tenant environment, confined to a specific corporate ecosystem. This provides a high level of security within the corporate environment but limits its reach. VBA, with its desktop-based architecture, has global reach through a client-server model, capable of interacting with spreadsheets anywhere in the world by synergically leveraging cloud-based databases. The scope and reach of these two languages are thus different, further distinguishing them.
Integration with Other Technologies
Office Scripts is tightly integrated with modern Microsoft technologies like Power Automate, designed to work within the Microsoft ecosystem. VBA, being older, was not designed with these newer technologies in mind. This difference in integration capabilities is notable but reflects the different eras and purposes for which these languages were developed.
The Hype and Reality of Social Media
Social media plays a significant role in shaping perceptions. Office Scripts, being new, generates buzz and excitement, whereas VBA, having been around for decades, does not. This hype often overshadows the practical realities of these tools. Many who promote Office Scripts may not fully understand or utilize its capabilities, creating a disconnect between perception and reality.
To fill this gap. we look at an illustrative example in PART 2 – The Donald Trump Birthday Challenge 2020, which was built to contrast desktop Excel with VBA, with Excel on the Web with Office Scripts, and Google Sheets with Google Action Scripts (another JavaScript derivative) – all working in the same process but with different programming languages to make them work seamlessly with the same cloud-based data.
Object-Oriented Programming and Manipulating Objects
Both VBA and Office Scripts function by manipulating objects within Excel. However, the types of objects they manipulate differ based on the environments they operate in. VBA interacts with desktop Excel objects, while Office Scripts work with objects in Excel on the Web. Though they share some terminology, the underlying architecture and the nature of these objects differ significantly.
Conclusion: Can They Be Compared?
In summary, VBA and Office Scripts may seem comparable on the surface, but they serve fundamentally different purposes and users. VBA is tailored for desktop environments and is accessible to beginners, while Office Scripts is designed for the cloud and appeals to more advanced users, serious programmers who typically are not interested in programming Excel (!). Comparing the two is like comparing a car with a boat—each is designed for a different context and purpose.
The debate is not about which is better but about understanding the contexts in which each is useful. VBA remains a powerful tool for desktop-based solutions, while Office Scripts is better suited for cloud-based, collaborative environments that are grounded on ‘sharing common spreadsheets’ (as distinct from a client-server architecture, that share the same data among many spreadsheets). Understanding these differences is crucial for making informed decisions about which tool to use for a given task.
Ultimately, while it’s tempting to position Office Scripts as a replacement for VBA, the reality is that they are not interchangeable. Each has its strengths and limitations, and their use should be guided by the specific needs of the task at hand. In short – the architecture of the specific solution.
Add comment