Derek Smyth's Software Development Blog

Column letter to column number in Excel.

In a previous blog I posted a VBA algorithm for Excel that took the column letter (A, B, Z, AA) and returned the corresponding column number. There was a tiny little problem in the code, so I'm reposting the entry with the fix. The tiny little problem was you had to pass the column letter in upper case.

I couldn't find a method to covert the column letter to column number in Excel, i.e. Column A = Column 1, Column AA = 27 so, as you do, I wrote my own. It's good for letting the user enter a column in a GUI as a letter while you use it in code as a number.

Function ColRef(Col As String) As Integer
'
' Returns Excel column number
'
' Input:
' Col Excel column reference (eg. AA)
'
' Output:
' ColRef Column number (eg. 27)

'col needs to be upper case
    Col = UCase(Col)
   
    If Len(Col) = 1 Then
        ColRef = Asc(Col) - 64
       
    ElseIf Len(Col) = 2 Then
        C1 = Left$(Col, 1)
        ColRef1 = (Asc(C1) - 64) * 26
       
        C2 = Right$(Col, 1)
        ColRef = ColRef1 + (Asc(C2) - 64)
    End If
   
    If (ColRef <> 256) Then
        MsgBox "Wrong Column number", vbExclamation
        ColRef = -1
        Exit Function
    End If

End Function

It takes the ASCII code of a single column letter and coverts it to it's numerical equivalent by taking 64 from it. ASCII code of A = 65 (- 64) = Column 1.

If there are two letters then it works in base 26.
AB
Asc(B) = 66 - 64 = 2
Asc(A) = 65 - 64 = 1 * 26 = 26
AB = 26 + 2 = 28

Compiling class libraries using Visual Studio Standard

About a week ago a VB developer, who I was giving an overview to an .NET application, asked about creating DLL's in Visual Studio. Unfortunately for him he only had Standard Edition and so the option to add a class library project wasn't available from the templates.

However a little knowledge goes a long way and I remembered reading about how the vbproj/csproj file of a project is essentially an XML file containing the build/compile options of the project. So I did a little exploring and found that by changing the <OutputType>WinExe</OutputType> line to <OutputType>Library</OutputType> in the vbproj/csproj file of a project you can indeed compile a class library DLL's using the IDE of Visual Studio Standard 2003.

I explored some more, although not enough, and found you can customise the whole build process using the MSBuild engine. The build process has targets (events) where you can insert your own tasks. Here are some links that I found that cover MSBuild.

Describes the available targets in the MSBuild compile process
http://blogs.msdn.com/msbuild/archive/2005/11/23/496396.aspx

MSBuild Preview Quickstart Tutorials
http://www.microsoft.com/downloads/details.aspx?FamilyID=e2b4bd85-bd66-4f09-8103-457c3ece8b3e&DisplayLang=en

Compile Apps Your Way With Custom Tasks For The Microsoft Build Engine
http://msdn.microsoft.com/msdnmag/issues/06/06/InsideMSBuild/default.aspx

Visual Studio 2005 Icons

Just found out that there are a nice selection of animations and graphics to be found in the Visual Studio 2005 folder. If you installed to the default directory you'll find these icons here.

C:\Program Files\Microsoft Visual Studio 8\Common7\VS2005ImageLibrary

It includes some animated gifs that would be useful for searching and file copies. To display these just use a picture control on a form, the picture control handles the animation and transparency.

So there something else that's quite good to know, the picture box displays animated gifs with full support for transparent backgrounds... interesting.

Hashing, MAC, and Digital Signatures

When you don't get to program certain technologies in your every day work you do tend to forget them, this was why I started my original blog over at blogger.com. I needed a place where I could write about and remember them.

A group of technologies I nealry forgot about was the differences in hashing, message authentication codes (MAC) and digital signatures. So I wrote an article on them as a means to remind myself of what they are and how they work. Hopefully it may prove useful to someone else so here it is.

Hashing, MAC and Digital Signatures.pdf

VBA Regular expressions with Word

Another post in VBA MSDN, it's been a slow week, asked 'is it possible to select all upper case words in a word document". I haven't done much programming with Word's API so thought I'd give it a shot as it gave me an excuse to brush up on some regular expressions.

Surprisingly Word's search feature doesn't allow regular expression patterns, which is a bit rubbish, but that doesn't stop them being used. So the first thing to do is add a reference to the regular expression VBScript library (VB Editor->Tools->References->VBScript Regular Expressions 5.5) 

The text needing to be searched in the document is all words that contain upper case characters. These words can be matched with this pattern "\b[A-Z]+\b". This can be broken down into the following...

\b = word boundry, this lets you perform whole word searches, for example "\bor\b" will match the text "or" only, it won't match "for" or "order", \b indicates the start or end of a word. "\bor" will match "order" and "or" but not "for".

[A-Z] = matches a single upper case character e.g. a character from A to Z

+ = matches one or more characters, e.g.[A-Z]+ matches one or more upper case characters

\b[A-Z]+\b = matches a whole word that contains one or more upper case characters

Thats the pattern broken down so here is the code to parse the document.

Sub BoldUpperCaseWords()
 
  Dim regEx, Match, Matches
 
  Set regEx = New RegExp            ' Create a regular expression.
  regEx.Pattern = "\b[A-Z]+\b"         ' Set pattern.
  regEx.IgnoreCase = False           ' Set case insensitivity.
  regEx.Global = True           ' Set global applicability.
 
  Set Matches = regEx.Execute(ThisDocument.Range.Text)    ' Execute search.
 
  For Each Match In Matches     ' Iterate Matches collection.
    'selects a range from the index of the character to the index of the character plus the length of the word
    ThisDocument.Range(Match.FirstIndex, _
                                    Match.FirstIndex + Len(Match.Value)).Bold = True
  Next
 
End Sub

The code above executes the pattern against the documents text and for each word that matches that pattern is bolded. A couple of points to highlight, the method ThisDocument.Range.Text returns all the text in a document (thats handy), the IgnoreCase property of the RegExp must be set to false as the text case is important for this code, and the Global property is set to true to indicate that the pattern will be used to make many matches over the search text.

Accessing Excel workbook as database schema (ADOX).

Another question posted onto MSDN VBA asked the question, I want to retrieve (using ADO) Excel worksheet name and total count of sheets. Very straight to the point ! Well since I'd solved the other problem I wondered if this was possible and it is!

To do this in VBA you need to reference the ADO Ext for DDL and Security objects. Once you do that you can basically get information about the sheets in the Excel workbook as you would tables in a database. Here's some code.... again the workbook must be saved for this to work.

Public Sub GetWorkbooksSchema()

    'the name of the workbook, the workbook must be saved prior to accessing it using ADO
    Dim sWorkbook As String
    sWorkbook = "E:\Knowledge Base\Forum Solutions\Office Apps\Excel Schema\Test Sheet.xls"
   
    'connection object needed to connect to Excel/Access
    Dim adoConnection As New ADODB.Connection
    'connection string to Excel/Access
    adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWorkbook & _
                                    ";Extended Properties=""Excel 8.0;HDR=Yes;"""
    'open the connection
    adoConnection.Open
   
    'treat the workbook as a database catalog, the catalog at the connection
    Dim adoWbkAsDatabase As New ADOX.Catalog
    adoWbkAsDatabase.ActiveConnection = adoConnection
   
    'get the catalogs tables, which in this case is the worksheets
    Dim adoTables As ADOX.Tables
    Set adoTables = adoWbkAsDatabase.Tables
   
    'display a count of the worksheets
    MsgBox adoTables.Count

    'for each table/sheet in the catalog/workbook display the name
    Dim adoTable As ADOX.Table
    For Each adoTable In adoTables
        MsgBox adoTable.Name
    Next

    'close the connection
    adoConnection.Close

    'tidy up
    Set adoConnection = Nothing
    Set adoWbkAsDatabase = Nothing
    Set adoTables = Nothing
    Set adoTable = Nothing

End Sub

The code comments should cover what's going on. Need to point out thought that it's not possible to modify the Excel file using this approach for example you can't append a table/sheet to the workbook throught the Tables collection. It looks like read only access to information. Also Excel doesn't have the concept of keys and indexes so your very limited in that respect.

If you want to add or delete sheets and so on you can use SQL DDL statements instead like the CREATE TABLE SQL statement.

    Dim adoCommand As New ADODB.Command
    adoCommand.ActiveConnection = adoConnection
    adoCommand.CommandType = adCmdText
   
    'creating a table/sheet using the CREATE TABLE command must specify field(s) in the sheet/table
    'there must be a header row in the newly created sheet
    adoCommand.CommandText = "CREATE TABLE [Sheet5] ( ID int )"

Just like to highlight that you can take advantage of the fact that Excel can be treated as a database in ADO.NET too. The advantage of using these approaches is that your not coupling your application to Excel as you do using automation.

Treating separate worksheets as relational data in Excel.

A request on the MSDN VBA forum got me thinking. The problem was this; in Excel there are two ADO recordsets, (a) and (b), populated from SQL Server both containing a different list of ID's. How do you find the items that appear in (a) but not (b) and vice versa.

My inital thought was write a stored procedure and return the actual required data from the server rather doing any local processing however this wasn't possible, so how do you do it locally. As far as I know there is no way to create a relational link between recordsets and no way to then run an SQL query over the recordsets. So another option would have been to drop the recordsets into an Access database and do a local query, but the recordset were in Excel and it felt like a hassle bringing in Access to do one task. It looked like the only choice left was a process of loops and filters, not nice.

Then it dawned on me, if you can query a single worksheet with SQL using ADO as though it was a table, would it be possible to query two worksheets joined together as though they were related tables. Turns out it is possible.

Before I expand on the approach and post the code there are some inital steps to take into consideration. Since each worksheet acts like a table the first thing to do is to drop each recordset into seperate worksheets, which can be done using the Range.CopyFromRecordset() method. i.e. MySheet.Range("A1").CopyFromRecordset MyRecordset.

Another step you need to do is save the workbook once the data has been populated. The connection string used to open Excel as a database only works from a file and not from a in memory workbook. So thats an important step to take.

I should point out here that a header row in each worksheet could be used to define the fields used in the SQL statements however it's not necessary. To save an additional step of placing a header row you can specify F1, F2, F3 for the field names in the SQL statements

So presume that the steps above have been done and I have a spreadsheet that contains three worksheets. A sheet called Recordset1 contains the data dumped from an in-memory recordset, Recordset2 contains the data dumped from the other recordset, neither of worksheets contain a header row as the data for each recordet is dumped to cell A1, and the worksheet has been saved. 

    'connection object needed to connect to Excel
    Dim adoConnection As New ADODB.Connection

    'connection string to Excel/Access
    adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWorkbook & ";Extended Properties=""Excel 8.0;HDR=Yes;""" 

    adoConnection.Open
   
    Dim adoCommand As New ADODB.Command
    adoCommand.CommandType = adCmdText
    adoCommand.ActiveConnection = adoConnection
   
    'unmatch query between recordset1 and recordset2, i.e. find all ID's in recordset1 that aren't in recordset2
    adoCommand.CommandText = "SELECT Recordset1$.F1 FROM [Recordset1$] LEFT JOIN [Recordset2$] ON Recordset1$.F1 = Recordset2$.F1 WHERE (((Recordset2$.F1) Is Null));"
    Dim adoInRst1NotRs2 As ADODB.Recordset
    Set adoInRst1NotRs2 = adoCommand.Execute
   
    'unmatch query between recordset2 and recordset1, i.e. find all ID's in recordset2 that aren't in recordset1
    adoCommand.CommandText = "SELECT Recordset2$.F1 FROM [Recordset2$] LEFT JOIN [Recordset1$] ON Recordset2$.F1 = Recordset1$.F1 WHERE (((Recordset1$.F1) Is Null));"
    Dim adoInRst2NotRs1 As ADODB.Recordset
    Set adoInRst2NotRs1 = adoCommand.Execute
   
    'dump results to another worksheet to test that the approach works
    Worksheets("Actual Results").Range("A1").CopyFromRecordset adoInRst1NotRs2
    Worksheets("Actual Results").Range("B1").CopyFromRecordset adoInRst2NotRs1
   
    'close the connection
    adoConnection.Close

Basically whats happening is an adoConnection is set up to the workbook and two non matching queries are ran. It's the LEFT JOIN of the queries that makes the relationships between the sheets work. The end result is still two recordsets but at least they only contain the information thats required.

*Many thanks to Keith Turnerk for letting me know the following, A small suggestion to correct a potential error in the above code...because you have no header row the connection string needs to read HDR=No rather than HDR=Yes. Otherwise the first value in Recordset1 & 2 are ignored in the queries*

.NET v3.0 (WinFX)

About maybe a year ago I started to read about the then up and coming technologies of Microsoft's new Windows Vista operating system but I never really gave them that much attention due to the fact I thought they were only going to be part of Windows Vista. With the money businesses invested in moving from NT to XP I really didn't see Vista being rolled out as a replacement for XP Professional.

A few weeks back though I read another article that said the technologies of Vista (code named Indigo, Avalon, etc) had been packaged up and released under the new term.NET v3.0. I made a mental note to return and have a better read about what .NET v3.0 really was all about and today I did that, partly because I found this article on MSDN.

Introducing the .NET Framework 3.0

So what did I find out... well I tell you one of the most nicest things I found out about .NET v3.0, and I like this a lot, is the ability to define and create user interfaces using an XML language called eXtensible Application Markup Language (XAML). I think thats a really really nice thing to be able to do, and heres why....

First of all it basically makes Windows applications compatible with the DOM, so just as an HTML web page can be queried using methods like GetElementByID so can the user interface of a Windows application. This means that code which needs to access both a Windows and Web front end won't require that much alteration as both front ends are accessible in the same way.

But thats not really the thing that stood out the most. What I liked the most about XAML is since XAML is just an XML language then it can be changed using DOM, XSLT and/or XQuery. My programming studies was heavy involved with Artifical Intelligence and part of that covered the idea of intelligent user interfaces.

These are interfaces which are capable of changing themselves to suit the user needs. So for example, if the user consistantly uses a series of commands all the time then the user interface would be intelligent enough to alter itself so that the commands would be combined and perhaps placed under a new toolbar button. I thought this idea was great but I could never work out how you could code changes to compiled forms stored in executables or DLL's. With XAML, without knowing the full facts, it now looks possible to write code that changes forms dynamically. Nice!

There are other nice benefits of defining forms using XML but there is something else I want to cover; Windows Workflow Foundation.

The theory behind this is developed software has a certain flow of activities, like a flow chart, like a use case. Do A, then do B and if the result of C is D then do E. Currently developers not only need to build the tasks A, B, C, D and E into their software but they also need to build the flow i.e. if the result of C is D then E, and so on. The idea behind Workflow Foundation is to seperate out the code that performs the flow process and let a seperate engine do it. So for example rather than writing the code that controls the flow why not draw a flowchart that tells the engine the flow through the various tasks with in the software. So not only are you documenting the design of the software by doing so your creating the software.

I might not be explaining that fully so please read the Introduction article when you get time as it has all the diagrams you need and much better explanations of all the new technologies of .NET v3.0. It's also worth while to start looking into these new technologies because Microsoft has decided to use them in their own software, Office 2007, Share Point Services and BizTalk Server are all going to be developed to use these new WinFx technologies. So worth a read.

If your looking to get a hold of .NET v3.0 here is the link. http://msdn.microsoft.com/winfx/downloads/products/golive/resources/