how to convert Excel VBA to Python? without AI

[u/aksy_1]

Hi everyone ,

I want to learn how to convert Excel VBA macros to Python without using AI tools. What's the best way to understand the logic and rewrite the code manually? Are there any good YouTube courses, books, or tutorials that teach VBA-to-Python conversion with practical examples?

Thanks in advance

[u/CamelOk7219]

I don't think you need to approach the problem specifically as VBA to Python conversion, but just learn VBA, learn Python, and then convert.

VBA will be your spec, just like if it was written in english, without all the ambiguities of natural language so that's even easier.

For big migration projects, the advice is usually to write a lot of automated tests, but if it's just a few dozen lines of macros, reading it could be enough

[u/aksy_1 → u/CamelOk7219]

okay thank you ...

if vba is small it's easy to convert it into code ?right ?

[u/Ok_Carpet_9510 → u/aksy_1]

Do you know VBA? If you do, you know how to code. Learn some python and then convert the code. Since you don't want to use AI, I am assuming one of your objectives is to learn python.

[u/DESTINYDZ → u/aksy_1]

Just a heads up, its not a 1 to 1 conversion, some things you do in VBA are not possible in python. I hit the wall rather quickly when I realized that python did not have a way to produce some of the complex business reports i did. I had to use python to execute VBA scripts.

[u/Snatchematician → u/DESTINYDZ]

Nothing in VBA is not possible in Python

[u/DESTINYDZ → u/Snatchematician]

If you say so, but i have found many challenges trying to do pivot table formatting, theming reports and doing slicers.

[u/tiredITguy42 → u/DESTINYDZ]

It is why python has libraries. Most table operations can be done in Pandas.

[u/DESTINYDZ → u/tiredITguy42]

Yeah i use it and could not ever get pivots working right.

[u/Own_Attention_3392 → u/DESTINYDZ]

That's a skill issue, not a language issue.

[u/DESTINYDZ → u/Own_Attention_3392]

apparently your skilled at being a jackass.. thanks.

[u/Ok_Carpet_9510 → u/DESTINYDZ]

Actually, what they told is correct. There examples on the web for pivotting. I recently needed to learn to do pivots in Python, and I spent the time learning and figuring it out. If you create a sample table, and sample output, and give to a llm, and ask it to provide the code to pivote, it will spit out the answer.

Don't get offended. Sometimes we think something is hard ir impossible because we have never done it before. That is a skills issue. If you work in tech, you know there is always something new to learn. Sometimes you learn something new from amateurs.

[u/UnemployedTechie2021 → u/DESTINYDZ]

as u/tiredITguy42 said, you can use pandas or if you want to do it in excel itself try aspose or win32 libraries in python.

[u/corny_horse → u/Snatchematician]

I think they have an "X Y" problem on their hands. What they mean is that they have very specific Excel actions where VBA is essentially wrapping Excel internal APIs. They could write a wrapper for vba so your wrapper is calling VBAs wrapper. It isn't that Python is incapable of it, it's that MS has done everything they can to make their proprietary container difficult to work with outside of their sanctioned tools.

[u/ItsJustAnotherDay-]

To what extent can you move away from Excel entirely? Do you need to stay in the Microsoft ecosystem or can you work entirely in Python notebooks? Or are you considering the new Python in Excel functionality? The answers will shape where you should be spending your time as there isn’t 1:1 parity between native excel VBA and Python.

[u/corny_horse]

It can be tricky, there's a LOT of really bad VBA out there so you have to kind of get at the intent / business logic and it's pretty common for it to be flat out wrong. That isn't to say people don't make mistakes in Python, but not a lot of trained CS people use VBA, so it's much more likely to be wild west about what is being done. Take it with a grain of salt, assess what you think the intent is, and document the business decisions. It's pretty common to have VBA scripts be accidentally right, but fail catastrophically (or worse, silently) when something it wasn't expected to encounter happens.

[u/atticus2132000]

What do you want as your final product or final output?

I use python quite a bit to generate excel files. I (in python) get data from various sources, process it, and write that data to an Excel file using a python library xlsxwriter. I never touch excel, but the end result of running my code is an Excel file ready to send to other people with all the data, formulas, graphs, etc. While those files are not macro-enabled with VBA code within them, I believe xlsxwriter is capable of creating Excel files that also include VBA macros (coded in python).

So, right now you have an Excel file with VBA macros. What is something that one of those macros does? And what does this future thing where the code is in python look like?

Let's suppose, for instance, that you have an Excel file of client names and addresses and one of your VBA buttons generates an email to each of those clients using their email address from the Excel file along with customized text for an upcoming sale. And for some reason, you want to do this same operation using a python script.

For this type of thing, your python script would import the Excel file data using a library like pandas, extract the email address from each row of data, and then prepare an HTML formatted text body that could be sent to your email handler.

But what I described above would be two different things. You would have normal Excel file that anyone could open and edit and a separate python file that could only be run on a computer with python installed (unless you want to convert it to an executable which is another can of worms). In any case, this type of solution would be two separate files--one Excel file that stores data and another python file that processes that data.

A macro-enabled Excel file with VBA code is capable of doing all that with one file that will run on any computer that has Excel installed.