Excel (VBA)

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

 
(Windows 8.1 Pro : Excel 2013)
subとfunctionで値が異なる
投稿日時: 22/01/07 16:31:40
投稿者: mypurple999

セルのA1からC1に1を入れて、B列を非表示にして下記を実行すると
mySum1は 2 になり、mySum2は 3 になります。
何故、mySum2は 2 にならないのでしょうか
可視セルの合計を出すユーザー関数を作ろうと思っています。
よろしくお願いします。
 
Sub mySum1()
    MsgBox Application.Sum(Range("A1:C1").SpecialCells(xlCellTypeVisible))
End Sub
 
Function mySum2()
    mySum2 = Application.Sum(Range("A1:C1").SpecialCells(xlCellTypeVisible))
End Function

回答
投稿日時: 22/01/07 17:00:58
投稿者: めんたん

書いてるいる通り実行しましたが、どちらも2を返しましたよ。

回答
投稿日時: 22/01/07 17:07:56
投稿者: Suzu

Range("A1:C1") この参照先について、
  アクティブシートが違うことで 値が変わっていませんか?

投稿日時: 22/01/07 17:09:10
投稿者: mypurple999

回答ありがとうございます。
mySum2の方はセルに「=mySum2()」を入れているのでしょうか。
当方では上記式を入れると3になります。

回答
投稿日時: 22/01/07 17:16:02
投稿者: めんたん

失礼、VBEで見ていました。
セルに入力すると3を返しますね。

回答
投稿日時: 22/01/07 17:37:21
投稿者: simple

横から失礼します。
 
[Excel のユーザー定義関数の制限について]
https://support.microsoft.com/ja-jp/topic/excel-%E3%81%AE%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%AE%9A%E7%BE%A9%E9%96%A2%E6%95%B0%E3%81%AE%E5%88%B6%E9%99%90%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1
によると、
>プロパティの設定およびほとんどのメソッドの実行。
はユーザー定義関数では実行できないとされています。
 
ですから本来は、SpecialCellsメソッドの実行不可のためエラーとなって良さそうですが、
なぜかそれを無視して非表示も含めて算出してしまっている、ということでしょう(推定です)
 
バグなのか仕様なのか、MS社が何とでも言えるというのが実態です。
ユーザーとしては、そういう動作をするものだと観念して使うほか無いと思います。
どうしてもということなら、あなたがMS社を問い詰めるしかないです。

回答
投稿日時: 22/01/07 17:40:41
投稿者: simple

よくありがちなのは、ユーザー定義関数内で最終行を求めようとして
Endプロパティを使おうとすると、これができないんですよ。
この場合は面倒でも、引数にRangeを含めないといけないことになります。

回答
投稿日時: 22/01/07 18:18:48
投稿者: sk

引用:
可視セルの合計を出すユーザー関数

引用:
mySum2 = Application.Sum(Range("A1:C1").SpecialCells(xlCellTypeVisible))

任意の数式セルから呼び出すことを前提として
列方向でも非表示セルを除いた集計が出来る SUBTOTAL 関数」
みたいなユーザー関数を作成したい、ということでしょうか。
 
結論としては「 SpecialCells では無理」ということになるとは思いますが。

回答
投稿日時: 22/01/07 18:33:28
投稿者: めんたん

あまりユーザ定義関数で使ったことがなかったので、そんな動きをするとは知らず。
適当に答えてすみませんでした。
 
とりあえずは表示非表示をチェックしながら足していくのがいいのですかね。
 
Function mySum2(範囲 As Range) As Long
    For Each r In 範囲
        If Not r.Columns.Hidden Then
            mySum2 = mySum2 + r.Value
        End If
    Next
End Function

回答
投稿日時: 22/01/07 18:47:17
投稿者: sk

行/列の方向を問わずに、ということならば
こうなるのではないかと。
 
--------------------------------------------------------
Function SumVisibleCells(Target As Range)
     
    Application.Volatile
     
    Dim rngCell As Range
    Dim dblTotal As Double
     
    For Each rngCell In Target
        If rngCell.EntireRow.Hidden = False And _
           rngCell.EntireColumn.Hidden = False And _
           IsNumeric(rngCell.Value) = True Then
            dblTotal = dblTotal + rngCell.Value
        End If
    Next
 
    SumVisibleCells = dblTotal
 
End Function
--------------------------------------------------------
 
あと、自動再計算関数として使用する場合は
プロシージャ内で Volatile メソッドを呼び出すようにして下さい。

投稿日時: 22/01/07 19:03:03
投稿者: mypurple999

ありがとうございます。
思った通りのことができました。