GMail Update

So I've been using GMail for a little under a month now and I am still very impressed. I am still unsure how it would fit into a larger organisation (with 10+ seats) there are 2 things I dislike about it at the moment.

Firstly there's the username issue, I finally managed to get my username straightened out and in fairness I may have been partly to blame. I had assigned my required forename.surname as an alias to my current account, so I was unable to rename until I removed this alias - so after waiting the required 5 (or was it 7 days? I actually waited 14) I finally started to rage at Google, hammering my keyboard with search terms until I finally gave up and decided to mess around with my account settings the error message in a round-about way lead me to believe removing the alias would work, and it did!

Secondly I don't like how the username you sign into email with follows you across all of Googles sites. Now for most people this isn't a problem and its actually benefits them but for me it doesn't. For my personal business I have my own domain and therefore use that for all my email, I have a separate signon for Blogger and for YouTube (although I think I somehow ended up managing to link the Blogger and YouTube accounts)

It's not really a problem, but it is an annoyance.

Google Chrome

I never got caught up in the hype of Google Chrome when it first came out. Since I first got online I’ve been a massive fan of tabbed browsing and I used to use a bit of software called “Kitty Browser” This is back in the Windows 98 days but I believe it just wrapped IE5 and added some additional functionality. Fast forward a number of years and we now have Firefox (and recently IE7,8 and 9) thrown in some “Add-ons” and you have a very solid browser.

Enter Google Chrome, I tried it a year or two ago and quickly uninstalled it after 30 seconds. Today I’ve tried it again and given it a really good go. I’ll report back anything of note but my first impression is that rendering of web pages is amazingly quick, this thing is like lightning compared to IE/Firefox, I’d dismissed what others had said about it but having now used it I am very impressed. 

GMail

I’ve run into this problem before and it serves are reminder of how anal Google can be at times. Internet at my home as gone down (BT line problem) as a result I have no email as everything was coming into an Exchange box in my attic. I’ve now decided to shift my email over to Google.

The Google Apps for Business (http://www.google.com/apps/intl/en/business/index.html) is an excellent bit of software, it really is. The migration wizard (step by step guide) explained things and provide further information if needed. I’d say within about 5 minutes I was up and running, now this may have been where I went astray. Google only allows a username to be created once (and it ignores . [full stops], so forenamesurname and forename.surname are the same) Having signed in at admin and created 2 new (additional users) I started to look at how much this would cost, a reasonable £2.75 per user per month (if signing up for 12 months [£33]) however Google wants me to pay for 3 accounts! Administrator and my 2 users, so I promptly set about deleting myself and trying to rename the admin, however as I’d already created a user (even though I deleted) I am now unable to sign in with forename.surname@domain, very annoying. What solution do Google offer to this? Adding forename.surname as a nickname (alias) to the admin account, this is just stupid. I tried it and also fiddled around with the reply-to address, but it still always shows as admin@domain

The only possible saving grace in this is that I think the username may become available again after 5 days... it still seems farcical that I only made the username 5 minutes ago but I now have to wait 5 days in order to reclaim it  (very annoying considering I own the domain name)

Excel Tips

Recently I’ve been doing a lot of working with Excel and I’ve really pushed myself to solve some problems that have bugged me for a number of years.

I wanted a way to convert between column letters and a numerical value. As you may be aware you can reference a cell via its row number and the column letter, but you can also reference it via row number and column number e.g. A=1, B=2 and C=3

It’s useful sometimes (when programming especially) to switch between the methods you use. Not wanting to type out (in this case) 153 strings e.g. A,B,C,D ... EZ, I found the following formula which can be copied into A1 and downwards and it will give you the running values

=SUBSTITUTE(ADDRESS(1, ROW(), 4), "1", "")

Very useful.

The second formula (actually function) I’ve come across is 

=SUMIF(range,criteria,sum_range)

Usage: Range is the range of cells you want to query on e.g. Place, Sex, Unit of measure, Criteria is the what records you want it to match on e.g. London, Male, KG. Sum Range is the range of cells you want it to total up.

Very simple, yet very powerful

How to sort listview by clicked column

Found an excellent article on another blog http://www.fryan0911.com/2009/05/vbnet-how-to-sort-listview-by-clicked.html I'll also post the article here in case Fryan's blog is ever down

 1. On your existing project, add a new class with following code:

Public Class clsListviewSorter ' Implements a comparer
    Implements IComparer
    Private m_ColumnNumber As Integer
    Private m_SortOrder As SortOrder
    Public Sub New(ByVal column_number As Integer, ByVal sort_order As SortOrder)
        m_ColumnNumber = column_number
        m_SortOrder = sort_order
    End Sub
    ' Compare the items in the appropriate column
    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
        Dim item_x As ListViewItem = DirectCast(x, ListViewItem)
        Dim item_y As ListViewItem = DirectCast(y, ListViewItem)
        ' Get the sub-item values.
        Dim string_x As String
        If item_x.SubItems.Count <= m_ColumnNumber Then
            string_x = ""
        Else
            string_x = item_x.SubItems(m_ColumnNumber).Text
        End If
        Dim string_y As String
        If item_y.SubItems.Count <= m_ColumnNumber Then
            string_y = ""
        Else
            string_y = item_y.SubItems(m_ColumnNumber).Text
        End If
        ' Compare them.
        If m_SortOrder = SortOrder.Ascending Then
            If IsNumeric(string_x) And IsNumeric(string_y) Then
                Return Val(string_x).CompareTo(Val(string_y))
            ElseIf IsDate(string_x) And IsDate(string_y) Then
                Return DateTime.Parse(string_x).CompareTo(DateTime.Parse(string_y))
            Else
                Return String.Compare(string_x, string_y)
            End If
        Else
            If IsNumeric(string_x) And IsNumeric(string_y) Then
                Return Val(string_y).CompareTo(Val(string_x))
            ElseIf IsDate(string_x) And IsDate(string_y) Then
                Return DateTime.Parse(string_y).CompareTo(DateTime.Parse(string_x))
            Else
                Return String.Compare(string_y, string_x)
            End If
        End If
    End Function
End Class

2. Declare a private variable on the form where the listview you want to be sorted is located.

 Private m_SortingColumn As ColumnHeader

3. Then on the listview's ColumnClick event, add the following code

 Private Sub ListView1_ColumnClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles ListView1.ColumnClick
        ' Get the new sorting column.
        Dim new_sorting_column As ColumnHeader = ListView1.Columns(e.Column)
        ' Figure out the new sorting order.
        Dim sort_order As System.Windows.Forms.SortOrder
        If m_SortingColumn Is Nothing Then
            ' New column. Sort ascending.
            sort_order = SortOrder.Ascending
        Else ' See if this is the same column.
            If new_sorting_column.Equals(m_SortingColumn) Then
                ' Same column. Switch the sort order.
                If m_SortingColumn.Text.StartsWith("> ") Then
                    sort_order = SortOrder.Descending
                Else
                    sort_order = SortOrder.Ascending
                End If
            Else
                ' New column. Sort ascending.
                sort_order = SortOrder.Ascending
            End If
            ' Remove the old sort indicator.
            m_SortingColumn.Text = m_SortingColumn.Text.Substring(2)
        End If
        ' Display the new sort order.
        m_SortingColumn = new_sorting_column
        If sort_order = SortOrder.Ascending Then
            m_SortingColumn.Text = "> " & m_SortingColumn.Text
        Else
            m_SortingColumn.Text = "< " & m_SortingColumn.Text
        End If
        ' Create a comparer.
        ListView1.ListViewItemSorter = New clsListviewSorter(e.Column, sort_order)
        ' Sort.
        ListView1.Sort()
    End Sub

PHPBB3 Tables and Excel

One of the forums I currently host and manage makes use of PHPBB3, the user’s post a lot of different tables (league standings e.t.c.) so being able to post tables is a must so I added some BB codes for [table], [tr] and [td] and they can now happily post all the tables they like.

The source data for these tables is held in excel, and having to upload and update 3 different tables each week is a little tiresome so I developed a little VBA Macro that will convert a selection to PHPBB3 Table code.

Throw a button on the toolbar and link it to the macro and job done!

---

Private Sub CommandButtonClose_Click()
    'closes the form
    End
End Sub

---

Private Sub CommandButtonCopy_Click()
    'Copies the content of the TextBoxTable
    Dim ansDataO As DataObject
    Set ansDataO = New DataObject
   
    ansDataO.SetText TextBoxTable.Text
    ansDataO.PutInClipboard
End Sub

---

Private Sub UserForm_Initialize()
    Dim i_ColumnCount As Integer
    Dim i_RowCount As Integer
    Dim i_ColumnLoop As Integer
    Dim i_RowLoop As Integer
   
    'Gets the number of columns / rows in our selection
    i_ColumnCount = Selection.Columns.Count
    i_RowCount = Selection.Rows.Count
 
    'stores the phpbb code
    TextBoxTable.Text = "[table]"
   
    'loops through each row in our selection
    For i_RowLoop = 1 To i_RowCount
        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[tr]"
           
        'for each row we loop, now loop through the column
        For i_ColumnLoop = 1 To i_ColumnCount

            TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[td]"
           
            TextBoxTable.Text = TextBoxTable.Text & Selection.CurrentRegion.Cells(i_RowLoop, i_ColumnLoop).Value
                   
            TextBoxTable.Text = TextBoxTable.Text & "[/td]"
        Next

        TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/tr]"
    Next
       
    TextBoxTable.Text = TextBoxTable.Text & vbCrLf & "[/table]"

End Sub

---

PHP IIS Got a whole lost easier

I can't remember if I’ve ever blogged about my problems setting PHP up on Windows under IIS, it can be a real headache... sometimes it works as per the documentation, other times (even on a clean server) it doesn't and I end up messing around with all kinds of settings and NTFS permissions, after several system reboots things finally start to work.

Recently I’ve been working on a project that involves using PHP and MSSQL (something I've not done before) it looks like php_mssql.dll is now history (up to PHP v5.2) the latest version of PHP uses a slightly different dll.

To my relief my latest PHP installation was a breeze, fire up the URL below, check all the stuff you want it to install and off it goes, all configured, all working (I wonder what the security is like)

http://www.microsoft.com/web/Downloads/platform.aspx

Online document conversion

I needed to convert a PDF document to an image today (didn't want to screen dump it due to resolution loss) after finding 2 or 3 services that boasted they could do the job 'online' and for 'free' (all 3 of which failed or couldn't handle a PDF as large as 1MB) I came across Zamzar. This website can handle a lot of common file conversions (e.g. mov to mpeg e.t.c.) It also features a YouTube video ripper (that could come in handy)

Check it out at http://www.zamzar.com/