London Marathon 2016

A bit slower this year, just dipping inside 3 hours, with 2:58:52. It’s my slowest time, but I really can’t complain as I’ve only been running once or twice a week with our new arrival at home, and lots on at work. Hopefully next year will be quicker, and maybe an Autumn marathon in 2017 too.

VBA Macro to find distinct items in an Excel spreadsheet column and count number of times found

Just in case of use to anyone, a quick macro that loops through a column on an excel spreadsheet looking at each different value found, and getting the count of the number of times found…

Sub countValuesInColumn(column As Integer, startRow As Integer)

'Get the frequency of distinct items found in a column
'Assumes fewer than 100 different distinct values
'@author: David Boyce 
'@created: 2015-09-24

Dim activeRow, foundTotal As Variant
Dim activeValue, itemsArray(100, 1), msgTxt As String

activeRow = startRow

Do While NotFound = False
  If ActiveWorkbook.ActiveSheet.Cells(activeRow, column) = "" Then Exit Do
  activeValue = ActiveWorkbook.ActiveSheet.Cells(activeRow, column)
    For n = LBound(itemsArray) To UBound(itemsArray)
      If itemsArray(n, 0) = "" Then
        itemsArray(n, 0) = activeValue
        itemsArray(n, 1) = 1
        Exit For
        If itemsArray(n, 0) = activeValue Then
          itemsArray(n, 1) = itemsArray(n, 1) + 1
          Exit For
        End If
      End If
    activeRow = activeRow + 1

For n = LBound(itemsArray) To UBound(itemsArray)
  If itemsArray(n, 0) = "" Then Exit For
  msgTxt = msgTxt & itemsArray(n, 0) & " : " & itemsArray(n, 1) & vbCrLf
  foundTotal = foundTotal + itemsArray(n, 1)

msgTxt = msgTxt & foundTotal
MsgBox msgTxt

End Sub

ebizmarts Sage Pay Suite Pro, Slow Checkout Problem

Just in case it helps someone else in the future. I was having problems with the Sage Pay Suite Pro from ebizmarts on a development site, it was hanging after pressing the “place order” button, and then taking over a minute, before moving to the success page with the payment taken.

First assumption was that it must be a conflict with another custom module, or the theme, and so I started disabling other modules and enabling as much logging as possible, without any joy. I then tried setting up another payment method (cash on delivery), and after pressing “place order” the success page loaded almost immediately.

I tried a different integration method (server rather than direct) but it was also very slow. It occurred to me, it might be the domain name (using a test one, rather than the one registered with ebizmarts). So I used the host file, and updated the development magento site to use the registered one. But no, still really slow.

A bit more googling and I came across this page. I disabled the profiler in the Sagepay settings, and the checkout time was back under 10 seconds. A bit strange because the same issue wasn’t present on the live site, but this seems to have resolved the problem.

London Marathon 2015

Last weekend was the 35th London Marathon, it started way back in 1981. I was running for the 5th time, having first raced London in 2001 whilst working an internship in the city. I had a pretty good race all things considered, finishing in 2:46:30 which was close to my best (2:44:31). I was feeling pretty good up to about 18 miles, but started feeling quite sick from around 6 miles to the finish. I put that down to the energy drink and gel I took, and am quite tempted to try running on just water, or maybe 1 energy drink in the future. The ballot for the 2016 race opens on Monday 4th May, and it’s worth entering early (ie soon after midnight) as in recent years it’s closed within a matter of a few hours!

Magento – Multiple Shopping Cart Price Rules using the same Coupon Code

This has just gone into production so I can’t 100% vouch for it, but it looks like you can remove the unique index on a coupon code in magento v1.6 (I think the new versions have improved functionality so a coupon can be used with multiple rules).

This means the same coupon code can have multiple rules (for example offering free post off any item purchased, as well as another benefit such as a discount if a certain item is in the basket).

The table in the mySQL database “salesrule_coupon” needs the index “UNQ_SALESRULE_COUPON_CODE” removed…


The file /app/code/core/Mage/SalesRule/Model/Resource/Coupon.php needs the function_construct() updated…


protected function _construct()
        $this->_init('salesrule/coupon', 'coupon_id');
            'field' => 'code',
            'title' => Mage::helper('salesRule')->__('Coupon with the same code')


protected function _construct()
        $this->_init('salesrule/coupon', 'coupon_id');
        #    'field' => 'code',
        #    'title' => Mage::helper('salesRule')->__('Coupon with the same code')

You can then create multiple rules, and specify the same coupon code.

Spotting PO Boxes in different languages

Just in case of help to someone else looking to spot PO Boxes in addresses in various different languages, this array might be useful…

$postBoxArray[] = "post box";
$postBoxArray[] = "postbox";
$postBoxArray[] = "postboks";
$postBoxArray[] = "postfach";
$postBoxArray[] = "po box";
$postBoxArray[] = "p.o. Box";
$postBoxArray[] = "apartado";
$postBoxArray[] = "pl ";
$postBoxArray[] = "pl.";
$postBoxArray[] = "boƮte postale";
$postBoxArray[] = "cp ";
$postBoxArray[] = "cp.";
$postBoxArray[] = "poczta";
$postBoxArray[] = "caixa";
$postBoxArray[] = "box ";

foreach ($postBoxArray as &$value) {
  if ( strpos(strtolower($order->$address1),$value) || strpos(strtolower($order->address2),$value) ) {
    #produce warning here

Magento problems with checkout_cart_product_add_after event

I’ve recently been writing some code to fire off emails based on abandoned baskets. The site I’m working on doesn’t send the customer to the basket page when an item is added as it’s better to return the customer to the product page so they can more easily added a second variation of the same product (maybe a different colour or size).

So in order to fire off some code when the item is added I used the checkout_cart_product_add_after event. I found two problems using this event – one is that it fires twice when a configurable product is added to the basket. So when using some code like this to iterate through the items in the basket it was adding an extra entry…

$cart = Mage::getModel('checkout/cart')->getQuote();
$items = $cart->getAllVisibleItems();
foreach ($items as $itemId => $item) {
  $mySku = $item->getSku();
  $myItemId = $item->getId();
  $myItemName = $item->getName();
  $myItemQuantity = $item->getQty();
  $myItemPrice = $item->getBasePriceInclTax() - $item->getBaseDiscountAmount();

The price was also not getting set correctly, the quick fix is to switch to using a different event – there’s one called checkout_cart_add_product_complete and this one will only fire once, and set the price correctly.

There’s a handy list of all the Magento events available here.

Kindle and Calibre

I’ve always said I would never buy an ebook reader, as I dislike reading from a screen – possibly as a result of having jobs for the past 10 years that mean I’ve spent much of the day looking at multiple monitors on my desk. But since buying a Kindle back in 2012 I’m somewhat of a reluctant convert. I say reluctant because I haven’t always agreed with Amazon’s business practices, and it doesn’t make a lot of sense that many books have been more expensive to buy in the ebook format, than getting the printed version (even with the additional postage costs).

However I’ve found the contrast of the Kindle’s “e ink” display is really just as good as any printed book, so that’s one stumbling block gone, and being able to access a book from your Mobile Phone/Kindle/PC/Laptop – in fact from any internet connected web browser using Kindle Cloud Reader really does mean you can take your library everywhere; So when you do find yourself with 5 spare minutes, you can take the opportunity to read a chapter of your latest book. Comparing books I’ve either bought, or borrowed from the library, I’ve had much more success with the ones read on the Kindle.

One piece of software I would recommend to any Kindle owner is Calibre. It has many features, but the one I find most useful is it’s ability to access a news / blog source on a scheduled basis, process all the latest articles and generate an ebook which it can send to your kindle by email. It’s very easy to set-up, with a lot of news sources (eg BBC news and sport) to choose from, and it will happily just take the url to an RSS feed for a blog.

The fact it automatically sends the data is great because when you find the time to have a read, you’ll find it’s already downloaded for you. You could access all the same stories from browsing the web, but on the Kindle it just seems more organised, plus with all the adverts stripped and other distractions taken out, which is a definite bonus.