We're excited to announce that from 14th April we'll be running live online training courses too!
From 14th April we'll be running live online training courses too!
February 2020 newsletter competition - spot the VBA errors
Wally Owl is at it again, this time managing to make no less than 10 coding mistakes in a simple Excel VBA macro. See how many you can find (if you can spot all 10, you may win a prize!).

Posted by Andy Brown on 06 February 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.

Spot the mistakes in an Excel VBA macro

Remember Wally Owl, who made so many mistakes in his SQL query back in August?  This time Wally's had a go at writing an Excel macro, with equally unfortunate results. 

Wally's problem

Wally wants to automate adding an item to his shopping list:

The original shopping list

Any added item should appear at the bottom of the list.

 

This is what Wally would like his macro to show first:

Initial question

Initially Wally's user (probably himself, let's be honest) should type in what he or she wants to order.

After specifying what they want, the user should enter how much of it they want to buy:

Entering amount to buy

The user can choose how much of this product they want to get.

If all goes well (and I have a hunch that it won't) the user should see their new item and amount:

The added item

The new item should be added to the bottom of the list (here I've added 1 kilogram of carrots).

 

Finally, Wally's users should see a reassuring error message saying what's just happened:

The final message box

The final message box should look like this (obviously the exact text will depend on what the user ordered).

Wally's solution

Wally's written his macro in his favourite word-processing program, which unfortunately doesn't trap any syntax errors.  He's sent it to you for checking.

Option Implicit

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", _

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

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(xlToDown).Select

'go down to next blank cell

ActiveCell.Offset(0,1).Activate

'type the two inputs into cells

ActiveCell = ItemName

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

'celebrate completion!

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

vbCrLf(2) & _

"Item ==> " & ItemName & _

vbCrLf & _

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

End Sub

Sadly, Wally has made 10 mistakes which will stop his macro working as described.  To enter the competition, please send your answer listing these mistakes (in any intelligible form) to this address:

Email address for entry

Be warned that Wally has also included the odd red herring (some mistakes - despite being bad practice - won't actually stop the macro working).

 

The first correct answer drawn out of the randomised electronic Wise Owl sorting hat will win a £50 Amazon voucher!

This blog has 0 threads Add post