6本目：セルに計算式

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

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

出題：セルに計算式

#VBA100本ノック 6本目
画像のようにA1から始まる表があります。
D列にB列×C列の計算式を入れてください。
ただし商品コードに"-"の枝番が付いている場合は計算式を入れずそのままにしてください。
例.D2にはA2×B2の計算式を入れる。D4:D5には計算式を入れない。

◇ 出題ページはこちら

ソースコード

定義モジュール

Option Explicit

' データ
Public Const R1stデータ = 2
Public Const C1stデータ = 1
Public Enum CNoデータ
    商品コード = C1stデータ
    単価
    点数
    金額
End Enum
Public Const CLastデータ = CNoデータ.金額
Public Const FormulaR1C1_金額 = "=RC[-2]*RC[-1]"

メインモジュール

Option Explicit

' 100本ノック006：セルに計算式
Sub ハイフンなし商品のみに金額計算式を入力する()
    With WSデータ
    
        Dim R As Long
        For R = R1stデータ To Get最終行(WSデータ)
            
            If InStr(.Cells(R, CNoデータ.商品コード), "-") = 0 Then
            
                .Cells(R, CNoデータ.金額).FormulaR1C1 = FormulaR1C1_金額
                
            End If
            
        Next
    
    End With
End Sub

汎用関数モジュール

Option Explicit

' 最終行の取得
' 参考：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プロパティが簡単ですが、
今回は飛び飛びのエリアに入力する必要があります。

その場合、Formulaですとなかなか面倒なコードになってしまいますが、
FormulaR1C1を使うことで相対位置を使った式にすることが出来ます。

"=RC[-2]*RC[-1]" ' ← 2つ左のセル × 1つ左のセル

　
この書き方ができることは覚えておきましょう。

ちなみにこの式を定数化する方法ですが、

• 2や-1をEnumから計算することはおすすめしません。

定義モジュールに数式を文字列定数として宣言しておき、
改修時にはEnumと一緒にここを修正すれば問題ないですからね。

このあたり、メンテナンス性を重視しすぎてコーディングコストがかさんでしまわないよう気を付けましょう。