Just another WordPress site

19 Jun 18 TDD in Excel

What is TDD?

Test-driven development (TDD) is a software development process that relies on the repetition of a very short development cycle: requirements are turned into very specific test cases, then the software is improved to pass the new tests, only” (Wikipedia)

We could think this refers only to programming/coding. I don’t think so.

How did I apply a version of TDD in Excel?

Theoretically, when we build an Exel model then it should be simple and easy to read.

However, life goes beyond the simplicity and readability rule because it is important to maintain the structure of the table and/or the importance of calculation speed.
If we use complicated formula, that should be protected or converted to values to ensure the formulas cannot be edited by those who are not familiar with it.

In any case, complicated formulas must be thoroughly tested.

These test cases were listed and the formula was copied to under one another to check different kinds of input values for the correct calculation.

For example, we would like to calculate the date of the first Monday of the first working week.
This is a very simple example, I use this method for much more complicated calculations.

The trick is the week that counts as the first workweek is the one that contains the first Thursday of the year.

I listed years where the first days of January are on different days so that all of the 7 cases are correct. (I added the correct result to compare the calculated results with)

And it seems that there was a need for it because a bad calculation can give a good result.

If I had tested it only till 2020, I could think that everything is good in the first run.
If I had tested it till 2025, I would think the second run is correct.
Thus, the third run gave a result that is good for each case.

For a perfect solution, it would also be worth dealing with invalid inputs, eg. someone makes a mistake and writes a date instead of a year, or writes a text, an error or a fraction.


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:ms access query in design viewThe joker record is not yet included.I open the query for editing (View/SQL view).

SELECT tblUser.userID, tblUser.username
FROM tblUser
ORDER BY tblUser.username;

I change the query source to:

SELECT tblUser.userID, tblUser.username
FROM tblUser
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.

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

Add Line Number Icon

Voila, it is done, and we can use it.