COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
The answer to our February 2020 VBA competition (and its winner)
We are pleased to announce that our February 2020 competition was won by Ken Warthen, whose name was the first chosen randomly out of our electronic sorting hat. This blog lists the 10 VBA mistakes Wally Owl made.

Posted by Andy Brown on 12 March 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.

Answer to (and winner of) our February 2020 competition

There were 8 entrants for our February competition (Andrew, Christopher, David, Gary, James, Ken, Neil and Paul - you may notice they all have something in common!).  I typed these names into our Excel electronic sorting hat, then:

  1. Used the RANDBETWEEN function to generate random numbers between 1 and 100 next to each;
  2. Pressed F9 five times with my eyes shut to recalculate;
  3. Copied / pasted values to freeze these numbers;
  4. Sorted the people into descending order by number.

At the end of this fair and transparent process, I'm pleased to say that we have our first international winner: Ken Warthen of Access Developer Resources.  A £50 (or dollar equivalent) Amazon voucher is on its way digitally to him.

Our correct macro last month should have read:

Sub Add_Item()

'add another item to your shopping list

Dim ItemName, ItemAmount As String

'get the name of the item

ItemName = InputBox("Type in what you need", _

"Enter item", "Type product name here")

ItemAmount = InputBox( _

prompt:="Type amount needed", _

Default:="Type amount here", _

Title:="Amount")

'go to top item cell

Range("A1").Select

'go to bottom of column

ActiveCell.End(xlDown).Select

'go down to next blank cell

ActiveCell.Offset(1, 0).Activate

'type the two inputs into cells

ActiveCell = ItemName

ActiveCell.Offset(0, 1).Value = ItemAmount

'celebrate completion!

MsgBox "You have added the following item to your list:" & _

vbCrLf(2) & _

"Item ==> " & ItemName & _

vbCrLf & _

"Amount ==> " & ItemAmount, vbOKOnly + vbExclamation, "Added item!"

End Sub

Here are Wally Owl's errors:

Correct answers

If you find this too hard to read, you can download the Word document listing the mistakes here.

Because of a bit of ambiguity in the question, I decided to accept people who spotted at least 9 of the 10 answers, although coincidentally Ken's winning answer correctly listed all 10 mistakes.

This blog has 0 threads Add post