Tag: Excel

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

VBA – Customize Active Window in Excel

In this article I present a good possibility to customize your Active Window from Excel itself and not from the ribbon. By inserting some form controls, we receive a decent-looking customization dashboard like the one below:   The idea of

Tagged with: , ,

VBA – Excel Change Formulas to Values

Changing formulas to values is something really easy in MS Excel – what the majority of people (including me) would do the following – simply record a macro, where you select all cells and paste them as values. The result

Tagged with: , ,

VBA – How to delete quickly all pictures from Excel?

Probably, if you are programming with Excel as much as me, one day you have the following question – all the pictures in Excel are generated by my code and I really do not care to give them names. I

Tagged with: , , ,

MS Excel – Simple Linear Regression

Linear regression is something rather useful for forecasting analysis. If you want to understand more about linear regression, you may go to wikipedia, there you have a wonderful article about it. Long story short, linear regression is an approach, modeling

Tagged with: ,

MS Excel – Indication of duplicate values

An hour ago I had to look for duplicate values in a spreadsheet. I looked for a minute in google and found plenty of solutions, as you can imagine. One of the best was described here, but it is quite useless,

Tagged with: ,

VBA – How to start?

Lesson 1 – VBA and Excel What is VBA? – VBA is something great, if you are working more than 1 hour with MS Excel and MS Access. It is simply said a must for everyone, who earns his money

Tagged with: , ,
Top