13 Oct 09 Giant chart with conditional formatting
Sometimes I meet demonstrative tasks where the great number of data makes impossible to show it in even a full monitor/paper sized chart.
A few days ago, there was such a task, where I needed to illustrate the harness of hundreds of birds. Those being less adaptable and/or not being a Excel expert, might consider this impossible.
I’ve got a trick to solve it: I don’t use a chart.
Conditional formatting is a perfect tool to do this. Using it, colored cells will compose the diagram. It is possible to create multiple-monitor charts with it. Of course this technique has its own limits too: for an example the number of disposable columns, and the fact, that not all types of diagrams can be created with it. (It can be surely used to create a: bar or a column chart, a scatter plot or a Gantt chart).
This is how a giant chart looks like:
I put some small things on it for better perspicuity and legibility:
- Aid-lines each 10 row and 10 column.
- A tricky headpiece to ensure the legibility of each piece of data in contempt of downsizing.
What do you think about it?
I would appreciate if you would write it between the comments.
23 Sep 09 The World’s most useful excel worksheet
Why shouldn’t I write about it, when it’s close to my topic, and you find it on lots of Hungarian websites?
This might be a long step towards Excel for lots…
This is a namely a blood alcohol level calculating Excel sheet.
It shows us when we are going to sober up.
Use it at your own risk!
I don’t know who is the author of it, because each version has another name (plagiarist seem to have written their own name in it), but the original file is creditable.
The idea is a strike of a genius, and the applied techniques show some possibilities of Excel.
The World's most useful excel worksheet
Besides the fact that it’s fun, I uploaded this file because of its use of form elements.
I altered it in a way that the slider, the radio button and the checkbox can be tried out in it.
The file is downloadable from here: http://www.access-excel-vba.com/mostuseful.xls
It’s not containing a macro, so it doesn’t need programming abilities, the form elements can be used without this, and although it’s spectacular, you can create it in an easy way.
Use it!
Cheerio!
13 Jul 09 Acceleration with the keyboard
The air is boiling at my place.
Not only the computers, and the spring/summer are heating me, but I’ve completely turned on a key on my keyboard.
I’ve already had something similar with software, computers, mouse devices, but this is something else! 
You will understand it soon, because it’s a logical love. Maybe my bride will understand it too…
It was between the right ALT and the right side CTRL and it haven’t really bothered me for a long time, what is it for, or what does it depict. It was there with its abilities to be discovered.
Luckily I pressed it, and a miracle happened! The local menu appeared!
It’s incorporated in most of the programs, and it contains the actions that can be performed in the current situation.
It can accelerate your work a lot, because you don’t need to use the mouse.
Let’s take a look at its usage. I demonstrate it in Excel, but it works identically in most of the software.
When I press this button the local menu appears.
We can step through the menu items with the up-down arrows, and can execute one with Enter.
It is even quicker to find the underlined letter in the menu item to be selected, and when the local menu is visible, just press that letter in the keyboard, and the action will be executed right away. (In the picture the letter “F” is underlined in “Format Cells…”)
The local menu can be turned off with the ESC button.
If you liked or disliked this article, I wait your comments.
If lots of people liked this one, I will publish some other keyboard shortcut tricks too. And of course other tricks too.
09 Jun 09 Fake item in dropdown – MS Access SQL trick
I often use a combobox to filter.
For an example, when browsing between the tasks assigned to the staff, it can be selected, which co-workers jobs should be displayed.
It occurs sometimes, that in an Access form we have to include to a combobox / dropdown list an “Any/All” item, which can be used, when we don’t want to filter according to the co-workers.
I have a stylish/interesting solution for this problem.
I create a query, which shows only the active records in alphabetical order.
It looks like this:
The joker record is not yet included.
I open the query for editing (View/SQL view).
SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
ORDER BY tblUser.username;
I change the query source to:
SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
UNION SELECT 0, 'All'
FROM tblUser
WHERE (((tblUser.userID)=1))
ORDER BY tblUser.username;
I base my virtual record on another record, but I assign its values myself.
Therefore my query returns an enlarged list.
When using on a form, I set the default value of the dropdown list to 0.
I set the dropdown’s update event that when it is set to 0 the filter omits this dropdown, and in all other cases it omits the selected one.
I hope you liked this hint.
18 Mar 09 Secret of the giant excel array formula
I’ve already mentioned this on Twitter:
# The secret of my giant excel array formula is soon to be revealed. 928 characters, 78 left braces, 76 function in a cell.
# My Excel monster-formula makes 240 cells needless. Half as many lines are enough
9:13 AM Mar 16th from web
# What do you think, how can such a big formula be maintained? Tomorrow I’ll give you the answer.10:39 AM Mar 17th from web
# Here’s the great day, and the secret of the giant excel array formula.3 minutes ago from web
One of my customers needed an auxiliary line to perform a calculation reoccurring in every line.
This broke the unity of the whole system and made it quite hard to use.
My formula was much simpler at the beginning, but I improved it so many times that it became gigantic.
It is not easy to review a formula of such size, and to maintain it the traditional way is quite impossible.
I turned to VBA for a solution. I pieced the formula together in a macro.
I created variables for the recurring parts of the formula, which could even follow from each other:
strDate = "R" & intDateRow & "C"
strIndex = "IF(RC" & intIndexColumn & "=0, R24C15, RC" & intIndexColumn & ")"
strMonthStartDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,1)"
strMonthEndDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,0)"
Than from these variables I aggregated the whole function.
By breaking apart the formula, it remained maintainable, and comprehensible.
This was one of the great secrets, I gathered together in 9 years of Excel programming, but there are a lot more.
12 Mar 09 Error communicating with the OLE server or ActiveX Control
Several MS Access databases working on one computer and they don’t work on another.
The error message belongs to the forms of the database.
When you click on a button, MS Access gives this error message:
Error communicating with the OLE server or ActiveX Control
The error is generally caused by the use of special characters in object names.
When you create a form in Hungarian or other localized Access you will see, that the objects on the form get problematic names (Labels – Címke, Header – Űrlapfej, Footer – Űrlapláb, Combobox – KombináltLista etc.)
The solution:
1. You have to rename the special named objects on the problematic form.
2. If the error remained then create a new mdb file and import all objects from your old mdb.
16 Feb 09 Relative range names in practice
With range names you can do big tricks with excel.
I will show you one of these tricks today:
When you define a range name, then excel will propose the absolute cell reference as default.
But you can use a relative or a mixed reference too.
The active cell is the starting point.
You have to create your reference like you would write into that cell.
My trick is:
- Select A2 cell.
- Choose Insert –> Name –> Define
- Type a name for the range: “Last”
- Write in the refers to box “=A1″ without quotes and without dollar signs.
- Give a name to this range, for example “last”
This range name will refer to the upper cell to the current position.
It can be used like this:
=SUM(B2:Last)
will give you a dynamic range that will grow or shrink when you insert or delete some rows, even if the deleted row is the last row from the range.
This is a perfect solution.
If you insert multiple rows and don’t fill every cell, the normal reference will not upgrade, the relative name will always work.
02 Feb 09 Illustrated Excel keyboard shortcuts
It can make our work quicker, if we execute most of the tasks from the keyboard, and use the mouse less frequently.
If someone is familiar with a few keyboard shortcuts, he/ she will know the name is not given by chance.
With one of my colleagues we created a reminder note, which contains the Microsoft Excel hotkeys for the moves. You can print it on a normal sheet, and put it next to your monitor, to be in view, so you can learn it easily.
Please click on the link to download it: http://www.access-excel-vba.com/ms_excel_keyboard_shortcuts.pdf
The keystrokes works with OpenOffice Calc too.
Other useful resources:
http://exceltip.com/news/excel-shortcut-heaven.html
http://tricks-4-fun.blogspot.com/2008/09/best-excel-shortcuts.html
28 Jan 09 VBA Error Handling with line numbers
Sometimes it is easier to find an error if we know the code line where the error occurred. I have a method to show this in an error message.
This is a light version of my error handler and focuses only to the line number of the error.
There is a variable called “erl” which contains the line number of the error, so we can use it in the error message:
MsgBox Err.Number & " - " & Erl & " - " Err.Description
But this will show 0 instead of the line number if we didn’t put numbers at the beginning of the rows.
Dick Kusleika wrote about the MZ Tools, and MZTools is a great tool for this too.
There is an “add line number” icon on the toolbar which made the line numbering in my stead.
Add Line Number Icon
Voila, it is done, and we can use it.
04 Jan 09 This is the beginning of a beautiful blog…
A new Excel, Access, VBA blog arrived onto this world.
The happy father is Zoltan Till, a Hungarian developer from Budapest.
Yes, that’s me!
I’ve created a wide variety of business applications since 2000. I’m a Hungarian programmer, trainer, presenter in conferences, and consultant from Budapest. My favorite topic is Excel but I’ll also write of some other subjects because I have been working with SAP, Oracle, MS SQL, Lotus Notes / Domino, and BO (Business Objects) too.
The adventure begins soon.
Subscribe!
