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 »
If you are just starting with Docker and want a practical project, here is a fun one: let’s build a tiny CRUD app in Python, run it inside a Docker container, and then connect to it from Excel with VBA. …
Docker + Python CRUD API + Excel VBA – All for beginners 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 …
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 …
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 »
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 »
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 »
Intersection of dictionary (or of any two lists) is a list with elements, that are present in both other lists. Thus, if we have the following 3 dictionaries in Python: dict_a = dict([(‘aa’, 4139), (‘bb’, 4127), (‘cc’, 4098)]) dict_b = …
Everyone, who has worked with Excel more than a year knows that the comments are actually quite useful – they allow us to put info data into a cell, without actually putting data in the cell. However, the standard way …
What is the difference between IIF() and IF() in #VBA? The idea of IIF() is that it evaluates both the True and the False part of the condition, before evaluating the condition itself. Thus, if the False part throws an …
Hashing strings in general is useful. It is one of the good things, that we get from granted by the various .Net libraries and we should not try to do on our own, as the HMACSHA1 is quite a powerful …
Converting Excel column number to letter and letter to column number is actually something that every VBA developer does at least once a day. The easiest way is probably to use the property .Column or .Address of the range object …
VBA – Convert Excel Column Number to Letter and Letter to Column Number Read more »
Ok, if you are visiting this article, then probably the reason is that you have received the following messagebox upon openning of Excel: And after looking all over your cells, with both VBA and “Find”, you have not found anything …
How to fix – This workbook contains one or more links that cannot be updated… Read more »
Nested dictionary means a dictionary within a dictionary. Some days ago last week I was trying to build one and it took me some solid 15 minutes to do it, thus I have decided that it is worth an article …
Check if folder is empty: Public Function FolderIsEmpty(myPath As String) As Boolean FolderIsEmpty = CBool(Dir(myPath & “*.*”) = “”) End Function Delete all files in a folder: Public Sub DeleteAllFiles(path As String) Kill path & “*.*” End Sub Create text …
VBA and source control? Do you know what is VBA? Are you sure we can source control the code, that resides in Excel? Oh, you mean that lengthy way of extracting the modules one by one? But why? Don’t you …
Using API calls with VBA and Python to write a database is actually a pretty decent architecture. Here, I would like to say that “It takes the best of the both worlds”, but the truth is that it takes the …
Using API calls with VBA and Python to Read and Write to a Database Read more »
Yesterday I received an email, refering to some 2 old articles in VitoshAcademy for nested loops without recursion: VBA – Nested loops with recursion (Permutations) VBA – Avoid nested loops with recursion (Part 2) As far as the person from …
Cells and ranges in Excel are quite interesting, as these will always give some points to think of. About an year, after writing the article about VBA – Cells and Ranges in Excel (A bit more than the standard story), …
Most probably you feel like you have a good clue already how to locate a value in a given Excel row. There are plenty of ways – Find() , looking through the cells one by one, looking through the Internet for …
More than 3 years ago, I wrote an article about floating point numbers in Excel and a tiny problem with those, concerning all floating point numbers. This article is actually a nice one and it is worth reading – What …
I have decided to make a 19 minute YouTube video, to explain the basics of VBA debugging, as there are quite a few tricks that not everyone knows. The agenda: Properties Window -> F4 Immediate Window -> Ctrl + G …
Copying worksheet in VBA is actually a trivial task – use the macro recorder see the code and use it further. Anyway, if you are coding professionally with VBA (yup, we still exist:), you somehow get stomach problems, whenever you …