プロが教えるわが家の防犯対策術!

こんにちは

添付図のようにA列には、数字が1ずつ増加されて入力してあります。
B1に以下のユーザー定義関数で定義した関数を入力し、フィルハンドルで下まで引っ張って入力してあるのですが、この方法だと他の行に定義した関数が上手く反映されないのです。
一度、セルを入力状態にして、Enterで確定をすると反映されるのですが、それだとユーザー定義関数の意味が無いのです。
どなたか理由と解決法を教えていただけないでしょうか


[ユーザー定義関数]

'アクティブセルの左側の数字に1を加算した数値を返す

Function sum2(intNO As Integer) As Integer
sum2 = Cells(ActiveCell.Row, ActiveCell.Column - 1) + 1
End Function

「エクセルのユーザー定義関数について」の質問画像

質問者からの補足コメント

  • 開始点を変えて、再度フィルハンドルを実行した結果です。

    「エクセルのユーザー定義関数について」の補足画像1
      補足日時:2016/03/05 19:36
  • へこむわー

    すいません、お礼に記載した、実際に入力されたセルの数字がずれてました
    以下のように入力されていま

    セル名  入力された内容 実際に表示された値
    セルB1 =myNO(A1)    2 (B2以降はフィルハンドルで埋める)
    セルB2 =myNO(A2)    2 
    セルB3 =myNO(A3)    2
    セルB4 =myNO(A4)    2

      補足日時:2016/03/05 19:39

A 回答 (4件)

アクティブセルって、選択されたセルの中で、


今まさに入力するセルの場所。単一セルです。
つまり、名前ボックスに表示されているセル(定義された名前を除く)
で一つのセルしか存在しません
参考まで
    • good
    • 0

「アクティブセルの左側の数字に1を加算した数値を返す」なんて、おもしろい発想ですね。

セルを移動するたびに、値が変わるということですよね。
問題なのは、アクティブセルを移動しただけでは、再計算が行われないので、値が変わらないことだと思います。

そこで、アクティブセルを移動した時に再計算を行わせるよう、Worksheet_SelectionChangeイベントに次のようなコードを書きます。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub

関数は、次のようにすべきです。引数はいらないので、取っちゃいました。
Function myNO() As Integer
Application.Volatile
myNO = Cells(ActiveCell.Row, ActiveCell.Column - 1) + 1
End Function

そして、A1セルに、=MyNo() と入力します。

空いているセルに適当に数字を入力して、それらの数字の右側にカーソルを移動するたびに、その左側の数字に+1した値が、A1セルに表示されます。
    • good
    • 0

No.1です。



一般的に「関数」の場合数式が入っているセルを基準にすることはないと思います。
(おそらく循環参照になるはず・・・)

ユーザー定義関数も他の関数と同様に使えますので、
当方自身もそのようなユーザー定義関数の使い方をしたことはありません。

なぜそのような結果が表示されるのか、原因はつかめませんが、
「ActiveCell」が邪魔をしているのではないでしょうか?
フィルハンドルでフィル&コピーすると数式を入れたセル以降すべてがActiveCellになりますが
Excel的には最初のセル番地をActiveCellとして認識しているような感じがします。
(これも原因はわかりません)

どうしても数式を入れているセルを基準にしたい場合は
ユーザー定義関数のコードを

Function mySum(i As Long, j As Long)
mySum = Cells(i, j - 1) + 1
End Function

とし、数式を
=mySum(ROW(),COLUMN())
とすればお望みの結果にはなると思います。

何度も言うようですが、こういう使い方を今までしたコトがないので
直接の回答にならなくてごめんなさい。m(_ _)m
    • good
    • 0

こんばんは!



ユーザー定義関数の「名前」に問題があると思います。
そのユーザー定義関数が入っている数式バー内を見てください。
仮に1行目にそのユーザー定義関数が入っていると
=SUM2(A1)
と表示されているはずです。

Excel的にはこのような計算方法はありませんので、エラーが表示されます。

ユーザー定義関数名には「予約語」のように極力使わない方がよい「言葉」があります。
特に今回の「SUM」などは関数として存在しますので、極力避けます。

Function mySum(myRng As Range)
mySum = myRng + 1
End Function

くらいで大丈夫だと思います。m(_ _)m
    • good
    • 0
この回答へのお礼

こんばんは、早速のご回答有り難うございます。
確かにご提示の例では、期待通りの動作になりました。

しかし、最初の質問のコード内容は変えずに関数名を変えてみたところ、やはり、フィルハンドルで埋めていくと、下記のように先頭のセルと同じ値が返されました。


Function myNO(intNO As Integer) As Integer
myNO = Cells(ActiveCell.Row, ActiveCell.Column - 1) + 1
End Function

セル名  入力された内容 実際に表示された値
セルB1 =myNO(A2) 2 (B2以降はフィルハンドルで埋める)
セルB2 =myNO(A3) 2
セルB3 =myNO(A4) 2
セルB4 =myNO(A5) 2

しかし、この状態でセルB2をダブルクリックもしくは数式バーをシングルクリックしてからEnterを押すときちんと、数字の3が返されます。(以降も同様の操作で加算した値が返される)

そこで、「フィルハンドルでは範囲の先頭がアクティブセルとして認識される」という結論に至ったのですが、いかがでしょうか?
その証拠に一度、B1セルにユーザー定義関数myNOを入力し、以降をフィルハンドルで埋めて、その後、補足の添付図のようにB2セルB9セルにかけて再度フィルハンドルで埋めていくと、B2がアクティブセルと認識され、それに1を加算した値が、B3以降に返されております。

お礼日時:2016/03/05 19:35

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!