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ベストアンサー
- 回答日時:
まず、
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の意味や違いがまだ理解できていませんが、すっきりしました。
ご回答いただいた内容を勉強します。ありがとうございました。
No.2
- 回答日時:
因みに、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"、2なら"B"を返す関数」なんか要りません。
あと、作った「式」をセルに代入する時は
Cells(4, i) = 式の文字列
ではなく
Cells(4, i).Formula = 式の文字列
にしないといけません。
もし「代入しようとしたセルの表示形式が、文字列になっている場合」に
Cells(4, i) = 式の文字列
と書くと、式としてではなく「ただの文字列」としてセルに値が代入されてしまい、式が計算されません。
No.3
- 回答日時:
更に追記。
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]」の部分が「固定の文字列」である事に注目して下さい。変数にする必要がありません。
これは「参照する先が、常に、自分自身のセルの1行下」なので「参照する式が一定」になるからです。
これをうまく応用すれば「式が固定の文字列で済む」ので、余計な処理がすべて不要になります。
No.4
- 回答日時:
入力したい数式はそれであってますか?
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
と書いてください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) 実行時エラー´5854´ 文字列型パラメーターが長すぎます。 3 2023/06/08 21:17
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- Visual Basic(VBA) vbaの計算 if elseと範囲について 6 2022/11/26 01:49
- Visual Basic(VBA) いつもお世話になっております、VBAで教えて頂きたいのですが 2 2022/05/05 22:20
- Visual Basic(VBA) マクロ実行時、自動で背景色を変えたい。 C列にあるチェックボックスをチェックするとB列に「TRUE」 4 2022/11/08 11:14
- Visual Basic(VBA) ①ExcelVBAでカレンダーを作り、別のユザーフォームで日付を入力したいのですがエラーになります。 1 2023/02/17 18:39
- Visual Basic(VBA) VBA Userformで一部別シートに転記がしたいのですが 2 2023/05/24 13:08
- Visual Basic(VBA) 前回ご教授いただいたコードに覚えたてのループ処理で品名りんごAから順に20回for nextでループ 7 2023/01/13 22:01
- Visual Basic(VBA) Excel-VBAでのファイルの開き方 4 2023/02/14 11:01
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBAで大量のファイルをシート名...
-
Vba 実数および実数タイプの変...
-
エクセルVBAについて
-
ユーザーフォームに別シートか...
-
VB.net(VB)で、フォームにExcel...
-
Excelのマクロについて教えてく...
-
エクセルの合計を自動で表示さ...
-
Excelのマクロでワードのテキス...
-
VBAの計算で@が出てしまう件
-
VBA listBoxから
-
エクセルのマクロについて教え...
-
Excelのマクロについて教えてく...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
左右の表のキー位置を合わせたい
-
VBAの質問になります メッセー...
-
Excel マクロについての相談
-
VBA レジストリの値の読み方に...
-
2つのマクロでチェックボックス...
-
Vba SelStart、SelLen教えてく...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel VBA 定義されたプロージ...
-
Excel-VBAのmsgBox()の不思議
-
【VBA】マクロの入ったファイル...
-
VBA 複数条件の分岐処理の上手...
-
現在のブックを閉じないで、マ...
-
VBAで各列の"+"と"o"の合計数を...
-
VBAに詳しい方教えてください。
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
ユーザーフォームに別シートか...
-
エクセルのマクロについて教え...
-
ExcelVBA シート名を複数セルか...
-
エクセルのマクロについて教え...
-
VBA listBoxから
-
Excelのマクロについて教えてく...
-
エクセルのマクロについて教え...
おすすめ情報