Tag: VBA

VBA – Printing Dictionary of a Dictionary of a Dictionary…

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

Tagged with: , , ,

VBA – Automatically Save Excel File with VBA

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

Tagged with: , , , , ,

VBA – Ubound of Multidimensional Array or How to Get What We Need?

Transforming Range to Array in VBA is actually a one liner – we take the range and we assign it to a variant:

The “strange” part is that the array is converted to 2-dimensional array after this: and working

Tagged with: , , , , , ,

VBA and Python – Intersection of dictionaries

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:

Then, an intersection of their keys will contain only

Tagged with: , , , , , ,

VBA – Make Excel Comments Beautiful

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

Tagged with: , , , ,

VBA – IIF() vs IF()

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

Tagged with: , , , ,

VBA – Hashing in Excel with HMACSHA1

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

Tagged with: , , , ,

VBA – Convert Excel Column Number to Letter and Letter to Column Number

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 that

Tagged with: , , , ,

How to fix – This workbook contains one or more links that cannot be updated…

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

Tagged with: , , , ,

VBA – Nested Dictionary

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

Tagged with: , , ,

VBA – Folders and Files Functions

Check if folder is empty:

Delete all files in a folder:

Create text file in a given path with text:

All together:

Tagged with: , , ,

VBA – Source Control with Git – Video

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

Tagged with: , ,

Using API calls with VBA and Python to Read and Write to a Database

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

Tagged with: , , , , , , , ,

VBA – Compute All Combinations of a Given Set

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

Tagged with: , , , , ,

VBA – Cells and Ranges in Excel – Video

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),

Tagged with: , , , , ,

VBA – How to locate a value in an Excel row?

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

Tagged with: , , ,

How to declare as decimal in VBA

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

Tagged with: , ,

VBA Debugging – Video

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

Tagged with: , , , ,

VBA – Copy Worksheet

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

Tagged with: , , , , ,

VBA – String.Format or how to put variables in a string

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

Tagged with: , , , ,

VBA – Make a list of the formula errors in an Excel

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

Tagged with: , , , , , ,

VBA Boilerplate

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!

Tagged with: , ,

VBA – Extracting financial data from a website in table format

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

Tagged with: , , , , , ,

Excel – How to build magic square with VBA, following Python Mod Mathematics

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

Tagged with: , , ,

Dynamic dropdown list in Excel VBA with variables

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

Tagged with: , , , , ,

VBA – How to implement workbook events in Excel – GIF

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

Tagged with: , , ,

VBA – How to avoid naming variables with non-latin alphabet characters (special letters)

Writing code in the local language is actually taught by about 100% of the German VBA books, which I have read. Which is “kind of ok”, if the VBA code only works on German computers. However, if this is not

Tagged with: , ,

VBA – Color Only Half of the Border of an Excel Cell

Coloring the border of an Excel cell is a trivial task. The LineColor feature in Excel is easy to use out of the box: However, whenever half of the cell needs to be colored, the trick is a bit more tough

Tagged with: , , , , ,

VBA – Longest Palindromic Subsequence Algorithm with Excel – GIF

After writing about the longest palindromic substring, now it is time to see the longest palindromic subsequence. What is the difference? Pretty much, if we take the word abracadabra: the longest substring is ada in abracadabra the longest subsequence is

Tagged with: , , , , ,

VBA – Longest Palindromic Substring Algorithm with Excel – GIF

The longest palindromic substring algorithm exists since 1975. It is different (and easier) than the longest palindromic subsequence. The idea of the substring is to return “anana”, if “bananazorro” is given. This is achieved with a 2 dimensional boolean array matrix and

Tagged with: , , , , , ,