Tag: Excel

VBA – Formulas in Excel LocalFormula

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

Tagged with: , , , ,

VBA – Difference between Trim, VBA.Trim, =Trim() and WorksheetFunction.Trim

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

Tagged with: , , , ,

C# – Looking for a value in Excel with C# & VisualStudio

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

Tagged with: , , ,

VBA – Why Range.Find() sometimes mistakes January with November and Febrary with December?

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

Tagged with: , , , ,

VBA – ArrayList class – a helper to sort arrays in VBA

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

Tagged with: , , , ,

The dates in Excel, VBA and SQL Server

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

Tagged with: , , ,

VBA – RegEx in Excel – Part 2

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

Tagged with: , , , ,

VBA Excel – How to convert range to array easily

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

Tagged with: , , , ,

VBA – RegEx in Excel

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.

Tagged with: , , ,

VBA – Listing all procedures in all modules

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

Tagged with: , , ,

Excel VBA – Search and highlight text

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

Tagged with: , , ,

Excel VBA – Activity Selection Algorithm

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

Tagged with: , , ,

Excel – Use Sumproduct() instead of Sumifs() or Countifs()

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.

Tagged with: , , , , ,

Excel VBA – NetworkDays Formula

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

Tagged with: , ,

VBA vs C++ function – which one is faster in Excel?

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

Tagged with: , , ,

C++ – Adding C++ function to VBA/Excel

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

Tagged with: , , ,

VBA – Too many different cell formats

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

Tagged with: , , ,

MySQL for MS Excel

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

Tagged with: , ,

Simple Excel – Usage of column names for formulas

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

Tagged with: ,

Excel – Usage of Checkmark with formulas and custom formatting

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

Tagged with: , , , ,

VBA – create a log sheet of Excel with specific actions

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

Tagged with: , , , ,

VBA – Merge horizontal cells with equal values in Excel

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

Tagged with: , ,

VBA – Save text from filtered table in Excel in separate file

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

Tagged with: , , , ,

VBA – Excel custom functions – Document Properties

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

Tagged with: , ,

VBA – Save a Chart as a Picture (Gif)

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

Tagged with: , , ,

VBA – Getting column name from number in Excel – Function

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

Tagged with: , ,

VBA – Defining last cells in row/column and size of spreadsheet

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

Tagged with: , , , , ,

VBA – 5 Excel Worksheet Based Tricks

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

Tagged with: , , , , , , , , , ,

VBA – Custom Excel Formulas (2)

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

Tagged with: , , , ,

C# – WPF – Generate an Excel file with WPF

In the current article I proudly present a simple application, which creates a simple Excel file from XAML. In the example I present I am using Excel 2010 as this is my current version. In order to be able to

Tagged with: , , , , , , ,