エクセル VBAでのワークシート関数の記載について

ワークシートにて
=TRIMMEAN(OFFSET(R2C2,0,0,COUNTA(A:(A)),1),0.2)

こちらを計算しているのですが、処理の関係でVBA上での記載が必要となりました。
worksheet.functionを使用しての書き直しをしているのですが、
offset以下の表記がよくわからず、苦戦しています。

.Cells(1, 6).Value =
WorksheetFunction.TrimMean
(.Offset(Cells(2, 2), 0, 0, WorksheetFunction.CountA("A:A"), 1), 0.2)

↑見やすい位置で改行しています。ご了承ください。

どのように記載すればよろしいでしょうか。教えてください。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2023/05/17 00:41

こんばんは

関数式があるのなら、面倒な変換をしない方が簡単です。

でも、その前に・・
ご提示の式をそのまま入力すると、ワークシートに拒否されますので、
　=TRIMMEAN(OFFSET($B$2,0,0,COUNTA(A:A),1),0.2)
であろうと勝手に解釈しました。
また、ご提示のコードはＷith文のなかにあるものと解釈。

.Cells(1, 6).FormulaLocal = "=TRIMMEAN(OFFSET($B$2,0,0,COUNTA(A:A),1),0.2)"
.Cells(1, 6).Value = .Cells(1, 6).Value

でもいけると思います。
内容としては、エクセルが計算した計算式の結果を、セルに再代入しています。
※ F1セルの書式が「文字列」になっていないことが条件になります。

この回答へのお礼

回答ありがとうございました。

なるほど、あえて関数を変換せずに代入させる方法ですね。
難しく考えず、スマートにできました。

推測もありがとうございました。説明不足で申し訳ありません。

お礼日時：2023/05/17 06:30

No.2 回答者： Qchan1962 回答日時： 2023/05/16 23:49

すみません書き飛ばしました

VBAではObject.Offset(0, 0)は同じObject（基準）を示しますので書く必要がありません。したがって
ws.Cells(2, 6).Value = .TrimMean(ws.Cells(2, 2).Resize(.CountA(ws.Columns(1)), 1), 0.2)
で良いと思います

この回答へのお礼

回答ありがとうございました。
worksheetfunctionもwithで使うことができるとは知りませんでした。
勉強になります。

お礼日時：2023/05/17 06:28

No.1 回答者： Qchan1962 回答日時： 2023/05/16 23:43

こんばんは

ワークシート関数のOffset引数　OFFSET(基準,行数,列数,高さ,幅)　は
VBAの場合　WorksheetFunctionを使わず
基準.OFFSET(行数,列数).Resize(高さ,幅)みたいに書いた方が私的には分かり易いです
これを踏まえると
Dim ws As Worksheet
Set ws = ActiveSheet
With WorksheetFunction
ws.Cells(2, 6).Value = .TrimMean(ws.Cells(2, 2).Offset(0, 0).Resize(.CountA(ws.Columns(1)), 1), 0.2)
End With
で結果は求められると思いますが、何しろ私は関数が良く解らないので
もっと良い書き方があるかも知れませんので参考程度でお願いします