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!
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 »
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 »
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 …
VBA – How to avoid naming variables with non-latin alphabet characters (special letters) Read more »
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 …
VBA – Color Only Half of the Border of an Excel Cell Read more »
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 …
VBA – Longest Palindromic Subsequence Algorithm with Excel – GIF Read more »
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 …
VBA – Longest Palindromic Substring Algorithm with Excel – GIF Read more »
Inserting an image to Excel with VBA is actually one line: ThisWorkbook.Worksheets(1).Pictures.Insert (“C:\Users\myPic.png”) The interesting part happens, when it should be correctly positioned. In order to position it the way we want, we need to give 3 parameters – the …
VBA – Insert an image/picture in Excel and position it correctly Read more »
The idea of the article is to make an updateable combobox, which takes all the keys of column A and writes them to the ComboBox. Then, upon selection of the corresponding values in the combobox, it provides the corresponding values …
VBA – Put a Collection inside a Scripting Dicitonary OR Make an Updateable ComboBox Read more »
The idea of the article is to show how to add, edit and remove specific entries of the ListBox in Excel through VBA: ListBox is a control, which is available through Developer>Insert>ActiveX>ListBox in Excel: Once you add it, it has …
VBA – ListBox in Excel – Adding, Editing and Removing Data Read more »
Reversing words in a sentence with VBA in Excel is quite easy, using the built-in StrReverse function. StrReverse takes a string and reverses it. Thus, “VitoshAcademy.com” becomes “moc.ymedacAhsotiV”: The idea of reversing the order of the words in a sentence …
Long time ago, an astronomer called Benford was taking a look at logarithm books, noticing that the earlier pages (starting with 1) are pretty much more worn out than the later ones. He thought about the result, and managed to …
VBA has a dictionary structure. Dictionary is an object, and it can be referenced either with early binding or with a late binding, referring to Microsoft Scripting Runtime: What is a dictionary? This is the Microsoft definition of the VBA …
Some months ago #VBA was finally considered the most dreaded language in the 2019 StackOverflow survey: One of the reasons for this, is probably that #VBA is quite too easy to enter, thus plenty of non-IT professionals feel fascinated about …
There are parts of #VBA, that can scare any VBA developer, who has not bumped into other languages. Although, the “abstract class” term is quite popular in C# and other languages, and is quite easy to be created, in VBA, …
VBA – Abstract classes. Classes, that cannot be instantiated. Read more »
Finding the biggest square of specific units in a given matrix of these units is a standard dynamic programming problem. The examples in Google for it are above 18 million, thus I would simply show how it is done with …
C# – Find the Biggest Square in a Matrix – What can Excel and VBA add to it? Read more »
Copying an object in VBA to a new object is somehow tough. If you use Set foo = bar then, both foo and bar would be pointing to the same address in the heap. And if you change the properties of one, …
VBA – How to copy a new object in VBA, without copying its reference Read more »
The environmental variables are a dynamic-named values that can affect the way running processes will behave on a computer. With other words, if you want to see them in your PC, simply open the command prompt and write the nice word …
Some time ago I wrote an article for Data scraping from Internet with Excel , which was scraping book information from amazon.com, based on a given word. Thus, for a keyword as “VBA”, this is the data, printed in the immediate …
VBA – Data scraping from Internet with Excel – Part 2 Read more »
Animation consists of some pictures. In general, there are about 24 pictures per second, in order to do the “magic”. This could be easily automated with Excel, using Application.Wait (Now + #12:00:01 AM#), if we agree to make it a bit …
Automation of Excel is actually pretty interesting task. VBA is indeed the best built-in tool for this and although there is a lot of “hate” towards it it really deserves to be taken into account seriously, at least for small daily …
VBA – Extracting text from string between two identical characters using VBA Read more »
Writing for RegEx in VBA is sometimes a good idea and sometimes a bad idea. Anyway, if you need to write one, you better be careful. These are the RegEx articles in VBA in VitoshAcademy so far: https://www.vitoshacademy.com/vba-regex-in-excel-part-2/ https://www.vitoshacademy.com/vba-regex-in-excel/ So, …
VBA is extremely underestimated programming language. I have always been thinking that there are almost no real #VBA developers, because the “real” developers do not want to do some meaningless “macro” stuff and the people who work with #VBA are …
Gmail has the option to revert a message for about 10 seconds after you have sent it: Well, Outlook with VBA can do similar things, if you are willing to program it a bit with VBA. Let’s say that you …
VBA – How to check messages for specific words before sending them with Outlook Read more »
Adding comments with VBA is somehow quite a trivial task, if you already have the text of the comment and you are allowed to delete the comment if it is present. In general, the only difficult part is the check, …
Sometimes, you may need to scrape some structured data from the internet with Excel. Honestly, Excel. Honestly, Excel should be your last weapon of choice there, as far as it is really not a web-scraping software. Still, it is quite …