Parsing INI Files With VBA

INI files are still widely used to enable users to configure applications even though their use is deprecated in the Windows world. The recommended method is to write user configuration data to the Windows registry.

From VBA Word, the INI files (and Windows registry) can be accessed and modified through the VBA Object library function System.PrivateProfileString(). In VBA Excel, this library call is not available. To read INI files, one option is to employ the Windows API function GetPrivateProfileString Lib "kernel32". The other option is to write your own INI parser, which is what I did the other day.

Apart from the normal comments (comments start with # in this version of INI), the script also tolerates end-of-line comments.

The script also enforces unique section names by generating an error if a duplicate section name is detected. This is quite important as the script stores the INI data as key-value pairs, and the way it ensures unique key names is by prefixing section names to key names, with a dot separating the two.

The script also gives users the option of loading the generated data file to Teradata using the FastLoad utility.

Sample ini file:

# Author: Ram Limbu
# Date: 2009/11/28
#
#
#

[FILE_SETTINGS] # start of file settings

DestDir=C:\Documents and Settings\Ram_2\My Documents\Excel Projects\Config # don't end this line with slash
Delimiter=,
DestFileName=ErrorFiles.csv

[DATABASE_SETTINGS] # start of database settings

InsertData=Yes # Acceptable values: Yes or No
DbName=IPSHARE
TblName=RL_PP_Hist
DbUsername=cncra/RLIMBU
DbPw=secret

Option Explicit


Const MsgTitle As String = "Wooden Horse Pty Ltd"

Sub Batch_Err_Parser()
    
    '//define constants
    Const OldDelim As String = "|"
    Const NewDefDelim As String = ","
    Const Colon As String = ":"
    Const BackSlash As String = "\"
    
    '//define vars
    Dim LastRow As Long
    Dim Rec As String
    Dim TimeStamp As String
    Dim Msn As String
    Dim RejCode As String
    Dim RejMsg As String
    Dim FileName As String
    Dim DestDir As String
    Dim DestFile As String
    Dim NewDelim As String
    Dim Identifier As String
    Dim OrderNotes As String
    Dim Status As String
    Dim t1 As Double
    Dim t2 As Double
    Dim Rng As Range
    Dim C As Range
    Dim Arr As Variant
    Dim ColonPos1 As Integer
    Dim ColonPos2 As Integer
    Dim TotPasses As Integer
    Dim TotFails As Integer
    Dim RecCtr As Long
    Dim DatExists As Boolean
    Dim fs As Object
    Dim ts As Object
    Dim ws As Worksheet
    Dim INI_COL As New Collection
   
    
    On Error GoTo Err_Rtn:
    
    t1 = Timer
    
    '// call initialization rtn
    InitRtn
    
    '//ensure that it is a batch error rpt
    If Mid(Cells(1, 1), 3, 8) <> "RPT_OPOM" Then
        If Not MsgBox("Is this Marketing Batch Error report?", vbYesNo + vbQuestion, MsgTitle) Then
            Exit Sub
        End If
    End If

    Set ws = ActiveSheet
      
    '// find the last row
    LastRow = ws.Cells(65536, 1).End(xlUp).Row
    
    '// if there is no data, exit
    If LastRow = 1 Then
        MsgBox "Goodbye!", vbExclamation, MsgTitle
        Exit Sub
    End If
    
    '// get ini data
    If Rtn_Config_Col(INI_COL) <> 0 Then  '// 0 = successful func call
        Exit Sub
    End If
    
    '// read new delim from ini settings
    NewDelim = INI_COL.Item("[FILE_SETTINGS].Delimiter")
    If Trim(NewDelim) = "" Then
        NewDelim = NewDefDelim
    End If
    
    '// read identifier
    Identifier = INI_COL.Item("[FILE_SETTINGS].Identifier")
    
    '// if headers are turned on, write them to data file
    If UCase(INI_COL.Item("[FILE_SETTINGS].Headers")) = "ON" Then
        Rec = "service_no" & NewDelim & "status" & NewDelim & "time_stamp" & NewDelim & _
                "rej_code" & NewDelim & "rej_descript" & NewDelim & "identifier" & vbCrLf
    End If
    
    '// create the data range
    Set Rng = ws.Cells(1, 1).Resize(LastRow)
    
    '// loop the cells and parse the records
    Application.DisplayStatusBar = True
    
    For Each C In Rng
        Status = Trim(Left(C, 4))
        If Status = "PASS" Or Status = "FAIL" Then
            RecCtr = RecCtr + 1
            Application.StatusBar = "Processing record: " & RecCtr
            If Status = "PASS" Then
                TotPasses = TotPasses + 1
            Else
                TotFails = TotFails + 1
            End If
            If Status = "FAIL" Or (Status = "PASS" And UCase(INI_COL.Item("[FILE_SETTINGS].FailOnly")) <> "YES") Then
                Arr = Split(C, OldDelim)
                Msn = Right(Arr(4), 10)
                ColonPos1 = InStr(1, Arr(6), Colon, vbTextCompare) ' find 1st position of colon
                ColonPos2 = InStr(ColonPos1 + 1, Arr(6), Colon, vbTextCompare) ' find 2nd position of colon
                RejCode = Trim(Mid(Arr(6), ColonPos1 + 1, ColonPos2 - ColonPos1 - 1)) ' extract rej code
                RejMsg = Trim(Mid(Arr(6), ColonPos2 + 1, Len(Arr(6)))) ' extract rejection description
                OrderNotes = Trim(Arr(7)) 'extract order notes
                OrderNotes = Right(OrderNotes, Len(OrderNotes) - InStr(OrderNotes, Colon)) 'extract only aft colon
                RejMsg = RejMsg & " " & OrderNotes
                RejMsg = Left(RejMsg, 298) ' ensure that rejmsg is not more than 298 characters as db vartext field is 300 only
                Rec = Rec & Msn & NewDelim & Arr(0) & NewDelim & TimeStamp & NewDelim & _
                  RejCode & NewDelim & RejMsg & NewDelim & Identifier & vbCrLf
                DatExists = True
            End If
        ElseIf Status = "DATE" Then
            TimeStamp = Right(C, 19)
        ElseIf UCase(Left(C, 15)) = "INPUT FILE NAME" Then
            FileName = Trim(Right(C, Len(C) - 16))
        End If
        DoEvents
    Next C '// end of main loop
    
    '// if error records exist, write them to a file
    If DatExists Then
        Application.StatusBar = "Writing to text file ..."
        '// read dest folder and file details from ini setting collection
        DestDir = INI_COL.Item("[FILE_SETTINGS].DestDir")
        
        '// if there is no slash at the end of destination folder, put one there
        If Right$(DestDir, 1) <> BackSlash Then
            DestDir = DestDir & BackSlash
        End If
        
        DestFile = INI_COL.Item("[FILE_SETTINGS].DestFile")
        If UCase(Trim(DestFile)) = "DEFAULT" Or Trim(DestFile) = "" Then  '// if no filename has been supplied, name it after error file
            If FileName <> "" Then '// ensure error report filename exists
                DestFile = FileName
            Else
                MsgBox "Error: No valid destination file could be found. Exiting ...", vbOKOnly + vbInformation, MsgTitle
                Exit Sub
            End If
        End If
       
        Set fs = CreateObject("Scripting.FileSystemObject")
        If fs.FolderExists(DestDir) Then
            DestFile = DestDir & DestFile  'prepend dest dir
            Set ts = fs.CreateTextFile(DestFile)
            ts.Write Rec
            ts.Close
        Else
            MsgBox DestDir & " doesn't exist or not accessible!", vbOKOnly + vbCritical, MsgTitle
            Exit Sub
        End If
        
        'do clean up
        Set fs = Nothing
        Set ts = Nothing
        Set ws = Nothing
        
        'if the user has elected to write records to db, fastload them
        If UCase(INI_COL.Item("[DATABASE_SETTINGS].InsertData")) = "YES" Then
            '//ensure all other database setting values are provided
            With INI_COL
                If .Item("[DATABASE_SETTINGS].DbName") <> "" And .Item("[DATABASE_SETTINGS].TblName") <> "" & _
                    .Item("[DATABASE_SETTINGS].DbUsername") <> "" And .Item("[DATABASE_SETTINGS].DbPw") <> "" Then
                    DbRtn Db:=.Item("[DATABASE_SETTINGS].DbName"), Tbl:=.Item("[DATABASE_SETTINGS].TblName"), _
                        UsrNm:=.Item("[DATABASE_SETTINGS].DbUsername"), Pw:=.Item("[DATABASE_SETTINGS].DbPw"), _
                        InputFile:=DestFile, TargetDir:=DestDir, Start:=IIf(UCase(.Item("[FILE_SETTINGS].Headers")) = "ON", 2, 1), _
                            Delim:=NewDelim
                Else
                    MsgBox "Since you didn't provide all required database settings, records were not " & vbCrLf _
                        & "written to database!", vbInformation + vbOKOnly, MsgTitle
                End If
            End With
            
        End If
        
        t2 = Timer
        Application.StatusBar = "Done!"
        MsgBox "Finito! Time taken = " & Format(t2 - t1, "##0.000") & " seconds" & vbCrLf _
            & "Total Passes = " & TotPasses & vbCrLf _
            & "Total Fails = " & TotFails & vbCrLf _
            & "Total Records = " & TotPasses + TotFails, vbInformation + vbOKOnly, MsgTitle
        
    End If
    Application.StatusBar = ""
    Exit Sub
Err_Rtn:
    Application.StatusBar = ""
    MsgBox Err.Description, vbInformation + vbOKOnly, MsgTitle
End Sub

Private Function Rtn_Config_Col(ByRef INI_COL As Collection) As Integer
    Dim INI_File As String
    Dim Rec As String
    Dim SecName As String
    Dim Kee As String
    Dim val As String
    Dim SubStrPos As Integer
    Dim fs As Object
    Dim f As Object
    Dim flds As Object
    Dim SecCol As New Collection

    
    '// define some constants to make script more userfrieldly
    Const READ_ONLY As Integer = 1
    Const CommStr As String = "#"
    Const Eq As String = "="
    Const SecStart As String = "["
    Const SecEnd As String = "]"
    Const MinValLen As String = 3
    Const Blank As String = ""
    
    Set fs = CreateObject("WScript.Shell")
    Set flds = fs.SpecialFolders
    INI_File = flds("mydocuments") & "\Batch_Errors\Batch_Errors.ini"
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    '//ensure ini file exists
    If Not fs.FileExists(INI_File) Then
        MsgBox "My Documements\Batch_Errors folder " & vbCrLf & _
        "or INI file does not exist! Exiting ...", vbCritical + vbOKOnly, MsgTitle
        Rtn_Config_Col = -1
    End If
    
    Set f = fs.OpenTextFile(INI_File, READ_ONLY)
    
    '//read the ini file
    
    Do Until f.AtEndOfStream
    
        Rec = Trim(f.ReadLine)
        
        '// ignore comments and blank lines
        If Left(Rec, 1) <> CommStr And Rec <> Blank Then
            '// check to see if there are comments at end of key-value pair
            '// if so, take them out of current record
            SubStrPos = InStr(2, Rec, CommStr)
            
            If SubStrPos > 0 Then '// end of line comments exists
                Rec = Trim(Left(Rec, SubStrPos - 1)) '// eliminate comments
            End If
                
            '//check for section name
            If Left(Rec, 1) = SecStart Then
                If Right(Rec, 1) = SecEnd And Len(Rec) >= MinValLen Then '// ensure it is valid section name
                    'ensure that there are no duplicate section names
                    SecName = Rec
                    On Error Resume Next
                    SecCol.Add SecName, SecName
                    If Err <> 0 Then
                        MsgBox "Error: The INI file has duplicate section names! Exiting ...", vbCritical + vbOKOnly, MsgTitle
                        Rtn_Config_Col = -1
                    End If
                End If
            Else
                '// check if it is key-value pair
                SubStrPos = InStr(2, Rec, Eq)
                If SubStrPos > 0 And Len(Rec) >= MinValLen Then
                    '// insert key-value pair into collection
                    '// prepend section name to key name to make it unique
                    Kee = Left(Rec, SubStrPos - 1)
                    val = Trim(CleanData(Right(Rec, Len(Rec) - SubStrPos)))
                    Kee = Trim(CleanData(SecName & "." & Kee))
                    INI_COL.Add val, CStr(Kee)
                End If
            End If
        End If
    Loop
    f.Close
    Set fs = Nothing
    Set flds = Nothing
    Rtn_Config_Col = 0
End Function

Private Function CleanData(Data As String) As String
    Dim i As Integer
    For i = 0 To 31
        While InStr(1, Data, Chr(i)) > 0
            Data = Replace(Data, Chr(i), " ")
        Wend
    Next i
    
    For i = 127 To 255
        While InStr(1, Data, Chr(i)) > 0
            Data = Replace(Data, Chr(i), " ")
        Wend
    Next i
    CleanData = Data
End Function

Private Sub InitRtn()
    '// in this routine, ensure that user has
    '// Batch_Errors.ini file within Batch_Errors in My Documents folder. If not, create one
    '// and inform user
    Dim fso As Object
    Dim fws As Object
    Dim sfld As Object
    Dim Rec As String
    Dim txt_file_o As Object
       
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fws = CreateObject("WScript.Shell")
    Set sfld = fws.SpecialFolders
    If Not fso.FolderExists(sfld("mydocuments") & "\Batch_Errors") Then
        '// create Batch_Errors folder
        Application.StatusBar = "Creating " & sfld("mydocuments") & "\Batch_Errors"
        fso.CreateFolder (sfld("mydocuments") & "\Batch_Errors")
        Application.StatusBar = "Creating .ini file ..."
        
        '// create ini file
        Rec = "# This .ini file was created by batch error parser on " & Format(Now, "YYYY-MM-DD HH:MM:SS") & vbCrLf & "#" & vbCrLf
        Rec = Rec & "# Comments start with the hash symbol (#), just like this line" & vbCrLf & "#" & vbCrLf & "#" & vbCrLf
        Rec = Rec & "# start of file settings" & vbCrLf & vbCrLf
        Rec = Rec & "[FILE_SETTINGS]" & vbCrLf & vbCrLf
        Rec = Rec & "DestDir=" & sfld("mydocuments") & "\Batch_Errors" & vbCrLf
        Rec = Rec & "Delimiter=,             # leaving it blank will choose comma as default delimiter" & vbCrLf
        Rec = Rec & "DestFile=default        # the destination file name defaults to error report name" & vbCrLf
        Rec = Rec & "Headers=On              # On or Off" & vbCrLf
        Rec = Rec & "Identifier=             # write something descriptive to identify service numbers or leave blank" & vbCrLf
        Rec = Rec & "FailOnly=No             # Yes or No. If Yes, only services that returned rejections will be extracted" & vbCrLf & vbCrLf
        Rec = Rec & "# start of database settings" & vbCrLf & vbCrLf
        Rec = Rec & "# If you want the error report data to insert into a table, then provide appropriate values " & vbCrLf
        Rec = Rec & "# for the following database settings, starting with 'InsertData'." & vbCrLf
        Rec = Rec & "# *** Please, note: you need to create the required table as follows" & vbCrLf
        Rec = Rec & "# before attempting to load data into it via this utility!" & vbCrLf
        Rec = Rec & "#" & String(50, "-") & vbCrLf
        Rec = Rec & "# CREATE SET TABLE DB_FOO.TBL_BAR (" & vbCrLf
        Rec = Rec & "#      service_no VARCHAR(20)" & vbCrLf
        Rec = Rec & "#     ,status VARCHAR(10)" & vbCrLf
        Rec = Rec & "#     ,tm_stamp VARCHAR(30)" & vbCrLf
        Rec = Rec & "#     ,rej_code VARCHAR(10)" & vbCrLf
        Rec = Rec & "#     ,rej_descript VARCHAR(300)" & vbCrLf
        Rec = Rec & "#     ,identifier VARCHAR(50)" & vbCrLf
        Rec = Rec & "#     ,open_dt DATE" & vbCrLf
        Rec = Rec & "#     ,close_dt VARCHAR(30)" & vbCrLf
        Rec = Rec & "# ) PRIMARY INDEX (service_no);" & vbCrLf
        Rec = Rec & "#" & String(50, "-") & vbCrLf & vbCrLf
        Rec = Rec & "# Since the data get FastLoaded, you need to have FastLoad utility installed on your machine." & vbCrLf & vbCrLf
        Rec = Rec & "[DATABASE_SETTINGS]" & vbCrLf & vbCrLf
        Rec = Rec & "InsertData=No          # Yes or No. To insert data into database, write 'Yes'" & vbCrLf
        Rec = Rec & "DbName=xx_my_db         # replace 'xx_my_db' with your database name, i.e. IPSHARE" & vbCrLf
        Rec = Rec & "TblName=xx_my_tbl       # replace 'xx_my_tbl' with your table name" & vbCrLf
        Rec = Rec & "DbUsername=cncra/jdoe   # replace 'cncra/jdoe' with your data source name/username" & vbCrLf
        Rec = Rec & "DbPw=secret             # replace 'secret' with your password"
        
        Set txt_file_o = fso.CreateTextFile(sfld("mydocuments") & "\Batch_Errors\Batch_Errors.ini")
        txt_file_o.Write Rec
        txt_file_o.Close
        Set txt_file_o = Nothing
        Application.StatusBar = ""
    End If
    
End Sub

Sub DbRtn(Db As String, Tbl As String, UsrNm As String, Pw As String, InputFile As String, TargetDir As String, Start As Integer, Delim As String)
    '// this routine fastloads error records into named tables
    
    Dim flScript As String
    Dim fso As Object
    Dim fo As Object
    Dim ScriptInput As String
    Dim ScriptOutput As String
    Dim ShStatus As Double
    Dim BatOut As String
    
    
    '// start creating fastload script
    
    flScript = "/* " & String(84, "+") & " */" & vbCrLf
    flScript = flScript & "/* FASTLOAD SCRIPT CREATED BY BATCH ERROR PARSER  */" & vbCrLf
    flScript = flScript & "/* ON " & Format(Now, "YYYY-MM-DD hh:mm:ss") & " TO FASTLOAD BATCH ERROR DATA  */" & vbCrLf
    flScript = flScript & "/* " & String(84, "+") & " */" & vbCrLf
    flScript = flScript & vbCrLf & "/* Setup FastLoad parameters  */" & vbCrLf & vbCrLf
    flScript = flScript & "SESSIONS 50;" & vbCrLf
    flScript = flScript & "ERRLIMIT 50;" & vbCrLf
    flScript = flScript & "LOGON " & UsrNm & ", " & Pw & ";" & vbCrLf
    flScript = flScript & "SHOW VERSIONS;" & vbCrLf
    flScript = flScript & "RECORD " & Start & ";" & vbCrLf
    flScript = flScript & "SET RECORD VARTEXT """ & Delim & """ DISPLAY_ERRORS NOSTOP;" & vbCrLf & vbCrLf
    flScript = flScript & "/* Define text file layout and input file */" & vbCrLf & vbCrLf
    flScript = flScript & "DEFINE service_number (VARCHAR(20))" & vbCrLf
    flScript = flScript & "     ,status (VARCHAR(10))" & vbCrLf
    flScript = flScript & "     ,tm_stamp (VARCHAR(30))" & vbCrLf
    flScript = flScript & "     ,rej_code (VARCHAR(10))" & vbCrLf
    flScript = flScript & "     ,rej_descript (VARCHAR(300))" & vbCrLf
    flScript = flScript & "     ,identifier (VARCHAR(50)) " & vbCrLf
    flScript = flScript & "FILE=" & InputFile & ";" & vbCrLf & vbCrLf
    flScript = flScript & "SHOW;" & vbCrLf & vbCrLf
    flScript = flScript & "BEGIN LOADING " & Db & "." & Tbl & vbCrLf
    flScript = flScript & " ERRORFILES " & Db & ".Batch_Err1, " & Db & ".Batch_Err2" & vbCrLf
    flScript = flScript & " CHECKPOINT 500;" & vbCrLf & vbCrLf
    flScript = flScript & "INSERT INTO " & Db & "." & Tbl & " VALUES" & vbCrLf
    flScript = flScript & "  ( :service_number " & vbCrLf
    flScript = flScript & "    ,:status " & vbCrLf
    flScript = flScript & "    ,:tm_stamp " & vbCrLf
    flScript = flScript & "    ,:rej_code " & vbCrLf
    flScript = flScript & "    ,:rej_descript " & vbCrLf
    flScript = flScript & "    ,:identifier " & vbCrLf
    flScript = flScript & "    ,CURRENT_DATE " & vbCrLf
    flScript = flScript & "    ,'2899-12-31'); " & vbCrLf & vbCrLf
    flScript = flScript & "END LOADING;" & vbCrLf
    flScript = flScript & "LOGOFF;"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    ScriptInput = TargetDir & "FastLoadThis" & ".src"
    ScriptOutput = TargetDir & "FastLoad_" & Format(Date, "YYYY_MM_DD") & ".log"
    BatOut = TargetDir & "Batch_Errors.bat"
    
    On Error Resume Next
        Kill BatOut
        Kill ScriptInput
    On Error GoTo 0
    
    Set fo = fso.CreateTextFile(ScriptInput)
    fo.Write flScript
    fo.Close
    
    '//create bat file
    flScript = "FastLoad < """ & ScriptInput & """ > """ & ScriptOutput & """ 2>&1"
    Set fo = fso.CreateTextFile(BatOut)
    fo.Write flScript
    fo.Close
    
    'do clean-up
    Set fso = Nothing
    Set fo = Nothing
    
    '//start fastload
    Call Shell(BatOut)

End Sub

Deleting Rows in Excel with VBA

Deleting and shifting Rows up in VBA is a straightforward task that nevertheless hides a few traps. Say, any Row that has a numeric value in Column 'A' needs to be deleted and the Rows below shifted up. A VBA solution such as the one that follows does not work.

Option Explicit 

Sub DeleteRows()
    Dim LastRow, Ctr As Long  

    '// find the last Row with data
    LastRow = Cells(65536, 1).End(xlUp).Row

    '// loop through the Rows, deleteing Rows that have a
    '// numberic value in Column 'A' and shifting Rows up   

    For Ctr = 1 To LastRow
        If IsNumeric(Cells(Ctr, 1)) Then
            Cells(Ctr, 1).EntireRow.Delete Shift:=xlShiftUp
        End If
    Next Ctr
End Sub 

The problem is that if two or more consecutive Rows have numeric values in Column 'A', not all the Rows that need to be deleted get deleted. Why? Say that Rows 3 and 4 have numeric values in Column 'A'. When Row 3 is deleted, Row 4 moves up to take its place, in effect becoming Row 3. Meanwhile, the loop counter gets incremented in the next pass from 3 to 4, with the code scanning the value in Cell A4 but inadvertently skipping the value in Cell A3. 

An elegant solution to this problem is to start looping at the last Row with data in the Worksheet and move up one Row at a time, deleting and shifting Rows up when the required condition is satisfied. Here is the solution: 

Option Explicit 

Sub DeleteRows()
    Dim LastRow, Ctr As Long   

    '// find the last row with data
    LastRow = Cells(65536, 1).End(xlUp).Row   

    '// loop through the Rows, deleteing Rows that have a
    '// numberic value in Column 'A' and shifting Rows up    

    For Ctr = LastRow To 1 Step -1
        If IsNumeric(Cells(Ctr, 1)) Then
            Cells(Ctr, 1).EntireRow.Delete Shift:=xlShiftUp
        End If
    Next Ctr 
End Sub

The Joy of Running

My tongue-in-cheek remark to a friend after reading Christopher McDougall's Born To Run was this: "Marathons are for wimps". Now, I have yet to run a marathon and I have no illusions about the dedication, hard work, toughness and tenacity required to complete a marathon but after reading about 'ultra freaks' running up and down the 10,000+ feet high Colorado mountains for 100 miles, or plodding 135 miles across the floor of the Death Valley in California in 135 degrees heat, one can be forgiven for thinking that marathons are a stroll in the park.

Born To Run opens with an all too familiar story. A beefy former war correspondent who is built like someone that nature intended 'to take a bullet for the President' rather than pound down the pavement, Mcdougall is doing the rounds of sports shrinks to treat his dodgy feet. In the winter of 2003, he is in Mexico chasing a missing pop star for The New York Times Magazine when he stumbles across a picture of a Jesus like figure running down a rockslide. Welcome to the secret world of Tarahumara Indians, the greatest ultra runners the world has never heard about.

Living like invisible ghosts in the shadowy recesses of the Copper Canyons of Mexico, the peaceful Tarahumara tribes have for centuries outrun their Indian and European tormentors alike just to survive. What is the secret behind their superhuman prowess that enables them to run for hundreds of miles without rest? This is the question that persuades Mcdougall to abandon his celebrity hunt and start another type of hunt, the hunt for a mythical gringo named Caballo Blanco, the 'white horse' who lives with the Tarahumara and runs like one.

Before the book finishes, there is a gripping duel over 100 miles of punishing Colorado trails between a Community College teacher named Ann Tracy and top Tarahumara runners who have been coaxed to participate in the Leadville 100 ultramarathon with promises of corn bags for their villages. I felt the description of this race alone gave all the bang for my bucks.

The book climaxes with a 50 mile foot race in the treacherous Tarahumara territory between the cream of the Tarahumara tribe and the superstars of the North American - which is to say, the world's - ultramarathon scene.

And the secret to the Tarahumara success? In a sentence or three, it is this: All humans are hardwired to run. As kids, all of us run with joyful abandon. As we grow up, we lose this sense of playfulness and sheer joy in running. The Tarahumara keep this playful spirit alive into adulthood, and they have over the millenia woven the art of running into rituals that govern their daily lives. Also, they do not buy expensive, gel-filled Nike running shoes that seem to do more harm than good.

Shantaram: Crime and Redemption

If one has to summarise in three words Gregory David Roberts' 2004 debut novel Shantaram, then it has to be this: "Dostoevsky meets Bollywood". 

Or, that is what I thought after yet another scene in the novel in which Bombay's (when the story unfolds, the bustling Indian island city is about another couple of decades away from being re-named Mumbai in response to a Hindu nationalist popular groundswell) chic set banter and indulge themselves in one of the city's fashionable tourist haunts by the sea. 

And what a chic set it is. Roberts, who tells the story in first person, drinks, smokes, eats and philosophises with local Indian journalists, film producers, mafia enforcers, Iranian army deserters, washed-up Palestinian fighters, Pakistani spies, European outcasts, and an Afghan mafia boss who claims to have found the rational basis of morality.   

And just like Bollywood movies that the author claims to love, the novel packs a lot of action, and enough surprises to satisfy even the most demanding fan of Bollywood suspense thrillers. However, the book is much more than a pulp version of Bollywood. 

At the heart of the narrative is that eternal quest for meaning, love and redemption that inform all great works of art. Roberts, who escaped from a maximum security jail in Victoria while serving a sentence for armed robbery, finds all of these and more in the most unlikely of places and persons. 

Towards the very end of the 933 pages long book, Roberts declares: "The truth is that, no matter what kind of game you find yourself in, no matter how good or bad the luck, you can change your life completely with a single thought or a single act of love". 

One can only say "Amen!" to that and recommend the book as a must read.

Muna Madan

Gurkha Nepalese Community (GNC), a Sydney-based Nepalese community organization, is staging Muna Madan as part of its annual Dashain festivities this year. A casual conversation with a dear friend of mine who also happens to be one half of a GNC power couple inspired the following thoughts about the most popular lyrical drama of the youngest Himalayan republic.

• Muna Madan is arguably the greatest work of Nepal's unarguably the greatest poet, Laxmi Prasad Devkota


• A miniature masterpiece, the work partly owes it popular appeal to its use of folk idioms and metres


• The drama centres around the trials and travails of a high-caste migrant worker journeying to Lhasa (Tibet) for work and back


• Apart from the constraints placed by a traditional society on romantic love, the drama also highlights the twin evils of casteism and manpower drain that have impeded Nepal's emergence from the dark shadows of its past


• The most famous line from Muna Madan, spoken by the high-caste protagonist to a "lowly" Bhote who nurses him back to health, is: "Man becomes great not by caste, but by heart"

Removing Excel VBA password

For whatever reason, one sometimes confronts the need to remove Excel VBA project passwords. Here is an easy way to do it. Hopefully, this will one day spare you hours of googling and frustration.

My City2Surf 2009 Performance

My preparations to run this year's City2Surf under 60 minutes were derailed by a combination of flue, nasty weather and good old inertia (the complete list of excuses actually runs longer than the world's biggest fun run!) Anyway, I clocked 69 minutes 9 seconds, improving last year's result by 39 seconds.

The unexpected 'improvement' - after a month of inactivity, my preparation consisted of four comfortable jogs over the week leading to the event - illustrates what I suspect is the exponential nature of the efforts required to improve one's speed. After a certain point, disproportionate inputs seem to require to exact minuscule gains in speed. Conversely, comfortable pace is not necessarily a prelude to a catastrophic loss (or, is that gain?) in time.

In a sense, running, even the fun ones, is all about timing. One has to know when to hold back and when to give all. As my preparation was far from ideal, I checked my impulse to go all out in the first kilometers even as other adrenaline-fueled runners zoomed past me. It was not until after around 10 km that I upped my tempo. When I crossed the finish line, I still had enough left in the tank to run another 5 km. Perhaps, I held myself back a bit too long but this did not prevent me from feeling very satisfied with my performance, especially considering the comfortable pace that improved on last year's result, which was achieved through far more lung-busting efforts.