VBA - how to paste array formula to Table column

If you are working with a Table (ListObject) manually, you can use an array formula. Simply past your formula with CTRL+SHIFT+ENTER. There is no problem and it works perfectly. But if you try paste the same array formula via VBA, you'll get this error: Run-time error '1004': Unable to set the FormulaArray property of the range class.

Realtime filter of Listbox values through a Textbox

I have a Userform with one ListBox and one TextBox. The ListBox contains data and I want to filter these data depending on the text in the Textbox. Moreover, I want to actualize values in the ListBox during typing a text in the TextBox. This is very simple and I'll show you how to do it.

How to remove diacritical marks

Have you ever deal with diacritical marks? Some languages contain characters with diacritical marks, even English language. To replace these characters with their regular equivalents two string constants are usually used. The first one contains the characters with diacritical marks (something like "äáâăąćçčďđëéěęíîĺľłńňöóôőŕřśşšťţüúůűýżźž") and the second one contains the characters to replace them ("aaaaacccddeeeeiilllnnoooorrsssttuuuuyzzz").

A "text effect" in Headers or Footers

I tried to customize headers in my workbook and I discovered a bug. I suppose I'm not the first one and I know I'm not the last one. This bug isn't serious, moreover could be a little bit helpful.

VBA - how to unzip or zip files

Sometimes people ask me whether it is possible to work with zip archives directly in Excel application. That's why I decided to put here subroutines that can unpack a zip archive, pack up files to a zip archive or replace (actualize) an existing file in an existing zip archive.

F.Q.A.: A pressed key in VBA

The question:
"I have a macro with a loop (Do-Loop). When I run it I need to interrupt the running by pressing of any key (for example space), jump behind Loop and finish the macro. Is there a way how can I do it?"

How to create an outline from a database

I've been asked to create a list of elements from a database with the following requirements: the elements have to be arranged hierarchically and there have to be a possibility to collapse/expand them by levels. The structure of the database is set by levels, ie. 1st column = the highest level, 2nd column = the lower level, ..., the last column = the lowest level.

F.Q.A.: FIND_FORMULA

The question:
"There are values (numbers) in the input range. I would like to find the formula of how the calculation of these values is done to get the asked result."

Horizontal page breaks

Time to time someone asks for something like: "I have a lot of rows with data and I would like to have subtotals for each printed page. Is there a method how to do this?" It's not a problem to create the subtotals for the most of the users. But how to find page breaks? There are several methods which are less or more reliable. I want to show how to do this with Excel4Macro user defined function. What is a significant plus is that the height of rows don't have to be the same or the rows can be hidden.

The date of Easter

Several years ago I looked for a formula which calculated the date of Easter. I needed it for creating a working calendar and Easter Monday is a holiday in my country. In fact it was no problem to find this formula. Just ask Google "excel easter formula". It has been working reliably and calculating the date for requested year since then.

RichTextBox in Excel

I never needed a tool like RichTextBox in the Excel. But I've found out a lot of people want it. If you click on this link, you can see that this question will appear relatively frequently. But you can also see that this control is not supported in the Excel. You can find some tutorials and maybe some of them will work. Don't know it, haven't tried. So I don't judge.

Easy and quick formula translation

Are you using a localized version of MS Office? Did you find a formula with the English names of functions? You can translate each function in the formula manualy or you could use Immediate window in VB editor.

How to change a formula

I have the same formula in multiple cells and I want to change this formula in all these cells. This is a simple task if the formula is placed in a continuous range. I do it thus:

Snake for Excel

My first mobile phone was a Nokia. My third, fourth and fifth phones were Nokia, as well. And, for a change, my first smartphone was a Nokia. Now I don't have Nokia phone. And who knows if I ever will have a Nokia phone again.
By creating this game I want to say: Goodbye Nokia, I really liked your devices.

Array formula (CSE) in a VBA code

Sometimes for continuing of a code we need to get a value from a range in the sheet. For example we have a range with numbers and we want to find a minimum. There is the object called WorksheetFunction, then we can write:

Working with two monitors

Working with two monitors is realy great. There are keyboard shortcuts in Windows 7 that make the work with them much easier and faster (try for example WIN+arrows or WIN+SHIFT+left or right arrow). But if we want to use this tools in Excel, we have to work with multiple instances. However, in this case we lose advantages of the single instance (like copy + paste values etc.).