Tag: VBA

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

VBA – Insert an image/picture in Excel and position it correctly

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 image

Tagged with: , , , , ,

VBA – Put a Collection inside a Scripting Dicitonary OR Make an Updateable ComboBox

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

Tagged with: , , , , ,

VBA – ListBox in Excel – Adding, Editing and Removing Data

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

Tagged with: , , , , , , , ,

VBA – Reverse words in a sentence

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

Tagged with: , , , ,

VBA – Benford Analysis (First-Digit Law)

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

Tagged with: , ,

VBA – Dictionary

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

Tagged with: , , , , ,

Top VBA-Excel errors

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

Tagged with: , , , ,

VBA – Abstract classes. Classes, that cannot be instantiated.

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,

Tagged with: , , , , ,

C# – Find the Biggest Square in a Matrix – What can Excel and VBA add to it?

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

Tagged with: , ,

VBA – How to copy a new object in VBA, without copying its reference

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, you would automatically

Tagged with: , ,

VBA – Environmental Variables

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

Tagged with: ,
Top