8本目：点数の合否判定

Excel&VBA解説サイト「エクセルの神髄」様出題の問題集、
「VBA100本ノック」に対する私の回答と解説のページです。

100本ノックの出題リストはこちらから
excel-ubara.com

出題：点数の合否判定

#VBA100本ノック 8本目
「成績表」シートに5教科の成績表があります。
以下の2条件を満たした者が合格となります。
・5教科合計が350点以上
・全ての科目が50点以上
G列に、合格者に対しては「合格」と出力し、不合格は空欄にしてください。

◇ 出題ページはこちら

ソースコード

定義モジュール

' 成績表
Public Const R1st成績表 = 2
Public Const C1st成績表 = 1
Public Enum CNo成績表
    氏名 = C1st成績表
    教科1st
    英語 = CNo成績表.教科1st
    国語
    数学
    理科
    社会
    教科Last = CNo成績表.社会
    合否判定
End Enum
Public Const CLast成績表 = CNo成績表.合否判定

メインモジュール

' 100本ノック008：点数の合否判定
Sub 合否判定を計算する()
    
    ' データ全行をループ
    Dim R As Long
    For R = R1st成績表 To Get最終行(WS成績表)
        
        ' 各教科点数のセル範囲を取得
        Dim rng各教科点数 As Range
        Set rng各教科点数 = WS成績表.Range _
            (WS成績表.Cells(R, CNo成績表.教科1st) _
           , WS成績表.Cells(R, CNo成績表.教科Last))
           
        ' SUM/MINで合否を判定
        If Fx.Sum(rng各教科点数) >= 350 And _
           Fx.Min(rng各教科点数) >= 50 Then
            WS成績表.Cells(R, CNo成績表.合否判定) = "合格"
        Else
            WS成績表.Cells(R, CNo成績表.合否判定) = ""
        End If
        
    Next

End Sub

汎用関数モジュール

' WorksheetFunctionの短縮取得
' 参考：https://www.limecode.jp/entry/utility/shortcall-worksheetfunction
Function Fx() As WorksheetFunction
    Set Fx = WorksheetFunction
End Function

' 最終行の取得
' 参考：https://www.limecode.jp/entry/library/get-lastrow-lastcolumn
Function Get最終行(指定オブジェクト As Variant, Optional ByVal C As Long = -1) As Long

    ' 渡されたオブジェクトからセル範囲を取得
    Dim 対象セル範囲 As Range
    Select Case TypeName(指定オブジェクト)
    Case "Range"
        If 指定オブジェクト.Cells.CountLarge = 1 Then ' 単独セルにはCurrentRegionを取る
            Set 対象セル範囲 = 指定オブジェクト.CurrentRegion
        Else
            Set 対象セル範囲 = 指定オブジェクト
        End If
    Case "Worksheet"
        Set 対象セル範囲 = 指定オブジェクト.UsedRange
    Case "AutoFilter", "ListObject"
        Set 対象セル範囲 = 指定オブジェクト.Range
    Case Else
        Err.Raise 1000, , "対象外のオブジェクト「" & TypeName(指定オブジェクト) & "」が指定されました。"
    End Select

    ' エリアの最終行を取得
    Get最終行 = 対象セル範囲.Rows.Count + 対象セル範囲.Row - 1

    ' 列が指定されていればその列の入力最終行を取得
    If C <> -1 Then
        Do While 対象セル範囲.Worksheet.Cells(Get最終行, C) = ""
            Get最終行 = Get最終行 - 1
            If Get最終行 < 対象セル範囲.Row Then
                Get最終行 = 0
                Exit Function
            End If
        Loop
    End If

End Function

別解：Formula版

Sub 合否判定を計算する_Formula版()
    
    Dim 合否計算エリア As Range
    Set 合否計算エリア = WS成績表.Range("G2:G" & Get最終行(WS成績表))
    
    合否計算エリア.Formula = "=IF(AND(SUM(B2:F2)>=350,MIN(B2:F2)>=50),""合格"","""")"
    合否計算エリア.Value = 合否計算エリア.Value
    
End Sub

解説

数値を判定して文字列を出力する基本的な問題でした。

せっかくのExcelですからSUM関数とMIN関数を使いたいところですね。

サンプルにはFor文でWorksheetFunctionを使用したバージョンと、
Formulaで直接セルに数式を入力したバージョンを記載しました。

どちらも便利な記法ですので、どちらとも使えるようになっておきましょう。

また枝葉の話ですが、本問はEnumの宣言に迷うところです。

例えば各教科の先頭・末尾を、

        Set rng各教科点数 = WS成績表.Range _
            (WS成績表.Cells(R, CNo成績表.英語) _
           , WS成績表.Cells(R, CNo成績表.社会))

こうしてしまうと、教科が増えたり並び変わるとエラーになります。

よって今回は、

Public Enum CNo成績表
    氏名 = C1st成績表
    教科1st
    英語 = CNo成績表.教科1st
    国語
    数学
    理科
    社会
    教科Last = CNo成績表.社会
    合否判定
End Enum

こんなEnumを作って対応しました。

このあたり、形は何でもよいと思いますが、
「英語」「社会」をソースに使ってしまうとまずいことになります。

自分なりの対応方法を確立しておきましょう。