Attribute VB_Name = "VBAMnt"
Option Explicit

' generate source code of Module3
Sub importXslAsBasicFunction()
  Dim xslname As String
  xslname = "raw2gmd"
  Dim infnam As String, outfnam As String
  infnam = ThisWorkbook.path + "\" + xslname + ".xsl"
  outfnam = ThisWorkbook.path + "\" + xslname + ".bas"
  Dim ifp As Integer, ofp As Integer
  ifp = FreeFile
  Open infnam For Input Access Read As #ifp
  ofp = FreeFile
  Open outfnam For Output Access Write As #ofp
  Dim funcname As String
  funcname = "sXSL" + xslname
  Print #ofp, "Function " + funcname + "() As String"
  Print #ofp, "Dim r as string"
  Print #ofp, "r = """""
  Dim line As String
  Do While Not EOF(ifp)
    Line Input #ifp, line
    Dim oline As String
    oline = "r = r + """
    Do While InStr(line, """") > 0
      oline = oline + Left$(line, InStr(line, """")) + """"
      line = Mid$(line, InStr(line, """") + 1)
    Loop
    oline = oline + line + """ + Chr$(13) + Chr$(10)"
    Print #ofp, oline
  Loop
  Print #ofp, funcname + " = r"
  Print #ofp, "End Function"
  Close #ifp
  Close #ofp
End Sub

' fix table CCCC
Sub REF_VOLA()
  Dim offset As Long
  offset = 0
  Dim vola As Worksheet
  Dim cccc As Worksheet
  Set vola = ThisWorkbook.Sheets("VOLA")
  Set cccc = ThisWorkbook.Sheets("CCCC")
  Dim nv As Long, ic As Long, nc As Long, iv As Long
  nc = cccc.Cells(cccc.Rows.Count, 1).End(xlUp).row
  ThisWorkbook.Colors(50) = RGB(128, 128, 128)
  For ic = 2 To nc
    Dim indexNbr As String
    indexNbr = cccc.Cells(ic, 2).Value
    Application.StatusBar = "fixing CCCC by WMO index " & indexNbr
    If (indexNbr <> "-----") Then
      iv = Search_iRow(vola, 1, indexNbr, 2)
    Else
      iv = -1
    End If
    Dim jc As Long
    If (iv > -1) Then
      cccc.Cells(ic, 3 + offset).Value = vola.Cells(iv, 4).Value ' StationName
      cccc.Cells(ic, 4 + offset).Value = vola.Cells(iv, 3).Value ' Country
      cccc.Cells(ic, 5 + offset).Value = vola.Cells(iv, 5).Value ' Latitude
      cccc.Cells(ic, 6 + offset).Value = vola.Cells(iv, 6).Value ' Longitude
      Dim ha As String
      ha = vola.Cells(iv, 9).Value
      If (ha = "") Then
        ha = vola.Cells(iv, 7).Value
      End If
      If (ha <> "") Then
        cccc.Cells(ic, 7 + offset).Value = ha
      End If
      For jc = 3 To 7
        cccc.Cells(ic, jc).Font.ColorIndex = 50
        cccc.Cells(ic, jc).Font.Italic = True
      Next jc
    Else
      Dim hyphen As Long, coord As String
      For jc = 5 To 6
        coord = cccc.Cells(ic, jc).Value
        hyphen = InStr(coord, "-")
        If (hyphen > 0) Then
          Mid(coord, hyphen, 1) = " "
          cccc.Cells(ic, jc).Value = coord
        End If
      Next jc
    End If
  Next ic
End Sub