How to Replace VBA with Python(Step-By-Step Tutorial)

today i'm going to show you how to get started with python for excel it doesn't matter if you've never used python before i'm going to take you through everything step by step the first thing we're going to cover is installing python and installing the ide then we're going to look at how to use open pixel to interact with excel files we'll look at excel wings and how to use it to interact with the vba object model and then we'll cover pandas the library for data analysis and finally we'll look at how to run python from excel events all the code examples for this video are available to download from the description below so let's go ahead and get started if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it so we're going to start by installing anaconda which you can find at anaconda.com so this contains an installation that has python the libraries that we're going to use and many other useful things so we simply go to the individual edition and click on download and then save the file now when the file is saved we double click and then just run through the installation so everything in the installation we're going to use the default so you can just keep pressing next i agree and then next next now in this screen it may pause here for a couple of minutes so don't panic if it does just let it go until it's finished so once that's installed we're going to install visual studio code so that's going to be our development environment so we go to visual studio you can just google it visual studio code and we click on download for windows or whatever platform that you're using and the same thing we're just going to save the file and then when the file is saved what we'll do is we'll double click on the file and then we will just go through all the defaults of the installation so just next next and keep going and just let it install so once we click finish it will automatically open and now at this point if we want we can make some selections you can see dark team is selected by default but we can select light team or anything we want and we can go through this if you if you like to set it up but the easiest thing is just to click on the tab at the top and just cancel we can always make the changes later when we're more familiar so we can use file new to open a file there's one more thing we have to do and then we're fully set up to run python we want to go to view and we want to type in default and then we want to click on select default profile so we'll be using this later and this basically means that we have the same terminal as using run command in windows so now we're set up and we're ready to run python so now we have python installed and visual studio code installed what can we do well we can actually go and get started writing python code all we've got to do is say file new and you can see here that it asks us to select a language well we don't need to select the language because when we save the file with a python extension it will automatically recognize it as python so let's just write one line of code print welcome to python and then we'll save it so we can do control s to save just like an andy windows application and then we're going to call it first dot py and then we'll click on save now you can see that the color has changed to run this code what we do is we go to run and we've got two options we can run it with debugging and run without debugging so in vba when we run the code it's always basically in debugging mode but when we run python we can choose to have it as debugging which means it will stop if there's breakpoints but if we run it without debugging it means it ignores breakpoints and will just run straight through so let's run it without debugging so you can see that the output that we get here is in the terminal now let me just clear this so let me let me run it one more time and you can see it has welcome to python when we run it it puts out what it's doing you can see that has the file names it has the folders and so on and then at the end it has welcome to python so it's not quite as clear as the immediate window in vba but it's the same result we're still getting welcome to python written out once we're set up here we're able to run the code and we're able to see the output so what we can do is we can write any python we want so you can get examples anywhere from the web and put them here and run them and try it out for yourself so let's do a new one let's say name equals and we'll say john smith and we want to print out the first name so what you can do in python is we can use the square brackets we can say the starting position and that's zero so all arrays and structures and everything start at zero in python and we say we want to take everything up to position four but not including it so again we run this code and you'll see john appeared so now we can run the code from visual studio but what if you wanted to run the code you know from somewhere else like a batch file or from windows scheduler but what we have here at the terminal is actually windows command so in other words if we do windows kr and we run command it opens up the window which we used to call the dos window because we can run dust commands from here and what we have in the terminal itself is the very same as the command window so this even though we're getting the output here this is actually the same and we can run commands from here we can use cls to clear everything and if we want to run the file what we can do is we can do python like this and we can do the file name when we hit enter you can see the result is john now if we change it let's just change john and we'll change that to jack just to show let's run it again you can see we run it there brings out the extra kind of bits of bits of details about the file but if we run it here you can see that it just writes out jack now the fact that we can run it like this means that we can use it in a batch file or we can use it in windows scheduler and that's how we can run python when you're deploying this you normally create the python file as an executable so one more thing to to kind of explain about the terminal so let's clear everything in the terminal is that we can actually run python from here so in other words if i type python like this i'm actually in python at the moment so i can actually do things like name equals and let's say jenny and then i can say a print name and you can see it printed out jenny so often on the internet when you're looking up examples of python you'll often see that they're done like this they're done in kind of a command line python but for clarity here i like to have the code within the visual studio file itself because it looks more like how we write vba now we can exit this by doing ctrl z and that will bring us back to the terminal okay so now let's look at how we can manipulate excel and how we do it is by using our open pixel library so to use we say import open pixel now this was included when we installed anaconda so it automatically installed this library which is very useful so one key thing about open pixel is that it's not actually talking to excel what it's actually doing is dealing directly with the file so let me explain how files work in excel if you don't know about this you may be surprised to find this out when we have a file in excel like say we've got marks xlsx let's make a copy of this file this is actually really a zip file that contains xml so if i change this to zip it's a zip file and if i double click on it you can see that it opens up and you can see the different contents that's available here and so all of these we've got worksheets we've got different stuff are all xml files and we can just open them like this and you can see that it has xml now you can see even here if you look you can see chemistry you can see literature and so on and these are the things that that's in the actual file so let me just explain that or let me just show you that when i open marks here you can see we've got the student marks and we've got the different subjects like history geography maths and so on and so if we flip back to the file here what you can see is that we've got all the details in our worksheet xml file so what's happening with openpixel is that it's just going to read this file so you don't actually even need excel installed so how we read it is as follows the first thing just like in vba is we want to get the book so we say book equals open pixel and then we're going to use the load workbook function so this is very simple it just it's basically like the open the workbooks open in vba and we just put in the file name so that's going to be in our sandbox folder and the file is going to be called marks and once we get the book of course we want to get the sheet so we can get the sheet like this we say sheet equals book and then we use the square brackets which you use a lot in python and we say class a so that will give us back our sheet so let's print what's in our sheet and we'll say let's print what's at cell a10 and let's save this file and then let's run it so we do ctrl f5 to run and you can see that what we got there was it said cell class a which is the sheet and the range is a 10. now if we want to see what's actually in the cell itself we do value and let's run our code again and you can see it printed out student 9 which is the value in cell a10 now imagine that we want we want to make a change to that worksheet so there's one thing you've got to keep in mind so let's get rid of this we can easily change like this we can just say sheet and let's say b15 and we want to say hello so just hello there and so if we do this in vba what happens is hello there gets written straight to the worksheet and the reason is because we're dealing with excel the application but when we're using open pixel as we've seen we're dealing with the file so when we make a change to the file we've got to actually save it when we're finished with our changes so we simply use book save and this takes the same parameter is just the file name as we've used above so now let's run let's save it first and then let's run our code and so it ran okay there's no errors or anything like this so what we want to do is we want to open up our book and see if the change did indeed occur so we go to marks and we open it up here and you can see that it added hello there to b12 or b15 so let's close this again another useful thing that we often do in vba or in when we're dealing with excel is we want to read through a range of items so this is how we do it here the first bit of code is is the same of course we get the book we get the sheet and then we use a for loop like this we say for rule in and it's going to be sheet and we use the iter rows which is sharp for iteration or read array of rows go through the rows and then what we do is we print out like this so we do print and then we do roll now what's really nice about this is that this this has parameters and the parameters are like mineral max roll and so on let's just do it as mineral and mineral equals once we're going to start at row one and max roll equals five so we want to start we're going to finish at five and the same thing with the columns then we're going to start that column three i'm going to finish at column let's say if i would say six and we want the values so let's say values only equals true now that should have a capital let's see so everything is okay we control s let me just clear our term a little bit here so we can see what's going to happen now we run hit ctrl f5 and you can see it printed out as we expected history geography match biology and the results for each of the reasons that python is so popular is because of the pandas library so the pandas library is a data analysis library that provides huge functionality for manipulating data so let's have a look at some examples of using panda so we can use it very easy by typing import pandas as a pd and now we've got pandas available to us so what we can do then is we normally read data into what's called a data frame and once it's in the data frame we can manipulate it so we do data frame equals and we'll do pd for pandas and we're going to read excel and we can do many different things we can read from csv files we can get the data from many places and all we've got to do then is just say the part of our file and we've seen this one before and we can use the defaults but what we want to do is we want to use open pixel to read so we're going to we saw that in the last section and that's what we're going to have as our engine so it means that's the way we're reading the file so this gives us a lot of flexibility using engine because it means we can read in many different ways so open py excel and this gives us access to the worksheet and then the worksheet gets automatically loaded to our data frame so let's have a quick look at the worksheet that we're going to be dealing with and you can see it here it's just a list of students and their results in different subjects and of course the class that they come from so let's start with something simple let's print out the names of the columns so we can say results equals df.columns and then we'll print the results so that we can see exactly what they are let me just clear this and then let's run this code and you can see that it printed out all the headers students class history geography and so on so let's do something even more interesting let's print out the first two rows and we can use the normal way that we use python to split our index and we do it like this we use i lock and then we basically say we want to start at the first column and we want to read until column two so this will give us the columns that position north and one or sorry the rows position node and one so let's run our code and you can see we got the first two rows student one and student two now imagine we want to get the first five rows and we just want to get history geography and match results so what we would do is we would say we've got we've got a first five rows and then we want to start at column position two which is history and we want to read to five but not including five so we want history geography maths now let's run this code and see what we get and you can see we got all the results for the students so you can see it's very easy to manipulate data with pandas it doesn't take a lot of code so we can do a lot with just one line of code or one simple function so let's look at another case imagine we just want to filter by all the students in class a so what we can do in this case is we can say data frame and what we want is we want to use class so class is our header and we want that to be we're going to get that as a string and we want to say match so it matches and it matches a so we're going to get back all the classes all the records where the class matches a so let's run our code and see what happens and you can see these are all the results from class a now there's many different ways we can filter we can use contains instead of match we can use the greater than sign and so on so for example let's do the contains for example we can say student and students and let's say that contains two so that should be not student one and student twelve and we run this code you'll see that it gave us back student two and student twelve because they have 2 in the text so let's look at one more powerful function that we can use with pandas and that is group by so we can split everything up into groups and in this case we're going to do it by class so we're going to split everything up by class so now we've grouped by class and what we can do then is do different things once we have them in groups so there's many options but what we're going to do is we're going to say get me group a and from group a i want to get the mean so i want to get the average of all the all the subjects from group a so let's run our code and you can see it gives us back all the subjects and the average for those subjects for class a so if we wanted to do it for class c for example just change it to c and then we would run the code again so this is a brief look at what the pandas library can do you can see it's very very powerful we can do a lot with data with just a few simple lines of code and this is why python one of the reasons anyway that python is so popular now we're going to look at the excel wings library so the excel wings library we imported like this import excel wings as xw so what this library does is allows us to interact directly with excel so this is how we basically use vba we interact with the excel object so the first thing we always want to do is we want to get a workbook so we can say workbook equals and we'll say xw.books.open and then we put in the name of the book it's very similar to vba as you can see and once we get to workbook we want to get the sheet so we can use the sheets object and we'll say class a which is the name of our sheet and then we want to get the range and the range is sheet.range and of course we use the normal that we use in vba so a1 to c let's say c2 so let's print out this range.value and we run the code and let's see what we get you can see that we get students class history student 1b and 38. so this is what we would have expected this is reading so let's try writing to the sheet so the difference with open pixel is that we're reading from the file itself so if we make a change to the file it only goes to the file when the file is saved but it's different here because we're dealing directly with excel so if we change a value in in a cell it will automatically update so it will update straight away so let's say a 16 and we'll say the value equals we'll say excel wings and let's say hello excel wins here now that should be hash to comment it out and let's hash this one out too because we don't need it and let's go to let's open our we'll open the one we were dealing with previously and that is our marks so let's open marks and you can see that we have marks here okay so if we run the code i'm just going to make this smaller so if we run our code you can see that it wrote out to our worksheet here so we didn't have to save the file or anything and this is because it's talking directly to excel so okay so this is pretty much the same as what you would see if you're dealing with vba so obviously for using python we want to have some advantages to using it over vba what we can do with our ranges is we can use pandas so let's add pandas here we do import pandas as we did before as pd and then what we can do is we can read a range so we can read the range let's say a1 to c13 and we use options and we use data frame so p d and we use data frame and then value and this is going to be our data frame so again once we have something in a data frame you know we can do anything that we that we want so let's do a let's close this file when we're finished with it and let's print out our data frame just to see what we have so you can see it printed out class history students there so a1 to c13 now once we have it in the data frame we can do many different things with it but one thing i really like in excel wings is that we have this thing called xw view and we can take our data frame and what we can do is just write it straight out to an excel worksheet so this is really useful it'll open the excel worksheet write it straight out to a table so that we can view it instantly and there you see the results are in our table so this is very useful for when we're testing code writing code to see exactly the results that we're going to get now as i said once it's in a data frame we can do all the stuff that we were doing in our previous examples so if we wanted for example we could get the for so we could get like the first couple of rows we could say df equals df and just give us the first two rows and then we run our code and you can see that it did and it opened up a new workbook with the results in it which is very useful functionality when we're using vba we normally run the code when some excel event happens so for example a button is clicked or a cell changes or a sheet is activated so we want to do the same with python in many cases and how we do it is that we handle the events in vba and then we call the python code when the event occurs now to do this what we need to do is install the excel wings add-in so we do the excel win add-in like this we go to our anaconda prompt so if you can if you don't know where to find it just go to the start menu and just search for it there it'll it'll show up and then you'll have excel wings add-in installed now mine is installed already so you'll probably get something a little different and you can see it said successfully install the excel wins add-in please restart excel now after you've done this when you open excel excel will be ready for you to to put the add-in in place so the add-in will be on your hard drive and what you've got to do is copy it so let's open the folders you'll have a folder where the packages are and that folder will be something like this wherever you've installed anaconda you'll have library site packages and excel wings add-in so you've got to copy this file to your add-in folder now if you want to find your startup folder you can do so in vba itself just go to the visual basic editor we just print out application and startup path and you can see this is our startup path and so we can just copy this and then we can use this in our explorer now you can see i've already put it in but if you don't have it in or you have the wrong one let's just go back here so imagine you don't have the right one you can just paste it here hit enter and then it shows up so you can you basically just copy from the other folder into here you can see i've already got it done myself and then when you restart excel what you'll see is as follows you see excel wings appear on the ribbon so it's automatically filled in these different paths and you don't have to worry about it what we want to do now is we want to run python code from vba so when some vba event occurs so we've got some simple python code here and what this is going to do is when it's called from vba so we're going to click a button in excel that's going to run vba and then this code will run and it will write call from vba into cell a1 so how do we do it well let's go ahead and look at vba so in the vba editor we create a sub i'm going to call it run i'm going to call it python code now the next thing we want is we want to have a reference to excel wings so make sure that you have the reference now i've got a checked on here if it's not checked on make sure that you do and once we do that then we have access to run python so we can say run python and what we put in here is the name of the file so the name of the file is python call from vba so we do import python call from vba and then we put in the name of the function which is main and that's all we've got to do and now we'll go to our worksheet and we'll insert a button so we go to the developer tab we select insert and we select the button so let's just have a big button here and we select run python code so let's click on our button and see what happens and you can see that the text called from vba appeared so we clicked on the button and then the button called this run python code sub which in turn then called the python so called the python call from vba file and within that it called the main function so that's how easy that it is for running python code from vba now in this case we did a very simple example but of course in other cases we might want to do something much more complicated with python or otherwise what would be the point of calling it now imagine we want to run vba code from python so how do we do it it's actually very straightforward so we've got a soap here display text and what this is going to do is it's going to display a message box with the text that we provide as a parameter so we're going to run this from python so just like before we use excel wings and we open the workbook and then we can get access to our macro like this we say display text and we say that's going to be assigned to the workbook macro so you see very easy and all we've got to do is put in our macro so we put in the module which is module 1 and display text which is the name so now that we have our macro we can just pass it the value like this i was would say i was called from python so let's save this and then let's run our code and when we switch to excel you can see that io's call from python appears as a message box now the pointing code is still paused we click ok and then when we switch back to python you see that it has just completed so this is how we call vba code from python i've covered a lot in this video so let's have a brief overview of the main points so we can set up python by installing anaconda and visual studio code we use the open pixel library if we want to interact directly with excel files so we don't need to have excel installed and this makes it quite quick to use pandas is a powerful library and this allows us to do many type of data analysis tasks excel wings package it allows us to interact with excel just like vba does and what we can also do is when excel events occur like worksheet change events we can run python from vba and one more thing we can also do is we can also run vba very easily from python all the code samples for this video are available in the description below so make sure to download them and try them out for yourself if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it

Channel: Excel Macro Mastery

Views: 34,076

Rating: 4.972826 out of 5

Keywords: ExcelPython

Id: 6qo3ly3-_I8

Length: 31min 19sec (1879 seconds)

Published: Thu Sep 02 2021