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 »
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 »
About 4 years ago, I wrote an article about prime numbers in Excel and their visualisation with VBA – vba-function-in-excel-to-check-prime-numbers. Pretty much, it displays the prime numbers in a 10 x N matrix, starting from 1 and finishing at N: …
Prime numbers matrix and coloring with Python and xlsxwriter 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 »
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 »
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 …
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 »
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 …
C# – Reading from Excel Spreadsheets with Asynchronous Programming (Async) Read more »
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 »
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: …
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 …
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, …
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, …
=VLOOKUP and =INDEX(MATCH(),MATCH()) are well known formulas, if you need to get some value corresponding to another value from a specific column. The problem comes, when you are having more than one column or row, and you need to locate the …
Excel – Get Top Row of a Multi Row / Mult Column Range Read more »
So, the idea is the following – imagine that you have an NxN range, which should be filled out with consecutive numbers. Thus, if the range is 6×6, you start filling the numbers normally and once you reach the 6. …
Formatting a substring of a string in an Excel Cell is actually quite a challenging task. There are two ways to do it – select part of the substring that you wish to format and change its color. Then make …
Changing tab color of Excel is somehow useful. With VBA, it is easily programmed, as far as one can use the macro recorder to see the correct way to do it. However, if you need to change the color of …
Selecting multiple cells in Excel through VBA may be quite a difficult task. Although, using Select is not always a good practice, sometimes it should be done. To generate some numeric values, let’s run the following: Public Sub FullMe() Dim myCell …
Excel and VBA are a powerful tool for simulation. The idea is to simulate dice throwing and to keep the best N throws as our score. Other model variables are Times I roll, which stands for how many times do …
Markov model is a a stochastic model describing a sequence of possible events in which the probability of each event depends only on the state attained in the previous event. Wikipedia This is a good introduction video for the Markov …
The worksheet events which are built within VBA are quite useful. One may build them quite easily in Excel and they work satisfactory. However, let’s imagine, that you have a merged cell as in the picture above and you want …
VBA – How to Change Merged cell in Excel with Events Read more »
There is an ERP joke, claiming that the third most popular button of an ERP system is “Export to Excel” (after OK and Cancel). Thus, working with Excel from .Net is happens often. There are some pitfalls, that you should …
.Net – Reading Excel file in C# and transferring it to a matrix Read more »
