[[20220810173159]]　『Applicationを格納したら…』（ngk）　ページの最後に飛ぶ

『Applicationを格納したら…』（ngk）

こんにちは。

これが必要なのかは置いておいて、

Dim App As New Application
Dim xlsF As WorksheetFunction
Set xlsF = App.WorksheetFunction

として、エクセル関数を使った時と

Dim App As Application
Dim xlsF As WorksheetFunction
Set App = Application
Set xlsF = App.WorksheetFunction

として、エクセル関数を使った時とで
計算結果が同じ時と違う時があるのは何故でしょうか？

ご存知の方は教えてもらえたら幸甚です。

＜使用 Excel：unknown、使用 OS：unknown ＞

うわ～。。。＾＾
不思議ですね～、
後学のため具体的に教えて戴けますか

（隠居Z） 2022/08/10(水) 17:37

 Option Explicit
Public v()
Sub t1()
    Dim App As New Application
    Dim xlsF As WorksheetFunction
    Set xlsF = App.WorksheetFunction
    v = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    MsgBox xlsF.Sum(v)
    t2
End Sub
Private Sub t2()
    Dim App As Application
    Dim xlsF As WorksheetFunction
    Set App = Application
    Set xlsF = App.WorksheetFunction
    MsgBox xlsF.Sum(v)
End Sub
10回くらいだと計算結果はおなじでした
excel　2016
win　10
でした。
（隠居Z） 2022/08/10(水) 17:49

よく話題に上るにはMatch関数でエラーに関する扱いですね。
エラーになって止まるものと、止まらずにエラー値だけ返す(Application)
ものがあります。
何故かはMs社しか回答できないでしょう。
開発時期の違いもあるでしょうね。
Excel庶民としては、そう言うものとして活用するだけです。

勿論、不思議に思う気持ちや、そうした質問をすることは大切と思います。　
（γ） 2022/08/10(水) 17:53

Application.WorksheetFunction
WorksheetFunction

これ同じです。エラー値返せません。
実行エラー。

Vlookup なども同じ。

Application.Vlookup
などは、office95の時の書き方で、2000以降で消すつもりだった？のに
未だに残ってる感じ。
エラートラップでかわすより、こっちの方がよく使われるから？？？
重宝してます。
（まだ16） 2022/08/10(水) 20:02

 質問者さんのサンプルコードは
 Appllcationのインスタンスが違うだけなので、
 インスタンスが違うと関数の結果が違うのというのも不思議な話ですが、
 その事象を確認できるコードやサンプルデータの提示をお願いします。

 Application.Match と Application.WorksheetFunction.Match の
 挙動の違いともまたちょっと違いますよね
（´・ω・`） 2022/08/10(水) 20:13

https://www.239-programing.com/cgi-bin/excelvba_bbs.cgi?id=936
（りんく） 2022/08/11(木) 07:24

 挙動の違いですか？
 詳しくは解りませんが、こういうやつですかね？

 Sub tes1()
 dd = Application.CountIf(Range("A1:A15"), Range("A1:A15"))

 For Each nn In dd
   MsgBox nn
 Next
 MsgBox dd(1, 1)
 End Sub

 Sub tes2()
 dd = WorksheetFunction.CountIf(Range("A1:A15"), Range("A1:A15"))

 For Each nn In dd
   MsgBox nn
 Next
 MsgBox dd(1, 1)
 End Sub
（まだ16） 2022/08/11(木) 08:39

 念のため書いておきますが、
 質問者さんの主張は、以下の様な場合に、結果が異なる場合があるといってます
 (どんな関数かは書いてないので、SUM関数とは限りません。あくまで例として)

 Sub sample()
    Dim thisApp As Application
    Dim newApp As Application
    Dim ret1 As Variant, ret2 As Variant
    Dim sumRange As Range

    Set thisApp = Application      ' 質問者さんの２つのコードの本質的な違いは
    Set newApp = New Application   ' この２行の違いです

    newApp.Visible = True

    Set sumRange = ThisWorkbook.Worksheets(1).Range("A1:A10")
    For i = 1 To 10
       sumRange.Cells(i, 1).Value = Rnd * 100
    Next

    ret1 = thisApp.WorksheetFunction.Sum(sumRange)
    ret2 = thisApp.WorksheetFunction.Sum(sumRange)

    Debug.Print ret1, ret2, ret1 = ret2
 End Sub

 私はそんな訳ないでしょという立場です
（´・ω・`） 2022/08/11(木) 08:58

 >私はそんな訳ないでしょという立場です

 あれ、（´・ω・`）さんへの返信に見えましたか、
 すみません。
 私も同じです。

 多分質問者は、エラートラップしているのだと思います。
 で、結果が返ったのか、デフォルト値のままなのかが
 まだよく解ってないのかも？
（まだ16） 2022/08/11(木) 09:10

 あわわ、完全に質問を読み違えていました。失礼しました。
 実例を知りたいですね。

（γ） 2022/08/11(木) 13:56

皆さんご回答ありがとうございます。

私が実際に起きた例で行くと

標準モジュール(仮にSampleとします)に

Option Explicit

'共通のオブジェクト
Public App As New Application
Dim xlsF As WorksheetFunction

としてクラスモジュールに

Public Sub Test

    Set Sample.xlsF = App.WorksheetFunction 
    With まとめファイル.Worksheets(1)
        Dim cnt As Long: cnt = Sumple.xlsF.CountA(.Range("C:C"))

としたとき、C列がすべて空欄(""やスペースではなく)であるにも関わらす
cntの値が1048576になってしまったので、
イミディエイトウィンドウでNewで宣言した場合とNewを付けずSetで格納した場合とで
cntの値が変わった、という現象が起きました。

同じ使い方をしても他のファイルでは正しく動いてたので何故かな。。と思いまして
（ngk） 2022/08/12(金) 07:56

＞イミディエイトウィンドウで～
の部分を

Newで宣言した場合とNewを付けずSetで格納した場合とで
イミディエイトウィンドウで確認したところ
cntの値がNewありは1048576、Setで格納は0と
違う結果を示した、という現象が起きました。

に変えてく下さい。
（ngk） 2022/08/12(金) 11:01

実行例

Option Explicit

Public NewApp As New Application
Public ThisNewApp As Application
Public ThisApp As Application
Public NewWsF As WorksheetFunction
Public ThisNewWsF As WorksheetFunction
Public ThisWsF As WorksheetFunction

Sub test()

    Set ThisApp = Application
    Set ThisNewApp = New Application
    Set NewWsF = NewApp.WorksheetFunction
    Set ThisWsF = ThisApp.WorksheetFunction
    Set ThisNewWsF = ThisNewApp.WorksheetFunction

    Dim NewWb As Workbook
    Set NewWb = Workbooks.Add

    With NewWb.Worksheets(1)

        Debug.Print NewWsF.CountA(.Range("A:A"))
        Debug.Print ThisNewWsF.CountA(.Range("A:A"))
        Debug.Print ThisWsF.CountA(.Range("A:A")), "End"

    End With

    With Workbooks("作成フォルダまとめ.xlsx").Worksheets(1)

        Debug.Print NewWsF.CountA(.Range("A:A"))
        Debug.Print ThisNewWsF.CountA(.Range("A:A"))
        Debug.Print ThisWsF.CountA(.Range("A:A")), "End"

    End With
End Sub

結果：

 1048576 
 1048576 
 0            End
 1048576 
 1048576 
 0            End

Newを付けたらダメらしいです。
理由はわかりません。

＞同じ使い方をしても他のファイルでは正しく動いてたので
同じ使い方をしているファイルはありませんでした。
すみません。
（ngk） 2022/08/12(金) 11:44

とりあえずなるべくシンプルなコードを書いて確認してみました。

 Public Sub test()
    Dim App As Application
    Dim xlsF As WorksheetFunction
    Set App = New Application
    Set xlsF = App.WorksheetFunction

    With ThisWorkbook.Worksheets(1)
        Dim cnt As Long: cnt = xlsF.CountA(.Range("C:C"))
        Debug.Print cnt
    End With
    App.Quit
 End Sub

再現しました。イミディエイトウィンドウに 1048576 と出力されました。

WorksheetFunctionのApplicationのインスタンスと、ThisWorkbookのApplicationのインスタンスが異なるのが、気持ち悪いので、

 Public Sub test1()
    Dim App As Application
    Dim xlsF As WorksheetFunction
    Set App = New Application
    Set xlsF = App.WorksheetFunction

    With App.Workbooks.Open(ThisWorkbook.FullName, , True)
        Dim cnt As Long: cnt = xlsF.CountA(.Worksheets(1).Range("C:C"))
        Debug.Print cnt
        .Close
    End With
    App.Quit
 End Sub

別インスタンスのApplicationでブックを開いて、それを対象にしたら、０が出力されました。

別インスタンスのApplicationのブックは参照できないような気かするのでエラーになるのが自然な気かしますが、なぜ、 1048576が返るのかは謎です。

（hatena） 2022/08/12(金) 11:49

Option Explicit

Sub test()

    #Const flg = 1

    Dim App As Application
    Dim Func As WorksheetFunction

#If flg = 1 Then

    Set App = New Application
#Else
    Set App = Application
#End If

    Set Func = App.WorksheetFunction

    MsgBox Func.CountA(ThisWorkbook.Worksheets(1).Range("A:A"))
End Sub

 >cntの値がNewありは1048576

現象を確認しました。
なぜそうなるかの見解は識者の方に。。。。

（まっつわん） 2022/08/12(金) 12:02

 現象を確認しました。
 >私はそんな訳ないでしょという立場です
 撤回してお詫び申し上げます。
　
 原因・理由はわかりませんが。
（´・ω・`） 2022/08/12(金) 14:34

 原因の「推測」ですけども、

 別インスタンスのWorksheetFunctionにThsiworkbookのRangeオブジェクトが渡されているかに見えますが、
 規定値(Valueプロパティ）が渡されているに過ぎず、全データが値ありとしてカウントされる。

 一方、当インスタンスのWorksheetFunctionは間違いなくThsiworkbookのRangeオブジェクトが渡されているので、
 その関数の仕様によって、Empty値は排除されてカウントされる。

 Sub test()
    Dim App As Application

    Set App = New Application

    With ThisWorkbook.Worksheets(1)
        .Range("A1").Value = 1
        .Range("A2").Value = Empty
        .Range("A3").Value = "A"

        Debug.Print WorksheetFunction.CountA(.Range("A1:A3"))        '2
        Debug.Print WorksheetFunction.CountA(.Range("A1:A3").Value)  '3

        Debug.Print App.WorksheetFunction.CountA(.Range("A1:A3"))    '3
    End With
End Sub

（半平太） 2022/08/12(金) 14:58

つまり同じインスタンスじゃないとRangeオブジェクトで渡さないってことなんですかねぇ・・・

それぞれのインスタンスでやってみた結果

Option Explicit

Public NewApp As New Application

 Public ThisNewApp As Application
 Public ThisApp As Application
 Public NewWsF As WorksheetFunction
 Public ThisNewWsF As WorksheetFunction
 Public ThisWsF As WorksheetFunction

Sub test()

    Set ThisApp = Application
    Set ThisNewApp = New Application
    Set NewWsF = NewApp.WorksheetFunction
    Set ThisWsF = ThisApp.WorksheetFunction
    Set ThisNewWsF = ThisNewApp.WorksheetFunction

    Dim NewWb As Workbook
    Set NewWb = ThisApp.Workbooks.Add

    With NewWb.Worksheets(1)
        Debug.Print NewWsF.CountA(.Range("A:A"))
        Debug.Print ThisNewWsF.CountA(.Range("A:A"))
        Debug.Print ThisWsF.CountA(.Range("A:A")), "End"
    End With

    ThisApp.DisplayAlerts = False
    NewWb.Close
    ThisApp.DisplayAlerts = True

    Set NewWb = NewApp.Workbooks.Add

    With NewWb.Worksheets(1)
        Debug.Print NewWsF.CountA(.Range("A:A"))
        Debug.Print ThisNewWsF.CountA(.Range("A:A"))
        Debug.Print ThisWsF.CountA(.Range("A:A")), "End"
    End With

    ThisApp.DisplayAlerts = False
    NewWb.Close
    ThisApp.DisplayAlerts = True

    Set NewWb = ThisNewApp.Workbooks.Add

    With NewWb.Worksheets(1)
        Debug.Print NewWsF.CountA(.Range("A:A"))
        Debug.Print ThisNewWsF.CountA(.Range("A:A"))
        Debug.Print ThisWsF.CountA(.Range("A:A")), "End"
    End With

    ThisApp.DisplayAlerts = False
    NewWb.Close
    ThisApp.DisplayAlerts = True

End Sub

結果：

 1048576 
 1048576 
 0            End
 0 
 1048576 
 1048576      End
 1048576 
 0 
 1048576      End

（ngk） 2022/08/12(金) 16:45

 Rangeオブジェクトには直接関係しないケースで
 別インスタンスのWorksheetFunctionが異なる挙動をする例がありましたので報告します。
 (なんと、別インスタンスの方が高性能になった)

 Sub test()
     Dim Ary36
     Dim Ary37
     Dim AppOwn As Application
     Dim AppNew As Application

     Set AppOwn = Application
     Set AppNew = New Application

     Columns("A").ClearContents

     Range("A65536") = 36 'ターゲットに36を入力
     Range("A65537") = 37 'ターゲットに37を入力

     Ary36 = Range("A1:A65536").Value '値配列作成
     Ary37 = Range("A1:A65537").Value '値配列作成

     Debug.Print AppOwn.WorksheetFunction.Match(36, Ary36, 0) ' →65536
     Debug.Print AppNew.WorksheetFunction.Match(36, Ary36, 0) ' →65536

     Debug.Print AppOwn.WorksheetFunction.Match(37, Ary37, 0) ' →実行時エラー
     Debug.Print AppNew.WorksheetFunction.Match(37, Ary37, 0) ' →65537
 End Sub

 通常、Lookup系の関数は、第二引数(検索範囲）が65536個を越えた場合、
 セル範囲なら問題は起きませんが、値配列の場合は#NAエラーになります。

 不可解な仕様なので、バグと呼んでもいいのでしょうが、その評価は置くとして、
 この振る舞いを利用して、両者に違いが出るか検証してみました。

 自インスタンスは、予想通りエラーになりましたが、
 他インスタンスは、チャンとヒットします。

（半平太） 2022/08/30(火) 11:21

悪いことばかりではないんですね。
検証有難うございます。
（ngk） 2022/08/30(火) 18:06

コメント返信：

[ 一覧(最新更新順) ]

[[20220810173159]] 『Applicationを格納したら…』（ngk） ページの最後に飛ぶ

[[20220810173159]]　『Applicationを格納したら…』（ngk）　ページの最後に飛ぶ