エクセル VBAで関数セット

はじめに

最近エクセルVBAを触ったが、忘れそうなので、これを見れば思いだせるようメモっておく。

例

ある特定の範囲に関数をセットする。
直接シートに関数をセットすればいいが、今回はあえてvbaでセットする方法を記載する。

sheet2と、sheet1のある個所を掛けるという関数をセットする。

Set sh1 = ThiwWorkbook.Sheets(‘sheet1)

'以下のように関数をセットする
sh1.Range(“G5”).formula =“=G11Sheet2!G7”
sh1.Range(“G6”).formula =“=G12Sheet2!G8”
sh1.Range(“G7”).formula =“=G13Sheet2!G9”
sh1.Range(“G8”).formula =“=G14Sheet2!G10”
sh1.Range(“G9”).formula =“=G15Sheet2!G11”
sh1.Range(“H5”).formula =“=H11Sheet2!H7”
sh1.Range(“H6”).formula =“=H12Sheet2!H8”
sh1.Range(“H7”).formula =“=H13Sheet2!H9”
sh1.Range(“H8”).formula =“=H14Sheet2!H10”
sh1.Range(“H9”).formula =“=H15Sheet2!H11”

関数設定にあたって

・相対参照、絶対参照について
・セルのアドレスより$で分割

相対参照、絶対参照

文字で書くとややこしいが、理解要。
Excelでセル参照をする際に使用されるもの。
要はコピーで行とか列にそって変えていくなら相対参照、。

1. 相対参照：数式セルをコピーした時、自動的に参照セルも移動する状態。
2. 絶対参照：数式セルをコピーしても、参照セルが固定された状態。
3. 複合参照：「1」と「2」を組み合わせた状態。

相対参照は、数式セルをコピーした時、自動的に参照セルも移動する状態。一方、絶対参照は、数式セルをコピーしても、参照セルが固定された状態。複合参照は、相対参照と絶対参照を組み合わせたもの。

絶対参照は行の番号と列のアルファベットの前にそれぞれ$を入力し指定する。A1のセルを常に参照したい場合は、$A$1と入力。相対参照は、数式セルをコピーした時、自動的に参照セルも移動する状態。例えば、B1に=A1+50という数式を入力すると、下のセルへとコピーすると参照先がコピー先に応じて変化する。複合参照は、両方組み合わせたもので、$A1やA$1などがある。

セルのアドレスより列を取得

colLetter = Split(Cells(1, c).Address, "$")(1)

このコードは、セルのアドレスを「$」で分割して、列名を取得するもの。

Cells(1, c) は、1行目のc列目のセルを表す。
例えば、cが3なら、Cells(1, c)はC1セルになる。
Cells(1, c).Address は、セルのアドレスを文字列で返す。
例えば、C1セルのアドレスは$C$1となる。
Split(Cells(1, c).Address, "")は、セルのアドレスを「」で区切って配列する、例えば、$C$1を$で区切ると、「」「C」「1」という3つの要素からなる配列になる。
Split(Cells(1, c).Address,"$")(1) は、配列の2番目の要素を取り出す。例えば、「」「C」「1」という配列の2番目の要素は「C」。
colLetter = Split(Cells(1, c).Address,"$")(1) は、colLetterという変数に配列の2番目の要素を代入する。今回は、「C」をcolLetterに代入する。

今回の場合

絶対参照は使用せず、相対参照での関数記載となる。

Sub SetFormulas()
    Dim sh1 As Worksheet
    Dim r As Integer, c As Integer
    Dim startCol As Integer, endCol As Integer
    Dim startRow As Integer, endRow As Integer
    Dim srcRow As Integer, destRow As Integer
    Dim formula As String, colLetter As String, colLetterSheet2 As String
    
    ' Sheet1のワークシートオブジェクトを初期化
    Set sh1 = ThisWorkbook.Sheets("Sheet1")
    
    ' 開始と終了の列番号を定義（G=7、AE=31）
    startCol = 7
    endCol = 31
    
    ' 開始と終了の行番号を定義（5から9）
    startRow = 5
    endRow = 9
    
    ' 行と列を通じてループを回して、関数を設定
    For r = startRow To endRow
        srcRow = r + 6 ' 対応するソース行（11から始まる）
        destRow = r - startRow + 7 ' 対応するSheet2の行（7から始まる）
        
        For c = startCol To endCol
            ' 列番号を文字に変換
            colLetter = Split(Cells(1, c).Address, "$")(1)
            
            ' Sheet2の対応する列文字を変換（G7から始まるので7列先）
            colLetterSheet2 = Split(Cells(1, c - startCol + 7).Address, "$")(1)
            
            ' 関数を設定
            formula = "=" & colLetter & srcRow & "*Sheet2!" & colLetterSheet2 & destRow
            sh1.Cells(r, c).formula = formula
        Next c
    Next r
End Sub

例)sh1.Range(“H9”).formula =“=H15Sheet2!H11”

おわりに

他にも忘れないよう記載しておくべきことは色々あるが、いったん関数セットからメモをとった。