Excel VBA ByRef引数の型？

Excel2010でセルに関数式を埋め込むマクロを書いています。
そこで、「ByRef引数の型が一致しません」というエラーが出て困っています。
プログラムの中からエラーの出る所だけを取り出してtest1,test2として試したところ、
test1はエラーが出て、test2はプログラムが動いて目的とするセルに関数式が挿入されました。

Sub test1()
dim i, myR as integer
myR = 30
For i = 7 To 31
Cells(4, i) = "=IF(" & ConvertToLetter(i) & i - 4 & "="""","""",SUM(" & ConvertToLetter(i) & "5:" & ConvertToLetter(i) & myR & ")/(" & myR & "-COUNTIF(" & ConvertToLetter(i) & "5:" & ConvertToLetter(i) & myR & ","""")))"
next i
End Sub

test1でConvertToLetter(i)のiが青色で選択され「ByRef引数の型不一致」エラーとなってしまいます。

そこで、試しに一度変数iの値をkに渡して同じプログラムをtest2として実行してみました。
Sub test2()
dim i, k, myR as integer
myR = 30
For i = 7 To 31
k=i
Cells(4, i) = "=IF(" & ConvertToLetter(k) & i - 4 & "="""","""",SUM(" & ConvertToLetter(k) & "5:" & ConvertToLetter(k) & myR & ")/(" & myR & "-COUNTIF(" & ConvertToLetter(k) & "5:" & ConvertToLetter(k) & myR & ","""")))"
next i
End Sub

このtest2は、きちんと実行され、目的とするG4(～AE4)のセルに
=IF(G3="","",SUM(G5:G30)/(30-COUNTIF(G5:G30,"")))
という式が挿入されました。

質問1：なぜ、test1がダメで、test2ならうまくいくのかという理由がわかりません。
質問2：うまくいったtest2と同じ式を、もっと長いプログラムの中に入れるとやはり「ByRef引数の型が一致しません」エラーが出てプログラムが止まってしまいます。test2単独のプログラムならうまく動くのに、他のプログラムの一部に埋め込むとエラーが出る理由もわかりません。

VBAプログラムを試行錯誤しながら勉強している初心者です。どなたかご教示お願いします。

ちなみに、ConvertToLetterは、
Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int((iCol - 1) / 26)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
という、関数で定義しています。

No.1 ベストアンサー 回答者： chie65535 回答日時： 2013/06/28 12:16

まず、

Function ConvertToLetter(iCol As Integer) As String

は「参照渡し」になっています。

引数の渡し方の指定子であるByVal、ByRefを省略すると、自動的にByRefになります。つまり

Function ConvertToLetter(ByRef iCol As Integer) As String

と書いているのと同じなのです。

んで、test1では

For i = 7 To 31
Cells(4, i) = "=IF(" & ConvertToLetter(i)

のように「参照渡しが許されないFor文のループ変数を、参照渡ししようとしている」ので「ByRef引数の型不一致」のエラーになります。

test2では

For i = 7 To 31
k=i
Cells(4, i) = "=IF(" & ConvertToLetter(k)

のように「参照渡しが許されないFor文のループ変数を渡さず、普通の変数を参照渡ししている」ので、エラーにはなりません。

解決方法は「ConvertToLetterの引数を値渡しに変える」です。つまり

Function ConvertToLetter(iCol As Integer) As String
を
Function ConvertToLetter(ByVal iCol As Integer) As String
に変えれば、すべて解決します。

引数にByRefを付けたり、何も書かない場合は「引数の値を関数内で書き換えてしまうと、書き換えた結果が呼び出し元にも影響してしまう」ので、故意にそういう事をするのでなければ、必ず「ByVal」を指定しましょう。

自作関数の引数は、バグを産まない為にも「必ずByValかByRefのどちらかを明記する」ようにしましょう。

この回答へのお礼

ありがとうございました。Byval,ByRefの意味や違いがまだ理解できていませんが、すっきりしました。
ご回答いただいた内容を勉強します。ありがとうございました。

お礼日時：2013/06/28 16:24

No.4 回答者： end-u 回答日時： 2013/06/28 15:08

入力したい数式はそれであってますか？

H4セルが=IF(H4="","",SUM(H5:H30)/(30-COUNTIF(H5:H30,"")))循環しちゃってますけど？
単純なAVERAGEとも違うって事なんですよね..

..って事はおいといて。
ループインデックスを参照渡しする事の是非もおいとくとして。

コンパイルエラーですよね。
>質問1：なぜ、test1がダメで、test2ならうまくいくのかという理由がわかりません。
提示されたtest2でもうまくいきません。
問題は
>dim i, myR as integer
>dim i, k, myR as integer
ここ。
変数iもkも型宣言が省略されているとみなされ、Variant型になっています。
だから型不一致コンパイルエラーです。
ByVal値渡しだと暗黙的に型変換されるのでコンパイルエラーにひっかからないとは思いますが
ループインデックスをVariantにする必然性はないので、ちゃんと型宣言しておくほうが良いでしょう。
カンマで繋ぐ場合は
Dim i As Integer, myR As Integer
と書いてください。

No.3 回答者： chie65535 回答日時： 2013/06/28 13:23

更に追記。

i=7
Cells(4,i).FormulaR1C1 = "=R[1]C[0]"

と書くと、Cells(4,i)に「=G5」って言う式が代入されます。

この方式だと「自分のセルからの相対位置」で指定出来るので「固定の文字列」で大丈夫になります。

例えば、

G4セルに対して「FormulaR1C1 = "=R[1]C[0]"」ってやれば「=G5」に

H4セルに対して「FormulaR1C1 = "=R[1]C[0]"」ってやれば「=H5」に

I4セルに対して「FormulaR1C1 = "=R[1]C[0]"」ってやれば「=I5」に

になります。

式の中の「[1]」や「[0]」の部分が「固定の文字列」である事に注目して下さい。変数にする必要がありません。

これは「参照する先が、常に、自分自身のセルの１行下」なので「参照する式が一定」になるからです。

これをうまく応用すれば「式が固定の文字列で済む」ので、余計な処理がすべて不要になります。

No.2 回答者： chie65535 回答日時： 2013/06/28 12:46

因みに、OFFSETワークシート関数を使えば「数値をそのままセル指定に使える」ので、ConvertToLetterなどと言う関数は要りません。

例えば

=IF(G3="","",SUM(G5:G30)/(30-COUNTIF(G5:G30,"")))

は

=IF(OFFSET($A$1,5,7,1,1)="","",SUM(OFFSET($A$1,5,7,26,1))/(30-COUNTIF(OFFSET($A$1,5,7,26,1),"")))

と言う式にする事が出来ます。

また、INDIRECTワークシート関数を使えば「R1C1」と言うセル指定も使えます。

例えば

=IF(G3="","",SUM(G5:G30)/(30-COUNTIF(G5:G30,"")))

は

=IF(INDIRECT("R3C7",0)="","",SUM(INDIRECT("R5C7:R30C7",0))/(30-COUNTIF(INDIRECT("R5C7:R30C7",0),"")))

と言う式にする事が出来ます。

こうすると「1なら"A"、２なら"B"を返す関数」なんか要りません。

あと、作った「式」をセルに代入する時は

Cells(4, i) = 式の文字列

ではなく

Cells(4, i).Formula = 式の文字列

にしないといけません。

もし「代入しようとしたセルの表示形式が、文字列になっている場合」に

Cells(4, i) = 式の文字列

と書くと、式としてではなく「ただの文字列」としてセルに値が代入されてしまい、式が計算されません。