Will Python Kill VBA in 2021?

in the last few years python has exploded as a language for data analysis it's been used more and more with excel now you may be wondering is it still worth learning vba or should you abandon it completely and switch to python today i'm going to do an in-depth comparison between python and vba and get to the bottom of these questions if you liked 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 basic the language was released in 1964. now in 1991 visual basic was released and visual basic was based on the basic language but with the visual component and it was a big hit when it came out because it allows people to very quickly create user interfaces for windows now in 1993 visual basic was added to office as vba and then in 1998 visual basic 6 was released and this was the last version of visual basic because after this microsoft moves the visual to visualbasic.net which is essentially a different language the last release of vba was in 2007. now there was a version released version 7 but version 7 was really just changes for 32-bit versus 64-bit now python was released in 1990 and in its early years was just kind of used by a small community in 2005 the numpy package was released so this is a library that allows scientific calculation and this helped make python more popular in 2008 then pandas was a library that was released that allows users to do a lot of manipulation with data analysis and from this and in 2012 python really started to become popular so should we be using vba or should we be using python in 2021 well keep watching and we'll find out now what is vba used for so vba is used primarily for excel secondary it will be used for access then for all the other applications which it's available is not really used that much at all whereas python has much more general applications so python is used for web development data analysis and automation scripts so these are kind of the main uses of python can be used for many other different applications but this is what it's primarily used for so in terms of platform vba works with windows now vba also works with the mac but it's kind of limited in that you have no user forms number one and the second thing is that any libraries that work on windows won't work on the mac with python we can write the same python code and use the same libraries on windows or on the mac and the code will compile and run on both of these platforms so now let's write some python code so we can see exactly how it works now first of all we're going to do this in vba and then we're going to do it in python we're going to read this data to an array and then we're going to print out a row from the array to the immediate window so to do this in vba what we want to do first is to clear our array and then we want to assign all the values from the range to our array after this we need to declare another array and this is to store our row now when we create this array we have we also have to give it a size so it's going to be one row and the size is going to be the number of columns that's in our array so once we have the size what we do after that is we basically read through all the columns in a given row and we copy all the data so we say row 1 comma and then we say i and that equals mark so it's 2 because it's row 2 and i again because we're reading from whatever is the current column so this will read all the data now to write out today that we do something similar we're again just going to read through the columns and print the value in the array to the immediate window so we run the code you'll see that it printed out the second row there's quite a lot of lines of code to do this but we're going to see how we can do this now much easier in python so the first thing we do is import the pandas library so the pandas library allows us to do data analysis and for example here what we're doing is we're doing read excel and this allows us to easily read an excel worksheet into what we call a data frame and the data frame allows us to easily manipulate so all we have to do here is specify the workbook and the sheet name now we store it in a data frame as i said so we just used the variable df we don't need to declare it or anything like that and then what we can do is use the loc function or the loc property of the data frame and what this allows us to do is access rows or columns so we can easily specify the row that we want we say one so the rows start at zero so this is actually row two and we're the second one here means that the row is going to stop at one so basically just give us one row now we print out the rule and when we run this code what you'll see is that we got back the one row that we expected and you can see that it gave us back row two so now let's just make a small change here imagine that we want to start a row three and we want to get the next four rows so what we can do is we can specify two and we can specify five and this tells us to start that to stop at five and you can see that it provided all the rules back that we were looking for so now we're going to do something more interesting we're going to filter all this data and we're going to filter it by students who got greater than 60 in their geography results i'm going to take the results of this and we're going to write them out to another workbook so let's go ahead and write the python code to do this so we can take our existing code and we can do this with just a few changes we're going to use loc again but this time we're going to use it to filter so we say df for the data frame and we're going to use the name geography so geography is the name of our field i'm going to say greater than 60. now we're going to change row to results here and this will return us the data frame to the results now what we do with results then is we're basically going to do to excel so this is similar in a way to read excel the difference is obviously it's writing it out to excel but all we need to do is to supply the workbook and the worksheet now there's other parameters but we're just going to keep it simple this time now when we run the code what you can see is that it wrote out the results as we were expecting to a new workbook and you can see it only took four lines of code now to do this in vba is much more complicated we can do things like use advanced filter or we can use ado but both of those have their limitations so you can see python is very powerful for manipulating data we've only really touched on the basics of what it can do so far we've been running the python code from visual studio but when we do a real world application what we want to do is run it from excel so for example if we click on the filter button here we'll want to run some python code so how do we do it well what we do is we use a library called excel wings and this also has an add-in that you can see here for excel and when we click on the filter button what happens is we call a run python function so let's have a look at that you can see here run python what we do is we have the name of the python file here and we have the name of the procedure now once you've installed excel wings we can basically just use it anytime by clicking on the reference here so let's see the python code that we're going to use so you can see the python code here what we want to do is we want to read the data from the marks that we just saw we want to filter this data by students that are in class b and then we want to write out the results to the class b worksheet let's run the code and see what happens so let's filter this code now let's go to class b and you can see that it has filtered as we expected so it ran the python code and that's how we run python from excel and we can also use this for different things like worksheet events or anything else where some kind of event occurs in excel using a modern development environment like visual studio code has lots of advantages over the old visual basic editor so let's have a look at three useful things we can do with visual studio code so the first one here if you check out we have a for loop and we have an if statement now if we move the cursor to the left margin you can see the arrows and if we click on those you can see that it hides the code in both the if statement and the for loop this is very useful when we're writing code or when we're debugging code and it would be a very useful addition if it was in the visual basic editor now a second thing that is very useful is when we're writing code if we have an error python then tells us straight away you can see here that it underlines all the problems in our code now if we put the cursor on one of the problems it will actually tell us what the issue is and the issue is that it's expected in and when we fix it you can see it got rid of the underlines so we go to print d and just put our cursor over this just to see what the problem is and you can see print d is not defined so we get rid of the d and you can see it removes the error now the final thing i'm going to show you is using lint so lint helps us follow coding guidelines and the way it does it is very similar to finding errors if there's a problem when we save you can see there's an underscore here and if we put a cursor over it tells us that we should have a white space after a comma so this gives us advice on coding guidelines and is very useful when it comes to programming these are three simple but very useful things that we get when we use a modern development environment if you want to install a vb application on another computer it's just a matter of giving the user the workbook and as long as they've got excel installed it should work fine however python is a little trickier we need to have python installed on the machine where our application runs so we can achieve this in one of three ways we can install python on the machine that we're going to install our application we can put it on a share drive so the machine can access it here or we can create a frozen executable now if you run frozen python keep in mind that it doesn't work with the mac so these are a few different options we have and there are paid solutions that can do the job a bit better now one thing to keep in mind is if we're installing software in large organizations they may have restrictions on what software you can install it may not be possible to install python and this can obviously be a problem have a look at python and vba the question is will python kill off vba so one way we can look at it is like this over the last 10 years there's been a lot of searching for python xl but it's really exploded in the last five years however over the last year it has tapered off somewhat so what are the advantages of python that would make it take over from vba well the first thing is the code is much easier to write in python the second thing is that it's great for data analysis and there are some cases where you actually don't need to use excel at all that you could completely replace your excel data analysis with python now python uses modern ides and we had a look at just a few advantages of using a modern development environment now the same code you write will work on the windows or the mac so you can develop your code on either one and there isn't any issues you've got a wide selection of libraries to use we can use these for data analysis but there's also many other things they can do and python once you learn it can be used for many types of applications that have nothing to do with excel now the disadvantages of python over vba are that it requires a lot of setting up so you've got to set it up on every computer where you want to use it you've got to install python in some form on every computer that you want to use and there can be problems if there isn't permissions now in the beginning there's a steep learning curve although once you get up and running with python i think in the long term it's a bit easier than vba now the thing is vba is sufficient in many situations and going to the trouble of learning python and setting up the python environment may not be necessary and vba is simpler for small applications so what is the future for python and vba going forward well vba is going to be supported for a long time into the future now python isn't part of microsoft suite of software and this is why there's a lot of overhead when we want to use it if in the future microsoft added python to their suite of software then i think vba would definitely decline and python would definitely take over but it doesn't look likely that this is going to happen anytime soon let me know if you plan to use python in the comments below if you liked 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: 175,165

Rating: 4.9097996 out of 5

Keywords:

Id: Lh6FLhgPxbc

Length: 12min 39sec (759 seconds)

Published: Mon Jun 28 2021