Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
関数の値が#VALUEになる
投稿日時: 18/11/08 08:18:42
投稿者: George

いつもお世話になっています。
 
今回、以下のような事象が出て困っています。
 
ブック内のシートに自前関数を使ってセルに式を入れているのですが、
ファイルを開いてすぐだと関数が入っているセルの値が#VALUE!になります。
 
そのセルをすべてF2キーを押してEnterキーで確定すると正常な値が返ってくるのですが、
ファイルの保存をして再び開くと現象が再現してしまいます。
 
何か回避する方法がないか皆さんの知恵を貸してもらえればと思います。

回答
投稿日時: 18/11/08 10:18:44
投稿者: bi

数式タブにある「計算方法の設定」を見てください。「手動」になっていませんか?

投稿日時: 18/11/08 11:14:05
投稿者: George

bi さんの引用:
数式タブにある「計算方法の設定」を見てください。「手動」になっていませんか?

返信ありがとうございます。
オプションの計算方式の設定は「自動」になっています。

回答
投稿日時: 18/11/08 11:44:51
投稿者: sk

引用:
ブック内のシートに自前関数を使ってセルに式を入れているのですが、
ファイルを開いてすぐだと関数が入っているセルの値が#VALUE!になります。

引用:
そのセルをすべてF2キーを押してEnterキーで確定すると正常な値が返ってくる

引用:
オプションの計算方式の設定は「自動」になっています。

Application.Volatile メソッドを
呼び出していないからでは。
 
即効テクニック より:
https://www.moug.net/tech/exvba/0100006.html

投稿日時: 18/11/08 13:37:02
投稿者: George

skさん 返信いただきありがとうございます。
 

引用:
Application.Volatile メソッドを
呼び出していないからでは。

セルに書いている関数すべてに書いてみたのですが、結果は変わりませんでした。

回答
投稿日時: 18/11/08 14:18:22
投稿者: sk

引用:
セルに書いている関数すべてに書いてみたのですが、
結果は変わりませんでした。

ではとりあえず、以下の情報を明記されることをお奨めします。
 
・その現象が発生しているセルの数式。
 
・数式に含まれているユーザー定義関数において
 実行しているコード。

投稿日時: 18/11/09 08:25:42
投稿者: George

引用:

・その現象が発生しているセルの数式。
・数式に含まれているユーザー定義関数において
 実行しているコード。

ユーザ定義関数の詳細(ソースコード)は以下の通りです。
 
Function chkFukugou(ByVal denbun As Range) As String
    If denbun.value = "" Then
        chkFukugou = RESULT_NO_DENBUN
    ElseIf Right(denbun.value, 3) = Right(denbun.Offset(0, 1).value, 3) Then
        chkFukugou = RESULT_FUKUGOU
    Else
        chkFukugou = RESULT_1PAGE
    End If
End Function

Function chkKitaiFukugoSosinCommon(ByVal denbun As Range) As Integer
    Dim splitValue As Variant   '分割用
    
    If denbun.value = "" Then
        chkKitaiFukugoSosinCommon = 0
    Else
    
        If Left(Cells(ActiveCell.row, Result_POSITION), 4) = Left(Application.Caller.Offset(0, -1), 4) Then 
            chkKitaiFukugoSosinCommon = 0
        Else
            chkKitaiFukugoSosinCommon = 1
        End If
    End If
    
End Function

Function chkValueCommon(ByVal denbun As Range) As String
    Dim splitValue As Variant   '分割用
    Dim pageNo As String        '頁番号
    Dim ramName As String       'RAM名

    splitValue = split(Cells(ActiveCell.row, Result_POSITION), " ")     
    pageNo = splitValue(2)  '頁番号取得
    ramName = splitValue(3) 'RAM名を取得
    chkValueCommon = chkValue(denbun, pageNo, ramName)
    
End Function

Function chkKitaiSosinCommon(ByVal denbun As Range) As Integer
    Dim splitValue As Variant   '分割用
    
    If denbun.value = "" Then
        chkKitaiSosinCommon = 0
        
    Else
        splitValue = split(Cells(ActiveCell.row, Result_POSITION), " ") 
        
        If StrConv(splitValue(4), vbNarrow) = StrConv(Application.Caller.Offset(0, -1), _
          vbNarrow) Then
            chkKitaiSosinCommon = 0
        Else
            chkKitaiSosinCommon = 1
        End If
    End If
    
End Function

 
 
なお、セルの数式ですが、以下のようになっています。
=chkfukugou($AG$7)
=chkKitaiFukugoSosinCommon($AG$7)
=chkValueCommon($AG$7)
=chkKitaiSosinCommon($AG$7)
AG7には150文字の0と1から成る電文が入っています。
 
 

投稿日時: 18/11/09 08:48:05
投稿者: George

追記です。
まだエラーが出るところがありました。
その部分の数式は以下の通りです。
  
=IF($AG$7="","",IF(SUBTOTAL(9,AH8:AH14)=0,"○","×"))
 
AG7は先ほども書いた電文が入っている部分でAH8〜AH14は
chkKitaiFukugoSosinCommon()関数かchkKitaiSosinCommon()関数が書かれています。

投稿日時: 18/11/09 09:01:05
投稿者: George

さらに追記です。
ファイルを保存すると再計算されているのかわかりませんが、またエラー(#VALUE!)になります。
なお、対象となるセルの値が入っているところでも例外なく起こっています。

回答
投稿日時: 18/11/09 09:33:27
投稿者: sk

引用:
RESULT_NO_DENBUN

引用:
RESULT_FUKUGOU

引用:
RESULT_1PAGE

引用:
Result_POSITION

これらはどこで定義されている変数/定数/関数なのでしょうか。
 
仮にモジュールレベルで宣言されている変数であるとして、
これらの変数のデータ型は何でしょうか。
また、それぞれの値をいつ(どのイベントで)
設定されているのでしょうか。
 
引用:
Left(Cells(ActiveCell.row, Result_POSITION), 4)

また、アクティブセルと同じ行のいずれかの列のセルの値を
取得しようとしている箇所がいくつか見られますが、
再計算のタイミングによっては、おかしな挙動に
なりかねないのではないでしょうか。

投稿日時: 18/11/09 11:12:23
投稿者: George

skさんへ
 
定数については以下の通りに定義されています。
なお、定数は関数と同じ標準モジュール内に定義されています。

Public Const RESULT_NO_DENBUN As String = "送信値なし"
Public Const RESULT_FUKUGOU As String = "複合送信"
Public Const RESULT_1PAGE As String = "一頁送信"
Public Const RESULT_ERROR As String = "設定不正"

 
引用:
また、それぞれの値をいつ(どのイベントで)
設定されているのでしょうか。

ごめんなさい。どういう意味でおっしゃっているのか
理解しかねますのでお手数ですがもう少し噛み砕いて言ってもらえますでしょうか。
 
Result_POSITION については以下の通り定義されています。
なお、定数は関数とは別の標準モジュール内に定義されています。
Public Const Result_POSITION As Integer = 8

 
引用:
また、アクティブセルと同じ行のいずれかの列のセルの値を
取得しようとしている箇所がいくつか見られますが、
再計算のタイミングによっては、おかしな挙動に
なりかねないのではないでしょうか。

確かに改めて見ると再計算する際に違うセルにいたらおかしな挙動をするかもしれませんね。
実際は関数が書かれている行番号を参照したいです。

回答
投稿日時: 18/11/09 12:06:47
投稿者: sk

引用:
ごめんなさい。どういう意味でおっしゃっているのか
理解しかねますのでお手数ですがもう少し噛み砕いて
言ってもらえますでしょうか。

RESULT_NO_DENBUN などが定数ではなく変数であると
仮定した場合での話ですので、その問題は無視して下さい。
 
引用:
確かに改めて見ると再計算する際に違うセルにいたらおかしな挙動をするかもしれませんね。
実際は関数が書かれている行番号を参照したいです。

引用:
Left(Cells(ActiveCell.Row, Result_POSITION), 4)

Left(Cells(Application.Caller.Row, Result_POSITION), 4)
 
-----------------------------------------------------------
 
他の箇所も同様に修正を。
 
また、出来れば Caller プロパティが返すデータの型が
Range オブジェクトであるかどうかを判別する処理を
加えた方が良いでしょう。
 
引用:
If Left(Cells(ActiveCell.Row, Result_POSITION), 4) = Left(Application.Caller.Offset(0, -1), 4) Then

この場合、関数を呼び出す数式が A 列のセルに
記述されている場合はエラーとなります。
 
引用:
splitValue = Split(Cells(ActiveCell.Row, Result_POSITION), " ")
pageNo = splitValue(2) '頁番号取得
ramName = splitValue(3) 'RAM名を取得

引用:
splitValue = split(Cells(ActiveCell.row, Result_POSITION), " ")
 
If StrConv(splitValue(4), vbNarrow) = StrConv(Application.Caller.Offset(0, -1), _
            vbNarrow) Then

Split 関数によって返される 1 次元配列の要素の数が
必ず 4 以上( 5 以上)になるという保証がないのであれば、
当然ここもエラー発生の原因となり得ます。
 
そしてどの関数に関しても言えることですが、
例外処理がほとんど行われていないというのが
そもそもよろしくないと思います。

投稿日時: 18/11/09 14:33:34
投稿者: George

引用:
引用:
Left(Cells(ActiveCell.Row, Result_POSITION), 4)

Left(Cells(Application.Caller.Row, Result_POSITION), 4)

この部分は処理を施しましたが関数によって結果に差異(上手くいく関数といかない関数あり)が出ます。
 
 
引用:
また、出来れば Caller プロパティが返すデータの型が
Range オブジェクトであるかどうかを判別する処理を
加えた方が良いでしょう。

この部分は
IsObject(Application.Caller)
でよろしいのでしょうか?
 
引用:
If Left(Cells(ActiveCell.Row, Result_POSITION), 4) = Left(Application.Caller.Offset(0, -1), 4) Then

この場合、関数を呼び出す数式が A 列のセルに
記述されている場合はエラーとなります。
 
引用:

Split 関数によって返される 1 次元配列の要素の数が
必ず 4 以上( 5 以上)になるという保証がないのであれば、
当然ここもエラー発生の原因となり得ます。

私が作った関数ではないですがお恥ずかしい限りです。
しかし、1次元配列の要素の数は必ず4になるようになっています。
おそらくそのためにエラー処理を施さなかったと思われます。

回答
投稿日時: 18/11/09 16:11:46
投稿者: sk

引用:
この部分は処理を施しましたが関数によって
結果に差異(上手くいく関数といかない関数あり)が出ます。

・関数に渡されているセルの値。
 
・関数を呼び出している数式セルと同じ行の H 列のセルの値。
 
・それぞれの Offset プロパティによって参照されるセルの値。
 
が実際に何なのかにもよるでしょうし、それぞれの関数の仕様や
用途を私が知る由もありませんので、どこをどう直すべきかに
ついても正確にはお答えできません。
 
今のところ挙げられる修正すべき(と思われる)箇所としては、
 
引用:
Left(Cells(Application.Caller.Row, Result_POSITION), 4)

上記のように、アクティブシートのセル範囲
参照しようとしている 3 つの箇所です。
 
例えば、chkKitaiFukugoSosinCommon を呼び出している
数式セルのあるワークシートがアクティブシートではない状態で
再計算が実行されれば、Left 関数の対象となるセルは
当然ずれることになるでしょう。
(開いているブックが 1 つしかなく、ブック内のワークシートも
1 つしかないのであれば別ですが)
 
引用:
chkValueCommon = chkValue(denbun, pageNo, ramName)

また、chkValueCommon の中で呼び出している
上記の Function プロシージャの内部処理についても
念のため検証された方がよいでしょう。
 
引用:
この部分は
 
IsObject(Application.Caller)
 
でよろしいのでしょうか?

それらの関数を数式セルからのみ呼び出すつもりなのであれば
それでも良いと思います。
 
(別解)
----------------------------------------------------------------
 
If TypeName(Application.Caller) = "Range" Then
 
----------------------------------------------------------------
 
引用:
私が作った関数ではないですがお恥ずかしい限りです。

心中お察し致します。

回答
投稿日時: 18/11/09 22:28:59
投稿者: チオチモリン

引用:

        If Left(Cells(ActiveCell.row, Result_POSITION), 4) = Left(Application.Caller.Offset(0, -1), 4) Then

 
同一行に書かれていますので、意図して書かれたコード(使い分けている)のようにも思えます。
つまり、
行を選択しておいて、強制再計算により、結果を見るような仕様なのかもしれません。
(ご自身で書かれたコードではないようですし、運用の仕方を間違えているだけかも?)
 

投稿日時: 18/11/15 08:13:09
投稿者: George

みなさんありがとうございました。
 
作成者にここで指摘いただいた点について伝えた上で
私の方でも確認出来ましたのでクローズとさせていただきます。
 
Application.Caller で解決しました。