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.

Hosting Magento Images on an External Web Server

I’ve been interested to know for some time if it might be possible to host the product images of a magento website on an external server. The server I’m using to host a magento site (acting as both web and database server) is coming under ever increasing load, despite having some upgrades, and putting plenty of caching in place.

The medium/long term solution is to upgrade the infrastructure, but I’m interested as an interim step whether it might be possible to move the hosting of the imagery to a 3rd party cloud solution (like Amazon web services) to take some of the load/traffic off the magento server, and improve the performance and experience for customers visiting the site.

Signing up to AWS (Amazon Web Services) is a pretty straight forward affair. The EC2 Service (Amazon Elastic Compute Cloud) allows you to create a server instance in various locations around the world (Dublin being the closest to the UK). You can choose from different Amazon Machine Images (AMIs), which offer different performance levels and operating systems.

As part of the set-up of the instance you create ssh certificates which allow you to ssh/scp/rsync to/from the server. There are also a few other settings to adjust:

Security groups – these allow you to choose which ports are open (and from where)

Elastic ips – these allow you to create a static ip, which can then be pointed at your server instance (when you stop/terminate an instance its hostname and ip address changes each time it’s started again).

I tarred up the /media directory of the magento site, and scp’d it across to the new server instance…

scp -i **location of key file** media.tar ec2-user@**ipaddress of AWS instance**:**location to place file**

I started apache on the new server instance, and untarred the media directory into the web root, so it has a /media directory with all the various sub directories.

When a customer requested a product image from the live web server I want apache to tell them to go off to the AWS server to download the image. This is achieved by editing the .htaccess file on the live server within the media directory, and adding this entry…

RewriteCond %{HTTPS} !on
RewriteRule ^(.*)\.(jpg)$ http://**AWS IP Address here**/media/$1.jpg

Basically this says that if a jpg file within the media directory is requested (on a non https request) then redirect the browser to get the image from the AWS server instance. The $1 contains any sub directory location path. I wanted to only redirect http requests because the AWS instance doesn’t have a SSL certificate and this might cause alerts when customers were using a secure connection to the site.

To keep the AWS server instance imagery up-to-date I set up a cron job on the magento server to regularly update any changes using rsync…

rsync -vazOe "ssh -i **path to key**" **path to media directory on magento server** ec2-user@**ip of AWS Server**:**path to media directory on AWS server** -u --exclude ".htaccess"

I monitored the apache access and error logs on the AWS server instance to make sure all the images were getting requested ok, and all looked good. Amazon charges using a credit system based on use, so I’ll have to keep an eye on the bills – but so far so good.