VBA is not a scripting language… Not at all!
After building a minimal FastAPI + SQLite CRUD application in Docker, I wanted to show how Excel can connect directly to it. With a few lines of VBA, we can turn Excel into a client that talks to the API. …
Docker Basics in Excel with VBA | Full CRUD Demo Read more »
After the article for introduction to Graphs in Python, I have decided to put the graph theory into practice and start looking for the shortest points between points in a city. Parts of the code are inspired from the book …
Shortest route between points in a city – with Python and OpenStreetMap Read more »
Ok, so some 10 years ago, I was having fun coding A* Search Algorithms in Excel in VitoshAcademy and this is what I had built back then: VBA – A* search algorithm with Excel – Really? VBA – A Search …
Pivot tables are one of the most powerful tools in Excel for summarizing and analyzing data. No doubt about it. However, sometimes you need to go beyond their built-in functionality. In this article, we will explore how to use VBA to …
The Rule of 114 is a quick way to estimate how long it will take to triple your money with compound interest. The idea is simple: divide 114 by the annual interest rate (in %), and you will get an …
Have you ever had a feeling you need to solve an inequality like this one ((x+6)^3)/(7-x)^5>0 and you did not know how to start? In the video at the bottom of the page I am showing the few steps needed, …
There is something interesting about linear regression. I have just noticed, that I have actually quite some articles on it some years ago, but today I wanted to make a YouTube video as well. Well, what is the difference this …
So, what exactly is standard deviation? In simple terms, it’s a measure of how spread out the numbers in a dataset are from the mean or average. If the numbers are close to the mean, the standard deviation is small. …
Talking about dot products in linear algebra can be really a subject of hours, but long story short this is probably the minimal knowledge you can live with: The dot product (inner product) is an operation on 2 vectors that …
Excel with VBA is really a powerful tool. There should be some 5 more years, until Python really becomes a standard for the Excel people and I am not sure that it would actually happen that quickly. Anyway, working with …
In the last weeks, I have noted that one of my answers in StackOverflow has received some upvotes. Which was somehow strange, as people keep on telling me that #VBA is going to die and it is a “funny scripting …
Working with Python and Excel is actually quite handy, especially if you are into it. In this article, you can see how to create Excel files, write data and formulas into them and read these. Pretty much simple CRUD methods. …
Simplifying Excel Tasks with Python: Data Handling and Testing Tutorial Read more »
Flask is actually really a powerful tool for making an API. In the video below, you would see how quickly I am building an API, that does four really simple functions. The functions are – saying “Hello”, multiplying a number by …
Python – Make a simple API with Flask and Postman Read more »
Some time ago, I wrote an article about defaultdict in Python here – vitoshacademy.com/python-defaultdict-object-or-how-to-avoid-the-key-error. Now, I have decided to make a video about it and increase the scope with standard dictionary functions and an advanced class, that checks whether the …
Python Dictionary – Advanced. With YouTube video. Read more »
As one of the acknoledged reviewers of the Manning book Python How-To, I have decided to make a YouTube video, based on a part of the book, mentioning Lambda Expressions. The video is here, and the code I have used …
Python – Lambda Expressions – With YouTube Video Read more »
Have you ever got your connectors in MS Word not in line? Like this one: The good and intended way to do it, was pushed by Microsoft some time ago – go buy yourself MS Visio. But, if you do …
Following the step-by-step original tutorial here, I have created a simple Azure video tutorial, explaining how to deploy a python flask web app with local git. The idea to have something on your own PC and then simply using this …
If you are one of the many VBA developers, then most probably you have been asked once or twice to download some file from a local eshare or site. Before downloading it, it really makes sense to check, whether this …
VBA – Check if resource or url exists in the internet Read more »
Inserting into string in Excel should be a trivial task, but it always makes sense to have something prepared in the boilerplate, before you start working. Ok, why is it important to have a nice function, if something like this …
The idea of the article is to provide a Python code that does the following: Takes a list or DataFrame; Splits it into quite a few smaller parts; Writes each one on a separate Excel worksheet; Pretty much like this: …
Python – Write a DataFrame or List to multiple tabs in Excel Read more »
Same article, but for Python is here – https://www.vitoshacademy.com/python-split-worksheet-to-worksheets-save-excel-worksheets-to-csv/ This article does 2 things: Splits one worksheet to multiple worksheets Then goes through the worksheets and saves them as *.CSV files I hope that is enough for you. Ok, so …
VBA – Split worksheet to worksheets, save Excel worksheets to csv Read more »
Option Compare in VBA is the thing, that will save you ugly stuff like this one: Sub Testing() Dim userInput As String userInput = InputBox(“What is decimal 4094 in hex?”) If UCase(userInput) = UCase(“ffe”) Then Debug.Print “You have correctly written …
VBA – Option Compare (Text || Binary || Database) Read more »
Getting N-th string between two substrings might sound a bad idea, until you do not need it. And with VBA, you better have something prepared. So, imagine the following XML (from this article here): <FootballInfo> <row> <ID>1</ID> <FirstName>Peter</FirstName> <LastName>The Keeper</LastName> …
VBA – Get Substring Between 2 Substrings – Locate value of Nth XML Read more »
Getting some tiny URL, that redirects you to somewhere else might be dangerous. There are a lot of people, that would never ever click on bit.ly/something or goo.gl/something, just because they do not know where the redirect is going to …
How to get redirect url using Excel VBA or Python Read more »
Ok, the title became too lengthy, but the idea is to try to duplicate the beauty of what this formula does: =INDEX(myTable[#All],MATCH(1, (myTable[[#All],[Profit]]=C9)* (myTable[[#All],[Currency]]=D9)* (myTable[[#All],[Value]]=B9),0),1) And this is the cell of the formula above: Anyway, the strange thing was that …
VBA – VLookup with Multiple Criteria in Excel without Excel Formula but with VBA Read more »
Reading and writing to an Excel ActiveX textbox with Python can be fun. If you really do not have anything better to do. Anyway, if this is your current task, you are on the correct place to see how it …
Python – Read and Write to Excel ActiveX Textbox Read more »
If you have ever dreamed that it will be possible to fill out an Excel cell with a picture, using Python, then you should probably reevaluate your dreams. Seriously, who dreams of something like that? Without further dreaming, the code …
Python – How to fill Excel cell with image, using python Read more »
Working with dictionaries in VBA is a bit more unpleasant, than working with dictionaries with almost any other language. Because of the VBA IDE, you only see the keys, but not the values. Thus, if you have experience with Visual …
VBA – Printing Dictionary of a Dictionary of a Dictionary… Read more »
Saving Excel files, when working with VBA is unfortunately a bit of pain the ass. You always need to make a new version every now and then, because it does not matter how seasoned VBA developer you are, still there …
Transforming Range to Array in VBA is actually a one liner – we take the range and we assign it to a variant: Dim myRange As Range Set myRange = Range(“a1″).CurrentRegion Dim myArray As Variant myArray = myRange The “strange” …
VBA – Ubound of Multidimensional Array or How to Get What We Need? Read more »