Introduction
There are still a lot of corporations that rely on tools such as Excel to execute some core processes. Theses processes might have been created years ago and the creator of the file might not longer work for the same company anymore. As a results, some programmers find themselves in a situation where they need to spend time and energy to reverse engineer a file in order to understand the way it works. In this article, we will show you a free tool that can create an interactive representation of your Excel VBA structure.
How to use the tool
The tool was created with the Python programming language so you need to install Python on your machine in order to use it. After the installation, you can use the pip install command to install the library. You need to be on a Windows machine with Microsoft Office installed.
pip install DrawExcelAt this moment you are able to use the module from your Python file. You can import and use the module in your python script like this:
import DrawExcelDrawExcel.DrawExcel(r'C:\Users\bob\Desktop\MyProject',r'MinimumStockCalculator.xlsm')
The first argument is the folder where is located you Excel file to scan. Second argument is the name of the Excel file.
During the execution a folder will be created next to your Excel file. The name of this folder will be the same as the name of your excel file. The recent created folder will contain all the .SVG files that represent your file structure. After the execution is completed, a .SVG file will open in your favorite internet browser. See example below:
If the file does not open you can locate the folder next to you excel file and open the file _MAIN.gv.svg .
Each rectangle represents a VB component of your file. It can be an Excel object (Sheets, ThisWorkbook), it can also be a form or a module. Each of theses components can contain functions or sub, theses are the circle you see in the image above and below. The arrows represent how each function interact among each others. A function can have an interaction inside a VB component and outside the VB component.
If you see that what a VB component (rectangle) is too messy you can zoom into the zone. You just have to click on the hyper link that is on the name of the rectangle. Example if you click on Sheet3 you will get this.
This will get you a better view of what is going on inside this VB component. Use the back button of you web browser to go back to the main view.
Conclusion
I hope this helps anybody who is having hard time with a VBA file. I also hope this tool helps some developers to migrate an old VBA Excel into a more structured Python code. I created this tool during my free time and I wanted to give back to the community by making it available to everybody. I think a lot of corporations can benefit from migrating an Excel file into a python process. If you are using Python to execute your process, you can go way further than using Excel because there are a lot of data science libraries available in Python.
You can request for new functionalities, report an issue or event better you can contribute to this project on Github:
brochuJP/DrawExcel: This tool will draw the VBA structure of your MS Excel file. (github.com)
If you are a VBA programmer and you are interested of learning a second language, I encourage you to learn Python. The Win32com library in Python will help you to make the bridge between your VBA scripts and the Python language. When using the Win32com library from Python you are able to code in a language that is very similar to VBA so the learning curve is not to steep.
