文字列の両端のみスペースを取り除いて文字列を抜き出す

<分類:値抜き取り>
<使用例:文字列( 123456789 ABCDEF )から(123456789 ABCDEF)>
<プログラム例>
<分類:値抜き取り>

Sub 使用例()
   
   Dim s As String

   s = "   123456789   ABCDEF   "
   
   MsgBox (共通_文字列抜出し両端空白削除(s))

End Sub

Function 共通_文字列抜出し両端空白削除(p1)
'p1:対象文字列

   Dim i As Long
   Dim mojisu As Long
   Dim mojista As Long
   Dim mojiend As Long
   
   mojisu = Len(p1)
   
   共通_文字列抜出し両端空白削除 = ""

   For i = 1 To mojisu - 1
       If Mid(p1, i, 1) = " " Or _
          Mid(p1, i, 1) = " " Then
       Else
          mojista = i
          Exit For
       End If
   Next i
  
  
   For i = mojisu To 1 Step -1
       If Mid(p1, i, 1) = " " Or _
          Mid(p1, i, 1) = " " Then
       Else
          mojiend = i
          Exit For
       End If
   Next i
   
    共通_文字列抜出し両端空白削除 = Mid(p1, mojista, mojiend - mojista + 1)
End Function

ワークシートのデータの最終行を取得する

<分類:値取得>
<使用例:①上から下方向へデータの入った連続した行の最終行を取得、また、②Excelの最大行から上方向にデータが存在する行まで調べて最終行を取得 ①と②お互い一致したら正常、不一致だったら、データの行間に隙間があるので異常(最終行取得エラーとして0を出力)>
<注意:サンプルデータを作成するプログラムになっていますので、Sheet1は何もデータがないシートをご用意ください>
<プログラム例>

Sub 使用例()
   Dim max_row As Long
     
  'サンプルデータ作成
   Cells(1, 1) = "2019/9/8"
   Cells(1, 2) = "00:00:00"
   Cells(1, 3) = "28.7"
   
   Cells(2, 1) = "2019/9/8"
   Cells(2, 2) = "01:00:00"
   Cells(2, 3) = "29.0"
   
   Cells(3, 1) = "2019/9/8"
   Cells(3, 2) = "02:00:00"
   Cells(3, 3) = "29.1"
   
   Cells(4, 1) = "2019/9/8"
   Cells(4, 2) = "03:00:00"
   Cells(4, 3) = "29.1"
   
   max_row = 共通_最終行取得("Sheet1")
   Cells(max_row, 1).Select
   
   MsgBox ("最終行は" & max_row & "です")
      
   
End Sub

Function 共通_最終行取得(sheet_name As String)

    Dim maxrow_d As Long
    Dim maxrow_u As Long
    
    maxrow_d = Worksheets(sheet_name).Cells(1, 1).End(xlDown).Row
    maxrow_u = Worksheets(sheet_name).Cells(Rows.Count, 1).End(xlUp).Row
    
    If maxrow_d = maxrow_u Then
       共通_最終行取得 = maxrow_u
    Else
       共通_最終行取得 = 0
       MsgBox ("最終行不一致")
    End If
    
End Function

時間範囲判定応用: A列に日付、B列に時間、C列に値が設定されている履歴データの指定時間範囲内のC列の値の平均値を取得する

<分類:値取得>
<使用例:対象範囲(開始)の日付(2019/8/16)時間(02:00:00)~対象範囲(終了)の日付(2019/8/16)時間(13:00:00)の平均値(30.375)を取得する>
<注意:サンプルデータを作成するプログラムになっていますので、Sheet1は何もデータがないシートをご用意ください>
<プログラム例>

Sub 使用例()
   Dim fdate As Date
   Dim ftime As Date
   Dim gdate As Date
   Dim gtime As Date
   Dim tdate As Date
   Dim ttime As Date
   Dim i As Long
   Dim goukei As Single
   Dim gcnt As long
   
   Worksheets("Sheet1").Select
   
  'サンプルデータ作成
   Cells(1, 1) = "2019/8/16"
   Cells(1, 2) = "00:00:00"
   Cells(1, 3) = "28.7"
   
   Cells(2, 1) = "2019/8/16"
   Cells(2, 2) = "01:00:00"
   Cells(2, 3) = "29.0"
   
   Cells(3, 1) = "2019/8/16"
   Cells(3, 2) = "02:00:00"
   Cells(3, 3) = "29.1"
   
   Cells(4, 1) = "2019/8/16"
   Cells(4, 2) = "03:00:00"
   Cells(4, 3) = "29.1"
   
   Cells(5, 1) = "2019/8/16"
   Cells(5, 2) = "04:00:00"
   Cells(5, 3) = "29.2"
   
   Cells(6, 1) = "2019/8/16"
   Cells(6, 2) = "05:00:00"
   Cells(6, 3) = "29.1"
   
   Cells(7, 1) = "2019/8/16"
   Cells(7, 2) = "06:00:00"
   Cells(7, 3) = "29.1"
   
   Cells(8, 1) = "2019/8/16"
   Cells(8, 2) = "07:00:00"
   Cells(8, 3) = "29.5"
   
   Cells(9, 1) = "2019/8/16"
   Cells(9, 2) = "08:00:00"
   Cells(9, 3) = "30.1"
   
   Cells(10, 1) = "2019/8/16"
   Cells(10, 2) = "09:00:00"
   Cells(10, 3) = "31.6"
   
   Cells(11, 1) = "2019/8/16"
   Cells(11, 2) = "10:00:00"
   Cells(11, 3) = "32.5"
   
   Cells(12, 1) = "2019/8/16"
   Cells(12, 2) = "11:00:00"
   Cells(12, 3) = "31.7"
   
   Cells(13, 1) = "2019/8/16"
   Cells(13, 2) = "12:00:00"
   Cells(13, 3) = "32.2"
   
   Cells(14, 1) = "2019/8/16"
   Cells(14, 2) = "13:00:00"
   Cells(14, 3) = "31.3"
   
   Cells(15, 1) = "2019/8/16"
   Cells(15, 2) = "14:00:00"
   Cells(15, 3) = "31.6"
   
   Cells(16, 1) = "2019/8/16"
   Cells(16, 2) = "15:00:00"
   Cells(16, 3) = "30.9"
   
   Cells(17, 1) = "2019/8/16"
   Cells(17, 2) = "16:00:00"
   Cells(17, 3) = "30.8"
   
   Cells(18, 1) = "2019/8/16"
   Cells(18, 2) = "17:00:00"
   Cells(18, 3) = "26.9"
   
   Cells(19, 1) = "2019/8/16"
   Cells(19, 2) = "18:00:00"
   Cells(19, 3) = "27.7"
   
   Cells(20, 1) = "2019/8/16"
   Cells(20, 2) = "19:00:00"
   Cells(20, 3) = "28.0"
   
   Cells(21, 1) = "2019/8/16"
   Cells(21, 2) = "20:00:00"
   Cells(21, 3) = "28.3"
   
   Cells(22, 1) = "2019/8/16"
   Cells(22, 2) = "21:00:00"
   Cells(22, 3) = "28.8"
   
   Cells(23, 1) = "2019/8/16"
   Cells(23, 2) = "22:00:00"
   Cells(23, 3) = "29.0"
   
   Cells(24, 1) = "2019/8/16"
   Cells(24, 2) = "23:00:00"
   Cells(24, 3) = "28.7"
   
   fdate = "2019/8/16"
   ftime = "02:00:00"
   
   tdate = "2019/8/16"
   ttime = "13:00:00"
   
   goukei = 0
   gcnt = 0
   i = 1
   Do While Cells(i, 1) <> ""
          
      gdate = Cells(i, 1)
      gtime = Cells(i, 2)
      
      If 時間範囲判定(fdate, ftime, gdate, gtime, tdate, ttime) = "内" Then
         goukei = goukei + Cells(i, 3)
         gcnt = gcnt + 1
      End If
   
      i = i + 1
   Loop
   
   MsgBox ("平均は" & goukei / gcnt & "です")
   

End Sub

Function 時間範囲判定(fdate As Date, ftime As Date, gdate As Date, gtime As Date, tdate As Date, ttime As Date)

    Dim fr As String
    Dim tr As String
    Dim ftr As String
    
    If DateDiff("d", fdate, gdate) > 0 Then
       fr = "大"
    End If

    If DateDiff("d", fdate, gdate) = 0 Then
       
       If DateDiff("s", ftime, gtime) >= 0 Then
          fr = "大"
       Else
          fr = "小"
       End If
    End If

    If DateDiff("d", fdate, gdate) < 0 Then
       fr = "小"
    End If
    
    If DateDiff("d", gdate, tdate) > 0 Then
       tr = "大"
    End If

    If DateDiff("d", gdate, tdate) = 0 Then
       
       If DateDiff("s", gtime, ttime) >= 0 Then
          tr = "大"
       Else
          tr = "小"
       End If
    End If

    If DateDiff("d", gdate, tdate) < 0 Then
       tr = "小"
    End If
    
    ftr = fr & tr
    
    Select Case ftr
           Case "大大"
               時間範囲判定 = "内"
           Case Else
               時間範囲判定 = "外"
    End Select
    
End Function