VBA is not a scripting language… Not at all!
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 →
Everyone knows the feeling that they need to use the =Index(Match(),Match()) formula in Excel and something tiny was not enough to reach their goal. Some time ago I knew that something is missing I was trying to “filter” with the …
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 …
Convert number to name – [3 -> March] Converting month number to name in VBA is actually coming out of the box with the MonthName() function, which is built-in the stanard VBA library: Sub MonthNameExample() Dim i As Long For …
VBA – Convert Month Name to Number and Vice Versa Read more →
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 excel worksheets around is actually a piece of cake for a VBA developer (or so they/we think). Mainly because you can record the actions and then “learn” from them, editing a bit the code and etc. Well, there is …
VBA – Copying Excel worksheet to a new workbook without leaving the old Excel reference Read more →
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 …
Ok. This article is for all the people, who have written code like this one: if direction == “left” then if column > 0 then: new_row = current_row new_col = current_col – 1 When they were solving a problem like …
C#, Python, Java and all the other “fancy” languages have a built-in method, which writes variables into a string. VBA does not have one. Depending on why do we need it, and if we can guarantee that no injection would …
VBA – String.Format or how to put variables in a string Read more →
Errors in Excel are different – from “Division by Zero” (#Div/0) and #Name to #REF! and #Num. There are lots of ways to summarize these into some kind of report and in the current article I will show the VBA …
VBA – Make a list of the formula errors in an Excel Read more →
Dreaming of having a boilerplate for Excel VBA project? Or you have other dreams, which are more normal? Well, I cannot help in the latter case, but if you want a boilerplate for VBA – here it is!
There are quite a few methods for writing data from a text file to Excel. The main are the following: Through a Query Table Through opening the text file and Reading and writing line by line Reading the text file, …
Although there are plenty of other tools for extracting data from a website (take a look at Beautiful Soup), VBA is somehow good, because … well, because it is somehow challenging to do it every time. Yesterday, I answered a …
VBA – Extracting financial data from a website in table format Read more →
Some 4 years ago I wrote an article for calculating magic square with python. What the article did was actually checking whether the square is magic, and not calculating a new one. This one is going to show how these …
Excel – How to build magic square with VBA, following Python Mod Mathematics Read more →
Dropdown lists in Excel usually are built from a range or from a variable list. Dropdown list from Excel Range Thus, building a dynamic dropdown list may include building a dynamic range, with undeclared end. Thus, let’s imagine that the …
Dynamic dropdown list in Excel VBA with variables Read more →
Writing formulas with Excel with Python is a technique, that needs some practice, actually. Although XlsWriter has some good documentaion, not a lot is covered on the point, that concerns creating a file with a formula in it. First and …
Write Formulas in Excel through Python XlsWriter Read more →
The birthday paradox is an interesting problem, mainly because of its somehow “unexpected” results. The problem usually is stated as the following: What is the minimum number of people in a room, in order to have a probability higher than …
The Excel Workbook object is a part of the Workbooks collection. The events are the code, which is “activated” or “triggered”, once a specific event happens. These events are different and could be unlimited, but there are a few built-in Workbook …
VBA – How to implement workbook events in Excel – GIF Read more →