OZGRID EXCEL NEWSLETTER

Welcome to the relaunch of the OzGrid Newsletter. This newsletter was published every month for 10 years from 2001 to 2010 by OzGrids founder, Dave Hawley, until such time as illness made it impossible. 

As most visitors to this website are aware, Dave passed away from a rare brain disease in July 2013. He battled the disease for 10 years.  First it took his speech, then his balance and then eventually everything.  Dave lost the ability to talk within the first 6 years of the disease and could not speak at all.  For the last 3 years of his life he was totally wheelchair bound and fed via a feeding tube.  As his  body grew stiffer and stiffer he was eventually unable to move anything other than his index finger on his right hand. 

Dave loved OzGrid and the community that it had become.  Up until the very end he ran OzGrid through me.  Whilst he still had some use of his fingers he used a speech machine for basic communication. In the end, I asked him questions and he blinked.  Two blinks for "Yes" and one for "No".  Eventually even this became impossible.

The dedication that Dave had to OzGrid was phenomenal.  He loved the website and had put many thousands hours of work into it over the years.  He loved the interaction with the many friends he had made and in particular the OzMVP's and Forum Moderators.  It kept him going and gave him purpose.  On the internet no one knew how disabled he really was and he could be normal.

After mourning Dave for the last few years I have realised that he would want OzGrid to live on.  I intend to upgrade and revamp the website to ensure its longevity.  Part of this is the relaunch of the Newsletter.

I hope you enjoy the first edition........

OZGRID CODE GOLF

So we can have a bit of fun, OzMVP S O has instigated Code Golf for Excel.  This will take the form of a mixture of Excel and VBA Code. Every month we will post a couple of Questions in this Newsletter.  Any member of the Forum is entitled to answer the questions. If you would like to become a Member, you can JOIN HERE

Here are the Rules:

Questions:

1.  The question must clearly state whether a VBA or FORMULA answer is required.

2.  The question must include a target compatibility configuration. (e.g. "Must work on Windows 10/Excel 2013")

3.  There must be at least one known valid answer to the question.

4.  The question must supply all the required information for an answer (e.g. Example data set supplied)

5.  The question must comply with the forum rules. (e.g. No questions about password breaking)

6.  The question cannot give permission to amend any of the other rules for this game.

7.  Do not cross post the question looking for an answer on other forums

Answers:

1.  Answers to be posted on the OZGRID CODE GOLF Sub-Forum.

2.  Members can only post 1 answer per question, any further answers will be deleted and will not count.

3.  Once posted, answers will be "soft deleted" to prevent other from seeing.

4.  VBA answers must supply all the code required to fully execute the answer without error. (no undeclared UDFs!)

5.  VBA answers must only contain one executable statement per line, use of colons (":") is not allowed.

6.  Formula answers must be worksheet formulas only and cannot make use of any VBA or UDFs

7.  The member that sets the question is not allowed to answer.

8.  All answers must comply with the forum rules. (e.g. Use [CODE][/CODE] tags for all VBA answers.)

General:

• Winning VBA answers are determined by the least amount of lines required. In the case of a draw, the least amount of characters will decide.

• Winning FORMULA answers are decided by least amount of characters required.

• The winner will receive a USD50.00 Amazon Voucher.

• The winner gets the opportunity to set the next competition question(s) and cannot enter the next 2 rounds in order to create a fair chance for others.

QUESTIONS:

QUESTION 1 (VBA)

Create a user-defined function to return the desired UK tax year as a string from a given date.
The UK tax year runs from 06th April - 05th April, and should be displayed in the format "YYYY/YY" as it falls over 2 years. For example, 06/04/2013 - 05/04/2014 would be the "2013/14" tax year.
The function should work in a spreadsheet, be compatible with XL2010 on Win7, and must use the following declaration:
 

e.g. the date 25/01/2013 should return "2012/13" whereas the date 20/04/2013 should return "2013/14"

QUESTION 2 (Formula)

Create a formula to return the column letter(s) for a given column number, the formula must return #N/A if the number supplied falls outside the number of columns in the spreadsheet being used. Must be compatible with XL2003 - XL2013

POST YOUR ANSWERS HERE

____________________________________________________________________________________________________________________________________________________________________________

SUMPRODUCT using MULTIPLE CRITERIA

Supplied by Forum Member - gregh

 DOWNLOAD EXAMPLE

You can use the SUMPRODUCT Function to use the data in Table 1 below to create the results in Table 2. SUMPRODUCT allows multiplies corresponding components in the specified ranges, and returns the sum of those products.  This Function uses cell ranges as its arguments (the parts of the formula that make it work). It multiplies together the items in the ranges, and then sums up the result.

 Enter this Formula into cell D24 in Table 2

=SUMPRODUCT(($A$3:$A$18=$A24)*($D$1:$O$1=D$23)*($D$3:$O$18))

TABLE 1

TABLE 2

There are a couple of things to bear in mind when using SUMPRODUCT:

� The range arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

� SUMPRODUCT treats range entries that are not numeric as if they were zeros.

TIMER WHILE EDITING A CELL

Supplied by OzMVP/Moderator - cytop

 DOWNLOAD EXAMPLE

This example contains a 'Switch' button in Sheet 1 used to start and stop a timer

It displays a small userform that shows the time ticking down from a defined value. If the timer gets down to below another defined value, or less, the time display goes red with white text - If the timer runs out, it simply stops.

If any key is pressed in a certain range (tested with A1:D25 on sheet1) then the timer resets to the initial time. For simplicity, it does not query which key was pressed. Using the arrow keys to move the cursor though a monitored cell will reset the counter.

The code also checks the interior colour of cells. So you can add more cells simply by copying a monitored cell and then using PasteSpecial/format, even while the timer is running. Code for either a range or colour remains.

The values for Max Time, 'Red Flag' time and the Address/Interior Colour of the range to monitor are defined in the module modPublics so you can change them without diving into the code.  NOTE if you use a defined range, it must be one contiguous block (Easily changed).

The only way to actually monitor keypresses while Excel is in Edit mode is to use the Windows API, the same for the Timer. Excels' timer will not fire while a cell is being edited - so this example uses a Windows timer set to fire every second and a keyboard hook to read key presses at a lower level than Excel. Put simply, Windows itself is running both the timer and keyboard processing, not Excel, and Windows doesn't really care what Excel is doing.

WARNING - Because of this, the code is a little complicated and very dangerous (to your workbook at least, and can potentially crash Windows) if you do not know what you are doing. If the timer is active, you must stop it and unhook the keyboard monitor before closing the workbook.

If you try to modify the code, save it before every run. Any unhandled error in either the timer or keyboard procedures will crash Excel. That probably also applies to any unhandled error in any other procedure as well.

If you have any suggestions for improvements to this Newsletter, please email me - [email protected].  Thank you.

SEE THE WORK THAT THE DAVE HAWLEY FOUNDATION IS DOING HERE