Attribute VB_Name = "VBASub"
'
' search in the worksheet structure
'
Option Explicit

' --- search for a row using single column as a key ---
Public Function Search_iRow(sh As Worksheet, icol As Long, key As String, start As Long) As Long
  Dim r As Long
  Dim nrows As Long
  nrows = sh.Cells(sh.Rows.Count, icol).End(xlUp).row
  For r = start To nrows
    If (sh.Cells(r, icol).Value = key) Then
      GoTo Found
    End If
  Next r
  r = -1
Found:
  Search_iRow = r
End Function

' --- search for a row using two columns as a key ---
Public Function Search_iRow2(sh As Worksheet, _
  icol1 As Long, key1 As String, _
  icol2 As Long, key2 As String, start As Long) As Long
  Dim r As Long
  Dim nrows As Long
  nrows = sh.Cells(sh.Rows.Count, icol1).End(xlUp).row
  For r = start To nrows
    If (sh.Cells(r, icol1).Value = key1) And (sh.Cells(r, icol2).Value = key2) Then
      GoTo Found
    End If
  Next r
  r = -1
Found:
  Search_iRow2 = r
End Function

' --- search for a station, returns location info ---
' * return value is Variant containing an Array of (sheet name, latitude, longitude, height).
' * each element is string as used in the sheet (no conversion is done)
' * assumes column names ("Latitude", "Longitude", "Ha", "Hp", "Hha") are given in 1st row.
'
Function StationLookup(stnid As String, sheetname As String) As Variant
  Dim sh As Worksheet
  Set sh = ThisWorkbook.Sheets(sheetname)
  Dim irow As Long
  irow = Search_iRow(sh, icol:=1, key:=stnid, start:=2)
  If irow < 0 Then
    StationLookup = Array(sheetname, "", "", "", "")
    Exit Function
  End If
  Dim laloh(0 To 3) As String, icol As Long, ncols As Long
  Erase laloh
  ncols = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column
  For icol = 1 To ncols
    Dim cell As String
    cell = sh.Cells(irow, icol).Value
    Select Case (sh.Cells(1, icol).Value)
    Case "Latitude"
      laloh(0) = cell
    Case "Longitude"
      laloh(1) = cell
    Case "Ha"
      laloh(2) = cell
    Case "Hp"
      laloh(2) = cell
    Case "Hha"
      If (laloh(2) <> "") Then laloh(2) = cell
    Case "StationName"
      laloh(3) = cell
    End Select
  Next icol
  StationLookup = Array(sheetname, laloh(0), laloh(1), laloh(2), laloh(3))
End Function

' --- search for runtime option written in sheet "Options" ---
Function getopt(key As String) As String
  Dim sh As Worksheet
  Set sh = ThisWorkbook.Sheets("Options")
  Dim i As Long
  i = Search_iRow(sh, 1, key, 2)
  If (i > 0) Then
    getopt = sh.Cells(i, 2).Value
  Else
    getopt = ""
  End If
End Function