title: Vbscript - Issues in getting the Excel column Index
body: <p>I am stuck on an issue while creating a simple function that returns the excel column index, given the column name. I am not able to understand why is the script is behaving so weirdly(please see code comments for issues). I know there must be a silly mistake but I am just not able to find it. Will greatly appreciate your help!</p>
  
  <p>The following is the function which was supposed to return the index of the column in an excel sheet:</p>
  
  <pre><code>function fn_findColumnIndex(sheetObj, strColumnName)
      Dim i, j, strTemp, intIndex
      j = sheetObj.usedRange.Columns.Count        'will replace it later with better ways to get column count. This works fine for now when I tried to print the value
      intIndex = 0
      for i = 1 to j step 1
          strTemp = sheetObj.cells(1,j).value     'Targeting the values in the 1st row(i.e, the column names)
          'msgbox strTemp                         'when I uncomment this line, all the values in row 1 are displayed as blank. That's the reason the function always return 0. But why are these values blank?
          if strComp(strTemp,strColumnName,1) = 0 then
              intIndex = j
              Exit For
          end if
      next
      fn_findColumnIndex = intIndex
  end function
  </code></pre>
  
  <p>The following is the piece of code which is calling the above-mentioned function:</p>
  
  <pre><code>set objExcel = createObject("Excel.Application")
  objExcel.visible = false
  objExcel.displayAlerts = false
  strCDPath = "E:\Base\TestDesign\CycleDriver\CycleDriver.xlsx"
  Set objBook = objExcel.WorkBooks.Open(strCDPath)
  set objSheet = objBook.WorkSheets("CycleDriver")
  intRunColumn = fn_findColumnIndex(objSheet,"Run")               'getting the value 0 here. Why?
  intModuleColumn = fn_findColumnIndex(objSheet,"Module")         'getting the value 0 here. Why?
  intTestScriptColumn = fn_findColumnIndex(objSheet,"TestScript") 'getting the value 0 here. Why?
  objBook.close
  objExcel.quit
  </code></pre>
  
  <p>This is how the Sheet looks like:
  <a href="https://i.stack.imgur.com/DWAo3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DWAo3.png" alt="enter image description here"></a></p>