COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Announcing the winner of our August 2020 newsletter competition
We had 19 entries for this month's competition, but despite this Matthew Palmer of Public Health England managed to win for a second month in a row. This blog gives a couple of possible solutions to the problem (warning: the blog contains disturbing images of Donald Trump).

Posted by Andy Brown on 23 September 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Results of the August 2020 competition, and prize-winner

If you put the reds, greens and blues together from last month's puzzle pixel by pixel (unsurprisingly, everyone did this using VBA) you got this:

Donald Trump

Not, I think, what most people expected to end up with.

 

Comments accompanying answers included:

  • Well, I wasn't expecting Donald Trump to appear in my Excel file!
  • My favourite world leader!
  • Donald Trump - why!?
  • I feel like I've been the equivalent of 'Rick-Rolled' on this one.
  • The picture is of Donald Trump and I would therefore argue that your workbook is not harmless, despite your disclaimer!
  • While  I've been able to decode the puzzle, it scared the living Bejesus out of me! It's the one, the only, the Donald.
  • That'll be a picture of Donald Trump . I'll go wash my screen now.

Possible Solutions

Various people submitted code answers.  For what it's worth, here was my solution:

Option Explicit

Sub RecreatePicture()

'use the Red sheet as a template - assume all the same structure

'(also note all worksheets have been named in VBA to make coding easier)

'get red sheet range

Dim PixelRange As Range

Dim cRed As Range

Dim cBlue As Range

Dim cGreen As Range

Dim DebugRowCount As Integer

RedSheet.Select

Set PixelRange = Range("A1").CurrentRegion

'clear any old picture and go to top left

PicSheet.Select

Cells.Clear

'create tiny grid

Cells.EntireColumn.ColumnWidth = 0.63

Cells.EntireRow.RowHeight = 6

Range("A1").Select

'loop over cells

DebugRowCount = 1

For Each cRed In PixelRange.Cells

' If cRed.Row <> DebugRowCount Then

' DebugRowCount = cRed.Row

' Stop

' End If

Set cBlue = BlueSheet.Cells(cRed.Row, cRed.Column)

Set cGreen = GreenSheet.Cells(cRed.Row, cRed.Column)

PicSheet.Cells(cRed.Row, cRed.Column).Interior.Color = _

RGB(cRed.Value, cGreen.Value, cBlue.Value)

Next cRed

MsgBox "All done - time to guess who this is!"

End Sub

And here's a much shorter solution from our Wise Owl VBA guru (Mr. Flappy Owl himself) Andrew:

Sub Depixellate()

Dim Reds, Greens, Blues

Dim x As Long, y As Long

Reds = RedSheet.Range("A1").CurrentRegion

Greens = GreenSheet.Range("A1").CurrentRegion

Blues = BlueSheet.Range("A1").CurrentRegion

For x = LBound(Reds, 1) To UBound(Reds, 1)

For y = LBound(Reds, 2) To UBound(Reds, 2)

PicSheet.Cells(x, y).Interior.Color = RGB(Reds(x, y), Greens(x, y), Blues(x, y))

Next y

Next x

End Sub

The Winner!

Out of 19 entries, for the second week in a row it was Matthew Palmer of Public Health England who wins this month's £50 Amazon voucher - congratulations to him.

This proves that we really do award the prize randomly.  In an ideal world the Excel random number generator wouldn't pick the same person twice in a row!

This blog has 0 threads Add post