Imagine one of the most trivial tasks in Excel – remove rows from Excel, for which contain the number 1 in their first column. Or in the column below, these are the rows, which are selected. If you are familiar with …
VBA – Delete / Remove rows from a specific worksheet in Excel Read more »
The formulas in Excel, used through VBA are not easy. You need to write a string like “=IFERROR(OR((1=A1),(2=A2)),””No Error””,””Error””)” and this is really not easy and fun to debug. Furthermore, if it is false, VBA will nicely throw a runtime …
Trimming is a function, that reduces spaces. There are at least 4 ways to Trim a string in Excel/VBA: Trim VBA.Trim =Trim() WorksheetFunction.Trim Thus, is there a difference between these 4 actually? This is what MSDN says about String.Trim: Returns …
VBA – Difference between Trim, VBA.Trim, =Trim() and WorksheetFunction.Trim Read more »
Using Visual Studio and Excel is actually quite fun – the Visual Studio advanced environment provides a bit more possibilities than the Visual Basic Editor, built-in Excel. In this article, about a year ago I have shown how to loop …
C# – Looking for a value in Excel with C# & VisualStudio Read more »
After writing that the built-in Find() function in VBA does not return the first value by default, a new interesting case with the Find function appeared. In order to simulate it, the following task could be imagined: Write the first …
If you are coding professionally in VBA, most probably you are using arrays every now and then and you have your own function for sorting an array. Something like this bubble sort or anything similar is what you have written …
VBA – ArrayList class – a helper to sort arrays in VBA Read more »
There is a lot over the Internet, written for the dates in Excel, VBA and SQL Server, but still, there is quite much to be written. In general, it all has started, when the developers of Lotus 1-2-3 have decided …
After the first part about RegEx in Excel lately I had to do something else with RegEx. The task was quite simple, pretty much called “stripping a string”. The idea is that you have a string and you should remove all …
Converting a range to array makes a lot of sense in VBA, when some operations should be performed with data in a given range. The time saving is quite noticeable and it is a bit easier to access and modify …
VBA Excel – How to convert range to array easily Read more »
There is a well-known joke in the IT world – if you have a problem and you try RegEx to solve it, now you have two problems. Still, RegEx in VBA (and thus in Excel) exists as an additional library. …
Sometimes you need to unite two projects into one in VBA. In these two projects, if you are using similar “own built libraries” you are in a kind of trouble. The trouble is called Compile Error :Ambiguous name detected: name Then …
Sometimes we have an excel file, where we want to highlight anything, matching a given cell. Without using the search & highlight option, if possible. Like this: Thus, in this case, whenever something in range A1 is written, the values …
Wikipedia says that the activity selection problem is a combinatorial optimization problem concerning the selection of non-conflicting activities to perform within a given time frame, given a set of activities each marked by a start time (si) and finish time (fi). The problem is to select the maximum …
Sumifs() and Countifs() are formulas in Excel, that sum or count a range with multiple conditions. Unfortunately, these formulas do not work, when you want to refer to a closed workbook. That’s where the magic of the Sumproduct() formula comes. …
Excel – Use Sumproduct() instead of Sumifs() or Countifs() Read more »
In Excel, there are plenty of ways to calculate the differences between days. However, the NetworkDays Formula includes weekends and may include the holidays, which would be different for each country/province. Thus, it is useful! Here is how to use …
Comparing the speed of VBA and C++ in general is quite not fair. Its like comparing the speed of a military jet and a Boeing 767. But still, it’s interesting to see how much C++ is faster in Excel’s native …
VBA vs C++ function – which one is faster in Excel? Read more »
After adding a c# library to VBA I have decided to take a look how to do a similar trick with C++. C++ is a bit fancier in a way. And it works somehow else. So, let’s start. Imagine that we …
Sometimes with Excel you receive an error message, saying “Too Many Different Cell Formats”. This is really hard to believe, because you have not done something bad or strange and yet you cannot finish your action. Still, Microsoft knows about …
Yes, it exists! Today, I somehow managed to install it from here. This is how it looks like: Just a small icon in the Excel data tab. Once you click, you are asked to make the connection with the DB. Due …
In the current article, I show why it is convenient to use the Format as Table option in Excel. Pretty much, it enables a new feature – you may refer to the cells in it not by an address, but with the …
Simple Excel – Usage of column names for formulas Read more »
Plenty of times, when I open some someones Excel files I see something as a check column, where you have only “YES” and “No” as values. They check something and if you are used to it, it can be quite …
Excel – Usage of Checkmark with formulas and custom formatting Read more »
When you work with professionally built VBA tool, sometimes logging your actions (or the actions of some colleague) if the file is shared, can be of use. The idea is not to go into spying, but simply to check who …
VBA – create a log sheet of Excel with specific actions Read more »
If you work as a VBA developer long enough, you may find quite interesting tasks to appear in front of you. Sometimes you have to merge horizontal cells with equal values. And because you are not going to do in …
VBA – Merge horizontal cells with equal values in Excel Read more »
I the current article I will simply present how to save the results from a filtered table in MS Excel in a separate text file. To do this you simply need some table as the following one, where you can …
VBA – Save text from filtered table in Excel in separate file Read more »
If you program with Excel and VBA, probably you have plenty of various functions, showing you how to access some of the file properties. Mainly file path and file name. With the current VBA module, you may see how to …
VBA – Excel custom functions – Document Properties Read more »
With the current article I will simple present some easy code, transferring Chart (diagram) to a picture. It is not something unusual, but sometimes it may be very handy, if you are trying to use the picture later. So, let’s imagine …
Long long time ago, when I was building some Excel VBA spreadsheet tool for a leading retailer, I was having some cheat-sheet in Excel, in order to find the number of the column in Excel – e.g. A-1, B-2, C-3 …
VBA – Getting column name from number in Excel – Function Read more »
In this post I will simply show a few VBA tricks, for defining last used cell in a row (or column) and defining the size of the worksheet. Let’s assume that you wish to delete all the contents from a …
VBA – Defining last cells in row/column and size of spreadsheet Read more »
In this article, I present 5 useful Excel worksheet based tricks: Set a date and time for the beginning of the latest update on the sheet; Color the changed cells in red; Change the sheet name based on the value …
After the first article for custom excel formulas with VBA (here) I have decided to enlarge my knowledge in the area with some additional examples. Here I present three different custom formulas for excel. 1. The first one is an …