VS Code + Excel VBA: Write Better Macros Faster
Introduction
When writing VBA code, many users find the Visual Basic Editor built into Microsoft Excel to be somewhat outdated. Modern text editors like VS Code offer far more features, including extensions that integrate AI tools to enhance coding efficiency. This blog post will explore how to connect your Excel spreadsheet directly with VS Code, allowing you to leverage these modern coding capabilities.
Connecting Excel with VS Code
To illustrate the connection, consider a simple button in an Excel workbook that runs a VBA macro and displays a message box. When I update my macro in VS Code and save the changes, clicking the button in Excel will reflect the updates instantly. This integration enables me to write all my VBA code in VS Code while utilizing its advanced features like syntax highlighting, line numbers, and various extensions.
Using Codeium for Enhanced Coding
In this setup, I have installed an extension called Codeium, which serves as a free alternative to GitHub Copilot. With Codeium, I can simply write a comment describing what I want to accomplish, and it will generate the corresponding code. For example, if I want to copy the value from cell A1 to cell B1, I write a comment, and Codeium creates the code for me.
Testing the Generated Code
To test the functionality, I can enter some text in cell A1 and click the button. The text should then be copied to cell B1, demonstrating how effectively Codeium can assist in writing VBA code.
Advanced Chat Features
One of the exciting features of Codeium and similar tools is a separate chat window that has access to my current module. I can write prompts directly in this window. For instance, I could ask it to list all the sheet names in a message box. After pressing enter, it generates the code, which I can copy and paste into my module.
Setting Up the Connection
Now, let’s discuss how to set up this connection, which is quite straightforward. First, I stop the current connection in the terminal by pressing Control + C. To establish the connection, I head over to the xlwings GitHub release page. xlwings is primarily a Python package for automating Excel, but you can download a standalone command line tool without needing Python.
Downloading and Installing xlwings
Once I download the xlwings executable file, I copy it to my Windows directory, enabling it to run from anywhere without specifying the full path. Alternatively, I could place it on my desktop but would need to provide the full path when executing commands.
Executing Commands in VS Code
Next, I navigate to the folder containing my macro-enabled workbook, open VS Code in this directory, and access the terminal. I type the command xlwings vba edit. This command can be executed in either the built-in VS Code terminal or the Windows terminal, as long as it’s run in the same directory as my workbook.
Establishing the Connection
After executing the command, the terminal prompts for confirmation to proceed. Once confirmed, an active connection is established between Excel and VS Code. All my VBA modules in the current directory become accessible, allowing me to write VBA code directly in VS Code.
Conclusion
Setting up this integration is quick and easy, providing a modern coding environment for writing VBA code. This method not only enhances productivity through better tools but also makes coding more enjoyable. A special thanks goes to Felix, the creator of xlwings, for making this possible. I hope you found this guide helpful, and I’m eager to hear if you’ll try it out or if you have any questions.
Thanks for reading!
