Category: VBA \ Excel

VBA is not a scripting language… Not at all!

Writing data from text file to Excel cells

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,

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

Write Formulas in Excel through Python XlsWriter

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

Tagged with: , , , ,

Python – The birthday paradox algorithm

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

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 – Cells and Ranges in Excel (A bit more than the standard story)

Excel’s ranges and cells are both complicated and very simple. They are simple, because everyone has an idea what is a range and what is a cell, and they are complicated, because these are both: Properties of the worksheet object

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

C# – CodeForces – From Hero to Zero

It was some time ago, since I last took a look at programming challange in CodeForces, thus I have decided to take a look again. So, I chose the lowest problem of the second division just to boost up my

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# – Reading from Excel Spreadsheets with Asynchronous Programming (Async)

Reading from Excel in C# is actally not at tough as many VBA developers would assume. The simple reading is actually quite straightforward – there is only small little trick, requiring the adding of the Microsoft.Office.Interop.Excel reference to the Visual

Tagged with: , , ,

C# – How to get the last filled cell of a given row in Excel

Getting the last row of a given column in Excel with a formula or with VBA is quite a trivial task. It is explained here quite well 🙂 However, when we come to C#, the only “trick” that is provided

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

VBA – Data scraping from Internet with Excel – Part 2

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

Tagged with: , , ,

VBA – Add Shape to Range in Excel

Adding shapes to a range in VBA with Excel is actually a trivial task, if you are aware of the Shapes.AddShape method. It has 5 parameters, 4 of which could be pretty easily remapped to the parameters of the range:

Tagged with: , ,

VBA – Animation with Excel

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 old-fashioned, with 1 picture

Tagged with: , ,

VBA – Extracting text from string between two identical characters using VBA

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

Tagged with: , , ,
Top